C#执行SQL脚本文件

方法一：osql.exe(这种方式最简单，但貌似微软以后会取消这个功能，改为SqlCmd了)

最简单的方法就是调用 osql.exe .

具体方法如下(以sql server200 数据库为例):

            try            {                System.Diagnostics.PRocess pr = new System.Diagnostics.Process();                pr.StartInfo.FileName = "osql.exe ";                pr.StartInfo.Arguments = "-U sa -P sa -d master -s 127.0.0.1 -i Sql.sql";                pr.StartInfo.UseShellExecute = false;                pr.StartInfo.RedirectStandardOutput = true;  //重定向输出                             pr.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;//隐藏输出窗口                pr.Start();                                System.IO.StreamReader sr = pr.StandardOutput;                Console.WriteLine(sr.ReadToEnd());                   pr.WaitForExit();                pr.Close();            }            catch(Exception err)            {                MessageBox.Show(err.ToString());            }

以下是osql的一些参数用法:

用法: osql                   [-U 登录 ID]          [-P 密码]  [-S 服务器]                [-H 主机名]           [-E 可信连接]  [-d 使用数据库名称]        [-l 登录超时值]       [-t 查询超时值]  [-h 标题]                  [-s 列分隔符]         [-w 列宽]  [-a 数据包大小]            [-e 回显输入]         [-I 允许带引号的标识符]  [-L 列出服务器]            [-c 命令结束]         [-D ODBC DSN 名称]  [-q "命令行查询"]          [-Q "命令行查询" 并退出]  [-n 删除编号方式]          [-m 错误级别]  [-r 发送到 stderr 的消息]  [-V 严重级别]  [-i 输入文件]              [-o 输出文件]  [-p 打印统计信息]               [-b 出错时中止批处理]  [-X[1] 禁用命令，[退出的同时显示警告]]  [-O 使用旧 ISQL 行为禁用下列项]

------------------------------------------------------------------------------------------------------------------------------------------------

private void execfile(){try{string connStr = "data source={0};user id={1};passWord={2};persist security info=false;packet size=4096";ExecuteSql(connStr, "master", "CREATE DATABASE " + "数据库名"); //这个数据库名是指你要新建的数据库名称 下同System.Diagnostics.Process sqlProcess = new System.Diagnostics.Process();sqlProcess.StartInfo.FileName = "osql.exe ";sqlProcess.StartInfo.Arguments = " -U 数据库用户名 -P 密码 -d 数据库名 -i 存放sql文本的目录sql.sql";sqlProcess.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;sqlProcess.Start();sqlProcess.WaitForExit();sqlProcess.Close();}catch (Exception ex){throw ex;}}private void ExecuteSql(string conn, string DatabaseName, string Sql){System.Data.SqlClient.SqlConnection MySQLConnection = new System.Data.SqlClient.SqlConnection(conn);System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand(Sql, mySqlConnection);Command.Connection.Open();Command.Connection.ChangeDatabase(DatabaseName);try{Command.ExecuteNonQuery();}finally{Command.Connection.Close();}}

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ventry/archive/2006/03/02/613981.aspx

try{System.Diagnostics.Process pr = new System.Diagnostics.Process();pr.StartInfo.FileName = "osql.exe ";pr.StartInfo.Arguments = "-U sa -P lmj -d testdata -s 127.0.0.1 -i script.sql";pr.StartInfo.UseShellExecute = false;pr.StartInfo.RedirectStandardOutput = true; //重定向输出

pr.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;//隐藏输出窗口pr.Start();

System.IO.StreamReader sr = pr.StandardOutput;Console.WriteLine(sr.ReadToEnd());

pr.WaitForExit();pr.Close();}catch (Exception err){//MessageBox.Show(err.ToString());}

asp.net(C#)执行.SQL脚本实现数据库建表

using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.IO; //输入输出流命名空间

public class Lj{public static ArrayList ExecuteSqlFile(string varFileName){//// TODO:读取.sql脚本文件//StreamReader sr = File.OpenText(varFileName);//传入的是文件路径及完整的文件名ArrayList alSql = new ArrayList(); //每读取一条语名存入ArrayListstring commandText = "";string varLine = "";while (sr.Peek() > -1){varLine = sr.ReadLine();if (varLine == ""){continue;}if (varLine != "GO"){commandText += varLine;commandText += " ";}else{alSql.Add(commandText);commandText = "";}}

sr.Close();return alSql;}}public partial class baidu : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e){

string constr = "data source=.;uid=sa;pwd=;database=lx"; // 定义链接字符窜SqlConnection conn = new SqlConnection(constr);conn.Open(); SqlCommand cmd = new SqlCommand();cmd.Connection = conn;ArrayList Lists = Lj.ExecuteSqlFile(Server.MapPath("NetShop.sql")); //调用ExecuteSqlFile()方法，反回 ArrayList对象;string teststr; //定义遍历ArrayList 的变量;foreach (string varcommandText in Lists){teststr = varcommandText; //遍历并符值;//Response.Write(teststr + "|@|<br>");cmd.CommandText = teststr; //为SqlCommand赋Sql语句;cmd.ExecuteNonQuery(); //执行}conn.Close();}}

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/FuCity/archive/2008/05/20/2462295.aspx

有用的using System;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.xml.Linq;

public partial class _Default : System.Web.UI.Page{//protected void Page_Load(object sender, EventArgs e)//{// execfile();//}//private void execfile()//{// //try// //{// // string connStr = "data source={0};user id={1};password={2};persist security info=false;packet size=4096";// // ExecuteSql(connStr, "master", "CREATE DATABASE " + "testdata"); //这个数据库名是指你要新建的数据库名称 下同// // System.Diagnostics.Process sqlProcess = new System.Diagnostics.Process();// // sqlProcess.StartInfo.FileName = "osql.exe ";// // sqlProcess.StartInfo.Arguments = " -U sa -P lmj -d testdata -i script.sql";// // sqlProcess.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;// // sqlProcess.Start();// // sqlProcess.WaitForExit();// // sqlProcess.Close();// //}// //catch (Exception ex)// //{// // throw ex;// //}/

-----------------------------------------------------------------------------------------------------------------------------------------------

方法二：

让C#程序直接执行sql脚本文件,类似SSMS直接打开sql文件并执行一样,搜索了一下结果还真有,不过需要借用mssql的几个类库及命名空间:

Microsoft.SqlServer.ConnectionInfo.dll

Microsoft.SqlServer.Smo.dll

--------------------------------------------

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

这里有一个小插叙,对于vs2008里可以直接引用这个这两个类库,但在vs2010里的引用就无法找到这两个类库了(不知道微软是怎么考虑的)

还好我电脑vs2008和vs2010都安装了

不过我还是避易就难的讲一下,vs2010在引用里无法找到这两个类库,那我们就想办法找到它,首先我电脑安装了mssqlserver2008 ,

我去X:/Program Files/Microsoft SQL Server/100/SDK/Assemblies 找到

Microsoft.SqlServer.ConnectionInfo.dll

Microsoft.SqlServer.Smo.dll

Microsoft.SqlServer.Management.Sdk.Sfc.dll(这个一定要考到你的程序目录,但你可以不引用)

然后手动添加应用,第三个dll一定要引用,不然会报错

代码如下：

//补卡操作         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;             }         }
方法三： 执行sql语句（最麻烦）
代码 public static int ExecuteSqlScript(string sqlFile){    int returnValue = -1;    int sqlCount = 0, errorCount = 0;    if (!File.Exists(sqlFile))    {        Log.WriteLog(string.Format("sql file not exists!", sqlFile));        return -1;    }    using (StreamReader sr = new StreamReader(sqlFile))    {        string line = string.Empty;        char spaceChar = ' ';        string newLIne = "/r/n", semicolon = ";";        string sprit = "/", whiffletree = "-";                       string sql = string.Empty;        do        {            line = sr.ReadLine();            // 文件结束            if (line == null) break;            // 跳过注释行            if (line.StartsWith(sprit) || line.StartsWith(whiffletree)) continue;            // 去除右边空格            line = line.TrimEnd(spaceChar);            sql += line;            // 以分号(;)结尾，则执行SQL            if (sql.EndsWith(semicolon))            {                try                {                    sqlCount++;                    SqlHelper.ExecuteNonQuery(sql, null);                }                catch (Exception ex)                {                    errorCount++;                    Log.WriteLog(sql +  newLIne + ex.Message);                  }                                        sql = string.Empty;            }            else            {                // 添加换行符                if(sql.Length > 0) sql += newLIne;            }        } while (true);    }    if (sqlCount > 0 && errorCount == 0)        returnValue = 1;    if (sqlCount == 0 && errorCount == 0)        returnValue = 0;    else if (sqlCount > errorCount && errorCount > 0)        returnValue = -1;    else if (sqlCount == errorCount)        returnValue = -2;    return returnValue;}