如何在C#里面执行.SQL脚本（SQL SERVER）

    我是不喜欢用OSQL.EXE,ISQL.EXE的，虽然N早之前，用OSQL.EXE查询过局域网内的SQL SERVER服务器。ADO.NET足够了。只要过滤掉“GO“。
    
    如下：
    using System;
using System.Xml;
using System.Data;
using System.IO;
using System.Collections;
using System.Data.SqlClient;

namespace ExecuteSqlFile
{
 /// <summary>
 /// DBAccess 的摘要说明。
 /// </summary>
 public class DBAccess
 {
  public DBAccess()
  {   
  }

  #region 属性

  private static string ConStr = "";

  private static string ConString
  {
   get
   {
    if(ConStr == "")
    {
     try
     {
      XmlDocument doc = new XmlDocument();
      doc.Load("ServerConfig.xml");    
      
      string userid = doc.SelectSingleNode("ServerConfig/UserId").InnerText;
      string password = doc.SelectSingleNode("ServerConfig/PassWord").InnerText;
      string servername = doc.SelectSingleNode("ServerConfig/ServerName").InnerText;
      string database = doc.SelectSingleNode("ServerConfig/DataBase").InnerText;
      ConStr = "server = " + servername + ";uid = "
       + userid + ";pwd = " + password + ";database = " + database;
     }
     catch(Exception ex)
     {
      throw ex;
     }
    }

    return ConStr;
   }
  }

  private static SqlConnection Con;

  public static SqlConnection MyConnection
  {
   get
   {
    if(Con == null)
    {
     Con = new SqlConnection(ConString);     
    }
    return Con;
   }
  }
  
  #endregion 

  
  /// <summary>
  /// 执行Sql文件
  /// </summary>
  /// <param name="varFileName"></param>
  /// <returns></returns>
  public static bool ExecuteSqlFile(string varFileName)
  {
   if(!File.Exists(varFileName))
   {
    return false;
   }

   StreamReader sr = File.OpenText(varFileName);
   
   ArrayList alSql = new ArrayList();

   string commandText = "";

   string varLine = "";

   while(sr.Peek() > -1)
   {
    varLine = sr.ReadLine();
    if(varLine == "")
    {
     continue;
    }
    if(varLine != "GO")
    {
     commandText += varLine;
     commandText += "\r\n";
    }
    else
    {
     alSql.Add(commandText);
     commandText = "";
    }
   }
   
   sr.Close();

   try
   {
    ExecuteCommand(alSql);
   }
   catch
   {
    return false;
   }

   return true;
  }

  private static void ExecuteCommand(ArrayList varSqlList)
  {
   MyConnection.Open();
   SqlTransaction varTrans = MyConnection.BeginTransaction();

   SqlCommand command = new SqlCommand();
   command.Connection = MyConnection;
   command.Transaction = varTrans;

   try
   {   
    foreach(string varcommandText in varSqlList)
    {
     command.CommandText = varcommandText;
     command.ExecuteNonQuery();
    }
    varTrans.Commit();
   }
   catch(Exception ex)
   {
    varTrans.Rollback();
    throw ex;
   }
   finally
   {
    MyConnection.Close();
   }
  }
 }
}



配置文件如下：
<?xml version="1.0" encoding="utf-8" ?>
<ServerConfig>
 <ServerName>localhost</ServerName>
 <DataBase>tttttt</DataBase>
 <UserId>sa</UserId> 
 <PassWord>sa</PassWord> 
</ServerConfig>
保存为ServerConfig.xml
 放到Bin/Debug/里面




调用如下：
DBAccess.ExecuteSqlFile(@"E:\My Document\MySql\test1.sql");

本文地址：http://com.8s8s.com/it/it42917.htm