[Post] C # execute an SQL script to read XML files

[Post] C # execute an SQL script to read XML files

Add the following reference:

Using System. IO;
Using System. Data. SqlClient;
Using System. Collections;
Using System. Xml;

The GoSql class is as follows:

Class GoSql
{
Private static string ConStr = "";
Private static string ConString // read the XML configuration file here for Reference
{
Get
{
If (ConStr = "")
{
Try
{
XmlDocument doc = new XmlDocument ();
Doc. Load ("SqlScripts \ ServerConfig. xml"); // The XML Path of the SQL Server Configuration executed here
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;
}
}
Public static bool ExecuteSqlFile (string varFileName)
{
If (! File. Exists (varFileName ))
{
Return false;
}
StreamReader rs = new StreamReader (varFileName, System. Text. Encoding. Default); // pay attention to Encoding
ArrayList alSql = new ArrayList ();
String commandText = "";
String varLine = "";
While (rs. Peek ()>-1)
{
VarLine = rs. ReadLine ();
If (varLine = "")
{
Continue;
}
If (varLine! = "GO ")
{
CommandText + = varLine;
CommandText + = "\ r \ n ";
}
Else
{
CommandText + = "";
}
}
AlSql. Add (commandText );
Rs. Close ();
Try
{
ExecuteCommand (alSql );
Return true;
}
Catch (Exception ex)
{
Throw ex;
}
}
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 ();
}
}
}

The content of the configuration file ServerConfig. xml is as follows:

<? Xml version = "1.0" encoding = "UTF-8"?>
<ServerConfig>
<ServerName> 192.168.0.68 </ServerName>
<DataBase> lanxi_stgylmis </DataBase>
<UserId> sa </UserId>
<PassWord> sa </PassWord>
</ServerConfig>

The call method is as follows:

Private void button#click (object sender, EventArgs e)
{
Try
{// Tell the file path
If (GoSql. ExecuteSqlFile (Application. StartupPath. ToString () + "\ SqlScripts \ 0802.16. SQL "))
{
MessageBox. Show ("the SQL statement is executed! ");
}
Else
{
MessageBox. Show ("operation failed! ");
}
}
Catch (Exception ex)
{
MessageBox. Show (ex. Message. ToString ());
}
}

From: http://www.smartgz.com/blog/Article/1114.asp