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 ChangedDatabase() { m_adapter = new SQLiteDataAdapter("select * from serverlist", m_dbwrap.Database.Connection); SQLiteCommandBuilder cb = new SQLiteCommandBuilder(m_adapter); m_adapter.InsertCommand = cb.GetInsertCommand(); m_adapter.DeleteCommand = cb.GetDeleteCommand(); m_adapter.UpdateCommand = cb.GetUpdateCommand(); Reload(); }
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); } } }
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 applyButton_Click(object sender, EventArgs e) { SQLiteConnection mCN = ConnectionManager.connection; SQLiteDataAdapter mDA = modulesTableAdapter.Adapter; SQLiteCommandBuilder mCB = new SQLiteCommandBuilder(mDA); DataSet mDS = modulesDataSet; DataSet dsChanges = new DataSet(); if (!mDS.HasChanges()) return; dsChanges = mDS.GetChanges(DataRowState.Modified); if (dsChanges != null) { mDA.UpdateCommand = mCB.GetUpdateCommand(); mDA.Update(dsChanges, "modules"); } dsChanges = mDS.GetChanges(DataRowState.Added); if (dsChanges != null) { mDA.InsertCommand = mCB.GetInsertCommand(); mDA.Update(dsChanges, "modules"); } dsChanges = mDS.GetChanges(DataRowState.Deleted); if (dsChanges != null) { mDA.DeleteCommand = mCB.GetDeleteCommand(); mDA.Update(dsChanges, "modules"); } mDS.AcceptChanges(); UpdateModulesDropDown(); (treeView.Model as SlowTreeModel).Root.UpdateModulesFromDbRec(); treeView.Invalidate(); }
/// <summary> /// Загрузка авторов из БД /// </summary> /// <returns></returns> public AuthorList LoadAuthors() { if (ds != null) { ds.Dispose(); } ds = new DataSet(); AuthorList list = new AuthorList(); using (SQLiteCommand mycommand = new SQLiteCommand(conn)) { mycommand.CommandText = "select * from authors"; da = new SQLiteDataAdapter(); da.SelectCommand = mycommand; System.Data.SQLite.SQLiteCommandBuilder cb = new System.Data.SQLite.SQLiteCommandBuilder(da); da.Fill(ds, "authors"); foreach (DataRow row in ds.Tables["authors"].Rows) { var author = Xml2Author(row["author_xml"].ToString()); if (author != null) { author.Changed = false; list.Add(author); } } } return(list); }
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"); } }
/// <summary> /// Загрузить категории /// </summary> /// <returns></returns> public CategoryList LoadCategories() { if (ds != null) { ds.Dispose(); } ds = new DataSet(); CategoryList list = null; using (SQLiteCommand mycommand = new SQLiteCommand(conn)) { mycommand.CommandText = "select * from categories"; da = new SQLiteDataAdapter(); da.SelectCommand = mycommand; System.Data.SQLite.SQLiteCommandBuilder cb = new System.Data.SQLite.SQLiteCommandBuilder(da); da.Fill(ds, "categories"); foreach (DataRow row in ds.Tables["categories"].Rows) { string categories_xml = row["categories_xml"].ToString(); if (!string.IsNullOrEmpty(categories_xml)) { try { var reader = new StringReader(categories_xml); var sr = new XmlSerializer(typeof(CategoryList)); list = (CategoryList)sr.Deserialize(reader); foreach (Category category in list) { category.SetOwner(list); } list.Reorder(); return(list);//(CategoryList)sr.Deserialize(reader); } catch { } break; } } } if (list == null) { list = new CategoryList(); list.Add(new Category() { Name = "Default" }); } foreach (Category category in list) { category.SetOwner(list); } list.Reorder(); return(list); }
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 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); } }
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"); } }
internal KeyQuery(SQLiteConnection cnn, string database, string table, params string[] columns) { using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder()) { _command = cnn.CreateCommand(); for (int n = 0; n < columns.Length; n++) { columns[n] = builder.QuoteIdentifier(columns[n]); } } _command.CommandText = UnsafeNativeMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT {0} FROM [{1}].[{2}] WHERE ROWID = ?", String.Join(",", columns), database, table); _command.Parameters.AddWithValue(null, (long)0); }
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"; } }
/// <summary> /// Сохранение автора в БД /// </summary> /// <param name="author"></param> public void SaveAuthor(Author author) { lock (_locker) { if (string.IsNullOrEmpty(author.Id)) { //author.Id = Guid.NewGuid().ToString(); author.CheckID(); } //author.timeStamp = DateTime.Now.ToUniversalTime(); string author_xml = Author2Xml(author); if (!string.IsNullOrEmpty(author_xml)) { // проверим, есть ли такой автор в БД using (SQLiteCommand cmd = new SQLiteCommand("", conn)) { string sql = ""; sql = string.Format("select * from authors where id='{0}'", author.Id); cmd.CommandText = sql; int cnt = cmd.ExecuteNonQuery(); //long timeStamp = author.timeStamp.ToBinary(); if (ds != null) { ds.Dispose(); } ds = new DataSet(); da = new SQLiteDataAdapter(); da.SelectCommand = cmd; var cb = new System.Data.SQLite.SQLiteCommandBuilder(da); da.Fill(ds, "author"); if (ds.Tables["author"].Rows.Count == 0) { DataRow dr = ds.Tables["author"].NewRow(); dr["id"] = author.Id; ds.Tables["author"].Rows.Add(dr); } ds.Tables["author"].Rows[0]["author_xml"] = author_xml; //ds.Tables["author"].Rows[0]["stamp"] = timeStamp; da.Update(ds.Tables["author"]); //if (cnt > 0) // sql = string.Format("update authors set author_xml='{0}', stamp={1} where id='{1}'", author_xml, timeStamp, author.Id); //else // sql = string.Format("insert into authors (id, author_xml, stamp) ('{0}','{1}',{2})", author.Id, author_xml, timeStamp); //cmd.CommandText = sql; //cmd.ExecuteNonQuery(); } } } }
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; }
protected override void ProcessRecord() { base.BeginProcessing(); if (ShouldProcess("Database", "BeginTransaction")) { if (_Transaction == null) { _command = connection.CreateCommand(); _command.Transaction = connection.BeginTransaction(); } else { _command = _Transaction.Connection.CreateCommand(); _command.Transaction = _Transaction; } } _command.CommandText = "SELECT * FROM '" + name + "' LIMIT 1"; SQLiteDataAdapter adapter = new SQLiteDataAdapter(_command); SQLiteCommandBuilder commandbuilder = new SQLiteCommandBuilder(adapter); adapter.InsertCommand = (SQLiteCommand)commandbuilder.GetInsertCommand().Clone(); commandbuilder.DataAdapter = null; if (ShouldProcess("Database", "Update")) { foreach (DataRow row in inputobject.Rows) { if (row.RowState == DataRowState.Unchanged) { row.SetAdded(); } } adapter.Update(inputobject); } if (ShouldProcess("Transaction", "Commit")) { if (_command.Transaction != null) { if (_Transaction == null) { _command.Transaction.Commit(); } } } inputobject.AcceptChanges(); }
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; } }
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"; } }
/// <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 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 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); } }
/// <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); } }
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 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 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 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."); } } }
/// <summary> /// Сохранить категории /// </summary> public void SaveCategories(CategoryList categories) { long timeStamp = DateTime.Now.ToUniversalTime().ToBinary(); var xs = new XmlSerializer(typeof(CategoryList)); var sb = new StringBuilder(); var w = new StringWriter(sb, CultureInfo.InvariantCulture); xs.Serialize(w, categories, new XmlSerializerNamespaces(new[] { new XmlQualifiedName(string.Empty) })); string categories_xml = sb.ToString(); if (!string.IsNullOrEmpty(categories_xml)) { lock (_locker) { using (SQLiteCommand cmd = new SQLiteCommand("", conn)) { try { if (ds != null) { ds.Dispose(); } ds = new DataSet(); da = new SQLiteDataAdapter(); string sql = string.Format("select * from categories"); cmd.CommandText = sql; da.SelectCommand = cmd; var cb = new System.Data.SQLite.SQLiteCommandBuilder(da); da.Fill(ds, "categories"); // исправляем свой косяк, надо было изначально ID сделать, иначе SQLite без праймари ки ругается на датасет // это чтобы не перегенерировать БД if (!ds.Tables["categories"].Columns.Contains("id")) { // убьем таблицу категорий cmd.CommandText = "DROP TABLE categories"; cmd.ExecuteNonQuery(); // создаем таблицу категорий. Данных там мало и чтобы не мудохаться все храним в одной записи-строке sql = "create table categories(" + "id varchar PRIMARY KEY ,categories_xml text, stamp bigint)"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); // формируем датасет и дальше уже пишем данные ds = new DataSet(); da = new SQLiteDataAdapter(); sql = string.Format("select * from categories"); cmd.CommandText = sql; da.SelectCommand = cmd; cb = new System.Data.SQLite.SQLiteCommandBuilder(da); da.Fill(ds, "categories"); MainWindow.MainForm.GetLogger().Add( "Перегенерировали таблицу категорий (исправление некорректности)."); } if (ds.Tables["categories"].Rows.Count == 0) { DataRow dr = ds.Tables["categories"].NewRow(); dr["id"] = 1; dr["categories_xml"] = categories_xml; dr["stamp"] = timeStamp; ds.Tables["categories"].Rows.Add(dr); } else { ds.Tables["categories"].Rows[0]["categories_xml"] = categories_xml; ds.Tables["categories"].Rows[0]["stamp"] = timeStamp; } da.Update(ds.Tables["categories"]); } catch (Exception ex) { MainWindow.MainForm.GetLogger().Add("Ошибка записи категорий в БД: " + ex.Message); } } } } }
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 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 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; } }
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 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 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"; }
/// <summary> /// /// </summary> /// <returns></returns> public void QueryProducts() { // Create data adapter this.ProductsDataAdapter = new SQLiteDataAdapter(Properties.Resources.QueryProducts, _sqlConnection); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(this.ProductsDataAdapter); // Create data set this.ProductsDataSet = new DataSet("ProductSet"); this.ProductsDataAdapter.Fill(this.ProductsDataSet, "products"); // Get data table this.ProductsDataTable = this.ProductsDataSet.Tables["products"]; this.ProductsDataTable.PrimaryKey = new DataColumn[] { this.ProductsDataTable.Columns["id"] }; }
protected override DbDataAdapter GetDataAdapter(string tableName, string schemaName, out DbCommandBuilder builder) { var adapter = new SQLiteDataAdapter(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM \"{0}\"", tableName), _connection); builder = new SQLiteCommandBuilder(adapter); return adapter; }
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 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; }
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; } } }
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"); } }
/// <summary> /// Retrieves foreign key information from the specified set of filters /// </summary> /// <param name="strCatalog">An optional catalog to restrict results on</param> /// <param name="strTable">An optional table to restrict results on</param> /// <param name="strKeyName">An optional foreign key name to restrict results on</param> /// <returns>A DataTable with the results of the query</returns> private DataTable Schema_ForeignKeys(string strCatalog, string strTable, string strKeyName) { DataTable tbl = new DataTable("ForeignKeys"); DataRow row; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string)); tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string)); tbl.Columns.Add("CONSTRAINT_NAME", typeof(string)); tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string)); tbl.Columns.Add("IS_DEFERRABLE", typeof(bool)); tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool)); tbl.Columns.Add("FKEY_ID", typeof(int)); tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string)); tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int)); tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string)); tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string)); tbl.Columns.Add("FKEY_TO_TABLE", typeof(string)); tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string)); tbl.Columns.Add("FKEY_ON_UPDATE", typeof(string)); tbl.Columns.Add("FKEY_ON_DELETE", typeof(string)); tbl.Columns.Add("FKEY_MATCH", typeof(string)); if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main"; string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb; tbl.BeginLoadData(); using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this)) using (SQLiteDataReader rdTables = cmdTables.ExecuteReader()) { while (rdTables.Read()) { if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), StringComparison.OrdinalIgnoreCase) == 0) { try { using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder()) //using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this)) //using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly)) using (SQLiteCommand cmdKey = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this)) using (SQLiteDataReader rdKey = cmdKey.ExecuteReader()) { while (rdKey.Read()) { row = tbl.NewRow(); row["CONSTRAINT_CATALOG"] = strCatalog; row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}", rdTables[2], rdKey.GetInt32(0), rdKey.GetInt32(1)); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = builder.UnquoteIdentifier(rdTables.GetString(2)); row["CONSTRAINT_TYPE"] = "FOREIGN KEY"; row["IS_DEFERRABLE"] = false; row["INITIALLY_DEFERRED"] = false; row["FKEY_ID"] = rdKey[0]; row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString()); row["FKEY_TO_CATALOG"] = strCatalog; row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString()); row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString()); row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1]; row["FKEY_ON_UPDATE"] = (rdKey.FieldCount > 5) ? rdKey[5] : String.Empty; row["FKEY_ON_DELETE"] = (rdKey.FieldCount > 6) ? rdKey[6] : String.Empty; row["FKEY_MATCH"] = (rdKey.FieldCount > 7) ? rdKey[7] : String.Empty; if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), StringComparison.OrdinalIgnoreCase) == 0) tbl.Rows.Add(row); } } } catch (SQLiteException) { } } } } tbl.EndLoadData(); tbl.AcceptChanges(); return tbl; }
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"); } }
internal static void KeyInfoTest(DbConnection cnn) { using (DbCommand cmd = cnn.CreateCommand()) { // First test against integer primary key (optimized) keyinfo fetch cmd.CommandText = "Create table keyinfotest (id integer primary key, myuniquevalue integer unique not null, myvalue varchar(50))"; cmd.ExecuteNonQuery(); cmd.CommandText = "Select * from keyinfotest"; using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { using (DataTable tbl = reader.GetSchemaTable()) { if (tbl.Rows.Count != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned"); } } cmd.CommandText = "SELECT MyValue FROM keyinfotest"; using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { using (DataTable tbl = reader.GetSchemaTable()) { if (tbl.Rows.Count != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned"); } } cmd.CommandText = "DROP TABLE keyinfotest"; cmd.ExecuteNonQuery(); // Now test against non-integer primary key (unoptimized) subquery keyinfo fetch cmd.CommandText = "Create table keyinfotest (id char primary key, myuniquevalue integer unique not null, myvalue varchar(50))"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT MyValue FROM keyinfotest"; using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { using (DataTable tbl = reader.GetSchemaTable()) { if (tbl.Rows.Count != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned"); } } cmd.CommandText = "Select * from keyinfotest"; using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { using (DataTable tbl = reader.GetSchemaTable()) { if (tbl.Rows.Count != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned"); } } // Make sure commandbuilder can generate an update command with the correct parameter count using (DbDataAdapter adp = new SQLiteDataAdapter()) using (DbCommandBuilder builder = new SQLiteCommandBuilder()) { adp.SelectCommand = cmd; builder.DataAdapter = adp; builder.ConflictOption = ConflictOption.OverwriteChanges; using (DbCommand updatecmd = builder.GetUpdateCommand()) { if (updatecmd.Parameters.Count != 4) throw new ArgumentOutOfRangeException("Wrong number of parameters in update command!"); } } } }
// 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))); } } } } } } }
/// <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); } } }
private void sourceRefresh(String path) { // DataGridの内容消去 clsSource(); checkBox1.Checked = false; clsTarget(); clsRename(); // マウスカーソル変更 Cursor preCursor = this.Cursor; this.Cursor = Cursors.WaitCursor; // 項目無効 allButtons(false); totalCount = 0; // ファイル名の取得 using (var transaction = cn.BeginTransaction()) { using (SQLiteCommand cmd = cn.CreateCommand()) { cmd.CommandText = "PRAGMA journal_mode = MEMORY"; cmd.ExecuteNonQuery(); sm = SearchMode.None; recursiveFileList(path, ref totalCount); } transaction.Commit(); } adapter = new SQLiteDataAdapter("SELECT Id, checked, path, file, size, date FROM flist;", cn); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter); if (adapter != null) adapter.Fill(dtSource); // カーソルを元に戻す this.Cursor = preCursor; // 項目有効 allButtons(true); // ボタン表示チェック buttonEnable(); // 件数表示 checkedCounter(totalCount); // パネル1を表示 viewPanel(1); }
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 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 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"); } } }
/// <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; } }