//------------------------------------------------------------------------------------------------------------------- //Wonky (dont use for now) List<Series> passing (needs testing) public void SaveToExcel(List <Microsoft.Office.Interop.Excel.Series> _series)//include another param for table name & filepath { // string constring = "Data Source = C:\\Users\\Wesley Osborn\\Desktop\\DBtest;//_filepath"; // System.Data.SQLite.SQLiteConnection conDataBase = new System.Data.SQLite.SQLiteConnection(connString); System.Data.SQLite.SQLiteCommand cmdDataBase = new System.Data.SQLite.SQLiteCommand(" SELECT * FROM Emotions ;", sqlite_conn); // DataGridView DataGrid = _DGV; try { System.Data.SQLite.SQLiteDataAdapter sda = new System.Data.SQLite.SQLiteDataAdapter(); sda.SelectCommand = cmdDataBase; System.Data.DataTable dbdataset = new System.Data.DataTable(); sda.Fill(dbdataset); BindingSource bSource = new BindingSource(); bSource.DataSource = dbdataset; // _DGV.DataSource = bSource; sda.Update(dbdataset); //export to Excel DataSet ds = new DataSet("New_DataSet"); ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; sda.Fill(dbdataset); ds.Tables.Add(dbdataset); ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds); } catch (Exception) { throw; } }
private void Button_Click(object sender, RoutedEventArgs e) { string dbConnectionString = @"Data Source=patient.db;Version=3;"; SQLiteConnection sqlite_connection = new SQLiteConnection(dbConnectionString); try { sqlite_connection.Open(); string query = "select * from patient_table"; SQLiteCommand create_command = new SQLiteCommand(query, sqlite_connection); create_command.ExecuteNonQuery(); //SQLiteDataReader dr = create_command.ExecuteReader(); SQLiteDataAdapter dataAdapt = new SQLiteDataAdapter(query, sqlite_connection); //var ds = new DataSet(); DataTable dt = new DataTable("patient_table"); //dataAdapt.Fill(ds,"patient_table"); dataAdapt.Fill(dt); // listView1.DataContext = ds.Tables["patient_table"].DefaultView; admin_monthly_accounting_datagrid.ItemsSource = dt.DefaultView ; dataAdapt.Update(dt); sqlite_connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public DataSet GetDataByQuery(string search) { CompareInfo Compare = CultureInfo.InvariantCulture.CompareInfo; SQLiteDBHelper db = new SQLiteDBHelper(path); string outS = "("; bool flag = false; search = search.Replace(" ", ""); using (SQLiteDataReader reader = db.ExecuteReader("select ID,CodeName,Comments from CodeMass order by id", null)) { while (reader.Read()) { if ((Compare.IndexOf(reader.GetString(1).Replace(" ", ""), search, CompareOptions.IgnoreCase) != -1) || (Compare.IndexOf(reader.GetString(2).Replace(" ", ""), search, CompareOptions.IgnoreCase) != -1)) { outS = outS + reader.GetInt64(0).ToString() + ","; flag = true; } } if (flag) outS = outS.Substring(0, outS.Length - 1) + ")"; else outS="(-1,-2)"; } String connectionString = "Data Source=" + path; //String selectCommand = "Select ID, CodeName,Language,AddTime,UpdateTime,Source_Code,Comments from CodeMass"; //String selectCommand = "select * from CodeMass where CodeName like '%"+ChangeStrToLikeStr(search) + "%'"; String selectCommand = "select * from CodeMass where ID in " + outS; SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(selectCommand, connectionString); DataSet ds = new DataSet(); dataAdapter.Fill(ds, "T_CLASS"); return ds; }
public static List<Candle> Get(string instrumentID, DateTime fromTradingDay) { var table = new DataTable(); var command = new SQLiteCommand("SELECT * FROM candle WHERE instrumentid=@InstrumentID AND tradingday>=@TradingDay", DALUtil.Connection); command.Parameters.AddWithValue("@InstrumentID", instrumentID); command.Parameters.AddWithValue("@TradingDay", fromTradingDay); var adapter = new SQLiteDataAdapter(command); adapter.Fill(table); var candles = new List<Candle>(); foreach (var item in table.Rows) { var row = item as DataRow; var candle = new Candle(); candle.InstrumentID = row.Field<string>(0); candle.TradingDay = row.Field<DateTime>(1); candle.CandleTime = row.Field<DateTime>(2); candle.Open = DALUtil.GetDouble(row[3]); candle.Close = DALUtil.GetDouble(row[4]); candle.High = DALUtil.GetDouble(row[5]); candle.Low = DALUtil.GetDouble(row[6]); candle.Volume = row.Field<int>(7); candle.OpenInterest = DALUtil.GetDouble(row[8]); candles.Add(candle); } return candles; }
private void BajaUsuarios_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; } }
public int CheckcustomerID(int customerid) { string sql = "select * from Listcustomer where ID=@customerid "; try { conn.Open(); SQLiteCommand cmd = new SQLiteCommand(sql, conn); cmd.Parameters.AddWithValue("@customerid", customerid); SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); if (dt.Rows.Count>0) return 1; else return 0; } catch (Exception ex) { return 0; } finally { conn.Close(); } return 1; }
public static DataTable ExecuteReader(string sqlitefn, string command, List<SqliteParam> parameters, out string errorMessage) { DataTable dt = null; try { errorMessage = ""; using (var conn = new SQLiteConnection(sqlitefn)) { conn.Open(); var commandSql = new SQLiteCommand(command, conn); if (parameters != null && parameters.Count > 0) { foreach (SqliteParam param in parameters) { commandSql.Parameters.Add(param.Name, param.Type).Value = param.Value; } } dt = new DataTable(); var sqlDa = new SQLiteDataAdapter(commandSql); sqlDa.Fill(dt); conn.Close(); } } catch (Exception ex) { errorMessage = ex.ToString(); dt = null; } return dt; }
public static DataTable GetData(string strConn, string strSql, int timeout) { DataTable dt = new DataTable("td"); using (SQLiteConnection conn = new SQLiteConnection(strConn)) { conn.Open(); SQLiteCommand cmd = null; SQLiteDataAdapter da = null; try { cmd = new SQLiteCommand(strSql, conn) { CommandTimeout = timeout }; da = new SQLiteDataAdapter { SelectCommand = cmd }; da.Fill(dt); return dt; } catch (Exception ex) { throw new Exception("error getting data " + ex.Message); } finally { if (da != null) { da.Dispose(); } if (cmd != null) { cmd.Dispose(); } conn.Close(); } } }
static void Main(string[] args) { // Create sqlite connection SQLiteConnection connection = new SQLiteConnection(string.Format(@"Data Source={0}\SimpleDatabase.s3db", Environment.CurrentDirectory)); // Open sqlite connection connection.Open(); // Get all rows from example_table SQLiteDataAdapter db = new SQLiteDataAdapter("SELECT * FROM Names", connection); // Create a dataset DataSet ds = new DataSet(); // Fill dataset db.Fill(ds); // Create a datatable DataTable dt = new DataTable("Names"); dt = ds.Tables[0]; // Close connection connection.Close(); // Print table foreach (DataRow row in dt.Rows) { Console.WriteLine(string.Format("{0} {1}", row["Firstname"], row["Surname"])); } Console.ReadLine(); }
public static DataTable ExecuteNonQueryDt(string cmdText, SQLiteConnection con) { DataTable dt = new DataTable("Table"); try { using (con) { SQLiteDataAdapter da = new SQLiteDataAdapter(cmdText, con); con.Open(); da.Fill(dt); con.Close(); } return dt; } catch (Exception ex) { using (FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "\\" + System.DateTime.Now.Date.ToString("dd-MMM-yyyy") + "_Log.txt", FileMode.Append, FileAccess.Write)) { StreamWriter streamWriter = new StreamWriter(file); streamWriter.WriteLine(System.DateTime.Now + " - " + "ExecuteNonQueryDt" + " - " + ex.Message.ToString()); streamWriter.Close(); } return dt; } }
public DataSet GetTotalRecordsInTable(System.Data.SQLite.SQLiteConnection m_dbConnection, string query, Logger logger) { DataSet myDataSet = new DataSet(); try { //string query = $"SELECT count(1) TotalRecords FROM {tableName};"; //records = m_dbConnection.Query<ProcessedDetails1>(query).Count; //System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(m_dbConnection); //command.CommandText = query; //SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;"); //m_dbConnection.Open(); System.Data.SQLite.SQLiteDataAdapter myAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, m_dbConnection); ////myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; myAdapter.Fill(myDataSet, "Records"); //if (myDataSet.Tables[0].Rows.Count > 0) //{ // records = Convert.ToInt32(myDataSet.Tables[0].Rows[0][0].ToString()); //} //m_dbConnection.Close(); } catch (Exception excp) { logger.Error("Error while retrieving from sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace); } return(myDataSet); }
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 Consulta_Load(object sender, EventArgs e) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;"; DataSet DS = new DataSet(); SQLiteConnection con = new SQLiteConnection(connString); con.Open(); SQLiteDataAdapter DA = new SQLiteDataAdapter("select * from Expediente", con); DA.Fill(DS, "Expediente"); dataGridView1.DataSource = DS.Tables["Expediente"]; 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; } else { dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2; } }
private void Reporte_Load_1(object sender, EventArgs e) { Sistema_Caritas.CrystalReport1 objRpt = new Sistema_Caritas.CrystalReport1(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); String ConnStr = @"Data Source=" + appPath + @"\DBpinc.s3db ;Version=3;"; System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr); //String Query1 = "SELECT * FROM Ventashechas Where NVenta = '" + nventas + "'"; String Query1 = "Select NVenta as NVenta, ArticuloID as ArticuloID, Nombrearticulo, Cantidad, Fecha as Fecha, Total as Total, Ventatotal as Ventatotal FROM (SELECT * FROM Ventashechas, Ventas Where Ventashechas.NVenta = Ventas.NVenta) Where NVenta = '" + nventas + "'"; 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, "DataTable1"); // Setting data source of our report object objRpt.SetDataSource(Ds); // Binding the crystalReportViewer with our report object. this.crystalReportViewer2.ReportSource = objRpt; }
private void button1_Click(object sender, EventArgs e) { try { SQLiteConnection con = new SQLiteConnection(dbConnectionString); con.Open(); string sqlquery = "select Invoice_No, Cust_Id from Cust_Invoice"; SQLiteCommand cmd = new SQLiteCommand(sqlquery, con); //cmd.CommandType = CommandType.Text; SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd); DataTable dt = new DataTable(); // dt.Columns.Add("Invoice_No", typeof(int)); //dt.Columns.Add("Cust_Id", typeof(string)); //dt.Columns.Add("Invo_Date", typeof(DateTime)); adp.Fill(dt); bindingSource1.DataSource = dt; //dataGrid2.ItemsSource = dt.DefaultView; dataGridView1.DataSource = bindingSource1; //dataGridView1.DataBindings(); //con.Close(); } catch (SqlException) { MessageBox.Show("To run this example, replace the value of the " + "connectionString variable with a connection string that is " + "valid for your system."); } }
// datagridview de veriler görüntülenecek... public void btnverilerigetir_Click(object sender, EventArgs e) { dt = null; if (!string.IsNullOrEmpty(txttabloisim.Text.Trim())) { SQLiteDataAdapter da = new SQLiteDataAdapter(); if (sayi == 1) { da = db.verigetir(txttabloisim.Text); } else { da = proje.verigetir(txttabloisim.Text); } DataSet ds = new DataSet(); try { da.Fill(ds); dt = ds.Tables[0]; dataGridView1.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } } else MessageBox.Show("Tablo ismini giriniz."); }
public static DataSet ExecuteDataSet(string SqlRequest, SQLiteConnection Connection) { DataSet dataSet = new DataSet(); dataSet.Reset(); SQLiteCommand cmd = new SQLiteCommand(SqlRequest, Connection); try { Connection.Open(); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd); dataAdapter.Fill(dataSet); } catch (SQLiteException ex) { Log.Write(ex); //Debug.WriteLine(ex.Message); throw; // пересылаем исключение на более высокий уровень } finally { Connection.Dispose(); } return dataSet; }
private void loadHeroDiary() { // Load the latest Hero Diary entries using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + dbPath + @"\gv.db;Version=3;New=False;Compress=True;")) { conn.Open(); using (SQLiteCommand cmd = conn.CreateCommand()) { string commandText = "select Diary_ID as ID, Updated, EntryTime, Entry from Diary where HeroName=@HeroName order by Diary_ID desc limit 1000"; cmd.CommandText = commandText; cmd.Parameters.AddWithValue("@HeroName", this.HeroName); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); DataSet ds = new DataSet(); da = new SQLiteDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); BindingSource bindingSource = new BindingSource(); bindingSource.DataSource = ds.Tables[0]; grdDiary.DataSource = bindingSource; grdDiary.AutoGenerateColumns = true; grdDiary.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders; grdDiary.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; } } }
public static void Copy() { DataSet DS = new DataSet(); string ConnectionString = "Data Source=" + CalibrationSettings.Default.ConnectionDir + "Users.db;Version=3;New=False;Compress=True;"; string LoadCommand = "SELECT * FROM users"; DS.Clear(); SQLiteConnection Connection = new SQLiteConnection(ConnectionString); //SQLiteCommand Query = new SQLiteCommand(LoadCommand, Connection); SQLiteDataAdapter DataAdaptor = new SQLiteDataAdapter(LoadCommand, Connection); DataAdaptor.Fill(DS); foreach (DataRow dRow in DS.Tables[0].Rows) { DataClasses.UserProfile CopyUser = new UserProfile(); CopyUser.Gender = dRow[0].ToString(); CopyUser.Name = dRow[2].ToString(); CopyUser.Surname = dRow[3].ToString(); CopyUser.Weight = Convert.ToInt32(dRow[5]); CopyUser.Password = dRow[6].ToString() + "000"; if (dRow[1].ToString() == "255") CopyUser.Name = "Richard"; CopyUser.Name = new String(CopyUser.Name.Where(x => x != ' ' && x != '\r' && x != '\n').ToArray()); CopyUser.EmailAddress = CopyUser.Name + dRow[1].ToString() + "@grucox.com"; AccessorUsers.RegisterNewUser(CopyUser); } }
public CookieCollection GetCookie() { string value = ""; const string DOMAIN = ".nicovideo.jp"; const string KEY = "user_session"; const string TABLE = "moz_cookies"; string QUERY = string.Format("SELECT * FROM {2} WHERE host = \"{0}\" AND name = \"{1}\"", DOMAIN, KEY, TABLE); using (SQLiteConnection con = new SQLiteConnection("Data Source=" + filepath)) using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(QUERY, con)) { DataSet ds = new DataSet(); adapter.Fill(ds); try { value = (string)ds.Tables[0].Rows[0][2]; } catch (Exception e) { } } CookieCollection cookies = new CookieCollection(); cookies.Add(new Cookie(KEY, value, "/", DOMAIN)); return cookies; }
public static DataTable SelectQueryNew (string query, string tablename) { //http://lists.ximian.com/pipermail/mono-list/2005-June/027584.html checkOperatingSystem (); /*IDbConnection dbcon; dbcon = (IDbConnection) new SqliteConnection(CONstr); dbcon.Open(); IDbCommand dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = query; IDataReader reader = dbcmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(reader); dt.AcceptChanges();*/ try { DataTable dt = new DataTable(); using (SQLiteConnection CON = new SQLiteConnection(CONstr)) { SQLiteCommand CMD = new SQLiteCommand(query, CON); DataSet dataset = new DataSet(); SQLiteDataAdapter AD = new SQLiteDataAdapter(CMD); AD.Fill(dataset, tablename); dt = dataset.Tables[0]; } return dt; } catch(Exception Ex) { return new DataTable(); } }
private void ReporteSalidasBitacoraComedor_Load(object sender, EventArgs e) { CrystalReport6 objRpt = new CrystalReport6(); 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"; 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; objRpt.Refresh(); }
public static Toimipiste Hae(int toimipisteId) { Toimipiste toimipiste = null; DataSet ds = new DataSet(); SQLiteConnection conn = new SQLiteConnection(Properties.Settings.Default.Database); conn.Open(); SQLiteCommand comm = conn.CreateCommand(); comm.CommandText = "SELECT id, nimi, lahiosoite, postitoimipaikka, postinro, email, puhelinnro FROM toimipiste WHERE id = $id"; comm.Parameters.AddWithValue("$id", toimipisteId); using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(comm.CommandText, conn)) { adapter.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) { toimipiste = new Toimipiste(); toimipiste.Id = int.Parse(row["id"].ToString()); toimipiste.Nimi = row["nimi"].ToString(); toimipiste.ParsiOsoite(row); } } conn.Close(); return toimipiste; }
private static DataSet FindBooks(string filePath, string searchString) { searchString = searchString .Replace("%", "!%") .Replace("'", "!'") .Replace("\"", "!\"") .Replace("_", "!_") .ToLower(); SQLiteConnection connection = GetConnection(filePath); connection.Open(); using (connection) { DataSet dataSet = new DataSet(); SQLiteDataAdapter adapter = new SQLiteDataAdapter( string.Format( @"SELECT BookTitle, BookAuthor FROM Books WHERE LOWER(BookTitle) LIKE '%{0}%' ESCAPE '!'", searchString), connection); adapter.Fill(dataSet); return dataSet; } }
private void ReporteEntradasComedor_Load(object sender, EventArgs e) { CrystalReport5 objRpt = new CrystalReport5(); 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 Entradas"; 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, "DataTable4"); // Setting data source of our report object objRpt.SetDataSource(Ds); // Binding the crystalReportViewer with our report object. this.crystalReportViewer1.ReportSource = objRpt; objRpt.Refresh(); }
/// <summary> /// 将数据读取到 DataSet 中. /// </summary> public void ReadDataToDataSet() { Console.WriteLine("使用DataAdapter,将数据填充到DataSet中,然后脱离数据库,直接对DataSet进行处理。"); // 建立数据库连接. SQLiteConnection conn = new SQLiteConnection(GetConnString()); // 创建一个适配器 SQLiteDataAdapter adapter = new SQLiteDataAdapter(SQL, conn); // 创建DataSet,用于存储数据. DataSet testDataSet = new DataSet(); // 执行查询,并将数据导入DataSet. adapter.Fill(testDataSet, "result_data"); // 关闭数据库连接. conn.Close(); // 处理DataSet中的每一行数据. foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows) { // 将检索出来的数据,输出到屏幕上. Console.WriteLine("Date:{0} ; Money:{1} ", testRow["SALE_DATE"], testRow["SUM_MONEY"] ); } }
public static DataTable pegaExcecoesBloqueio(string termo) { string sql = "SELECT * FROM excecoes INNER JOIN bloqueios ON blo_id = exc_bloqueio WHERE blo_termo = @termo"; SQLiteConnection con = conexao.conectar(); SQLiteCommand cmd = new SQLiteCommand(sql, con); cmd.Parameters.Add(new SQLiteParameter("@termo",termo)); DataTable dados = null; try { cmd.ExecuteNonQuery(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); dados = new DataTable(); da.Fill(dados); } catch (SQLiteException e) { MessageBox.Show("Erro " + e.Message); } finally { con.Close(); } return dados; }
public static DataTable pegaExececoes(int bloqueio) { string sql = "SELECT * FROM excecoes WHERE exc_bloqueio = @bloqueio"; SQLiteConnection con = conexao.conectar(); SQLiteCommand cmd = new SQLiteCommand(sql, con); cmd.Parameters.Add(new SQLiteParameter("@bloqueio", bloqueio)); try { cmd.ExecuteNonQuery(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); DataTable dados = new DataTable(); da.Fill(dados); return dados; } catch (SQLiteException e) { MessageBox.Show("Erro " + e.Message); return null; } finally { con.Close(); } }
/// <summary> /// Получить данные из таблицы /// </summary> /// <param name="databasename">Имя таблицы</param> /// <param name="where">Условия</param> /// <param name="etc">Остальные параметры: сортировка, группировка и т.д.</param> /// <returns>Таблица с данными</returns> public DataTable FetchAll(string databasename, string where, string etc) { DataTable dt = new DataTable(); string sql = string.Format("SELECT * FROM {0} {1} {2}", databasename, where, etc); ConnectionState previousConnectionState = ConnectionState.Closed; using (SQLiteConnection connect = new SQLiteConnection(ConnectionString)) { try { previousConnectionState = connect.State; if (connect.State == ConnectionState.Closed) { connect.Open(); } SQLiteCommand command = new SQLiteCommand(sql, connect); SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); adapter.Fill(dt); } catch (Exception error) { System.Windows.Forms.MessageBox.Show(error.Message, "Ошибка при получении данных из базы", MessageBoxButtons.OK, MessageBoxIcon.Error); return null; } finally { if (previousConnectionState == ConnectionState.Closed) { connect.Close(); } } } return dt; }
protected DataTable loadEntsDtBySql(string sql) { DataTable ret = new DataTable("mytable"); try { open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.CommandText = sql; cmd.Connection = conn; SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ret); close(); return ret; } catch (Exception ex) { MessageBox.Show(ex.ToString()); return ret; } }
/// <summary> /// 执行数据库查询,返回DataSet对象 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="cmd">SqlCommand对象</param> /// <returns>DataSet对象</returns> public DataSet ExecuteDataSet(string connectionString,SQLiteCommand cmd) { SQLiteCommand myCmd = cmd; DataSet ds = new DataSet(); SQLiteConnection con = new SQLiteConnection(connectionString); SQLiteTransaction trans = null; PrepareCommand(myCmd, con, ref trans, false, myCmd.CommandType, myCmd.CommandText); try { SQLiteDataAdapter sda = new SQLiteDataAdapter(myCmd); sda.Fill(ds); } catch (Exception ex) { throw ex; } finally { if (myCmd.Connection != null) { if (myCmd.Connection.State == ConnectionState.Open) { myCmd.Connection.Close(); } } } return ds; }
/// <summary> /// 对SQLite数据库执行Insert操作,并返回rowID。 /// </summary> /// <param name="sql">要执行的Insert SQL语句</param> /// <param name="parameters">执行Insert语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns>RowID</returns> public static int ExcuteInsertReturnRowID(string sql, SQLiteParameter[] parameters = null) { int rowID = -1; int affectedRows; using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (DbTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql; if (parameters != null) { command.Parameters.AddRange(parameters); } affectedRows = command.ExecuteNonQuery(); } transaction.Commit(); } if (affectedRows == 0) { return rowID; } string getRowIDSql = "select last_insert_rowid()"; using (SQLiteCommand getRowIDCmd = new SQLiteCommand(getRowIDSql, connection)) { SQLiteDataAdapter adapter = new SQLiteDataAdapter(getRowIDCmd); DataTable data = new DataTable(); adapter.Fill(data); rowID = Convert.ToInt32(data.Rows[0][0]); } } return rowID; }
/// <summary> /// 执行一个查询语句,返回一个包含查询结果的DataTable /// </summary> /// <param name="sql">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns></returns> public int ExecuteDataTable(StringBuilder sql, SQLiteParameter[] parameters, ref DataTable dt) { try { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), connection)) { if (parameters != null) { command.Parameters.AddRange(parameters); } SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); adapter.Fill(dt); return 0; } } } catch (Exception ex) { return -1; throw new Exception(ex.Message); } }
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); }
/// <summary> /// 执行数据库查询,返回DataSet对象 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="commandText">执行语句或存储过程名</param> /// <param name="commandType">执行类型</param> /// <returns>DataSet对象</returns> public DataSet ExecuteDataSet(string connectionString, string commandText, CommandType commandType) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); DataSet ds = new DataSet(); SQLiteConnection con = new SQLiteConnection(connectionString); SQLiteCommand cmd = new SQLiteCommand(); SQLiteTransaction trans = null; PrepareCommand(cmd, con, ref trans, false, commandType, commandText); try { SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd); sda.Fill(ds); } catch (Exception ex) { throw ex; } finally { if (con != null) { if (con.State == ConnectionState.Open) { con.Close(); } } } return ds; }
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); } } }
public DataTable Select(string sql) { sl.SQLiteCommand cmd = this.con.CreateCommand(); cmd.CommandText = sql; sl.SQLiteDataAdapter da = new sl.SQLiteDataAdapter(cmd); DataTable dt = new DataTable(sql); da.Fill(dt); return(dt); }
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"); } }
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 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"; } }
private void Reporte_Load(object sender, EventArgs e) { CrystalReport3 objRpt = new CrystalReport3(); 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 + @"\EXCL.s3db ;Version=3;"; System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr); String Query1 = "SELECT * FROM Expediente Where Folio = '" + foliom + "'"; 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, "DataTable2"); // Setting data source of our report object objRpt.SetDataSource(Ds); // Binding the crystalReportViewer with our report object. this.crystalReportViewer1.ReportSource = objRpt; if (areaaf == "Frente") { appPath = Path.GetDirectoryName(Application.ExecutablePath); appPath = appPath + @"\body1.jpg"; } else if (areaaf == "Espalda") { appPath = Path.GetDirectoryName(Application.ExecutablePath); appPath = appPath + @"\body2.jpg"; } DsCC.Value = appPath; RpDatos.Add(DsCC); objRpt.DataDefinition.ParameterFields["Imagen"].ApplyCurrentValues(RpDatos); RpDatos.Clear(); paramField.HasCurrentValue = true; }
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; }
public void csYaziciBilgileriniGetir(int ModulID) { VeriTabaniniOlustur(); using (da = new System.Data.SQLite.SQLiteDataAdapter("select * from YaziciAyarlari where ModulID = @ModulID", sqlKonneksin)) { if (sqlKonneksin.State == ConnectionState.Closed) { sqlKonneksin.Open(); } da.SelectCommand.Parameters.Add("@ModulID", System.Data.DbType.Int32).Value = ModulID; dt = new DataTable(); da.Fill(dt); } }
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; } }
/// <summary> /// Recupera registro de publicacao pelo id /// </summary> /// <param name="id"></param> /// <returns></returns> public Publicacao Get(int id) { Publicacao retorno = null; using (var comm = new System.Data.SQLite.SQLiteCommand(sqliteConnection)) { comm.CommandText = "SELECT * FROM tbl_publicacao WHERE int_idapublicacao = " + id.ToString(); var adapter = new System.Data.SQLite.SQLiteDataAdapter(comm); var dataTable = new System.Data.DataTable(); adapter.Fill(dataTable); if (dataTable.Rows.Count > 0) { retorno = new Publicacao(dataTable.Rows[0]); } } return(retorno); }
/// <summary> /// Recupera registro de servidor pelo id /// </summary> /// <param name="id"></param> /// <returns></returns> public Servidor Get(int id) { Servidor retorno = null; using (var comm = new System.Data.SQLite.SQLiteCommand(sqliteConnection)) { comm.CommandText = "SELECT * FROM tbl_servidor WHERE int_idaservidor = '" + id + "'"; var adapter = new System.Data.SQLite.SQLiteDataAdapter(comm); var dataTable = new System.Data.DataTable(); adapter.Fill(dataTable); if (dataTable.Rows.Count > 0) { retorno = new Servidor(dataTable.Rows[0]); } } return(retorno); }
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"; } }
public static DataTable Consulta(string text) { try { var query = text; var dynamicParameters = new DynamicParameters(); using (var conn = new System.Data.SQLite.SQLiteConnection(LoadConnectionString())) { conn.Open(); using (var comm = new System.Data.SQLite.SQLiteCommand(conn)) { comm.CommandText = query; var adapter = new System.Data.SQLite.SQLiteDataAdapter(comm); var dataTable = new System.Data.DataTable(); adapter.Fill(dataTable); return(dataTable); /*foreach (System.Data.DataRow row in dataTable.Rows) * { * Console.WriteLine("Nome do Cliente: {0}", row["Nome"]); * } * * //var clienteId = comm.ExecuteScalar(); * using (var reader = comm.ExecuteReader()) * { * while (reader.Read()) * { * * } * }*/ } } } catch (Exception ex) { throw ex; } }
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; }
public List <Servidor> GetAll() { List <Servidor> retorno = new List <Servidor>(); using (var comm = new System.Data.SQLite.SQLiteCommand(sqliteConnection)) { comm.CommandText = "SELECT * FROM tbl_servidor ORDER BY vhr_nome"; var adapter = new System.Data.SQLite.SQLiteDataAdapter(comm); var dataTable = new System.Data.DataTable(); adapter.Fill(dataTable); if (dataTable.Rows.Count > 0) { foreach (DataRow row in dataTable.Rows) { retorno.Add(new Servidor(row)); } } } return(retorno); }
public static DataTable GetTableBySQL(string strSQL, bool bAddWithKey = false) { SQLiteConnection m_Connection = new SQLiteConnection(conStr); SQLiteCommand selectCommand = new SQLiteCommand(null, m_Connection) { CommandType = CommandType.Text, CommandText = strSQL }; System.Data.SQLite.SQLiteDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter(selectCommand); if (bAddWithKey) { adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; } DataTable dataTable = new DataTable(); adapter.Fill(dataTable); return(dataTable); }
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 BindGrid() // Esse Form de Consulta vai realizar a conexão com banco de dados e mostrar o que tem na tabela com os dados //inseridos no Form de cadastro. Basicamente é realizar um Select e associalos ao Fill para que possa ser compreendido //pelo Data Grid View , o Data Grid View é um recurso para manipular dados provenientes de um banco de dados. //Algumas adaptações via dll e Program.cs(https://stackoverflow.com/questions/3179028/mixed-mode-assembly-in-net-4) foram realizadas para //que o Data Grid View pudesse ser utilizado com Drive ODBC do SQLite. Devido a isso a sintaxe de conexão foi alterada...De todo modo funcionou { String connectionString = @"Data Source=C:\EncontreUmTrampo\cadastro.db"; System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand("select ID,Vaga,Empresa,Area,Local,Data,Etapa from Trampo"); cmd.Connection = conn; conn.Open(); cmd.ExecuteScalar(); System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd); System.Data.DataSet ds = new System.Data.DataSet(); da.Fill(ds); dataGridView1.DataSource = ds; dataGridView1.DataMember = ds.Tables[0].TableName; conn.Close(); }
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 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 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 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."); } } }