让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; } } |