C#执行SQL脚本文件

来源：互联网发布：java取地址符编辑：程序博客网时间：2024/04/28 20:23

方法一：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(); //每读取一条语名存入ArrayList
string 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;}

0 0