private void button2_Click_2(object sender, EventArgs e)
{
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataSet tester
{
DTAcctLog.Rows.Clear(); //doesn't affect the update.
conn.Open(); //open connection from using block
SADataAdapter da = new SADataAdapter("Select * from acctlog", conn); //create a new data adapter. I don't know what's special about this.
SACommandBuilder cb = new SACommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges; //cheaty bullshit for just overpowering the conflict changes negating the concurrency violation.
da.Fill(DTAcctLog); //you have to fill it to update it.
DTAcctLog.Rows[4]["repuid"] = 2000042;
DTAcctLog.Rows[5]["repuid"] = 2000032;
DTAcctLog.Rows[6]["repuid"] = 2000012;
DTAcctLog.Rows[7]["repuid"] = 2000015;
DTAcctLog.Rows[8]["repuid"] = 2000017;
DTAcctLog.Rows[9]["repuid"] = 2000025;
da.UpdateCommand = cb.GetUpdateCommand();
da.Update(DTAcctLog);
DTAcctLog.AcceptChanges();
dataGridView1.DataSource = DTAcctLog;
//da.Fill(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message + "\n\n" + ex.ToString());
}
}
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);
}
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);
}
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 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);
}
private void button1_Click_1(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataSet tester
{
conn.Open(); //open connection from using block
SADataAdapter da = new SADataAdapter(); //create a new data adapter. I don't know what's special about this.
da.SelectCommand = new SACommand("Select * from AcctLog", conn);
dataGridView1.DataSource = DTAcctLog;
SACommandBuilder cb = new SACommandBuilder(da);
da.Fill(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message);
}
finally
{
dataGridView1.Refresh();
}
}
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 Task <DataSet> FillAsync(SADataAdapter dta, DataSet dataSet, CancellationToken cancellationToken)
{
var result = new TaskCompletionSource <DataSet>();
if (cancellationToken == CancellationToken.None || !cancellationToken.IsCancellationRequested)
{
try
{
lock (asyncLock2)
{
dta.Fill(dataSet);
result.SetResult(dataSet);
}
}
catch (System.Exception ex)
{
result.SetException(ex);
}
}
else
{
result.SetCanceled();
}
return(result.Task);
}
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 Task <DataSet> FillSchemaAsync(SADataAdapter dta, DataSet dataSet, CancellationToken cancellationToken)
{
var result = new TaskCompletionSource <DataSet>();
if (cancellationToken == CancellationToken.None || !cancellationToken.IsCancellationRequested)
{
try
{
lock (asyncLock2)
{
dta.FillSchema(dataSet, SchemaType.Source);
result.SetResult(dataSet);
}
}
catch (System.Exception ex)
{
Logger.Info("Executing query Fill AsyncSchema: " + ex.Message);
result.SetException(ex);
}
}
else
{
result.SetCanceled();
}
return(result.Task);
}
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);
}
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");
}
}
}
}
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);
}
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request)
{
SACommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0]);
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
DataRow[] singleRow = new DataRow[1];
// Delete each record
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SetOriginalValues(request, packet, row, true);
row.AcceptChanges();
row.Delete();
singleRow[0] = row;
da.Update(singleRow);
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
}
scope.Complete();
}
return(request.Table);
}
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);
}
public override void UpdateDataSet(string sqlQuery, ref DataSet dts)
{
SACommand cmd = null;
try
{
if (_transaction != null && _currentState != ConnectionState.Open)
{
Open();
}
if (dts.HasChanges())
{
int i = 0;
IList <string> queryPart = Regex.Split(sqlQuery, ";");
foreach (DataTable table in dts.Tables)
{
if (_transaction == null)
{
cmd = new SACommand(queryPart[i++], _connection);
}
else
{
cmd = new SACommand(queryPart[i++], _connection, _transaction);
}
SADataAdapter dta = new SADataAdapter(cmd);
SACommandBuilder commandBuilder = new SACommandBuilder(dta);
dta.Update(table);
}
}
}
catch (System.Exception e)
{
if (_transaction != null && _currentState != ConnectionState.Open)
{
Rollback();
}
string msg = "DataAccessLayer Error: " + ShowMessage(e.Message);
throw new System.Exception(msg);
}
finally
{
if (_transaction == null && _currentState == ConnectionState.Open)
{
Close();
}
if (cmd != null)
{
cmd.Dispose();
}
}
}
private void DisplayDataForQuery()
{
dataGridView1.DataSource = null;
if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected)
{
if (queryBuilder.MetadataProvider is UniversalMetadataProvider)
{
if (this.DatabasePlatform == QueryBuilderDatabasePlatform.SQLAnywhere)
{
SACommand command = (SACommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
SAParameter parameter = new SAParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.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");
}
} //end sql anywhere
} //end universal metadata provider
} //end check if connected
} //end method
public override DataSet LoadDataSet(string sqlQuery)
{
DataSet dts = new DataSet();
try
{
if (_transaction == null)
{
Open();
_command = new SACommand(sqlQuery, _connection);
}
else
{
_command = new SACommand(sqlQuery, _connection, _transaction);
}
SADataAdapter dta = new SADataAdapter(_command);
dta.FillSchema(dts, SchemaType.Source);
dta.Fill(dts);
if (_transaction == null)
{
Close();
}
}
catch (System.Exception e)
{
if (_transaction != null)
{
_transaction.Rollback();
}
string msg = "Loading dataset error :" + e.Message;
Logger.Info(msg);
throw new System.Exception(msg);
}
finally
{
Close();
}
return(dts);
}
public override async Task <DataSet> AsyncDataSetLoad(string sqlQuery)
{
SADataAdapter dta = CreateDataAdapter(sqlQuery);
DataSet dts = null;
CancellationTokenSource cts = null;
CancellationTokenSource cts1 = null;
lock (asyncLoadLock)
{
dts = new DataSet();
cts = new CancellationTokenSource();
cts1 = new CancellationTokenSource();
}
if (dta != null)
{
try
{
await FillSchemaAsync(dta, dts, cts.Token);
dts = await FillAsync(dta, dts, cts1.Token);
}
catch (System.Exception e)
{
if (_transaction != null)
{
_transaction.Rollback();
}
string msg = "Loading dataset error :" + e.Message;
Logger.Info("Executing query: " + e.Message);
throw new System.Exception(msg);
}
finally
{
Close();
}
}
// to complete the async state machine.
await Task.Delay(1);
return(dts);
}
private void button2_Click_1(object sender, EventArgs e) //send to DB
{
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataTable Tester
{
conn.Open();
SADataAdapter da = new SADataAdapter("select * from acctlog", conn);
SACommandBuilder cb = new SACommandBuilder(da);
da.Fill(DTAcctLog);
da.Update(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message);
}
}
// 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
{
}
}
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;
}
public virtual IList <AbstractBusinessObject> Query(string tablename, Type boType, SACommand cmd) //string sql, SAParameterCollection criteria,
{
//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);
}
private void button1_Click_1(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataSet tester
{
conn.Open(); //open connection from using block
SADataAdapter da = new SADataAdapter(); //create a new data adapter. I don't know what's special about this.
da.SelectCommand = new SACommand("Select * from AcctLog", conn);
dataGridView1.DataSource = DTAcctLog;
SACommandBuilder cb = new SACommandBuilder(da);
da.Fill(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message);
}
finally
{
dataGridView1.Refresh();
}
}
DataTable IPlugin.GetTableIndexes(string database, string table)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable dt = new DataTable();
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
string query = "select * from sys.sysindexes where creator = '{0}' and indextype <> 'Foreign Key' and tname = '{0}'";
query = string.Format(query, schema, table);
SADataAdapter ad = new SADataAdapter(query, cn);
ad.Fill(dt);
metaData = context.CreateIndexesDataTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dtRow = dt.Rows[i];
string cols = dtRow["colnames"].ToString();
string[] columns = cols.Split(',');
foreach (string column in columns)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = table;
row["INDEX_NAME"] = dtRow["iname"];
row["PRIMARY_KEY"] = false;
switch(dtRow["indextype"].ToString().ToLower())
{
case "primary key":
row["PRIMARY_KEY"] = true;
row["UNIQUE"] = true;
break;
case "non-unique":
row["UNIQUE"] = false;
break;
case "unique":
row["UNIQUE"] = true;
break;
}
string[] columnData = column.Split(' ');
row["COLUMN_NAME"] = columnData[0];
if (columnData[1] == "ASC")
{
row["COLLATION"] = 1;
}
else
{
row["COLLATION"] = 2;
}
}
}
}
}
catch { }
return metaData;
}
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 static DataTable SaveStoredProcEntity(esDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == esDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SACommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildStoredProcInsertCommand(request);
SetModifiedValues(request, request.EntitySavePacket, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildStoredProcUpdateCommand(request);
SetOriginalValues(request, request.EntitySavePacket, row, false);
SetModifiedValues(request, request.EntitySavePacket, row);
row.AcceptChanges();
row.SetModified();
break;
case esDataRowState.Deleted:
cmd = da.DeleteCommand = Shared.BuildStoredProcDeleteCommand(request);
SetOriginalValues(request, request.EntitySavePacket, row, true);
row.AcceptChanges();
row.Delete();
break;
}
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "SaveEntityStoredProcedure", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(singleRow);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (cmd.Parameters != null)
{
foreach (SAParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
}
return dataTable;
}
private static DataTable SaveDynamicEntity(esDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == esDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SACommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket.ModifiedColumns);
SetModifiedValues(request, request.EntitySavePacket, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket.ModifiedColumns);
SetOriginalValues(request, request.EntitySavePacket, row, false);
SetModifiedValues(request, request.EntitySavePacket, row);
row.AcceptChanges();
row.SetModified();
break;
case esDataRowState.Deleted:
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.EntitySavePacket);
SetOriginalValues(request, request.EntitySavePacket, row, true);
row.AcceptChanges();
row.Delete();
break;
}
if (!needToDelete && request.Properties != null)
{
request.Properties["esDataRequest"] = request;
request.Properties["esEntityData"] = request.EntitySavePacket;
dataTable.ExtendedProperties["props"] = request.Properties;
}
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new SARowUpdatedEventHandler(OnRowUpdated);
}
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, request.EntitySavePacket, "SaveEntityDynamic", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(singleRow);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
}
return dataTable;
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableForLinqToSql(esDataRequest request, esDataResponse response)
{
SACommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = request.LinqContext.GetCommand(request.LinqQuery) as SACommand;
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
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, "LoadForLinqToSql", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
static private DataTable SaveDynamicCollection_InsertsUpdates(esDataRequest request)
{
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
SACommand cmd = null;
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new SARowUpdatedEventHandler(OnRowUpdated);
}
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
if (packet.RowState != esDataRowState.Added && packet.RowState != esDataRowState.Modified)
{
continue;
}
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
switch (packet.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, packet.ModifiedColumns);
SetModifiedValues(request, packet, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, packet.ModifiedColumns);
SetOriginalValues(request, packet, row, false);
SetModifiedValues(request, packet, row);
row.AcceptChanges();
row.SetModified();
break;
}
request.Properties["esDataRequest"] = request;
request.Properties["esEntityData"] = packet;
dataTable.ExtendedProperties["props"] = request.Properties;
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
da.Update(singleRow);
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
dataTable.Rows.Clear();
}
}
}
scope.Complete();
}
return(dataTable);
}
static private DataTable SaveDynamicEntity(esDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == esDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SACommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket.ModifiedColumns);
SetModifiedValues(request, request.EntitySavePacket, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket.ModifiedColumns);
SetOriginalValues(request, request.EntitySavePacket, row, false);
SetModifiedValues(request, request.EntitySavePacket, row);
row.AcceptChanges();
row.SetModified();
break;
case esDataRowState.Deleted:
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.EntitySavePacket);
SetOriginalValues(request, request.EntitySavePacket, row, true);
row.AcceptChanges();
row.Delete();
break;
}
if (!needToDelete && request.Properties != null)
{
request.Properties["esDataRequest"] = request;
request.Properties["esEntityData"] = request.EntitySavePacket;
dataTable.ExtendedProperties["props"] = request.Properties;
}
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new SARowUpdatedEventHandler(OnRowUpdated);
}
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
da.Update(singleRow);
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
}
return(dataTable);
}
static private 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);
da.Fill(dataTable);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return(response);
}
static private DataTable SaveStoredProcCollection(esDataRequest request)
{
bool needToInsert = false;
bool needToUpdate = false;
bool needToDelete = false;
Dictionary <DataRow, esEntitySavePacket> rowMapping = null;
if (request.ContinueUpdateOnError)
{
rowMapping = new Dictionary <DataRow, esEntitySavePacket>();
}
//================================================
// Create the DataTable ...
//================================================
DataTable dataTable = CreateDataTable(request);
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
SetModifiedValues(request, packet, row);
dataTable.Rows.Add(row);
if (request.ContinueUpdateOnError)
{
rowMapping[row] = packet;
}
break;
case esDataRowState.Modified:
SetOriginalValues(request, packet, row, false);
SetModifiedValues(request, packet, row);
dataTable.Rows.Add(row);
row.AcceptChanges();
row.SetModified();
if (request.ContinueUpdateOnError)
{
rowMapping[row] = packet;
}
break;
case esDataRowState.Deleted:
SetOriginalValues(request, packet, row, true);
dataTable.Rows.Add(row);
row.AcceptChanges();
row.Delete();
if (request.ContinueUpdateOnError)
{
rowMapping[row] = packet;
}
break;
}
}
if (Shared.HasUpdates(dataTable.Rows, out needToInsert, out needToUpdate, out needToDelete))
{
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
SACommand cmd = null;
if (needToInsert)
{
da.InsertCommand = cmd = Shared.BuildStoredProcInsertCommand(request);
}
if (needToUpdate)
{
da.UpdateCommand = cmd = Shared.BuildStoredProcUpdateCommand(request);
}
if (needToDelete)
{
da.DeleteCommand = cmd = Shared.BuildStoredProcDeleteCommand(request);
}
using (esTransactionScope scope = new esTransactionScope())
{
if (needToInsert)
{
esTransactionScope.Enlist(da.InsertCommand, request.ConnectionString, CreateIDbConnectionDelegate);
}
if (needToUpdate)
{
esTransactionScope.Enlist(da.UpdateCommand, request.ConnectionString, CreateIDbConnectionDelegate);
}
if (needToDelete)
{
esTransactionScope.Enlist(da.DeleteCommand, request.ConnectionString, CreateIDbConnectionDelegate);
}
try
{
da.Update(dataTable);
}
finally
{
if (needToInsert)
{
esTransactionScope.DeEnlist(da.InsertCommand);
}
if (needToUpdate)
{
esTransactionScope.DeEnlist(da.UpdateCommand);
}
if (needToDelete)
{
esTransactionScope.DeEnlist(da.DeleteCommand);
}
}
scope.Complete();
}
}
if (request.ContinueUpdateOnError && dataTable.HasErrors)
{
DataRow[] errors = dataTable.GetErrors();
foreach (DataRow rowWithError in errors)
{
request.FireOnError(rowMapping[rowWithError], rowWithError.RowError);
}
}
}
return(request.Table);
}
private static DataTable SaveDynamicCollection_Deletes(esDataRequest request)
{
SACommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0]);
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
DataRow[] singleRow = new DataRow[1];
// Delete each record
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SetOriginalValues(request, packet, row, true);
row.AcceptChanges();
row.Delete();
singleRow[0] = row;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(singleRow);
}
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
}
scope.Complete();
}
return request.Table;
}
DataTable IPlugin.GetTableIndexes(string database, string table)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable dt = new DataTable();
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
string query = "select * from sys.sysindexes where creator = '{0}' and indextype <> 'Foreign Key' and tname = '{0}'";
query = string.Format(query, schema, table);
SADataAdapter ad = new SADataAdapter(query, cn);
ad.Fill(dt);
metaData = context.CreateIndexesDataTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dtRow = dt.Rows[i];
string cols = dtRow["colnames"].ToString();
string[] columns = cols.Split(',');
foreach (string column in columns)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = table;
row["INDEX_NAME"] = dtRow["iname"];
row["PRIMARY_KEY"] = false;
switch (dtRow["indextype"].ToString().ToLower())
{
case "primary key":
row["PRIMARY_KEY"] = true;
row["UNIQUE"] = true;
break;
case "non-unique":
row["UNIQUE"] = false;
break;
case "unique":
row["UNIQUE"] = true;
break;
}
string[] columnData = column.Split(' ');
row["COLUMN_NAME"] = columnData[0];
if (columnData[1] == "ASC")
{
row["COLLATION"] = 1;
}
else
{
row["COLLATION"] = 2;
}
}
}
}
}
catch { }
return(metaData);
}
private static DataTable SaveDynamicCollection_InsertsUpdates(esDataRequest request)
{
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
SACommand cmd = null;
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new SARowUpdatedEventHandler(OnRowUpdated);
}
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
if (packet.RowState != esDataRowState.Added && packet.RowState != esDataRowState.Modified) continue;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
switch (packet.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, packet.ModifiedColumns);
SetModifiedValues(request, packet, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, packet.ModifiedColumns);
SetOriginalValues(request, packet, row, false);
SetModifiedValues(request, packet, row);
row.AcceptChanges();
row.SetModified();
break;
}
request.Properties["esDataRequest"] = request;
request.Properties["esEntityData"] = packet;
dataTable.ExtendedProperties["props"] = request.Properties;
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(singleRow);
}
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
dataTable.Rows.Clear();
}
}
}
scope.Complete();
}
return dataTable;
}
DataTable IPlugin.GetForeignKeys(string database, string table)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
DataTable fks = cn.GetSchema(iAnywhere.Data.SQLAnywhere.SAMetaDataCollectionNames.MetaDataCollections);//, new string[] { table });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
DataTable dt = new DataTable();
string query = "select * from sys.sysforeignkeys where primary_creator = '{0}' and (primary_tname = '{1}' OR foreign_tname = '{2}')";
query = string.Format(query, schema, table, table);
SADataAdapter ad = new SADataAdapter(query, cn);
ad.Fill(dt);
metaData = context.CreateForeignKeysDataTable();
foreach (DataRow dtRow in dt.Rows)
{
string cols = (string)dtRow["columns"];
cols = cols.Replace(" IS ", ";");
string[] fkColumns = cols.Split(',');
foreach (string fkCol in fkColumns)
{
if (fkCol.Length == 0)
{
break;
}
string[] fkCols = fkCol.Split(';');
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["FK_NAME"] = dtRow["role"];
row["PK_NAME"] = "Primary Key";
row["PK_TABLE_CATALOG"] = cn.Database;
row["PK_TABLE_SCHEMA"] = dtRow["primary_creator"];
row["PK_TABLE_NAME"] = dtRow["primary_tname"];
row["FK_TABLE_CATALOG"] = cn.Database;
row["FK_TABLE_SCHEMA"] = dtRow["foreign_creator"];
row["FK_TABLE_NAME"] = dtRow["foreign_tname"];
row["FK_COLUMN_NAME"] = fkCols[0];
row["PK_COLUMN_NAME"] = fkCols[1];
string pkQuery = "select iname from sys.sysindexes where creator = '{0}' and indextype = 'Primary key' and tname = '{1}'";
pkQuery = string.Format(pkQuery, schema, dtRow["primary_tname"]);
cn.Open();
using (SACommand pkCmd = new SACommand(pkQuery, cn))
{
row["PK_NAME"] = (string)pkCmd.ExecuteScalar();
cn.Close();
}
}
}
}
}
catch { }
return(metaData);
}
private static DataTable SaveStoredProcCollection(esDataRequest request)
{
bool needToInsert = false;
bool needToUpdate = false;
bool needToDelete = false;
Dictionary<DataRow, esEntitySavePacket> rowMapping = null;
if (request.ContinueUpdateOnError)
{
rowMapping = new Dictionary<DataRow, esEntitySavePacket>();
}
//================================================
// Create the DataTable ...
//================================================
DataTable dataTable = CreateDataTable(request);
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
SetModifiedValues(request, packet, row);
dataTable.Rows.Add(row);
if (request.ContinueUpdateOnError) rowMapping[row] = packet;
break;
case esDataRowState.Modified:
SetOriginalValues(request, packet, row, false);
SetModifiedValues(request, packet, row);
dataTable.Rows.Add(row);
row.AcceptChanges();
row.SetModified();
if (request.ContinueUpdateOnError) rowMapping[row] = packet;
break;
case esDataRowState.Deleted:
SetOriginalValues(request, packet, row, true);
dataTable.Rows.Add(row);
row.AcceptChanges();
row.Delete();
if (request.ContinueUpdateOnError) rowMapping[row] = packet;
break;
}
}
if (Shared.HasUpdates(dataTable.Rows, out needToInsert, out needToUpdate, out needToDelete))
{
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
SACommand cmd = null;
if (needToInsert) da.InsertCommand = cmd = Shared.BuildStoredProcInsertCommand(request);
if (needToUpdate) da.UpdateCommand = cmd = Shared.BuildStoredProcUpdateCommand(request);
if (needToDelete) da.DeleteCommand = cmd = Shared.BuildStoredProcDeleteCommand(request);
using (esTransactionScope scope = new esTransactionScope())
{
if (needToInsert) esTransactionScope.Enlist(da.InsertCommand, request.ConnectionString, CreateIDbConnectionDelegate);
if (needToUpdate) esTransactionScope.Enlist(da.UpdateCommand, request.ConnectionString, CreateIDbConnectionDelegate);
if (needToDelete) esTransactionScope.Enlist(da.DeleteCommand, request.ConnectionString, CreateIDbConnectionDelegate);
try
{
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "SaveCollectionStoredProcedure", System.Environment.StackTrace))
{
try
{
da.Update(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(dataTable);
}
}
finally
{
if (needToInsert) esTransactionScope.DeEnlist(da.InsertCommand);
if (needToUpdate) esTransactionScope.DeEnlist(da.UpdateCommand);
if (needToDelete) esTransactionScope.DeEnlist(da.DeleteCommand);
}
scope.Complete();
}
}
if (request.ContinueUpdateOnError && dataTable.HasErrors)
{
DataRow[] errors = dataTable.GetErrors();
foreach (DataRow rowWithError in errors)
{
request.FireOnError(rowMapping[rowWithError], rowWithError.RowError);
}
}
}
return request.Table;
}
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;
}
// 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
{
}
}
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;
}
DataTable IPlugin.GetForeignKeys(string database, string table)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
DataTable fks = cn.GetSchema(iAnywhere.Data.SQLAnywhere.SAMetaDataCollectionNames.MetaDataCollections);//, new string[] { table });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
DataTable dt = new DataTable();
string query = "select * from sys.sysforeignkeys where primary_creator = '{0}' and (primary_tname = '{1}' OR foreign_tname = '{2}')";
query = string.Format(query, schema, table, table);
SADataAdapter ad = new SADataAdapter(query, cn);
ad.Fill(dt);
metaData = context.CreateForeignKeysDataTable();
foreach (DataRow dtRow in dt.Rows)
{
string cols = (string)dtRow["columns"];
cols = cols.Replace(" IS ", ";");
string[] fkColumns = cols.Split(',');
foreach (string fkCol in fkColumns)
{
if (fkCol.Length == 0) break;
string[] fkCols = fkCol.Split(';');
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["FK_NAME"] = dtRow["role"];
row["PK_NAME"] = "Primary Key";
row["PK_TABLE_CATALOG"] = cn.Database;
row["PK_TABLE_SCHEMA"] = dtRow["primary_creator"];
row["PK_TABLE_NAME"] = dtRow["primary_tname"];
row["FK_TABLE_CATALOG"] = cn.Database;
row["FK_TABLE_SCHEMA"] = dtRow["foreign_creator"];
row["FK_TABLE_NAME"] = dtRow["foreign_tname"];
row["FK_COLUMN_NAME"] = fkCols[0];
row["PK_COLUMN_NAME"] = fkCols[1];
string pkQuery = "select iname from sys.sysindexes where creator = '{0}' and indextype = 'Primary key' and tname = '{1}'";
pkQuery = string.Format(pkQuery, schema, dtRow["primary_tname"]);
cn.Open();
using (SACommand pkCmd = new SACommand(pkQuery, cn))
{
row["PK_NAME"] = (string)pkCmd.ExecuteScalar();
cn.Close();
}
}
}
}
}
catch { }
return metaData;
}
// 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
{
}
}
private void button2_Click_1(object sender, EventArgs e) //send to DB
{
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataTable Tester
{
conn.Open();
SADataAdapter da = new SADataAdapter("select * from acctlog", conn);
SACommandBuilder cb = new SACommandBuilder(da);
da.Fill(DTAcctLog);
da.Update(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message);
}
}
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request)
{
SACommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0]);
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
DataRow[] singleRow = new DataRow[1];
// Delete each record
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SetOriginalValues(request, packet, row, true);
row.AcceptChanges();
row.Delete();
singleRow[0] = row;
da.Update(singleRow);
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
}
scope.Complete();
}
return request.Table;
}
private void button2_Click_2(object sender, EventArgs e)
{
string connectionString = connStr.Text.Trim();
try
{
using (SAConnection conn = new SAConnection(connectionString)) //DataSet tester
{
DTAcctLog.Rows.Clear(); //doesn't affect the update.
conn.Open(); //open connection from using block
SADataAdapter da = new SADataAdapter("Select * from acctlog", conn); //create a new data adapter. I don't know what's special about this.
SACommandBuilder cb = new SACommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges; //cheaty bullshit for just overpowering the conflict changes negating the concurrency violation.
da.Fill(DTAcctLog); //you have to fill it to update it.
DTAcctLog.Rows[4]["repuid"] = 2000042;
DTAcctLog.Rows[5]["repuid"] = 2000032;
DTAcctLog.Rows[6]["repuid"] = 2000012;
DTAcctLog.Rows[7]["repuid"] = 2000015;
DTAcctLog.Rows[8]["repuid"] = 2000017;
DTAcctLog.Rows[9]["repuid"] = 2000025;
da.UpdateCommand = cb.GetUpdateCommand();
da.Update(DTAcctLog);
DTAcctLog.AcceptChanges();
dataGridView1.DataSource = DTAcctLog;
//da.Fill(DTAcctLog);
}
}
catch (Exception ex)
{
MessageBoxHelper.PrepToCenterMessageBoxOnForm(this);
MessageBox.Show("Error\n" + ex.Message + "\n\n" + ex.ToString() );
}
}
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;
}
static private DataTable SaveStoredProcEntity(esDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == esDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SACommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildStoredProcInsertCommand(request);
SetModifiedValues(request, request.EntitySavePacket, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildStoredProcUpdateCommand(request);
SetOriginalValues(request, request.EntitySavePacket, row, false);
SetModifiedValues(request, request.EntitySavePacket, row);
row.AcceptChanges();
row.SetModified();
break;
case esDataRowState.Deleted:
cmd = da.DeleteCommand = Shared.BuildStoredProcDeleteCommand(request);
SetOriginalValues(request, request.EntitySavePacket, row, true);
row.AcceptChanges();
row.Delete();
break;
}
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
da.Update(singleRow);
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (cmd.Parameters != null)
{
foreach (SAParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
}
return(dataTable);
}
