C# 执行.SQL文件

先要添加sqldmo COM组件       COM 引用 "Microsoft  SQLDMO Object Library"

            SQLDMO.SQLServer2 sqlserver = new SQLDMO.SQLServer2Class();
            sqlserver.Connect("192.168.100.240", "sa", "1234");
            SQLDMO.Databases mydbs = sqlserver.Databases;
            SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
            mydb = (SQLDMO.Database)mydbs.Item("master", "sa");
            System.IO.StreamReader s = new System.IO.StreamReader(this.textBox1.Text);
            string sql = s.ReadToEnd();
            mydb.ExecuteImmediate(sql, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, sql.Length);

 

如果是建立数据库：见如下代码

SqlConnection conn=new SqlConnection("server=(local);user=sa;pwd=;database=master");
SqlCommand cmd=new SqlCommand("CREATE DATABASE [我的数据库] COLLATE Chinese_PRC_CI_AS;",conn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();

 

下面也可以执行SQL文件，但不能执行有go的sql文件，问题出在哪里还没搞清楚，如果哪位大哥看出问题，指点一下

        #region  這段代碼只能執行＜沒有GO的.SQL文件＞
        private string GetSql(string Name)
        {
            try
            {
                Assembly Asm = Assembly.GetExecutingAssembly();
                Stream strm = Asm.GetManifestResourceStream(Asm.GetName().Name + "." + Name);
                StreamReader reader = new StreamReader(strm);
                return reader.ReadToEnd();
            }
            catch (Exception ex)
            {
                Console.Write("In GetSql:" + ex.Message);
                throw ex;
            }
        }
        private void ExecuteSql(string DataBaseName, string Sql)
        {
            System.Data.SqlClient.SqlConnection conn=new System.Data.SqlClient.SqlConnection ("Password=1234;Persist Security Info=True;User ID=sa;Initial Catalog=master;Data Source=192.168.100.240");
            System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand(Sql,conn);

            Command.Connection.Open();
            Command.Connection.ChangeDatabase(DataBaseName);
            try
            {
                Command.ExecuteNonQuery();
            }
            finally
            {
                Command.Connection.Close();
            }
        }

 

        ///   <summary>  
        ///   分析msSqlServer文件*.sql  
        ///   </summary>  
        ///   <param   name="m_SqlFilePath">文件路径</param>  
        ///   <returns></returns>  
        private string GetExcuteSql(string m_SqlFilePath)
        {
            string m_ReturnStr = "";
            StreamReader sr = File.OpenText(m_SqlFilePath);
            //string s = sr.ReadLine();
            string sa = sr.ReadToEnd();
            StringBuilder sb = new StringBuilder();
            //while (s != null)
            //{

            //    if (s.Replace("   ", "") == "GO")
            //        sb.Append(";");
            //    else
            //        sb.Append(s+"  ");
            //    s = sr.ReadLine();
            //}
            m_ReturnStr = sb.ToString();
            sr.Close();
            //return m_ReturnStr;
            return sa;
        }
        #endregion

        private void Button1_Click(object sender, EventArgs e)
        {
          //ExecuteSql(strDBName, GetSql("sql.txt"));
          //ExecuteSql("master", GetSql(this.textBox1.Text));
          //ExecuteSql("master", GetExcuteSql(this.textBox1.Text));
            this.ExecSqlFile();
        }