public static int ExecSql(string connName, string sql, string param, ref string msg)
{
msg = string.Empty;
int rows = 0;
try
{
DbConnection conn = DbConnManager.GetDbConnection(connName);
DbCommand cmd = conn.CreateCommand();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
DbTransaction dbTrans = conn.BeginTransaction();
try
{
cmd.Transaction = dbTrans;
cmd.CommandText = sql;
AddParamToCommand(cmd, param);
rows = cmd.ExecuteNonQuery();
dbTrans.Commit();
}
catch (Exception e)
{
dbTrans.Rollback();
if (e is MyDbException)
{
throw e;
}
else
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
}
finally
{
conn.Close();
}
}
catch (Exception e)
{
msg = e.Message;
}
return(rows);
}
public static DataTable GetDataToTable(string connName, string sql, string param, ref string msg)
{
msg = string.Empty;
DataTable dataTable = new DataTable();
try
{
DbConnection conn = DbConnManager.GetDbConnection(connName);
DbDataAdapter adapter = DbConnManager.CreateDbDataAdapter(connName);
DbCommand cmd = conn.CreateCommand();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
try
{
adapter.SelectCommand = cmd;
cmd.CommandText = sql;
AddParamToCommand(cmd, param);
adapter.Fill(dataTable);
}
catch (Exception e)
{
if (e is MyDbException)
{
throw e;
}
else
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
}
finally
{
conn.Close();
}
}
catch (Exception e)
{
msg = e.Message;
}
return(dataTable);
}
public static int GetSeq(string connName, int dbId, String tableName, int step = 1)
{
DbConnection conn = DbConnManager.GetDbConnection(connName);
DbCommand cmd = conn.CreateCommand();
StringBuilder sql = new StringBuilder();
int seq = step;
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
DbTransaction dbTrans = conn.BeginTransaction();
try
{
cmd.Transaction = dbTrans;
sql.Append("update BHZT set REC_NUM = REC_NUM+").Append(step).Append(" where TBLNAME = '").Append(tableName).Append("'");
cmd.CommandText = sql.ToString();
//if (DbConnManager.CheckNoRecordsAffectedAfterExecSql(connName, cmd.ExecuteNonQuery()))
if (cmd.ExecuteNonQuery() == 0)
{
sql.Length = 0;
sql.Append("insert into BHZT (TBLNAME,REC_NUM) values ('").Append(tableName).Append("', ").Append(step).Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
}
else
{
sql.Length = 0;
sql.Append("select REC_NUM from BHZT where TBLNAME = '").Append(tableName).Append("'");
cmd.CommandText = sql.ToString();
DbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
seq = DbUtils.GetInt(reader, 0);
}
reader.Close();
}
dbTrans.Commit();
}
catch (Exception e)
{
dbTrans.Rollback();
if (e is MyDbException)
{
throw e;
}
else
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
}
finally
{
conn.Close();
}
return(dbId * 100000000 + seq);
}
public static int ExecSqlList(string connName, ArrayList sqlList, ref string msg)
{
msg = string.Empty;
int rows = 0;
try
{
DbConnection conn = DbConnManager.GetDbConnection(connName);
DbCommand cmd = conn.CreateCommand();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
DbTransaction dbTrans = conn.BeginTransaction();
try
{
cmd.Transaction = dbTrans;
foreach (string sql in sqlList)
{
string[] s = sql.Split('~');
if (s.Length > 0)
{
cmd.CommandText = s[0];
if (s.Length > 1)
{
AddParamToCommand(cmd, s[1]);
}
rows += cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
dbTrans.Commit();
}
catch (Exception e)
{
dbTrans.Rollback();
if (e is MyDbException)
{
throw e;
}
else
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
}
finally
{
conn.Close();
}
}
catch (Exception e)
{
msg = e.Message;
}
return(rows);
}