C#執行SQL腳本文件

2024-07-21 02:53:18

字體：大中小

來源：轉載

供稿：網友

方法一：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;}