创建数据库的示例
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo; //SMO所在的命名空间
using Microsoft.SqlServer.Management.Common; //SMO所在的命名空间
namespace SMOTest
{
class Program
{
static void Main(string[] args)
{
//通过IP用户名和密码获得连接对象
ServerConnection conn = new ServerConnection("192.168.100.102", "sa", "p@ssw0rd");
Server server = new Server(conn);//获得Server对象
//通过数据库名获得Database对象,但是该数据库并不存在
Database db = new Database(server, "SMOTestDB");
db.Create();//创建数据库
}
}
}
修改数据库
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo; //SMO所在的命名空间
using Microsoft.SqlServer.Management.Common; //SMO所在的命名空间
namespace SMOTest
{
class Program
{
static void Main(string[] args)
{
ServerConnection conn = new ServerConnection("192.168.100.102", "sa", "p@ssw0rd");
Server server = new Server(conn);
Database db = server.Databases["AdventureWorks"]; //通过名字获得Database对象
db.ReadOnly = true; //将数据库设置为只读
db.Alter(); //应用修改到数据库服务器中
}
}
}
删除数据库
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo; //SMO所在的命名空间
using Microsoft.SqlServer.Management.Common; //SMO所在的命名空间
namespace SMOTest
{
class Program
{
static void Main(string[] args)
{
ServerConnection conn = new ServerConnection("192.168.100.102", "sa", "p@ssw0rd");
Server server = new Server(conn);
Database db = server.Databases["SMOTestDB"]; //通过数据库名获得Database对象
db.Drop(); //删除该数据库
}
}
}
--
--12.3.3节示例
--
Column name = new Column(stuTable, "Name", DataType.NVarChar(10));
name.Nullable = false;
stuTable.Columns.Add(name); //创建列Name并添加到表中
Column sex = new Column(stuTable, "Sex", DataType.Bit);
sex.Nullable = false;
stuTable.Columns.Add(sex); //创建列Sex并添加到表中
Column birthday = new Column(stuTable, "Birthday", DataType.Date);
birthday.Nullable = false;
stuTable.Columns.Add(birthday); //创建Birthday列并添加到表中
修改表
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo; //SMO所在的命名空间
using Microsoft.SqlServer.Management.Common; //SMO所在的命名空间
namespace SMOTest
{
class Program
{
static void Main(string[] args)
{
ServerConnection conn = new ServerConnection("192.168.100.102", "sa", "p@ssw0rd");
Server server = new Server(conn);
Database db = server.Databases["SMOTestDB"]; //通过数据库名获得Database对象
Table stuTable = db.Tables["Student"]; //通过表名获得Table对象
//创建新的列Address
Column address = new Column(stuTable, "Address", DataType.NVarChar(200));
address.Nullable = false;
stuTable.Columns.Add(address); //将列Address添加到表中
stuTable.Alter(); //应用对表添加列的修改
}
}
}
删除列
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo; //SMO所需的命名空间
using Microsoft.SqlServer.Management.Common; //SMO所需的命名空间
namespace SMOTest
{
class Program
{
static void Main(string[] args)
{
ServerConnection conn = new ServerConnection("192.168.100.102", "sa", "p@ssw0rd");
Server server = new Server(conn);
Database db = server.Databases["SMOTestDB"];//通过数据库名获得Database对象
db.Tables["Student"].Columns["Address"].Drop();
//通过表名和列名获得列对象,然后删除该列
}
}
}
--
--12.3.4节示例
--
修改存储过程
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo; //SMO所需的命名空间
using Microsoft.SqlServer.Management.Common; //SMO所需的命名空间
namespace SMOTest
{
class Program
{
static void Main(string[] args)
{
ServerConnection conn =
new ServerConnection("192.168.100.102", "sa", "p@ssw0rd");
Server server = new Server(conn);
Database db = server.Databases["SMOTestDB"]; //通过数据库名获得Database对象
StoredProcedure sp = db.StoredProcedures["Hello"];
//通过存储过程名获得StoredProcedure对象
sp.TextMode = false;
sp.Parameters.Add(new StoredProcedureParameter(sp, //为存储过程添加参数
"@name2", DataType.NVarChar(10)));
sp.TextBody = "SELECT 'Hello '+@name+','+@name2"; //设置存储过程内容
sp.Alter(); //应用对存储过程的修改到数据库
}
}
}
--
--12.3.5节示例
--
生成创建表的脚本
ServerConnection conn = new ServerConnection("192.168.100.102", "sa", "p@ssw0rd");
Server server = new Server(conn);
Database db = server.Databases["AdventureWorks"]; //获得Database对象
Table table=db.Tables["AddressType","Person"]; //获得Table对象
foreach (string script in table.Script()) //循环输出创建该表的完整脚本
{
Console.WriteLine(script);
}
系统将生成创建表的脚本：
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[AddressType](
[AddressTypeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
生成删除脚本
ServerConnection conn = new ServerConnection("192.168.100.102", "sa", "p@ssw0rd");
Server server = new Server(conn);
Database db = server.Databases["AdventureWorks"]; //获得Database对象
Table table=db.Tables["AddressType","Person"]; //获得Table对象
ScriptingOptions options = new ScriptingOptions(); //创建ScriptingOptions对象
options.IncludeIfNotExists = true; //是否包含如果不存在则执行某操作的脚本
options.ScriptDrops = true; //是删除脚本
foreach (string script in table.Script(options)) //循环输出删除表的脚本
{
Console.WriteLine(script);
}
使用Scripter生成脚本到文件
ServerConnection conn = new ServerConnection("192.168.100.102", "sa", "p@ssw0rd");
Server server = new Server(conn); //创建Server对象
Scripter sper = new Scripter(server); //通过Server对象创建Scripter对象
UrnCollection urns = new UrnCollection();
Urn urn = new Urn("Server[@Name='MS-STUDYZY']/Database[@Name='AdventureWorks']/Table [@Name='AddressType' and @Schema='Person']"); //定义Urn
urns.Add(urn);
sper.Options.ToFileOnly = true; //将脚本生成到文件
sper.Options.FileName="D:\\Script.sql"; //指定文件路径
sper.Script(urns); //生成脚本
