The implementation of the iSQL function can implement the batch processing function.

For more information, see the download file:/Files/bigmouthz/dnet's Data Warehouse isqlexecution program ( ).rar
Some code is as follows:

DBCore. DataBaseVisitor. AbsDBHelper dbhelper = null;

Private void bt_DBLink_Click (object sender, System. EventArgs e)
{
Try
{
This. GetDBConfig (xml );
This. GetControlValue ();
Dbhelper = GetDBHelper ();
System. Windows. Forms. MessageBox. Show ("the database connection is normal! ");
This. rtb_State.Text + = "the database connection is normal! : _) \ R \ n ";
}
Catch
{
System. Windows. Forms. MessageBox. Show ("database connection failed! ");
This. rtb_State.Text + = "database connection failed! : _ (\ R \ n ";
}
}

Private void bt_LoadDBConfig_Click (object sender, System. EventArgs e)
{
This. GetDBConfig (xml );
This. SetControlValue ();
This. rtb_State.Text + = "the database configuration has been loaded! : _) \ R \ n ";
}

Private void bt_SaveDBConfig_Click (object sender, System. EventArgs e)
{
This. GetControlValue ();
This. SetDBConfig (xml );
This. rtb_State.Text + = "the file has been saved! : _) \ R \ n ";
}

Private void bt_TestDBLink_Click (object sender, System. EventArgs e)
{
Try
{
This. GetDBConfig (xml );
This. GetControlValue ();
DBCore. DataBaseVisitor. AbsDBHelper dbhelper = GetDBHelper ();
Object o = dbhelper. ExecuteScalar (TestSql );
This. rtb_State.Text = "test time:" + System. DateTime. Now. ToString ("yyyy-MM-dd hh: mm: ss") + "\ r \ n ";
This. rtb_State.Text + = o = null? "None Data! ": O. ToString ();
This. rtb_State.Text + = "execution completed! : _) \ R \ n ";
This. rtb_ SQL .Text = TestSql;
System. Windows. Forms. MessageBox. Show ("test passed! The database connection is normal! ");
}
Catch
{
System. Windows. Forms. MessageBox. Show ("database connection test failed! ");
This. rtb_State.Text + = "database connection test failed! : _) \ R \ n ";
}
}

Private void bt_load_Click (object sender, System. EventArgs e)
{
System. Windows. Forms. OpenFileDialog OFD;
OFD = new System. Windows. Forms. OpenFileDialog ();
If (OFD. ShowDialog () = DialogResult. OK)
{
System. IO. StreamReader sr = new System. IO. StreamReader (OFD. FileName, System. Text. Encoding. GetEncoding ("gb2312 "));
This. rtb_ SQL .Text = sr. ReadToEnd ();
Sr. Close ();
This. rtb_State.Text + = "file loading is complete! : _) \ R \ n ";
}
}

Private void batch_Exec ()
{
String split = "\ n ";
String tmp = this. rtb_ SQL .Text;

Int ibit = 0;
Int itmp = 0;
While (ibit> = 0)
{
Ibit = tmp. IndexOf (split, itmp );
If (ibit-itmp <0) break;
String line = tmp. Substring (itmp, ibit-itmp );
Itmp = ibit + 1;
If (line = null | line = "") continue;
Auto_Exec (line );
}
}

Private void auto_Exec (string filepath)
{
Try
{
System. IO. StreamReader sr = new System. IO. StreamReader (filepath, System. Text. Encoding. GetEncoding ("gb2312 "));
This. rtb_ SQL .Text = sr. ReadToEnd ();
Sr. Close ();
This. rtb_State.Text + = "file loaded automatically! : _) \ R \ n ";
Singe_Exec ();
}
Catch
{
This. rtb_ SQL .Text = "";
This. rtb_State.Text + = "An error occurred while loading the file automatically! : _ (\ R \ n ";
}
}

Private void Singe_Exec ()
{
If (dbhelper = NULL)
{
Try
{
This. getdbconfig (XML );
This. getcontrolvalue ();
Dbhelper = getdbhelper ();
This. rtb_state.text + = "the database is connected successfully! : _) \ R \ n ";
}
Catch
{
System. Windows. Forms. MessageBox. Show ("database connection failed! ");
This. rtb_state.text + = "database connection failed! : _ (\ R \ n ";
}
}

Try
{
String SQL = this. rtb_ SQL .Text;
SQL = SQL. Replace ("\ n", ""). Replace ("\ r", ""). Replace ("\ t ","");
If (this. cb_resulttype.Checked)
{
System. Data. DataSet ds = dbhelper. ExecuteDataset (SQL );
This. dg_display.DataSource = ds;
This. dg_display.Expand (-1 );
This. rtb_State.Text + = "execution time:" + System. DateTime. Now. ToString ("yyyy-MM-dd hh: mm: ss") + "\ r \ n ";
This. rtb_State.Text + = "execution completed! : _) \ R \ n ";
}
Else
{
Int o = dbhelper. ExecuteNonQuery (SQL );
This. dg_display.datasource = NULL;
This. rtb_state.text + = "execution time:" + system. datetime. Now. tostring ("yyyy-mm-dd hh: mm: SS") + "\ r \ n ";
This. rtb_state.text + = "number of lines affected by execution:" + O. tostring () + "\ r \ n ";
This. rtb_state.text + = "execution completed! : _) \ R \ n ";
}
}
Catch
{
System. Windows. Forms. MessageBox. Show ("An error occurred while executing the statement! :-(");
This. rtb_state.text + = "An error occurred while executing the statement! : _ (\ R \ n ";
}
}

Private void bt_exec_click (Object sender, system. eventargs E)
{
If (this. cb_exectype.checked)
{
Batch_Exec ();
}
Else
{
Singe_Exec ();
}
}

# Region Control_DBConfigVar
Private void SetControlValue ()
{
This. cb_dblink.Text = this. DBConnection;
This. tb_UserID.Text = this. UserID;
This. tb_PassWord.Text = this. Password;
This. tb_DBAddress.Text = this. DataSource;
This. tb_DBName.Text = this. InitialCatalog;
}

Private void GetControlValue ()
{
This. dbconnection = This. cb_dblink.text;
This. userid = This. tb_userid.text;
This. Password = This. tb_password.text;
This. datasource = This. tb_dbaddress.text;
This. initialcatalog = This. tb_dbname.text;
}
# Endregion

# Region dbconfig

Private string xml = system. environment. currentdirectory + "/DBC. dll ";
Private string SQL = "User ID = $0 $; Password = $1 $; Data Source = $2 $; initial catalog =$ 3 $; persist Security info = false "; // SQL dedicated engine connection string
Private string oledb = "provider = sqloledb; Data Source = $2 $; initial catalog =3 3 $; user id = $0 $; Password = $1 $ ;"; // oledb engine connection string
Private string Odbc = "Driver = {SQL Server}; Server = $2 $; Database = $3 $; Uid = $0 $; Pwd = $1 $ ;"; // Odbc engine connection string

Private string DBConnection; // select the connection method SQL | Odbc | OleDb
Private string UserID; // The Database User ID. The SA user is required to upgrade the database.
Private string Password; // database connection Password
Private string DataSource; // address of the database server or name of the Database Server
Private string InitialCatalog; // specify the name of the database to be accessed

Private string TestSql = "select * from dtproperties"; // test SQL

Private void SetDBConfig (string xml)
{
/**
<? Xml version = "1.0" encoding = "gb2312"?>
<DBConfig>
<DataBase>
<! -- This region prohibits modification of Begin -->
<SQL> user id = $0 $; password = $1 $; data source = $2 $; initial catalog =$ 3 $; persist security info = False </SQL>
<Odbc> Driver = {SQL Server}; Server = $2 $; Database = $3 $; Uid = $0 $; Pwd = $1 $; </Odbc>
<OleDb> Provider = sqloledb; Data Source = $2 $; Initial Catalog =$ 3 $; User Id = $0 $; Password = $1 $; </OleDb>
<! -- End cannot be modified in this region. -->

<DBConnection> SQL </DBConnection> <! -- Select the connection method SQL | Odbc | OleDb -->
<UserID> Sa </UserID> <! -- The Database User ID must use the SA user in upgrading the database -->
<Password> jsl </Password> <! -- Database connection password -->
<DataSource> 10.200.1.20.</DataSource> <! -- Address of the database server or name of the database server -->
<InitialCatalog> Qinghai </InitialCatalog> <! -- Specify the name of the database to be accessed -->
</DataBase>
</DBConfig>
<! -- FrameWork DBConfig. XML -->
**/
XmlTextWriter writer = new XmlTextWriter (xml, System. Text. Encoding. UTF8 );
Writer. Formatting = System. Xml. Formatting. Indented;
Writer. IndentChar = '\ T ';
Writer. WriteStartDocument ();
Writer. WriteComment ("DataBase Config." + System. DateTime. Now. ToString ("yyyy-MM-dd hh: mm: ss "));
Writer. WriteStartElement ("DBConfig ");
Writer. WriteStartElement ("DataBase ");
Writer. WriteComment ("Begin cannot be modified in this region ");
Writer. writeElementString ("SQL", "user id = $0 $; password = $1 $; data source = $2 $; initial catalog = $3 $; persist security info = False ");
Writer. writeElementString ("OleDb", "Provider = sqloledb; Data Source = $2 $; Initial Catalog =$ 3 $; User Id = $0 $; Password = $1 $; ");
Writer. writeElementString ("Odbc", "Driver = {SQL Server}; Server = $2 $; Database = $3 $; Uid = $0 $; Pwd = $1 $; ");
Writer. WriteComment ("End cannot be modified in this region ");
Writer. WriteElementString ("DBConnection", DBConnection );
Writer. WriteComment ("select the connection method SQL | Odbc | OleDb ");
Writer. WriteElementString ("UserID", UserID );
Writer. WriteComment ("the Database User ID must use the SA user in upgrading the database ");
Writer. WriteElementString ("Password", Password );
Writer. WriteComment ("database connection password ");
Writer. WriteElementString ("DataSource", DataSource );
Writer. WriteComment ("database server address or database server name ");
Writer. WriteElementString ("InitialCatalog", InitialCatalog );
Writer. WriteComment ("specify the name of the database to be accessed ");
Writer. WriteElementString ("TestSql", "select * from dtproperties ");
Writer. WriteComment ("SQL for test ");
Writer. WriteEndElement ();
Writer. WriteEndElement ();
Writer. WriteEndDocument ();
Writer. WriteComment ("DBCore DBConfig. XML ");
Writer. Flush ();
Writer. Close ();
}

Private void GetDBConfig (string xml)
{
XmlTextReader reader = null;
Try
{
Reader = new XmlTextReader (xml );
While (! Reader. EOF)
{
While (reader. Read ())
{
If (reader. NodeType = XmlNodeType. Element)
{
Switch (reader. Name)
{
Case "SQL ":
SQL = reader. ReadElementString ("SQL"); break;
Case "OleDb ":
OleDb = reader. ReadElementString ("OleDb"); break;
Case "Odbc ":
Odbc = reader. ReadElementString ("Odbc"); break;
Case "DBConnection ":
DBConnection = reader. ReadElementString ("DBConnection"); break;
Case "UserID ":
UserID = reader. ReadElementString ("UserID"); break;
Case "Password ":
Password = reader. ReadElementString ("Password"); break;
Case "DataSource ":
DataSource = reader. ReadElementString ("DataSource"); break;
Case "InitialCatalog ":
InitialCatalog = reader. ReadElementString ("InitialCatalog"); break;
Case "TestSql ":
TestSql = reader. ReadElementString ("TestSql"); break;
}
}
Else if (reader. NodeType = XmlNodeType. EndElement)
{
Break;
}
}
}
}
Finally
{
If (reader! = Null) reader. Close ();
}
}

Private DBCore. DataBaseVisitor. AbsDBHelper GetDBHelper ()
{
DBCore. DataBaseVisitor. AbsDBHelper dbhelper;
Switch (DBConnection)
{
Case "SQL ":
Dbhelper = new DBCore. DataBaseVisitor. JSLSqlHelper ();
SQL = SQL. Replace ("$0 $", UserID );
SQL = SQL. Replace ("$1 $", Password );
SQL = SQL. Replace ("$2 $", DataSource );
SQL = SQL. Replace ("$3 $", InitialCatalog );
Dbhelper. SetDBConnection = SQL;
Break;
Case "OleDb ":
Dbhelper = new DBCore. DataBaseVisitor. JSLOleDbHelper ();
OleDb = OleDb. Replace ("$0 $", UserID );
OleDb = OleDb. Replace ("$1 $", Password );
OleDb = OleDb. Replace ("$2 $", DataSource );
Oledb = oledb. Replace ("$3 $", initialcatalog );
Dbhelper. setdbconnection = oledb;
Break;
Case "ODBC ":
Dbhelper = new dbcore. databasevisitor. jslodbchelper ();
ODBC = ODBC. Replace ("$0 $", userid );
ODBC = ODBC. Replace ("$1 $", password );
ODBC = ODBC. Replace ("$2 $", datasource );
ODBC = ODBC. Replace ("$3 $", initialcatalog );
Dbhelper. setdbconnection = ODBC;
Break;
Default:
Dbhelper = NULL;
Break;
}
Return dbhelper;
}

# Endregion

Private void bt_SaveFile_Click (object sender, System. EventArgs e)
{
System. Windows. Forms. SaveFileDialog SFD;
SFD = new System. Windows. Forms. SaveFileDialog ();
SFD. filter = "txt files (*. txt) | *. txt | SQL Files (*. SQL) | *. SQL | All files (*. *) | *. *";
SFD. Title = "Save an SQL/Text File ";
SFD. RestoreDirectory = true;
If (SFD. ShowDialog () = DialogResult. OK)
{
If (SFD. FileName! = "")
{
Using (system. Io. streamwriter Sw = new system. Io. streamwriter (SFD. openfile (), system. Text. encoding. getencoding ("gb2312 ")))
{
Sw. Write (this. rtb_ SQL .text );
Sw. Close ();
}
}
}
}

}