如何在C#中用程序执行指定的SQL脚本文件，实现自动安装创建数据库.txt

最新推荐文章于 2019-09-03 21:00:01 发布

comzheng

最新推荐文章于 2019-09-03 21:00:01 发布

阅读量4.1k

点赞数

文章标签：数据库 sql 脚本 c# string exception

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;
    //    //}
    //    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());
    //    }

//}

    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();
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (execfile())
            {
                Response.Write("Success");
            }
        }
    }
/// <summary>
/// 创建连接起用进程建立数据库
/// </summary>
/// <returns></returns>
    private bool execfile()
    {
        try
        {
            string connStr = "data source=127.0.0.1;user id=sa;password=lmj;persist security info=false;packet size=4096";
            ExecuteSql(connStr, "master", "CREATE DATABASE" + " SqlTest");//调用ExecuteNonQuery()来创建数据库
            System.Diagnostics.Process sqlProcess = new System.Diagnostics.Process();//创建一个进程
            sqlProcess.StartInfo.FileName = "osql.exe";//OSQL基于ODBC驱动连接服务器的一个实用工具（可查阅SQL帮助手册）
            //string str = @"C:/Program Files/Microsoft SQL Server/MSSQL/Data";
            sqlProcess.StartInfo.Arguments = " -U sa -P sa -d SqlTest -i C://Program Files//Microsoft SQL Server//MSSQL//Data";//获取启动程序时的参数
            sqlProcess.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;//调用进程的窗口状态，隐藏为后台
            sqlProcess.Start();
            sqlProcess.WaitForExit();
            sqlProcess.Close();
            return true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
/// <summary>
/// 创建数据库，调用ExecuteNonQuery()执行
/// </summary>
/// <param name="conn"></param>
/// <param name="DatabaseName"></param>
/// <param name="Sql"></param>
    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();
        }
    }
}