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 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 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 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 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 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 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(); } }
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 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 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 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 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); }
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); }
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 { } }
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; }
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 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; }
// 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; }
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); }
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); }
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; }
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); }
// 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 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); }
// 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_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; }
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; }