public static void UpdateTable(string query, SQLiteConnection dbConnection, DataTable table)
{
//object maxId = null;
//if (query.ToLower().Contains("from traffic_object"))
// maxId = GetScalar("Select max(obj_id) From traffic_object", dbConnection);
using (SQLiteTransaction transaction = dbConnection.BeginTransaction())
{
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, dbConnection))
{
using (SQLiteCommand command = dbConnection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = query;
adapter.SelectCommand = command;
using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder())
{
builder.DataAdapter = adapter;
adapter.Update(table);
transaction.Commit();
}
}
}
}
}
private void ChangedDatabase()
{
m_adapter = new SQLiteDataAdapter("select * from serverlist", m_dbwrap.Database.Connection);
SQLiteCommandBuilder cb = new SQLiteCommandBuilder(m_adapter);
m_adapter.InsertCommand = cb.GetInsertCommand();
m_adapter.DeleteCommand = cb.GetDeleteCommand();
m_adapter.UpdateCommand = cb.GetUpdateCommand();
Reload();
}
private void button1_Click_1(object sender, EventArgs e)
{
if (comboBox1.SelectedIndex == 0)
{
string numero;
try
{
numero = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para borrar
cmd.CommandText = "DELETE FROM Entradas WHERE [Numero] = " + numero;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Entrada eliminada exitosamente");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "select * from Entradas";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";
//create the database query
string query = "SELECT * From Proveedor Where Nombreproveedor like '%" + textBox1.Text + "%'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
private void applyButton_Click(object sender, EventArgs e)
{
SQLiteConnection mCN = ConnectionManager.connection;
SQLiteDataAdapter mDA = modulesTableAdapter.Adapter;
SQLiteCommandBuilder mCB = new SQLiteCommandBuilder(mDA);
DataSet mDS = modulesDataSet;
DataSet dsChanges = new DataSet();
if (!mDS.HasChanges()) return;
dsChanges = mDS.GetChanges(DataRowState.Modified);
if (dsChanges != null)
{
mDA.UpdateCommand = mCB.GetUpdateCommand();
mDA.Update(dsChanges, "modules");
}
dsChanges = mDS.GetChanges(DataRowState.Added);
if (dsChanges != null)
{
mDA.InsertCommand = mCB.GetInsertCommand();
mDA.Update(dsChanges, "modules");
}
dsChanges = mDS.GetChanges(DataRowState.Deleted);
if (dsChanges != null)
{
mDA.DeleteCommand = mCB.GetDeleteCommand();
mDA.Update(dsChanges, "modules");
}
mDS.AcceptChanges();
UpdateModulesDropDown();
(treeView.Model as SlowTreeModel).Root.UpdateModulesFromDbRec();
treeView.Invalidate();
}
/// <summary>
/// Загрузка авторов из БД
/// </summary>
/// <returns></returns>
public AuthorList LoadAuthors()
{
if (ds != null)
{
ds.Dispose();
}
ds = new DataSet();
AuthorList list = new AuthorList();
using (SQLiteCommand mycommand = new SQLiteCommand(conn))
{
mycommand.CommandText = "select * from authors";
da = new SQLiteDataAdapter();
da.SelectCommand = mycommand;
System.Data.SQLite.SQLiteCommandBuilder cb = new System.Data.SQLite.SQLiteCommandBuilder(da);
da.Fill(ds, "authors");
foreach (DataRow row in ds.Tables["authors"].Rows)
{
var author = Xml2Author(row["author_xml"].ToString());
if (author != null)
{
author.Changed = false;
list.Add(author);
}
}
}
return(list);
}
private void textBox2_TextChanged(object sender, EventArgs e)
{
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Almacen Where ArticuloID = '" + textBox2.Text + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
label6.Text = "";
label5.Text = "";
//textBox3.Text = "0";
label10.Text = "";
label12.Text = "";
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
label6.Text = Row["Cantidadexistencia"].ToString();
label5.Text = Row["Precioventa"].ToString();
label12.Text = Row["Nombrearticulo"].ToString();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count != 0)
{
DialogResult resultado = MessageBox.Show("Esta seguro que desea eliminar toda la venta?", "Seguro?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (resultado == DialogResult.Yes)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "Delete From Ventas Where [NVenta] = " + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
cmd.CommandText = "Delete From Ventashechas Where [NVenta] = " + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";
dataGridView1.Left = 247;
//create the database query
string query = "Select * From Ventas";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
}
else
{
MessageBox.Show("Tiene que elegir una venta para eliminarlo");
}
}
private void button3_Click(object sender, EventArgs e)
{
if (comboBox1.SelectedIndex == 1)
{
string numero;
try
{
numero = dataGridView2.SelectedRows[0].Cells[0].Value.ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para borrar
cmd.CommandText = "DELETE FROM Salidas WHERE [Numero] = " + numero;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Salida eliminada exitosamente");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "select * from Salidas";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView2.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
fecha = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
nombre = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
edad = Int32.Parse(dataGridView1.SelectedRows[0].Cells[2].Value.ToString());
apoyo = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "DELETE FROM Donaciones WHERE Fecha = '" + fecha + "' AND Nombre = '" + nombre + "' AND Edad = '" + edad + "' AND Apoyo = '" + apoyo + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Donacion eliminada con exito");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;";
//create the database query
string query = "select * from Donaciones";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No hay donaciones que eliminar");
}
}
/// <summary>
/// Загрузить категории
/// </summary>
/// <returns></returns>
public CategoryList LoadCategories()
{
if (ds != null)
{
ds.Dispose();
}
ds = new DataSet();
CategoryList list = null;
using (SQLiteCommand mycommand = new SQLiteCommand(conn))
{
mycommand.CommandText = "select * from categories";
da = new SQLiteDataAdapter();
da.SelectCommand = mycommand;
System.Data.SQLite.SQLiteCommandBuilder cb = new System.Data.SQLite.SQLiteCommandBuilder(da);
da.Fill(ds, "categories");
foreach (DataRow row in ds.Tables["categories"].Rows)
{
string categories_xml = row["categories_xml"].ToString();
if (!string.IsNullOrEmpty(categories_xml))
{
try
{
var reader = new StringReader(categories_xml);
var sr = new XmlSerializer(typeof(CategoryList));
list = (CategoryList)sr.Deserialize(reader);
foreach (Category category in list)
{
category.SetOwner(list);
}
list.Reorder();
return(list);//(CategoryList)sr.Deserialize(reader);
}
catch
{ }
break;
}
}
}
if (list == null)
{
list = new CategoryList();
list.Add(new Category()
{
Name = "Default"
});
}
foreach (Category category in list)
{
category.SetOwner(list);
}
list.Reorder();
return(list);
}
public void SaveDatabase() {
DataSet changes = DataSet.GetChanges();
System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("Data Source=" + DB_Connection + ";Version=3;");
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("SELECT * FROM Members", con);
SQLiteCommandBuilder cb = new SQLiteCommandBuilder(dataAdapter);
dataAdapter.Update(changes);
DataSet.AcceptChanges();
con.Close();
GC.Collect();
}
private void button1_Click(object sender, EventArgs e)
{
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Usuarios";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
bool usuarioexistente = false;
bool admin = false;
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
if (Row["Usuario"].ToString() == textBox1.Text && Row["Contrasena"].ToString() == textBox2.Text)
{
usuarioexistente = true;
if (Row["TipoDeUsuario"].ToString() == "Administrador")
{
admin = true;
AdminAvailable(true);
this.Close();
}
break;
}
}
if (usuarioexistente == false)
{
MessageBox.Show("Usuario o contraseña incorrecta");
}
else if (usuarioexistente == true && admin == false)
{
MessageBox.Show("La cuenta utilizada no es de un administrador");
}
}
private void button1_Click(object sender, EventArgs e)
{
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Usuarios";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
bool usuarioexistente = false;
bool admin = false;
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
if (Row["Usuario"].ToString() == textBox1.Text && Row["Contrasena"].ToString() == textBox2.Text)
{
usuarioexistente = true;
if (Row["TipoDeUsuario"].ToString() == "Administrador")
{
admin = true;
AdminAvailable(true);
this.Close();
}
break;
}
}
if (usuarioexistente == false)
{
MessageBox.Show("Usuario o contraseña incorrecta");
}
else if (usuarioexistente == true && admin == false)
{
MessageBox.Show("La cuenta utilizada no es de un administrador");
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
if (textBox1.Text != "")
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBUC.s3db";
string query = "";
query = "SELECT Nombre,Usuario,TipoDeUsuario from Usuarios WHERE Usuario LIKE '%" + textBox1.Text + "%'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
else
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBUC.s3db";
string query = "";
query = "SELECT Nombre,Usuario,TipoDeUsuario from Usuarios";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count != 0)
{
DialogResult resultado = MessageBox.Show("Esta seguro que desea eliminar?", "Seguro?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (resultado == DialogResult.Yes)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "Delete From Proveedor Where [Nombreproveedor] = '" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";
//create the database query
string query = "Select * From Proveedor";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
}
else
{
MessageBox.Show("Tiene que elegir un articulo para borrarlo");
}
}
internal KeyQuery(SQLiteConnection cnn, string database, string table, params string[] columns)
{
using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder())
{
_command = cnn.CreateCommand();
for (int n = 0; n < columns.Length; n++)
{
columns[n] = builder.QuoteIdentifier(columns[n]);
}
}
_command.CommandText = UnsafeNativeMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT {0} FROM [{1}].[{2}] WHERE ROWID = ?", String.Join(",", columns), database, table);
_command.Parameters.AddWithValue(null, (long)0);
}
public static DataTable LogIn(DataTable dtIn)
{
string cmdstring = "select * from userdata";
SQLiteDataAdapter sa = new SQLiteDataAdapter(cmdstring, objConnection);
DataTable dtOut = new DataTable();
sa.Fill(dtOut);
dtOut.Rows.Add(dtIn.Rows[0].ItemArray);
SQLiteCommandBuilder objCommandBuilder = new SQLiteCommandBuilder(sa);
sa.Update(dtOut);
currentUser = dtIn.Rows[0];
objConnection.Close();
return dtIn;
}
private void NuevoExpedienteSillas_Load(object sender, EventArgs e)
{
float width_ratio = (Screen.PrimaryScreen.Bounds.Width / 800);
float heigh_ratio = (Screen.PrimaryScreen.Bounds.Height / 600f);
SizeF scale = new SizeF(width_ratio, heigh_ratio);
this.Scale(scale);
//And for font size
foreach (Control control in this.Controls)
{
control.Font = new Font("Microsoft Sans Serif", Font.SizeInPoints * heigh_ratio * width_ratio);
}
comboBox1.SelectedIndex = 0;
comboBox2.SelectedIndex = 0;
comboBox3.SelectedIndex = 0;
comboBox4.SelectedIndex = 0;
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBESIL.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM SRTamanoTipo";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["IDFormatoSillas"].ToString();
int autonum = Int32.Parse(num);
label28.Text = (autonum + 1).ToString();
}
else
{
label28.Text = "1";
}
}
/// <summary>
/// Сохранение автора в БД
/// </summary>
/// <param name="author"></param>
public void SaveAuthor(Author author)
{
lock (_locker)
{
if (string.IsNullOrEmpty(author.Id))
{
//author.Id = Guid.NewGuid().ToString();
author.CheckID();
}
//author.timeStamp = DateTime.Now.ToUniversalTime();
string author_xml = Author2Xml(author);
if (!string.IsNullOrEmpty(author_xml))
{
// проверим, есть ли такой автор в БД
using (SQLiteCommand cmd = new SQLiteCommand("", conn))
{
string sql = "";
sql = string.Format("select * from authors where id='{0}'", author.Id);
cmd.CommandText = sql;
int cnt = cmd.ExecuteNonQuery();
//long timeStamp = author.timeStamp.ToBinary();
if (ds != null)
{
ds.Dispose();
}
ds = new DataSet();
da = new SQLiteDataAdapter();
da.SelectCommand = cmd;
var cb = new System.Data.SQLite.SQLiteCommandBuilder(da);
da.Fill(ds, "author");
if (ds.Tables["author"].Rows.Count == 0)
{
DataRow dr = ds.Tables["author"].NewRow();
dr["id"] = author.Id;
ds.Tables["author"].Rows.Add(dr);
}
ds.Tables["author"].Rows[0]["author_xml"] = author_xml;
//ds.Tables["author"].Rows[0]["stamp"] = timeStamp;
da.Update(ds.Tables["author"]);
//if (cnt > 0)
// sql = string.Format("update authors set author_xml='{0}', stamp={1} where id='{1}'", author_xml, timeStamp, author.Id);
//else
// sql = string.Format("insert into authors (id, author_xml, stamp) ('{0}','{1}',{2})", author.Id, author_xml, timeStamp);
//cmd.CommandText = sql;
//cmd.ExecuteNonQuery();
}
}
}
}
public void UpdateDatatableQuery(DataTable dt, String query)
{
DataTable sdt = new DataTable();
using (SQLiteCommand command = Conn.CreateCommand())
{
command.CommandText = query;
SQLiteDataAdapter adp = new SQLiteDataAdapter(command);
adp.Fill(sdt);
SQLiteCommandBuilder cb = new SQLiteCommandBuilder(adp);
//SQLiteCommand update = cb.GetUpdateCommand();
//adp.UpdateCommand = update;
adp.Update(dt);
}
}
private void Form1_Load(object sender, EventArgs e)
{
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Ventas";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["NVenta"].ToString();
int autonum = Int32.Parse(num);
textBox1.Text = (autonum + 1).ToString();
}
else
{
textBox1.Text = "1";
}
try
{
Sistema_Caritas.ConvertidorMonetario.CurrencyConvertor CC = new Sistema_Caritas.ConvertidorMonetario.CurrencyConvertor();
label21.Text = CC.ConversionRate(Sistema_Caritas.ConvertidorMonetario.Currency.USD, Sistema_Caritas.ConvertidorMonetario.Currency.MXN).ToString();
}
catch
{
comboBox1.Visible = false;
label20.Visible = false;
label21.Visible = false;
button5.Visible = false;
}
timer1.Enabled = true;
}
protected override void ProcessRecord()
{
base.BeginProcessing();
if (ShouldProcess("Database", "BeginTransaction"))
{
if (_Transaction == null)
{
_command = connection.CreateCommand();
_command.Transaction = connection.BeginTransaction();
}
else
{
_command = _Transaction.Connection.CreateCommand();
_command.Transaction = _Transaction;
}
}
_command.CommandText = "SELECT * FROM '" + name + "' LIMIT 1";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(_command);
SQLiteCommandBuilder commandbuilder = new SQLiteCommandBuilder(adapter);
adapter.InsertCommand = (SQLiteCommand)commandbuilder.GetInsertCommand().Clone();
commandbuilder.DataAdapter = null;
if (ShouldProcess("Database", "Update"))
{
foreach (DataRow row in inputobject.Rows)
{
if (row.RowState == DataRowState.Unchanged)
{
row.SetAdded();
}
}
adapter.Update(inputobject);
}
if (ShouldProcess("Transaction", "Commit"))
{
if (_command.Transaction != null)
{
if (_Transaction == null)
{
_command.Transaction.Commit();
}
}
}
inputobject.AcceptChanges();
}
private void dataGridView1_Click(object sender, EventArgs e)
{
try
{
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";
string nombre = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string usuario = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
string tipodeusuario = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
//create the database query
string query = "SELECT * FROM Usuarios WHERE Nombre = '" + nombre + "' AND Usuario = '" + usuario + "' AND TipoDeUsuario = '" + tipodeusuario + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
DataRow Row = dTable.Rows[0];
string contrasena = Row["Contrasena"].ToString();
textBox5.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
textBox2.Text = usuario;
textBox3.Text = contrasena;
textBox4.Text = contrasena;
comboBox1.SelectedIndex = comboBox1.FindStringExact(tipodeusuario);
}
catch
{
textBox5.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
comboBox1.SelectedIndex = 1;
}
}
private void NuevaEncuesta_Load(object sender, EventArgs e)
{
appPath = Path.GetDirectoryName(Application.ExecutablePath);
sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;");
cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
////
appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
connString = @"Data Source= " + appPath2 + @"\DBESIL.s3db ;Version=3;";
//create the database query
query = "SELECT * FROM DatosGenerales";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["Noencuesta"].ToString();
int autonum = Int32.Parse(num);
label2.Text = (autonum + 1).ToString();
}
else
{
label2.Text = "1";
}
}
/// <summary>
/// 修改数据表记录
/// </summary>
/// <returns></returns>
public static bool UpdateTable(DataTable srcTable, string tableName) {
bool isok = false;
try {
SQLiteCommand command = new SQLiteCommand();
using (SQLiteConnection conn = GetSQLiteConnection()) {
if (conn.State != ConnectionState.Open) conn.Open();
command.Connection = conn;
command.CommandText = "SELECT * FROM " + tableName;
SQLiteDataAdapter SQLiteDA = new SQLiteDataAdapter(command);
SQLiteCommandBuilder SQLiteCB = new SQLiteCommandBuilder(SQLiteDA);
SQLiteDA.InsertCommand = SQLiteCB.GetInsertCommand();
SQLiteDA.DeleteCommand = SQLiteCB.GetDeleteCommand();
SQLiteDA.UpdateCommand = SQLiteCB.GetUpdateCommand();
SQLiteDA.Update(srcTable);
}
isok = true;
} catch { ;}
return isok;
}
private void button5_Click(object sender, EventArgs e)
{
dataGridView1.Left = 247;
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";
//create the database query
string query = "SELECT * From Ventas";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
int i = 0;
foreach (DataGridViewColumn c in dataGridView1.Columns)
{
i += c.Width;
}
dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2;
button5.Visible = false;
}
private void textBox3_TextChanged(object sender, EventArgs e)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";
string query = "";
if (textBox3.Text != "")
{
if (comboBox2.SelectedIndex == 0)
{
//create the database query
query = "SELECT ArticuloID, Nombrearticulo From Almacen Where ArticuloID like '%" + textBox3.Text + "%'";
}
else if (comboBox2.SelectedIndex == 1)
{
query = "SELECT ArticuloID, Nombrearticulo From Almacen Where Nombrearticulo like '%" + textBox3.Text + "%'";
}
}
else
{
query = "SELECT ArticuloID, Nombrearticulo From Almacen";
}
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView2.DataSource = bSource;
dAdapter.Update(dTable);
}
private void button4_Click(object sender, EventArgs e)
{
bool matched = false;
Int32 err = 0;
err = m_FPM.MatchTemplate(m_RegMin1, m_RegMin2, m_SecurityLevel, ref matched);
if ((err == (Int32)SGFPMError.ERROR_NONE))
{
if (matched)
{
// Save template after merging two template - m_FetBuf1, m_FetBuf2
Byte[] merged_template;
Int32 buf_size = 0;
m_FPM.GetTemplateSizeAfterMerge(m_RegMin1, m_RegMin2, ref buf_size);
merged_template = new Byte[buf_size];
m_FPM.MergeAnsiTemplate(m_RegMin1, m_RegMin2, merged_template);
if (m_StoredTemplate == null)
{
m_StoredTemplate = new Byte[buf_size];
merged_template.CopyTo(m_StoredTemplate, 0);
}
else
{
Int32 new_size = 0;
err = m_FPM.GetTemplateSizeAfterMerge(m_StoredTemplate, merged_template, ref new_size);
Byte[] new_enroll_template = new Byte[new_size];
err = m_FPM.MergeAnsiTemplate(merged_template, m_StoredTemplate, new_enroll_template);
m_StoredTemplate = new Byte[new_size];
new_enroll_template.CopyTo(m_StoredTemplate, 0);
}
StatusBar.Text = "Template registration success";
////////////////////////////////////
PictureBox picturebox3 = new PictureBox();
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Usuarios where Usuario = '" + textBox1.Text + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count >= 1)
{
DataRow Row = dTable.Rows[0];
if (Row["Huella"] != null)
{
System.Byte[] fp_image = (System.Byte[])Row["Huella"];
///////////////////////////////////////////////////
Int32 error = (Int32)SGFPMError.ERROR_NONE;
Int32 img_qlty = 0;
m_FPM.GetImageQuality(m_ImageWidth, m_ImageHeight, fp_image, ref img_qlty);
if (error == (Int32)SGFPMError.ERROR_NONE)
{
DrawImage(fp_image, picturebox3);
SGFPMFingerInfo finger_info = new SGFPMFingerInfo();
finger_info.FingerNumber = (SGFPMFingerPosition)1;
finger_info.ImageQuality = (Int16)img_qlty;
finger_info.ImpressionType = (Int16)SGFPMImpressionType.IMPTYPE_LP;
finger_info.ViewNumber = 1;
// Create template
error = m_FPM.CreateTemplate(finger_info, fp_image, m_VrfMin);
if (error == (Int32)SGFPMError.ERROR_NONE)
{
StatusBar.Text = "Verification image is captured";
if (m_StoredTemplate == null)
{
StatusBar.Text = "No data to verify";
return;
}
string[] fingerpos_str = new string[]
{
"Unknown finger",
"Right thumb",
"Right index finger",
"Right middle finger",
"Right ring finger",
"Right little finger",
"Left thumb",
"Left index finger",
"Left middle finger",
"Left ring finger",
"Left little finger"
};
SGFPMFingerPosition finger_pos = SGFPMFingerPosition.FINGPOS_UK;
bool finger_found = false;
SGFPMANSITemplateInfo sample_info = new SGFPMANSITemplateInfo();
err = m_FPM.GetAnsiTemplateInfo(m_StoredTemplate, sample_info);
for (int i = 0; i < sample_info.TotalSamples; i++)
{
matched = false;
err = m_FPM.MatchAnsiTemplate(m_StoredTemplate, i, m_VrfMin, 0, m_SecurityLevel, ref matched);
if (matched)
{
finger_found = true;
finger_pos = (SGFPMFingerPosition)sample_info.SampleInfo[i].FingerNumber;
break;
}
}
if (err == (Int32)SGFPMError.ERROR_NONE)
{
if (finger_found)
{
StatusBar.Text = "The matched data found. Finger position: " + fingerpos_str[(Int32)finger_pos];
textBox2.Text = Row["Contrasena"].ToString();
button1.Enabled = true;
button1.PerformClick();
}
else
{
StatusBar.Text = "Cannot find a matched data";
}
}
else
{
StatusBar.Text = "MatchAnsiTemplate() Error : " + err;
}
}
else
{
StatusBar.Text = "GetMinutiae() Error : " + error;
}
}
else
{
StatusBar.Text = "GetImage() Error : " + error;
}
}
else
{
MessageBox.Show("No hay huella con la cual comparar");
}
////////////////////////////////////////////////
}
else
{
MessageBox.Show("El usuario no existe U olvido colocarlo");
}
}
else
{
StatusBar.Text = "Template registration failed";
}
}
else
{
StatusBar.Text = "MatchTemplate() Error: " + err;
}
}
private void 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_1(object sender, EventArgs e)
{
bool foliop = textBox12.Text.All(Char.IsNumber);
bool folionon = false;
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Data Source=" + appPath2 + @"\EXCL.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Expediente";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
if (textBox12.Text == Row["Folio"].ToString())
{
folionon = true;
break;
}
}
bool edadp = textBox2.Text.All(Char.IsNumber);
float pesov;
bool pesop = float.TryParse(textBox6.Text, out pesov);
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && textBox6.Text != "" && textBox7.Text != "" && textBox8.Text != "" && textBox9.Text != "" && textBox10.Text != "" && textBox11.Text != "" && textBox12.Text != "" && textBox13.Text != "" && textBox14.Text != "" && textBox15.Text != "" && textBox16.Text != "" && textBox17.Text != "" && textBox18.Text != "" && textBox19.Text != "" && textBox20.Text != "" && textBox21.Text != "" && textBox22.Text != "")
{
if (edadp == true)
{
if (pesop == true)
{
if (folionon == false)
{
if (foliop == true)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "INSERT INTO Expediente (Folio, Nombre, Sexo, Edad, Ocupacion, Estadocivil, Religion, TA, Peso, Tema,FC, FR, EnfermedadesFamiliares, AreaAfectada, Antecedentes, Habitos,GPAC,FUMFUP,Motivo, CuadroClinico, ID, EstudiosSolicitados, TX, PX, Doctor, CP, SSA) VALUES ('" + textBox12.Text + "','" + textBox1.Text + "', '" + comboBox1.Text + "', '" + textBox2.Text + "', '" + textBox4.Text + "', '" + comboBox2.Text + "', '" + textBox3.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + textBox8.Text + "', '" + textBox9.Text + "', '" + textBox10.Text + "', '" + comboBox3.Text + "', '" + textBox11.Text + "', '" + textBox13.Text + "', '" + comboBox4.Text + "', '" + comboBox5.Text + "', '" + textBox14.Text + "', '" + textBox15.Text + "', '" + textBox16.Text + "', '" + textBox17.Text + "', '" + textBox18.Text + "', '" + textBox19.Text + "', '" + textBox20.Text + "', '" + textBox21.Text + "', '" + textBox22.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
this.Close();
}
else
{
MessageBox.Show("Solo introduzca numeros en el folio", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Ya existe un expediente con el mismo folio", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Solo introduzca numeros en el peso", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Solo introduzca numeros en la edad", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Ha dejado campos en blanco", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// JVUmaOutput処理
/// </summary>
public static void JVUmaOutput()
{
DataTable umaDataTable = new DataTable();
string delDate = DateTime.Today.AddMonths(-6).ToString("yyyyMMdd");
// 出力データ取得
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.CommandText = "SELECT * FROM uma WHERE DelKubun='0' OR (DelKubun='1' AND DelDate>='" + delDate + "')";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
{
da.Fill(umaDataTable);
}
}
// 年替わり更新
if (JVRelayClass.IsNextYear == true)
{
foreach (DataRow umaDataRow in umaDataTable.Rows)
{
if (umaDataRow["UmaClass"].ToString() != GetUmaClass(umaDataRow))
{
umaDataRow["UmaClass"] = GetUmaClass(umaDataRow);
}
}
// データ更新
using (SQLiteTransaction tran = DbConn.BeginTransaction())
{
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.Transaction = tran;
command.CommandText = "SELECT * FROM uma";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da))
{
cb.SetAllValues = false;
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.Update(umaDataTable);
}
}
tran.Commit();
}
}
// 出力
foreach (DataRow dr in umaDataTable.Rows)
{
OutputUmaData(eOutput.Umanushi, dr);
}
}
static void Main(string[] args)
{
string dbLocation = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "tilecache.s3db");
int minz = 7;
int maxz = 10;
double minx = -95.844727;
double miny = 35.978006;
double maxx = -88.989258;
double maxy = 40.563895;
string mapServiceUrl = "http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Census_USA/MapServer";
int maxDegreeOfParallelism = 10;
bool replaceExistingCacheDB = true;
bool showHelp = false;
bool verbose = false;
string mapServiceType = "agsd";
string settings = string.Empty;
bool showInfo = false;
ITileUrlSource tileSource = new OSMTileUrlSource()
{
MapServiceUrl = TileHelper.OSM_BASE_URL_TEMPLATE,
};
var options = new OptionSet()
{
{"h|?|help", "Show this message and exits", h => showHelp = h != null},
{"v|verbose", "Display verbose information logs while running", v => verbose = v != null},
{"t|type=", "Type of the map service to be cached", t => mapServiceType = t.ToLower()},
{"m|mapservice=", "Url of the Map Service to be cached", m => mapServiceUrl = m},
{"s|settings=", "Extra settings needed by the type of map service being used", s => settings = s},
{"o|output=", "Complete file path and file name where the tile cache needs to be outputted", o => dbLocation = o},
{"z|minz=", "Minimum zoom scale at which to begin caching", z => int.TryParse(z, out minz)},
{"Z|maxz=", "Maximum zoom scale at which to end caching", Z => int.TryParse(Z, out maxz)},
{"x|minx=", "Minimum X coordinate value of the extent to cache", x => double.TryParse(x, out minx)},
{"y|miny=", "Minimum Y coordinate value of the extent to cache", y => double.TryParse(y, out miny)},
{"X|maxx=", "Maximum X coordinate value of the extent to cache", X => double.TryParse(X, out maxx)},
{"Y|maxy=", "Maximum Y coordinate value of the extent to cache", Y => double.TryParse(Y, out maxy)},
{"p|parallelops=", "Limits the number of concurrent operations run by TileCutter", p => int.TryParse(p, out maxDegreeOfParallelism)},
{"r|replace=", "Delete existing tile cache MBTiles database if already present and create a new one.", r => Boolean.TryParse(r, out replaceExistingCacheDB)},
{"i|info=", "", i => showInfo = i != null}
};
options.Parse(args);
if (showHelp)
{
ShowHelp(options);
return;
}
var tiles = GetTiles(minz, maxz, minx, miny, maxx, maxy);
if (showInfo)
{
var zoomLevels = Enumerable.Range(0, 21);
var tileCountByZoomLevel = new Dictionary<int, int>();
foreach (var level in zoomLevels)
tileCountByZoomLevel[level] = 0;
foreach (var tile in tiles)
tileCountByZoomLevel[tile.Level]++;
foreach (var item in tileCountByZoomLevel)
if (item.Value > 0)
Console.WriteLine(string.Format("Zoom level {0} - {1} tiles", item.Key, item.Value));
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
return;
}
if (!string.IsNullOrEmpty(mapServiceType))
tileSource = GetTileSource(mapServiceType, mapServiceUrl, settings);
string tileCacheDirectory = Path.GetDirectoryName(dbLocation);
string tilecacheFileName = Path.GetFileNameWithoutExtension(dbLocation);
if (!Directory.Exists(tileCacheDirectory))
{
Console.WriteLine("The tilecache path provided is not valid");
return;
}
string errorLogFile = Path.Combine(tileCacheDirectory, tilecacheFileName + ".log");
//if the user option to delete existing tile cache db is true delete it or else add to it
if (replaceExistingCacheDB && File.Exists(dbLocation))
File.Delete(dbLocation);
//Connect to the sqlite database
string connectionString = string.Format("Data Source={0}; FailIfMissing=False", dbLocation);
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
//Check if the 'metadata' table exists, if not create it
var rows = connection.GetSchema("Tables").Select("Table_Name = 'metadata'");
if (!rows.Any())
{
var command = connection.CreateCommand();
command.CommandText = "CREATE TABLE metadata (name text, value text);";
command.ExecuteNonQuery();
connection.Execute("CREATE UNIQUE INDEX name ON metadata ('name')");
connection.Execute(@"INSERT INTO metadata(name, value) values (@a, @b)",
new[] {
new { a = "name", b = "cache" },
new { a = "type", b = "overlay" },
new { a = "version", b = "1" },
new { a = "description", b = "some info here" },
new { a = "format", b = "png" },
new { a = "bounds", b = string.Format("{0},{1},{2},{3}", minx, miny, maxx, maxy)}
});
}
//Check if the 'images' table exists, if not create it
rows = connection.GetSchema("Tables").Select("Table_Name = 'images'");
if (!rows.Any())
{
var command = connection.CreateCommand();
command.CommandText = "CREATE TABLE [images] ([tile_id] INTEGER NOT NULL PRIMARY KEY, [tile_md5hash] VARCHAR(256) NOT NULL, [tile_data] BLOB NULL);";
command.ExecuteNonQuery();
command = connection.CreateCommand();
command.CommandText = "CREATE UNIQUE INDEX images_hash on images (tile_md5hash)";
command.ExecuteNonQuery();
}
//Check if the 'map' table exists, if not create it
rows = connection.GetSchema("Tables").Select("Table_Name = 'map'");
if (!rows.Any())
{
var command = connection.CreateCommand();
command.CommandText = "CREATE TABLE [map] ([map_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [tile_id] INTEGER NOT NULL, [zoom_level] INTEGER NOT NULL, [tile_row] INTEGER NOT NULL, [tile_column] INTEGER NOT NULL);";
command.ExecuteNonQuery();
}
}
FileStream errorLog = File.Create(errorLogFile);
StreamWriter errorWriter = new StreamWriter(errorLog);
Console.WriteLine("Output Cache file is: " + dbLocation);
BlockingCollection<TileImage> images = new BlockingCollection<TileImage>();
Task.Factory.StartNew(() =>
{
ParallelOptions parallelOptions = new ParallelOptions() { MaxDegreeOfParallelism = maxDegreeOfParallelism };
Parallel.ForEach(tiles, parallelOptions, (tile) =>
{
string tileUrl = tileSource.GetTileUrl(tile);
WebClient client = new WebClient();
try
{
byte[] image = client.DownloadData(tileUrl);
images.Add(new TileImage()
{
Tile = tile,
Image = image
});
if (verbose)
Console.WriteLine(string.Format("Tile Level:{0}, Row:{1}, Column:{2} downloaded.", tile.Level, tile.Row, tile.Column));
}
catch (WebException ex)
{
errorWriter.WriteLine(String.Format("{0},{1},{2} - {3}", tile.Level, tile.Column, tile.Row, ex.Message));
errorLog.Flush();
Console.WriteLine(string.Format("Error while downloading tile Level:{0}, Row:{1}, Column:{2} - {3}.", tile.Level, tile.Row, tile.Column, ex.Message));
return;
}
finally { client.Dispose(); }
});
}).ContinueWith(t =>
{
images.CompleteAdding();
if (verbose)
Console.WriteLine("All downloads complete.");
errorLog.Flush();
errorLog.Dispose();
});
int currentTileId = 1;
Action<TileImage[]> processBatch = (batch) =>
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand mapCommand = connection.CreateCommand(),
imagesCommand = connection.CreateCommand(), tileCountCommand = connection.CreateCommand(),
mapDeleteCommand = connection.CreateCommand(),
imageDeleteCommand = connection.CreateCommand())
{
tileCountCommand.CommandText = "select ifnull(max(tile_id), 1) from images";
var tileCountObj = tileCountCommand.ExecuteScalar();
if (tileCountObj != null)
{
int.TryParse(tileCountObj.ToString(), out currentTileId);
currentTileId++;
}
//Create a dummy query for the [map] table and fill the adapter with it
//the purpose of this is to get the table structure in a DataTable
//the adapter also builds the insert command for it in when it is populated
mapCommand.CommandText = "SELECT * FROM [map] WHERE 1 = 2";
SQLiteDataAdapter mapAdapter = new SQLiteDataAdapter(mapCommand);
//Create a dummy query for the [images] table and fill the adapter with it
//the purpose of this is to get the table structure in a DataTable
//the adapter also builds the insert command for it in when it is populated
imagesCommand.CommandText = "SELECT * FROM [images] WHERE 1 = 2";
SQLiteDataAdapter imagesAdapter = new SQLiteDataAdapter(imagesCommand);
using (SQLiteCommandBuilder mapCmdBuilder = new SQLiteCommandBuilder(mapAdapter),
imagesCmdBuilder = new SQLiteCommandBuilder(imagesAdapter))
{
using (imagesAdapter.InsertCommand = (SQLiteCommand)((ICloneable)imagesCmdBuilder.GetInsertCommand()).Clone())
using (mapAdapter.InsertCommand = (SQLiteCommand)((ICloneable)mapCmdBuilder.GetInsertCommand()).Clone())
{
imagesCmdBuilder.DataAdapter = null;
mapCmdBuilder.DataAdapter = null;
using (DataTable mapTable = new DataTable(),
imagesTable = new DataTable())
{
if (batch.Any())
{
var conditions = batch.Select(b => string.Format("([zoom_level]={0} and [tile_row]={1} and [tile_column]={2})", b.Tile.Level, b.Tile.Row, b.Tile.Column));
var whereClause = string.Join("or", conditions);
var mapDeleteStatement = string.Format("delete from map where {0}", whereClause);
var imageDeleteStatement = string.Format("delete from images where [tile_id] in (select [tile_id] from map where {0})", whereClause);
imageDeleteCommand.CommandText = imageDeleteStatement;
imageDeleteCommand.ExecuteNonQuery();
mapDeleteCommand.CommandText = mapDeleteStatement;
mapDeleteCommand.ExecuteNonQuery();
}
imagesAdapter.Fill(imagesTable);
mapAdapter.Fill(mapTable);
//Dictionary to eliminate duplicate images within batch
Dictionary<string, int> added = new Dictionary<string, int>();
//looping thru keys is safe to do here because
//the Keys property of concurrentDictionary provides a snapshot of the keys
//while enumerating
//the TryGet & TryRemove inside the loop checks for items that were removed by another thread
List<int> tileIdsInCurrentBatch = new List<int>();
foreach (var tileimg in batch)
{
string hash = Convert.ToBase64String(new System.Security.Cryptography.MD5CryptoServiceProvider().ComputeHash(tileimg.Image));
int tileid = -1;
if (!added.ContainsKey(hash))
{
mapCommand.CommandText = "SELECT [tile_id] FROM [images] WHERE [tile_md5hash] = @hash";
mapCommand.Parameters.Add(new SQLiteParameter("hash", hash));
object tileObj = mapCommand.ExecuteScalar();
if (tileObj != null && int.TryParse(tileObj.ToString(), out tileid))
added.Add(hash, tileid);
else
{
tileid = currentTileId++;
added.Add(hash, tileid);
DataRow idr = imagesTable.NewRow();
idr["tile_md5hash"] = hash;
idr["tile_data"] = tileimg.Image;
idr["tile_id"] = added[hash];
imagesTable.Rows.Add(idr);
}
}
tileIdsInCurrentBatch.Add(added[hash]);
DataRow mdr = mapTable.NewRow();
mdr["zoom_level"] = tileimg.Tile.Level;
mdr["tile_column"] = tileimg.Tile.Column;
mdr["tile_row"] = tileimg.Tile.Row;
mdr["tile_id"] = added[hash];
mapTable.Rows.Add(mdr);
}//for loop thru images
tileIdsInCurrentBatch.Clear();
imagesAdapter.Update(imagesTable);
mapAdapter.Update(mapTable);
transaction.Commit();
if (verbose)
Console.WriteLine(String.Format("Saving an image batch of {0}.", batch.Length));
}//using for datatable
}//using for insert command
}//using for command builder
}//using for select command
}
}//using for connection
};
List<TileImage> tilebatch = new List<TileImage>();
foreach (var tileImage in images.GetConsumingEnumerable())
{
tilebatch.Add(tileImage);
if (tilebatch.Count < 50)
continue;
processBatch(tilebatch.ToArray());
tilebatch.Clear();
}
if (verbose)
Console.WriteLine("Saving remaining images that didn't fit into a batch.");
processBatch(tilebatch.ToArray());
tilebatch.Clear();
if (verbose)
Console.WriteLine("Creating Index on table [map] and Creating View [tiles].");
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
connection.Execute("CREATE UNIQUE INDEX IF NOT EXISTS map_index on map (zoom_level, tile_column, tile_row)");
connection.Execute("CREATE VIEW IF NOT EXISTS tiles as SELECT map.zoom_level as zoom_level, map.tile_column as tile_column, map.tile_row as tile_row, images.tile_data as tile_data FROM map JOIN images on images.tile_id = map.tile_id");
connection.Close();
}
Console.WriteLine("All Done !!!");
}
private void VerMoficarBitacora_Load(object sender, EventArgs e)
{
if (entradasalida == "Entradas")
{
panel1.BringToFront();
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Entradas where Numero = " + numero;
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[0];
string num = Row["Numero"].ToString();
int autonum = Int32.Parse(num);
label1.Text = (autonum).ToString();
textBox1.Text = Row["Entrantes"].ToString();
textBox2.Text = Row["Descripcion"].ToString();
textBox3.Text = Row["Cantidad"].ToString();
textBox4.Text = Row["Peso"].ToString();
textBox5.Text = Row["Equivalentes"].ToString();
textBox6.Text = Row["Notas"].ToString();
textBox7.Text = Row["Procedencia"].ToString();
comboBox1.SelectedIndex = comboBox1.FindStringExact(Row["Utilidad"].ToString());
textBox8.Text = Row["Responsable"].ToString();
}
else
{
}
}
else
{
panel2.BringToFront();
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Salidas where Numero = " + numero;
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[0];
string num = Row["Numero"].ToString();
int autonum = Int32.Parse(num);
label12.Text = (autonum).ToString();
textBox9.Text = Row["Salida"].ToString();
textBox11.Text = Row["Descripcion"].ToString();
textBox12.Text = Row["Cantidad"].ToString();
textBox14.Text = Row["Peso"].ToString();
textBox16.Text = Row["Equivalentes"].ToString();
textBox15.Text = Row["Nota"].ToString();
textBox13.Text = Row["TipoDeApoyo"].ToString();
textBox10.Text = Row["Responsable"].ToString();
}
else
{
}
}
}
private void NuevaEntradaSalidaBitacora_Load(object sender, EventArgs e)
{
comboBox1.SelectedIndex = 0;
comboBox2.SelectedIndex = 0;
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Entradas";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["Numero"].ToString();
int autonum = Int32.Parse(num);
label1.Text = (autonum + 1).ToString();
}
else
{
label1.Text = "1";
}
//create the database query
query = "SELECT * FROM Salidas";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["Numero"].ToString();
int autonum = Int32.Parse(num);
label12.Text = (autonum + 1).ToString();
}
else
{
label12.Text = "1";
}
m_ImageWidth = 260;
m_ImageHeight = 300;
m_Dpi = 500;
m_FPM = new SGFingerPrintManager();
Int32 error;
SGFPMDeviceName device_name = SGFPMDeviceName.DEV_UNKNOWN;
Int32 device_id = (Int32)SGFPMPortAddr.USB_AUTO_DETECT;
device_name = SGFPMDeviceName.DEV_AUTO;
m_DeviceOpened = false;
if (device_name != SGFPMDeviceName.DEV_UNKNOWN)
{
error = m_FPM.Init(device_name);
}
else
{
error = m_FPM.InitEx(m_ImageWidth, m_ImageHeight, m_Dpi);
}
if (error == (Int32)SGFPMError.ERROR_NONE)
{
SGFPMDeviceInfoParam pInfo = new SGFPMDeviceInfoParam();
m_FPM.GetDeviceInfo(pInfo);
m_ImageWidth = pInfo.ImageWidth;
m_ImageHeight = pInfo.ImageHeight;
StatusBar.Text = "Initialization Success";
pictureBox1.Visible = true;
progressBar1.Visible = true;
button3.Visible = true;
button4.Visible = false;
}
else
{
StatusBar.Text = "Init() Error " + error;
return;
}
// Set template format to ANSI 378
error = m_FPM.SetTemplateFormat(SGFPMTemplateFormat.ANSI378);
// Get Max template size
Int32 max_template_size = 0;
error = m_FPM.GetMaxTemplateSize(ref max_template_size);
m_RegMin1 = new Byte[max_template_size];
m_RegMin2 = new Byte[max_template_size];
m_VrfMin = new Byte[max_template_size];
// OpenDevice if device is selected
if (device_name != SGFPMDeviceName.DEV_UNKNOWN)
{
error = m_FPM.OpenDevice(device_id);
if (error == (Int32)SGFPMError.ERROR_NONE)
{
m_DeviceOpened = true;
}
else
{
StatusBar.Text = "OpenDevice() Error : " + error;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox2.SelectedIndex == 0)
{
int cantidad;
if (int.TryParse(textBox3.Text, out cantidad))
{
float peso;
if (float.TryParse(textBox4.Text, out peso))
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "INSERT INTO Entradas values ('" + label1.Text + "', '" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + comboBox1.Text + "', '" + textBox8.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Entrada guardada con exito.");
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox7.Text = "";
comboBox1.SelectedIndex = 0;
textBox8.Text = "";
///create the connection string
string connString = @"Data Source= " + appPath + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Entradas";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["Numero"].ToString();
int autonum = Int32.Parse(num);
label1.Text = (autonum + 1).ToString();
}
else
{
label1.Text = "1";
}
}
else
{
MessageBox.Show("Revise de nuevo su campo de peso, solo se permiten numeros flotantes.");
}
}
else
{
MessageBox.Show("Revise de nuevo su campo de cantidad, solo se permiten numeros.");
}
}
}
/// <summary>
/// Сохранить категории
/// </summary>
public void SaveCategories(CategoryList categories)
{
long timeStamp = DateTime.Now.ToUniversalTime().ToBinary();
var xs = new XmlSerializer(typeof(CategoryList));
var sb = new StringBuilder();
var w = new StringWriter(sb, CultureInfo.InvariantCulture);
xs.Serialize(w, categories,
new XmlSerializerNamespaces(new[] { new XmlQualifiedName(string.Empty) }));
string categories_xml = sb.ToString();
if (!string.IsNullOrEmpty(categories_xml))
{
lock (_locker)
{
using (SQLiteCommand cmd = new SQLiteCommand("", conn))
{
try
{
if (ds != null)
{
ds.Dispose();
}
ds = new DataSet();
da = new SQLiteDataAdapter();
string sql = string.Format("select * from categories");
cmd.CommandText = sql;
da.SelectCommand = cmd;
var cb = new System.Data.SQLite.SQLiteCommandBuilder(da);
da.Fill(ds, "categories");
// исправляем свой косяк, надо было изначально ID сделать, иначе SQLite без праймари ки ругается на датасет
// это чтобы не перегенерировать БД
if (!ds.Tables["categories"].Columns.Contains("id"))
{
// убьем таблицу категорий
cmd.CommandText = "DROP TABLE categories";
cmd.ExecuteNonQuery();
// создаем таблицу категорий. Данных там мало и чтобы не мудохаться все храним в одной записи-строке
sql = "create table categories(" +
"id varchar PRIMARY KEY ,categories_xml text, stamp bigint)";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
// формируем датасет и дальше уже пишем данные
ds = new DataSet();
da = new SQLiteDataAdapter();
sql = string.Format("select * from categories");
cmd.CommandText = sql;
da.SelectCommand = cmd;
cb = new System.Data.SQLite.SQLiteCommandBuilder(da);
da.Fill(ds, "categories");
MainWindow.MainForm.GetLogger().Add(
"Перегенерировали таблицу категорий (исправление некорректности).");
}
if (ds.Tables["categories"].Rows.Count == 0)
{
DataRow dr = ds.Tables["categories"].NewRow();
dr["id"] = 1;
dr["categories_xml"] = categories_xml;
dr["stamp"] = timeStamp;
ds.Tables["categories"].Rows.Add(dr);
}
else
{
ds.Tables["categories"].Rows[0]["categories_xml"] = categories_xml;
ds.Tables["categories"].Rows[0]["stamp"] = timeStamp;
}
da.Update(ds.Tables["categories"]);
}
catch (Exception ex)
{
MainWindow.MainForm.GetLogger().Add("Ошибка записи категорий в БД: " + ex.Message);
}
}
}
}
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "")
{
bool edadesnumero = true;
try
{
Int32.Parse(textBox2.Text);
}
catch
{
edadesnumero = false;
}
if (edadesnumero == true)
{
try
{
fecha = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
nombre = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
edad = Int32.Parse(dataGridView1.SelectedRows[0].Cells[2].Value.ToString());
apoyo = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "UPDATE Donaciones SET Nombre = '" + textBox1.Text + "', Edad = '" + textBox2.Text + "', Apoyo ='" + textBox3.Text + "', Comunidad = '" + textBox4.Text + "' WHERE Nombre='" + nombre + "' AND Edad='" + edad + "' AND Apoyo = '" + apoyo + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Cambios guardados con exito");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;";
//create the database query
string query = "select * from Donaciones";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No se ha seleccionado ninguna donacion para modificar");
}
}
else
{
MessageBox.Show("Solo se aceptan numeros en el campo de edad");
}
}
else
{
MessageBox.Show("No se pueden guardar los cambios ya que hay campos en blanco");
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox5.Text != "" && textBox4.Text != "" && textBox3.Text != "" && textBox2.Text != "")
{
try
{
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";
string nombre = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string usuario = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
string tipodeusuario = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
//create the database query
string query = "SELECT * FROM Usuarios WHERE Nombre = '" + nombre + "' AND Usuario = '" + usuario + "' AND TipoDeUsuario = '" + tipodeusuario + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
DataRow Row = dTable.Rows[0];
string ID = Row["ID"].ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "UPDATE Usuarios SET Nombre = '" + textBox5.Text + "', Usuario = '" + textBox2.Text + "', Contrasena = '" + textBox3.Text + "', TipoDeUsuario = '" + comboBox1.Text + "', FechaContrasena = '" + DateTime.Now.ToShortDateString() + "' WHERE ID = '" + ID + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Usuario modificado exitosamente");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
connString = @"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;";
//create the database query
query = "select Nombre,Usuario,TipoDeUsuario from Usuarios";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No hay usuarios que modificar");
}
}
else
{
MessageBox.Show("No puede dejar espacios en blanco");
}
}
private void ModificarUsuario_Load(object sender, EventArgs e)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;";
DataSet DS = new DataSet();
SQLiteConnection con = new SQLiteConnection(connString);
con.Open();
SQLiteDataAdapter DA = new SQLiteDataAdapter("select Nombre,Usuario,TipoDeUsuario from Usuarios", con);
DA.Fill(DS, "Usuarios");
dataGridView1.DataSource = DS.Tables["Usuarios"];
con.Close();
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
int i = 0;
foreach (DataGridViewColumn c in dataGridView1.Columns)
{
i += c.Width;
}
if ((i + dataGridView1.RowHeadersWidth + 2) > 616)
{
dataGridView1.Width = 616;
dataGridView1.Left = 79;
}
else
{
dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2;
dataGridView1.Left = 211;
}
try
{
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";
string nombre = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string usuario = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
string tipodeusuario = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
//create the database query
string query = "SELECT * FROM Usuarios WHERE Nombre = '" + nombre + "' AND Usuario = '" + usuario + "' AND TipoDeUsuario = '" + tipodeusuario + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
DataRow Row = dTable.Rows[0];
string contrasena = Row["Contrasena"].ToString();
textBox5.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
textBox2.Text = usuario;
textBox3.Text = contrasena;
textBox4.Text = contrasena;
comboBox1.SelectedIndex = comboBox1.FindStringExact(tipodeusuario);
}
catch
{
textBox5.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
comboBox1.SelectedIndex = 1;
}
}
private void button12_Click(object sender, EventArgs e)
{
byte[] pic = ImageToByte(pictureBox2.Image, System.Drawing.Imaging.ImageFormat.Jpeg);
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
string conStringDatosUsuarios = @"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;";
SQLiteConnection con = new SQLiteConnection(conStringDatosUsuarios);
SQLiteCommand cmd = con.CreateCommand();
cmd.CommandText = String.Format("INSERT INTO SRTamanoTipo VALUES ('" + label28.Text + "','" + textBox14.Text + "','" + textBox15.Text + "','" + textBox16.Text + "','" + textBox17.Text + "','" + textBox18.Text + "','" + textBox19.Text + "','" + comboBox2.Text + "','" + comboBox3.Text + "','" + comboBox4.Text + "', @0);");
SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary);
param.Value = pic;
cmd.Parameters.Add(param);
con.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exc1)
{
MessageBox.Show(exc1.Message);
}
con.Close();
//-----
cmd.CommandText = String.Format("INSERT INTO SRDatosGenerales VALUES ('" + label28.Text + "','" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "','" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "','" + textBox1.Text + "','" + textBox2.Text + "','" + comboBox1.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "', '" + textBox8.Text + "', '" + textBox9.Text + "', '" + textBox10.Text + "', '" + textBox11.Text + "', '" + textBox12.Text + "', '" + textBox13.Text + "');");
con.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exc1)
{
MessageBox.Show(exc1.Message);
}
con.Close();
panel1.BringToFront();
this.Controls.Clear();
this.InitializeComponent();
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBESIL.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM SRTamanoTipo";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["IDFormatoSillas"].ToString();
int autonum = Int32.Parse(num);
label28.Text = (autonum + 1).ToString();
}
else
{
label28.Text = "1";
}
}
private void button2_Click(object sender, EventArgs e)
{
string numeroencuesta = "";
try
{
numeroencuesta = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para borrar
cmd.CommandText = "DELETE FROM DatosGenerales WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM CuadroFamiliar WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM DatosVivienda WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM EgresosMensuales WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM Observaciones WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM ServicioMedico WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Encuesta eliminada exitosamente");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;";
//create the database query
string query = "select Noencuesta AS [Numero de Encuesta], Programa, Nombre, Lugardeorigen as [Lugar de Origen], Domicilio, Tiempopermanencia as [Tiempo de Permanencia], NoIntegrantesFam as [Numero de Integrantes Familiares], Parroquia from DatosGenerales";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//ͨ�������ݺ���
private string UpdateTable(DataTable srcTable, string tableName)
{
try
{
command.CommandText = "SELECT * FROM " + tableName;
SQLiteDataAdapter oda = new SQLiteDataAdapter(command);
SQLiteCommandBuilder ocb = new SQLiteCommandBuilder(oda);
oda.InsertCommand = ocb.GetInsertCommand();
oda.DeleteCommand = ocb.GetDeleteCommand();
oda.UpdateCommand = ocb.GetUpdateCommand();
oda.Update(srcTable);
}
catch (Exception ex)
{
return ex.ToString();
}
return "success";
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public void QueryProducts()
{
// Create data adapter
this.ProductsDataAdapter = new SQLiteDataAdapter(Properties.Resources.QueryProducts, _sqlConnection);
SQLiteCommandBuilder builder = new SQLiteCommandBuilder(this.ProductsDataAdapter);
// Create data set
this.ProductsDataSet = new DataSet("ProductSet");
this.ProductsDataAdapter.Fill(this.ProductsDataSet, "products");
// Get data table
this.ProductsDataTable = this.ProductsDataSet.Tables["products"];
this.ProductsDataTable.PrimaryKey = new DataColumn[] { this.ProductsDataTable.Columns["id"] };
}
protected override DbDataAdapter GetDataAdapter(string tableName, string schemaName, out DbCommandBuilder builder)
{
var adapter = new SQLiteDataAdapter(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM \"{0}\"", tableName), _connection);
builder = new SQLiteCommandBuilder(adapter);
return adapter;
}
private void button4_Click(object sender, EventArgs e)
{
if (comboBox2.SelectedIndex == 1)
{
int cantidad;
if (int.TryParse(textBox12.Text, out cantidad))
{
float peso;
if (float.TryParse(textBox14.Text, out peso))
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "INSERT INTO Salidas (Numero, Salida, Descripcion, Cantidad, Peso, Equivalentes, Nota, TipoDeApoyo, Responsable, Huella) values ('" + label12.Text + "', '" + textBox9.Text + "', '" + textBox11.Text + "', '" + textBox12.Text + "', '" + textBox14.Text + "', '" + textBox16.Text + "', '" + textBox15.Text + "', '" + textBox13.Text + "', '" + textBox10.Text + "',@0 )";
SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary);
cmd.Connection = sqlConnection1;
param.Value = fp_image;
cmd.Parameters.Add(param);
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Salida guardada con exito.");
textBox9.Text = "";
textBox11.Text = "";
textBox12.Text = "";
textBox14.Text = "";
textBox16.Text = "";
textBox15.Text = "";
textBox13.Text = "";
textBox10.Text = "";
///create the connection string
string connString = @"Data Source= " + appPath + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Salidas";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["Numero"].ToString();
int autonum = Int32.Parse(num);
label12.Text = (autonum + 1).ToString();
}
else
{
label12.Text = "1";
}
}
else
{
MessageBox.Show("Revise de nuevo su campo de peso, solo se permiten numeros flotantes.");
}
}
else
{
MessageBox.Show("Revise de nuevo su campo de cantidad, solo se permiten numeros.");
}
}
}
private void ReporteSalidaSeleccionada_Load(object sender, EventArgs e)
{
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Salidas where Numero = '" + numero + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
DataRow Row = dTable.Rows[0];
System.Byte[] rdr = (System.Byte[])Row["Huella"];
Image imagen = ByteToImage(rdr);
imagen.Save(appPath2 + @"\huella.jpg", System.Drawing.Imaging.ImageFormat.Jpeg);
//--------------------------
CrystalReport8 objRpt = new CrystalReport8();
CrystalDecisions.Shared.ParameterValues RpDatos = new CrystalDecisions.Shared.ParameterValues();
CrystalDecisions.Shared.ParameterDiscreteValue DsCC = new CrystalDecisions.Shared.ParameterDiscreteValue();
CrystalDecisions.Shared.ParameterField paramField = new CrystalDecisions.Shared.ParameterField();
paramField.Name = "Imagen";
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
String ConnStr = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;";
System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr);
String Query1 = "SELECT * FROM Salidas where Numero = " + numero;
//String Query1 = "Select * from SRDatosGenerales Where IDFormatoSillas = '"+idformatossillas+"'";
//String Query1 = "Select * from SRTamanoTipo Where IDFormatoSillas = '"+idformatossillas+"'";
System.Data.SQLite.SQLiteDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter(Query1, ConnStr);
DataSet Ds = new DataSet();
// here my_dt is the name of the DataTable which we
// created in the designer view.
adapter.Fill(Ds, "DataTable5");
// Setting data source of our report object
objRpt.SetDataSource(Ds);
// Binding the crystalReportViewer with our report object.
this.crystalReportViewer1.ReportSource = objRpt;
appPath = appPath + @"\huella.jpg";
DsCC.Value = appPath;
RpDatos.Add(DsCC);
objRpt.DataDefinition.ParameterFields["Imagen"].ApplyCurrentValues(RpDatos);
RpDatos.Clear();
paramField.HasCurrentValue = true;
}
public void Update(DataTable table)
{
using (SQLiteConnection connection = new SQLiteConnection(this.GetConnectionString()))
{
this._connection = connection;
SQLiteDataAdapter adapter = new SQLiteDataAdapter(
string.Format("SELECT * FROM {0}", table.TableName),
connection);
SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(adapter);
adapter.RowUpdated += OnRowUpdated;
try
{
adapter.Update(table);
}
catch (Exception)
{
throw;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";
string nombre = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string usuario = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
string tipodeusuario = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
//create the database query
string query = "SELECT * FROM Usuarios WHERE Nombre = '" +nombre+ "' AND Usuario = '" + usuario + "' AND TipoDeUsuario = '" + tipodeusuario + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
DataRow Row = dTable.Rows[0];
string ID = Row["ID"].ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "DELETE FROM Usuarios WHERE ID = '" + ID + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Usuario eliminado exitosamente");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
connString = @"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;";
//create the database query
query = "select Nombre,Usuario,TipoDeUsuario from Usuarios";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No hay usuarios que borrar");
}
}
/// <summary>
/// Retrieves foreign key information from the specified set of filters
/// </summary>
/// <param name="strCatalog">An optional catalog to restrict results on</param>
/// <param name="strTable">An optional table to restrict results on</param>
/// <param name="strKeyName">An optional foreign key name to restrict results on</param>
/// <returns>A DataTable with the results of the query</returns>
private DataTable Schema_ForeignKeys(string strCatalog, string strTable, string strKeyName)
{
DataTable tbl = new DataTable("ForeignKeys");
DataRow row;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
tbl.Columns.Add("TABLE_CATALOG", typeof(string));
tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
tbl.Columns.Add("TABLE_NAME", typeof(string));
tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string));
tbl.Columns.Add("IS_DEFERRABLE", typeof(bool));
tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool));
tbl.Columns.Add("FKEY_ID", typeof(int));
tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string));
tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int));
tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string));
tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string));
tbl.Columns.Add("FKEY_TO_TABLE", typeof(string));
tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string));
tbl.Columns.Add("FKEY_ON_UPDATE", typeof(string));
tbl.Columns.Add("FKEY_ON_DELETE", typeof(string));
tbl.Columns.Add("FKEY_MATCH", typeof(string));
if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb;
tbl.BeginLoadData();
using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this))
using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
{
while (rdTables.Read())
{
if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), StringComparison.OrdinalIgnoreCase) == 0)
{
try
{
using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder())
//using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
//using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
using (SQLiteCommand cmdKey = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this))
using (SQLiteDataReader rdKey = cmdKey.ExecuteReader())
{
while (rdKey.Read())
{
row = tbl.NewRow();
row["CONSTRAINT_CATALOG"] = strCatalog;
row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}", rdTables[2], rdKey.GetInt32(0), rdKey.GetInt32(1));
row["TABLE_CATALOG"] = strCatalog;
row["TABLE_NAME"] = builder.UnquoteIdentifier(rdTables.GetString(2));
row["CONSTRAINT_TYPE"] = "FOREIGN KEY";
row["IS_DEFERRABLE"] = false;
row["INITIALLY_DEFERRED"] = false;
row["FKEY_ID"] = rdKey[0];
row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString());
row["FKEY_TO_CATALOG"] = strCatalog;
row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString());
row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString());
row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1];
row["FKEY_ON_UPDATE"] = (rdKey.FieldCount > 5) ? rdKey[5] : String.Empty;
row["FKEY_ON_DELETE"] = (rdKey.FieldCount > 6) ? rdKey[6] : String.Empty;
row["FKEY_MATCH"] = (rdKey.FieldCount > 7) ? rdKey[7] : String.Empty;
if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), StringComparison.OrdinalIgnoreCase) == 0)
tbl.Rows.Add(row);
}
}
}
catch (SQLiteException)
{
}
}
}
}
tbl.EndLoadData();
tbl.AcceptChanges();
return tbl;
}
private void button1_Click(object sender, EventArgs e)
{
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Usuarios";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
bool usuarioexistente = false;
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
if (Row["Usuario"].ToString() == textBox1.Text && Row["Contrasena"].ToString() == textBox2.Text)
{
MessageBox.Show("Bienvenido al Sistema Caritas");
((Form)this.MdiParent).Controls["menuStrip1"].Enabled = true;
usuarioexistente = true;
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "Update Usuarios Set FechaUltimaSesion = '" + DateTime.Now.ToShortDateString() + "' Where ID = '" + Row["ID"].ToString() + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
string prueba = Row["TipoDeUsuario"].ToString();
if (Row["TipoDeUsuario"].ToString() == "Administrador")
{
Form frm = (Form)this.MdiParent;
MenuStrip ms = (MenuStrip)frm.Controls["menuStrip1"];
ToolStripMenuItem ti = (ToolStripMenuItem)ms.Items["controlDeUsuariosToolStripMenuItem"];
ti.Visible = true;
}
Bienvenida.tipouser = Row["TipoDeUsuario"].ToString();
this.Close();
//--------------
appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;";
//create the database query
query = "SELECT * FROM Almacen";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count > 0)
{
for (int k = 0; k < dTable.Rows.Count; k++)
{
Row = dTable.Rows[k];
Int32 cantidadexistencia = Int32.Parse(Row["Cantidadexistencia"].ToString());
string nombre = Row["Nombrearticulo"].ToString();
appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
connString = @"Data Source= " + appPath2 + @"\DBstk.s3db ;Version=3;";
//create the database query
query = "SELECT * FROM Existencia Where ArticuloID = '" + Row["ArticuloID"].ToString() + "'";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable2 = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable2);
dAdapter.Update(dTable2);
if (dTable2.Rows.Count > 0)
{
Row = dTable2.Rows[0];
Int64 cantlimite = Int64.Parse(Row["Limite"].ToString());
if (cantlimite >= cantidadexistencia)
{
MessageBox.Show("Quedan " + cantlimite.ToString() + " o menos en existencia del articulo " + nombre + "");
}
}
}
}
//--------------
break;
}
}
if (usuarioexistente == false)
{
MessageBox.Show("Usuario o contraseña incorrecta");
}
}
internal static void KeyInfoTest(DbConnection cnn)
{
using (DbCommand cmd = cnn.CreateCommand())
{
// First test against integer primary key (optimized) keyinfo fetch
cmd.CommandText = "Create table keyinfotest (id integer primary key, myuniquevalue integer unique not null, myvalue varchar(50))";
cmd.ExecuteNonQuery();
cmd.CommandText = "Select * from keyinfotest";
using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
using (DataTable tbl = reader.GetSchemaTable())
{
if (tbl.Rows.Count != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
}
}
cmd.CommandText = "SELECT MyValue FROM keyinfotest";
using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
using (DataTable tbl = reader.GetSchemaTable())
{
if (tbl.Rows.Count != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
}
}
cmd.CommandText = "DROP TABLE keyinfotest";
cmd.ExecuteNonQuery();
// Now test against non-integer primary key (unoptimized) subquery keyinfo fetch
cmd.CommandText = "Create table keyinfotest (id char primary key, myuniquevalue integer unique not null, myvalue varchar(50))";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT MyValue FROM keyinfotest";
using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
using (DataTable tbl = reader.GetSchemaTable())
{
if (tbl.Rows.Count != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
}
}
cmd.CommandText = "Select * from keyinfotest";
using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
using (DataTable tbl = reader.GetSchemaTable())
{
if (tbl.Rows.Count != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
}
}
// Make sure commandbuilder can generate an update command with the correct parameter count
using (DbDataAdapter adp = new SQLiteDataAdapter())
using (DbCommandBuilder builder = new SQLiteCommandBuilder())
{
adp.SelectCommand = cmd;
builder.DataAdapter = adp;
builder.ConflictOption = ConflictOption.OverwriteChanges;
using (DbCommand updatecmd = builder.GetUpdateCommand())
{
if (updatecmd.Parameters.Count != 4)
throw new ArgumentOutOfRangeException("Wrong number of parameters in update command!");
}
}
}
}
// Utilizes the SQLiteCommandBuilder, which in turn utilizes SQLiteDataReader's GetSchemaTable() functionality
internal void InsertMany(DbConnection cnn, bool bWithIdentity)
{
int nmax = 1000;
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbDataAdapter adp = new SQLiteDataAdapter())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.Transaction = dbTrans;
cmd.CommandText = "SELECT * FROM TestCase WHERE 1=2";
adp.SelectCommand = cmd;
using (DbCommandBuilder bld = new SQLiteCommandBuilder())
{
bld.DataAdapter = adp;
using (adp.InsertCommand = (SQLiteCommand)((ICloneable)bld.GetInsertCommand()).Clone())
{
bld.DataAdapter = null;
if (bWithIdentity)
{
adp.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS [ID]";
adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}
using (DataTable tbl = new DataTable())
{
adp.Fill(tbl);
for (int n = 0; n < nmax; n++)
{
DataRow row = tbl.NewRow();
row[1] = n + nmax;
tbl.Rows.Add(row);
}
frm.Write(String.Format(" InsertMany{0} ({1} rows) Begins ... ", (bWithIdentity == true) ? "WithIdentityFetch" : " ", nmax));
int dtStart = Environment.TickCount;
adp.Update(tbl);
int dtEnd = Environment.TickCount;
dtEnd -= dtStart;
frm.Write(String.Format("Ends in {0} ms ... ", (dtEnd)));
dtStart = Environment.TickCount;
dbTrans.Commit();
dtEnd = Environment.TickCount;
dtEnd -= dtStart;
frm.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
}
}
}
}
}
}
}
/// <summary>
/// Turn a datatable into a table in the temporary database for the connection
/// </summary>
/// <param name="cnn">The connection to make the temporary table in</param>
/// <param name="table">The table to write out</param>
/// <param name="dest">The temporary table name to write to</param>
private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest)
{
StringBuilder sql = new StringBuilder();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteCommand cmd = cnn.CreateCommand())
using (DataTable source = new DataTable())
{
sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest));
string separator = String.Empty;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType);
string typeName = SQLiteConvert.DbTypeToTypeName(dbtypeName);
sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator);
separator = ", ";
}
sql.Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest));
using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd))
{
builder.DataAdapter = adp;
adp.Fill(source);
foreach (DataRow row in table.Rows)
{
object[] arr = row.ItemArray;
source.Rows.Add(arr);
}
adp.Update(source);
}
}
}
private void sourceRefresh(String path)
{
// DataGridの内容消去
clsSource();
checkBox1.Checked = false;
clsTarget();
clsRename();
// マウスカーソル変更
Cursor preCursor = this.Cursor;
this.Cursor = Cursors.WaitCursor;
// 項目無効
allButtons(false);
totalCount = 0;
// ファイル名の取得
using (var transaction = cn.BeginTransaction()) {
using (SQLiteCommand cmd = cn.CreateCommand()) {
cmd.CommandText = "PRAGMA journal_mode = MEMORY";
cmd.ExecuteNonQuery();
sm = SearchMode.None;
recursiveFileList(path, ref totalCount);
}
transaction.Commit();
}
adapter = new SQLiteDataAdapter("SELECT Id, checked, path, file, size, date FROM flist;", cn);
SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
if (adapter != null)
adapter.Fill(dtSource);
// カーソルを元に戻す
this.Cursor = preCursor;
// 項目有効
allButtons(true);
// ボタン表示チェック
buttonEnable();
// 件数表示
checkedCounter(totalCount);
// パネル1を表示
viewPanel(1);
}
private void 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 textBox12_TextChanged_1(object sender, EventArgs e)
{
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Almacen Where ArticuloID = '" + textBox12.Text + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
label27.Text = "";
textBox10.Text = "";
textBox9.Text = "";
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
label27.Text = Row["Cantidadexistencia"].ToString();
cantidadtotal = Int32.Parse(Row["Cantidadexistencia"].ToString());
textBox10.Text = Row["Precioventa"].ToString();
textBox9.Text = Row["Preciocompra"].ToString();
}
///create the connection string
appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
connString = @"Data Source= " + appPath2 + @"\DBstk.s3db ;Version=3;";
//create the database query
query = "SELECT * FROM Existencia Where ArticuloID = '" + textBox12.Text + "'";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
textBox14.Text = Row["Limite"].ToString();
}
}
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
if (tabControl1.SelectedIndex == 2)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";
//create the database query
string query = "SELECT * From Almacen";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
int i = 0;
foreach (DataGridViewColumn c in dataGridView1.Columns)
{
i += c.Width;
}
dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2;
}
else if (tabControl1.SelectedIndex == 3)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";
//create the database query
string query = "SELECT ArticuloID, Nombrearticulo From Almacen";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView2.DataSource = bSource;
dAdapter.Update(dTable);
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
int i = 0;
foreach (DataGridViewColumn c in dataGridView2.Columns)
{
i += c.Width;
}
dataGridView2.Width = i + dataGridView2.RowHeadersWidth + 2;
BarcodeLib.Barcode barcode = new BarcodeLib.Barcode()
{
IncludeLabel = true,
Alignment = AlignmentPositions.CENTER,
Width = 300,
Height = 100,
RotateFlipType = RotateFlipType.RotateNoneFlipNone,
BackColor = Color.White,
ForeColor = Color.Black,
};
try
{
Image img = barcode.Encode(TYPE.CODE128B, dataGridView2.SelectedRows[0].Cells[0].Value.ToString());
pictureBox1.Image = img;
}
catch
{
}
}
}
private void button3_Click_1(object sender, EventArgs e)
{
int cant = 0;
bool cantidad = Int32.TryParse(textBox6.Text, out cant);
float vent = 0.0F;
bool venta = float.TryParse(textBox7.Text, out vent);
float comp = 0.0F;
bool compra = float.TryParse(textBox8.Text, out comp);
if (textBox1.Text != "" && cantidad == true && compra == true && venta == true)
{
try
{
bool existeprov = false;
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Proveedor Where Nombreproveedor = '" + textBox5.Text + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
if (Row["Nombreproveedor"].ToString() == textBox5.Text)
{
existeprov = true;
}
}
if (existeprov == true)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBPInc.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "INSERT INTO Almacen Values ('" + Int64.Parse(textBox4.Text) + "', '" + textBox1.Text + "', '" + float.Parse(textBox7.Text) + "', '" + float.Parse(textBox8.Text) + "', '" + Int32.Parse(textBox6.Text) + "' ,'" + textBox5.Text + "', '" + DateTime.Now.ToShortDateString() + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBstk.s3db ;Version=3;");
cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT INTO Existencia Values('"+Int64.Parse(textBox4.Text)+"', '"+Int64.Parse(textBox13.Text)+"')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox7.Text = "";
textBox8.Text = "";
textBox1.Text = "";
textBox13.Text = "";
}
else
{
MessageBox.Show("El Proveedor Especificado no existe");
}
}
catch
{
MessageBox.Show("Ya existe un articulo con esa ID");
}
}
else
{
if (textBox1.Text != "")
{
MessageBox.Show("No ha desginado un nombre para el articulo");
}
else if (cantidad == false)
{
MessageBox.Show("La cantidad puesta no es valida");
}
else if (compra == false)
{
MessageBox.Show("La cantidad puesta no es valida en el campo de compra");
}
else if (venta == false)
{
MessageBox.Show("La cantidad puesta no es valida en el campo de venta");
}
}
}
/// <summary>
/// JVUmaReadWriting処理
/// </summary>
public static void JVUmaReadWriting()
{
bool reading = true;
int nCount = 0;
object buffObj = new byte[0];
int buffsize = 110000;
string timeStamp;
string buffname;
DataTable umaDataTable = new DataTable();
DataTable raceDataTable = new DataTable();
DataTable raceUmaDataTable = new DataTable();
// 初期化時
if (JVRelayClass.Option == (int)JVRelayClass.eJVOpenFlag.SetupSkipDialog)
{
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.CommandText = "DELETE FROM uma";
command.ExecuteNonQuery();
}
}
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.CommandText = "SELECT * FROM uma";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
{
da.Fill(umaDataTable);
}
}
raceDataTable.Columns.Add("RaceKey", typeof(string));
raceDataTable.Columns.Add("RaceDate", typeof(string));
raceDataTable.Columns.Add("DataKubun", typeof(string));
raceDataTable.Columns.Add("GradeCD", typeof(string));
raceDataTable.Columns.Add("SyubetuCD", typeof(string));
raceUmaDataTable.Columns.Add("RaceKey", typeof(string));
raceUmaDataTable.Columns.Add("RaceDate", typeof(string));
raceUmaDataTable.Columns.Add("KettoNum", typeof(string));
raceUmaDataTable.Columns.Add("KakuteiJyuni", typeof(string));
if (umaDataTable.PrimaryKey.Length == 0)
{
umaDataTable.PrimaryKey = new[] { umaDataTable.Columns["KettoNum"] };
}
if (raceDataTable.PrimaryKey.Length == 0)
{
raceDataTable.PrimaryKey = new[] { raceDataTable.Columns["RaceKey"] };
}
if (raceUmaDataTable.PrimaryKey.Length == 0)
{
raceUmaDataTable.PrimaryKey = new[] { raceUmaDataTable.Columns["RaceKey"], raceUmaDataTable.Columns["KettoNum"] };
}
ProgressUserState.Maxinum = ReadCount;
ProgressUserState.Value = 0;
ProgressUserState.Text = "データ読み込み中...";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
do
{
//---------------------
// JVLink読込み処理
//---------------------
buffObj = new byte[0];
int nRet = AxJVLink.JVGets(ref buffObj, buffsize, out buffname);
timeStamp = AxJVLink.m_CurrentFileTimeStamp;
byte[] buff = (byte[])buffObj;
string buffStr = System.Text.Encoding.GetEncoding(932).GetString(buff);
// 正常
if (0 < nRet)
{
string recordSpec = JVData_Struct.MidB2S(ref buff, 1, 2);
buffObj = new byte[0];
buff = new byte[0];
switch (recordSpec)
{
case "UM":
{
JVData_Struct.JV_UM_UMA uma = new JVData_Struct.JV_UM_UMA();
uma.SetDataB(ref buffStr);
WriteDbUmaData(eOutput.Umanushi, uma, umaDataTable);
}
break;
case "RA":
{
JVData_Struct.JV_RA_RACE race = new JVData_Struct.JV_RA_RACE();
race.SetDataB(ref buffStr);
WriteDbRaceData(eOutput.Umanushi, race, raceDataTable);
}
break;
case "SE":
{
JVData_Struct.JV_SE_RACE_UMA raceUma = new JVData_Struct.JV_SE_RACE_UMA();
raceUma.SetDataB(ref buffStr);
WriteDbRaceUmaData(eOutput.Umanushi, raceUma, raceUmaDataTable);
}
break;
default:
// 対象外recspecのファイルをスキップする。
AxJVLink.JVSkip();
nCount++;
ProgressUserState.Value = nCount;
ProgressUserState.Text = "データ読み込み中...";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
break;
}
}
// ファイルの切れ目
else if (-1 == nRet)
{
nCount++;
ProgressUserState.Value = nCount;
ProgressUserState.Text = "データ読み込み中...";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
}
// 全レコード読込み終了(EOF)
else if (0 == nRet)
{
ProgressUserState.Value = ProgressUserState.Maxinum;
ProgressUserState.Text = "データ読み込み完了";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
reading = false;
}
// エラー
else if (-1 > nRet)
{
// エラーファイルをスキップする。
AxJVLink.JVSkip();
nCount++;
ProgressUserState.Value = nCount;
ProgressUserState.Text = "データ読み込み中...";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
}
System.Threading.Thread.Sleep(10);
}
while (true == reading);
// データ整備
if (raceUmaDataTable.Rows.Count > 0)
{
foreach (DataRow raceUmaDataRow in raceUmaDataTable.Select("", "RaceDate"))
{
DataRow raceDataRow = raceDataTable.Rows.Find(raceUmaDataRow["RaceKey"]);
DataRow umaDataRow = umaDataTable.Rows.Find(raceUmaDataRow["KettoNum"]);
if (raceDataRow != null && umaDataRow != null)
{
string raceDate = umaDataRow["RaceDate"].ToString();
if ("" == raceDate || 0 > raceDate.CompareTo(raceUmaDataRow["RaceDate"].ToString()))
{
if (raceDataRow["DataKubun"].ToString() != "9" && raceDataRow["DataKubun"].ToString() != "0")
{
// レースを追加
umaDataRow["BeforeUmaClass"] = umaDataRow["UmaClass"];
umaDataRow["BeforeRaceDate"] = umaDataRow["RaceDate"];
umaDataRow["BeforeRaceDataKubun"] = umaDataRow["RaceDataKubun"];
umaDataRow["BeforeRaceGradeCD"] = umaDataRow["RaceGradeCD"];
umaDataRow["BeforeRaceSyubetuCD"] = umaDataRow["RaceSyubetuCD"];
umaDataRow["BeforeRaceKakuteiJyuni"] = umaDataRow["RaceKakuteiJyuni"];
umaDataRow["RaceDate"] = raceUmaDataRow["RaceDate"];
umaDataRow["RaceDataKubun"] = raceDataRow["DataKubun"];
umaDataRow["RaceGradeCD"] = raceDataRow["GradeCD"];
umaDataRow["RaceSyubetuCD"] = raceDataRow["SyubetuCD"];
umaDataRow["RaceKakuteiJyuni"] = raceUmaDataRow["KakuteiJyuni"];
umaDataRow["UmaClass"] = GetUmaClass(umaDataRow);
}
}
else if (0 == raceDate.CompareTo(raceUmaDataRow["RaceDate"].ToString()))
{
if (raceDataRow["DataKubun"].ToString() == "9" || raceDataRow["DataKubun"].ToString() == "0")
{
// レース中止、データ削除のため戻す
umaDataRow["UmaClass"] = umaDataRow["BeforeUmaClass"];
umaDataRow["RaceDate"] = umaDataRow["BeforeRaceDate"];
umaDataRow["RaceDataKubun"] = umaDataRow["BeforeRaceDataKubun"];
umaDataRow["RaceGradeCD"] = umaDataRow["BeforeRaceGradeCD"];
umaDataRow["RaceSyubetuCD"] = umaDataRow["BeforeRaceSyubetuCD"];
umaDataRow["RaceKakuteiJyuni"] = umaDataRow["BeforeRaceKakuteiJyuni"];
umaDataRow["BeforeUmaClass"] = null;
umaDataRow["BeforeRaceDate"] = null;
umaDataRow["BeforeRaceDataKubun"] = null;
umaDataRow["BeforeRaceGradeCD"] = null;
umaDataRow["BeforeRaceSyubetuCD"] = null;
umaDataRow["BeforeRaceKakuteiJyuni"] = null;
}
else
{
// レース結果の更新
if (umaDataRow["RaceDataKubun"].ToString() != raceDataRow["DataKubun"].ToString())
{
umaDataRow["RaceDataKubun"] = raceDataRow["DataKubun"];
}
if (umaDataRow["RaceGradeCD"].ToString() != raceDataRow["GradeCD"].ToString())
{
umaDataRow["RaceGradeCD"] = raceDataRow["GradeCD"];
}
if (umaDataRow["RaceSyubetuCD"].ToString() != raceDataRow["SyubetuCD"].ToString())
{
umaDataRow["RaceSyubetuCD"] = raceDataRow["SyubetuCD"];
}
if (umaDataRow["RaceKakuteiJyuni"].ToString() != raceUmaDataRow["KakuteiJyuni"].ToString())
{
umaDataRow["RaceKakuteiJyuni"] = raceUmaDataRow["KakuteiJyuni"];
}
if (umaDataRow["UmaClass"].ToString() != GetUmaClass(umaDataRow))
{
umaDataRow["UmaClass"] = GetUmaClass(umaDataRow);
}
}
}
}
}
}
// データ更新
using (SQLiteTransaction tran = DbConn.BeginTransaction())
{
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.Transaction = tran;
command.CommandText = "SELECT * FROM uma";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da))
{
cb.SetAllValues = false;
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.Update(umaDataTable);
}
command.CommandText = "DELETE FROM uma WHERE BirthYear <= '" + DiscardBirthYear + "'";
command.ExecuteNonQuery();
command.CommandText = "UPDATE timestamp SET date ='" + LastFileTimestamp + "'";
command.ExecuteNonQuery();
}
tran.Commit();
JVRelayClass.DbTimeStamp = LastFileTimestamp;
}
}