我是不喜欢用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");
|