public static void UpdateTable(string query, SQLiteConnection dbConnection, DataTable table) { //object maxId = null; //if (query.ToLower().Contains("from traffic_object")) // maxId = GetScalar("Select max(obj_id) From traffic_object", dbConnection); using (SQLiteTransaction transaction = dbConnection.BeginTransaction()) { using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, dbConnection)) { using (SQLiteCommand command = dbConnection.CreateCommand()) { command.Transaction = transaction; command.CommandText = query; adapter.SelectCommand = command; using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder()) { builder.DataAdapter = adapter; adapter.Update(table); transaction.Commit(); } } } } }
private void button1_Click_1(object sender, EventArgs e) { if (comboBox1.SelectedIndex == 0) { string numero; try { numero = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para borrar cmd.CommandText = "DELETE FROM Entradas WHERE [Numero] = " + numero; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Entrada eliminada exitosamente"); appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "select * from Entradas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } catch { MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
public Form2(string userName) { InitializeComponent(); this.btn.Click += new System.EventHandler(this.btn_Click); label7.Text = "Hello! "+ userName; fill_comboBox(); fill_Listbox(); timer1.Start(); //--- load data in DataGridView try { string Query = "SELECT eid,name,surname,age FROM employeeinfo"; DataBaseOperation.ConnectToDataBase(dbConnectionString, Query, ref sqliteCon, ref sqliteCmd); //--- add data to dataGrid SQLiteDataAdapter dataAdp = new SQLiteDataAdapter(sqliteCmd); dataTable = new DataTable("employeeinfo"); //name of database dataAdp.Fill(dataTable); dataGridView1.DataSource = dataTable.DefaultView; dataAdp.Update(dataTable); sqliteCon.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void Button_Click(object sender, RoutedEventArgs e) { string dbConnectionString = @"Data Source=patient.db;Version=3;"; SQLiteConnection sqlite_connection = new SQLiteConnection(dbConnectionString); try { sqlite_connection.Open(); string query = "select * from patient_table"; SQLiteCommand create_command = new SQLiteCommand(query, sqlite_connection); create_command.ExecuteNonQuery(); //SQLiteDataReader dr = create_command.ExecuteReader(); SQLiteDataAdapter dataAdapt = new SQLiteDataAdapter(query, sqlite_connection); //var ds = new DataSet(); DataTable dt = new DataTable("patient_table"); //dataAdapt.Fill(ds,"patient_table"); dataAdapt.Fill(dt); // listView1.DataContext = ds.Tables["patient_table"].DefaultView; admin_monthly_accounting_datagrid.ItemsSource = dt.DefaultView ; dataAdapt.Update(dt); sqlite_connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
//------------------------------------------------------------------------------------------------------------------- //Wonky (dont use for now) List<Series> passing (needs testing) public void SaveToExcel(List <Microsoft.Office.Interop.Excel.Series> _series)//include another param for table name & filepath { // string constring = "Data Source = C:\\Users\\Wesley Osborn\\Desktop\\DBtest;//_filepath"; // System.Data.SQLite.SQLiteConnection conDataBase = new System.Data.SQLite.SQLiteConnection(connString); System.Data.SQLite.SQLiteCommand cmdDataBase = new System.Data.SQLite.SQLiteCommand(" SELECT * FROM Emotions ;", sqlite_conn); // DataGridView DataGrid = _DGV; try { System.Data.SQLite.SQLiteDataAdapter sda = new System.Data.SQLite.SQLiteDataAdapter(); sda.SelectCommand = cmdDataBase; System.Data.DataTable dbdataset = new System.Data.DataTable(); sda.Fill(dbdataset); BindingSource bSource = new BindingSource(); bSource.DataSource = dbdataset; // _DGV.DataSource = bSource; sda.Update(dbdataset); //export to Excel DataSet ds = new DataSet("New_DataSet"); ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; sda.Fill(dbdataset); ds.Tables.Add(dbdataset); ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds); } catch (Exception) { throw; } }
private void textBox1_TextChanged(object sender, EventArgs e) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"; //create the database query string query = "SELECT * From Proveedor Where Nombreproveedor like '%" + textBox1.Text + "%'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); }
private void textBox2_TextChanged(object sender, EventArgs e) { string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Almacen Where ArticuloID = '" + textBox2.Text + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); label6.Text = ""; label5.Text = ""; //textBox3.Text = "0"; label10.Text = ""; label12.Text = ""; for (int i = 0; i < dTable.Rows.Count; i++) { DataRow Row = dTable.Rows[i]; label6.Text = Row["Cantidadexistencia"].ToString(); label5.Text = Row["Precioventa"].ToString(); label12.Text = Row["Nombrearticulo"].ToString(); } }
private void button1_Click(object sender, EventArgs e) { if (dataGridView1.Rows.Count != 0) { DialogResult resultado = MessageBox.Show("Esta seguro que desea eliminar toda la venta?", "Seguro?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation); if (resultado == DialogResult.Yes) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "Delete From Ventas Where [NVenta] = " + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + ""; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); cmd.CommandText = "Delete From Ventashechas Where [NVenta] = " + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + ""; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"; dataGridView1.Left = 247; //create the database query string query = "Select * From Ventas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } } else { MessageBox.Show("Tiene que elegir una venta para eliminarlo"); } }
private void button3_Click(object sender, EventArgs e) { if (comboBox1.SelectedIndex == 1) { string numero; try { numero = dataGridView2.SelectedRows[0].Cells[0].Value.ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para borrar cmd.CommandText = "DELETE FROM Salidas WHERE [Numero] = " + numero; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Salida eliminada exitosamente"); appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "select * from Salidas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView2.DataSource = bSource; dAdapter.Update(dTable); } catch { MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
private void button1_Click(object sender, EventArgs e) { try { fecha = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); nombre = dataGridView1.SelectedRows[0].Cells[1].Value.ToString(); edad = Int32.Parse(dataGridView1.SelectedRows[0].Cells[2].Value.ToString()); apoyo = dataGridView1.SelectedRows[0].Cells[3].Value.ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "DELETE FROM Donaciones WHERE Fecha = '" + fecha + "' AND Nombre = '" + nombre + "' AND Edad = '" + edad + "' AND Apoyo = '" + apoyo + "'"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Donacion eliminada con exito"); appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;"; //create the database query string query = "select * from Donaciones"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } catch { MessageBox.Show("No hay donaciones que eliminar"); } }
public void SaveDatabase() { DataSet changes = DataSet.GetChanges(); System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("Data Source=" + DB_Connection + ";Version=3;"); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("SELECT * FROM Members", con); SQLiteCommandBuilder cb = new SQLiteCommandBuilder(dataAdapter); dataAdapter.Update(changes); DataSet.AcceptChanges(); con.Close(); GC.Collect(); }
private void button1_Click(object sender, EventArgs e) { ///create the connection string string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Usuarios"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); bool usuarioexistente = false; bool admin = false; for (int i = 0; i < dTable.Rows.Count; i++) { DataRow Row = dTable.Rows[i]; if (Row["Usuario"].ToString() == textBox1.Text && Row["Contrasena"].ToString() == textBox2.Text) { usuarioexistente = true; if (Row["TipoDeUsuario"].ToString() == "Administrador") { admin = true; AdminAvailable(true); this.Close(); } break; } } if (usuarioexistente == false) { MessageBox.Show("Usuario o contraseña incorrecta"); } else if (usuarioexistente == true && admin == false) { MessageBox.Show("La cuenta utilizada no es de un administrador"); } }
private void button1_Click(object sender, EventArgs e) { if (dataGridView1.Rows.Count != 0) { DialogResult resultado = MessageBox.Show("Esta seguro que desea eliminar?", "Seguro?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation); if (resultado == DialogResult.Yes) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "Delete From Proveedor Where [Nombreproveedor] = '" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "'"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"; //create the database query string query = "Select * From Proveedor"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } } else { MessageBox.Show("Tiene que elegir un articulo para borrarlo"); } }
private void textBox1_TextChanged(object sender, EventArgs e) { if (textBox1.Text != "") { string appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBUC.s3db"; string query = ""; query = "SELECT Nombre,Usuario,TipoDeUsuario from Usuarios WHERE Usuario LIKE '%" + textBox1.Text + "%'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } else { string appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBUC.s3db"; string query = ""; query = "SELECT Nombre,Usuario,TipoDeUsuario from Usuarios"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } }
public static DataTable LogIn(DataTable dtIn) { string cmdstring = "select * from userdata"; SQLiteDataAdapter sa = new SQLiteDataAdapter(cmdstring, objConnection); DataTable dtOut = new DataTable(); sa.Fill(dtOut); dtOut.Rows.Add(dtIn.Rows[0].ItemArray); SQLiteCommandBuilder objCommandBuilder = new SQLiteCommandBuilder(sa); sa.Update(dtOut); currentUser = dtIn.Rows[0]; objConnection.Close(); return dtIn; }
private void NuevoExpedienteSillas_Load(object sender, EventArgs e) { float width_ratio = (Screen.PrimaryScreen.Bounds.Width / 800); float heigh_ratio = (Screen.PrimaryScreen.Bounds.Height / 600f); SizeF scale = new SizeF(width_ratio, heigh_ratio); this.Scale(scale); //And for font size foreach (Control control in this.Controls) { control.Font = new Font("Microsoft Sans Serif", Font.SizeInPoints * heigh_ratio * width_ratio); } comboBox1.SelectedIndex = 0; comboBox2.SelectedIndex = 0; comboBox3.SelectedIndex = 0; comboBox4.SelectedIndex = 0; string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBESIL.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM SRTamanoTipo"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["IDFormatoSillas"].ToString(); int autonum = Int32.Parse(num); label28.Text = (autonum + 1).ToString(); } else { label28.Text = "1"; } }
public void UpdateDatatableQuery(DataTable dt, String query) { DataTable sdt = new DataTable(); using (SQLiteCommand command = Conn.CreateCommand()) { command.CommandText = query; SQLiteDataAdapter adp = new SQLiteDataAdapter(command); adp.Fill(sdt); SQLiteCommandBuilder cb = new SQLiteCommandBuilder(adp); //SQLiteCommand update = cb.GetUpdateCommand(); //adp.UpdateCommand = update; adp.Update(dt); } }
private void Form1_Load(object sender, EventArgs e) { string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Ventas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["NVenta"].ToString(); int autonum = Int32.Parse(num); textBox1.Text = (autonum + 1).ToString(); } else { textBox1.Text = "1"; } try { Sistema_Caritas.ConvertidorMonetario.CurrencyConvertor CC = new Sistema_Caritas.ConvertidorMonetario.CurrencyConvertor(); label21.Text = CC.ConversionRate(Sistema_Caritas.ConvertidorMonetario.Currency.USD, Sistema_Caritas.ConvertidorMonetario.Currency.MXN).ToString(); } catch { comboBox1.Visible = false; label20.Visible = false; label21.Visible = false; button5.Visible = false; } timer1.Enabled = true; }
private void dataGridView1_Click(object sender, EventArgs e) { try { ///create the connection string string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;"; string nombre = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); string usuario = dataGridView1.SelectedRows[0].Cells[1].Value.ToString(); string tipodeusuario = dataGridView1.SelectedRows[0].Cells[2].Value.ToString(); //create the database query string query = "SELECT * FROM Usuarios WHERE Nombre = '" + nombre + "' AND Usuario = '" + usuario + "' AND TipoDeUsuario = '" + tipodeusuario + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); DataRow Row = dTable.Rows[0]; string contrasena = Row["Contrasena"].ToString(); textBox5.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); textBox2.Text = usuario; textBox3.Text = contrasena; textBox4.Text = contrasena; comboBox1.SelectedIndex = comboBox1.FindStringExact(tipodeusuario); } catch { textBox5.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; comboBox1.SelectedIndex = 1; } }
public void YaziciBilgileriniKaydet(int ModulID, DataTable dtYaziciBil) { using (da = new System.Data.SQLite.SQLiteDataAdapter("select * from YaziciAyarlari where ModulID = @ModulID", sqlKonneksin)) { da.InsertCommand = new System.Data.SQLite.SQLiteCommand(@"insert into YaziciAyarlari (RaporDizaynID, ModulID, YaziciAdi, KagitKaynagi, KagitKaynagiIndex --, RenkliMi, KagitTipi, CiftTarafliMi , Aciklama) values (@RaporDizaynID, @ModulID, @YaziciAdi, @KagitKaynagi, @KagitKaynagiIndex --, @RenkliMi, @KagitTipi, @CiftTarafliMi , @Aciklama)", sqlKonneksin); da.InsertCommand.Parameters.Add("@ModulID", System.Data.DbType.Int16).Value = ModulID; da.InsertCommand.Parameters.Add("@RaporDizaynID", System.Data.DbType.String, 0, "RaporDizaynID"); da.InsertCommand.Parameters.Add("@YaziciAdi", System.Data.DbType.String, 0, "YaziciAdi"); da.InsertCommand.Parameters.Add("@KagitKaynagi", System.Data.DbType.String, 0, "KagitKaynagi"); da.InsertCommand.Parameters.Add("@KagitKaynagiIndex", System.Data.DbType.Int16, 0, "KagitKaynagiIndex"); //da.InsertCommand.Parameters.Add("@RenkliMi", System.Data.DbType.Boolean, 0, "RenkliMi"); //da.InsertCommand.Parameters.Add("@KagitTipi", System.Data.DbType.String, 0, "KagitTipi"); //da.InsertCommand.Parameters.Add("@CiftTarafliMi", System.Data.DbType.Int16, 0, "CiftTarafliMi"); da.InsertCommand.Parameters.Add("@Aciklama", System.Data.DbType.String, 0, "Aciklama"); da.UpdateCommand = new System.Data.SQLite.SQLiteCommand(@"update YaziciAyarlari set RaporDizaynID = @RaporDizaynID, ModulID = @ModulID, YaziciAdi = @YaziciAdi, KagitKaynagi = @KagitKaynagi, KagitKaynagiIndex = @KagitKaynagiIndex --, RenkliMi = @RenkliMi, KagitTipi = @KagitTipi, CiftTarafliMi = @CiftTarafliMi , Aciklama = @Aciklama where RaporDizaynID = @RaporDizaynID", sqlKonneksin); da.UpdateCommand.Parameters.Add("@ID", System.Data.DbType.Int32, 0, "ID"); da.UpdateCommand.Parameters.Add("@ModulID", System.Data.DbType.Int16).Value = ModulID; da.UpdateCommand.Parameters.Add("@RaporDizaynID", System.Data.DbType.String, 0, "RaporDizaynID"); da.UpdateCommand.Parameters.Add("@YaziciAdi", System.Data.DbType.String, 0, "YaziciAdi"); da.UpdateCommand.Parameters.Add("@KagitKaynagi", System.Data.DbType.String, 0, "KagitKaynagi"); da.UpdateCommand.Parameters.Add("@KagitKaynagiIndex", System.Data.DbType.Int16, 0, "KagitKaynagiIndex"); //da.UpdateCommand.Parameters.Add("@RenkliMi", System.Data.DbType.Boolean, 0, "RenkliMi"); //da.UpdateCommand.Parameters.Add("@KagitTipi", System.Data.DbType.String, 0, "KagitTipi"); //da.UpdateCommand.Parameters.Add("@CiftTarafliMi", System.Data.DbType.Int16, 0, "CiftTarafliMi"); da.UpdateCommand.Parameters.Add("@Aciklama", System.Data.DbType.String, 0, "Aciklama"); da.DeleteCommand = new System.Data.SQLite.SQLiteCommand("delete from YaziciAyarlari where RaporDizaynID = @RaporDizaynID", sqlKonneksin); da.DeleteCommand.Parameters.Add("@RaporDizaynID", System.Data.DbType.Int32, 0, "RaporDizaynID"); da.Update(dtYaziciBil); } }
/// <summary> /// 修改数据表记录 /// </summary> /// <returns></returns> public static bool UpdateTable(DataTable srcTable, string tableName) { bool isok = false; try { SQLiteCommand command = new SQLiteCommand(); using (SQLiteConnection conn = GetSQLiteConnection()) { if (conn.State != ConnectionState.Open) conn.Open(); command.Connection = conn; command.CommandText = "SELECT * FROM " + tableName; SQLiteDataAdapter SQLiteDA = new SQLiteDataAdapter(command); SQLiteCommandBuilder SQLiteCB = new SQLiteCommandBuilder(SQLiteDA); SQLiteDA.InsertCommand = SQLiteCB.GetInsertCommand(); SQLiteDA.DeleteCommand = SQLiteCB.GetDeleteCommand(); SQLiteDA.UpdateCommand = SQLiteCB.GetUpdateCommand(); SQLiteDA.Update(srcTable); } isok = true; } catch { ;} return isok; }
private void NuevaEncuesta_Load(object sender, EventArgs e) { appPath = Path.GetDirectoryName(Application.ExecutablePath); sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;"); cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //// appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string connString = @"Data Source= " + appPath2 + @"\DBESIL.s3db ;Version=3;"; //create the database query query = "SELECT * FROM DatosGenerales"; //create an OleDbDataAdapter to execute the query dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["Noencuesta"].ToString(); int autonum = Int32.Parse(num); label2.Text = (autonum + 1).ToString(); } else { label2.Text = "1"; } }
private void button5_Click(object sender, EventArgs e) { dataGridView1.Left = 247; string appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"; //create the database query string query = "SELECT * From Ventas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells; int i = 0; foreach (DataGridViewColumn c in dataGridView1.Columns) { i += c.Width; } dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2; button5.Visible = false; }
private void textBox3_TextChanged(object sender, EventArgs e) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"; string query = ""; if (textBox3.Text != "") { if (comboBox2.SelectedIndex == 0) { //create the database query query = "SELECT ArticuloID, Nombrearticulo From Almacen Where ArticuloID like '%" + textBox3.Text + "%'"; } else if (comboBox2.SelectedIndex == 1) { query = "SELECT ArticuloID, Nombrearticulo From Almacen Where Nombrearticulo like '%" + textBox3.Text + "%'"; } } else { query = "SELECT ArticuloID, Nombrearticulo From Almacen"; } //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView2.DataSource = bSource; dAdapter.Update(dTable); }
private void ReporteSalidaSeleccionada_Load(object sender, EventArgs e) { string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Salidas where Numero = '" + numero + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); DataRow Row = dTable.Rows[0]; System.Byte[] rdr = (System.Byte[])Row["Huella"]; Image imagen = ByteToImage(rdr); imagen.Save(appPath2 + @"\huella.jpg", System.Drawing.Imaging.ImageFormat.Jpeg); //-------------------------- CrystalReport8 objRpt = new CrystalReport8(); CrystalDecisions.Shared.ParameterValues RpDatos = new CrystalDecisions.Shared.ParameterValues(); CrystalDecisions.Shared.ParameterDiscreteValue DsCC = new CrystalDecisions.Shared.ParameterDiscreteValue(); CrystalDecisions.Shared.ParameterField paramField = new CrystalDecisions.Shared.ParameterField(); paramField.Name = "Imagen"; string appPath = Path.GetDirectoryName(Application.ExecutablePath); String ConnStr = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"; System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr); String Query1 = "SELECT * FROM Salidas where Numero = " + numero; //String Query1 = "Select * from SRDatosGenerales Where IDFormatoSillas = '"+idformatossillas+"'"; //String Query1 = "Select * from SRTamanoTipo Where IDFormatoSillas = '"+idformatossillas+"'"; System.Data.SQLite.SQLiteDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter(Query1, ConnStr); DataSet Ds = new DataSet(); // here my_dt is the name of the DataTable which we // created in the designer view. adapter.Fill(Ds, "DataTable5"); // Setting data source of our report object objRpt.SetDataSource(Ds); // Binding the crystalReportViewer with our report object. this.crystalReportViewer1.ReportSource = objRpt; appPath = appPath + @"\huella.jpg"; DsCC.Value = appPath; RpDatos.Add(DsCC); objRpt.DataDefinition.ParameterFields["Imagen"].ApplyCurrentValues(RpDatos); RpDatos.Clear(); paramField.HasCurrentValue = true; }
void TabTO(string nomer) { string Query = "SELECT date 'Дата ТО', id_work 'Вид ТО', pokazaniya 'Показания КМ', nextto 'Следующий ТО', id_person 'Провел ТО', operation 'Операция, материалы' FROM otchetto WHERE id_avto='" + nomer + "' ORDER BY date"; SQLiteCommand cmdDataBase = new SQLiteCommand(Query, DB.Con); SQLiteDataAdapter sda = new SQLiteDataAdapter(); sda.SelectCommand = cmdDataBase; DataTable dbdataset = new DataTable(); sda.Fill(dbdataset); BindingSource bSource = new BindingSource(); bSource.DataSource = dbdataset; dataGridView1.DataSource = bSource; sda.Update(dbdataset); }
private void button12_Click(object sender, EventArgs e) { byte[] pic = ImageToByte(pictureBox2.Image, System.Drawing.Imaging.ImageFormat.Jpeg); string appPath = Path.GetDirectoryName(Application.ExecutablePath); string conStringDatosUsuarios = @"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;"; SQLiteConnection con = new SQLiteConnection(conStringDatosUsuarios); SQLiteCommand cmd = con.CreateCommand(); cmd.CommandText = String.Format("INSERT INTO SRTamanoTipo VALUES ('" + label28.Text + "','" + textBox14.Text + "','" + textBox15.Text + "','" + textBox16.Text + "','" + textBox17.Text + "','" + textBox18.Text + "','" + textBox19.Text + "','" + comboBox2.Text + "','" + comboBox3.Text + "','" + comboBox4.Text + "', @0);"); SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary); param.Value = pic; cmd.Parameters.Add(param); con.Open(); try { cmd.ExecuteNonQuery(); } catch (Exception exc1) { MessageBox.Show(exc1.Message); } con.Close(); //----- cmd.CommandText = String.Format("INSERT INTO SRDatosGenerales VALUES ('" + label28.Text + "','" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "','" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "','" + textBox1.Text + "','" + textBox2.Text + "','" + comboBox1.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "', '" + textBox8.Text + "', '" + textBox9.Text + "', '" + textBox10.Text + "', '" + textBox11.Text + "', '" + textBox12.Text + "', '" + textBox13.Text + "');"); con.Open(); try { cmd.ExecuteNonQuery(); } catch (Exception exc1) { MessageBox.Show(exc1.Message); } con.Close(); panel1.BringToFront(); this.Controls.Clear(); this.InitializeComponent(); string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBESIL.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM SRTamanoTipo"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["IDFormatoSillas"].ToString(); int autonum = Int32.Parse(num); label28.Text = (autonum + 1).ToString(); } else { label28.Text = "1"; } }
private void button1_Click(object sender, EventArgs e) { if (textBox5.Text != "" && textBox4.Text != "" && textBox3.Text != "" && textBox2.Text != "") { try { ///create the connection string string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;"; string nombre = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); string usuario = dataGridView1.SelectedRows[0].Cells[1].Value.ToString(); string tipodeusuario = dataGridView1.SelectedRows[0].Cells[2].Value.ToString(); //create the database query string query = "SELECT * FROM Usuarios WHERE Nombre = '" + nombre + "' AND Usuario = '" + usuario + "' AND TipoDeUsuario = '" + tipodeusuario + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); DataRow Row = dTable.Rows[0]; string ID = Row["ID"].ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "UPDATE Usuarios SET Nombre = '" + textBox5.Text + "', Usuario = '" + textBox2.Text + "', Contrasena = '" + textBox3.Text + "', TipoDeUsuario = '" + comboBox1.Text + "', FechaContrasena = '" + DateTime.Now.ToShortDateString() + "' WHERE ID = '" + ID + "'"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Usuario modificado exitosamente"); appPath = Path.GetDirectoryName(Application.ExecutablePath); connString = @"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;"; //create the database query query = "select Nombre,Usuario,TipoDeUsuario from Usuarios"; //create an OleDbDataAdapter to execute the query dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } catch { MessageBox.Show("No hay usuarios que modificar"); } } else { MessageBox.Show("No puede dejar espacios en blanco"); } }
private void button2_Click(object sender, EventArgs e) { string numeroencuesta = ""; try { numeroencuesta = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para borrar cmd.CommandText = "DELETE FROM DatosGenerales WHERE [Noencuesta] = " + numeroencuesta; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); //comando sql para borrar cmd.CommandText = "DELETE FROM CuadroFamiliar WHERE [Noencuesta] = " + numeroencuesta; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); //comando sql para borrar cmd.CommandText = "DELETE FROM DatosVivienda WHERE [Noencuesta] = " + numeroencuesta; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); //comando sql para borrar cmd.CommandText = "DELETE FROM EgresosMensuales WHERE [Noencuesta] = " + numeroencuesta; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); //comando sql para borrar cmd.CommandText = "DELETE FROM Observaciones WHERE [Noencuesta] = " + numeroencuesta; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); //comando sql para borrar cmd.CommandText = "DELETE FROM ServicioMedico WHERE [Noencuesta] = " + numeroencuesta; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Encuesta eliminada exitosamente"); appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;"; //create the database query string query = "select Noencuesta AS [Numero de Encuesta], Programa, Nombre, Lugardeorigen as [Lugar de Origen], Domicilio, Tiempopermanencia as [Tiempo de Permanencia], NoIntegrantesFam as [Numero de Integrantes Familiares], Parroquia from DatosGenerales"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } catch { MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void button1_Click(object sender, EventArgs e) { ///create the connection string string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Usuarios"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); bool usuarioexistente = false; for (int i = 0; i < dTable.Rows.Count; i++) { DataRow Row = dTable.Rows[i]; if (Row["Usuario"].ToString() == textBox1.Text && Row["Contrasena"].ToString() == textBox2.Text) { MessageBox.Show("Bienvenido al Sistema Caritas"); ((Form)this.MdiParent).Controls["menuStrip1"].Enabled = true; usuarioexistente = true; string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "Update Usuarios Set FechaUltimaSesion = '" + DateTime.Now.ToShortDateString() + "' Where ID = '" + Row["ID"].ToString() + "'"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); string prueba = Row["TipoDeUsuario"].ToString(); if (Row["TipoDeUsuario"].ToString() == "Administrador") { Form frm = (Form)this.MdiParent; MenuStrip ms = (MenuStrip)frm.Controls["menuStrip1"]; ToolStripMenuItem ti = (ToolStripMenuItem)ms.Items["controlDeUsuariosToolStripMenuItem"]; ti.Visible = true; } Bienvenida.tipouser = Row["TipoDeUsuario"].ToString(); this.Close(); //-------------- appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;"; //create the database query query = "SELECT * FROM Almacen"; //create an OleDbDataAdapter to execute the query dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count > 0) { for (int k = 0; k < dTable.Rows.Count; k++) { Row = dTable.Rows[k]; Int32 cantidadexistencia = Int32.Parse(Row["Cantidadexistencia"].ToString()); string nombre = Row["Nombrearticulo"].ToString(); appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string connString = @"Data Source= " + appPath2 + @"\DBstk.s3db ;Version=3;"; //create the database query query = "SELECT * FROM Existencia Where ArticuloID = '" + Row["ArticuloID"].ToString() + "'"; //create an OleDbDataAdapter to execute the query dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable2 = new DataTable(); //fill the DataTable dAdapter.Fill(dTable2); dAdapter.Update(dTable2); if (dTable2.Rows.Count > 0) { Row = dTable2.Rows[0]; Int64 cantlimite = Int64.Parse(Row["Limite"].ToString()); if (cantlimite >= cantidadexistencia) { MessageBox.Show("Quedan " + cantlimite.ToString() + " o menos en existencia del articulo " + nombre + ""); } } } } //-------------- break; } } if (usuarioexistente == false) { MessageBox.Show("Usuario o contraseña incorrecta"); } }
private void VerMoficarBitacora_Load(object sender, EventArgs e) { if (entradasalida == "Entradas") { panel1.BringToFront(); string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Entradas where Numero = " + numero; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[0]; string num = Row["Numero"].ToString(); int autonum = Int32.Parse(num); label1.Text = (autonum).ToString(); textBox1.Text = Row["Entrantes"].ToString(); textBox2.Text = Row["Descripcion"].ToString(); textBox3.Text = Row["Cantidad"].ToString(); textBox4.Text = Row["Peso"].ToString(); textBox5.Text = Row["Equivalentes"].ToString(); textBox6.Text = Row["Notas"].ToString(); textBox7.Text = Row["Procedencia"].ToString(); comboBox1.SelectedIndex = comboBox1.FindStringExact(Row["Utilidad"].ToString()); textBox8.Text = Row["Responsable"].ToString(); } else { } } else { panel2.BringToFront(); string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Salidas where Numero = " + numero; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[0]; string num = Row["Numero"].ToString(); int autonum = Int32.Parse(num); label12.Text = (autonum).ToString(); textBox9.Text = Row["Salida"].ToString(); textBox11.Text = Row["Descripcion"].ToString(); textBox12.Text = Row["Cantidad"].ToString(); textBox14.Text = Row["Peso"].ToString(); textBox16.Text = Row["Equivalentes"].ToString(); textBox15.Text = Row["Nota"].ToString(); textBox13.Text = Row["TipoDeApoyo"].ToString(); textBox10.Text = Row["Responsable"].ToString(); } else { } } }
private void button2_Click(object sender, EventArgs e) { if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "") { bool edadesnumero = true; try { Int32.Parse(textBox2.Text); } catch { edadesnumero = false; } if (edadesnumero == true) { try { fecha = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); nombre = dataGridView1.SelectedRows[0].Cells[1].Value.ToString(); edad = Int32.Parse(dataGridView1.SelectedRows[0].Cells[2].Value.ToString()); apoyo = dataGridView1.SelectedRows[0].Cells[3].Value.ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "UPDATE Donaciones SET Nombre = '" + textBox1.Text + "', Edad = '" + textBox2.Text + "', Apoyo ='" + textBox3.Text + "', Comunidad = '" + textBox4.Text + "' WHERE Nombre='" + nombre + "' AND Edad='" + edad + "' AND Apoyo = '" + apoyo + "'"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Cambios guardados con exito"); appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;"; //create the database query string query = "select * from Donaciones"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } catch { MessageBox.Show("No se ha seleccionado ninguna donacion para modificar"); } } else { MessageBox.Show("Solo se aceptan numeros en el campo de edad"); } } else { MessageBox.Show("No se pueden guardar los cambios ya que hay campos en blanco"); } }
private void button4_Click(object sender, EventArgs e) { bool matched = false; Int32 err = 0; err = m_FPM.MatchTemplate(m_RegMin1, m_RegMin2, m_SecurityLevel, ref matched); if ((err == (Int32)SGFPMError.ERROR_NONE)) { if (matched) { // Save template after merging two template - m_FetBuf1, m_FetBuf2 Byte[] merged_template; Int32 buf_size = 0; m_FPM.GetTemplateSizeAfterMerge(m_RegMin1, m_RegMin2, ref buf_size); merged_template = new Byte[buf_size]; m_FPM.MergeAnsiTemplate(m_RegMin1, m_RegMin2, merged_template); if (m_StoredTemplate == null) { m_StoredTemplate = new Byte[buf_size]; merged_template.CopyTo(m_StoredTemplate, 0); } else { Int32 new_size = 0; err = m_FPM.GetTemplateSizeAfterMerge(m_StoredTemplate, merged_template, ref new_size); Byte[] new_enroll_template = new Byte[new_size]; err = m_FPM.MergeAnsiTemplate(merged_template, m_StoredTemplate, new_enroll_template); m_StoredTemplate = new Byte[new_size]; new_enroll_template.CopyTo(m_StoredTemplate, 0); } StatusBar.Text = "Template registration success"; //////////////////////////////////// PictureBox picturebox3 = new PictureBox(); string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Usuarios where Usuario = '" + textBox1.Text + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count >= 1) { DataRow Row = dTable.Rows[0]; if (Row["Huella"] != null) { System.Byte[] fp_image = (System.Byte[])Row["Huella"]; /////////////////////////////////////////////////// Int32 error = (Int32)SGFPMError.ERROR_NONE; Int32 img_qlty = 0; m_FPM.GetImageQuality(m_ImageWidth, m_ImageHeight, fp_image, ref img_qlty); if (error == (Int32)SGFPMError.ERROR_NONE) { DrawImage(fp_image, picturebox3); SGFPMFingerInfo finger_info = new SGFPMFingerInfo(); finger_info.FingerNumber = (SGFPMFingerPosition)1; finger_info.ImageQuality = (Int16)img_qlty; finger_info.ImpressionType = (Int16)SGFPMImpressionType.IMPTYPE_LP; finger_info.ViewNumber = 1; // Create template error = m_FPM.CreateTemplate(finger_info, fp_image, m_VrfMin); if (error == (Int32)SGFPMError.ERROR_NONE) { StatusBar.Text = "Verification image is captured"; if (m_StoredTemplate == null) { StatusBar.Text = "No data to verify"; return; } string[] fingerpos_str = new string[] { "Unknown finger", "Right thumb", "Right index finger", "Right middle finger", "Right ring finger", "Right little finger", "Left thumb", "Left index finger", "Left middle finger", "Left ring finger", "Left little finger" }; SGFPMFingerPosition finger_pos = SGFPMFingerPosition.FINGPOS_UK; bool finger_found = false; SGFPMANSITemplateInfo sample_info = new SGFPMANSITemplateInfo(); err = m_FPM.GetAnsiTemplateInfo(m_StoredTemplate, sample_info); for (int i = 0; i < sample_info.TotalSamples; i++) { matched = false; err = m_FPM.MatchAnsiTemplate(m_StoredTemplate, i, m_VrfMin, 0, m_SecurityLevel, ref matched); if (matched) { finger_found = true; finger_pos = (SGFPMFingerPosition)sample_info.SampleInfo[i].FingerNumber; break; } } if (err == (Int32)SGFPMError.ERROR_NONE) { if (finger_found) { StatusBar.Text = "The matched data found. Finger position: " + fingerpos_str[(Int32)finger_pos]; textBox2.Text = Row["Contrasena"].ToString(); button1.Enabled = true; button1.PerformClick(); } else { StatusBar.Text = "Cannot find a matched data"; } } else { StatusBar.Text = "MatchAnsiTemplate() Error : " + err; } } else { StatusBar.Text = "GetMinutiae() Error : " + error; } } else { StatusBar.Text = "GetImage() Error : " + error; } } else { MessageBox.Show("No hay huella con la cual comparar"); } //////////////////////////////////////////////// } else { MessageBox.Show("El usuario no existe U olvido colocarlo"); } } else { StatusBar.Text = "Template registration failed"; } } else { StatusBar.Text = "MatchTemplate() Error: " + err; } }
private void button1_Click_1(object sender, EventArgs e) { bool foliop = textBox12.Text.All(Char.IsNumber); bool folionon = false; string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Data Source=" + appPath2 + @"\EXCL.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Expediente"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); for (int i = 0; i < dTable.Rows.Count; i++) { DataRow Row = dTable.Rows[i]; if (textBox12.Text == Row["Folio"].ToString()) { folionon = true; break; } } bool edadp = textBox2.Text.All(Char.IsNumber); float pesov; bool pesop = float.TryParse(textBox6.Text, out pesov); if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && textBox6.Text != "" && textBox7.Text != "" && textBox8.Text != "" && textBox9.Text != "" && textBox10.Text != "" && textBox11.Text != "" && textBox12.Text != "" && textBox13.Text != "" && textBox14.Text != "" && textBox15.Text != "" && textBox16.Text != "" && textBox17.Text != "" && textBox18.Text != "" && textBox19.Text != "" && textBox20.Text != "" && textBox21.Text != "" && textBox22.Text != "") { if (edadp == true) { if (pesop == true) { if (folionon == false) { if (foliop == true) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "INSERT INTO Expediente (Folio, Nombre, Sexo, Edad, Ocupacion, Estadocivil, Religion, TA, Peso, Tema,FC, FR, EnfermedadesFamiliares, AreaAfectada, Antecedentes, Habitos,GPAC,FUMFUP,Motivo, CuadroClinico, ID, EstudiosSolicitados, TX, PX, Doctor, CP, SSA) VALUES ('" + textBox12.Text + "','" + textBox1.Text + "', '" + comboBox1.Text + "', '" + textBox2.Text + "', '" + textBox4.Text + "', '" + comboBox2.Text + "', '" + textBox3.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + textBox8.Text + "', '" + textBox9.Text + "', '" + textBox10.Text + "', '" + comboBox3.Text + "', '" + textBox11.Text + "', '" + textBox13.Text + "', '" + comboBox4.Text + "', '" + comboBox5.Text + "', '" + textBox14.Text + "', '" + textBox15.Text + "', '" + textBox16.Text + "', '" + textBox17.Text + "', '" + textBox18.Text + "', '" + textBox19.Text + "', '" + textBox20.Text + "', '" + textBox21.Text + "', '" + textBox22.Text + "')"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); this.Close(); } else { MessageBox.Show("Solo introduzca numeros en el folio", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Ya existe un expediente con el mismo folio", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Solo introduzca numeros en el peso", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Solo introduzca numeros en la edad", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Ha dejado campos en blanco", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void button1_Click(object sender, EventArgs e) { string idformatosilla = ""; try { idformatosilla = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para borrar cmd.CommandText = "DELETE FROM SRDatosGenerales WHERE [IDFormatoSillas] = " + idformatosilla; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); //comando sql para borrar cmd.CommandText = "DELETE FROM SRTamanoTipo WHERE [IDFormatoSillas] = " + idformatosilla; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Expediente eliminado exitosamente"); appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;"; //create the database query string query = "select * from SRDatosGenerales"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } catch { MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public ModificarExpSillas(string IDFormatoSillas) { InitializeComponent(); idformatossillas = IDFormatoSillas; string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBESIL.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM SRDatosGenerales where IDFormatoSillas = '" + idformatossillas + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); DataRow Row = dTable.Rows[0]; label28.Text = Row["IDFormatoSillas"].ToString(); dateTimePicker1.Value = DateTime.Parse(Row["Fecha"].ToString()); dateTimePicker2.Value = DateTime.Parse(Row["Fechadenacimiento"].ToString()); textBox1.Text = Row["Edad"].ToString(); textBox2.Text = Row["Nombre"].ToString(); comboBox1.SelectedIndex = comboBox1.FindStringExact(Row["Genero"].ToString()); textBox3.Text = Row["ClinicaAsociacionMedica"].ToString(); textBox4.Text = Row["Direccion"].ToString(); textBox5.Text = Row["Ciudad"].ToString(); textBox6.Text = Row["Estado"].ToString(); textBox7.Text = Row["Pais"].ToString(); textBox8.Text = Row["Telefono"].ToString(); textBox9.Text = Row["Fax"].ToString(); textBox10.Text = Row["Email"].ToString(); textBox11.Text = Row["TipoDiscapacidad"].ToString(); textBox12.Text = Row["Estatura"].ToString(); textBox13.Text = Row["Peso"].ToString(); //create the database query query = "SELECT * FROM SRTamanoTipo where IDFormatoSillas = '" + idformatossillas + "'"; //create an OleDbDataAdapter to execute the query dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); Row = dTable.Rows[0]; textBox14.Text = Row["Coronilla"].ToString(); textBox15.Text = Row["Hombro"].ToString(); textBox16.Text = Row["PiernaSuperior"].ToString(); textBox17.Text = Row["PiernaInferior"].ToString(); textBox18.Text = Row["Pecho"].ToString(); textBox19.Text = Row["Cadera"].ToString(); comboBox2.SelectedIndex = comboBox2.FindStringExact(Row["SentarseSinAyuda"].ToString()); comboBox3.SelectedIndex = comboBox2.FindStringExact(Row["SoporteCabeza"].ToString()); comboBox4.SelectedIndex = comboBox2.FindStringExact(Row["SoporteCuerpo"].ToString()); System.Byte[] rdr = (System.Byte[])Row["Foto"]; pictureBox2.Image = ByteToImage(rdr); }
/// <summary> /// Executes the respective command for each inserted, updated, or deleted row in the DataSet. /// </summary> /// <remarks> /// e.g.: /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); /// </remarks> /// <param name="insertCommand">A valid transact-SQL statement or stored procedure to insert new records into the data source</param> /// <param name="deleteCommand">A valid transact-SQL statement or stored procedure to delete records from the data source</param> /// <param name="updateCommand">A valid transact-SQL statement or stored procedure used to update records in the data source</param> /// <param name="dataSet">The DataSet used to update the data source</param> /// <param name="tableName">The DataTable used to update the data source.</param> public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName) { if (insertCommand == null) throw new ArgumentNullException("insertCommand"); if (deleteCommand == null) throw new ArgumentNullException("deleteCommand"); if (updateCommand == null) throw new ArgumentNullException("updateCommand"); if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException("tableName"); // Create a SQLiteDataAdapter, and dispose of it after we are done using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter()) { // Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update(dataSet, tableName); // Commit all the changes made to the DataSet dataSet.AcceptChanges(); } }
public static void updateTable(DataTable table , string query) { using (SQLiteTransaction txn = sqliteCon.BeginTransaction()) { using (SQLiteDataAdapter dbAdapter = new SQLiteDataAdapter(query, sqliteCon)) { dbAdapter.InsertCommand = new SQLiteCommandBuilder(dbAdapter).GetInsertCommand(true); dbAdapter.Update(table); } txn.Commit(); } }
private static DataTable SaveDynamicEntity(tgDataRequest request) { bool needToDelete = request.EntitySavePacket.RowState == tgDataRowState.Deleted; DataTable dataTable = CreateDataTable(request); using (SQLiteDataAdapter da = new SQLiteDataAdapter()) { da.AcceptChangesDuringUpdate = false; DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); SQLiteCommand cmd = null; switch (request.EntitySavePacket.RowState) { case tgDataRowState.Added: cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket); SetModifiedValues(request, request.EntitySavePacket, row); break; case tgDataRowState.Modified: cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket); SetOriginalValues(request, request.EntitySavePacket, row, false); SetModifiedValues(request, request.EntitySavePacket, row); row.AcceptChanges(); row.SetModified(); break; case tgDataRowState.Deleted: cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request); SetOriginalValues(request, request.EntitySavePacket, row, true); row.AcceptChanges(); row.Delete(); break; } if (!needToDelete && request.Properties != null) { request.Properties["tgDataRequest"] = request; request.Properties["esEntityData"] = request.EntitySavePacket; dataTable.ExtendedProperties["props"] = request.Properties; } DataRow[] singleRow = new DataRow[1]; singleRow[0] = row; try { if (!request.IgnoreComputedColumns) { da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(OnRowUpdated); } tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, request.EntitySavePacket, "SaveEntityDynamic", System.Environment.StackTrace)) { try { da.Update(singleRow); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { da.Update(singleRow); } } finally { tgTransactionScope.DeEnlist(cmd); } if (request.EntitySavePacket.RowState != tgDataRowState.Deleted && cmd.Parameters != null) { foreach (SQLiteParameter param in cmd.Parameters) { switch (param.Direction) { case ParameterDirection.Output: case ParameterDirection.InputOutput: request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value; break; } } } cmd.Dispose(); } return dataTable; }
/// <summary> /// JVUmaOutput処理 /// </summary> public static void JVUmaOutput() { DataTable umaDataTable = new DataTable(); string delDate = DateTime.Today.AddMonths(-6).ToString("yyyyMMdd"); // 出力データ取得 using (SQLiteCommand command = DbConn.CreateCommand()) { command.CommandText = "SELECT * FROM uma WHERE DelKubun='0' OR (DelKubun='1' AND DelDate>='" + delDate + "')"; using (SQLiteDataAdapter da = new SQLiteDataAdapter(command)) { da.Fill(umaDataTable); } } // 年替わり更新 if (JVRelayClass.IsNextYear == true) { foreach (DataRow umaDataRow in umaDataTable.Rows) { if (umaDataRow["UmaClass"].ToString() != GetUmaClass(umaDataRow)) { umaDataRow["UmaClass"] = GetUmaClass(umaDataRow); } } // データ更新 using (SQLiteTransaction tran = DbConn.BeginTransaction()) { using (SQLiteCommand command = DbConn.CreateCommand()) { command.Transaction = tran; command.CommandText = "SELECT * FROM uma"; using (SQLiteDataAdapter da = new SQLiteDataAdapter(command)) using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da)) { cb.SetAllValues = false; cb.ConflictOption = ConflictOption.OverwriteChanges; da.UpdateCommand = cb.GetUpdateCommand(); da.InsertCommand = cb.GetInsertCommand(); da.DeleteCommand = cb.GetDeleteCommand(); da.Update(umaDataTable); } } tran.Commit(); } } // 出力 foreach (DataRow dr in umaDataTable.Rows) { OutputUmaData(eOutput.Umanushi, dr); } }
private static DataTable SaveDynamicCollection_Deletes(tgDataRequest request) { SQLiteCommand cmd = null; DataTable dataTable = CreateDataTable(request); using (tgTransactionScope scope = new tgTransactionScope()) { using (SQLiteDataAdapter da = new SQLiteDataAdapter()) { da.AcceptChangesDuringUpdate = false; da.ContinueUpdateOnError = request.ContinueUpdateOnError; try { cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request); tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); DataRow[] singleRow = new DataRow[1]; // Delete each record foreach (tgEntitySavePacket packet in request.CollectionSavePacket) { DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); SetOriginalValues(request, packet, row, true); row.AcceptChanges(); row.Delete(); singleRow[0] = row; #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace)) { try { da.Update(singleRow); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { da.Update(singleRow); } if (row.HasErrors) { request.FireOnError(packet, row.RowError); } dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow } } finally { tgTransactionScope.DeEnlist(cmd); cmd.Dispose(); } } scope.Complete(); } return request.Table; }
private void NuevaEntradaSalidaBitacora_Load(object sender, EventArgs e) { comboBox1.SelectedIndex = 0; comboBox2.SelectedIndex = 0; string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Entradas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["Numero"].ToString(); int autonum = Int32.Parse(num); label1.Text = (autonum + 1).ToString(); } else { label1.Text = "1"; } //create the database query query = "SELECT * FROM Salidas"; //create an OleDbDataAdapter to execute the query dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["Numero"].ToString(); int autonum = Int32.Parse(num); label12.Text = (autonum + 1).ToString(); } else { label12.Text = "1"; } m_ImageWidth = 260; m_ImageHeight = 300; m_Dpi = 500; m_FPM = new SGFingerPrintManager(); Int32 error; SGFPMDeviceName device_name = SGFPMDeviceName.DEV_UNKNOWN; Int32 device_id = (Int32)SGFPMPortAddr.USB_AUTO_DETECT; device_name = SGFPMDeviceName.DEV_AUTO; m_DeviceOpened = false; if (device_name != SGFPMDeviceName.DEV_UNKNOWN) { error = m_FPM.Init(device_name); } else { error = m_FPM.InitEx(m_ImageWidth, m_ImageHeight, m_Dpi); } if (error == (Int32)SGFPMError.ERROR_NONE) { SGFPMDeviceInfoParam pInfo = new SGFPMDeviceInfoParam(); m_FPM.GetDeviceInfo(pInfo); m_ImageWidth = pInfo.ImageWidth; m_ImageHeight = pInfo.ImageHeight; StatusBar.Text = "Initialization Success"; pictureBox1.Visible = true; progressBar1.Visible = true; button3.Visible = true; button4.Visible = false; } else { StatusBar.Text = "Init() Error " + error; return; } // Set template format to ANSI 378 error = m_FPM.SetTemplateFormat(SGFPMTemplateFormat.ANSI378); // Get Max template size Int32 max_template_size = 0; error = m_FPM.GetMaxTemplateSize(ref max_template_size); m_RegMin1 = new Byte[max_template_size]; m_RegMin2 = new Byte[max_template_size]; m_VrfMin = new Byte[max_template_size]; // OpenDevice if device is selected if (device_name != SGFPMDeviceName.DEV_UNKNOWN) { error = m_FPM.OpenDevice(device_id); if (error == (Int32)SGFPMError.ERROR_NONE) { m_DeviceOpened = true; } else { StatusBar.Text = "OpenDevice() Error : " + error; } } }
private void ModificarUsuario_Load(object sender, EventArgs e) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;"; DataSet DS = new DataSet(); SQLiteConnection con = new SQLiteConnection(connString); con.Open(); SQLiteDataAdapter DA = new SQLiteDataAdapter("select Nombre,Usuario,TipoDeUsuario from Usuarios", con); DA.Fill(DS, "Usuarios"); dataGridView1.DataSource = DS.Tables["Usuarios"]; con.Close(); dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells; int i = 0; foreach (DataGridViewColumn c in dataGridView1.Columns) { i += c.Width; } if ((i + dataGridView1.RowHeadersWidth + 2) > 616) { dataGridView1.Width = 616; dataGridView1.Left = 79; } else { dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2; dataGridView1.Left = 211; } try { ///create the connection string string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;"; string nombre = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); string usuario = dataGridView1.SelectedRows[0].Cells[1].Value.ToString(); string tipodeusuario = dataGridView1.SelectedRows[0].Cells[2].Value.ToString(); //create the database query string query = "SELECT * FROM Usuarios WHERE Nombre = '" + nombre + "' AND Usuario = '" + usuario + "' AND TipoDeUsuario = '" + tipodeusuario + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); DataRow Row = dTable.Rows[0]; string contrasena = Row["Contrasena"].ToString(); textBox5.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); textBox2.Text = usuario; textBox3.Text = contrasena; textBox4.Text = contrasena; comboBox1.SelectedIndex = comboBox1.FindStringExact(tipodeusuario); } catch { textBox5.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; comboBox1.SelectedIndex = 1; } }
/// <summary> /// Turn a datatable into a table in the temporary database for the connection /// </summary> /// <param name="cnn">The connection to make the temporary table in</param> /// <param name="table">The table to write out</param> /// <param name="dest">The temporary table name to write to</param> private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest) { StringBuilder sql = new StringBuilder(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); using (SQLiteCommand cmd = cnn.CreateCommand()) using (DataTable source = new DataTable()) { sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest)); string separator = String.Empty; foreach (DataColumn dc in table.Columns) { DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType); string typeName = SQLiteConvert.DbTypeToTypeName(dbtypeName); sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator); separator = ", "; } sql.Append(")"); cmd.CommandText = sql.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest)); using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd)) { builder.DataAdapter = adp; adp.Fill(source); foreach (DataRow row in table.Rows) { object[] arr = row.ItemArray; source.Rows.Add(arr); } adp.Update(source); } } }
// Utilizes the SQLiteCommandBuilder, which in turn utilizes SQLiteDataReader's GetSchemaTable() functionality internal void InsertMany(DbConnection cnn, bool bWithIdentity) { int nmax = 1000; using (DbTransaction dbTrans = cnn.BeginTransaction()) { using (DbDataAdapter adp = new SQLiteDataAdapter()) { using (DbCommand cmd = cnn.CreateCommand()) { cmd.Transaction = dbTrans; cmd.CommandText = "SELECT * FROM TestCase WHERE 1=2"; adp.SelectCommand = cmd; using (DbCommandBuilder bld = new SQLiteCommandBuilder()) { bld.DataAdapter = adp; using (adp.InsertCommand = (SQLiteCommand)((ICloneable)bld.GetInsertCommand()).Clone()) { bld.DataAdapter = null; if (bWithIdentity) { adp.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS [ID]"; adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord; } using (DataTable tbl = new DataTable()) { adp.Fill(tbl); for (int n = 0; n < nmax; n++) { DataRow row = tbl.NewRow(); row[1] = n + nmax; tbl.Rows.Add(row); } frm.Write(String.Format(" InsertMany{0} ({1} rows) Begins ... ", (bWithIdentity == true) ? "WithIdentityFetch" : " ", nmax)); int dtStart = Environment.TickCount; adp.Update(tbl); int dtEnd = Environment.TickCount; dtEnd -= dtStart; frm.Write(String.Format("Ends in {0} ms ... ", (dtEnd))); dtStart = Environment.TickCount; dbTrans.Commit(); dtEnd = Environment.TickCount; dtEnd -= dtStart; frm.WriteLine(String.Format("Commits in {0} ms", (dtEnd))); } } } } } } }
private void textBox12_TextChanged_1(object sender, EventArgs e) { ///create the connection string string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Almacen Where ArticuloID = '" + textBox12.Text + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); label27.Text = ""; textBox10.Text = ""; textBox9.Text = ""; for (int i = 0; i < dTable.Rows.Count; i++) { DataRow Row = dTable.Rows[i]; label27.Text = Row["Cantidadexistencia"].ToString(); cantidadtotal = Int32.Parse(Row["Cantidadexistencia"].ToString()); textBox10.Text = Row["Precioventa"].ToString(); textBox9.Text = Row["Preciocompra"].ToString(); } ///create the connection string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string connString = @"Data Source= " + appPath2 + @"\DBstk.s3db ;Version=3;"; //create the database query query = "SELECT * FROM Existencia Where ArticuloID = '" + textBox12.Text + "'"; //create an OleDbDataAdapter to execute the query dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); for (int i = 0; i < dTable.Rows.Count; i++) { DataRow Row = dTable.Rows[i]; textBox14.Text = Row["Limite"].ToString(); } }
private void button3_Click_1(object sender, EventArgs e) { int cant = 0; bool cantidad = Int32.TryParse(textBox6.Text, out cant); float vent = 0.0F; bool venta = float.TryParse(textBox7.Text, out vent); float comp = 0.0F; bool compra = float.TryParse(textBox8.Text, out comp); if (textBox1.Text != "" && cantidad == true && compra == true && venta == true) { try { bool existeprov = false; ///create the connection string string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Proveedor Where Nombreproveedor = '" + textBox5.Text + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); for (int i = 0; i < dTable.Rows.Count; i++) { DataRow Row = dTable.Rows[i]; if (Row["Nombreproveedor"].ToString() == textBox5.Text) { existeprov = true; } } if (existeprov == true) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBPInc.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "INSERT INTO Almacen Values ('" + Int64.Parse(textBox4.Text) + "', '" + textBox1.Text + "', '" + float.Parse(textBox7.Text) + "', '" + float.Parse(textBox8.Text) + "', '" + Int32.Parse(textBox6.Text) + "' ,'" + textBox5.Text + "', '" + DateTime.Now.ToShortDateString() + "')"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBstk.s3db ;Version=3;"); cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "INSERT INTO Existencia Values('"+Int64.Parse(textBox4.Text)+"', '"+Int64.Parse(textBox13.Text)+"')"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); textBox4.Text = ""; textBox5.Text = ""; textBox6.Text = ""; textBox7.Text = ""; textBox8.Text = ""; textBox1.Text = ""; textBox13.Text = ""; } else { MessageBox.Show("El Proveedor Especificado no existe"); } } catch { MessageBox.Show("Ya existe un articulo con esa ID"); } } else { if (textBox1.Text != "") { MessageBox.Show("No ha desginado un nombre para el articulo"); } else if (cantidad == false) { MessageBox.Show("La cantidad puesta no es valida"); } else if (compra == false) { MessageBox.Show("La cantidad puesta no es valida en el campo de compra"); } else if (venta == false) { MessageBox.Show("La cantidad puesta no es valida en el campo de venta"); } } }
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e) { if (tabControl1.SelectedIndex == 2) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"; //create the database query string query = "SELECT * From Almacen"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells; int i = 0; foreach (DataGridViewColumn c in dataGridView1.Columns) { i += c.Width; } dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2; } else if (tabControl1.SelectedIndex == 3) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"; //create the database query string query = "SELECT ArticuloID, Nombrearticulo From Almacen"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView2.DataSource = bSource; dAdapter.Update(dTable); dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells; int i = 0; foreach (DataGridViewColumn c in dataGridView2.Columns) { i += c.Width; } dataGridView2.Width = i + dataGridView2.RowHeadersWidth + 2; BarcodeLib.Barcode barcode = new BarcodeLib.Barcode() { IncludeLabel = true, Alignment = AlignmentPositions.CENTER, Width = 300, Height = 100, RotateFlipType = RotateFlipType.RotateNoneFlipNone, BackColor = Color.White, ForeColor = Color.Black, }; try { Image img = barcode.Encode(TYPE.CODE128B, dataGridView2.SelectedRows[0].Cells[0].Value.ToString()); pictureBox1.Image = img; } catch { } } }
private void button1_Click(object sender, EventArgs e) { if (comboBox2.SelectedIndex == 0) { int cantidad; if (int.TryParse(textBox3.Text, out cantidad)) { float peso; if (float.TryParse(textBox4.Text, out peso)) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "INSERT INTO Entradas values ('" + label1.Text + "', '" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + comboBox1.Text + "', '" + textBox8.Text + "')"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Entrada guardada con exito."); textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; textBox5.Text = ""; textBox6.Text = ""; textBox7.Text = ""; comboBox1.SelectedIndex = 0; textBox8.Text = ""; ///create the connection string string connString = @"Data Source= " + appPath + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Entradas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["Numero"].ToString(); int autonum = Int32.Parse(num); label1.Text = (autonum + 1).ToString(); } else { label1.Text = "1"; } } else { MessageBox.Show("Revise de nuevo su campo de peso, solo se permiten numeros flotantes."); } } else { MessageBox.Show("Revise de nuevo su campo de cantidad, solo se permiten numeros."); } } }
public void Update(DataTable table) { using (SQLiteConnection connection = new SQLiteConnection(this.GetConnectionString())) { this._connection = connection; SQLiteDataAdapter adapter = new SQLiteDataAdapter( string.Format("SELECT * FROM {0}", table.TableName), connection); SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(adapter); adapter.RowUpdated += OnRowUpdated; try { adapter.Update(table); } catch (Exception) { throw; } } }
static void Main(string[] args) { string dbLocation = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "tilecache.s3db"); int minz = 7; int maxz = 10; double minx = -95.844727; double miny = 35.978006; double maxx = -88.989258; double maxy = 40.563895; string mapServiceUrl = "http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Census_USA/MapServer"; int maxDegreeOfParallelism = 10; bool replaceExistingCacheDB = true; bool showHelp = false; bool verbose = false; string mapServiceType = "agsd"; string settings = string.Empty; bool showInfo = false; ITileUrlSource tileSource = new OSMTileUrlSource() { MapServiceUrl = TileHelper.OSM_BASE_URL_TEMPLATE, }; var options = new OptionSet() { {"h|?|help", "Show this message and exits", h => showHelp = h != null}, {"v|verbose", "Display verbose information logs while running", v => verbose = v != null}, {"t|type=", "Type of the map service to be cached", t => mapServiceType = t.ToLower()}, {"m|mapservice=", "Url of the Map Service to be cached", m => mapServiceUrl = m}, {"s|settings=", "Extra settings needed by the type of map service being used", s => settings = s}, {"o|output=", "Complete file path and file name where the tile cache needs to be outputted", o => dbLocation = o}, {"z|minz=", "Minimum zoom scale at which to begin caching", z => int.TryParse(z, out minz)}, {"Z|maxz=", "Maximum zoom scale at which to end caching", Z => int.TryParse(Z, out maxz)}, {"x|minx=", "Minimum X coordinate value of the extent to cache", x => double.TryParse(x, out minx)}, {"y|miny=", "Minimum Y coordinate value of the extent to cache", y => double.TryParse(y, out miny)}, {"X|maxx=", "Maximum X coordinate value of the extent to cache", X => double.TryParse(X, out maxx)}, {"Y|maxy=", "Maximum Y coordinate value of the extent to cache", Y => double.TryParse(Y, out maxy)}, {"p|parallelops=", "Limits the number of concurrent operations run by TileCutter", p => int.TryParse(p, out maxDegreeOfParallelism)}, {"r|replace=", "Delete existing tile cache MBTiles database if already present and create a new one.", r => Boolean.TryParse(r, out replaceExistingCacheDB)}, {"i|info=", "", i => showInfo = i != null} }; options.Parse(args); if (showHelp) { ShowHelp(options); return; } var tiles = GetTiles(minz, maxz, minx, miny, maxx, maxy); if (showInfo) { var zoomLevels = Enumerable.Range(0, 21); var tileCountByZoomLevel = new Dictionary<int, int>(); foreach (var level in zoomLevels) tileCountByZoomLevel[level] = 0; foreach (var tile in tiles) tileCountByZoomLevel[tile.Level]++; foreach (var item in tileCountByZoomLevel) if (item.Value > 0) Console.WriteLine(string.Format("Zoom level {0} - {1} tiles", item.Key, item.Value)); Console.WriteLine("Press any key to exit..."); Console.ReadKey(); return; } if (!string.IsNullOrEmpty(mapServiceType)) tileSource = GetTileSource(mapServiceType, mapServiceUrl, settings); string tileCacheDirectory = Path.GetDirectoryName(dbLocation); string tilecacheFileName = Path.GetFileNameWithoutExtension(dbLocation); if (!Directory.Exists(tileCacheDirectory)) { Console.WriteLine("The tilecache path provided is not valid"); return; } string errorLogFile = Path.Combine(tileCacheDirectory, tilecacheFileName + ".log"); //if the user option to delete existing tile cache db is true delete it or else add to it if (replaceExistingCacheDB && File.Exists(dbLocation)) File.Delete(dbLocation); //Connect to the sqlite database string connectionString = string.Format("Data Source={0}; FailIfMissing=False", dbLocation); using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); //Check if the 'metadata' table exists, if not create it var rows = connection.GetSchema("Tables").Select("Table_Name = 'metadata'"); if (!rows.Any()) { var command = connection.CreateCommand(); command.CommandText = "CREATE TABLE metadata (name text, value text);"; command.ExecuteNonQuery(); connection.Execute("CREATE UNIQUE INDEX name ON metadata ('name')"); connection.Execute(@"INSERT INTO metadata(name, value) values (@a, @b)", new[] { new { a = "name", b = "cache" }, new { a = "type", b = "overlay" }, new { a = "version", b = "1" }, new { a = "description", b = "some info here" }, new { a = "format", b = "png" }, new { a = "bounds", b = string.Format("{0},{1},{2},{3}", minx, miny, maxx, maxy)} }); } //Check if the 'images' table exists, if not create it rows = connection.GetSchema("Tables").Select("Table_Name = 'images'"); if (!rows.Any()) { var command = connection.CreateCommand(); command.CommandText = "CREATE TABLE [images] ([tile_id] INTEGER NOT NULL PRIMARY KEY, [tile_md5hash] VARCHAR(256) NOT NULL, [tile_data] BLOB NULL);"; command.ExecuteNonQuery(); command = connection.CreateCommand(); command.CommandText = "CREATE UNIQUE INDEX images_hash on images (tile_md5hash)"; command.ExecuteNonQuery(); } //Check if the 'map' table exists, if not create it rows = connection.GetSchema("Tables").Select("Table_Name = 'map'"); if (!rows.Any()) { var command = connection.CreateCommand(); command.CommandText = "CREATE TABLE [map] ([map_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [tile_id] INTEGER NOT NULL, [zoom_level] INTEGER NOT NULL, [tile_row] INTEGER NOT NULL, [tile_column] INTEGER NOT NULL);"; command.ExecuteNonQuery(); } } FileStream errorLog = File.Create(errorLogFile); StreamWriter errorWriter = new StreamWriter(errorLog); Console.WriteLine("Output Cache file is: " + dbLocation); BlockingCollection<TileImage> images = new BlockingCollection<TileImage>(); Task.Factory.StartNew(() => { ParallelOptions parallelOptions = new ParallelOptions() { MaxDegreeOfParallelism = maxDegreeOfParallelism }; Parallel.ForEach(tiles, parallelOptions, (tile) => { string tileUrl = tileSource.GetTileUrl(tile); WebClient client = new WebClient(); try { byte[] image = client.DownloadData(tileUrl); images.Add(new TileImage() { Tile = tile, Image = image }); if (verbose) Console.WriteLine(string.Format("Tile Level:{0}, Row:{1}, Column:{2} downloaded.", tile.Level, tile.Row, tile.Column)); } catch (WebException ex) { errorWriter.WriteLine(String.Format("{0},{1},{2} - {3}", tile.Level, tile.Column, tile.Row, ex.Message)); errorLog.Flush(); Console.WriteLine(string.Format("Error while downloading tile Level:{0}, Row:{1}, Column:{2} - {3}.", tile.Level, tile.Row, tile.Column, ex.Message)); return; } finally { client.Dispose(); } }); }).ContinueWith(t => { images.CompleteAdding(); if (verbose) Console.WriteLine("All downloads complete."); errorLog.Flush(); errorLog.Dispose(); }); int currentTileId = 1; Action<TileImage[]> processBatch = (batch) => { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand mapCommand = connection.CreateCommand(), imagesCommand = connection.CreateCommand(), tileCountCommand = connection.CreateCommand(), mapDeleteCommand = connection.CreateCommand(), imageDeleteCommand = connection.CreateCommand()) { tileCountCommand.CommandText = "select ifnull(max(tile_id), 1) from images"; var tileCountObj = tileCountCommand.ExecuteScalar(); if (tileCountObj != null) { int.TryParse(tileCountObj.ToString(), out currentTileId); currentTileId++; } //Create a dummy query for the [map] table and fill the adapter with it //the purpose of this is to get the table structure in a DataTable //the adapter also builds the insert command for it in when it is populated mapCommand.CommandText = "SELECT * FROM [map] WHERE 1 = 2"; SQLiteDataAdapter mapAdapter = new SQLiteDataAdapter(mapCommand); //Create a dummy query for the [images] table and fill the adapter with it //the purpose of this is to get the table structure in a DataTable //the adapter also builds the insert command for it in when it is populated imagesCommand.CommandText = "SELECT * FROM [images] WHERE 1 = 2"; SQLiteDataAdapter imagesAdapter = new SQLiteDataAdapter(imagesCommand); using (SQLiteCommandBuilder mapCmdBuilder = new SQLiteCommandBuilder(mapAdapter), imagesCmdBuilder = new SQLiteCommandBuilder(imagesAdapter)) { using (imagesAdapter.InsertCommand = (SQLiteCommand)((ICloneable)imagesCmdBuilder.GetInsertCommand()).Clone()) using (mapAdapter.InsertCommand = (SQLiteCommand)((ICloneable)mapCmdBuilder.GetInsertCommand()).Clone()) { imagesCmdBuilder.DataAdapter = null; mapCmdBuilder.DataAdapter = null; using (DataTable mapTable = new DataTable(), imagesTable = new DataTable()) { if (batch.Any()) { var conditions = batch.Select(b => string.Format("([zoom_level]={0} and [tile_row]={1} and [tile_column]={2})", b.Tile.Level, b.Tile.Row, b.Tile.Column)); var whereClause = string.Join("or", conditions); var mapDeleteStatement = string.Format("delete from map where {0}", whereClause); var imageDeleteStatement = string.Format("delete from images where [tile_id] in (select [tile_id] from map where {0})", whereClause); imageDeleteCommand.CommandText = imageDeleteStatement; imageDeleteCommand.ExecuteNonQuery(); mapDeleteCommand.CommandText = mapDeleteStatement; mapDeleteCommand.ExecuteNonQuery(); } imagesAdapter.Fill(imagesTable); mapAdapter.Fill(mapTable); //Dictionary to eliminate duplicate images within batch Dictionary<string, int> added = new Dictionary<string, int>(); //looping thru keys is safe to do here because //the Keys property of concurrentDictionary provides a snapshot of the keys //while enumerating //the TryGet & TryRemove inside the loop checks for items that were removed by another thread List<int> tileIdsInCurrentBatch = new List<int>(); foreach (var tileimg in batch) { string hash = Convert.ToBase64String(new System.Security.Cryptography.MD5CryptoServiceProvider().ComputeHash(tileimg.Image)); int tileid = -1; if (!added.ContainsKey(hash)) { mapCommand.CommandText = "SELECT [tile_id] FROM [images] WHERE [tile_md5hash] = @hash"; mapCommand.Parameters.Add(new SQLiteParameter("hash", hash)); object tileObj = mapCommand.ExecuteScalar(); if (tileObj != null && int.TryParse(tileObj.ToString(), out tileid)) added.Add(hash, tileid); else { tileid = currentTileId++; added.Add(hash, tileid); DataRow idr = imagesTable.NewRow(); idr["tile_md5hash"] = hash; idr["tile_data"] = tileimg.Image; idr["tile_id"] = added[hash]; imagesTable.Rows.Add(idr); } } tileIdsInCurrentBatch.Add(added[hash]); DataRow mdr = mapTable.NewRow(); mdr["zoom_level"] = tileimg.Tile.Level; mdr["tile_column"] = tileimg.Tile.Column; mdr["tile_row"] = tileimg.Tile.Row; mdr["tile_id"] = added[hash]; mapTable.Rows.Add(mdr); }//for loop thru images tileIdsInCurrentBatch.Clear(); imagesAdapter.Update(imagesTable); mapAdapter.Update(mapTable); transaction.Commit(); if (verbose) Console.WriteLine(String.Format("Saving an image batch of {0}.", batch.Length)); }//using for datatable }//using for insert command }//using for command builder }//using for select command } }//using for connection }; List<TileImage> tilebatch = new List<TileImage>(); foreach (var tileImage in images.GetConsumingEnumerable()) { tilebatch.Add(tileImage); if (tilebatch.Count < 50) continue; processBatch(tilebatch.ToArray()); tilebatch.Clear(); } if (verbose) Console.WriteLine("Saving remaining images that didn't fit into a batch."); processBatch(tilebatch.ToArray()); tilebatch.Clear(); if (verbose) Console.WriteLine("Creating Index on table [map] and Creating View [tiles]."); using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); connection.Execute("CREATE UNIQUE INDEX IF NOT EXISTS map_index on map (zoom_level, tile_column, tile_row)"); connection.Execute("CREATE VIEW IF NOT EXISTS tiles as SELECT map.zoom_level as zoom_level, map.tile_column as tile_column, map.tile_row as tile_row, images.tile_data as tile_data FROM map JOIN images on images.tile_id = map.tile_id"); connection.Close(); } Console.WriteLine("All Done !!!"); }
private void button4_Click(object sender, EventArgs e) { if (comboBox2.SelectedIndex == 1) { int cantidad; if (int.TryParse(textBox12.Text, out cantidad)) { float peso; if (float.TryParse(textBox14.Text, out peso)) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "INSERT INTO Salidas (Numero, Salida, Descripcion, Cantidad, Peso, Equivalentes, Nota, TipoDeApoyo, Responsable, Huella) values ('" + label12.Text + "', '" + textBox9.Text + "', '" + textBox11.Text + "', '" + textBox12.Text + "', '" + textBox14.Text + "', '" + textBox16.Text + "', '" + textBox15.Text + "', '" + textBox13.Text + "', '" + textBox10.Text + "',@0 )"; SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary); cmd.Connection = sqlConnection1; param.Value = fp_image; cmd.Parameters.Add(param); sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Salida guardada con exito."); textBox9.Text = ""; textBox11.Text = ""; textBox12.Text = ""; textBox14.Text = ""; textBox16.Text = ""; textBox15.Text = ""; textBox13.Text = ""; textBox10.Text = ""; ///create the connection string string connString = @"Data Source= " + appPath + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Salidas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["Numero"].ToString(); int autonum = Int32.Parse(num); label12.Text = (autonum + 1).ToString(); } else { label12.Text = "1"; } } else { MessageBox.Show("Revise de nuevo su campo de peso, solo se permiten numeros flotantes."); } } else { MessageBox.Show("Revise de nuevo su campo de cantidad, solo se permiten numeros."); } } }
//ͨ�������ݺ��� private string UpdateTable(DataTable srcTable, string tableName) { try { command.CommandText = "SELECT * FROM " + tableName; SQLiteDataAdapter oda = new SQLiteDataAdapter(command); SQLiteCommandBuilder ocb = new SQLiteCommandBuilder(oda); oda.InsertCommand = ocb.GetInsertCommand(); oda.DeleteCommand = ocb.GetDeleteCommand(); oda.UpdateCommand = ocb.GetUpdateCommand(); oda.Update(srcTable); } catch (Exception ex) { return ex.ToString(); } return "success"; }
public static void UpdateWorker(DataTable dt) { try { using (SQLiteConnection conn = new SQLiteConnection(Tehtava10SMOliotTalteen.Properties.Settings.Default.Tietokanta)) { conn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(); da.UpdateCommand = new SQLiteCommand("UPDATE player SET Fname = @newFname, Lname = @newLname, Transfer = @newTransfer, Team = @newTeam" + "WHERE (Fname = @oldFname) AND (Lname = @oldLname) AND (Transfer = @oldTransfer) AND (Team = @oldTeam)", conn); SQLiteParameter param1 = da.UpdateCommand.Parameters.Add("@oldFname", DbType.String, 20, "Fname"); param1.SourceVersion = DataRowVersion.Original; SQLiteParameter param2 = da.UpdateCommand.Parameters.Add("@oldLname", DbType.String, 30, "Lname"); param2.SourceVersion = DataRowVersion.Original; SQLiteParameter param3 = da.UpdateCommand.Parameters.Add("@oldTransfer", DbType.Int32, 20, "Transfer"); param3.SourceVersion = DataRowVersion.Original; SQLiteParameter param4 = da.UpdateCommand.Parameters.Add("@oldTeam", DbType.Int32, 20, "Team"); param4.SourceVersion = DataRowVersion.Original; SQLiteParameter paramA = da.UpdateCommand.Parameters.Add("@newFname", DbType.String, 20, "Fname"); paramA.SourceVersion = DataRowVersion.Current; SQLiteParameter paramB = da.UpdateCommand.Parameters.Add("@newLname", DbType.String, 30, "Lname"); paramB.SourceVersion = DataRowVersion.Current; SQLiteParameter paramC = da.UpdateCommand.Parameters.Add("@newTransfer", DbType.Int32, 20, "Transfer"); paramC.SourceVersion = DataRowVersion.Current; SQLiteParameter paramD = da.UpdateCommand.Parameters.Add("@newTeam", DbType.Int32, 20, "Team"); paramD.SourceVersion = DataRowVersion.Current; da.InsertCommand = new SQLiteCommand("INSERT INTO player (Fname, Lname, Transfer, Team) VALUES (@Fname, @Lname, @Transfer, @Team)", conn); da.InsertCommand.Parameters.Add("@Fname", DbType.String, 20, "Fname"); da.InsertCommand.Parameters.Add("@Lname", DbType.String, 30, "Lname"); da.InsertCommand.Parameters.Add("@Transfer", DbType.Int32, 20, "Transfer"); da.InsertCommand.Parameters.Add("@Team", DbType.Int32, 20, "Team"); da.DeleteCommand = new SQLiteCommand("DELETE FROM player WHERE (Fname = '@Fname') AND (Lname = '@Lname') AND (Transfer = '@Transfer') AND (Team = '@Team')", conn); da.DeleteCommand.Parameters.Add("@Fname", DbType.String, 20, "Fname"); da.DeleteCommand.Parameters.Add("@Lname", DbType.String, 30, "Lname"); da.DeleteCommand.Parameters.Add("@Transfer", DbType.Int32, 20, "Transfer"); da.DeleteCommand.Parameters.Add("@Team", DbType.Int32, 20, "Team"); da.Update(dt); conn.Close(); } } catch (Exception ex) { throw ex; } }
private void button1_Click(object sender, EventArgs e) { try { ///create the connection string string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;"; string nombre = dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); string usuario = dataGridView1.SelectedRows[0].Cells[1].Value.ToString(); string tipodeusuario = dataGridView1.SelectedRows[0].Cells[2].Value.ToString(); //create the database query string query = "SELECT * FROM Usuarios WHERE Nombre = '" +nombre+ "' AND Usuario = '" + usuario + "' AND TipoDeUsuario = '" + tipodeusuario + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); DataRow Row = dTable.Rows[0]; string ID = Row["ID"].ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "DELETE FROM Usuarios WHERE ID = '" + ID + "'"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Usuario eliminado exitosamente"); appPath = Path.GetDirectoryName(Application.ExecutablePath); connString = @"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;"; //create the database query query = "select Nombre,Usuario,TipoDeUsuario from Usuarios"; //create an OleDbDataAdapter to execute the query dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } catch { MessageBox.Show("No hay usuarios que borrar"); } }
void TabVod(string vodutel) { //MessageBox.Show(vodutel); string Query = "SELECT id_avto 'Гос.номер', date 'Дата ТО', id_work 'Вид ТО', pokazaniya 'Показания КМ', nextto 'Следующий ТО', id_person 'Провел ТО', operation 'Операция, материалы' FROM otchetto WHERE id_person LIKE '%" + vodutel + "%' ORDER BY id_avto"; string Query1 = "SELECT id_avto 'Гос.номер', date 'Дата ремонта', id_work 'Вид ремонта', pokazaniya 'Показания КМ', id_person 'Провел ремонт', operation 'Операция, материалы' FROM otchetrem WHERE id_person LIKE'%" + vodutel + "%' ORDER BY id_avto"; SQLiteCommand cmdDataBase = new SQLiteCommand(Query, DB.Con); SQLiteCommand cmdDataBase2 = new SQLiteCommand(Query1, DB.Con); SQLiteDataAdapter sda = new SQLiteDataAdapter(); SQLiteDataAdapter sda2 = new SQLiteDataAdapter(); sda.SelectCommand = cmdDataBase; sda2.SelectCommand = cmdDataBase2; DataTable dbdataset = new DataTable(); DataTable dbdataset2 = new DataTable(); sda.Fill(dbdataset); sda2.Fill(dbdataset2); BindingSource bSource = new BindingSource(); BindingSource bSource2 = new BindingSource(); bSource.DataSource = dbdataset; bSource2.DataSource = dbdataset2; dataGridView2.DataSource = bSource; dataGridView3.DataSource = bSource2; sda.Update(dbdataset); sda.Update(dbdataset2); }
/// <summary> /// JVUmaReadWriting処理 /// </summary> public static void JVUmaReadWriting() { bool reading = true; int nCount = 0; object buffObj = new byte[0]; int buffsize = 110000; string timeStamp; string buffname; DataTable umaDataTable = new DataTable(); DataTable raceDataTable = new DataTable(); DataTable raceUmaDataTable = new DataTable(); // 初期化時 if (JVRelayClass.Option == (int)JVRelayClass.eJVOpenFlag.SetupSkipDialog) { using (SQLiteCommand command = DbConn.CreateCommand()) { command.CommandText = "DELETE FROM uma"; command.ExecuteNonQuery(); } } using (SQLiteCommand command = DbConn.CreateCommand()) { command.CommandText = "SELECT * FROM uma"; using (SQLiteDataAdapter da = new SQLiteDataAdapter(command)) { da.Fill(umaDataTable); } } raceDataTable.Columns.Add("RaceKey", typeof(string)); raceDataTable.Columns.Add("RaceDate", typeof(string)); raceDataTable.Columns.Add("DataKubun", typeof(string)); raceDataTable.Columns.Add("GradeCD", typeof(string)); raceDataTable.Columns.Add("SyubetuCD", typeof(string)); raceUmaDataTable.Columns.Add("RaceKey", typeof(string)); raceUmaDataTable.Columns.Add("RaceDate", typeof(string)); raceUmaDataTable.Columns.Add("KettoNum", typeof(string)); raceUmaDataTable.Columns.Add("KakuteiJyuni", typeof(string)); if (umaDataTable.PrimaryKey.Length == 0) { umaDataTable.PrimaryKey = new[] { umaDataTable.Columns["KettoNum"] }; } if (raceDataTable.PrimaryKey.Length == 0) { raceDataTable.PrimaryKey = new[] { raceDataTable.Columns["RaceKey"] }; } if (raceUmaDataTable.PrimaryKey.Length == 0) { raceUmaDataTable.PrimaryKey = new[] { raceUmaDataTable.Columns["RaceKey"], raceUmaDataTable.Columns["KettoNum"] }; } ProgressUserState.Maxinum = ReadCount; ProgressUserState.Value = 0; ProgressUserState.Text = "データ読み込み中..."; MainBackgroundWorker.ReportProgress(0, ProgressUserState); do { //--------------------- // JVLink読込み処理 //--------------------- buffObj = new byte[0]; int nRet = AxJVLink.JVGets(ref buffObj, buffsize, out buffname); timeStamp = AxJVLink.m_CurrentFileTimeStamp; byte[] buff = (byte[])buffObj; string buffStr = System.Text.Encoding.GetEncoding(932).GetString(buff); // 正常 if (0 < nRet) { string recordSpec = JVData_Struct.MidB2S(ref buff, 1, 2); buffObj = new byte[0]; buff = new byte[0]; switch (recordSpec) { case "UM": { JVData_Struct.JV_UM_UMA uma = new JVData_Struct.JV_UM_UMA(); uma.SetDataB(ref buffStr); WriteDbUmaData(eOutput.Umanushi, uma, umaDataTable); } break; case "RA": { JVData_Struct.JV_RA_RACE race = new JVData_Struct.JV_RA_RACE(); race.SetDataB(ref buffStr); WriteDbRaceData(eOutput.Umanushi, race, raceDataTable); } break; case "SE": { JVData_Struct.JV_SE_RACE_UMA raceUma = new JVData_Struct.JV_SE_RACE_UMA(); raceUma.SetDataB(ref buffStr); WriteDbRaceUmaData(eOutput.Umanushi, raceUma, raceUmaDataTable); } break; default: // 対象外recspecのファイルをスキップする。 AxJVLink.JVSkip(); nCount++; ProgressUserState.Value = nCount; ProgressUserState.Text = "データ読み込み中..."; MainBackgroundWorker.ReportProgress(0, ProgressUserState); break; } } // ファイルの切れ目 else if (-1 == nRet) { nCount++; ProgressUserState.Value = nCount; ProgressUserState.Text = "データ読み込み中..."; MainBackgroundWorker.ReportProgress(0, ProgressUserState); } // 全レコード読込み終了(EOF) else if (0 == nRet) { ProgressUserState.Value = ProgressUserState.Maxinum; ProgressUserState.Text = "データ読み込み完了"; MainBackgroundWorker.ReportProgress(0, ProgressUserState); reading = false; } // エラー else if (-1 > nRet) { // エラーファイルをスキップする。 AxJVLink.JVSkip(); nCount++; ProgressUserState.Value = nCount; ProgressUserState.Text = "データ読み込み中..."; MainBackgroundWorker.ReportProgress(0, ProgressUserState); } System.Threading.Thread.Sleep(10); } while (true == reading); // データ整備 if (raceUmaDataTable.Rows.Count > 0) { foreach (DataRow raceUmaDataRow in raceUmaDataTable.Select("", "RaceDate")) { DataRow raceDataRow = raceDataTable.Rows.Find(raceUmaDataRow["RaceKey"]); DataRow umaDataRow = umaDataTable.Rows.Find(raceUmaDataRow["KettoNum"]); if (raceDataRow != null && umaDataRow != null) { string raceDate = umaDataRow["RaceDate"].ToString(); if ("" == raceDate || 0 > raceDate.CompareTo(raceUmaDataRow["RaceDate"].ToString())) { if (raceDataRow["DataKubun"].ToString() != "9" && raceDataRow["DataKubun"].ToString() != "0") { // レースを追加 umaDataRow["BeforeUmaClass"] = umaDataRow["UmaClass"]; umaDataRow["BeforeRaceDate"] = umaDataRow["RaceDate"]; umaDataRow["BeforeRaceDataKubun"] = umaDataRow["RaceDataKubun"]; umaDataRow["BeforeRaceGradeCD"] = umaDataRow["RaceGradeCD"]; umaDataRow["BeforeRaceSyubetuCD"] = umaDataRow["RaceSyubetuCD"]; umaDataRow["BeforeRaceKakuteiJyuni"] = umaDataRow["RaceKakuteiJyuni"]; umaDataRow["RaceDate"] = raceUmaDataRow["RaceDate"]; umaDataRow["RaceDataKubun"] = raceDataRow["DataKubun"]; umaDataRow["RaceGradeCD"] = raceDataRow["GradeCD"]; umaDataRow["RaceSyubetuCD"] = raceDataRow["SyubetuCD"]; umaDataRow["RaceKakuteiJyuni"] = raceUmaDataRow["KakuteiJyuni"]; umaDataRow["UmaClass"] = GetUmaClass(umaDataRow); } } else if (0 == raceDate.CompareTo(raceUmaDataRow["RaceDate"].ToString())) { if (raceDataRow["DataKubun"].ToString() == "9" || raceDataRow["DataKubun"].ToString() == "0") { // レース中止、データ削除のため戻す umaDataRow["UmaClass"] = umaDataRow["BeforeUmaClass"]; umaDataRow["RaceDate"] = umaDataRow["BeforeRaceDate"]; umaDataRow["RaceDataKubun"] = umaDataRow["BeforeRaceDataKubun"]; umaDataRow["RaceGradeCD"] = umaDataRow["BeforeRaceGradeCD"]; umaDataRow["RaceSyubetuCD"] = umaDataRow["BeforeRaceSyubetuCD"]; umaDataRow["RaceKakuteiJyuni"] = umaDataRow["BeforeRaceKakuteiJyuni"]; umaDataRow["BeforeUmaClass"] = null; umaDataRow["BeforeRaceDate"] = null; umaDataRow["BeforeRaceDataKubun"] = null; umaDataRow["BeforeRaceGradeCD"] = null; umaDataRow["BeforeRaceSyubetuCD"] = null; umaDataRow["BeforeRaceKakuteiJyuni"] = null; } else { // レース結果の更新 if (umaDataRow["RaceDataKubun"].ToString() != raceDataRow["DataKubun"].ToString()) { umaDataRow["RaceDataKubun"] = raceDataRow["DataKubun"]; } if (umaDataRow["RaceGradeCD"].ToString() != raceDataRow["GradeCD"].ToString()) { umaDataRow["RaceGradeCD"] = raceDataRow["GradeCD"]; } if (umaDataRow["RaceSyubetuCD"].ToString() != raceDataRow["SyubetuCD"].ToString()) { umaDataRow["RaceSyubetuCD"] = raceDataRow["SyubetuCD"]; } if (umaDataRow["RaceKakuteiJyuni"].ToString() != raceUmaDataRow["KakuteiJyuni"].ToString()) { umaDataRow["RaceKakuteiJyuni"] = raceUmaDataRow["KakuteiJyuni"]; } if (umaDataRow["UmaClass"].ToString() != GetUmaClass(umaDataRow)) { umaDataRow["UmaClass"] = GetUmaClass(umaDataRow); } } } } } } // データ更新 using (SQLiteTransaction tran = DbConn.BeginTransaction()) { using (SQLiteCommand command = DbConn.CreateCommand()) { command.Transaction = tran; command.CommandText = "SELECT * FROM uma"; using (SQLiteDataAdapter da = new SQLiteDataAdapter(command)) using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da)) { cb.SetAllValues = false; cb.ConflictOption = ConflictOption.OverwriteChanges; da.UpdateCommand = cb.GetUpdateCommand(); da.InsertCommand = cb.GetInsertCommand(); da.DeleteCommand = cb.GetDeleteCommand(); da.Update(umaDataTable); } command.CommandText = "DELETE FROM uma WHERE BirthYear <= '" + DiscardBirthYear + "'"; command.ExecuteNonQuery(); command.CommandText = "UPDATE timestamp SET date ='" + LastFileTimestamp + "'"; command.ExecuteNonQuery(); } tran.Commit(); JVRelayClass.DbTimeStamp = LastFileTimestamp; } }