/// <summary>
/// TODO: fill in whether it should retry.
///
/// </summary>
/// <param name="ulex"></param>
/// <param name="cmd"></param>
/// <returns></returns>
protected bool ShouldRetry(SAException ulex, SACommand cmd)
{
return false;
}
public SADataReader ExecuteSPReader(string procedureName)
{
SADataReader reader;
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);
CommandBehavior behavior = CommandBehavior.Default;
if (this.AutoCloseConnection)
{
behavior = behavior | CommandBehavior.CloseConnection;
}
if (_isSingleRow)
{
behavior = behavior | CommandBehavior.SingleRow;
}
reader = cmd.ExecuteReader(behavior);
_parameterCollection = cmd.Parameters;
cmd.Dispose();
return(reader);
}
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();
}
}
public void ExecuteSPDataSet(ref DataSet dataSet, string procedureName, string tableName)
{
SACommand cmd = new SACommand();
this.Connect();
SADataAdapter da = new SADataAdapter();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
da.SelectCommand = cmd;
da.Fill(dataSet, tableName);
_parameterCollection = cmd.Parameters;
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection)
{
this.Disconnect();
}
}
public void ExecuteSqlDataSet(ref DataSet dataSet, string sql, string tableName)
{
SACommand cmd = new SACommand();
this.Connect();
SADataAdapter da = new SADataAdapter();
cmd.CommandTimeout = this.CommandTimeout;
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
da.SelectCommand = cmd;
da.Fill(dataSet, tableName);
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection)
{
this.Disconnect();
}
}
public DataSet ExecuteSqlDataSet(string sql)
{
SACommand cmd = new SACommand();
this.Connect();
SADataAdapter da = new SADataAdapter();
DataSet ds = new DataSet();
cmd.CommandTimeout = this.CommandTimeout;
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
da.SelectCommand = cmd;
da.Fill(ds);
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection)
{
this.Disconnect();
}
return(ds);
}
public SADataReader ExecuteSAReader(string sql)
{
SADataReader reader;
SACommand cmd = new SACommand();
this.Connect();
// Order of commands is important for parameer substitution
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = this.CommandTimeout;
this.CopyParameters(cmd);
cmd.CommandText = sql;
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
CommandBehavior behavior = CommandBehavior.Default;
if (this.AutoCloseConnection)
{
behavior = behavior | CommandBehavior.CloseConnection;
}
if (_isSingleRow)
{
behavior = behavior | CommandBehavior.SingleRow;
}
reader = cmd.ExecuteReader(behavior);
cmd.Dispose();
return(reader);
}
private void CopyParameters(SACommand command)
{
for (int i = 0; i < _parameters.Count; i++)
{
command.Parameters.Add(_parameters[i]);
}
}
static public SACommand BuildStoredProcUpdateCommand(esDataRequest request)
{
Dictionary <string, SAParameter> types = Cache.GetParameters(request);
SACommand cmd = new SACommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spUpdate);
PopulateStoredProcParameters(cmd, request);
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsComputed || col.IsConcurrency || col.IsEntitySpacesConcurrency)
{
SAParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.InputOutput;
}
}
return(cmd);
}
public JsonResult mostrarListaPartidos()
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("listarPartidos", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <Partido> listaPartidos = new List <Partido>();
while (reader.Read())
{
listaPartidos.Add(new Partido {
id = reader.GetInt32(0), nome = reader.GetString(1)
});
}
reader.Close();
conn.Close();
return(Json(listaPartidos));
}
catch (SAException ex)
{
conn.Close();
return(Json(false));
}
}
public SACommand CreateAddProductCommand(SAConnection conn, SATransaction transaction)
{
var cmd = new SACommand(_sql, conn, transaction);
CreateParameters(cmd);
return cmd;
}
public DataTable ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SAParameter[] param)
{
SACommand cmd = null;
DataTable table = new DataTable();
cmd = _connection.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(param);
try
{
_connection.Open();
SADataAdapter da = null;
using (da = new SADataAdapter(cmd))
{
da.Fill(table);
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
_connection.Close();
}
return(table);
}
/// <summary>
/// Calls a stored procedure if needed.
/// </summary>
/// <param name="statementProcedureName">Stored procedure name</param>
/// <param name="statementProcList">List of parameters for a stored procedure</param>
public override void CallStoredProcedure(string statementProcedureName, IList <string> statementProcList)
{
string sqlString = "";
string sqlParams = "";
foreach (string param in statementProcList)
{
if (!string.IsNullOrEmpty(sqlString))
{
sqlString = sqlString + ",";
}
sqlParams = sqlParams + "'" + param + "'";
}
sqlString = "call " + statementProcedureName + " (" + sqlParams + ")";
try
{
SACommand command = CommandTransaction(sqlString);
if (command != null)
{
command.ExecuteScalar();
}
}
catch (System.Exception e)
{
Rollback();
string msg = "DataAccessLayer Error: " + ShowMessage(e.Message);
throw new System.Exception(msg);
}
finally
{
CloseInTransaction();
}
}
public override DataTable ExecuteSelectCommand(string CommandName, CommandType cmdType)
{
SACommand cmd = null;
DataTable table = new DataTable();
cmd = _connection.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
try
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
SADataAdapter da = null;
using (da = new SADataAdapter(cmd))
{
da.Fill(table);
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
_connection.Close();
}
return(table);
}
private SADataAdapter CreateDataAdapter(string sqlQuery)
{
SADataAdapter dta = null;
lock (dataAdapterLock)
{
try
{
if (_transaction == null)
{
Open();
_command = new SACommand(sqlQuery, _connection);
}
else
{
_command = new SACommand(sqlQuery, _connection, _transaction);
}
dta = new SADataAdapter(_command);
}
catch (System.Exception e)
{
if (_transaction != null)
{
_transaction.Rollback();
}
string msg = "Loading dataset error :" + e.Message;
throw new System.Exception(msg);
}
}
return(dta);
}
public JsonResult guardarPartido(string nomePartido)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL guardarPartido(\"nomePartido\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Text;
param.Direction = ParameterDirection.Input;
param.Value = nomePartido;
cmd.Parameters.Add(param);
SADataReader reader = cmd.ExecuteReader();
reader.Close();
conn.Close();
return(Json(true));
}
catch (SAException ex)
{
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
conn.Close();
return(Json(false));
}
}
public bool inserirCandidatoEleicao(int idEleicao, int idCandidato)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL inserirCandidatoEleicao(\"idCandidato\" = ?, \"idEleicao\"= ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Integer;
param1.Direction = ParameterDirection.Input;
param1.Value = idCandidato;
cmd.Parameters.Add(param1);
SAParameter param2 = cmd.CreateParameter();
param2.SADbType = SADbType.Integer;
param2.Direction = ParameterDirection.Input;
param2.Value = idEleicao;
cmd.Parameters.Add(param2);
SADataReader reader = cmd.ExecuteReader();
return(true);
}
catch (Exception ex)
{
conn.Close();
return(false);
}
}
static public void PopulateStoredProcParameters(SACommand cmd, esDataRequest request)
{
Dictionary <string, SAParameter> types = Cache.GetParameters(request);
SAParameter p;
foreach (esColumnMetadata col in request.Columns)
{
p = types[col.Name];
p = CloneParameter(p);
p.SourceVersion = DataRowVersion.Current;
if (p.SADbType == SADbType.TimeStamp)
{
p.Direction = ParameterDirection.InputOutput;
}
if (col.IsComputed && col.CharacterMaxLength > 0)
{
p.Size = (int)col.CharacterMaxLength;
}
cmd.Parameters.Add(p);
}
}
public ActionResult electionMain()
{
if (Session["UserID"] != null)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
conn.Open();
SACommand cmd = new SACommand("listarCandidatos", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <Candidato> listaCandidatos = new List <Candidato>();
while (reader.Read())
{
listaCandidatos.Add(new Candidato {
id = reader.GetInt32(0), nomeCandidato = reader.GetString(1), nomePartido = reader.GetString(2)
});
}
reader.Close();
conn.Close();
ViewBag.listaCandidatos = listaCandidatos;
return(View());
}
else
{
return(RedirectToAction("Login"));
}
}
public JsonResult listarCaderno()
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("listarCaderno", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <cadernoEleitoral> cadernoEleitorals = new List <cadernoEleitoral>();
while (reader.Read())
{
cadernoEleitorals.Add(new cadernoEleitoral {
id = Int32.Parse(reader.GetString(0)), periodo = reader.GetString(1)
});
}
reader.Close();
conn.Close();
return(Json(cadernoEleitorals));
}
catch (SAException ex)
{
conn.Close();
return(Json(false));
}
}
static public SACommand BuildDynamicDeleteCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary <string, SAParameter> types = Cache.GetParameters(request);
SACommand cmd = new SACommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
string sql = "DELETE FROM " + CreateFullName(request) + " ";
string comma = String.Empty;
comma = String.Empty;
sql += " WHERE ";
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency)
{
SAParameter p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
sql += comma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = " AND ";
}
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return(cmd);
}
// Execute a SQL select statement using an existing DB connection
internal static bool ExecuteSelect(SAConnection _conn, string SqlStatement, bool ErrMsg,
string table, string CallingClass, string CallingFunction,
out SADataReader myDataReader)
{
bool RetVal = true;
myDataReader = null;
try
{
SACommand cmd = new SACommand(SqlStatement, _conn);
myDataReader = cmd.ExecuteReader();
}
catch (Exception ex)
{
RetVal = false;
if (ErrMsg)
{
string err = String.Format(StringTable.GetString("DatabaseError"),
table, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, CallingClass, CallingFunction, err);
}
}
return(RetVal);
}
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 DataTable GetData(CommandType type, string sql, List <SQLParam> parms)
{
DataTable dt = new DataTable();
SAConnection _conn = null;
try
{
_conn = this.CreateConnection();
SACommand _cmd = this.CreateCommand(type, _conn, sql, parms);
SADataAdapter _adp = new SADataAdapter(_cmd);
_adp.Fill(dt);
}
catch (Exception ex)
{
string _err = string.Format("SQLAnywhereProvider.ExecuteDataSet error: {0}", ex);
throw new Exception(_err);
}
finally
{
this.Dispose(_conn);
}
return(dt);
}
public DataSet ExecuteDataSet(string _procName, List <SQLParam> _parameters)
{
DataSet _ds = new DataSet();
SAConnection _conn = null;
try
{
_conn = this.CreateConnection();
SACommand _cmd = this.CreateCommand(CommandType.Text, _conn, _procName, _parameters);
SADataAdapter _adp = new SADataAdapter(_cmd);
_adp.Fill(_ds);
}
catch (Exception ex)
{
string _err = string.Format("SQLAnywhereProvider.ExecuteDataSet error: {0}", ex);
throw new Exception(_err);
}
finally
{
this.Dispose(_conn);
}
return(_ds);
}
/// <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);
}
public JsonResult apagarEleicao(int idEleicao)
{
// CALL "dev"."listarEleitores"("idCaderno" = 4)
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL apagarEleicao(\"idEleicao\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Integer;
param.Direction = ParameterDirection.Input;
param.Value = idEleicao;
cmd.Parameters.Add(param);
SADataReader reader = cmd.ExecuteReader();
reader.Close();
conn.Close();
return(Json(true));
}
catch (SAException ex)
{
conn.Close();
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
return(Json(false));
}
}
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.");
}
}
}
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);
}
esDataResponse IDataProvider.esLoadDataTable(esDataRequest request)
{
esDataResponse response = new esDataResponse();
try
{
switch (request.QueryType)
{
case esQueryType.StoredProcedure:
response = LoadDataTableFromStoredProcedure(request);
break;
case esQueryType.Text:
response = LoadDataTableFromText(request);
break;
case esQueryType.DynamicQuery:
response = new esDataResponse();
SACommand cmd = QueryBuilder.PrepareCommand(request);
LoadDataTableFromDynamicQuery(request, response, cmd);
break;
case esQueryType.DynamicQueryParseOnly:
response = new esDataResponse();
SACommand cmd1 = QueryBuilder.PrepareCommand(request);
response.LastQuery = cmd1.CommandText;
break;
#if (LINQ)
case esQueryType.IQueryable:
response = new esDataResponse();
LoadDataTableForLinqToSql(request, response);
break;
#endif
case esQueryType.ManyToMany:
response = LoadManyToMany(request);
break;
default:
break;
}
}
catch (Exception ex)
{
response.Exception = ex;
}
return(response);
}
static private void CleanupCommand(SACommand cmd)
{
if (cmd != null && cmd.Connection != null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
}
private void tabControl1_Selected(object sender, TabControlEventArgs e)
{
// Move the input focus to the query builder.
// This will fire Leave event in the text box and update the query builder
// with modified query text.
queryBuilder1.Focus();
Application.DoEvents();
// Try to execute the query using current database connection:
if (e.TabPage == tabPageData)
{
dataGridView1.DataSource = null;
if (queryBuilder1.MetadataProvider != null && queryBuilder1.MetadataProvider.Connected)
{
SACommand command = (SACommand)queryBuilder1.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder1.SQL;
// handle the query parameters
if (queryBuilder1.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder1.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder1.Parameters[i].FullName))
{
SAParameter parameter = new SAParameter();
parameter.ParameterName = queryBuilder1.Parameters[i].FullName;
parameter.DbType = queryBuilder1.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
SADataAdapter adapter = new SADataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
}
}
public static void AddParameters(SACommand cmd, tgDataRequest request)
{
if (request.QueryType == tgQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}"))
{
int i = 0;
string token = String.Empty;
string sIndex = String.Empty;
string param = String.Empty;
foreach (tgParameter esParam in request.Parameters)
{
sIndex = i.ToString();
token = '{' + sIndex + '}';
param = "?"; // Delimiters.Param + "p" + sIndex;
request.QueryText = request.QueryText.Replace(token, param);
i++;
cmd.Parameters.AddWithValue(Delimiters.Param + esParam.Name, esParam.Value);
}
}
else
{
SAParameter param;
foreach (tgParameter esParam in request.Parameters)
{
param = cmd.Parameters.AddWithValue(Delimiters.Param + esParam.Name, esParam.Value);
switch (esParam.Direction)
{
case tgParameterDirection.InputOutput:
param.Direction = ParameterDirection.InputOutput;
break;
case tgParameterDirection.Output:
param.Direction = ParameterDirection.Output;
param.DbType = esParam.DbType;
param.Size = esParam.Size;
param.Scale = esParam.Scale;
param.Precision = esParam.Precision;
break;
case tgParameterDirection.ReturnValue:
param.Direction = ParameterDirection.ReturnValue;
break;
// The default is ParameterDirection.Input;
}
}
}
}
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);
}
}
// If it's an Insert we fetch the @@Identity value and stuff it in the proper column
protected static void OnRowUpdated(object sender, SARowUpdatedEventArgs e)
{
try
{
PropertyCollection props = e.Row.Table.ExtendedProperties;
if (props.ContainsKey("props"))
{
props = (PropertyCollection)props["props"];
}
if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update))
{
esDataRequest request = props["esDataRequest"] as esDataRequest;
esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"];
if (e.StatementType == StatementType.Insert)
{
if (props.Contains("AutoInc"))
{
string autoInc = props["AutoInc"] as string;
SACommand cmd = new SACommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT @@IDENTITY;";
cmd.CommandType = CommandType.Text;
object o = null;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
o = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
o = cmd.ExecuteScalar();
}
if (o != null)
{
packet.CurrentValues[autoInc] = o;
e.Row[autoInc] = o;
}
}
if (props.Contains("EntitySpacesConcurrency"))
{
string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string;
packet.CurrentValues[esConcurrencyColumn] = 1;
}
}
//-------------------------------------------------------------------------------------------------
// Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way
//-------------------------------------------------------------------------------------------------
if (props.Contains("Defaults"))
{
// Build the Where parameter and parameters
SACommand cmd = new SACommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
string select = (string)props["Defaults"];
string[] whereParameters = ((string)props["Where"]).Split(',');
string comma = String.Empty;
string where = String.Empty;
int i = 1;
foreach (string parameter in whereParameters)
{
SAParameter p = new SAParameter(":p" + i++.ToString(), e.Row[parameter]);
cmd.Parameters.Add(p);
where += comma + "[" + parameter + "]=" + p.ParameterName;
comma = " AND ";
}
// Okay, now we can execute the sql and get any values that have defaults that were
// null at the time of the insert and/or our timestamp
cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";";
SADataReader rdr = null;
try
{
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
}
if (rdr.Read())
{
select = select.Replace("[", String.Empty).Replace("]", String.Empty);
string[] selectCols = select.Split(',');
for (int k = 0; k < selectCols.Length; k++)
{
packet.CurrentValues[selectCols[k]] = rdr.GetValue(k);
}
}
}
finally
{
// Make sure we close the reader no matter what
if (rdr != null) rdr.Close();
}
}
if (e.StatementType == StatementType.Update)
{
string colName = props["EntitySpacesConcurrency"] as string;
object o = e.Row[colName];
switch (Type.GetTypeCode(o.GetType()))
{
case TypeCode.Int16: packet.CurrentValues[colName] = ((System.Int16)o) + 1; break;
case TypeCode.Int32: packet.CurrentValues[colName] = ((System.Int32)o) + 1; break;
case TypeCode.Int64: packet.CurrentValues[colName] = ((System.Int64)o) + 1; break;
case TypeCode.UInt16: packet.CurrentValues[colName] = ((System.UInt16)o) + 1; break;
case TypeCode.UInt32: packet.CurrentValues[colName] = ((System.UInt32)o) + 1; break;
case TypeCode.UInt64: packet.CurrentValues[colName] = ((System.UInt64)o) + 1; break;
}
}
}
}
catch { }
}
static private esDataResponse LoadDataTableFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new SACommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
SADataAdapter da = new SADataAdapter();
cmd.CommandText = request.QueryText;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
esDataResponse IDataProvider.ExecuteScalar(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;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteScalar", System.Environment.StackTrace))
{
try
{
response.Scalar = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
response.Scalar = cmd.ExecuteScalar();
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
public virtual IList<AbstractBusinessObject> Delete(string tablename, Type type, string criteria,
Hashtable parameters)
{
SACommand selectcmd = new SACommand("SELECT * from " + tablename + " where " + criteria);
SACommand deletecmd = new SACommand ("DELETE from " + tablename + " where " + criteria);
selectcmd.Connection = conn;
deletecmd.Connection = conn;
//string name, value;
if (parameters != null)
{
IDictionaryEnumerator ps = parameters.GetEnumerator();
while (ps.MoveNext())
{
if (ps.Key is string)
{
selectcmd.Parameters.Add((string)ps.Key, ps.Value);
deletecmd.Parameters.Add((string)ps.Key, ps.Value);
}
}
}
//for (int i=0; i < parameters.Count; i++)
//{
// name = parameters.GetKey(i);
// value = parameters[name];
// selectcmd.Parameters.Add(name, value);
// deletecmd.Parameters.Add(name, value);
//}
IList<AbstractBusinessObject> rc = Query(tablename, type, selectcmd);
if (selectcmd != null)
selectcmd.Dispose();
ExecuteNonQuery(deletecmd);
if (deletecmd != null)
deletecmd.Dispose();
return rc;
}
protected Object ExecuteScalar(SACommand cmd)
{
Object rc = null;
int retries = -1;
bool retryFlag = true;
while (retryFlag && retries < 1200)
{
try
{
lock (BaseTableAdapter.conn)
{
if (cmd.Connection.State == ConnectionState.Open)
{
_log.Log(String.Format("ExecuteScalar - {0}", cmd.CommandText), "DB", 5);
cmd.Transaction = _trans;
rc = cmd.ExecuteScalar();
}
else
{
_log.Log(String.Format("ExecuteScalar Error connection not open - {0}", cmd.CommandText), "DB", 5);
}
}
return rc;
}
catch (SAException ex)
{
retryFlag = ShouldRetry(ex, cmd);
string logoutput = String.Format("---- ExecuteScalar ----\r\n{0}\r\n{1}", ex.Message, ex.StackTrace);
_log.Log(logoutput, "DB", 3);
_log.Log(logoutput);
retries = retries + 1;
}
}
return rc;
}
private static esDataResponse LoadDataTableFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new SACommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
SADataAdapter da = new SADataAdapter();
cmd.CommandText = request.QueryText;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromText", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
private void CreateParameters(SACommand cmd)
{
cmd.Parameters.Add(new SAParameter("id", SADbType.Integer));
cmd.Parameters.Add(new SAParameter("code", SADbType.NVarChar, 15));
cmd.Parameters.Add(new SAParameter("name", SADbType.NVarChar, 255));
cmd.Parameters.Add(new SAParameter("mft", SADbType.NVarChar, 56));
cmd.Parameters.Add(new SAParameter("size", SADbType.NVarChar, 15));
cmd.Parameters.Add(new SAParameter("discontinued", SADbType.Bit));
cmd.Parameters.Add(new SAParameter("barcode", SADbType.NVarChar, 56));
}
public static SACommand BuildStoredProcInsertCommand(tgDataRequest request)
{
Dictionary<string, SAParameter> types = Cache.GetParameters(request);
SACommand cmd = new SACommand();
if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spInsert);
PopulateStoredProcParameters(cmd, request);
foreach (tgColumnMetadata col in request.Columns)
{
if (col.HasDefault &&
(col.Default.ToLower().Contains("newid") || col.Default.ToLower().Contains("newsequentialid")))
{
// They could pre-assign this even though it has a default
SAParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.InputOutput;
}
if (col.IsComputed || col.IsAutoIncrement || col.IsEntitySpacesConcurrency)
{
SAParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.InputOutput;
}
}
return cmd;
}
esDataResponse IDataProvider.ExecuteReader(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;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
cmd.Connection = new SAConnection(request.ConnectionString);
cmd.Connection.Open();
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
public static SACommand BuildDynamicUpdateCommand(tgDataRequest request, List<string> modifiedColumns)
{
string where = String.Empty;
string scomma = String.Empty;
string wcomma = String.Empty;
string defaults = String.Empty;
string defaultsWhere = String.Empty;
string defaultsComma = String.Empty;
string defaultsWhereComma = String.Empty;
List<SAParameter> whereParams = new List<SAParameter>();
string sql = "UPDATE " + CreateFullName(request) + " SET ";
PropertyCollection props = new PropertyCollection();
Dictionary<string, SAParameter> types = Cache.GetParameters(request);
SACommand cmd = new SACommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
tgColumnMetadataCollection cols = request.Columns;
foreach (tgColumnMetadata col in cols)
{
bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name);
if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency))
{
SAParameter p = CloneParameter(types[col.Name]);
cmd.Parameters.Add(p);
sql += scomma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
scomma = ", ";
}
else if (col.IsAutoIncrement)
{
// Nothing to do but leave this here
}
else if (col.IsConcurrency)
{
SAParameter p = CloneParameter(types[col.Name]);
p.SourceVersion = DataRowVersion.Original;
//cmd.Parameters.Add(p);
whereParams.Add(p);
where += wcomma;
where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
wcomma = " AND ";
}
else if (col.IsEntitySpacesConcurrency)
{
props["EntitySpacesConcurrency"] = col.Name;
SAParameter p = CloneParameter(types[col.Name]);
p.SourceVersion = DataRowVersion.Original;
//cmd.Parameters.Add(p);
whereParams.Add(p);
sql += scomma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " + 1";
scomma = ", ";
where += wcomma;
where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
wcomma = " AND ";
}
else if (col.IsComputed)
{
// Do nothing but leave this here
}
else if (cols.IsSpecialColumn(col))
{
// Do nothing but leave this here
}
else if (col.HasDefault)
{
// defaults += defaultsComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
// defaultsComma = ",";
}
if (col.IsInPrimaryKey)
{
SAParameter p = CloneParameter(types[col.Name]);
p.SourceVersion = DataRowVersion.Original;
//cmd.Parameters.Add(p);
whereParams.Add(p);
where += wcomma;
where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
wcomma = " AND ";
defaultsWhere += defaultsWhereComma + col.Name;
defaultsWhereComma = ",";
}
}
#region Special Columns
if (cols.DateModified != null && cols.DateModified.IsServerSide)
{
sql += scomma;
sql += Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["DateModified.ServerSideText"];
scomma = ", ";
defaults += defaultsComma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose;
defaultsComma = ",";
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
sql += scomma;
sql += Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"];
scomma = ", ";
defaults += defaultsComma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose;
defaultsComma = ",";
}
#endregion
if (defaults.Length > 0)
{
props["Defaults"] = defaults;
props["Where"] = defaultsWhere;
}
if (whereParams.Count > 0)
{
foreach (SAParameter p in whereParams)
{
cmd.Parameters.Add(p);
}
}
sql += " WHERE " + where + ";";
request.Properties = props;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
public static SACommand BuildStoredProcDeleteCommand(tgDataRequest request)
{
Dictionary<string, SAParameter> types = Cache.GetParameters(request);
SACommand cmd = new SACommand();
if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spDelete);
SAParameter p;
foreach (tgColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey)
{
p = types[col.Name];
p = CloneParameter(p);
p.SourceVersion = DataRowVersion.Current;
cmd.Parameters.Add(p);
}
else if (col.IsConcurrency || col.IsEntitySpacesConcurrency)
{
p = types[col.Name];
p = CloneParameter(p);
p.SourceVersion = DataRowVersion.Original;
cmd.Parameters.Add(p);
}
}
return cmd;
}
public static SACommand BuildDynamicInsertCommand(tgDataRequest request, List<string> modifiedColumns)
{
string sql = String.Empty;
string defaults = String.Empty;
string into = String.Empty;
string values = String.Empty;
string comma = String.Empty;
string defaultComma = String.Empty;
string where = String.Empty;
string whereComma = String.Empty;
PropertyCollection props = new PropertyCollection();
Dictionary<string, SAParameter> types = Cache.GetParameters(request);
SACommand cmd = new SACommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
tgColumnMetadataCollection cols = request.Columns;
foreach (tgColumnMetadata col in cols)
{
bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name);
if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency))
{
SAParameter p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
values += comma + p.ParameterName;
comma = ", ";
}
else if (col.IsAutoIncrement)
{
props["AutoInc"] = col.Name;
props["Source"] = request.ProviderMetadata.Source;
}
else if (col.IsConcurrency)
{
props["Timestamp"] = col.Name;
props["Source"] = request.ProviderMetadata.Source;
}
else if (col.IsEntitySpacesConcurrency)
{
props["EntitySpacesConcurrency"] = col.Name;
SAParameter p = types[col.Name];
into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
values += comma + "1";
comma = ", ";
SAParameter clone = CloneParameter(p);
clone.Value = 1;
cmd.Parameters.Add(clone);
}
else if (col.IsComputed)
{
// Do nothing but leave this here
}
else if (cols.IsSpecialColumn(col))
{
// Do nothing but leave this here
}
else if (col.HasDefault)
{
defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
defaultComma = ",";
}
if (col.IsInPrimaryKey)
{
where += whereComma + col.Name;
whereComma = ",";
}
}
#region Special Columns
if (cols.DateAdded != null && cols.DateAdded.IsServerSide)
{
into += comma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose;
values += comma + request.ProviderMetadata["DateAdded.ServerSideText"];
comma = ", ";
defaults += defaultComma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose;
defaultComma = ",";
}
if (cols.DateModified != null && cols.DateModified.IsServerSide)
{
into += comma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose;
values += comma + request.ProviderMetadata["DateModified.ServerSideText"];
comma = ", ";
defaults += defaultComma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose;
defaultComma = ",";
}
if (cols.AddedBy != null && cols.AddedBy.IsServerSide)
{
into += comma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose;
values += comma + request.ProviderMetadata["AddedBy.ServerSideText"];
comma = ", ";
defaults += defaultComma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose;
defaultComma = ",";
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
into += comma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose;
values += comma + request.ProviderMetadata["ModifiedBy.ServerSideText"];
comma = ", ";
defaults += defaultComma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose;
defaultComma = ",";
}
#endregion
if (defaults.Length > 0)
{
comma = String.Empty;
props["Defaults"] = defaults;
props["Where"] = where;
}
sql += " INSERT INTO " + CreateFullName(request);
if (into.Length != 0)
{
sql += "(" + into + ") VALUES (" + values + ")";
}
else
{
sql += "DEFAULT VALUES";
}
request.Properties = props;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
public static SACommand BuildDynamicDeleteCommand(tgDataRequest request, tgEntitySavePacket packet)
{
Dictionary<string, SAParameter> types = Cache.GetParameters(request);
SACommand cmd = new SACommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string sql = "DELETE FROM " + CreateFullName(request) + " ";
string comma = String.Empty;
comma = String.Empty;
sql += " WHERE ";
foreach (tgColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency)
{
SAParameter p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
sql += comma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = " AND ";
}
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
static private esDataResponse LoadDataSetFromStoredProcedure(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
DataSet dataSet = new DataSet();
cmd = new SACommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
SADataAdapter da = new SADataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataSet);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.DataSet = dataSet;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
private static void CleanupCommand(SACommand cmd)
{
if (cmd != null && cmd.Connection != null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
}
public static SACommand BuildStoredProcUpdateCommand(tgDataRequest request)
{
Dictionary<string, SAParameter> types = Cache.GetParameters(request);
SACommand cmd = new SACommand();
if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spUpdate);
PopulateStoredProcParameters(cmd, request);
foreach (tgColumnMetadata col in request.Columns)
{
if (col.IsComputed || col.IsConcurrency || col.IsEntitySpacesConcurrency)
{
SAParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.InputOutput;
}
}
return cmd;
}
// This is used only to execute the Dynamic Query API
private static void LoadDataTableFromDynamicQuery(esDataRequest request, esDataResponse response, SACommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
SADataAdapter da = new SADataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromDynamicQuery", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
};
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
public static void GatherReturnParameters(SACommand cmd, tgDataRequest request, tgDataResponse response)
{
if (cmd.Parameters.Count > 0)
{
if (request.Parameters != null && request.Parameters.Count > 0)
{
response.Parameters = new tgParameters();
foreach (tgParameter esParam in request.Parameters)
{
if (esParam.Direction != tgParameterDirection.Input)
{
response.Parameters.Add(esParam);
SAParameter p = cmd.Parameters[Delimiters.Param + esParam.Name];
esParam.Value = p.Value;
}
}
}
}
}
private void SetAddCommandParameters(SACommand cmd, Product product)
{
cmd.Parameters[0].Value = product.ID;
cmd.Parameters[1].Value = product.ProductCode;
cmd.Parameters[2].Value = product.Name;
cmd.Parameters[3].Value = product.Manufacturer;
cmd.Parameters[4].Value = product.Size;
cmd.Parameters[5].Value = !product.Active;
cmd.Parameters[6].Value = product.BarCode;
}
public static void PopulateStoredProcParameters(SACommand cmd, tgDataRequest request)
{
Dictionary<string, SAParameter> types = Cache.GetParameters(request);
SAParameter p;
foreach (tgColumnMetadata col in request.Columns)
{
p = types[col.Name];
p = CloneParameter(p);
p.SourceVersion = DataRowVersion.Current;
if (p.SADbType == SADbType.TimeStamp)
{
p.Direction = ParameterDirection.InputOutput;
}
if (col.IsComputed && col.CharacterMaxLength > 0)
{
p.Size = (int)col.CharacterMaxLength;
}
cmd.Parameters.Add(p);
}
}
private static tgDataResponse LoadDataSetFromStoredProcedure(tgDataRequest request)
{
tgDataResponse response = new tgDataResponse();
SACommand cmd = null;
try
{
DataSet dataSet = new DataSet();
cmd = new SACommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
SADataAdapter da = new SADataAdapter();
da.SelectCommand = cmd;
try
{
tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromStoredProcedure", System.Environment.StackTrace))
{
try
{
da.Fill(dataSet);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataSet);
}
}
finally
{
tgTransactionScope.DeEnlist(da.SelectCommand);
}
response.DataSet = dataSet;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
private static esDataResponse LoadManyToMany(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new SACommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string mmQuery = request.QueryText;
string[] sections = mmQuery.Split('|');
string[] tables = sections[0].Split(',');
string[] columns = sections[1].Split(',');
string prefix = String.Empty;
if (request.Catalog != null || request.ProviderMetadata.Catalog != null)
{
prefix += Delimiters.TableOpen;
prefix += request.Catalog != null ? request.Catalog : request.ProviderMetadata.Catalog;
prefix += Delimiters.TableClose + ".";
}
if (request.Schema != null || request.ProviderMetadata.Schema != null)
{
prefix += Delimiters.TableOpen;
prefix += request.Schema != null ? request.Schema : request.ProviderMetadata.Schema;
prefix += Delimiters.TableClose + ".";
}
string table0 = prefix + Delimiters.TableOpen + tables[0] + Delimiters.TableClose;
string table1 = prefix + Delimiters.TableOpen + tables[1] + Delimiters.TableClose;
string sql = "SELECT * FROM " + table0 + " JOIN " + table1 + " ON " + table0 + ".[" + columns[0] + "] = ";
sql += table1 + ".[" + columns[1] + "] WHERE " + table1 + ".[" + sections[2] + "] = ?";
if (request.Parameters != null)
{
Shared.AddParameters(cmd, request);
}
SADataAdapter da = new SADataAdapter();
cmd.CommandText = sql;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadManyToMany", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
//string sql, SAParameterCollection criteria,
public virtual IList<AbstractBusinessObject> Query(string tablename, Type boType, SACommand cmd)
{
//SACommand objSql;
//SADataReader dataReader;
SADataAdapter adp = new SADataAdapter();
DataSet ds;
ArrayList abos = new ArrayList();
// Getting rowcount
//sql = "SELECT count(*) as Count FROM "+tablename+" where UserID = @userid";
//objSql = new SACommand(sql, conn);
//objSql.Parameters.AddWithValue("@userid", UserID);
//dataReader = ExecuteReader(objSql);
//dataReader.Read();
//int rowcount = dataReader.GetInt32(0);
//dataReader.Close();
//if (rowcount <= 0)
// return null;
if (BaseTableAdapter._trans != null)
cmd.Transaction = _trans;
try
{
// filling the DataSet with the DataAdapter
adp.TableMappings.Add("Table", tablename);
//add Parameters
cmd.CommandType = CommandType.Text;
adp.SelectCommand = cmd;
ds = new DataSet(tablename);
lock(BaseTableAdapter.conn)
adp.Fill(ds);
// Retrieve all the rows
DataRowCollection rows = ds.Tables[0].Rows;
if (rows.Count < 1)
return null;
// Loop through the Columns in the DataSet and map that to the properties of the class
IEnumerator columns = ds.Tables[0].Columns.GetEnumerator();
DataColumn datacolumn;
DataRow datarow;
string cname;
object cvalue;
ConstructorInfo cons = boType.GetConstructor(Type.EmptyTypes);
PropertyInfo[] props = boType.GetProperties();
//( rows.Count
_log.Log(String.Format("Querying cmd={0}", cmd.CommandText), "DB", 5 );
for (int r = 0; r < rows.Count; r++)
{
datarow = rows[r];
AbstractBusinessObject curr = (AbstractBusinessObject)cons.Invoke(null);
columns.Reset();
while (columns.MoveNext())
{
try
{
datacolumn = (DataColumn)columns.Current;
cname = datacolumn.ColumnName;
for (int i = 0; i < props.Length; i++)
{
if (props[i].Name.ToLower() == cname.ToLower())
{
cvalue = Convert.ChangeType(datarow[datacolumn], props[i].PropertyType);
props[i].SetValue(curr, cvalue, null);
_log.Log(String.Format("\tName={0} Value={1}", cname, cvalue), "DB", 5);
break; // break for loop
}
}
}
catch (InvalidCastException ivce)
{
// go to next column
}
}
abos.Add(curr);
}
}
catch (Exception ex)
{
string logoutput = String.Format("----Error in BaseTableAdapter, Query----\r\n{0}\r\n{1}", ex.Message, ex.StackTrace);
_log.Log(logoutput, "DB", 3);
_log.Log(logoutput);
}
finally
{
adp.Dispose();
}
AbstractBusinessObject[] rc = (AbstractBusinessObject[])abos.ToArray(boType);
return rc;
}
esDataResponse IDataProvider.ExecuteReader(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;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
cmd.Connection = new SAConnection(request.ConnectionString);
cmd.Connection.Open();
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteReader", System.Environment.StackTrace))
{
try
{
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
esDataResponse IDataProvider.ExecuteScalar(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;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
response.Scalar = cmd.ExecuteScalar();
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableFromDynamicQuery(esDataRequest request, esDataResponse response, SACommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
SADataAdapter da = new SADataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
};
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
