原文 http://www.cnblogs.com/JamesLi2015/archive/2013/05/24/3096214.html
开发与数据库有关的程序,经常需要对数据库进行自动化操作,而不是打开SQL Server Management Studio来写SQL或是图形化操作数据,于是需要一个公共类库,可以处理SQL Server有关的基础操作,比如连接数据库,读取所有的表,修改存储过程,读取表的所有字段及其类型。目前为止,我找到以下方法
1 DMO (legacy of SQL Server 2000)
DMO (Interop.SQLDMO.dll) 操作这个程序集,它包含很多基础的数据库相关的功能。在SQL Server 2000中,这个程序集的作用很大,非托管代码,互操作可能会带来性能损失
2 直接写SQL脚本(ADO.NET)
用ADOO.NET 发送到SQL Server中,比如下面的存储过程,可以帮助产生表的结构信息,这个功能常用来产生hotfix的Revised SQL语句
--exec GenerateScript 'Item'
Create Procedure GenerateScript
(
@tableName varchar(100)
)
as
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)
Begin
declare @sql varchar(8000)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values('bit')
insert into @cols values('binary')
insert into @cols values('bigint')
insert into @cols values('int')
insert into @cols values('float')
insert into @cols values('datetime')
insert into @cols values('text')
insert into @cols values('image')
insert into @cols values('uniqueidentifier')
insert into @cols values('smalldatetime')
insert into @cols values('tinyint')
insert into @cols values('smallint')
insert into @cols values('sql_variant')
set @sql=''
Select
@sql=@sql+
case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Data_Type in (Select datatype from @cols) then '' else '(' end+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+','+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end
when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end+
case when Data_Type in (Select datatype from @cols)then '' else ')' end+
case when Is_Nullable='No' then ' Not null,' else ' null,' end
from
Information_Schema.COLUMNS where Table_Name=@tableName
select
@table= 'Create table ' + table_Name
from
Information_Schema.COLUMNS
where
table_Name=@tableName
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql as DDL
End
Else
Select 'The table '+@tableName + ' does not exist'
再比如,要修改数据库中某些列的排序规则,可以运行下面的脚本
declare @TableName Nvarchar(4000),
@ColumnName Nvarchar(4000),
@CharacterMaxLen Nvarchar(4000),
@CollationName Nvarchar(4000),
@IsNullable Nvarchar(4000),
@DataType Nvarchar(4000),
@SQLText Nvarchar(4000)
SET @CollationName = 'SQL_Latin1_General_CP1_CI_AS'
declare MyTableCursor cursor for
SELECT name FROM sys.Tables
OPEN MyTableCursor
FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE MyColumnCursor Cursor
FOR
SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE from information_schema.columns
WHERE table_name = @TableName AND (Data_Type LIKE '%char%'
OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
ORDER BY ordinal_position
Open MyColumnCursor
FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
@CharacterMaxLen, @IsNullable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
@DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END +
') COLLATE ' + @CollationName + ' ' +
CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
PRINT @SQLText
FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
@CharacterMaxLen, @IsNullable
END
CLOSE MyColumnCursor
DEALLOCATE MyColumnCursor
FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor
通过Google,可以轻松的找到很多这样的脚本,时间的积累,会增加很多实用的脚本。
如果你不想通过Google,可以采用一个灵活的变通方法,下载一个代码生成相关的工具软件,反编译它的源代码,你会看到有很多与SQL元数据相关的SQL语句,直接拿来主义就可以了。
说到代码生成器,Code Smith是很优秀的一个工具,找到它的SchemaProviders目录,看到有以下几个程序集文件
以我熟悉的SQL Server为例子,来看看它有哪些免费的资源给我们用
注意到上图中所示,Code Smith提供了丰富的SQL元数据脚本,可以减少你很多查找Google的时间。
3 SQL Server Management Object (From Microsoft SQL Server,2005/2008/2008 R2/2012)
DMO的托管版本,功能上有很大的增强。SQL Serve的一些工具也是基于这个类库开发的。
每个版本的SQL Server都会伴随着新的Management Object发布,独立成一个Windows 组件包。
举例说明应用SMO是开发SQL Server的工具
SQL Backup and Restore 备份与还原
核心源程序代码
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = “MIS";
BackupDeviceItem backupDeviceItem = new BackupDeviceItem("C:\MIS20130523.bak", DeviceType.File);
backup.Devices.Add(backupDeviceItem);
backup.Initialize = true;
String script = backup.Script(SqlServerSelection);
backup.SqlBackup(SqlServerSelection);
SQL Server Document 给SQL对象添加注释
打开一个数据库表,列举它的字段,给表或字段添加备注信息,使用extended property完成,核心代码如下
//更新表的备注信息
string updateTableDescription = @" if(select count(1) from fn_listextendedproperty(N'MS_Description'," +
" N'SCHEMA',N'{2}',N'TABLE',N'{0}',NULL,NULL))>0 " +
" EXEC sp_updateextendedproperty N'MS_Description',N'{1}', " +
" N'SCHEMA',N'{2}', N'TABLE',N'{0}',NULL,NULL " +
" ELSE " +
" EXEC sp_addextendedproperty N'MS_Description',N'{1}', " +
" N'SCHEMA',N'{2}',N'TABLE',N'{0}' ";
//更新字段的备注信息
string updateColumnDescription=@"if(select count(1) from fn_listextendedproperty(N'MS_Description',N'SCHEMA', "+
" N'{3}',N'TABLE',N'{0}',N'COLUMN', N'{1}'))>0 "+
" EXEC sp_updateextendedproperty N'MS_Description',N'{2}', "+
" N'SCHEMA',N'{3}', N'TABLE',N'{0}',N'COLUMN', N'{1}' "+
" ELSE "+
" EXEC sp_addextendedproperty N'MS_Description',N'{2}', "+
" N'SCHEMA',N'{3}',N'TABLE',N'{0}',N'COLUMN', N'{1}' ";
//读取表的描述
string getTableDescription = " select value from " +
" fn_listextendedproperty(N'MS_Description',N'SCHEMA',N'{1}',N'TABLE'," +
" N'{0}',NULL, NULL) ";
//读取字段的描述
string getColumnDescription=" select objname,value from "+
" fn_listextendedproperty(N'MS_Description',N'SCHEMA',N'{1}',N'TABLE',"+
" N'{0}',N'COLUMN', NULL) ";
如代码所示,上面4个SQL语句,就是这个软件的核心代码。
Create Stored Procedures 产生存储过程的脚本文件
当有新的存储过程要部署给客户时,不必到SQL Serve 中,只需要用这个小工具,即可产生SQL存储过程的源代码
StoredProcedureParameter parm;
StoredProcedure sp=new StoredProcedure(tbl.Parent, procName, spSchema.Name);
sp.TextMode = false
foreach (Column col in tbl.Columns)
{
if (col.InPrimaryKey == true)
{
// Parameter columns
parm = new StoredProcedureParameter(sp, "@" + col.Name);
parm.DataType = col.DataType;
parm.DataType.MaximumLength= col.DataType.MaximumLength;
sp.Parameters.Add(parm);
}
}
sp.Create();
同样的原理,也可以用下面的工具来产生表的创建脚本
Load and Register Assemblies 加载和查看托管程序
以托管代码写的SQL Server Object(Data type,function stored procedure,trigger)需要以程序集注册的方式寄宿(host)到SQL Server中,运用这个工具可以以图形化的界面完成。
SqlAssembly asm;
asm.Create(new String[] { “C:\ManagedType.dll” });
udf = new UserDefinedFunction(db, "StringToInt32");
udf.TextMode = false;
udf.ImplementationType = ImplementationType.SqlClr;
udf.AssemblyName = "UtilityConversion";
udf.ClassName = "Microsoft.Samples.SqlServer.Conversions";
udf.MethodName = "StringToInt32";
udf.FunctionType = UserDefinedFunctionType.Scalar;
udf.DataType = DataType.Int;
parm = new UserDefinedFunctionParameter(udf, "@Input");
udf.Parameters.Add(parm);
parm.DataType = DataType.NVarChar(255);
udf.Create();
Manage Database 管理数据库
可以创建和删除数据库,这个小功能在ERP程序中,可以用来创建一个新套帐。
Database db;
string sDatabaseName="MIS";
db = new Database(SqlServerSelection,sDatabaseName);
DataFile df = new DataFile(fg, sDatabaseName + @"_Data3",
SqlServerSelection.Information.MasterDBPath + @"\"
+ sDatabaseName + @"_Data3" + @".ndf");
db.FileGroups.Add(fg);
LogFile lf = new LogFile(db, sDatabaseName + @"_Log",
SqlServerSelection.Information.MasterDBPath + @"\" + sDatabaseName +
@"_Log" + @".ldf");
db.LogFiles.Add(lf);
db.Create();
Manage Database User 管理数据库用户和登陆
Database db=new Database("MIS");
User usr = new User(db, "James");
usr.Login = "sa"
usr.Create();
仅仅只有这四句,就可以创建用户,这个功能可以用来批量管理SQL Server的用户。
SQL Server Services 管理SQL服务实例
.NET的服务管理接口可以实现这个功能,不过,这里有更简洁的方法,核心代码如下
Service svc;
ManagedComputer mc = new ManagedComputer(SqlServerSelection.Name);
svc=mc.Services[ServicesListView.SelectedItems[0].Text];
svc.Start();
这三行代码,即可启动指定的SQL Server服务,API设计的相当简洁。
Connect to SQL Server 连接管理器
用这个功能,可以返回能够连接的SQL Server连接字符串信息,用于程序的业务逻辑功能。
ServerConnection ServerConn = new ServerConnection();
if (WindowsAuthenticationRadioButton.Checked == true)
{
ServerConn.LoginSecure = true;
}
else
{
ServerConn.LoginSecure = false;
ServerConn.Login = UserNameTextBox.Text;
ServerConn.Password = PasswordTextBox.Text;
}
ServerConn.Connect();
如果这段代码没有抛出异常,表示连接成功,可以依据它返回一个可用的连接字符串。
托管代码可能效率不高,如果不想依赖于Management Object,可以直接写SQL脚本,运用ADO.NET技术实现。