Csharp: SMO run SQL script, csharpsmo
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 Microsoft. sqlServer. management. smo; // in microsoft. sqlserver. smo. using Microsoft. sqlServe R. 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. regularExpre Ssions; // reference location: C: \ Program Files \ Microsoft SQL Server \ 100 \ SDK \ Assemblies \ // <summary> // tu juwen /// </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 proper Ties. 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 User-defined function. udf. textBody = "begin declare @ ISOweek int SET @ ISOweek = DATEPART (wk, @ DATE) + 1-DATEPART (wk, CAST (DATEPART (yy, @ DATE) as CHAR (4) + '000000') 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) E ND; "; // Create the user-defined function on the instance of SQL Server. udf. create (); // Remove the user-defined function. // udf. drop ();} /// <summary> /// tu juwen /// </summary> /// <param name = "sender"> </param> /// <param name = "e"> </param> private void button3_Click (object sender, eventArgs e) {try {// tu juwen 2017-06-02 Microsoft. sqlServer. management. common. serverConnection serverconn = ne W ServerConnection (@ "GEOVI-BD87B6B9C \ GEOVINDU", "geovindu", "888888"); string sqlConnectionString = @ "Data Source = GEOVI-BD87B6B9C \ GEOVINDU; Initial Catalog = Du; user ID = Geovin Du; Password = 888888 "; // 1. an error occurred. // FileInfo file = new FileInfo ("fu. SQL "); // string script = file. openText (). readToEnd (); // script = script. replace ("\ t ",""). replace ("\ n", ""); // SqlConnection conn = new SqlConnection (sqlConnec TionString); // 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. SQL script trough sqlcmd. /// </summary> /// <param name = "fileName">. SQL script </param> /// <param name = "machineName"> The name of the server. </param> /// <param name = "databaseName"> Name of the database to connect. </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. SQL. "); // check for used arguments foreach (var shortArg in new [] {" S "," d "," E "," I "}) {var tmpArg = args. singleOrDefault (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 =>. startsWith ("-U", StringComparison. invariantCulture); var passwordArg = args. singleOrDefault (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 w Ith 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 =>. 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 error:
+ $ Exception {"a mixed-mode assembly is generated for the runtime of version v2.0.50727. If other information is not configured, the Assembly cannot be loaded at runtime 4.0. ": Null} System. Exception {System. IO. FileLoadException}
App. config Configuration:
1. One method
<startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/> <supportedRuntime version="v2.0.50727"/></startup>
2. Two Methods
<startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0"/> </startup>