让C#程序直接执行sql脚本文件,类似SSMS直接打开sql文件并执行一样,搜索了一下结果还真有,不过需要借用mssql的几个类库及命名空间:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
添加上面三个dll的引用
代码如下:
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 |
//补卡操作
private void PatchCard ( ) { string path = System . Environment . CurrentDirectory ; string CardNo = txtCardNo . Text . Trim ( ) ; string connectonstring =ConfigurationManager . AppSettings [ "connectionString" ] . ToString ( ) ; if (CardNo == null ||CardNo == "" ) { MessageBox . Show ( "卡号不能为空!" ) ; return ; } if ( !path . EndsWith ( @"\" ) ) { path += @"\" ; } path += "补蓝鲸卡.sql" ; //获取脚本位置 if (File . Exists (path ) ) { FileInfo file = new FileInfo (path ) ; string script = file . OpenText ( ) . ReadToEnd ( ) ; script =script . Replace ( "H00001", CardNo ) ; //替换脚本里的参数 try { //执行脚本 SqlConnection conn = new SqlConnection (connectonstring ) ; Microsoft . SqlServer . Management . Smo . Server server = new Server ( new ServerConnection (conn ) ) ; int i = server . ConnectionContext . ExecuteNonQuery (script ) ; if (i == 1 ) { MessageBox . Show ( "恭喜!\n" +CardNo + " 补卡成功!", "成功" ) ; txtCardNo . Text = "" ; CreateLog (CardNo, true ) ; } else { MessageBox . Show ( "@_@ 再试一次吧!", "失败" ) ; } } catch (Exception es ) { MessageBox . Show (es . Message ) ; CreateLog (CardNo + " " + es . Message, false ) ; } } else { MessageBox . Show ( "脚本不存在!" ) ; return ; } } |