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://*.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.一种方式
<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:SMO run sql script的更多相关文章
-
How to Enable Trace or Debug for APIs executed as SQL Script Outside of the Applications ?
In this Document Goal Solution 1: How do you enable trace for an API when executed from a SQL ...
-
可重复执行的SQL Script
问题 在工作中偶尔会遇到这样的问题:SQL script重复执行时会报错. 理想的状态下,SQL script跑一遍就够了,是不会重复执行的,但是实际情况往往很复杂. 比如Dev同学在开发时在A环境把 ...
-
MySQL5.7: sql script demo
-- MyISAM Foreign Keys显示不了外键,MyISAM此为5.0 以下版本使用 InnoDB 为5.0以上版本使用 drop table IF EXISTS city; CREATE ...
-
SQLite: sql script demo
如果有成熟的架构,如何根据数据库关系的表.视图等,进行代码生成架构?减少写代码的时间? -- 考虑主键外键 -- create database geovindu; use geovindu; --2 ...
-
How to import .sql script
How to import .sql script 1.Export .sql from pl/sql developer you can reference to other document in ...
-
npm link &; run npm script
npm link & run npm script https://blog.csdn.net/juhaotian/article/details/78672390 npm link命令可以将 ...
-
Run bash script as daemon
linux - Run bash script as daemon - Stack Overflow https://*.com/questions/19233529/run- ...
-
The fileSyncDll.ps1 is not digitally signed. You cannot run this script on the current system.
https://www.opentechguides.com/how-to/article/powershell/105/powershel-security-error.html Unblockin ...
-
doris: shell invoke .sql script for doris and passing values for parameters in sql script.
1. background in most cases, we want to execute sql script in doris routinely. using azkaban, to l ...
随机推荐
-
Android(3)—Mono For Android App版本自动更新(2)
0.前言 这篇博文是上一篇的延续,主要是修改上一个版中的BUG和优化一些待完善的项,也算是结贴,当然还有需要完善的,等日后项目中用到的时候再单独写出来吧,本篇主要写升级改进的部分: 改进1.修复[BU ...
-
.Net程序员之不学Java做安卓开发:Android Studio中的即时调试窗口
对学.Net的人来说,JAVA开发是一场噩梦. .net中的即时窗口,调试时直接在里面写代码,对程序中的各种方法/属性进行调用,很方便. Android Studio中找了好久,参考如下网址,也有类似 ...
-
Android中Service和Activity之间的通信
启动Service并传递数据进去: Android中通过Intent来启动服务会传递一个Intent过去. 可以在Intent中通过putExtra()携带数据 Intent startIntent ...
-
解决EasyUI-Datagrid和LinqToEntity结合应用时排序问题
我们在做WEB页面时,时常会选择JQuery框架的Datagrid,如Ext.EasyUI.Flexigrid,数据访问则采用LinqToSQL或LinqToEntity.UI用Jquery框架的目的 ...
-
OpenStack Nova 制作 Windows 镜像
OpenStack Nova 制作 Windows 镜像 windows虚拟机ubuntuimage防火墙云计算 本贴转自http://www.vpsee.com 上次 VPSee 给 OpenS ...
-
nginx低版本不支持pathinfo模式,thinkphp针对此问题的解决办法
将一个thinkphp项目从apache环境移到nginx1.2上,怎奈,nginx这个版本默认不支持pathinfo模式 首先,编辑nginx的虚拟主机配置文件 location ~ .*.(php ...
-
17.1.1.6 Creating a Data Snapshot Using Raw Data Files 创建一个数据快照使用 Raw Data Files
17.1.1.6 Creating a Data Snapshot Using Raw Data Files 创建一个数据快照使用 Raw Data Files 如果数据库是大的, 复制raw 数据文 ...
-
java于23设计模式
详情请参阅23设计模式 版权声明:本文博主原创文章,博客,未经同意不得转载.
-
TortoiseGit - pull request
有一个仓库,叫Repo A.你如果要往里贡献代码,首先要Fork这个Repo,于是在你的Github账号下有了一个Repo A2,.然后你在这个A2下工作,Commit,push等.然后你希望原始仓库 ...
-
在线上服务器上无管理员权限下升级NodeJS版本
前言 最近发现一个线上机器的问题,是因为node版本过低导致的,线上机器的node版本还是0.x版,遂打算升级node版本. 但是发现常规的npm包的n模块无法使用,提示没有权限创建文件夹,导致nod ...