/// <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;
}
