public int ExeTransactSql(string sSQL, SortedList paramList)
{
SqlTransaction MyTransaction;
//String _ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(_ConnString);
myConnection.Open();
MyTransaction = myConnection.BeginTransaction();
SqlCommand cmd = new SqlCommand(sSQL, myConnection, MyTransaction);
int result;
try
{
cmd.CommandType = CommandType.StoredProcedure;
for (int x = 0; x <= paramList.Count - 1; x++)
{
cmd.Parameters.Add((String)paramList.GetKey(x), paramList.GetByIndex(x));
}
result = Convert.ToInt32(cmd.ExecuteNonQuery());
MyTransaction.Commit();
cmd.Dispose();
cmd = null;
myConnection.Close();
}
catch (Exception ex)
{
if (ex.ToString() == "")
{
}
MyTransaction.Rollback();
result = -1;
//ErrorHandler.WriteError(Convert.ToString(ex));
if (myConnection.State == ConnectionState.Open)
{
myConnection.Close();
}
myConnection.Dispose();
cmd.Dispose();
MyTransaction.Dispose();
}
return(result);
}
public DataTable GetDataTableWithTransact(string sp, SortedList paramList)
{
SqlTransaction MyTransaction;
DataTable dt = new DataTable();
SqlConnection myConnection = new SqlConnection(_ConnString);
myConnection.Open();
MyTransaction = myConnection.BeginTransaction();
SqlCommand cmd = new SqlCommand(sp, myConnection, MyTransaction);
try
{
cmd.Connection = myConnection;
cmd.CommandText = sp;
cmd.CommandType = CommandType.StoredProcedure;
for (int x = 0; x <= paramList.Count - 1; x++)
{
cmd.Parameters.Add((String)paramList.GetKey(x), paramList.GetByIndex(x));
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
da.Dispose();
MyTransaction.Commit();
cmd.Dispose();
cmd = null;
myConnection.Close();
}
catch (Exception ex)
{
if (ex.ToString() == "")
{
}
MyTransaction.Rollback();
dt = null;
//ErrorHandler.WriteError(Convert.ToString(ex));
if (myConnection.State == ConnectionState.Open)
{
myConnection.Close();
}
myConnection.Dispose();
cmd.Dispose();
MyTransaction.Dispose();
}
return(dt);
}