SQL: MS SQL2000
假设服务器有一目录为:D:\ImageData,此目录结构如下:
ImageData
|
|--A文件夹
| |
| |--001.bmp
| |
| |--002.bmp
| ......
|
|--B文件夹
| |
| |--B1文件夹
| | |
| | |--B1001.bmp
| | ......
| |--003.bmp
| |
| |--004.bmp
| |
| |......
希望用SQL生成如下信息:
名称 ParentID ID
A文件夹 0 1
001.bmp 1 2
002.bmp 1 3
B文件夹 0 4
B1文件夹 4 5
B1001.bmp 5 6
003.bmp 4 7
004.bmp 4 8用途:客户端通过SQL管理服务器指定的目录。
假设服务器有一目录为:D:\ImageData,此目录结构如下:
ImageData
|
|--A文件夹
| |
| |--001.bmp
| |
| |--002.bmp
| ......
|
|--B文件夹
| |
| |--B1文件夹
| | |
| | |--B1001.bmp
| | ......
| |--003.bmp
| |
| |--004.bmp
| |
| |......
希望用SQL生成如下信息:
名称 ParentID ID
A文件夹 0 1
001.bmp 1 2
002.bmp 1 3
B文件夹 0 4
B1文件夹 4 5
B1001.bmp 5 6
003.bmp 4 7
004.bmp 4 8用途:客户端通过SQL管理服务器指定的目录。
解决方案 »
- 如何找到login与user的映射关系?
- 如何查询包含 “123” 排列组合的数据
- SQL2000与SQL2005的数据库连接串
- 关于left join 查询时候on和where的区别
- 求高手帮助,表空白行判断问题
- 在sql中做各种查询时,用注释把前面语句注释掉太麻烦,大家有什么好办法?
- 麻烦各位看看这段简单的把10位字符串转化为datetime型的语句!有错误提示!
- 请教:在SQL中如何定义一个整数部分为6,小数部分为2的数值类型呀
- 数据库的导入导出? 急急急急。。。。
- 怎样用一条语句列出所有最大值或最小值的记录
- 请教个关于存储过程的问题!
- 求一ms sql语句,在线等~~~~~~~~~~~~~
http://blog.csdn.net/fredrickhu/archive/2009/09/20/4573387.aspx
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
Declare @tmp int
declare @msg varchar(3000)
SET @msg='Hello. MS SQL Server 2000. I Love you!!!' --這字串將會被寫到SQL Server的安裝目錄下的Nipsan.Txt文件里面
/* 取得SQL Server的安裝路徑*/
declare @strPath nvarchar(512)
Exec sp_MSGet_Setup_paths @strPath OUTPUT
Set @strPath=@strPath+'\Nipsan.Txt' --組成文件名
--創建Scripting組件實例
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
--創建文件
EXEC @hr = sp_OAMethod @object, 'CreateTextFile', @tmp OUTPUT , @strPath
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
--把@msg寫到文件里面去
EXEC @hr = sp_OAMethod @tmp, 'Write',NULL, @msg
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
--關閉文件
EXEC @hr = sp_OAMethod @tmp, 'Close',NULL
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
-----------寫文件操作Demo完成---------------------------------------------------------------
--打開文件
EXEC @hr = sp_OAMethod @object, 'OpenTextFile', @tmp OUTPUT ,@strPath
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
SET @msg=''
--讀文件
EXEC @hr = sp_OAMethod @tmp, 'Read', @msg OUT,3000
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
-----讀文件操作Demo完成
我目的是要将服务器的文件夹信息通过SQL读入,而不是在服务器生成树
exec xp_cmdshell ' bcp ...'
select ... from ...
DBF-16 2
MRS-SW6 2
NHC-20(訊達) 2
NSL-30-D 2
OBC-D6V 2
SASTK-6S叠纸机 2
SF-20Q 2
三重热轮 2
天桥OBC-D6 2
糊付机D9 2
芯纸热轮 2
轨道车MRS-K1 2
轮转切断RSC-6A 2
面纸热轮 2
驱动部DR10 2
2.5米机型SF-300Q 1
DR-9N-新版 2
NHC-30 2
OBC-D6V 2
三重热轮TPH-10 2
分条机NSL-30-DB 2
原纸架MRS-SW6 2
叠纸机SASTK-S6 2
天桥OBC-D6 2
热板DBF-20 2
糊付机D9 2
芯纸热轮MPC-E9 2
轨道车MRS-K1 2
轮转切断RSC-6A 2
面纸热轮LPH-12 2
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;public partial class StoredProcedures
{
static int id = 0;
static ArrayList arrayList = new ArrayList(); class DirFileInfo
{
public SqlString _name;
public SqlInt32 _parentId, _Id,_isDir; public DirFileInfo(SqlString name, SqlInt32 parentid, SqlInt32 id,SqlInt32 isDir)
{
this._name = name;
this._parentId = parentid;
this._Id = id;
this._isDir = isDir;
}
} [Microsoft.SqlServer.Server.SqlProcedure]
public static void FilesTree(SqlString _ParentDirectory)
{
if (_ParentDirectory.IsNull)
throw new Exception("目录名不得为空"); if (!Directory.Exists(_ParentDirectory.Value ))
throw new Exception("该目录不存在"); string[] _dirs = Directory.GetDirectories(_ParentDirectory.Value );
foreach (string dir in _dirs)
{
id++;
arrayList.Add(new DirFileInfo(dir, 0, id, 1));
RecursionDir(dir, id);
} SqlDataRecord dataRecord = new SqlDataRecord(new SqlMetaData("Name", SqlDbType.NVarChar, 1024),
new SqlMetaData("ParentId",SqlDbType.Int ),
new SqlMetaData("Id",SqlDbType.Int),
new SqlMetaData("IsDir",SqlDbType.Int)); SqlContext.Pipe.SendResultsStart(dataRecord); for (int i = 0; i < arrayList.Count; i++)
{
DirFileInfo _dirFileInfo = (DirFileInfo)arrayList[i]; dataRecord.SetSqlString(0, _dirFileInfo._name);
dataRecord.SetSqlInt32(1, _dirFileInfo._parentId);
dataRecord.SetSqlInt32(2, _dirFileInfo._Id);
dataRecord.SetSqlInt32(3, _dirFileInfo._isDir); SqlContext.Pipe.SendResultsRow(dataRecord);
} SqlContext.Pipe.SendResultsEnd(); } static void RecursionDir(string _dir, int parentId)
{
string[] dirInfos = Directory.GetDirectories(_dir); foreach (string dir in dirInfos)
{
id++;
arrayList.Add(new DirFileInfo(dir, parentId, id, 1));
RecursionDir(dir, id); }
string[] files = Directory.GetFiles(_dir); foreach (string file in files)
{
id++;
arrayList.Add(new DirFileInfo(file, parentId, id, 0));
}
}
};CREATE ASSEMBLY Ass_Test
FROM 'G:\CLR_Test\SqlServerProject15\SqlServerProject15\bin\Debug\SqlServerProject15.dll'
WITH PERMISSION_SET=UNSAFE;
GO
CREATE PROCEDURE dbo.FilesTree @ParentDirectory nvarchar(1000)
AS
EXTERNAL NAME Ass_Test.StoredProcedures.FilesTree
GOEXEC dbo.FilesTree N'G:\Test'GO
DROP PROCEDURE dbo.FilesTree;
DROP ASSEMBLY Ass_Test/*
Name ParentId Id IsDir
------------------------------------------------------------ ----------- ----------- -----------
G:\Test\20090308 0 1 1
G:\Test\20090308\currency.txt 1 2 0
G:\Test\20090308\currency1.txt 1 3 0
G:\Test\20090308\currency3.txt 1 4 0
G:\Test\20090308\currency4.txt 1 5 0
G:\Test\20090309 0 6 1
G:\Test\20090309\currency.txt 6 7 0
G:\Test\xls 0 8 1
G:\Test\xls\新建文件夹 8 9 1
G:\Test\xls\新建文件夹\新建 文本文档.txt 9 10 0
G:\Test\xls\新建 Microsoft Office Excel 工作表.xlsx 8 11 0
G:\Test\xls\新建 文本文档.txt 8 12 0(12 行受影响)
*/
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;public partial class StoredProcedures
{
static int id = 0;
static ArrayList arrayList = new ArrayList(); class DirFileInfo
{
public SqlString _name;
public SqlInt32 _parentId, _Id,_isDir; public DirFileInfo(SqlString name, SqlInt32 parentid, SqlInt32 id,SqlInt32 isDir)
{
this._name = name;
this._parentId = parentid;
this._Id = id;
this._isDir = isDir;
}
} [Microsoft.SqlServer.Server.SqlProcedure]
public static void FilesTree(SqlString _ParentDirectory)
{
if (_ParentDirectory.IsNull)
throw new Exception("目录名不得为空"); if (!Directory.Exists(_ParentDirectory.Value ))
throw new Exception("该目录不存在"); string[] _dirs = Directory.GetDirectories(_ParentDirectory.Value );
foreach (string dir in _dirs)
{
id++;
arrayList.Add(new DirFileInfo(dir, 0, id, 1));
RecursionDir(dir, id);
}
string[] files = Directory.GetFiles(_ParentDirectory.Value);
foreach (string file in files)
{
id++;
arrayList.Add(new DirFileInfo(file, 0, id, 0));
} SqlDataRecord dataRecord = new SqlDataRecord(new SqlMetaData("Name", SqlDbType.NVarChar, 60),
new SqlMetaData("ParentId",SqlDbType.Int ),
new SqlMetaData("Id",SqlDbType.Int),
new SqlMetaData("IsDir",SqlDbType.Int)); SqlContext.Pipe.SendResultsStart(dataRecord); for (int i = 0; i < arrayList.Count; i++)
{
DirFileInfo _dirFileInfo = (DirFileInfo)arrayList[i]; dataRecord.SetSqlString(0, _dirFileInfo._name);
dataRecord.SetSqlInt32(1, _dirFileInfo._parentId);
dataRecord.SetSqlInt32(2, _dirFileInfo._Id);
dataRecord.SetSqlInt32(3, _dirFileInfo._isDir); SqlContext.Pipe.SendResultsRow(dataRecord);
} SqlContext.Pipe.SendResultsEnd(); } static void RecursionDir(string _dir, int parentId)
{
string[] dirInfos = Directory.GetDirectories(_dir); foreach (string dir in dirInfos)
{
id++;
arrayList.Add(new DirFileInfo(dir, parentId, id, 1));
RecursionDir(dir, id); }
string[] files = Directory.GetFiles(_dir); foreach (string file in files)
{
id++;
arrayList.Add(new DirFileInfo(file, parentId, id, 0));
}
}
};改一上面的,不然得不到ParentDirectory下的文件。
感谢你的回复!但我不知道如何使用,因为我对C#完全没有接触过!