csharp:SMO run sql script

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using Microsoft.SqlServer.Management.Common;//需添加microsoft.sqlserver.connectioninfo.dll的引用 using Microsoft.SqlServer.Management;// using Microsoft.SqlServer.Management.Smo;//在microsoft.sqlserver.smo.dll中 using Microsoft.SqlServer.Management.Smo.RegisteredServers;//Microsoft.SqlServer.SmoExtended using Microsoft.SqlServer.Management.Smo.Broker; using Microsoft.SqlServer.Management.Smo.Agent; using Microsoft.SqlServer.Management.Smo.SqlEnum; using Microsoft.SqlServer.Management.Smo.Mail; using Microsoft.SqlServer.Management.Smo.Internal; using System.IO; using System.Data.SqlClient; using System.Text; using System.Text.RegularExpressions;   ////引用位置： C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\            /// <summary>         /// 涂聚文 2017-06-02         /// </summary>         /// <param name="sender"></param>         /// <param name="e"></param>         private void button2_Click(object sender, EventArgs e)         {             //Connect to the local, default instance of SQL Server.               Microsoft.SqlServer.Management.Common.ServerConnection conn = new ServerConnection(@"GEOVI-BD87B6B9C\GEOVINDU", "geovindu", "888888");             Server srv = new Server(conn);             //Reference the AdventureWorks2012 database.               Database db = srv.Databases["du"];               //Define a UserDefinedFunction object variable by supplying the parent database and the name arguments in the constructor.               UserDefinedFunction udf = new UserDefinedFunction(db, "IsOWeek");               //Set the TextMode property to false and then set the other properties.               udf.TextMode = false;             udf.DataType = DataType.Int;             udf.ExecutionContext = ExecutionContext.Caller;             udf.FunctionType = UserDefinedFunctionType.Scalar;             udf.ImplementationType = ImplementationType.TransactSql;               //Add a parameter.                 UserDefinedFunctionParameter par = new UserDefinedFunctionParameter(udf, "@DATE", DataType.DateTime);             udf.Parameters.Add(par);               //Set the TextBody property to define the user-defined function.               udf.TextBody = "BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END;";               //Create the user-defined function on the instance of SQL Server.               udf.Create();               //Remove the user-defined function.              // udf.Drop();          }         /// <summary>         /// 涂聚文 2017-06-02         /// </summary>         /// <param name="sender"></param>         /// <param name="e"></param>         private void button3_Click(object sender, EventArgs e)         {             try             {                   //涂聚文 2017-06-02                 Microsoft.SqlServer.Management.Common.ServerConnection serverconn = new ServerConnection(@"GEOVI-BD87B6B9C\GEOVINDU", "geovindu", "888888");                 string sqlConnectionString = @"Data Source=GEOVI-BD87B6B9C\GEOVINDU;Initial Catalog=Du;User ID=Geovin Du;Password=888888";                 //1.有报错问题                 //FileInfo file = new FileInfo("fu.sql");                 //string script = file.OpenText().ReadToEnd();                 //script = script.Replace("\t", " ").Replace("\n", " ");                 //SqlConnection conn = new SqlConnection(sqlConnectionString);                 //Server server = new Server(serverconn);//new ServerConnection(conn)                 //Database db = server.Databases["du"];                 //server.ConnectionContext.ExecuteNonQuery(script);//出问题                       SqlConnection conn = new SqlConnection(sqlConnectionString);                     conn.Open();                 string script = File.ReadAllText("fu.sql");                       // split script on GO command                     IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);                     foreach (string commandString in commandStrings)                     {                         if (commandString.Trim() != "")                         {                             new SqlCommand(commandString, conn).ExecuteNonQuery();                         }                     }                     MessageBox.Show("Database updated successfully.");                                                 }             catch(Exception ex)             {                 MessageBox.Show(ex.Message.ToString());             }         }           /// <summary>         /// Run an .sql script trough sqlcmd.         /// </summary>         /// <param name="fileName">the .sql script</param>         /// <param name="machineName">The name of the server.</param>         /// <param name="databaseName">The name of the database to connect to.</param>         /// <param name="trustedConnection">Use a trusted connection.</param>         /// <param name="args">The arguments passed to the sql script.</param>         public void RunSqlScript(string fileName, string machineName, string databaseName, bool trustedConnection, string[] args)         {             // simple checks             if (!Path.GetExtension(fileName).Equals(".sql", StringComparison.InvariantCulture))                 throw new Exception("The file doesn't end with .sql.");               // check for used arguments             foreach (var shortArg in new[] { "S", "d", "E", "i" })             {                 var tmpArg = args.SingleOrDefault(a => a.StartsWith(string.Format("-{0}", shortArg), StringComparison.InvariantCulture));                 if (tmpArg != null)                     throw new ArgumentException(string.Format("Cannot pass -{0} argument to sqlcmd for a second time.", shortArg));             }               // check the params for trusted connection.             var userArg = args.SingleOrDefault(a => a.StartsWith("-U", StringComparison.InvariantCulture));             var passwordArg = args.SingleOrDefault(a => a.StartsWith("-P", StringComparison.InvariantCulture));             if (trustedConnection)             {                 if (userArg != null)                     throw new ArgumentException("Cannot pass -H argument when trustedConnection is used.");                 if (passwordArg != null)                     throw new ArgumentException("Cannot pass -P argument when trustedConnection is used.");             }             else             {                 if (userArg == null)                     throw new ArgumentException("Exspecting username(-H) argument when trustedConnection is not used.");                 if (passwordArg == null)                     throw new ArgumentException("Exspecting password(-P) argument when trustedConnection is not used.");             }                 // set the working directory. (can be needed with ouputfile)             // TODO: Test if the above statement is correct             var tmpDirectory = Directory.GetCurrentDirectory();             var directory = Path.IsPathRooted(fileName) ? Path.GetDirectoryName(fileName) : Path.Combine(fileName);//this.ProjectRoot             var file = Path.GetFileName(fileName);             Directory.SetCurrentDirectory(directory);               // create cmd line             var cmd = string.Format(string.Format("SQLCMD -S {0} -d {1} -i \"{2}\"", machineName, databaseName, file));             foreach (var argument in args.Where(a => a.StartsWith("-", StringComparison.InvariantCultureIgnoreCase)))                 cmd += " " + argument;             if (trustedConnection)                 cmd += " -E";               // create the process             var process = new System.Diagnostics.Process();             process.StartInfo.FileName = "cmd";             process.StartInfo.CreateNoWindow = true;             process.StartInfo.UseShellExecute = false;             process.StartInfo.RedirectStandardOutput = true;             process.StartInfo.RedirectStandardInput = true;               // start the application             process.Start();             process.StandardInput.WriteLine("@ECHO OFF");             process.StandardInput.WriteLine(string.Format("cd {0}", directory));             process.StandardInput.WriteLine(cmd);             process.StandardInput.WriteLine("EXIT");             process.StandardInput.Flush();             process.WaitForExit();               // write the output to my debug folder and restore the current directory            // Debug.Write(process.StandardOutput.ReadToEnd());             Directory.SetCurrentDirectory(tmpDirectory);         }   //              public void Restore(OdbcConnection sqlcon, string DatabaseFullPath, string backUpPath) //           { //               using (sqlcon) //               { //                   string UseMaster = "USE master"; //                   OdbcCommand UseMasterCommand = new OdbcCommand(UseMaster, sqlcon); //                   UseMasterCommand.ExecuteNonQuery(); //                   // The below query will rollback any transaction which is running on that database and brings SQL Server database in a single user mode. //                   string Alter1 = @"ALTER DATABASE //                   [" + DatabaseFullPath + "] SET Single_User WITH Rollback Immediate"; //                   OdbcCommand Alter1Cmd = new OdbcCommand(Alter1, sqlcon); //                   Alter1Cmd.ExecuteNonQuery(); //                   // The below query will restore database file from disk where backup was taken .... //                   string Restore = @"RESTORE DATABASE //                   [" + DatabaseFullPath + "] FROM DISK = N'" + //                   backUpPath + @"' WITH  FILE = 1,  NOUNLOAD,  STATS = 10"; //                   OdbcCommand RestoreCmd = new OdbcCommand(Restore, sqlcon); //                   RestoreCmd.ExecuteNonQuery(); //                   // the below query change the database back to multiuser //                   string Alter2 = @"ALTER DATABASE //                   [" + DatabaseFullPath + "] SET Multi_User"; //                   OdbcCommand Alter2Cmd = new OdbcCommand(Alter2, sqlcon); //                   Alter2Cmd.ExecuteNonQuery(); //                   Cursor.Current = Cursors.Default; //               } //            }

　　https://www.codeproject.com/Tips/873677/SQL-Server-Database-Backup-and-Restore-in-Csharp

https://www.codeproject.com/Articles/162684/SMO-Tutorial-of-n-Scripting

https://www.codeproject.com/articles/31826/sql-server-authentication-using-smo

https://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp

https://social.msdn.microsoft.com/Forums/en-US/43e8bc3a-1132-453b-b950-09427e970f31/run-a-sql-script-file-in-c?forum=adodotnetdataproviders

 

VS 2010 报错：

+ $exception {"混合模式程序集是针对“v2.0.50727”版的运行时生成的，在没有配置其他信息的情况下，无法在 4.0 运行时中加载该程序集。":null} System.Exception {System.IO.FileLoadException}

App.config 配置：

1.一种方式

?

1 2 3 4

<startup  useLegacyV2RuntimeActivationPolicy="true">   <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>   <supportedRuntime version="v2.0.50727"/> </startup>

2.二种方式

?

1 2 3

<startup useLegacyV2RuntimeActivationPolicy="true">     <supportedRuntime version="v4.0"/>   </startup>