public int ScalarIntx()
{
int iData = -1;
try
{
SQLSpecific.OpenConn(sql_con);
var wht = sql_cmd.ExecuteScalar();
if (wht == null)
{
iData = 0;
}
else
{
iData = Convert.ToInt32(wht.ToString());
}
} catch (Exception caught)
{
try
{
DBMain.AltLog(LogLevels.Warning, 70105, "ScalarIntx '" + caught.Message + "' " + sql_cmd.CommandText);
sql_con.Close();
return(-1);
} catch { }
}
sql_con.Close();
return(iData);
}
public string Scalar(string sSQL)
{
string sData = "";
try
{
SQLSpecific.OpenConn(sql_con);
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = sSQL;
var o = sql_cmd.ExecuteScalar();
if (o != null)
{
sData = o.ToString();
}
}
catch (System.Data.Common.DbException dbx)
{
DBMain.DBExceptionHandler(dbx, sSQL);
}
catch (Exception caught)
{
try
{
DBMain.AltLog(LogLevels.Warning, 70104, "Scalar '" + caught.Message + "' " + sSQL);
sql_con.Close();
return(null);
}
catch { }
}
sql_con.Close();
return(sData);
}
public DataTable DBProbe(string sSQL)
{
DataTable DT = new DataTable();
try
{
SQLSpecific.OpenConn(sql_con);
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = sSQL;
sql_da.SelectCommand = sql_cmd;
int i = sql_da.Fill(DT);
string s = "Database:" + sql_con.Database + ", DataSource:" + sql_con.DataSource.ToString() + ", ServerVersion:" + sql_con.ServerVersion;
DBDescStr = sql_da.GetType().FullName + "; " + s + " (" + sql_con.ConnectionString + ")";
}
catch (Exception caught)
{
DT = null;
DBErrorStr = caught.Message;
DBMain.AltLog(LogLevels.Warning, 70193, "DBProbe '" + caught.Message + "'");
}
if (sql_con != null)
{
sql_con.Close();
}
return(DT);
}
//Return a DataTable based on SQL query
public DataTable DT(string sSQL)
{
DataTable DT = new DataTable();
try
{
SQLSpecific.OpenConn(sql_con);
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = sSQL;
sql_da.SelectCommand = sql_cmd;
//DS.Reset();
int i = sql_da.Fill(DT); // hangs here during transfer operations
//DT = DS.Tables[0];
}
catch (Exception caught)
{
try
{
DBMain.AltLog(LogLevels.Warning, 70103, "DT '" + caught.Message + "' " + sSQL);
}
catch { }
}
//Return DataTable
if (sql_con != null)
{
sql_con.Close();
}
return(DT);
}
// assumes DB is there, but might need to build it before we get here
public bool Execute(string sSQL)
{
bool needDb = false;
try
{
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = sSQL;
SQLSpecific.OpenConn(sql_con);
try
{
int i = sql_cmd.ExecuteNonQuery();
}
catch (System.Data.Common.DbException dbx)
{
needDb = DBMain.DBExceptionHandler(dbx, sSQL);
}
}
catch (Exception caught)
{
try
{
DBMain.AltLog(LogLevels.Warning, 70101, "Execute '" + caught.Message + "' " + sSQL);
sql_con.Close();
return(false);
}
catch { }
}
if (sql_con != null)
{
sql_con.Close();
}
return(true);
}
public void SetConnection()
{
if (sql_con == null)
{
try
{
if (DBMain.ProviderInvariantName.Equals(""))
{
return;
}
sql_con = DBMain.CreateConnection(useConnStr: true);
sql_da = DBMain.CreateDataAdapter();
SQLSpecific.OpenConn(sql_con);
sql_cmd = sql_con.CreateCommand();
}
catch (Exception caught)
{
DBMain.AltLog(LogLevels.Error, 70100, "Unable to connect with database " + caught.Message);
}
}
}
bool Prep()
{
bool res = false;
if (sql_con == null)
{
if (SetConnection())
{
if (sql_con != null)
{
SQLSpecific.OpenConn(sql_con);
sql_cmd = sql_con.CreateCommand();
res = true;
}
}
}
else
{
res = sql_con.State == ConnectionState.Open;
}
return(res);
}
public long ExecuteTransactionID(ArrayList sqlList)
{
//Execute Sequence of Sql Statements
//Check Sql Type to determine if we have return value to replace in next statement
DbTransaction Trans = null;
string sRtn = "";
Int32 result = -1; // what to return here?
try
{
SQLSpecific.OpenConn(sql_con);
Trans = sql_con.BeginTransaction();
sql_cmd = sql_con.CreateCommand();
sql_cmd.Transaction = Trans;
bool needDb = false;
for (int i = 0; i < sqlList.Count; i++)
{
if (!sqlList[i].ToString().Equals(""))
{
sql_cmd.CommandText = sqlList[i].ToString();
//DBMain.AltLog(LogLevels.Info, 70115, "sql => " + sql_cmd.CommandText);
try
{
if (sqlList[i].ToString().ToUpper().StartsWith("SELECT"))
{
//Expect back a string
sRtn = sql_cmd.ExecuteScalar().ToString();
if (sRtn.Equals(""))
{
Trans.Rollback();
sql_con.Close();
return(-1);
}
}
else
{
if (sql_cmd.CommandText.Contains("<Rtn>"))
{
//substitute in sRtn Value
sql_cmd.CommandText = sql_cmd.CommandText.Replace("<Rtn>", sRtn);
}
//Expect back # rows modified
if (sql_cmd.ExecuteNonQuery() < 0)
{
Trans.Rollback();
sql_con.Close();
return(-1);
}
}
}
catch (System.Data.Common.DbException dbx)
{
Trans.Rollback();
needDb = DBMain.DBExceptionHandler(dbx, sql_cmd.CommandText);
sql_con.Close();
return(-1);
}
}
}
Trans.Commit();
sql_con.Close();
Int32.TryParse(sRtn, out result);
return(result);
}
catch (Exception caught)
{
if (sql_cmd != null)
{
DBMain.AltLog(LogLevels.Warning, 70107, "ExecuteTransactionID '" + caught.Message + "' " + sql_cmd);
}
if (Trans != null)
{
Trans.Rollback();
}
if (sql_con != null)
{
sql_con.Close();
}
return(-1);
}
}