SQL Server Management Object(SMO)大大简化数据库工具的开发 几行代码开发功能强大的SQL工具
开发与数据库有关的程序，经常需要对数据库进行自动化操作，而不是打开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技术实现。
