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