protected void ConfigureAdapterCommands(DataTable dt, DbDataAdapter da, DbCommandBuilder cb)
{
bool isIdentity;
string identityField;
switch (DBEngine)
{
case DBEngines.MSSqlServer:
break;
case DBEngines.Oracle:
{
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();
OracleDbType identityType;
MapOracleIdentityField(dt, out isIdentity, out identityField, out identityType);
if (isIdentity)
{
OracleDataAdapter oDa = (OracleDataAdapter)da;
da.InsertCommand.CommandText += string.Format(" RETURNING {0}{1}{2} INTO :{3}{4}",
TablePrefix, identityField.ToUpper(), TableSuffix,
OracleParamIdentityPrefix, identityField);
oDa.InsertCommand.Parameters.Add(OracleParamIdentityPrefix + identityField, identityType).Direction = ParameterDirection.Output;
}
else
{
da.InsertCommand = cb.GetInsertCommand();
}
break;
}
}
}
private void PrepareCommand(IDbCommand command, IDbConnection
connection,
IDbTransaction transaction, CommandType commandType, string
commandText,
IDbDataParameter[] commandParameters, CommandBuilderType commandBuilderType = CommandBuilderType.NONE)
{
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
if (iDbCommandBuilder != null)
{
switch (commandBuilderType)
{
case CommandBuilderType.InsertCommand:
iDbCommandBuilder.GetInsertCommand();
break;
case CommandBuilderType.DeleteCommand:
iDbCommandBuilder.GetDeleteCommand();
break;
case CommandBuilderType.UpdateCommand:
iDbCommandBuilder.GetUpdateCommand();
break;
case CommandBuilderType.InsertUpdateCommand:
iDbCommandBuilder.GetInsertCommand();
iDbCommandBuilder.GetUpdateCommand();
break;
case CommandBuilderType.NONE:
break;
default:
break;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
/* create a DbCommandBuilder and link it to the adapter */
DbCommandBuilder commandBuilder = factory.CreateCommandBuilder();
commandBuilder.DataAdapter = adapter;
using (DbConnection con = factory.CreateConnection())
{
con.ConnectionString = cs;
con.Open();
/* The SelectCommand is already defined in the MainForm_Load */
adapter.SelectCommand.Connection = con;
/* link the auto-generated commands to the adapter */
adapter.InsertCommand = commandBuilder.GetInsertCommand();
adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
/* instruct adapter to refresh the autoincrement primary key back to the client
* NOTE: this will not work for the OleDb and MS Access. It should work with
* other datasources such as MS SQL, Oracle etc, though. */
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
adapter.InsertCommand.CommandText += Environment.NewLine + "SELECT @@IDENTITY as ID";
MessageBox.Show(adapter.InsertCommand.CommandText);
adapter.Update(table);
}
}
public void UpdateTable(string tablename, EstraiProdottiFinitiDS ds, string tabellaFisica)
{
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", tabellaFisica);
using (DbDataAdapter a = BuildDataAdapter(query))
{
try
{
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);
}
catch (DBConcurrencyException ex)
{
}
catch
{
throw;
}
}
}
/// <summary>
/// 创建数据适配器
/// </summary>
/// <param name="connectionInfo">连接信息</param>
/// <param name="tableName">表名</param>
/// <param name="update">是否更新表</param>
/// <returns>数据适配器</returns>
public IDbDataAdapter CreateDataAdapter(string tableName
, bool update = true)
{
const string accessDbFactoryName = "System.Data.OleDb";
var commandInfo = new DataCommandInfo()
{
Sql = String.Format("SELECT * FROM {0} WHERE 1=0", tableName)
};
DbCommand command = CreateDataCommand(commandInfo);
DbDataAdapter adapter = CreateDataAdapter();
adapter.SelectCommand = command;
if (update)
{
DbCommandBuilder commandBuilder = Factory.CreateCommandBuilder();
commandBuilder.DataAdapter = adapter;
/// TODO: ACCESS数据库的更新、插入语句需要加[]，暂时的处理方法
if (DbFactoryName == accessDbFactoryName)
{
commandBuilder.QuotePrefix = "[";
commandBuilder.QuoteSuffix = "]";
}
adapter.InsertCommand = commandBuilder.GetInsertCommand();
adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
}
return(adapter);
}
private void AddLocaties(DbConnection connection, DbDataAdapter adapter, List <Adres> adresses)
{
string queryLocatie = "SELECT * FROM dbo.adresLocatieTest";
DbCommand commandLocatie = sqlFactory.CreateCommand();
commandLocatie.CommandText = queryLocatie;
commandLocatie.Connection = connection;
adapter.SelectCommand = commandLocatie;
DbCommandBuilder builder = sqlFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
DataTable table = new DataTable();
adapter.Fill(table);
DataColumn[] keyColumns = new DataColumn[1]; // primary key voor deze kolom instellen
keyColumns[0] = table.Columns["Id"]; //idem
table.PrimaryKey = keyColumns; //idem
foreach (Adres adres in adresses)
{
if (!table.Rows.Contains(adres.locatie.ID))
{
DataRow row = table.NewRow();
row["Id"] = adres.locatie.ID;
row["x"] = adres.locatie.x;
row["y"] = adres.locatie.y;
table.Rows.Add(row);
}
}
adapter.Update(table);
}
private void buttonSave_Click(object sender, RoutedEventArgs e)
{
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SQLite");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = "Data Source =D:\\person.db";
using (connection)
{
DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM person";
command.CommandType = CommandType.Text;
command.Connection = connection;
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();
adapter.Update(table);
}
MessageBox.Show("Saved/");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void UpdateTable(string tablename, EtichetteDS ds)
{
string query = "SELECT * FROM ETICHETTELV";
using (DbDataAdapter a = BuildDataAdapter(query))
{
try
{
a.ContinueUpdateOnError = false;
DataTable dt = ds.Tables["ETI_ARTICOLI"];
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(dt);
}
catch (DBConcurrencyException ex)
{
}
catch
{
throw;
}
}
}
internal virtual int InsertDataTable(string tableName, DataTable dataTable)
{
DbDataAdapter dbDA = DbProviderFactories.GetFactory(_providerName).CreateDataAdapter();
DbCommandBuilder cmdb = DbProviderFactories.GetFactory(_providerName).CreateCommandBuilder();
cmdb.DataAdapter = dbDA;
dbDA.ContinueUpdateOnError = false;
dbDA.SelectCommand = _connection.CreateCommand();
dbDA.SelectCommand.Transaction = _transaction;
dbDA.SelectCommand.CommandType = CommandType.Text;
dbDA.SelectCommand.CommandText = string.Format("select {0} from {1}", GetColumnNames(dataTable), tableName);
dbDA.InsertCommand = cmdb.GetInsertCommand();
dbDA.InsertCommand.Connection = _connection;
dbDA.InsertCommand.Transaction = _transaction;
foreach (DataRow row in dataTable.Rows)
{
if (row.RowState == DataRowState.Unchanged)
{
row.SetAdded();
}
}
return(dbDA.Update(dataTable));
}
private void AddAdres(DbConnection connection, DbDataAdapter adapter, List <Adres> adresses)
{
string queryAdres = "SELECT * FROM dbo.AdresTest";
DbCommand commandAdres = sqlFactory.CreateCommand();
commandAdres.CommandText = queryAdres;
commandAdres.Connection = connection;
adapter.SelectCommand = commandAdres;
DbCommandBuilder builder = sqlFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
DataTable table = new DataTable();
adapter.Fill(table);
DataColumn[] keyColumns = new DataColumn[1]; // primary key voor deze kolom instellen
keyColumns[0] = table.Columns["ID"]; //idem
table.PrimaryKey = keyColumns; //idem
foreach (Adres adres in adresses)
{
if (!table.Rows.Contains(adres.ID))
{
DataRow row = table.NewRow();
row["ID"] = adres.ID;
row["straatnaamID"] = adres.straatnaam.ID;
row["huisnummer"] = adres.huisnummer;
row["appartementnummer"] = adres.appartementnummer;
row["busnummer"] = adres.busnummer;
row["huisnummerlabel"] = adres.huisnummerlabel;
row["adreslocatieID"] = adres.locatie.ID;
table.Rows.Add(row);
}
}
adapter.Update(table);
}
internal TableManager(string tableName)
{
try
{
adapter = DbManager.ProviderFactory.CreateDataAdapter();
command = DbManager.ProviderFactory.CreateCommand();
DbCommandBuilder cb = DbManager.ProviderFactory.CreateCommandBuilder();
command.Connection = DbManager.Connection;
cb.ConflictOption = ConflictOption.OverwriteChanges;
cb.DataAdapter = adapter;
table = new DataTable();
temp = new DataTable();
table.TableName = temp.TableName = tableName;
command.CommandText = "SELECT * FROM DBTest_" + tableName;
adapter.SelectCommand = command;
adapter.InsertCommand = cb.GetInsertCommand();
adapter.DeleteCommand = cb.GetDeleteCommand();
adapter.UpdateCommand = cb.GetUpdateCommand();
Recharge("1 = 2");
}
catch (Exception e)
{
LogWriter.Log($"Failed to configure manager for table '{tableName}'. \nException message: {e.Message}");
}
}
public static DbDataAdapter CrearDbDataAdapter(string proveedor, DbCommand ordenSe)
{
DbDataAdapter adaptador = null;
try
{
// Crear DbProviderFactory y DbConnection.
DbProviderFactory factoria = DbProviderFactories.GetFactory(proveedor);
// Crear el objeto DbDataAdapter
adaptador = factoria.CreateDataAdapter();
adaptador.SelectCommand = ordenSe;
DbCommandBuilder cb = factoria.CreateCommandBuilder();
// Objeto DbDataAdapter para el que se generan
// automáticamente instrucciones SQL
cb.DataAdapter = adaptador;
adaptador.InsertCommand = cb.GetInsertCommand();
adaptador.DeleteCommand = cb.GetDeleteCommand();
adaptador.UpdateCommand = cb.GetUpdateCommand();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(adaptador);
}
internal TableManager(string tableName)
{
try
{
_da = _DbProviderFactory.CreateDataAdapter();
_cmd = _DbProviderFactory.CreateCommand();
DbCommandBuilder cb = _DbProviderFactory.CreateCommandBuilder();
_cmd.Connection = Connection;
cb.ConflictOption = ConflictOption.OverwriteChanges;
cb.DataAdapter = _da;
_dt = new DataTable();
_temp = new DataTable();
_dt.TableName = _temp.TableName = tableName;
_cmd.CommandText = "SELECT * FROM CAFEBD." + Table.TableName;
_da.SelectCommand = _cmd;
_da.InsertCommand = cb.GetInsertCommand();
_da.DeleteCommand = cb.GetDeleteCommand();
Recharge("1 = 2");
}
catch (Exception e)
{
Console.WriteLine("3");
Console.WriteLine(e);
}
}
public int Update(DataTable dt, DbCommand cmd)
{
try
{
string relTableName = dt.TableName;
string SQLString = string.Format("select * from {0} ", relTableName);
cmd.CommandText = SQLString;
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 (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void UpdateDataTable(string sql, DataTable dt)
{
using (DbCommand cmd = _cn.CreateCommand())
{
cmd.Connection = _cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.CommandTimeout = CommandTimeout;
using (DbDataAdapter da = _factory.CreateDataAdapter())
{
da.SelectCommand = cmd;
DbCommandBuilder cb = _factory.CreateCommandBuilder();
cb.DataAdapter = da;
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.Update(dt);
}
}
return;
}
//excel
public void Update_EXCEL_Table(CDCDS ds)
{
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM $T<{0}>", ds.CDC_EXCEL.TableName);
using (DbDataAdapter a = BuildDataAdapter(query))
{
try
{
InstallRowUpdatedHandler(a, Update_EXCEL_ElementHandler);
a.ContinueUpdateOnError = false;
DataTable dt = ds.CDC_EXCEL;
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(dt);
}
catch (DBConcurrencyException ex)
{
}
catch
{
throw;
}
}
}
public DbDataAdapter GetAdapter(DbCommand SelectCommand, bool BuildCommands)
{
DbDataAdapter objAdapter = null;
DbCommandBuilder objCommandBuilder = null;
try
{
objAdapter = this.GetAdapter(SelectCommand);
if (objAdapter != null)
{
if (BuildCommands == true)
{
InitializeCommandBuilder(ref objCommandBuilder);
if (objCommandBuilder != null)
{
objCommandBuilder.DataAdapter = objAdapter;
objAdapter.InsertCommand = objCommandBuilder.GetInsertCommand();
objAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand();
objAdapter.DeleteCommand = objCommandBuilder.GetDeleteCommand();
}
}
}
}
catch (Exception ex)
{
throw new Exception("GetAdapter: " + ex.Message);
}
finally
{
}
return(objAdapter);
}
internal DaoErrMsg InsertDateTable(DataTable dt, string InsertTableName)
{
DbCommand DbCmd = DbProviderFactories.GetFactory(m_DbConnection.ToString().RemoveLastIndexof('.')).CreateCommand();
DbCmd.CommandText = "SELECT * FROM " + InsertTableName;
DbCmd.Connection = m_DbConnection;
DbDataAdapter DbAdapter = DbProviderFactories.GetFactory(m_DbConnection.ToString().RemoveLastIndexof('.')).CreateDataAdapter();
DbAdapter.SelectCommand = DbCmd;
//DbCmd.ExecuteNonQuery();
try
{
using (DbCommandBuilder Builder = DbProviderFactories.GetFactory(m_DbConnection.ToString().RemoveLastIndexof('.')).CreateCommandBuilder())
{
Builder.DataAdapter = DbAdapter;
DbAdapter.InsertCommand = Builder.GetInsertCommand();
DbAdapter.Update(dt);
}
}
catch (Exception Ex)
{
PrintErrorLog(Ex.Message, DbCmd.CommandText);
return(new DaoErrMsg(true, Ex.Message));
}
return(new DaoErrMsg());
}
private void AddStraatnaam(DbConnection connection, DbDataAdapter adapter, List <Adres> adresses)
{
string queryStraatnaam = "SELECT * FROM dbo.straatnaamTest";
DbCommand commandStraatnaam = sqlFactory.CreateCommand();
commandStraatnaam.CommandText = queryStraatnaam;
commandStraatnaam.Connection = connection;
adapter.SelectCommand = commandStraatnaam;
DbCommandBuilder builder = sqlFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
DataTable table = new DataTable();
adapter.Fill(table);
DataColumn[] keyColumns = new DataColumn[1]; // primary key voor deze kolom instellen
keyColumns[0] = table.Columns["ID"]; //idem
table.PrimaryKey = keyColumns; //idem
foreach (Adres adres in adresses)
{
if (!table.Rows.Contains(adres.straatnaam.ID))
{
DataRow row = table.NewRow();
row["ID"] = adres.straatnaam.ID;
row["straatnaam"] = adres.straatnaam.straatnaam;
row["NIScode"] = adres.straatnaam.gemeente.NIScode;
table.Rows.Add(row);
}
}
adapter.Update(table);
}
public void UpdateTable(string tablename, SchedeProcessoDS ds)
{
string tablenameDB = tablename;
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", tablenameDB);
using (DbDataAdapter a = BuildDataAdapter(query))
{
try
{
a.ContinueUpdateOnError = false;
DataTable dt = ds.Tables[tablename];
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.AcceptChangesDuringFill = true;
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(dt);
}
catch (DBConcurrencyException ex)
{
}
catch
{
throw;
}
}
}
/// <summary>
/// Inserta un objeto bean persistente en la base de datos
/// </summary>
/// <typeparam name="T">tipo objeto bean persistente</typeparam>
/// <param name="bean">objeto bean persistente</param>
/// <returns></returns>
public int InsertarObjetoPersistente <T>(T bean, DbConnection connection, DbTransaction tr)
{
//calculamos el tipo de T para obtener información sobre los objetos que queremos contruir
//a partir de la base de datos
Type tipo = typeof(T);
//comprobamos que el tipo sea persistente
if (tipo.IsDefined(typeof(ObjetoPersistente), false))
{
//generamos el nombre de la tabla por defecto a partir del nombre del tipo
string nombreTabla = CalcularNombreTabla(tipo);
#region Calcular el comando de inserccion
DbCommandBuilder comandBuilder = CrearDBCommandBuilder(nombreTabla, connection, tr);
DbCommand comandoInsert = comandBuilder.GetInsertCommand(true);
if (tr != null)
{
comandoInsert.Transaction = tr;
}
#endregion
//connection.Close();
//connection.Open();
RellenarParametrosFrom <T>(bean, comandoInsert.Parameters);
return(comandoInsert.ExecuteNonQuery());
}
return(-1);
}
public DataAccessObjectBase(string table)
{
this.Db = new MicrosoftDatabase();
DbConnection conn = Db.CreateConnection();
try
{
m_selectCommand = Db.GetSqlStringCommand(string.Format(STR_SELECT, table));
m_selectCommand.Connection = conn;
m_adapter = Db.GetDataAdapter();
m_adapter.SelectCommand = m_selectCommand;
DbCommandBuilder builder = m_Db.GetCommandBuilder(m_adapter);
m_adapter.InsertCommand = builder.GetInsertCommand();
m_adapter.DeleteCommand = builder.GetDeleteCommand();
m_adapter.UpdateCommand = builder.GetUpdateCommand();
this.m_canUpdate = true;
}
catch (System.Exception ex)
{
this.m_canUpdate = false;
}
conn.Close();
}
public void InsertUpdateDelete_Speed()
{
var dbFactory = new DbFactory(SqlClientFactory.Instance);
var cmdGenerator = new DbCommandBuilder(dbFactory);
var testData = new Dictionary <string, object> {
{ "name", "Test" },
{ "age", 20 },
{ "created", DateTime.Now }
};
var testQuery = new Query("test", (QField)"id" == (QConst)5);
var stopwatch = new Stopwatch();
int iterations = 0;
stopwatch.Start();
while (iterations < 500)
{
iterations++;
var insertCmd = cmdGenerator.GetInsertCommand("test", testData);
var updateCmd = cmdGenerator.GetUpdateCommand(testQuery, testData);
var deleteCmd = cmdGenerator.GetDeleteCommand(testQuery);
}
stopwatch.Stop();
Console.WriteLine("Speedtest for DbCommandGenerator Insert+Update+Delete commands ({1} times): {0}", stopwatch.Elapsed, iterations);
}
/// <summary>
/// 设置DBDataAdapter的sql
/// </summary>
/// <param name="adapter"></param>
public static void SetDbDataAdapterCommand(DbDataAdapter adapter)
{
DbCommandBuilder cb = CreateCommandBuilder(adapter);
adapter.UpdateCommand = cb.GetUpdateCommand();
adapter.InsertCommand = cb.GetInsertCommand();
adapter.DeleteCommand = cb.GetDeleteCommand();
}
public static void PrintSqlCommandBuilder(DbCommandBuilder commandBuilder, string sComment /* = "" */)
{
PrintIfNotEmpty(sComment);
PrintDataAdapter(commandBuilder.DataAdapter, "");
Debug.WriteLine("SqlCommandBuilder InsertCommand " + commandBuilder.GetInsertCommand().CommandText);
Debug.WriteLine("SqlCommandBuilder UpdateCommand " + commandBuilder.GetUpdateCommand().CommandText);
Debug.WriteLine("SqlCommandBuilder DeleteCommand " + commandBuilder.GetDeleteCommand().CommandText);
}
/// <summary>
/// 更新系SQLの自動生成を行う
/// </summary>
/// <param name="factory">データ取得時に指定されたデータプロバイダのDbProviderFactory</param>
/// <param name="adapter">データ取得時に生成したDbDataAdapter</param>
/// <param name="log">実行ログ出力先のStringBuilder</param>
private static void CreateUpdateCommand(DbProviderFactory factory, DbDataAdapter adapter, StringBuilder log)
{
// CommandBuilderを生成し、DbDataAdapterに関連付ける
DbCommandBuilder cb = factory.CreateCommandBuilder();
cb.DataAdapter = adapter;
log.AppendFormat("< {0} クラスのオブジェクトによって動的に生成された SQL >", cb.GetType().ToString());
log.AppendLine();
// INSERT文を生成する
log.AppendLine("INSERT SQL コマンド:");
log.AppendLine(cb.GetInsertCommand().CommandText);
log.AppendLine();
log.AppendLine("INSERT SQL コマンド パラメータ:");
foreach (DbParameter param in cb.GetInsertCommand().Parameters)
{
log.AppendFormat("{0} => DbType = {1}, SourceColumn = {2}", param.ParameterName, param.DbType, param.SourceColumn);
log.AppendLine();
}
log.AppendLine().AppendLine();
// UPDATE文を生成する
log.AppendLine("UPDATE SQL コマンド:");
log.AppendLine(cb.GetUpdateCommand().CommandText);
log.AppendLine();
log.AppendLine("UPDATE SQL コマンド パラメータ:");
foreach (DbParameter param in cb.GetUpdateCommand().Parameters)
{
log.AppendFormat("{0} => DbType = {1}, SourceColumn = {2}", param.ParameterName, param.DbType, param.SourceColumn);
log.AppendLine();
}
log.AppendLine().AppendLine();
// DELETE文を生成する
log.AppendLine("DELETE SQL コマンド:");
log.AppendLine(cb.GetDeleteCommand().CommandText);
log.AppendLine();
log.AppendLine("DELETE SQL コマンド パラメータ:");
foreach (DbParameter param in cb.GetDeleteCommand().Parameters)
{
log.AppendFormat("{0} => DbType = {1}, SourceColumn = {2}", param.ParameterName, param.DbType, param.SourceColumn);
log.AppendLine();
}
}
public AdapterStatement(DbCommand selectCommand, DbCommandBuilder commandBuilder)
{
_commandBuilder = commandBuilder;
_adapter = commandBuilder.DataAdapter;
_adapter.SelectCommand = selectCommand;
_adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
_adapter.InsertCommand = commandBuilder.GetInsertCommand();
_adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
}
/// <summary>
/// Builds INSERT command.
/// </summary>
public void BuildInsertCommand()
{
if (Adapter == null || Adapter.DeleteCommand != null)
{
return;
}
EnsureBuilder();
Adapter.InsertCommand = _Builder.GetInsertCommand();
}
// Utilizes the SQLiteCommandBuilder,
// which in turn utilizes SQLiteDataReader's GetSchemaTable() functionality
// This insert is slow because it must raise callbacks before and after every update.
// For a fast update, see the FastInsertMany function beneath this one
internal static void DataAdapter(DbProviderFactory fact, DbConnection cnn, bool bWithIdentity)
{
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbDataAdapter adp = fact.CreateDataAdapter())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.Transaction = dbTrans;
cmd.CommandText = "SELECT * FROM TestCase WHERE 1 = 2";
adp.SelectCommand = cmd;
using (DbCommandBuilder bld = fact.CreateCommandBuilder())
{
bld.DataAdapter = adp;
using (adp.InsertCommand = (DbCommand)((ICloneable)bld.GetInsertCommand()).Clone())
{
if (bWithIdentity)
{
adp.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS [ID]";
adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}
bld.DataAdapter = null;
using (DataTable tbl = new DataTable())
{
adp.Fill(tbl);
for (int n = 0; n < 10000; n++)
{
DataRow row = tbl.NewRow();
row[1] = n + (50000 * ((bWithIdentity == true) ? 2 : 1));
tbl.Rows.Add(row);
}
Console.WriteLine(String.Format(" Inserting using CommandBuilder and DataAdapter\r\n ->{0} (10,000 rows) ...", (bWithIdentity == true) ? "(with identity fetch)" : ""));
int dtStart = Environment.TickCount;
adp.Update(tbl);
int dtEnd = Environment.TickCount;
dtEnd -= dtStart;
Console.Write(String.Format(" -> Insert Ends in {0} ms ... ", (dtEnd)));
dtStart = Environment.TickCount;
dbTrans.Commit();
dtEnd = Environment.TickCount;
dtEnd -= dtStart;
Console.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
}
}
}
}
}
}
}
private void tsmiImport_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Pasirinkite failą su aktų duomenimis";
ofd.Filter = "xml|*.xml";
ofd.FileName = Settings.Default.DefaultImportFileName;
if (ofd.ShowDialog() != DialogResult.OK)
{
return;
}
DataSet dsNew = new DataSet();
DataSet dsOld = new DataSet();
DbDataAdapter currAdapter = DbHelper.DPFactory.CreateDataAdapter();
DbConnection currConn = DbHelper.DPFactory.CreateConnection();
DbCommandBuilder cb = DbHelper.DPFactory.CreateCommandBuilder();
currConn.ConnectionString = DbHelper.ConnectionString;
currAdapter.SelectCommand = DbHelper.DPFactory.CreateCommand();
currAdapter.SelectCommand.Connection = conn;
currAdapter.SelectCommand.CommandText = "SELECT * FROM Aktai;";
//currAdapter.SelectCommand.CommandText = "SELECT id, aktas_padalinysId, aktas_Nr, aktas_data, aktas_trukumai, begis_tipasId, begis_protarpisMm, k11, k12, k21, k22, k23, k24, k31, k32, k41, k42, k51, medz_formaGamMetai, medz_misinGamMetai, medz_misinKodasId, medz_misinPartNr, medz_misinPorcNr, salyg_arSausa, salyg_oroTemp, salyg_begioTemp, suvirint_padalinysId, suvirint_suvirintojasId, tikrin_operatoriusId, tikrin_defektoskopasId, tikrin_nelygumaiVirsausMm, tikrin_nelygumaiSonoMm, sutvark_vadovasId, sutvark_padalinysId, tikrin_arDefektas, tikrin_defKodas, tikrin_sanduruCharakter FROM Aktai;";
currAdapter.TableMappings.Add("Table", "Aktai");
currAdapter.UpdateCommand = DbHelper.DPFactory.CreateCommand();
cb.DataAdapter = currAdapter;
currAdapter.InsertCommand = cb.GetInsertCommand();
try
{
dsNew.ReadXmlSchema("suvirinimai-schema.xsd");
dsNew.ReadXml(ofd.FileName);
currAdapter.Fill(dsOld);
dsOld.Merge(dsNew);
currAdapter.Update(dsOld);
currConn.Close();
}
catch (Exception importFailException)
{
Msg.ErrorMsg(string.Format(Messages.XmlImport_fail, Environment.NewLine, importFailException.Message));
return;
}
finally
{
currConn.Close();
}
query();
Msg.InformationMsg(string.Format("Importuota {0} įraš..", dsNew.Tables["Aktai"].Rows.Count));
Settings.Default.DefaultImportFileName = ofd.FileName;
Settings.Default.Save();
}