SQLDMOHelper

在网上传闻SQLDMO是个好东西，当时没有注意这个传闻是什么时候了，后来才在微软的官网上看见，从SQL Server2008开始就不用SQLDMO了，取而代之的是SMO。无奈了，还写了个Helper。感觉DMO用起来比用SQLCMD少输些命令，而且如果用ADO.NET调用存储过程的话，好像还比DMO方便。
这个SQLDMO绝不是像别的dll那样直接添加引用的，它是个COM组件，要注册了才能使用，这说明了，凡是要跑带这个组件的程序，都要先注册一下组件。
在网上下一个SQLDOM的压缩包，解压后
第一步：首先将msvcr71.dll, SQLDMO.DLL, Resources\2052\sqldmo.rll,Resources\1033\sqldmo.rll 拷贝到C:\Program Files\Microsoft SQL Server\80\Tools\Binn目录。
下载SQLDMO文件
第二步：打开开始，在运行中输入 regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll" 注册sqldmo.dll。
好了，是时候粘贴代码了
  1     class SQLDMOHelper
  2     {
  3 
  4         //增加
  5         public static void CreateDB(string dbName, string hostName, string instanceName, string username, string password)
  6         {
  7             SQLServer server = null;
  8             Application app=null;
  9             try
 10             {
 11                 server = new SQLServer();
 12                 app = new ApplicationClass();
 13                 ConfigServer(server, hostName, instanceName, username, password);
 14                 string dbPath = server.Registry.SQLDataRoot + @"\DATA\" + dbName;
 15                 
 16                 foreach (Database db in server.Databases)
 17                     if (db.Name == dbName) return;
 18 
 19                 Database database = new DatabaseClass();
 20                 DBFile file = new DBFileClass();
 21                 LogFile log = new LogFileClass();
 22 
 23                 database.Name = dbName;
 24                 file.Name = dbName + "file";
 25                 file.PhysicalName = dbPath + "_Data.mdf";
 26                 file.PrimaryFile = true;
 27                 file.FileGrowthType = 0;
 28                 file.FileGrowth = 1;
 29                 database.FileGroups.Item("primary").DBFiles.Add(file);
 30                 log.Name = dbName + "log";
 31                 log.PhysicalName = dbPath + "_Log.ldf";
 32                 database.TransactionLog.LogFiles.Add(log);
 33                 server.Databases.Add(database);
 34 
 35             }
 36             catch
 37             {
 38                 throw;
 39             }
 40             finally
 41             {
 42                 if (server != null)
 43                 {
 44                     server.DisConnect();
 45                     server.Close();
 46                 }
 47                 if (app != null)
 48                     app.Quit();
 49             }
 50 
 51         }
 52 
 53         public static void CreateDB(string dbName, string hostName, string instanceName)
 54         {
 55             CreateDB(dbName, hostName, instanceName, "", "");
 56         }
 57 
 58         //删除
 59 
 60         public static void DeleteDB(string dbName, string hostName, string instanceName, string username, string password)
 61         {
 62             SQLServer server=null;
 63             try
 64             {
 65                 server = new SQLServerClass();
 66                 ConfigServer(server, hostName, instanceName, username, password);
 67                 Database database = server.Databases.Item(dbName, null) as Database;
 68 
 69                 QueryResults queryRestlts = server.EnumProcesses(-1);
 70                 int iColPIDNum = -1;
 71                 int iColDbName = -1;
 72                 for (int i = 1; i <= queryRestlts.Columns; i++)
 73                 {
 74                     string strName = queryRestlts.get_ColumnName(i);
 75                     if (strName.ToUpper().Trim() == "SPID")
 76                     {
 77                         iColPIDNum = i;
 78                     }
 79                     else if (strName.ToUpper().Trim() == "DBNAME")
 80                     {
 81                         iColDbName = i;
 82                     }
 83                     if (iColPIDNum != -1 && iColDbName != -1)
 84                         break;
 85                 }
 86 
 87                 for (int i = 1; i <= queryRestlts.Rows; i++)
 88                 {
 89                     int lPID = queryRestlts.GetColumnLong(i, iColPIDNum);
 90                     string strDBName = queryRestlts.GetColumnString(i, iColDbName);
 91                     if (strDBName.ToUpper() == dbName)
 92                         server.KillProcess(lPID);
 93                 }
 94 
 95                 database.Remove();
 96             }
 97             catch
 98             {
 99                 throw;
100             }
101             finally
102             {
103                 if (server != null)
104                 {
105                     server.DisConnect();
106                     server.Close();
107                 }
108             }
109 
110         }
111 
112         public static void DeleteDB(string dbName, string hostName, string instanceName)
113         {
114             DeleteDB(dbName, hostName, instanceName, "", "");
115         }
116 
117         //附加 
118         public static void AttachDB(string dbName,string dbFileName,string hostName,string instanceName,string username,string password)
119         {
120             SQLServer server = null;
121             try
122             {
123                 server = new SQLServer();
124                 ConfigServer(server, hostName, instanceName, username, password);
125                 server.AttachDBWithSingleFile(dbName, dbFileName);
126             }
127             catch
128             {
129                 throw;
130             }
131             finally
132             {
133                 if (server != null)
134                 {
135                     server.DisConnect();
136                     server.Close();
137                 }
138             }
139         }
140 
141         public static void AttachDB(string dbName, string dbFileName, string hostName, string instanceName)
142         {
143             AttachDB(dbName, dbFileName, hostName, instanceName, "", "");
144         }
145 
146 
147         //分离
148         public static void DetachDB(string dbName, string dbFileName, string hostName, string instanceName, string username, string password)
149         {
150             SQLServer server = null;
151             try
152             {
153                 server = new SQLServer();
154                 ConfigServer(server, hostName, instanceName, username, password);
155                 server.DetachDB(dbName);
156             }
157             catch
158             {
159                 throw;
160             }
161             finally
162             {
163                 if (server != null)
164                 {
165                     server.DisConnect();
166                     server.Close();
167                 }
168             }
169         }
170 
171         public static void DetachDB(string dbName, string dbFileName, string hostName, string instanceName)
172         {
173             DetachDB(dbName, dbFileName, hostName, instanceName, "", "");
174         }
175 
176         //脱机
177         //联机
178 
179         /// <summary>
180         /// 数据库全备份 失败则会抛异常
181         /// </summary>
182         public static void BackupDB(string fileName, string dbName, string hostName, string instanceName, string username, string password)
183         {
184             SQLServer server=null;
185             Backup backup=null;
186             try
187             {
188                 server = new SQLServer();
189                 backup = new Backup(); ConfigServer(server, hostName, instanceName, username, password);
190                 backup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
191                 backup.Database = dbName;
192                 backup.BackupSetName = dbName;
193                 backup.BackupSetDescription = "数据库备份";
194                 backup.Files = fileName;
195                 backup.Initialize = true;
196                 backup.SQLBackup(server);
197             }
198             catch
199             {
200                 throw;
201             }
202             finally
203             {
204                 if (server != null)
205                 {
206                     server.DisConnect();
207                     server.Close();
208                 }
209             }
210         }
211 
212         public static void BackupDB(string fileName, string dbName, string hostName, string instanceName)
213         {
214             BackupDB(fileName, dbName, hostName, instanceName,"","");
215         }
216 
217         /// <summary>
218         /// 还原数据库 失败会抛异常
219         /// </summary>
220         public static void RestoreDB(string fileName, string dbName, string dbFileName, string hostName, string instanceName, string username, string password)
221         {
222             SQLServer server = null;
223             Restore restore = null;
224             try
225             {
226                 server = new SQLServerClass();
227                 restore = new RestoreClass();
228                 ConfigServer(server, hostName, instanceName, username, password);
229 
230                 QueryResults queryRestlts = server.EnumProcesses(-1);
231                 int iColPIDNum = -1;
232                 int iColDbName = -1;
233                 for (int i = 1; i <= queryRestlts.Columns; i++)
234                 {
235                     string strName = queryRestlts.get_ColumnName(i);
236                     if (strName.ToUpper().Trim() == "SPID")
237                     {
238                         iColPIDNum = i;
239                     }
240                     else if (strName.ToUpper().Trim() == "DBNAME")
241                     {
242                         iColDbName = i;
243                     }
244                     if (iColPIDNum != -1 && iColDbName != -1)
245                         break;
246                 }
247 
248                 for (int i = 1; i <= queryRestlts.Rows; i++)
249                 {
250                     int lPID = queryRestlts.GetColumnLong(i, iColPIDNum);
251                     string strDBName = queryRestlts.GetColumnString(i, iColDbName);
252                     if (strDBName.ToUpper() == dbName)
253                         server.KillProcess(lPID);
254                 }
255 
256                 restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
257                 restore.Database = dbName;
258                 restore.Files = fileName;
259                 restore.FileNumber = 1;
260                 restore.ReplaceDatabase = true;
261                 restore.SQLRestore(server);
262             }
263             catch
264             {
265                 throw;
266             }
267             finally
268             {
269                 if (server != null)
270                 {
271                     server.DisConnect();
272                     server.Close();
273                 }
274             }
275         }
276 
277         public static void RestoreDB(string fileName, string dbName, string dbFileName, string hostName, string instanceName)
278         {
279             RestoreDB(fileName, dbName, dbFileName, hostName,instanceName,"","");
280         }
281 
282         private static void ConfigServer(SQLServer server, string hostName, string instanceName, string userName = "", string password = "")
283         {
284             if (string.IsNullOrEmpty(userName) && string.IsNullOrEmpty(password))
285                 server.LoginSecure = true;
286             else
287             {
288                 server.Login = userName;
289                 server.Password = password;
290             }
291             server.Connect(hostName + "\\" + instanceName);
292         }
293     }
posted @ 2013-07-30 19:13 猴健居士阅读(559) 评论(0) 编辑收藏举报
努力加载评论中...
刷新页面返回顶部
SQLDMOHelper

公告
