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