存储过程判断文件夹是否存在

[bettercong]

我现在需要往【D:\SalarySystem】路径下插入一个Salary.xls的EXCEL文件
成为这样一个路径【D:\SalarySystem\Salary.xls】

我先首先判断一下D盘下SalarySystem这个文件夹是否存在，
(或者说是【D:\SalarySystem】这个路径是否存在）

请问高手们谁能教我判断的方法！！！

谢谢！！！

[王向飞]

xp_fileexist "D:\SalarySystem\Salary.xls"

[天-笑]

数据库实现不了吧，给你个 C#的代码 你参考下：

using System;
using System.IO;
using System.Windows.Forms;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Runtime.InteropServices;
using System.Text;


namespace DataClient
{
    public class clsFile
    {
        //构造函数
        public clsFile()
        {
            FileName = "log.txt";
            FilePath = Application.StartupPath + "\\log";  //此处Application 与 excel 中 Application 冲突
            IniName = "LocalSet.ini";
            IniPath = Application.StartupPath;
        }

        private string FileName = "";
        private string FilePath = "";
        private string IniName = "";
        private string IniPath = "";

        //INI文件操作代码
        [DllImport("kernel32")]
        private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);

        [DllImport("kernel32")]
        private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retVal, int size, string filePath);


        public void IniWriteValue(string Section, string Key, string Value, string filepath)//对ini文件进行写操作的函数
        {
            WritePrivateProfileString(Section, Key, Value, filepath);

        }

        public void IniWriteValue(string Section, string Key, string Value)//对ini文件进行写操作的函数
        {
            WritePrivateProfileString(Section, Key, Value, IniPath + "\\" + IniName);

        }

        public string IniReadValue(string Section, string Key, string filepath)//对ini文件进行读操作的函数
        {
            StringBuilder temp = new StringBuilder(255);
            int i = GetPrivateProfileString(Section, Key, "", temp, 255, filepath);
            return temp.ToString();

        }

        public string IniReadValue(string Section, string Key)//对ini文件进行读操作的函数
        {
            StringBuilder temp = new StringBuilder(255);
            int i = GetPrivateProfileString(Section, Key, "", temp, 255, IniPath + "\\" + IniName);
            return temp.ToString();

        }

        //检查文件是否存在*******************************************************************************
        public bool CheckFile(string FileWholePath)
        {
            bool retValue = false;
            string errMessage = null;
            try
            {
                if (File.Exists(FileWholePath))

                    retValue = true;

            }
            catch (Exception e)
            {
                errMessage = e.Message;
                retValue = false;
            }

            return retValue;
        }

        //创建文件***************************************************************************************
        public bool CreateFile()
        {
            bool retValue = true;
            string errMessage = null;
            try
            {
                if (!Directory.Exists(FilePath))
                {
                    Directory.CreateDirectory(FilePath);
                }

                FileStream fs = File.Create(FilePath + "\\" + FileName);

                fs.Flush();
                fs.Close();

            }
            catch (Exception e)
            {
                errMessage = e.Message;
                retValue = false;
            }

            return retValue;
        }

        //写TXT文件 content 内容***************************************************************************************
        public bool WriteLine(string content)
        {
            bool retValue = true, ret = false;
            string errMessage = null;
            try
            {
                if (!File.Exists(FilePath + "\\" + FileName))
                {
                    ret = CreateFile();
                    if (!ret)
                    {
                        retValue = false;
                    }
                }
                else
                {
                    FileInfo finfo = new FileInfo(FilePath + "\\" + FileName);
                    if (finfo.Length > 16384)
                    {
                        finfo.Delete();
                    }
                }

                StreamWriter FileWriter = File.AppendText(FilePath + "\\" + FileName);
                FileWriter.WriteLine(content);
                FileWriter.Flush();
                FileWriter.Close();
            }
            catch (Exception e)
            {
                errMessage = e.Message;
                retValue = false;
            }
            return retValue;
        }
    }
}

[天-笑]

[Quote=引用 1 楼 wxf163 的回复:]
xp_fileexist "D:\SalarySystem\Salary.xls"
[/Quote]
很好！！！

[东那个升]

DECLARE @FilePath varchar(1024)

SET @FilePath = 'E:\test\test.xls' 

--substitute a local file for testing locally

CREATE TABLE #FileExistance ([File Exists] smallint
, [File is a Directory] smallint
, [Parent Directory Exists] smallint)

insert into #FileExistance exec xp_fileexist @FilePath

select * from #FileExistance

drop table #FileExistance

[王向飞]

declare @tt table (i1 int ,i2 int ,i3 int)
insert into @tt exec xp_fileexist "F:\SalarySystem\Salary.xls" 
Select i1 from @tt
if exists (Select 1  from @tt where i1>0)
 select '文件存在'
else
select '文件不存在'

[永生天地]

一、xp_fileexist调用说明 

1、 
xp_fileexist "c:\autoexec.bat" 

File Exists  File is a Directory   Parent Directory Exists 
-----------    -------------------       ----------------------- 
1                 0                              1 
--1表存在，0表不存在 
(1 row(s) affected) 
------------------------------------------ 
2、 
declare @result int 
exec xp_fileexist 'c:\autoexec.bat', @result output 

@result=1表文件存在 
@result=0表文件不存 

二、测试 

if exists (select * 
            from sysobjects 
                where type='u' 
                    and 
                name = 'temp_xp_fileexist') 
    begin 
        drop table temp_xp_fileexist 
end 
        
create table temp_xp_fileexist(a bit,b bit,c bit) 
--declare @sql nvarchar(1000) 
--set @sql=@path+@fname 

Declare @result int 

--文件夹存在 011 0 
--insert into temp_xp_fileexist 
--exec xp_fileexist 'C:\test' 
--insert into temp_xp_fileexist 
--exec xp_fileexist 'C:\test',@result output 

--文件夹不存在 001 0 
--insert into temp_xp_fileexist 
--exec xp_fileexist 'C:\testss' 
--insert into temp_xp_fileexist 
--exec xp_fileexist 'C:\testss',@result output 

--文件夹存在、文件存在 101 1 
--insert into temp_xp_fileexist 
--exec xp_fileexist 'C:\test\test.txt' 
--exec xp_fileexist 'C:\test\test.txt',@result output 


--文件夹存在、文件存在 000 0 
insert into temp_xp_fileexist 
exec master.dbo.xp_fileexist 'C:\testsdf\test.txt' 
exec xp_fileexist 'C:\testsdf\test.txt',@result output 

--文件夹存在、文件不存在 001 0 
--insert into temp_xp_fileexist 
--exec xp_fileexist 'C:\test\testsdfs.txt' 
--exec xp_fileexist 'C:\test\testsdfs.txt',@result output 

select * from temp_xp_fileexist 

select @result 

drop table temp_xp_fileexist 



三、定义成文件判断函数 
create function dbo.FileExist( 
@filePath nvarchar(600), 
@fileName nvarchar(400) 
) returns int 
as 
begin 

declare @result int 
declare @sql nvarchar(1000) 
set @sql=@filePath+@fileName 

exec master.dbo.xp_fileexist @sql,@result output 
return @result 
end 

[SQLCenter]

xp_cmdshell更方便

declare @retval int
exec @retval = master..xp_cmdshell 'dir "C:\Program Files" /a', no_output
if @retval = 0
	print 'C:\Program Files 存在。'
else
	print 'C:\Program Files 不存在。'