Home>
using system;
using system.collections.generic;
using system.linq;
using system.text;
using system.collections;
using system.text.regularexpressions;
using system.data.sqlclient;
namespace consoleapplication1
{
 class program
 {
  //Note:Go is not allowed in the cmd.executenonquery () statement, and there is an error.
  static string connectionstring="server=20111011-2204 \\ sqlserver2008;uid=ecuser;pwd=1234;database=stu;";
  static void main (string [] args)
  {
   string sql =
@ "alter table student add datebak varchar (16)
  go
 update student set datebak=convert (char, getdate (), 101)
 go
 update student set memo=datebak
go
 alter table student drop column datebak
go
";
   console.writeline ("1. No transaction:");
   executesqlwithgo (sql);
   console.writeline ("2. Use transaction:");
   executesqlwithgousetran (sql);
   console.readline ();
  }
  public static void executesqlwithgo (string sql)
  {
   int effectedrows=0;
   using (sqlconnection conn=new sqlconnection (connectionstring))
   {
    conn.open ();
    sqlcommand cmd=new sqlcommand ();
    cmd.connection=conn;
    try
    {
     //Note:Here we use newline _ followed by 0 or more spaces _ followed by go to split the string
     string [] sqlarr=regex.split (sql.trim (), "\ r \ n \\ s * go", regexoptions.ignorecase);
     foreach (string strsql in sqlarr)
     {
      if (strsql.trim (). length>1&&strsql.trim ()!="\ r \ n")
      {
       cmd.commandtext=strsql;
       effectedrows=cmd.executenonquery ();
      }
     }
    }
    catch (system.data.sqlclient.sqlexception e)
    {
     throw new exception (e.message);
    }
    finally
    {
     conn.close ();
    }
   }
  }
  public static void executesqlwithgousetran (string sql)
  {
   using (sqlconnection conn=new sqlconnection (connectionstring))
   {
    conn.open ();
    sqlcommand cmd=new sqlcommand ();
    cmd.connection=conn;
    sqltransaction tx=conn.begintransaction ();
    cmd.transaction=tx;
    try
    {
     //Note:Here we use newline _ followed by 0 or more spaces _ followed by go to split the string
     string [] sqlarr=regex.split (sql.trim (), "\ r \ n \\ s * go", regexoptions.ignorecase);
     foreach (string strsql in sqlarr)
     {
      if (strsql.trim (). length>1&&strsql.trim ()!="\ r \ n")
      {
       cmd.commandtext=strsql;
       cmd.executenonquery ();
      }
     }
     tx.commit ();
    }
    catch (system.data.sqlclient.sqlexception e)
    {
     tx.rollback ();
     throw new exception (e.message);
    }
    finally
    {
     conn.close ();
    }
   }
  }
 }
}
c
  • Previous Adding RadioButton to ASPNET GridView cannot be selected
  • Next Knowledge of Java's use of this keyword and method overloading