如何通過代碼遠端控制Microsoft Sql Server)

微軟的Microsoft Sql Server有個SDK庫，裡面有相當於Manager功能各種命令，只要引用相應的Dll，就可以執行相應的命令，現在就以遠程執行檔案(.sql)為例。

一、引用相應的DLL （version: sql 2008）

引用以下DLL，檔案所在位置是安裝Mssql目錄+Microsoft SQL Server\100\SDK\Assemblies

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Dmf.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.ServiceBrokerEnum.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlClrProvider.dll
Microsoft.SqlServer.SqlEnum.dll

二、方法

#region Execute file of sql
        public bool ExecuteSqlFile(string userId, string password, string server, string file)
        {
            bool result = true;
            try
            {
                string sqlConnectionString = string.Format("server={2};database=master;uid={0};pwd={1}", userId, password, server);

                FileInfo fileInfo = new FileInfo(file);

                StringBuilder script = new StringBuilder(fileInfo.OpenText().ReadToEnd());
                SqlConnection conn = new SqlConnection(sqlConnectionString);

                Microsoft.SqlServer.Management.Smo.Server sqlServer = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(conn));

                sqlServer.ConnectionContext.ExecuteNonQuery(script.ToString());
            }
            catch
            {
                result = false;
            }
            return result;
        }
        #endregion

可要引用此方法就可以執行向遠端資料庫執行檔案了，引用的方法如下：

result = ExecuteSqlFile(dbUsername, dbPassword, dbServer, fileName);