C # Read the *. SQL file and execute the SQL statement

Idea: Read the file first, use GO as the Section, and use each GO Section as an SQL command to execute it together through transactions.

Public static ArrayList GetSqlFile (string varFileName, string dbname)
{
ArrayList alSql = new ArrayList ();
If (! File. Exists (varFileName ))
{
Return alSql;
}
StreamReader rs = new StreamReader (varFileName, System. Text. Encoding. Default); // pay attention to Encoding
String commandText = "";
String varLine = "";
While (rs. Peek ()>-1)
{
VarLine = rs. ReadLine ();
If (varLine = "")
{
Continue;
}
If (varLine! = "GO" & varLine! = "Go ")
{
CommandText + = varLine;
CommandText = commandText. Replace ("@ database_name = N 'dbhr'", string. Format ("@ database_name = n' {0} '", dbname ));
CommandText + = "\ r \ n ";
}
Else
{
AlSql. Add (commandText );
CommandText = "";
}
}

Rs. Close ();
Return alSql;
}

Public static void ExecuteCommand (ArrayList varSqlList, string connString)
{

SqlConnection MyConnection = new SqlConnection (connString );
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 ();
}
}