Csharp:smo Run SQL Script

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;// You need to add Microsoft.sqlserver.connectioninfo.dll reference using microsoft.sqlserver.management;//using The using of microsoft.sqlserver.management.smo;//in Microsoft.sqlserver.smo.dll Microsoft.sqlserver.management.smo.registeredservers;//microsoft.sqlserver.smoextendedusing 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;////Reference location: C:\Program Files\Microsoft SQL Server\100\sdk\assemblies//<summary&gt        ; Tu 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)) + ' + ' + CAST (24+datepart (Day, @DATE) as char (2))) +1 IF ((DATEPART (MM, @DATE            ) =12) and ((DATEPART (DD, @DATE)-datepart (DW, @DATE)) >=) 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>//Tu 2017-06-02///</summary>//<paraM name= "sender" ></param>//<param name= "E" ></param> private void Button3_Click (object sender, EventArgs e) {try {//Xiaozu 2017-06-02 Microsoft.sqlser Ver. 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. Error problem//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);//problem 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" & Gt The name of the server.</param>///<param name= "DatabaseName" >the name of the database to connect to.& lt;/param>//<param name= "Trustedconnection" >use a trusted connection.</param>//<para M name= "args" >the arguments passed to the SQL script.</param> public void Runsqlscript (String fileName, St            Ring 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 ("Ca                Nnot 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 Trustedconn            Ection is not used. "); Set the working directory. (can be needed with ouputfile)//Todo:test If the above statement is correct var tmpdirectory = Di Rectory.            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 would rollback any Transaction which is running on this database and brings SQL Server database in a single user mode.//s                    Tring Alter1 = @ "ALTER database//[" + Databasefullpath + "] SET single_user with Rollback Immediate";// OdbcCommand alter1cmd = New OdbcCommand (Alter1, Sqlcon);//Alter1cmd.executenonquery (                   )////The below query would restore database file from disk where backup was taken ....//                   string restore = @ "restore database//[" + Databasefullpath + "] from DISK = N '" +// Backuppath + @ "' WITH FILE = 1, nounload, STATS = ten ";//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 Error:

+ $exception {"Mixed-mode assemblies are generated for the runtime of the" v2.0.50727 "version and cannot be loaded in the 4.0 runtime without additional information being configured. ": null} system.exception {System.IO.FileLoadException}

App. Config configuration:

1. One way

<startup  uselegacyv2runtimeactivationpolicy= "true" >  <supportedruntime version= "v4.0" sku= ". netframework,version=v4.0 "/>  <supportedruntime version=" v2.0.50727 "/></startup>

2. Two different ways

<startup uselegacyv2runtimeactivationpolicy= "true" >    <supportedruntime version= "v4.0"/>  </ Startup>

　　

　

Csharp:smo Run SQL Script