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; // } // }
VS 2010 报错:
+ $exception {"混合模式程序集是针对“v2.0.50727”版的运行时生成的,在没有配置其他信息的情况下,无法在 4.0 运行时中加载该程序集。":null} System.Exception {System.IO.FileLoadException}
App.config 配置:
1.一种方式
<startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/> <supportedRuntime version="v2.0.50727"/> </startup>
2.二种方式
<startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0"/> </startup>
以上就是关于csharp的实例教程的详细内容,更多请关注php中文网其它相关文章!
已抢17129个
抢已抢50101个
抢已抢121060个
抢已抢184403个
抢已抢66221个
抢已抢350659个
抢