public SQLiteDataAdapter GetDataAdapterSql(string sqlSelect, string sqlUpdate)
{
SQLiteCommand select = new SQLiteCommand(sqlSelect, cnSQL);
SQLiteCommand selectUpdate = new SQLiteCommand(sqlUpdate, cnSQL);
//выявляем параметры в запросе и создаем их в команде
int p = 0;
while (p < sqlSelect.Length)
{
p = sqlSelect.IndexOf('@', p);
if (p < 0)
{
break;
}
int p2;
for (p2 = ++p; p2 < sqlSelect.Length; p2++) //ищем конец имени поля
{
if (!char.IsLetterOrDigit(sqlSelect[p2]))
{
break;
}
}
select.Parameters.Add(new SQLiteParameter(sqlSelect.Substring(p, p2 - p), null));
}
SQLiteDataAdapter da = new SQLiteDataAdapter(selectUpdate);
SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da);
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.SelectCommand = select;
return(da);
}
public Form1()
{
InitializeComponent();
var c = Connect();
var da = new SQLiteDataAdapter("select emp_id, emp_firstname, emp_lastname from emp where 1 = 0", c);
var b = new SQLiteCommandBuilder(da);
da.InsertCommand = new SQLiteCommand(
@"insert into emp(emp_firstname, emp_lastname ) values(:_emp_firstname, :_emp_lastname);
select emp_id, emp_firstname, emp_lastname from emp where emp_id = last_insert_rowid();", c);
da.InsertCommand.Parameters.Add("_emp_firstname", DbType.String, 0, "emp_firstname");
da.InsertCommand.Parameters.Add("_emp_lastname", DbType.String, 0, "emp_lastname");
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.UpdateCommand = b.GetUpdateCommand();
da.DeleteCommand = b.GetDeleteCommand();
var dt = new DataTable();
da.Fill(dt);
var nr = dt.NewRow();
nr["emp_firstname"] = "john";
nr["emp_lastname"] = "lennon";
dt.Rows.Add(nr);
da.Update(dt);
dt.AcceptChanges();
nr["emp_lastname"] = "valjean";
da.Update(dt);
}
public int SaveDataTable(System.Data.DataTable dt)
{
SQLiteTransaction tran = null;
try
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
string sql = string.Format(@"select * from {0} where 1=2", dt.TableName);
SQLiteCommand cmd = new SQLiteCommand(_Conn);;
SQLiteCommandBuilder comBuilder = new SQLiteCommandBuilder();
comBuilder.DataAdapter = adapter;
cmd.CommandText = sql;
cmd.Connection = _Conn;
adapter.SelectCommand = cmd;
adapter.InsertCommand = comBuilder.GetInsertCommand();
adapter.UpdateCommand = comBuilder.GetUpdateCommand();
adapter.DeleteCommand = comBuilder.GetDeleteCommand();
tran = _Conn.BeginTransaction();//transaction begin 传说中sqlite每执行一条insert语句都开启一个事务，死慢
cmd.Transaction = tran;
int result = adapter.Update(dt);
tran.Commit();//transaction end
return(result);
}
catch (Exception ex)
{
if (tran != null)
{
tran.Rollback();
}
throw ex;
}
}
private void LoadData()
{
SQLiteCommand command = _con.CreateCommand();
command.CommandText = "SELECT MAX(bizonylatszam) FROM befizetesek";
bizonylatszam = Convert.ToInt32(command.ExecuteScalar());
_adapTagok = new SQLiteDataAdapter("SELECT * FROM tagok", _con);
_adapBefizetesek = new SQLiteDataAdapter("SELECT * FROM befizetesek", _con);
SQLiteCommandBuilder _builderTagok = new SQLiteCommandBuilder(_adapTagok);
_adapTagok.InsertCommand = _builderTagok.GetInsertCommand();
_adapTagok.DeleteCommand = _builderTagok.GetDeleteCommand();
_adapTagok.UpdateCommand = _builderTagok.GetUpdateCommand();
SQLiteCommandBuilder _builderBefizetesek = new SQLiteCommandBuilder(_adapBefizetesek);
_adapBefizetesek.InsertCommand = _builderBefizetesek.GetInsertCommand();
_adapBefizetesek.DeleteCommand = _builderBefizetesek.GetDeleteCommand();
_adapBefizetesek.UpdateCommand = _builderBefizetesek.GetUpdateCommand();
_dataSet = new DataSet("befizetesek");
_adapTagok.Fill(_dataSet, "tagok");
_bindingSourceTagok.DataSource = _dataSet.Tables["tagok"];
_dataGridViewTagok.DataSource = _bindingSourceTagok;
_adapBefizetesek.Fill(_dataSet, "befizetesek");
_bindingSourceBefizetesek.DataSource = _dataSet.Tables["befizetesek"];
_dataGridViewBefizetesek.DataSource = _bindingSourceBefizetesek;
FormatTagokTable();
FormatBefizetesekTable();
}
private void applyButton_Click(object sender, EventArgs e)
{
SQLiteConnection mCN = ConnectionManager.connection;
SQLiteDataAdapter mDA = modulesTableAdapter.Adapter;
SQLiteCommandBuilder mCB = new SQLiteCommandBuilder(mDA);
DataSet mDS = modulesDataSet;
DataSet dsChanges = new DataSet();
if (!mDS.HasChanges())
{
return;
}
dsChanges = mDS.GetChanges(DataRowState.Modified);
if (dsChanges != null)
{
mDA.UpdateCommand = mCB.GetUpdateCommand();
mDA.Update(dsChanges, "modules");
}
dsChanges = mDS.GetChanges(DataRowState.Added);
if (dsChanges != null)
{
mDA.InsertCommand = mCB.GetInsertCommand();
mDA.Update(dsChanges, "modules");
}
dsChanges = mDS.GetChanges(DataRowState.Deleted);
if (dsChanges != null)
{
mDA.DeleteCommand = mCB.GetDeleteCommand();
mDA.Update(dsChanges, "modules");
}
mDS.AcceptChanges();
UpdateModulesDropDown();
(treeView.Model as SlowTreeModel).Root.UpdateModulesFromDbRec();
treeView.Invalidate();
}
/// <summary>
/// Load data from database to datagrid
/// </summary>
private void LoadData()
{
try
{
sqlDataAdapter = new SQLiteDataAdapter("Select *, 'Delete' AS [Delete] FROM " + currentTable, SqlConnection);
SqlCommandBuilder = new SQLiteCommandBuilder(sqlDataAdapter);
SqlCommandBuilder.GetInsertCommand();
SqlCommandBuilder.GetUpdateCommand();
SqlCommandBuilder.GetDeleteCommand();
dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet, currentTable);
dataGridView1.DataSource = null;
dataGridView1.DataSource = dataSet.Tables[currentTable];
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
DataGridViewLinkCell linkCell = new DataGridViewLinkCell();
dataGridView1[lastIndexTable, i] = linkCell;
}
dataGridView1.Refresh();
dataGridView1.Update();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool SaveDataGrid()
{
try
{
if (connection == null)
{
return(true);
}
dataGridView.CurrentCell = null;
comandBuilder.RefreshSchema();
dataGridView.EndEdit();
if (adapter.InsertCommand == null)
{
adapter.InsertCommand = new SQLiteCommand(" ");
}
if (adapter.UpdateCommand == null)
{
adapter.UpdateCommand = comandBuilder.GetUpdateCommand();
}
if (adapter.DeleteCommand == null)
{
adapter.DeleteCommand = comandBuilder.GetDeleteCommand();
}
adapter.Update(dataSet.Tables[NameTable]);
return(true);
}
catch (Exception e)
{
MessageBox.Show("Ошибка в процессе сохранения данных.\n" + e.Message, "Ошибка");
return(false);
}
}
public int?UpdateDt(DataTable Dt, string commandText)
{
//if application is exiting the don't perform DB process
if (this.isAppClosing)
{
return(null);
}
if (!DoesDBConnectionExists())
{
return(null);
}
try
{
SQLiteDataAdapter da = new SQLiteDataAdapter(commandText, _connection);
SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.Update(Dt);
}
catch (Exception ex)
{
LogTrace.WriteErrorLog(this.GetType().Name, MethodBase.GetCurrentMethod().Name,
String.Format("Error in query . " + commandText + ex.StackTrace));
throw;
}
finally
{
commandText = string.Empty;
}
return(Dt.Rows.Count);
}
public Query GetTableContent(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, bool editable)
{
string sql = "SELECT * FROM {0};";
string dbSQL = string.Format(sql, table.Name);
string dbConn = ConnectionString;
using (var conn = new SQLiteConnection(dbConn))
{
conn.Open();
var cmd = new SQLiteCommand(dbSQL, conn);
var sdaDatabases = new SQLiteDataAdapter(cmd);
if (editable)
{
var scb = new SQLiteCommandBuilder(sdaDatabases);
sdaDatabases.UpdateCommand = scb.GetUpdateCommand();
sdaDatabases.InsertCommand = scb.GetInsertCommand();
sdaDatabases.DeleteCommand = scb.GetDeleteCommand();
}
var dsDatabases = new DataTable("TableContent");
sdaDatabases.Fill(dsDatabases);
var query = new Query(_settings, server.Name, database.Name, dbSQL, dsDatabases);
query.Adapter = sdaDatabases;
return(query);
}
}
internal void event_CommitDataTableChanges(string tableName, DataTable dataTable)
{
try {
event_OpenConnection();
string query = "SELECT * FROM [" + tableName + "]";
SQLiteDataAdapter dataAdapter;
dataAdapter = new SQLiteDataAdapter(query, connection);
SQLiteCommandBuilder commandBuilder;
commandBuilder = new SQLiteCommandBuilder(dataAdapter);
commandBuilder.QuotePrefix = "[";
commandBuilder.QuoteSuffix = "]";
dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
dataAdapter.Update(dataTable);
commandBuilder.Dispose();
dataAdapter.Dispose();
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
private void button2_Click(object sender, EventArgs e) //删除产品
{
ds.AcceptChanges();
SQLiteCommandBuilder scb = new SQLiteCommandBuilder(da);
ds.Tables[0].Rows[this.dataGridView1.CurrentRow.Index].Delete();
da.DeleteCommand = scb.GetDeleteCommand();
da.Update(ds, "ProductInfo");
}
public void Push()
{
var builder = new SQLiteCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.DeleteCommand = builder.GetDeleteCommand();
_adapter.Update(LocalDataTable);
}
static void Update(string tableName)
{
var adapter = Adapters[tableName];
var commandBuilder = new SQLiteCommandBuilder(adapter);
adapter.DeleteCommand = commandBuilder.GetDeleteCommand(true);
adapter.InsertCommand = commandBuilder.GetInsertCommand(true);
adapter.UpdateCommand = commandBuilder.GetUpdateCommand(true);
adapter.Update(Tables[tableName]);
}
public override DbDataAdapter CreateAdapter(Table table, TableFilter filter)
{
if (!(table is SQLiteTable))
{
throw new ArgumentException("Table must be of type SQLiteTable", "table");
}
var adapter = new SQLiteDataAdapter(table.GetBaseSelectCommandText(filter), Connection);
if (!table.IsReadOnly)
{
var builder = new SQLiteCommandBuilder(adapter)
{
ConflictOption = ConflictOption.OverwriteChanges
};
SQLiteCommand updateCommand = null;
try {
updateCommand = builder.GetUpdateCommand();
} catch (InvalidOperationException) {
var selectSql = new StringBuilder();
selectSql.Append("SELECT RowId, ");
filter.WriteColumnsProjection(selectSql);
selectSql.Append(" FROM ");
selectSql.Append(table.QuotedName);
adapter = new SQLiteDataAdapter(selectSql.ToString(), Connection);
builder = new SQLiteCommandBuilder(adapter);
updateCommand = builder.GetUpdateCommand();
}
var insertCommand = builder.GetInsertCommand();
foreach (var column in table.Columns)
{
if (column.IsIdentity)
{
insertCommand.CommandText = insertCommand.CommandText + "; SELECT @RowId = last_insert_rowid()";
insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
var parameter = new SQLiteParameter {
ParameterName = "@RowId",
Direction = ParameterDirection.Output,
SourceColumn = column.Name,
SourceVersion = DataRowVersion.Current,
Value = DBNull.Value
};
insertCommand.Parameters.Add(parameter);
break;
}
}
adapter.UpdateCommand = updateCommand;
adapter.InsertCommand = insertCommand;
adapter.DeleteCommand = builder.GetDeleteCommand();
}
return(adapter);
}
public SQLiteDataAdapter GetSQLiteAdapter(string SelectCommand)
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter(SelectCommand, this.connection);
SQLiteCommandBuilder cb = new SQLiteCommandBuilder(adapter);
adapter.InsertCommand = cb.GetInsertCommand();
adapter.UpdateCommand = cb.GetUpdateCommand();
adapter.DeleteCommand = cb.GetDeleteCommand();
return(adapter);
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
_ad = new SQLiteDataAdapter("select * from students", DB.Connection);
var builder = new SQLiteCommandBuilder(_ad);
_ad.DeleteCommand = builder.GetDeleteCommand();
_ad.InsertCommand = builder.GetInsertCommand();
_ad.UpdateCommand = builder.GetUpdateCommand();
_ad.Fill(_dt);
btnEdit.Enabled = btnRemove.Enabled = _dt.Rows.Count != 0;
listBox1.DataSource = _dt;
listBox1.DisplayMember = "name";
}
//adatok betöltése a fizikai adatbázisból a logikai adatbázisba
private void LoadData()
{
_adap = new SQLiteDataAdapter("SELECT * FROM " + _tn, _con);
SQLiteCommandBuilder builder = new SQLiteCommandBuilder(_adap);
_adap.InsertCommand = builder.GetInsertCommand();
_adap.DeleteCommand = builder.GetDeleteCommand();
_adap.UpdateCommand = builder.GetUpdateCommand();
_dataSet = new DataSet(_tn);
_adap.Fill(_dataSet, _tn);
_bindingSource.DataSource = _dataSet.Tables[_tn];
FormatTables(_dataSet);
}
/// <summary>
/// 保存DataTable
/// </summary>
/// <param name="dt">DataTable</param>
/// <returns>影响的行数</returns>
public int SaveDataTable(SQLiteConnection conn, DataTable dt, string tableName)
{
bool isClose = conn == null;
SQLiteTransaction _tran = null;
try
{
if (isClose)
{
conn = this.GetSQLiteConnection();
conn.Open();
}
else
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "SELECT * FROM " + tableName;
SQLiteDataAdapter oda = new SQLiteDataAdapter(command);
SQLiteCommandBuilder ocb = new SQLiteCommandBuilder(oda);
oda.InsertCommand = ocb.GetInsertCommand();
oda.UpdateCommand = ocb.GetUpdateCommand();
oda.DeleteCommand = ocb.GetDeleteCommand();
_tran = conn.BeginTransaction();
command.Transaction = _tran;
int result = oda.Update(dt);
_tran.Commit();
return(result);
}
catch (Exception ex)
{
_tran.Rollback();
throw new Exception(ex.Message);
}
finally
{
if (_tran != null)
{
_tran.Dispose();
}
if (isClose)
{
conn.Close();
}
}
}
private void LoadData()
{
SQLiteCommand command = _con.CreateCommand();
command.CommandText = "SELECT MAX(kolcson_id) FROM kolcsonzesek";
kolcson_id = Convert.ToInt32(command.ExecuteScalar());
_adapKolcsonzesek = new SQLiteDataAdapter("SELECT * FROM kolcsonzesek WHERE vissza is null", _con);
_adapTagok = new SQLiteDataAdapter("SELECT * FROM tagok", _con);
_adapKonyvek = new SQLiteDataAdapter("SELECT * FROM konyvek", _con);
SQLiteCommandBuilder _builderKolcsonzesek = new SQLiteCommandBuilder(_adapKolcsonzesek);
_adapKolcsonzesek.InsertCommand = _builderKolcsonzesek.GetInsertCommand();
_adapKolcsonzesek.DeleteCommand = _builderKolcsonzesek.GetDeleteCommand();
_adapKolcsonzesek.UpdateCommand = _builderKolcsonzesek.GetUpdateCommand();
SQLiteCommandBuilder _builderTagok = new SQLiteCommandBuilder(_adapTagok);
_adapTagok.InsertCommand = _builderTagok.GetInsertCommand();
_adapTagok.DeleteCommand = _builderTagok.GetDeleteCommand();
_adapTagok.UpdateCommand = _builderTagok.GetUpdateCommand();
SQLiteCommandBuilder _builderKonyvek = new SQLiteCommandBuilder(_adapKonyvek);
_adapKonyvek.InsertCommand = _builderKonyvek.GetInsertCommand();
_adapKonyvek.DeleteCommand = _builderKonyvek.GetDeleteCommand();
_adapKonyvek.UpdateCommand = _builderKonyvek.GetUpdateCommand();
_dataSet = new DataSet("kolcsonzes");
_adapKolcsonzesek.Fill(_dataSet, "kolcsonzesek");
_bindingSourceKolcsonzesek.DataSource = _dataSet.Tables["kolcsonzesek"];
_dataGridViewKolcsonzesek.DataSource = _bindingSourceKolcsonzesek;
_adapTagok.Fill(_dataSet, "tagok");
_bindingSourceTagok.DataSource = _dataSet.Tables["tagok"];
_dataGridViewTagok.DataSource = _bindingSourceTagok;
_adapKonyvek.Fill(_dataSet, "konyvek");
_bindingSourceKonyvek.DataSource = _dataSet.Tables["konyvek"];
_dataGridViewKonyvek.DataSource = _bindingSourceKonyvek;
FormatKolcsonzesekTable();
FormatTagokTable();
FormatKonyvekTable();
}
public void Update(DataTable dataTable)
{
var query = $"select * from {TableName}";
using (SQLiteConnection connection = GetConnection())
using (var adapter = new SQLiteDataAdapter(query, connection))
using (var commandBuilder = new SQLiteCommandBuilder(adapter))
{
adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
adapter.InsertCommand = commandBuilder.GetInsertCommand();
adapter.Update(dataTable);
}
}
public static void updateData(DataTable dt, string q)
{
//Use command builder to generate update commands and push the db
ConnectDB();
con.Open();
SQLiteDataAdapter dap = new SQLiteDataAdapter(q, con);
SQLiteCommandBuilder build = new SQLiteCommandBuilder(dap);
dap.UpdateCommand = build.GetUpdateCommand();
dap.InsertCommand = build.GetInsertCommand();
dap.DeleteCommand = build.GetDeleteCommand();
dap.Update(dt);
}
protected override DataAdapter CreateAdapter(SQLiteConnection connection, DataTable data)
{
var command = connection.CreateCommand();
var adapter = new SQLiteDataAdapter();
var builder = new SQLiteCommandBuilder(adapter);
command.CommandText = string.Format("select * from {0}", data.TableName);
adapter.SelectCommand = command;
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
return(adapter);
}
public void Update()
{
var query = "SELECT ogc_fid, ort, straße from " + Table;
using (var conneciton = _context.GetConnection())
using (var command = new SQLiteCommand(query, conneciton))
using (var adapter = new SQLiteDataAdapter(command))
{
var builder = new SQLiteCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.Update(_table);
}
}
public void UpdateDatabase(DataTable table)
{
var selectQuery = $"SELECT * FROM {TableName}";
using (var conneciton = GetConnection())
using (var command = new SQLiteCommand(selectQuery, conneciton))
using (var adapter = new SQLiteDataAdapter(command))
{
var builder = new SQLiteCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.Update(table);
}
}
/// <summary>
/// Updates specified DataTable
/// </summary>
/// <param name="table">DataTable to update</param>
public void LoadDataSet(DataTable table)
{
string query = "SELECT song_id, artist, title, time, path FROM songs";
using (SQLiteConnection connection = new SQLiteConnection(cb.ConnectionString))
{
connection.Open();
SQLiteDataAdapter da = new SQLiteDataAdapter(query, connection);
SQLiteCommandBuilder cb2 = new SQLiteCommandBuilder(da);
da.DeleteCommand = cb2.GetDeleteCommand();
da.UpdateCommand = cb2.GetUpdateCommand();
da.InsertCommand = cb2.GetInsertCommand();
da.Update(table);
}
}
private void button1_Click(object sender, EventArgs e)
{
var query = "SELECT * FROM PERSON";
using (var connection = new SQLiteConnection("Data Source=db.sqlite"))
using (var adapter = new SQLiteDataAdapter(query, connection))
{
var builder = new SQLiteCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.Update(table);
}
}
//Метод, выводящий результаты на форму из базы данных
public void ShowInstruction()
{
tmp.dataGridView1.AllowUserToAddRows = false; //определение свойства формы, запрещащющее пользователю добавлять новые строки в dataGridView
tmp.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
string sql_command = "select * from Instruction"; // строка запроса к БД
adapter_instruction = new SQLiteDataAdapter(sql_command, conn); // инициализация и выполнение запроса
CommandBuilder = new SQLiteCommandBuilder(adapter_instruction); // формирование адаптера
adapter_instruction.UpdateCommand = CommandBuilder.GetUpdateCommand();
adapter_instruction.InsertCommand = CommandBuilder.GetInsertCommand();
adapter_instruction.DeleteCommand = CommandBuilder.GetDeleteCommand();
data_set_instruction = new DataSet();
adapter_instruction.Fill(data_set_instruction);
tmp.dataGridView1.DataSource = data_set_instruction.Tables[0];
}
public void ShowWork()
{
tmp.dataGridView3.AllowUserToAddRows = false;
tmp.dataGridView3.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
string sql_command = "select * from Worker";
adapter_worker = new SQLiteDataAdapter(sql_command, conn);
CommandBuilder = new SQLiteCommandBuilder(adapter_worker);
adapter_worker.UpdateCommand = CommandBuilder.GetUpdateCommand();
adapter_worker.InsertCommand = CommandBuilder.GetInsertCommand();
adapter_worker.DeleteCommand = CommandBuilder.GetDeleteCommand();
data_set_worker = new DataSet();
adapter_worker.Fill(data_set_worker);
tmp.dataGridView3.DataSource = data_set_worker.Tables[0];
}
private void setGridAll()
{
tabControl1.TabPages.Clear();
foreach (DataRow row in dsAllTables.Tables[0].Rows)
{
string tableName = row[0].ToString();
tabControl1.TabPages.Add(tableName, tableName);
DataGridView dgv = new DataGridView();
dgv.Dock = DockStyle.Fill;
//dgv.Anchor = AnchorStyles.Top | AnchorStyles.Right | AnchorStyles.Bottom | AnchorStyles.Left;
tabControl1.TabPages[tableName].Controls.Add(dgv);
//
SQLiteCommandBuilder builder;
try
{
adapters[tableName] = new SQLiteDataAdapter("select * from `" + tableName + "` where 1=1;", conn);
builder = new SQLiteCommandBuilder(adapters[tableName]);
datasets[tableName] = new DataSet();
adapters[tableName].Fill(datasets[tableName]);
}
catch (Exception ex) {
MessageBox.Show("Error: " + ex.Message);
continue;
}
//generate commands using command builder
try
{
var uc = builder.GetUpdateCommand().CommandText;
var ic = builder.GetInsertCommand().CommandText;
var dc = builder.GetDeleteCommand().CommandText;
//MessageBox.Show("auto generated update command: " + uc);
//MessageBox.Show("auto generated insert command: " + ic);
//MessageBox.Show("auto generated delete command: " + dc);
dgv.ReadOnly = false;
}
catch
{
MessageBox.Show(tableName + ": tables without primary key cannot be edited/deleted");
dgv.ReadOnly = true;
}
dgv.AutoGenerateColumns = true;
dgv.DataSource = datasets[tableName];
dgv.DataMember = datasets[tableName].Tables[0].TableName; //tableName;
}
}
public bool ExcuteAdapter(string connString, DataTable oTable, string cmdText, ref long lngMaxID)
{
SQLiteConnection conn = default(SQLiteConnection);
SQLiteDataAdapter oDataAdapter = new SQLiteDataAdapter();
SQLiteCommand oSqlCmd = new SQLiteCommand();
SQLiteCommandBuilder oCmdBuilder = default(SQLiteCommandBuilder);
try
{
conn = new SQLiteConnection(connString);
if (!(conn.State == ConnectionState.Open))
{
conn.Open();
}
oSqlCmd.Connection = conn;
oSqlCmd.CommandText = cmdText;
oSqlCmd.CommandType = CommandType.Text;
oDataAdapter.SelectCommand = oSqlCmd;
oCmdBuilder = new SQLiteCommandBuilder(oDataAdapter);
oCmdBuilder.GetUpdateCommand();
oCmdBuilder.GetInsertCommand();
oCmdBuilder.GetDeleteCommand();
oDataAdapter.Update(oTable);
oDataAdapter.SelectCommand = new SQLiteCommand("SELECT @@IDENTITY", conn);
lngMaxID = Convert.ToInt64(oDataAdapter.SelectCommand.ExecuteScalar());
return(true);
}
catch (SQLiteException ex)
{
throw new Exception("SQL Exception ", ex);
}
catch (Exception exx)
{
throw new Exception("ExeculateAdapter", exx);
}
finally
{
// cmd.Connection.Close()
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
oSqlCmd = null;
oDataAdapter = null;
oCmdBuilder = null;
}
}
