public void UpdateRegistrazioneDS(string tablename, RilevazioniDS ds)
{
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", tablename);
using (DbDataAdapter a = BuildDataAdapter(query))
{
a.ContinueUpdateOnError = false;
DataTable dt = ds.Tables[tablename];
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(dt);
}
}
public DatabaseReporter(DbProviderFactory factory, DbConnection connection)
{
this.connection = connection;
watch = System.Diagnostics.Stopwatch.StartNew();
DbCommand command = factory.CreateCommand();
command.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "output";
command.Connection = connection;
kopadapter = factory.CreateDataAdapter();
kopadapter.SelectCommand = command;
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.ConflictOption = ConflictOption.OverwriteChanges;
builder.DataAdapter = kopadapter;
// Wanneer : Additional information: De Microsoft.Jet.OLEDB.4.0-provider is niet geregistreerd op de lokale computer.
// ==> draaien als x86
kopadapter.InsertCommand = builder.GetInsertCommand();
kopadapter.UpdateCommand = builder.GetUpdateCommand();
kopadapter.DeleteCommand = builder.GetDeleteCommand();
command = factory.CreateCommand();
command.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "outputline";
command.Connection = connection;
regeladapter = factory.CreateDataAdapter();
regeladapter.SelectCommand = command;
builder = factory.CreateCommandBuilder();
builder.ConflictOption = ConflictOption.OverwriteChanges;
builder.DataAdapter = regeladapter;
regeladapter.InsertCommand = builder.GetInsertCommand();
regeladapter.UpdateCommand = builder.GetUpdateCommand();
regeladapter.DeleteCommand = builder.GetDeleteCommand();
}
public virtual bool UpDateTable(DataTable table, string tableName)
{
DbCommand command = GetDbCommand();
command.CommandText = string.Format("SELECT * FROM [{0}]", tableName);
command.Connection = GetDbConnection();
DbDataAdapter adapter = GetDbDataAdapter(command);
DbCommandBuilder builder = GetDbCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
int cou = adapter.Update(table);
return(cou > 0);
}
public bool SaveDataTable(DataTable dataTable, string tableName)
{
string sql = string.Format("select * from {0}", tableName);
DbDataAdapter sda = CurrentDBConnection.CreateDbDataAdapter(sql, GetConnection());
DbCommandBuilder scb = CurrentDBConnection.CreateCommandBuilder();
sda.InsertCommand = scb.GetInsertCommand();
sda.UpdateCommand = scb.GetUpdateCommand();
sda.DeleteCommand = scb.GetDeleteCommand();
sda.Fill(dataTable);
var r = sda.Update(dataTable);
return(true);
}
private void frmshirt_Load(object sender, EventArgs e)
{
DbCommand idbCommand = Connection.provideConnection().CreateCommand();
idbCommand.CommandText = "select * from `shirt`";
ad = returnAdapter();
DbCommandBuilder builder = returnBuilder();
builder.DataAdapter = ad;
ad.SelectCommand = idbCommand;
ad.Fill(this.newDataSet.shirt);
ad.DeleteCommand = builder.GetDeleteCommand();
ad.UpdateCommand = builder.GetUpdateCommand();
ad.InsertCommand = builder.GetInsertCommand();
MySqlDataAdapter ad3;
}
/// <summary>
/// 更新table到数据库
/// </summary>
/// <param name="dt">要更新的表</param>
/// <param name="sql">要执行的查询语句</param>
/// <returns></returns>
public int UpdateTable(DataTable dt, string sql)
{
ErrorMessage = "";
int affect = 0;
DbConnection connection = providerFactory.CreateConnection();
using (DbCommand command = CreateDbCommand(sql, null, CommandType.Text))
{
using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
{
command.CommandType = CommandType.Text;
//command.CommandText = sql;
command.CommandText = dt.ExtendedProperties["SQL"].ToString();
adapter.SelectCommand = command;
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
using (DbCommandBuilder cb = providerFactory.CreateCommandBuilder())
{
try
{
cb.DataAdapter = adapter;
adapter.InsertCommand = cb.GetInsertCommand();
adapter.UpdateCommand = cb.GetUpdateCommand();
adapter.DeleteCommand = cb.GetDeleteCommand();
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
command.Connection.Open();
if (dt.GetChanges() != null)
{
affect = adapter.Update(dt.GetChanges());
dt.AcceptChanges();
}
command.Connection.Close();
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
affect = -1;
}
}
}
}
return(affect);
}
//Erzeugt einen DbDataAdapter für die Verbindung und den SQL-Befehl
//Benötigt eine DbProviderFactory, Eine Verbindung und ein SQL-Befehl
public static DbDataAdapter CreateDbDataAdapter(DbProviderFactory dbProviderFactory, DbConnection dbConnection, string sql)
{
DbCommand dbSelectCommand = dbProviderFactory.CreateCommand();
dbSelectCommand.Connection = dbConnection;
dbSelectCommand.CommandText = sql;
DbDataAdapter dbDataAdapter = dbProviderFactory.CreateDataAdapter();
dbDataAdapter.SelectCommand = dbSelectCommand;
DbCommandBuilder dbCommandBuilder = dbProviderFactory.CreateCommandBuilder();
dbCommandBuilder.DataAdapter = dbDataAdapter;
dbDataAdapter.InsertCommand = dbCommandBuilder.GetInsertCommand();
dbDataAdapter.UpdateCommand = dbCommandBuilder.GetUpdateCommand();
dbDataAdapter.DeleteCommand = dbCommandBuilder.GetDeleteCommand();
return(dbDataAdapter);
}
private void PerformDelete(DataSet pDs, List <string> pTableOrder)
{
foreach (string MyTableName in pTableOrder)
{
DataRow[] MyRows = pDs.Tables[MyTableName].Select("", "", DataViewRowState.Deleted);
if (MyRows.Length > 0)
{
m_CurrentUpdating = MyTableName;
DbDataAdapter MyDa = CreateDataAdapter(pDs, MyTableName);
DbCommandBuilder MyCB = CreateCommandBuilder(MyDa);
MyDa.DeleteCommand = MyCB.GetDeleteCommand();
MyDa.DeleteCommand.Transaction = m_Trans;
MyDa.UpdateBatchSize = m_BatchSize;
MyDa.Update(MyRows);
}
}
}
public void UpdateDistintaBaseTable(ArticoliDS ds)
{
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", ds.DIBA.TableName);
using (DbDataAdapter a = BuildDataAdapter(query))
{
InstallRowUpdatedHandler(a, UpdateDistintaBaseHander);
a.ContinueUpdateOnError = false;
DataTable dt = ds.DIBA;
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.AcceptChangesDuringFill = true;
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(dt);
}
}
/// <summary>
/// 批量更新
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool UpdateDataTable(DataTable dt, string selectSql)
{
bool resule = true;
if (dt == null || dt.Rows.Count <= 0)
{
return(resule);
}
this.Open();
DbTransaction trans = connection.BeginTransaction();
try
{
DbProviderFactory dbfactory = GetFactory();
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = selectSql;
DbDataAdapter dr = dbfactory.CreateDataAdapter();
dr.SelectCommand = cmd;
dr.SelectCommand.Transaction = trans;
DbCommandBuilder builder = dbfactory.CreateCommandBuilder();
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
builder.ConflictOption = ConflictOption.OverwriteChanges;
builder.SetAllValues = false;
builder.DataAdapter = dr;
dr.InsertCommand = builder.GetInsertCommand();
dr.UpdateCommand = builder.GetUpdateCommand();
dr.DeleteCommand = builder.GetDeleteCommand();
resule = dr.Update(dt) > 0;
// dt.AcceptChanges();
trans.Commit();
return(resule);
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception(ex.Message);
}
finally
{
this.Close();
}
}
public void UpdateTableSPControlli(SchedeProcessoDS ds)
{
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", ds.SPCONTROLLI.TableName);
using (DbDataAdapter a = BuildDataAdapter(query))
{
InstallRowUpdatedHandler(a, UpdateSPCOntrolloHander);
a.ContinueUpdateOnError = false;
DataTable dt = ds.SPCONTROLLI;
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.AcceptChangesDuringFill = true;
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(dt);
}
}
/// <summary>
/// Gets the adapter.
/// </summary>
/// <param name="factory">The factory.</param>
/// <param name="connectionConfiguration">The connection configuration.</param>
/// <param name="query">The query.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">factory;The expected provider factory is not here.</exception>
/// <exception cref="System.ArgumentException">
/// The DBMS Connection string was not specified.
/// or
/// The DBMS query was not specified.
/// </exception>
public static DbDataAdapter GetAdapter(DbProviderFactory factory, string connectionConfiguration, string query)
{
if (factory == null)
{
throw new ArgumentNullException("factory", "The expected provider factory is not here.");
}
if (string.IsNullOrEmpty(connectionConfiguration))
{
throw new ArgumentException("The DBMS Connection string was not specified.");
}
if (string.IsNullOrEmpty(query))
{
throw new ArgumentException("The DBMS query was not specified.");
}
DbDataAdapter adapter = factory.CreateDataAdapter();
if (string.IsNullOrEmpty(connectionConfiguration) && string.IsNullOrEmpty(query))
{
return(adapter);
}
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionConfiguration;
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = query;
selectCommand.Connection = connection;
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.SelectCommand = selectCommand;
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
return(adapter);
}
static void Main(string[] args)
{
//DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OracleClient");
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = SQL_CONN;
using (connection) {
//runClassicSelect(connection, factory);
string sql = "SELECT id, name FROM People";
DbCommand command = connection.CreateCommand();
command.CommandText = sql;
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
DataTable people = new DataTable("People");
adapter.Fill(people);
print(people);
people.Rows.Add(23, "Albert");
print(people);
Console.ReadKey();
adapter.Update(people);
foreach (DataRow row in people.Select("id = 23"))
{
row.Delete();
}
print(people);
Console.ReadKey();
adapter.Update(people);
}
Console.ReadKey();
}
protected virtual DbDataAdapter CreateDbDataAdapter(string tableName)
{
DbCommand dbSelectCommand = _aData.ProviderFactory.CreateCommand();
dbSelectCommand.Connection = _dbConnection;
dbSelectCommand.CommandText = string.Format("SELECT * FROM {0}", tableName);
DbDataAdapter dbDataAdapter = _aData.ProviderFactory.CreateDataAdapter();
dbDataAdapter.SelectCommand = dbSelectCommand;
// --- fertig, wenn keine schreibenden Datenbankzugriffe erforderlich ----
DbCommandBuilder dbCommandBuilder = _aData.ProviderFactory.CreateCommandBuilder();
dbCommandBuilder.DataAdapter = dbDataAdapter;
dbDataAdapter.InsertCommand = dbCommandBuilder.GetInsertCommand();
dbDataAdapter.UpdateCommand = dbCommandBuilder.GetUpdateCommand();
dbDataAdapter.DeleteCommand = dbCommandBuilder.GetDeleteCommand();
return(dbDataAdapter);
}
public void UpdateTable(DataSet dataSet)
{
int iAffected = 0;
try
{
ObjDataAdapter.SelectCommand.CommandText = MstrQuery;
ObjCommand.CommandType = CommandType.StoredProcedure;
ObjCommandBuilder.DataAdapter = ObjDataAdapter;
ObjDataAdapter.InsertCommand = ObjCommandBuilder.GetInsertCommand();
ObjDataAdapter.UpdateCommand = ObjCommandBuilder.GetUpdateCommand();
ObjDataAdapter.DeleteCommand = ObjCommandBuilder.GetDeleteCommand();
iAffected = ObjDataAdapter.Update(dataSet);
}
catch (Exception ex)
{
throw new SystemException(ex.Message, ex);
}
}
internal TableManager(string tableName)
{
try
{
_da = TableManager._DbProviderFactory.CreateDataAdapter();
_cmd = TableManager._DbProviderFactory.CreateCommand();
DbCommandBuilder cb = TableManager._DbProviderFactory.CreateCommandBuilder();
_cmd.Connection = TableManager.Connection;
cb.ConflictOption = ConflictOption.OverwriteChanges;
cb.DataAdapter = _da;
_dt = new DataTable();
_temp = new DataTable();
_dt.TableName = _temp.TableName = tableName;
_cmd.CommandText = "Select * from Voll_" + Table.TableName;
_da.SelectCommand = _cmd;
_da.InsertCommand = cb.GetInsertCommand();
_da.DeleteCommand = cb.GetDeleteCommand();
_da.UpdateCommand = cb.GetUpdateCommand();
Recharge("1 = 2");
}
catch { }
}
/// <summary>
/// 保持数据表
/// </summary>
/// <param name="dtSource">数据源 (数据源中的表名必须是实际的数据表名)</param>
/// <param name="conn"></param>
/// <param name="tran"></param>
public static void UpdateDataTable(DataTable dtSource, DbConnection conn, DbTransaction tran)
{
if (dtSource == null || conn == null || tran == null)
{
return;
}
try
{
string sqlCmd = string.Format("SELECT * FROM [{0}]", dtSource.TableName);
DbCommand dbCommand = CreateCommand(sqlCmd, conn, tran);
DbDataAdapter adapter = CreateAdapter(dbCommand);
DbCommandBuilder cmdBuilder = CreateCommandBuilder(adapter);
adapter.DeleteCommand = cmdBuilder.GetDeleteCommand();
adapter.InsertCommand = cmdBuilder.GetInsertCommand();
adapter.UpdateCommand = cmdBuilder.GetUpdateCommand();
adapter.Update(dtSource);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 生成Command
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
/// <summary>
/// 生成DbDataAdapter
/// </summary>
/// <param name="selectCommand"></param>
/// <param name="type"></param>
/// <returns></returns>
public IDbDataAdapter CreateDbDataAdapter(IDbCommand selectCommand, DbCommandType type)
{
var adapt = DbProviderFactory.CreateDataAdapter();
adapt.SelectCommand = selectCommand as DbCommand;
DbCommandBuilder builder = DbProviderFactory.CreateCommandBuilder();
builder.DataAdapter = adapt;
adapt.InsertCommand = ((type & DbCommandType.InsertCommand) == DbCommandType.InsertCommand) ?
builder.GetInsertCommand() : adapt.InsertCommand;
adapt.UpdateCommand = ((type & DbCommandType.UpdateCommand) == DbCommandType.UpdateCommand) ?
builder.GetUpdateCommand() : adapt.UpdateCommand;
adapt.DeleteCommand = ((type & DbCommandType.DeleteCommand) == DbCommandType.DeleteCommand) ?
builder.GetDeleteCommand() : adapt.DeleteCommand;
adapt.SelectCommand = ((type & DbCommandType.SelectCommand) == DbCommandType.SelectCommand) ?
adapt.SelectCommand : null;
return(adapt);
}
public int Update(DataTable dt)
{
if (String.IsNullOrEmpty(dt.TableName))
{
throw new Exception("没有表名");
}
//貌似能自动打开释放连接
DbDataAdapter adapter = null;
DataTable tempDt = GetDataTable(dt.TableName, 1); //为了获取表的列数
if (dt.PrimaryKey.Length == 0 || (tempDt.Columns.Count != dt.Columns.Count)) //如果没有主键或者传入的表列不完整则调用自定义的构造
{
adapter = CreateAdapter(dt);
}
else
{
DbCommand cmd = this.DbProviderFactoryInstance.CreateCommand();
cmd.CommandText = "select * from " + dt.TableName;
adapter = this.DbProviderFactoryInstance.CreateDataAdapter();
adapter.SelectCommand = cmd;
DbCommandBuilder commandBuilder = this.DbProviderFactoryInstance.CreateCommandBuilder();
commandBuilder.DataAdapter = adapter;
adapter.DeleteCommand = commandBuilder.GetDeleteCommand(true);
adapter.InsertCommand = commandBuilder.GetInsertCommand(true);
adapter.UpdateCommand = commandBuilder.GetUpdateCommand(true);
}
adapter.InsertCommand.CommandText = adapter.InsertCommand.CommandText;
using (TransactionScope ts = new TransactionScope())
{
int result = adapter.Update(dt);
ts.Complete();
dt.AcceptChanges();
return(result);
}
}
public DbDataAdapter create_synch_adapter(string sel_sql, string qte_prefix = "[", string qte_suff = "]", bool only_insert = false
, DbCommand del_cmd = null, DbCommand upd_cmd = null)
{
DbCommand cmd_upd = null, cmd_del = null;
DbCommandBuilder cmdb = null;
try {
DbCommand cc = _conn.CreateCommand();
cc.CommandText = sel_sql;
cc.CommandType = CommandType.Text;
DbProviderFactory fctry = DbProviderFactories.GetFactory(_prov_name);
DbDataAdapter da = fctry.CreateDataAdapter();
da.SelectCommand = cc;
cmdb = fctry.CreateCommandBuilder();
cmdb.DataAdapter = da;
cmdb.QuotePrefix = qte_prefix;
cmdb.QuoteSuffix = qte_suff;
if (!only_insert)
{
cmd_upd = upd_cmd != null ? upd_cmd : cmdb.GetUpdateCommand(); da.UpdateCommand = cmd_upd;
}
if (!only_insert)
{
cmd_del = del_cmd != null ? del_cmd : cmdb.GetDeleteCommand(); da.DeleteCommand = cmd_del;
}
da.InsertCommand = cmdb.GetInsertCommand();
return(da);
} catch (Exception ex) {
logInfo("command builder: " + obj_dump.dump(cmdb));
logInfo("update command: " + obj_dump.dump(cmd_upd));
logInfo("delete command: " + obj_dump.dump(cmd_del));
logErr(ex); throw ex;
} finally { }
}
/// <summary>
/// 更新单张表，无需添加事务
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int Update(DataTable dt)
{
using (DbConnection connection = provider.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand cmd = provider.CreateCommand())
{
string relTableName = dt.TableName;
string SQLString = string.Format("select * from {0} ", relTableName);
cmd.Connection = connection;
cmd.CommandText = SQLString;
try
{
DbDataAdapter adapter = provider.CreateDataAdapter();
DbCommandBuilder objCommandBuilder = provider.CreateCommandBuilder();
adapter.SelectCommand = cmd;
objCommandBuilder.DataAdapter = adapter;
adapter.DeleteCommand = objCommandBuilder.GetDeleteCommand();
adapter.InsertCommand = objCommandBuilder.GetInsertCommand();
adapter.UpdateCommand = objCommandBuilder.GetUpdateCommand();
foreach (DataRow Row in dt.Rows)
{
Row.EndEdit();
}
int count = adapter.Update(dt);
dt.AcceptChanges();
return(count);
}
catch (DbException ex)
{
connection.Close();
connection.Dispose();
throw new Exception(ex.Message);
}
}
}
}
public void UpdateComponentiTable(string tablename, DataRow[] drs)
{
try
{
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", tablename);
using (DbDataAdapter a = BuildDataAdapter(query))
{
InstallRowUpdatedHandler(a, UpdateComponentiHander);
a.ContinueUpdateOnError = false;
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.AcceptChangesDuringFill = true;
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(drs);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void VerwijderCursussen(List <int> ids)
{
string query = "SELECT * FROM dbo.cursus";
DataSet ds = new DataSet();
DbConnection connection = getConnection();
using (DbDataAdapter adapter = sqlFactory.CreateDataAdapter())
{
try
{
DbCommandBuilder builder = sqlFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.SelectCommand = sqlFactory.CreateCommand();
adapter.SelectCommand.CommandText = query;
adapter.SelectCommand.Connection = connection;
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.FillSchema(ds, SchemaType.Source, "cursus");
adapter.Fill(ds, "cursus");
foreach (int id in ids)
{
DataRow r = ds.Tables["cursus"].Rows.Find(id);
r.Delete();
}
adapter.Update(ds, "cursus");
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
}
public bool runQuery(string target, string queryType, Dictionary <string, string[]> values, string condition = "")
{
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory(DbProvider);
DbConnection conn = factory.CreateConnection();
DataTable table = new DataTable();
conn.ConnectionString = connectionStringPrime;
using (conn)
{
string queryString = "SELECT * FROM " + target;
DbCommand dbcommand = factory.CreateCommand();
dbcommand.CommandText = queryString;
dbcommand.Connection = conn;
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = dbcommand;
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.Fill(table);
if (queryType == "INSERT")
{
DataRow newRow = table.NewRow();
foreach (var item in values)
{
if (item.Value[0] == "STRING")
{
newRow[item.Key] = item.Value[1];
}
else if (item.Value[0] == "DOUBLE")
{
newRow[item.Key] = Convert.ToDouble(item.Value[1]);
}
else if (item.Value[0] == "INT")
{
newRow[item.Key] = Convert.ToInt32(item.Value[1]);
}
else if (item.Value[0] == "DATETIME")
{
newRow[item.Key] = Convert.ToDateTime(item.Value[1]);
}
else if (item.Value[0] == "BOOL")
{
newRow[item.Key] = Convert.ToBoolean(item.Value[1]);
}
}
table.Rows.Add(newRow);
adapter.Update(table);
}
else if (queryType == "UPDATE")
{
DataRow[] editRow = table.Select(condition);
int length = editRow.Length;
for (int i = 0; i < length; i++)
{
foreach (var item in values)
{
if (item.Value[0] == "STRING")
{
editRow[i][item.Key] = item.Value[1];
}
else if (item.Value[0] == "DOUBLE")
{
editRow[i][item.Key] = Convert.ToDouble(item.Value[1]);
}
else if (item.Value[0] == "INT")
{
editRow[i][item.Key] = Convert.ToInt32(item.Value[1]);
}
else if (item.Value[0] == "DATETIME")
{
editRow[i][item.Key] = Convert.ToDateTime(item.Value[1]);
}
else if (item.Value[0] == "BOOL")
{
editRow[i][item.Key] = Convert.ToBoolean(item.Value[1]);
}
}
}
adapter.Update(table);
}
else if (queryType == "DELETE")
{
DataRow[] deleteRow = table.Select(condition);
foreach (DataRow row in deleteRow)
{
row.Delete();
}
adapter.Update(table);
}
}
return(true);
}
catch (Exception ex)
{
ErrorHandler e = new ErrorHandler(ex);
e.PrintError();
return(false);
}
}
public DataTable dynamicQueries <T>(string queryType, object objectClass, KeyValuePair <string, string[, ]> table)
{
//return dalk n Datatable. As dit insert, delete, of update is, return null
DataTable returnTable = null;
try
{
StringBuilder queryString = new StringBuilder();
queryString.Append("SELECT ");
for (int i = 0; i < table.Value.GetLength(0); i++)
{
if (i != table.Value.GetLength(0) - 1)
{
queryString.Append(table.Value[i, 0] + ", ");
}
else
{
queryString.Append(table.Value[i, 0] + " ");
}
}
queryString.Append("FROM ");
queryString.Append(table.Key);
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
// Create the DbCommand.
DbCommand command = factory.CreateCommand();
command.CommandText = queryString.ToString();
command.Connection = connection;
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
if (queryType == "SELECT")
{
// Fill the DataTable.
returnTable = new DataTable();
adapter.Fill(returnTable);
return(returnTable);
}
if (queryType != "SELECT")
{
// Create the DbCommandBuilder.
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
// Cast it to itself
T spesificClass = (T)Convert.ChangeType(objectClass, typeof(T));
if (queryType == "INSERT")
{
// Get the insert commands.
adapter.InsertCommand = builder.GetInsertCommand();
System.Diagnostics.Debug.WriteLine(adapter.InsertCommand.CommandText);
// Fill the DataTable.
returnTable = new DataTable();
adapter.Fill(returnTable);
// Insert a new row.
DataRow newRow = returnTable.NewRow();
for (int i = 0; i < table.Value.GetLength(0); i++)
{
//PropertyInfo info = spesificClass.GetType().GetProperty(table.Value[i,1]);
//newRow[table.Value[i, 0]] = info.GetValue(spesificClass);
string dbColumn = table.Value[i, 0];
string propName = table.Value[i, 1];
var value = spesificClass.GetType().GetProperty(propName).GetValue(spesificClass);
newRow[dbColumn] = value;
}
returnTable.Rows.Add(newRow);
adapter.Update(returnTable);
return(returnTable);
}
else
if (queryType == "UPDATE")
{
// Get the update commands.
adapter.UpdateCommand = builder.GetUpdateCommand();
System.Diagnostics.Debug.WriteLine(adapter.UpdateCommand.CommandText);
// Fill the DataTable.
returnTable = new DataTable();
adapter.Fill(returnTable);
// Edit an existing row.
//DataRow[] editRow = returnTable.Select("CustomerID = 'XYZZZ'");
//editRow[0]["CompanyName"] = "XYZ Corporation";
// Update the row based on GUID
DataRow[] editRow = returnTable.Select(string.Format("globalUniqueID = '{0}'", spesificClass.GetType().GetProperty("GUID").GetValue(spesificClass)));
//editRow[0]["CompanyName"] = "XYZ Corporation";
for (int i = 0; i < table.Value.GetLength(0); i++)
{
editRow[0][table.Value[i, 0]] = spesificClass.GetType().GetProperty(table.Value[i, 1]).GetValue(spesificClass);
}
adapter.Update(returnTable);
return(returnTable);
}
else
if (queryType == "DELETE")
{
// Get the delete commands.
adapter.DeleteCommand = builder.GetDeleteCommand();
System.Diagnostics.Debug.WriteLine(adapter.DeleteCommand.CommandText);
// Fill the DataTable.
returnTable = new DataTable();
adapter.Fill(returnTable);
// Delete a row.
DataRow[] deleteRow = returnTable.Select(string.Format("globalUniqueID = '{0}'", spesificClass.GetType().GetProperty("GUID").GetValue(spesificClass)));
foreach (DataRow row in deleteRow)
{
row.Delete();
}
adapter.Update(returnTable);
return(returnTable);
}
}
}
catch (Exception e)
{
FileHandlerTxt FHandler = new FileHandlerTxt();
FHandler.appendDataToTextFile(new List <string> {
string.Format("Exception {0} on {1}", e.Message, DateTime.UtcNow.ToLongDateString())
});
}
return(returnTable);
}
public DbCommand GetDeleteCommand(DbTransaction transaction, string tableName)
{
return(_commandBuilder.GetDeleteCommand(transaction, tableName));
}
/// <summary>
/// Saves changes made to table data to the database.
/// </summary>
/// <param name="table">DataTable with changes.</param>
/// <param name="selectQuery">SELECT SQL query which was used to read this table.</param>
/// <returns>Returns true if save was successful and returns false otherwise.</returns>
public bool UpdateTable(DataTable table, string selectQuery)
{
// Validate inputs
if (table == null)
{
throw new ArgumentNullException("table");
}
if (String.IsNullOrEmpty(selectQuery))
{
throw new ArgumentException(Resources.Error_EmptyString, "selectQuery");
}
Debug.Assert(Connection != null, "Connection is not initialized!");
// Extract changes from table
DataTable changes = table.GetChanges();
// If no changes detected, return true
if (changes == null)
{
return(true);
}
// Retrieving connection
DbConnection conn = GetConnection();
DbTransaction transaction = null;
try
{
// Ensure the connection is open
EnsureConnectionIsOpen();
// Start transaction
transaction = conn.BeginTransaction();
// Create command object
DbCommand comm = conn.CreateCommand();
comm.CommandText = selectQuery;
comm.Transaction = transaction;
// Create a data adapter and attach it to command
DbDataAdapter adapter = CreateDataAdapter();
adapter.SelectCommand = comm;
// Create a command builder and attach it to adapter
DbCommandBuilder builder = CreateCommandBuilder(adapter);
// Build update commands
// If there are deleted rows, create delete command
DataRow[] selection = table.Select(String.Empty, String.Empty, DataViewRowState.Deleted);
if (selection != null && selection.Length > 0)
{
adapter.DeleteCommand = builder.GetDeleteCommand();
}
// If there are modified rows, create update command
selection = table.Select(String.Empty, String.Empty, DataViewRowState.ModifiedCurrent);
if (selection != null && selection.Length > 0)
{
adapter.UpdateCommand = builder.GetUpdateCommand();
}
// If there are new rows, create insert command
selection = table.Select(String.Empty, String.Empty, DataViewRowState.Added);
if (selection != null && selection.Length > 0)
{
adapter.InsertCommand = builder.GetInsertCommand();
}
// Attach adapter commands to transactions
if (adapter.UpdateCommand != null)
{
adapter.UpdateCommand.Transaction = transaction;
}
if (adapter.InsertCommand != null)
{
adapter.InsertCommand.Transaction = transaction;
}
if (adapter.DeleteCommand != null)
{
adapter.DeleteCommand.Transaction = transaction;
}
// Saves data
adapter.Update(changes);
// Release resources
builder.Dispose();
adapter.Dispose();
// Commit transaction
transaction.Commit();
// Return results
return(true);
}
catch
{
// Try to ping connection after error to force socket recreation
TryToPingConnection(conn);
// On any error rolback transaction if any
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
Connection.UnlockProviderObject();
}
}
/// <summary>
/// Update.
/// </summary>
/// <param name="database"></param>
/// <param name="tables"></param>
/// <param name="tableNames"></param>
/// <param name="identity"></param>
/// <param name="transaction"></param>
private static void Update(Database database, DataTable[] tables, string[] tableNames, string identity, DbTransaction transaction)
{
#region Validations
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (tables == null)
{
throw new ArgumentNullException("ds");
}
if (tableNames == null)
{
throw new ArgumentNullException("tableNames");
}
if (tables.Length != tableNames.Length)
{
throw new ArgumentException("tables and tableNames parameters cannot differ in size");
}
#endregion
// retrieve connection
string cnName = GetDbFactory(database);
// retrieve connection
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[cnName];
DbProviderFactory factory = DbProviderFactories.GetFactory(settings.ProviderName);
// create adapter
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
// create commands
for (int i = 0; i < tableNames.Length; ++i)
{
using (DbCommand select = factory.CreateCommand())
{
string tableName = tableNames[i];
select.CommandText = string.Format("SELECT * FROM {0}", tableName);
select.Connection = transaction.Connection;
select.Transaction = transaction;
using (DbCommandBuilder builder = factory.CreateCommandBuilder())
{
// get table
DataTable table = tables[i];
adapter.SelectCommand = select;
builder.DataAdapter = adapter;
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.InsertCommand = builder.GetInsertCommand();
if (!string.IsNullOrEmpty(identity))
{
GenerateIdentityUpdate(factory, table, adapter, identity);
}
// update
adapter.Update(table);
}
}
}
}
}
public void BuildCommands()
{
var dbFactory = new DbFactory(SqlClientFactory.Instance);
var cmdGenerator = new DbCommandBuilder(dbFactory);
var q = new Query(new QTable("test", "t"));
q.Condition = createTestQuery();
q.Fields = new QField[] { "name", "t.age", new QField("age_months", "t.age*12") };
// SELECT TEST with prefixes and expressions
IDbCommand cmd = cmdGenerator.GetSelectCommand(q);
string masterSQL = "SELECT name,t.age,t.age*12 as age_months FROM test t WHERE (((name LIKE @p0) Or (NOT(age>=@p1))) And ((weight=@p2) And (type IN (@p3,@p4)))) Or ((name<>@p5) And (type IS NOT NULL))";
Assert.Equal(masterSQL, cmd.CommandText.Trim());
// SELECT WITH TABLE ALIAS TEST
cmd = cmdGenerator.GetSelectCommand(
new Query("accounts.a",
new QConditionNode((QField)"a.id", Conditions.In,
new Query("dbo.accounts.b", (QField)"a.id" != (QField)"b.id"))));
masterSQL = "SELECT * FROM accounts a WHERE a.id IN (SELECT * FROM dbo.accounts b WHERE a.id<>b.id)";
Assert.Equal(masterSQL, cmd.CommandText);
var testData = new Dictionary <string, object> {
{ "name", "Test" },
{ "age", 20 },
{ "weight", 75.6 },
{ "type", "staff" }
};
// INSERT TEST
cmd = cmdGenerator.GetInsertCommand("test", testData);
masterSQL = "INSERT INTO test (name,age,weight,type) VALUES (@p0,@p1,@p2,@p3)";
Assert.Equal(cmd.CommandText, masterSQL);
Assert.Equal(cmd.Parameters.Count, 4);
// UPDATE TEST
cmd = cmdGenerator.GetUpdateCommand(new Query("test", (QField)"name" == (QConst)"test"), testData);
masterSQL = "UPDATE test SET name=@p0,age=@p1,weight=@p2,type=@p3 WHERE name=@p4";
Assert.Equal(cmd.CommandText, masterSQL);
Assert.Equal(cmd.Parameters.Count, 5);
// UPDATE TEST (by query)
var changes = new Dictionary <string, IQueryValue>()
{
{ "age", (QConst)21 }, { "name", (QConst)"Alexandra" }
};
cmd = cmdGenerator.GetUpdateCommand(new Query("test", (QField)"id" == (QConst)1), changes);
masterSQL = "UPDATE test SET age=@p0,name=@p1 WHERE id=@p2";
Assert.Equal(masterSQL, cmd.CommandText);
Assert.Equal(3, cmd.Parameters.Count);
// DELETE BY QUERY TEST
cmd = cmdGenerator.GetDeleteCommand(new Query("test", (QField)"id" == (QConst)5));
masterSQL = "DELETE FROM test WHERE id=@p0";
Assert.Equal(cmd.CommandText, masterSQL);
Assert.Equal(cmd.Parameters.Count, 1);
// ------- escape identifiers asserts --------
dbFactory.IdentifierFormat = "[{0}]";
Assert.Equal(
"SELECT [name],[t].[age],t.age*12 as [age_months] FROM [test] [t] WHERE ((([name] LIKE @p0) Or (NOT([age]>=@p1))) And (([weight]=@p2) And ([type] IN (@p3,@p4)))) Or (([name]<>@p5) And ([type] IS NOT NULL))",
cmdGenerator.GetSelectCommand(q).CommandText.Trim());
Assert.Equal(
"INSERT INTO [test] ([name],[age],[weight],[type]) VALUES (@p0,@p1,@p2,@p3)",
cmdGenerator.GetInsertCommand("test", testData).CommandText);
Assert.Equal(
"UPDATE [test] SET [name]=@p0,[age]=@p1,[weight]=@p2,[type]=@p3 WHERE [name]=@p4",
cmdGenerator.GetUpdateCommand(new Query("test", (QField)"name" == (QConst)"test"), testData).CommandText);
Assert.Equal(
"DELETE FROM [test] WHERE [id]=@p0",
cmdGenerator.GetDeleteCommand(new Query("test", (QField)"id" == (QConst)5)).CommandText);
}
static void CreateDataAdapter2(DbProviderFactory factory, string connectionString)
{
try {
// Create the DbProviderFactory and DbConnection.
//DbProviderFactory factory =
// DbProviderFactories.GetFactory(providerName);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
using (connection) {
// Define the query.
string queryString =
"SELECT CustomerID, CompanyName FROM Customers";
// Create the select command.
DbCommand command = factory.CreateCommand();
command.CommandText = queryString;
command.Connection = connection;
// Create the DbDataAdapter.
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
// Create the DbCommandBuilder.
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
// Get the insert, update and delete commands.
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
// Display the CommandText for each command.
Trace.WriteLine("InsertCommand: {0}",
adapter.InsertCommand.CommandText);
Trace.WriteLine("UpdateCommand: {0}",
adapter.UpdateCommand.CommandText);
Trace.WriteLine("DeleteCommand: {0}",
adapter.DeleteCommand.CommandText);
// Fill the DataTable.
DataTable table = new DataTable();
adapter.Fill(table);
// Insert a new row.
DataRow newRow = table.NewRow();
newRow["CustomerID"] = "XYZZZ";
newRow["CompanyName"] = "XYZ Company";
table.Rows.Add(newRow);
adapter.Update(table);
// Display rows after insert.
Trace.WriteLine(string.Empty);
Trace.WriteLine("----List All Rows-----");
foreach (DataRow row in table.Rows)
{
Trace.WriteLine(row[0] + " " + row[1]);
}
Trace.WriteLine("----After Insert-----");
// Edit an existing row.
DataRow[] editRow = table.Select("CustomerID = 'XYZZZ'");
editRow[0]["CompanyName"] = "XYZ Corporation";
adapter.Update(table);
// Display rows after update.
Trace.WriteLine(string.Empty);
foreach (DataRow row in table.Rows)
{
Trace.WriteLine(row[0] + " " + row[1]);
}
Trace.WriteLine("----After Update-----");
// Delete a row.
DataRow[] deleteRow = table.Select("CustomerID = 'XYZZZ'");
foreach (DataRow row in deleteRow)
{
row.Delete();
}
adapter.Update(table);
// Display rows after delete.
Trace.WriteLine(string.Empty);
foreach (DataRow row in table.Rows)
{
Trace.WriteLine(row[0] + " " + row[1]);
}
Trace.WriteLine("----After Delete-----");
Trace.WriteLine("Customer XYZZZ was deleted.");
}
} catch (Exception ex) {
Console.WriteLine(ex.Message);
}
}
