create [table,column],alter column use SMO

最新推荐文章于 2025-05-29 14:59:57 发布

weixin_30339969

最新推荐文章于 2025-05-29 14:59:57 发布

阅读量104

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/ivan201314/archive/2013/04/21/3033857.html

本文详细介绍了如何使用 SQL Server Management Objects (SMO) 创建 SQL Server 登录名、数据库及表，并设置权限和角色。此外，还展示了如何定义表结构、创建主键、唯一索引、默认值以及外键等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这篇是接着上一篇《add login,create,backup,restore database to Sql Server using SMO》的，主要操作为：

1.Create SQL Server DataBase

2.Create SQL Server Login

3.Create Table,Alter table,Drop Table

顺序不要乱哦,第一步在上一篇我已经操作过了 ,不理解的可以去看哦^_^

由于时间关系,关于Column的的修改删除,大家可以自己完善下或者日后我加上;-)

 1         /// <summary>
 2         /// 创建SQLServer登录名 并指派数据库默认权限及数据库用户映射
 3         /// </summary>
 4         private void createLogin()
 5         {
 6             string LoginName = "Ivan";
 7             string LoginPwd = "123abc";
 8             string dbName="DBSMO";
 9             ServerConnection connection = new ServerConnection();
10             connection.ConnectionString = "Data Source=*.*.*.*;User ID=sa;Password=pwd";
11             Server server = new Server(connection);//Server srv = new Server("(local)");或者Server srv = new Server();都是默认选择本地数据库服务器
12             Microsoft.SqlServer.Management.Smo.Login login = server.Logins[LoginName];
13             if (login != null) 
14             {
15                 login.Drop();
16             }
17             login = new Microsoft.SqlServer.Management.Smo.Login(server, LoginName);
18             login.LoginType = LoginType.SqlLogin;
19             login.PasswordExpirationEnabled = true;
20             login.DefaultDatabase = dbName;
21             login.Create(LoginPwd);
22             DatabaseMapping dm = new DatabaseMapping(LoginName, dbName, LoginName);//将登录名映射到对应的数据库
23             //Response.Write("创建SqlServer登录名成功,loginName="+LoginName+"&loginPwd="+LoginPwd);
24             Database database = server.Databases[dbName];
25             Microsoft.SqlServer.Management.Smo.User user = database.Users[LoginName];
26             if (user != null) 
27             {
28                 user.Drop();
29             }
30             user = new Microsoft.SqlServer.Management.Smo.User(database, LoginName);
31             user.Login = LoginName;
32             user.Create();//创建数据库用户 默认为与之对应的登录名一致
33             user.AddToRole("db_owner");//给创建的数据库用户 分配数据库角色 db_owner拥有该数据库所有操作权限
34         }

 1         /// <summary>
 2         /// 创建Table
 3         /// </summary>
 4         private void createTable()
 5         {
 6             string dbName = "DBSMO";
 7             string loginName = "Ivan";
 8             string loginPwd = "123abc";
 9             ServerConnection connection = new ServerConnection();
10             connection.ConnectionString = "Data Source=*.*.*.*;User ID="+loginName+";Password="+loginPwd+"";
11             Server server = new Server(connection);
12             #region 遍历获取当前登录名下所映射的数据库用户所在的数据库对象
13             foreach(Microsoft.SqlServer.Management.Smo.Login login in server.Logins)
14             {
15                 if(login.Name==loginName)
16                 {
17                     DatabaseMapping[] dm = login.EnumDatabaseMappings();
18                     foreach(DatabaseMapping dbm in dm)
19                     {
20                         if(dbm.DBName==dbName)
21                         {
22                             string tableName="Ivan1";
23                             Database db = server.Databases[dbName];
24                             Microsoft.SqlServer.Management.Smo.Table table = db.Tables[tableName];
25                             if (table != null)
26                             {
27                                 table.Drop();
28                             }
29                             else 
30                             {
31                                 table = new Microsoft.SqlServer.Management.Smo.Table(db, tableName);
32                                 Microsoft.SqlServer.Management.Smo.Column column1 = new Column 
33                                 {
34                                     Name="id",
35                                     Parent=table,
36                                     DataType=DataType.Int,
37                                     Identity=true,
38                                     Nullable=false
39                                 };
40 
41                                 Microsoft.SqlServer.Management.Smo.Column column2 = new Column
42                                 {
43                                     Name = "name",
44                                     Parent = table,
45                                     DataType = DataType.NVarChar(200)
46                                 };
47                                 table.Columns.Add(column1);
48                                 table.Columns.Add(column2);
49                                 table.Create();//无法创建一个 Column 也不包含的 Table。 每个表必须得包含一个column
50                                 Response.Write("表" + tableName + "创建成功");
51                                 //另一种写法
52                                 Microsoft.SqlServer.Management.Smo.Column column3 = new Column(table, "Date", DataType.DateTime);
53                                 
54                                 table.Columns.Add(column3);
55                                 table.Alter();
56                                 Response.Write("表"+tableName+"修改成功");
57                                 table.Drop();
58                                 Response.Write("表" + tableName + "删除成功");
59                             }
60                         }
61                     }
62                 }
63             }
64             #endregion
65 }

2013-04-28：关于创建Table时没有考虑到主键,外键与唯一索引以及默认值的创建下面贴一段代码示例：

  1         /// <summary>
  2         /// 在DBSmo数据里创建一张表 FileList
  3         /// </summary>
  4         private void createTable()
  5         {
  6             #region
  7             string loginName = "Ivan";
  8             string loginPwd = "123abc";
  9             string dbName = "DBSMO";
 10             ServerConnection connection = new ServerConnection();
 11             connection.ConnectionString = "Data Source=*.*.*.*;User ID="+loginName+";Password="+loginPwd+";";
 12             Server server = new Server(connection);
 13             foreach (Microsoft.SqlServer.Management.Smo.Login login in server.Logins)
 14             {
 15                 if (login.Name == loginName)
 16                 {
 17                     DatabaseMapping[] dm = login.EnumDatabaseMappings();
 18                     foreach (DatabaseMapping dbm in dm)
 19                     {
 20                         if (dbm.DBName == dbName)
 21                         {
 22                             string tableName = "FileList";
 23                             Database db = server.Databases[dbName];
 24                             Microsoft.SqlServer.Management.Smo.Table table = db.Tables[tableName];
 25                             if (table != null)
 26                             {
 27                                 msg += "数据库中已存在同名表;";
 28                             }
 29                             else
 30                             {
 31                                 table = new Microsoft.SqlServer.Management.Smo.Table(db, tableName);
 32                                 Microsoft.SqlServer.Management.Smo.Column column1 = new Column
 33                                 {
 34                                     Name = "fileID",
 35                                     Parent = table,
 36                                     DataType = DataType.Int,
 37                                     Identity = true,
 38                                     Nullable = false
 39                                 };
 40                                 Microsoft.SqlServer.Management.Smo.Column column2 = new Column
 41                                 {
 42                                     Name = "fileName",
 43                                     Parent = table,
 44                                     DataType = DataType.NVarChar(50)
 45                                 };
 46                                 Microsoft.SqlServer.Management.Smo.Column column3 = new Column
 47                                 {
 48                                     Name = "fileData",
 49                                     Parent = table,
 50                                     DataType = DataType.Image
 51                                 };
 52                                 Microsoft.SqlServer.Management.Smo.Column column4 = new Column
 53                                 {
 54                                     Name = "fileLength",
 55                                     Parent = table,
 56                                     DataType = DataType.Int
 57                                 };
 58                                 Microsoft.SqlServer.Management.Smo.Column column5 = new Column
 59                                 {
 60                                     Name = "mime",
 61                                     Parent = table,
 62                                     DataType = DataType.VarChar(50)
 63                                 };
 64                                 Microsoft.SqlServer.Management.Smo.Column column6 = new Column
 65                                 {
 66                                     Name = "uploadTime",
 67                                     Parent = table,
 68                                     DataType = DataType.Date
 69                                 };
 70                               
 71                                 table.Columns.Add(column1);
 72                                 table.Columns.Add(column2);
 73                                 table.Columns.Add(column3);
 74                                 table.Columns.Add(column4);
 75                                 table.Columns.Add(column5);
 76                                 table.Columns.Add(column6);
 77                                 table.Create();
 78                                 #region 注意这必须是在表创建成功后 因为test_fileUDdefault1 是单独创建的，位于："可编程性"下面的"默认值"目录下 和table是依赖关系 1：N
 79                                 Default def = new Default(db, "test_fileUDdefault1");
 80                                 def.TextHeader = "create default[test_fileUDdefault1] as";
 81                                 def.TextBody = "getdate()";
 82                                 def.Create();
 83                                 def.BindToColumn(table.Name, "uploadTime");
 84 
 85                                 //Bind the default to a column in a table in AdventureWorks2012
 86                                 //def.BindToColumn("SpecialOffer", "StartDate", "Sales");
 87 
 88                                 //Unbind the default from the column and remove it from the database. 
 89                                 //def.UnbindFromColumn("SpecialOffer", "StartDate", "Sales");
 90                                 //def.Drop();
 91                                 #endregion
 92                                 #region 这是创建主键的 和创建默认值 一样 在表创建成功后执行
 93                                 // Define Index object on the table by supplying the Table1 as the parent table and the primary key name in the constructor.
 94                                 Index pk = new Index(table, "test_filePK1");
 95                                 pk.IndexKeyType = IndexKeyType.DriPrimaryKey;
 96                                 //add column1 as index column
 97                                 IndexedColumn idxCol1 = new IndexedColumn(pk, "fileID");
 98                                 pk.IndexedColumns.Add(idxCol1);
 99                                 //create the primary key
100                                 pk.Create();
101                                 #endregion
102                                 #region 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。一个表可以包含多个唯一索引 ∴这是另一个唯一索引关联uploadTime列的
103                                 //create unique index on the table
104                                 Index unique = new Index(table, "test_fileUnique1");
105                                 unique.IndexKeyType = IndexKeyType.DriUniqueKey;
106                                 //add column1 as the unique index column
107                                 IndexedColumn idxCol2 = new IndexedColumn(unique, "uploadTime");
108                                 //create the unique index
109                                 unique.Create();
110                                 #endregion
111                                 msg += "表创建成功!";
112                                 #region 表2 然后 创建一个外键 关联表1的主键
113                                 Microsoft.SqlServer.Management.Smo.Table table2 = new Microsoft.SqlServer.Management.Smo.Table(db, "Ftable");
114                                 Column col21 = new Column(table2, "id", DataType.Int);
115                                 Column col22 = new Column(table2, "fid", DataType.Int);
116                                 table2.Columns.Add(col21);
117                                 table2.Columns.Add(col22);
118                                 table2.Create();
119                                 // Define a Foreign Key object variable by supplying the Table2 as the parent table and the foreign key name in the constructor. 
120                                 ForeignKey fk = new ForeignKey(table2, "test_table2FK1");
121                                 //add col22 as the foreign key column.
122                                 ForeignKeyColumn fkc = new ForeignKeyColumn(fk, "fid", "fileID");
123                                 fk.Columns.Add(fkc);
124                                 fk.ReferencedTable = "FileList";
125                                 fk.Create();   
126                                 #endregion
127                             }
128         }