/// <summary> /// 检索SQL参数信息并填充 /// </summary> /// <param name="cmd"></param> public void DeriveParameters(IDbCommand cmd) { if ((cmd as OdbcCommand) != null) { OdbcCommandBuilder.DeriveParameters(cmd as OdbcCommand); } }
internal static Int32 UpdateDataTableUsingTrans(DataTable table, OdbcTransaction tran, OdbcCommand cmd) { OdbcDataAdapter adapter = null; try { cmd.Connection = tran.Connection; cmd.Transaction = tran; adapter = new OdbcDataAdapter(cmd); OdbcCommandBuilder commandBulider = new OdbcCommandBuilder(adapter); commandBulider.ConflictOption = ConflictOption.CompareAllSearchableValues; int rtn = adapter.Update(table); return(rtn); } catch (Exception ex) { tran.Rollback(); tran.Dispose(); if (tran.Connection != null) { tran.Connection.Dispose(); } throw ex; } }
public OdbcDbAgent(string connectStr) { _connection = null; _dataAdapter = null; _sqlCom = null; _connection = new OdbcConnection(connectStr); }
/// <summary> /// <see cref="OdbcCommandBuilder.DeriveParameters"/> /// </summary> /// <param name="cmd"></param> public override void DeriveParameters(System.Data.IDbCommand cmd) { if (cmd is OdbcCommand) { OdbcCommandBuilder.DeriveParameters(cmd as OdbcCommand); } }
//----------------------------------------------------------------------- // Métodos de usuario: Funciones de actualización de datos de la BBDD. //----------------------------------------------------------------------- /// <summary> /// Actualizar los cambios del DataRow activo, en el DataAdpater activo, obtenido previamente con SeekOdbcRow. /// Actualiza a partir de la PK de la tabla a actualizar. /// </summary> /// /// <param name="da"> OdbcDataAdapater para actualizar. </param> /// <param name="ds"> DataSet a actualizar. </param> /// <param name="dtIndex"> Índice de la tabla en el DataRow. </param> /// <param name="drIndex"> Fila de la tabla. </param> /// <param name="UpdColsValues"> Columnas a actualizar, en formato Col1, value1, Col2, Value2, ..., ColN, ValueN </param> /// ///<remarks> /// Recibe un array con las columnas a actualizar con sus valores, /// en formato Columna1, Valor1, Columna2, Valor2, ..., columnaN, ValorN. /// Las tablas a actualizar DEBEN tener PK. /// </remarks> /// /// <seealso cref="OdbcSeekRow(string, string, string, string, string, string)"/> /// /// <returns>Resultado (S/N/C)</returns> /// public Char UpdateCurrentRow(OdbcDataAdapter da, DataSet ds, int dtIndex, int drIndex, params object[] UpdColsValues) { Char retorno = 'S'; string dt = ds.Tables[dtIndex].TableName; try { _inicializar(); // Calcular la sentencia update a partir de la PK. cbTF = new OdbcCommandBuilder(da); da.UpdateCommand = cbTF.GetUpdateCommand(); da.InsertCommand = cbTF.GetInsertCommand(); da.DeleteCommand = cbTF.GetDeleteCommand(); for (int i = 0; i < UpdColsValues.Length; i += 2) { ds.Tables[dtIndex].Rows[drIndex][UpdColsValues[i].ToString()] = UpdColsValues[i + 1]; } SqlNoOfRows = da.Update(ds, dt); } catch (OdbcException ex) { // Error de BBDD. UsrErrorC = ex.HResult; UsrError = ex.Message; UsrErrorE = ex.StackTrace; retorno = 'C'; } return(retorno); }
private void ReadDataTable(string connectionString, string queryString) { //czytanie tabeli ####################################################### DataSet ds = new DataSet(); using (OdbcConnection connection = new OdbcConnection(connectionString)) { try { connection.Open(); OdbcDataAdapter adapter = new OdbcDataAdapter(queryString, connection); OdbcCommandBuilder cmdb = new OdbcCommandBuilder(adapter); adapter.Fill(ds, "Event2"); dataGridView1.DataSource = ds.Tables["Event2"]; adapter.Dispose(); connection.Close(); } catch (Exception ex) { LogListBox.Items.Insert(0, DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss") + " " + ex.Message); LogListBox.Items.Insert(0, DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss") + " Blad wystapil przy minimalnym EventID= " + eventIdTextBox.Text); } } }
public FrmFilaImpressao() { InitializeComponent(); //preenche grd fila impr String sFilaImpr = "select id_pedido as 'Pedido', nome as 'Nome', obs as 'Obs', convert(varchar,data,108) as 'Hora', status as 'Status' from fila_impressao fl left outer join vendas v on v.id=fl.id_pedido order by status desc"; var conn = new OdbcConnection(); conn.ConnectionString = "Dsn=odbc_pliniao;" + "Uid=sa;" + "Pwd=chico110388;"; conn.Open(); OdbcDataAdapter dataAdapter3 = new OdbcDataAdapter(sFilaImpr, conn); OdbcCommandBuilder commandBuilder3 = new OdbcCommandBuilder(dataAdapter3); ds = new DataSet(); dataAdapter3.Fill(ds); grdFilaImpressao.ReadOnly = true; grdFilaImpressao.DataSource = ds.Tables[0]; grdFilaImpressao.ClearSelection(); //fim preenche fila impr }
public void GetUpdateCommandDBConcurrencyExceptionTest() { OdbcConnection conn = (OdbcConnection)ConnectionManager.Singleton.Connection; try { string selectQuery = "select id, lname from employee where id = 3"; OdbcDataAdapter da = new OdbcDataAdapter(selectQuery, conn); DataSet ds = new DataSet(); da.Fill(ds, "IntTest"); Assert.AreEqual(1, ds.Tables.Count); OdbcCommandBuilder cb = new OdbcCommandBuilder(da); Assert.IsNotNull(cb); DataRow [] rows = ds.Tables [0].Select("id=1"); rows [0] [0] = 6660; // non existent ds.Tables [0].AcceptChanges(); // moves 6660 to original value rows [0] [0] = 1; // moves 6660 as search key into db table try { da.Update(rows); Assert.Fail("#1"); } catch (DBConcurrencyException ex) { // Concurrency violation: the UpdateCommand // affected 0 records Assert.AreEqual(typeof(DBConcurrencyException), ex.GetType(), "#2"); Assert.IsNull(ex.InnerException, "#3"); Assert.IsNotNull(ex.Message, "#4"); Assert.AreSame(rows [0], ex.Row, "#5"); Assert.AreEqual(1, ex.RowCount, "#6"); } } finally { ConnectionManager.Singleton.CloseConnection(); } }
protected override DbDataAdapter GetDataAdapter(string tableName, out DbCommandBuilder builder) { var adapter = new OdbcDataAdapter(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM \"{0}\"", tableName), OdbcConnectionString); builder = new OdbcCommandBuilder(adapter); return(adapter); }
private void btnBuscar_Click(object sender, EventArgs e) { String sQuery = "1=1"; if (cboConsulta.Text != "") { sQuery = " descr like '%" + cboConsulta.Text + "%'"; } string select = "SELECT id as Código, descr as Descrição, convert(varchar, cast(preco as money),1) as Preço FROM produto where " + sQuery; conexao c = new conexao(); var conn = new OdbcConnection(); conn.ConnectionString = "Dsn=odbc_pliniao;" + "Uid=sa;" + "Pwd=chico110388;"; conn.Open(); OdbcDataAdapter dataAdapter = new OdbcDataAdapter(select, conn); OdbcCommandBuilder commandBuilder = new OdbcCommandBuilder(dataAdapter); DataSet ds = new DataSet(); dataAdapter.Fill(ds); grdConsulta.ReadOnly = true; grdConsulta.DataSource = ds.Tables[0]; grdConsulta.Columns[0].Width = 100; grdConsulta.Columns[1].Width = 300; grdConsulta.Columns[2].Width = 100; }
protected override DbDataAdapter GetDataAdapter(string tableName, string schemaName, out DbCommandBuilder builder) { var adapter = new OdbcDataAdapter(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", schemaName ?? "dbo", tableName), ConnectionString); builder = new OdbcCommandBuilder(adapter); return(adapter); }
/*private void getCusomers() * { * SQL ="SELECT `customerId` , `lname` ,`fname`,`phone` FROM customers WHERE lname LIKE ?"; * * try * { * cmd.CommandText = SQL; * cmd.Connection = Db.Cn; * cmd.Parameters.Clear(); * cmd.Parameters.AddWithValue("@lname", txtSearch.Text + "%"); * //reader = cmd.ExecuteReader(); * dtcustomers.Clear(); * dtservices.Clear(); * //dtcustomers.Load(reader); * dgvCustomers.DataSource = null; * dgvCustomers.DataSource = dtcustomers; * } * catch (Exception ex) * { * MessageBox.Show(ex.Message, "oops!", * MessageBoxButtons.OK, * MessageBoxIcon.Information); * this.Close(); * } * finally * { * try * { * if (reader != null) * reader.Close(); * } * finally { } * } * } */ private void getCustomers() { SQL = "SELECT `customerId` , `lname` ,`fname`,`phone` FROM customers WHERE lname LIKE ?"; try { dacustomers = new OdbcDataAdapter(); dacustomers.SelectCommand = new OdbcCommand(SQL, Db.Cn); dacustomers.SelectCommand.Parameters.AddWithValue("@lname", txtSearch.Text + "%"); OdbcCommandBuilder builder = new OdbcCommandBuilder(dacustomers); //dtcustomers.Clear(); //dtdetails.Clear(); dacustomers.Fill(dtcustomers); //dgvServices.DataSource = null; dgvCustomers.DataSource = null; // dgvDetails.DataSource = null; dgvServices.DataSource = dtservices; dgvCustomers.DataSource = dtcustomers; } catch (Exception ex) { MessageBox.Show(ex.Message, "oops!", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } }
private void toolStripButton1_Click(object sender, EventArgs e) { //vendas canc:.................................................. string select = "select right('000000' + cast(id as nvarchar),6) as 'Núm. Pedido', CONCAT(nome, ' ' ,obs) as 'Nome', convert(varchar, cast(preco_total - (preco_total*desconto) as money),1) as 'Preço Total', convert(varchar(5),data, 114) as 'Hora do Pedido', concat(datediff(mi,data, GETDATE()), ' min') as 'Tempo de Espera' from vendas where convert(varchar(11),data,103)=convert(varchar(11),'" + dtConsulta.Text.ToString() + "',103) and isCancelado=1"; var conn = new OdbcConnection(); conn.ConnectionString = "Dsn=odbc_pliniao;" + "Uid=sa;" + "Pwd=chico110388;"; conn.Open(); OdbcDataAdapter dataAdapter = new OdbcDataAdapter(select, conn); OdbcCommandBuilder commandBuilder = new OdbcCommandBuilder(dataAdapter); DataSet ds = new DataSet(); dataAdapter.Fill(ds); grdConsultaVendasCanceladas.ReadOnly = true; grdConsultaVendasCanceladas.DataSource = ds.Tables[0]; grdConsultaVendasCanceladas.Columns[0].Width = 100; grdConsultaVendasCanceladas.Columns[1].Width = 300; grdConsultaVendasCanceladas.Columns[2].Width = 100; grdConsultaVendasCanceladas.Columns[3].Width = 100; grdConsultaVendasCanceladas.Columns[4].Width = 0; grdConsultaVendasCanceladas.Columns[4].Visible = false; grdConsultaVendasCanceladas.ClearSelection(); //................................................................... }
private void CreateUser() { SetPassword(txtUsername.Text, txtPassword.Text); try { if (txtUsername.Text != "" && txtPassword.Text != "" && txtEmail.Text != "") { da = new OdbcDataAdapter(); da.SelectCommand = new OdbcCommand(SQL, cn); OdbcCommandBuilder builder = new OdbcCommandBuilder(da); da.Fill(dt); MessageBox.Show("User Created"); this.Close(); } else { MessageBox.Show("Username or Password or Email field is empty"); } } catch (Exception ex) { MessageBox.Show(ex.Message, "oops!", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } }
// METHODS private void LoadMovies() { try { string query = "SELECT" + Environment.NewLine + "`film`.`film_id` AS `Id`," + Environment.NewLine + "`film`.`title` AS `Title`," + Environment.NewLine + "`film`.`release_year` AS `Release Year`," + Environment.NewLine + "`film`.`length` AS `Length`," + Environment.NewLine + "`film`.`language_id` AS `Language Id`," + Environment.NewLine + "`film`.`rating` AS `Rating`," + Environment.NewLine + "`film`.`rental_rate` AS `Price`," + Environment.NewLine + "film.`special_features` AS `Special Features`," + Environment.NewLine + "film.`rental_duration` AS `Rental Duration`," + Environment.NewLine + "film.`replacement_cost` AS `Replacement Cost`," + Environment.NewLine + "`film`.`description` AS `Description`," + Environment.NewLine + "`film`.`image` AS `Image`" + Environment.NewLine + "FROM film WHERE film.`title` LIKE " + Methods.Quote(txtMovieName.Text + "%"); dataAdapter.SelectCommand = new OdbcCommand(query, DataBase.connection); OdbcCommandBuilder commandBuilder = new OdbcCommandBuilder(dataAdapter); dtMovies.Clear(); dataAdapter.Fill(dtMovies); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public DataTable string_DataTable_fix_Ded(OdbcCommand OdCmd) { OdbcConnection con = new OdbcConnection(ConStr_fix); DataTable ReturnValue = new DataTable(); try { OdCmd.Connection = con; OdbcDataAdapter adapter = new OdbcDataAdapter(OdCmd); OdbcCommandBuilder b = new OdbcCommandBuilder(adapter); adapter.Fill(ReturnValue); } catch (Exception ex) { //NLogLogger MontrFinFileLog = new NLogLogger(); //MontrFinFileLog.Error(ex); } if (con.State != ConnectionState.Closed) { con.Close(); con.Dispose(); } return(ReturnValue); }
/// <summary> /// 更新数据表 /// </summary> /// <param name="table"></param> /// <param name="cmd"></param> /// <returns></returns> public static Int32 UpdateDataTable(DataTable table, OdbcCommand cmd) { using (OdbcConnection conn = new OdbcConnection(ConnStr)) { OdbcDataAdapter adapter = null; try { cmd.Connection = conn; adapter = new OdbcDataAdapter(cmd); OdbcCommandBuilder commandBulider = new OdbcCommandBuilder(adapter); commandBulider.ConflictOption = ConflictOption.CompareAllSearchableValues; conn.Open(); adapter.SelectCommand.Transaction = conn.BeginTransaction(); /////////////////开始事务 int rtn = adapter.Update(table); adapter.SelectCommand.Transaction.Commit(); /////提交事务 return(rtn); } catch (Exception ex) { if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null) { adapter.SelectCommand.Transaction.Rollback(); } throw ex; } finally { conn.Close(); conn.Dispose(); } } }
public void GetDT(string sqlstring, ref OdbcDataAdapter da, ref DataTable dt) { this._sqlcmd = sqlstring; try { using (OdbcConnection _cn = new OdbcConnection(this._connstr)) { _cn.Open(); OdbcCommand sqlcmd = new OdbcCommand(this._sqlcmd, _cn) { CommandType = CommandType.Text, CommandTimeout = _timeout }; this._da = new OdbcDataAdapter(sqlcmd); this._cb = new OdbcCommandBuilder(this._da); dt.Clear(); this._da.Fill(dt); da = this._da; _cn.Close(); _cn.Dispose(); } } catch (Exception ex) { string err = string.Format("{0}: {1}{2}SQL: {3}", MethodBase.GetCurrentMethod().Name, ex.ToString(), System.Environment.NewLine, this._sqlcmd); //if ( Debugger.IsAttached == true ) { Console.WriteLine(err); } Nlogger.Error(err); } }
public void getDataWithSelColumn(List <string> liColumns) { string[] delmetStrs = { "From", "from", "FROM" }; string[] arrStr = c_sql.Split(delmetStrs, 2, StringSplitOptions.None); if (arrStr.Length < 2 || liColumns.Count == 0) { return; } string selectItem = ""; selectItem = liColumns[0].ToString(); for (int i = 1; i < liColumns.Count; i++) { selectItem += "," + liColumns[i].ToString(); } string select_sql = " select " + selectItem + " from " + arrStr[1]; MessageBox.Show(select_sql); adapter = new OdbcDataAdapter(select_sql, _conn); cmdb = new OdbcCommandBuilder(adapter); ds = new DataSet(); adapter.TableMappings.Add("Table", tablename); adapter.Fill(ds); dt = ds.Tables[tablename]; return; }
// This function to get the image data from the database public byte[] getImage(string NPM) { string strSql = "SELECT FILE_FOTO FROM data_sidang WHERE NPM='" + NPM + "'"; DataSet ds = new DataSet("Image"); OdbcDataAdapter tempAP = new OdbcDataAdapter(strSql, this._objConn); OdbcCommandBuilder objCommand = new OdbcCommandBuilder(tempAP); tempAP.Fill(ds, "DATA"); DataTable dt = ds.Tables["DATA"]; if (dt.Rows.Count > 0) { try { this._objConn.Open(); byte[] buffer = (byte[])dt.Rows[0]["FILE_FOTO"]; return(buffer); } catch { this._objConn.Close(); return(null); } finally { this._objConn.Close(); } } else { return(null); } }
public void GetInsertCommandTestWithExpression() { if (ConnectionManager.Singleton.Engine.Type == EngineType.MySQL) { Assert.Ignore("Schema info from MySQL is incomplete"); } OdbcConnection conn = (OdbcConnection)ConnectionManager.Singleton.Connection; OdbcCommand cmd = null; try { string selectQuery = "select id, lname, id+1 as next_id from employee where id = 3"; OdbcDataAdapter da = new OdbcDataAdapter(selectQuery, conn); DataSet ds = new DataSet(); da.Fill(ds, "IntTest"); Assert.AreEqual(1, ds.Tables.Count); OdbcCommandBuilder cb = new OdbcCommandBuilder(da); cmd = cb.GetInsertCommand(); Assert.AreEqual("INSERT INTO employee (id, lname) VALUES (?, ?)", cmd.CommandText, "#1"); Assert.AreSame(conn, cmd.Connection, "#2"); AssertInsertParameters(cmd, "#3:"); } finally { if (cmd != null) { cmd.Dispose(); } ConnectionManager.Singleton.CloseConnection(); } }
public void QuoteIdentifier_UseConnection() { var commandBuilder = new OdbcCommandBuilder(); // Get quote string var quotedIdentifier = commandBuilder.QuoteIdentifier("Test", connection); var qs = quotedIdentifier.Remove(quotedIdentifier.IndexOf("Test")); Assert.NotEmpty(qs); // Test -> 'Test' var quotedTestString = commandBuilder.QuoteIdentifier("Source", connection); Assert.Equal($"{qs}Source{qs}", quotedTestString); // 'Test' -> Test Assert.Equal("Source", commandBuilder.UnquoteIdentifier(quotedTestString, connection)); // Test' -> 'Test''' quotedTestString = commandBuilder.QuoteIdentifier($"Test identifier{qs}", connection); Assert.Equal($"{qs}Test identifier{qs}{qs}{qs}", quotedTestString); // 'Test''' -> Test' Assert.Equal($"Test identifier{qs}", commandBuilder.UnquoteIdentifier(quotedTestString, connection)); // Needs an active connection Assert.Throws <InvalidOperationException>(() => commandBuilder.QuoteIdentifier("Test", null)); Assert.Throws <InvalidOperationException>(() => commandBuilder.QuoteIdentifier("Test")); Assert.Throws <InvalidOperationException>(() => commandBuilder.UnquoteIdentifier("Test", null)); Assert.Throws <InvalidOperationException>(() => commandBuilder.UnquoteIdentifier("Test")); }
public void preencheGrid(DataGridView grd, String sQuery) { conexao c = new conexao(); //PREENCHE O GRID.. DataSet ds = null; string select = sQuery; var conn = new OdbcConnection(); conn.ConnectionString = c.getConexaoString(); conn.Open(); OdbcDataAdapter dataAdapter = new OdbcDataAdapter(select, conn); OdbcCommandBuilder commandBuilder = new OdbcCommandBuilder(dataAdapter); ds = new DataSet(); dataAdapter.Fill(ds); grd.ReadOnly = true; grd.DataSource = ds.Tables[0]; grd.CurrentCell = null; // grd.Columns[0].Visible = false; grd.ClearSelection(); conn.Close(); }
public OdbcDbAgent() { _connection = null; _dataAdapter = null; _sqlCom = null; _connection = new OdbcConnection(); }
public OdbcDbAgent(IDbConnection connection) { _connection = null; _dataAdapter = null; _sqlCom = null; _connection = (OdbcConnection)connection; }
private void GetDBObjectNames(string name, List <string> list) { DataTable schema = null; using (DbConnection connection = GetConnection()) using (OdbcCommandBuilder builder = new OdbcCommandBuilder()) { connection.Open(); schema = connection.GetSchema("Tables"); foreach (DataRow row in schema.Rows) { string tableType = row["TABLE_TYPE"].ToString(); if (String.Compare(tableType, name) == 0) { string tableName = row["TABLE_NAME"].ToString(); string schemaName = row["TABLE_SCHEM"].ToString(); if (String.IsNullOrEmpty(schemaName)) { list.Add(tableName); } else { list.Add(schemaName + "." + builder.QuoteIdentifier(tableName, connection as OdbcConnection)); } } } } }
/*public override void Delete(TStorage obj) * { * try * { * using (var conn = new OdbcConnection(ConnectionString)) * { * conn.Open(); * using (var cmd = new OdbcCommand()) * { * cmd.Connection = conn; * * cmd.CommandText = * "DELETE FROM " + obj.Table + " WHERE " + obj.PKField + "=" + obj.ID; * cmd.ExecuteScalar(); * } * } * } * catch (Exception ex) * { * * } * }*/ public override void Delete(TStorage obj) { try { DataSet dataSet = new DataSet(); OdbcConnection conn = new OdbcConnection(ConnectionString); conn.Open(); OdbcDataAdapter odbcDataAdapter = new OdbcDataAdapter(); odbcDataAdapter.SelectCommand = new OdbcCommand("SELECT * FROM " + obj.Table, conn); OdbcCommandBuilder ocb = new OdbcCommandBuilder(odbcDataAdapter); odbcDataAdapter.Fill(dataSet, obj.Table); conn.Close(); DataTable dt = dataSet.Tables[0]; DataColumn[] keyColumns = new DataColumn[1]; keyColumns[0] = dt.Columns[obj.PKField]; dt.PrimaryKey = keyColumns; DataRow dr = dt.Rows.Find(obj.ID); dr.Delete(); conn.ConnectionString = ConnectionString; conn.Open(); odbcDataAdapter.SelectCommand = new OdbcCommand("SELECT * FROM " + obj.Table, conn); odbcDataAdapter.Update(dataSet, obj.Table); conn.Close(); } catch (Exception ex) { } }
private async Task <object> OpenConnectionAndExecute(SqlPassContext context, string queryTemplate, Dictionary <string, string> parameters, Func <OdbcCommand, Task <object> > action) { string connectionString = BuildConnectionString(context); using (OdbcConnection connection = new OdbcConnection(connectionString)) using (OdbcCommandBuilder builder = new OdbcCommandBuilder()) { await connection.OpenAsync(); string escapedTableName = builder.QuoteIdentifier(context.TableName, connection); string queryString = string.Format(CultureInfo.InvariantCulture, queryTemplate, escapedTableName); using (OdbcCommand command = new OdbcCommand(queryString, connection)) { foreach (var key in parameters.Keys) { if (parameters.TryGetValue(key, out var value)) { command.Parameters.AddWithValue($"@{key}", value); } } return(await action(command)); } } }
public TwitchMessageConsumer(ref ConcurrentQueue <string> commandMessageQueue) { config = new ConsumerConfig { BootstrapServers = server, GroupId = Guid.NewGuid().ToString(), EnableAutoCommit = true, StatisticsIntervalMs = 5000, SessionTimeoutMs = 6000, AutoOffsetReset = AutoOffsetReset.Latest, EnablePartitionEof = true, }; this.commandMessageQueue = commandMessageQueue; source = new CancellationTokenSource(); canceltoken = source.Token; connDB = new OdbcConnection(dbConnString); try { connDB.Open(); } catch (OdbcException e) { Console.WriteLine(e.Message + "\n\n" + e.StackTrace); } data = new DataSet(); dbAdapter = new OdbcDataAdapter(); dbCommand = new OdbcCommandBuilder(dbAdapter); }
public void ConflictOptionTest() { OdbcCommandBuilder cb = new OdbcCommandBuilder(); Assert.AreEqual(ConflictOption.CompareAllSearchableValues, cb.ConflictOption, "#1"); cb.ConflictOption = ConflictOption.CompareRowVersion; Assert.AreEqual(ConflictOption.CompareRowVersion, cb.ConflictOption, "#2"); }
// End RemoveTable method. /// <summary> /// Obtain a data adapter for the specified Table /// </summary> /// <param name="tableName">Name of the table to obtain the /// adapter for</param> /// <returns>Adapter object for the specified table</returns> /// <remarks>An adapter serves as a bridge between a DataSet (in memory /// representation of table) and the data source</remarks> private OdbcDataAdapter GetAdapterForTable(string tableName) { OdbcDataAdapter da = null; var di = PSDriveInfo as AccessDBPSDriveInfo; if (di == null || !TableNameIsValid(tableName) || !TableIsPresent(tableName)) { return null; } OdbcConnection connection = di.Connection; try { // Create an ODBC data adapter. This can be used to update the // data source with the records that will be created here // using data sets. string sql = "Select * from " + tableName; da = new OdbcDataAdapter(new OdbcCommand(sql, connection)); // Create an ODBC command builder object. This will create sql // commands automatically for a single table, thus // eliminating the need to create new sql statements for // every operation to be done. var cmd = new OdbcCommandBuilder(da); // Set the delete command for the table here. sql = "Delete from " + tableName + " where ID = ?"; da.DeleteCommand = new OdbcCommand(sql, connection); // Specify a DeleteCommand parameter based on the "ID" // column. da.DeleteCommand.Parameters.Add(new OdbcParameter()); da.DeleteCommand.Parameters[0].SourceColumn = "ID"; // Create an InsertCommand based on the sql string // Insert into "tablename" values (?,?,?)" where // ? represents a column in the table. Note that // the number of ? will be equal to the number of // columns. var ds = new DataSet(); da.FillSchema(ds, SchemaType.Source); ds.Locale = CultureInfo.InvariantCulture; sql = "Insert into " + tableName + " values ( "; for (int i = 0; i < ds.Tables["Table"].Columns.Count; i++) { sql += "?, "; } sql = sql.Substring(0, sql.Length - 2); sql += ")"; da.InsertCommand = new OdbcCommand(sql, connection); // Create parameters for the InsertCommand based on the // captions of each column. for (int i = 0; i < ds.Tables["Table"].Columns.Count; i++) { da.InsertCommand.Parameters.Add(new OdbcParameter()); da.InsertCommand.Parameters[i].SourceColumn = ds.Tables["Table"].Columns[i].Caption; } // Open the connection if it is not already open. if (connection.State != ConnectionState.Open) { connection.Open(); } } catch (Exception e) { WriteError(new ErrorRecord( e, "CannotAccessSpecifiedTable", ErrorCategory.InvalidOperation, tableName)); } return da; }