public int ExecuteNonSPQuery(string _sql, List <SQLParam> _parameters)
{
int _retValue = -1;
SAConnection _conn = null;
try
{
_conn = this.CreateConnection();
SACommand _cmd = this.CreateCommand(CommandType.Text, _conn, _sql, _parameters);
_retValue = _cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string _err = string.Format("SQLAnywhereProvider.ExecuteNonSPQuery error: {0}", ex);
throw new Exception(_err);
}
finally
{
this.Dispose(_conn);
}
return(_retValue);
}
public void ExecuteSP(string procedureName)
{
SACommand cmd = new SACommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
cmd.ExecuteNonQuery();
_parameterCollection = cmd.Parameters;
cmd.Dispose();
if (this.AutoCloseConnection)
{
this.Disconnect();
}
}
/// <summary>
/// Execute an insert, update, delete with OdbcParameters. It returns with
/// </summary>
/// <param name="CommandName">Parameters</param>
/// <param name="cmdType">type of the command</param>
/// <param name="pars">Parametes</param>
/// <returns></returns>
public bool ExecuteNonQuery(string CommandName, CommandType cmdType, SAParameter[] pars)
{
SACommand cmd = null;
int res = 0;
cmd = _connection.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(pars);
try
{
_connection.Open();
res = cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
_connection.Close();
}
if (res >= 1)
{
return(true);
}
return(false);
}
esDataResponse IDataProvider.ExecuteNonQuery(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
cmd = new SACommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
if (request.Parameters != null)
{
Shared.AddParameters(cmd, request);
}
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
response.RowsEffected = cmd.ExecuteNonQuery();
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return(response);
}
public void Connect()
{
if (_connection != null)
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
}
else
{
if (_connectionString != String.Empty)
{
StringCollection initKeys = new StringCollection();
// ToDo - Figure this out
initKeys.AddRange(new string[]
{
"ARITHABORT", "ANSI_NULLS", "ANSI_WARNINGS", "ARITHIGNORE", "ANSI_DEFAULTS",
"ANSI_NULL_DFLT_OFF", "ANSI_NULL_DFLT_ON", "ANSI_PADDING", "ANSI_WARNINGS"
});
StringBuilder initStatements = new StringBuilder();
StringBuilder connectionString = new StringBuilder();
Hashtable attribs = this.ParseConfigString(_connectionString);
foreach (string key in attribs.Keys)
{
if (initKeys.Contains(key.Trim().ToUpper()))
{
initStatements.AppendFormat("SET {0} {1};", key, attribs[key]);
}
else if (key.Trim().Length > 0)
{
connectionString.AppendFormat("{0}={1};", key, attribs[key]);
}
}
_connection = new SAConnection(connectionString.ToString());
_connection.Open();
if (initStatements.Length > 0)
{
SACommand cmd = new SACommand();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = initStatements.ToString();
cmd.Connection = _connection;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
else
{
throw new InvalidOperationException(
"You must set a connection object or specify a connection string before calling Connect.");
}
}
}
public int Update_Fee_Pkg_No(string case_no, string feePkgNo)
{
SACommand myCommand = myConnection.CreateCommand();
myCommand.CommandText =
$@"UPDATE user_case_data
SET Fee_Pkg_No = {feePkgNo}
where casenum = {case_no}";
int recordsAffected = myCommand.ExecuteNonQuery();
return(recordsAffected);
}
// Execute a SQL statement & return the connection
// Return value: true if successful
static bool ExecuteSql(bool ReturnConn, string SqlStatement, bool LogErrMsg,
string table, string CallingClass, string CallingFunction,
out SAConnection _conn)
{
bool RetVal = true;
_conn = null;
try
{
if (OpenDB(CallingClass, CallingFunction, out _conn))
{
SACommand cmd = new SACommand(SqlStatement, _conn);
cmd.ExecuteNonQuery();
}
else
{
RetVal = false;
}
}
catch (Exception ex)
{
RetVal = false;
if (LogErrMsg)
{
string err = String.Format(StringTable.GetString("DatabaseError"),
table,
ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, CallingClass, CallingFunction, err);
}
}
finally
{
if (!ReturnConn && _conn != null)
{
_conn.Close();
}
}
return(RetVal);
}
protected int ExecuteNonQuery(SACommand cmd)
{
int rc = -1;
int retries = -1;
bool retryFlag = true;
while (retryFlag && retries < 1200)
{
try
{
lock (BaseTableAdapter.conn)
{
if (cmd.Connection.State == ConnectionState.Open)
{
_log.Log(String.Format("ExecuteNonQuery - {0}", cmd.CommandText), "DB", 5);
cmd.Transaction = _trans;
rc = cmd.ExecuteNonQuery();
}
else
{
_log.Log(String.Format("ExecuteNonQuery Error connection not open - {0}", cmd.CommandText), "DB", 5);
}
}
return(rc);
}
catch (SAException ex)
{
retryFlag = ShouldRetry(ex, cmd);
if (!retryFlag)
{
rc = -1;
throw(ex);
}
string logoutput = String.Format("----ExecuteNonQuery----\r\n{0}\r\n{1}", ex.Message, ex.StackTrace);
_log.Log(logoutput, "DB", 3);
_log.Log(logoutput);
}
}
return(rc);
}
public void ExecuteSql(string sql)
{
SACommand cmd = new SACommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = sql;
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.Dispose();
if (this.AutoCloseConnection)
{
this.Disconnect();
}
}
public static bool ExecuteSql(string sql, bool throwException, params SAParameter[] list)
{
try
{
using (SAConnection con = MainClass.GetConnection())
{
con.Open();
using (SACommand cmd = new SACommand(sql, con))
{
cmd.Parameters.AddRange(list);
cmd.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception ex)
{
if (throwException)
{
throw ex;
}
}
return(false);
}
public void BasicAdoCode()
{
var connection = ConnectionFactory.GetConnection();
using (var cmd = new SACommand("WithOutParams", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
SAParameter param1 = new SAParameter("out1", SADbType.Integer);
param1.Direction = ParameterDirection.InputOutput;
param1.Value = 9;
cmd.Parameters.Add(param1);
SAParameter param2 = new SAParameter("out2", SADbType.Integer);
param1.Direction = ParameterDirection.InputOutput;
param2.Value = 9;
cmd.Parameters.Add(param2);
cmd.ExecuteNonQuery();
Console.WriteLine(param1.Value);
Console.WriteLine(param2.Value);
}
}
protected int ExecuteNonQuery(SACommand cmd)
{
int rc = -1;
int retries = -1;
bool retryFlag = true;
while (retryFlag && retries < 1200)
{
try
{
lock (BaseTableAdapter.conn)
{
if (cmd.Connection.State == ConnectionState.Open)
{
_log.Log(String.Format("ExecuteNonQuery - {0}", cmd.CommandText), "DB", 5);
cmd.Transaction = _trans;
rc = cmd.ExecuteNonQuery();
}
else
{
_log.Log(String.Format("ExecuteNonQuery Error connection not open - {0}", cmd.CommandText), "DB", 5);
}
}
return rc;
}
catch (SAException ex)
{
retryFlag = ShouldRetry(ex, cmd);
if (!retryFlag)
{
rc = -1;
throw(ex);
}
string logoutput = String.Format("----ExecuteNonQuery----\r\n{0}\r\n{1}", ex.Message, ex.StackTrace);
_log.Log(logoutput, "DB", 3);
_log.Log(logoutput);
}
}
return rc;
}
esDataResponse IDataProvider.ExecuteNonQuery(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
cmd = new SACommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteNonQuery", System.Environment.StackTrace))
{
try
{
response.RowsEffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
response.RowsEffected = cmd.ExecuteNonQuery();
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
esDataResponse IDataProvider.ExecuteNonQuery(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
cmd = new SACommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
response.RowsEffected = cmd.ExecuteNonQuery();
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}