public DataRow[] drExecute(string FileData, string sSql) { DataRow[] datarows = null; SQLiteDataAdapter dataadapter = null; DataSet dataset = new DataSet(); DataTable datatable = new DataTable(); try { using (SQLiteConnection con = new SQLiteConnection()) { con.ConnectionString = @"Data Source=" + FileData + "; Version=3; New=False;"; con.Open(); using (SQLiteCommand sqlCommand = con.CreateCommand()) { dataadapter = new SQLiteDataAdapter(sSql, con); dataset.Reset(); dataadapter.Fill(dataset); datatable = dataset.Tables[0]; datarows = datatable.Select(); k = datarows.Count(); } con.Close(); } } catch(Exception ex) { // throw ex; datarows = null; } return datarows; }
public DataTable getCarTable() { sql_con.Open(); sql_cmd = sql_con.CreateCommand(); string CommandText = "SELECT * FROM voiture"; DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); DT = DS.Tables[0]; sql_con.Close(); return DT; }
public String getSecteur(int id) { sql_con.Open(); sql_cmd = sql_con.CreateCommand(); string CommandText = "SELECT nom FROM secteur WHERE id = " + id; DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); DT = DS.Tables[0]; sql_con.Close(); return (String)DT.Rows[0][0]; }
public int CountDoc(string query) { SetConnection(); sql_con.Open(); sql_cmd = sql_con.CreateCommand(); DB = new SQLiteDataAdapter(query, sql_con); DataSet DataS = new DataSet(); DataS.Reset(); DB.Fill(DataS); //int t = Convert.ToInt16(DataS.Tables[0].Rows[0][0]); return Convert.ToInt16(DataS.Tables[0].Rows[0][0]); }
public void SearchData(string qerty,int tab) { tabControl1.SelectedIndex = tab; SetConnection(); sql_con.Open(); sql_cmd = sql_con.CreateCommand(); DB = new SQLiteDataAdapter(qerty, sql_con); DS.Reset(); DB.Fill(DS); DT = DS.Tables[0]; dataGrid1.DataSource = DT; sql_con.Close(); RefreshButtons(); }
public DataTable Select(string queryString) { sw.Write("test"+queryString); try { lock(DataBase._conn) { SQLiteCommand Cmd = new SQLiteCommand(); SQLiteDataAdapter da = new SQLiteDataAdapter(queryString, DataBase._conn); DataTable dt = new DataTable(); if(da != null) { da.Fill(dt); return dt; }else{ return null; } } }catch(Exception e){ Debug.WriteLine("DataBase Select Problem: "+ e.Message); return null; } }
public void UpdateDataGrid(object sender, EventArgs e) { SetConnection(); sql_con.Open(); sql_cmd = sql_con.CreateCommand(); string cols = "select start_date, end_date, operator, product_no, product_desc, target, upper_var, lower_var, weight, pack_num from mains "; DateTime s_dt = mc_starttime.SelectionStart; DateTime e_dt = mc_endtime.SelectionEnd; string CommandText = cols + String.Format("where start_date>='{0}-{1}-{2} 00:00:00' and end_date<='{3}-{4}-{5} 23:59:59'", s_dt.Year,s_dt.Month.ToString("D2"),s_dt.Day.ToString("D2"),e_dt.Year,e_dt.Month.ToString("D2"),e_dt.Day.ToString("D2")); if (lb_oper.SelectedIndex >=0 && lb_oper.SelectedItem.ToString() != "*") { CommandText += String.Format(" and operator='{0}'", lb_oper.SelectedItem.ToString()); } if (lb_prod.SelectedIndex >= 0 && lb_prod.SelectedItem.ToString() != "*") { CommandText += String.Format(" and product_desc='{0}'", lb_prod.SelectedItem.ToString()); } if (lb_prodno.SelectedIndex >= 0 && lb_prodno.SelectedItem.ToString() != "*") { CommandText += String.Format(" and product_no='{0}'", lb_prodno.SelectedItem.ToString()); } DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); DT = DS.Tables[0]; double total_sum = 0; UInt32 total_pack = 0; foreach (DataRow dr in DT.Rows) { total_pack += UInt32.Parse(dr["pack_num"].ToString()); total_sum += Double.Parse(dr["weight"].ToString()); } this.dataGridView1.DataSource = DT; foreach (DataGridViewColumn dcol in dataGridView1.Columns) { dcol.HeaderText = StringResource.str(dcol.Name); dcol.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; } this.lbl_summary.Text = String.Format("{0}:{1}{6} , {2}:{3} , {4}:{5}{6}",StringResource.str("totalweight"),total_sum.ToString("F1"), StringResource.str("totalpacknum"),total_pack.ToString(),StringResource.str("avgweight"),(total_pack==0 ? "0" :(total_sum/total_pack).ToString("F1")),StringResource.str("gram")); sql_con.Close(); }
private void DistinctValue(string field,ListBox lb) { lb.Items.Clear(); lb.Items.Add("*"); SetConnection(); sql_con.Open(); sql_cmd = sql_con.CreateCommand(); string CommandText = "select distinct " + field + " from mains"; DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); DT = DS.Tables[0]; foreach (DataRow dr in DT.Rows) { lb.Items.Add(dr[0].ToString()); } sql_con.Close(); }
// populates the all items page with values from the database private void PopulateAllLists() { //CheckSQLTable(); allDataTable.Clear(); sqlite_conn.Open(); sqlite_cmd.CommandText = "SELECT ProductType, SerialNumber, Location, Availability, Owner, ETR, Comments FROM Items"; sqlite_data_adapter = new SQLiteDataAdapter(sqlite_cmd); sqlite_data_adapter.Fill(allDataTable); sqlite_conn.Close(); }
// fills the data from the database into a DataTable and loads it into the searchgrid private void FillData(string val) { searchDataTable.Clear(); sqlite_conn.Open(); sqlite_cmd.CommandText = val; sqlite_data_search_adapter = new SQLiteDataAdapter(sqlite_cmd); sqlite_data_search_adapter.Fill(searchDataTable); SearchDataGrid.ItemsSource = searchDataTable.DefaultView; sqlite_conn.Close(); }
public static void tbox_to_default(SQLiteConnection sql_con) { sql_con.Open(); SQLiteCommand sql_cmd = sql_con.CreateCommand(); string CommandText = "SELECT * FROM settings"; SQLiteDataAdapter DB = new SQLiteDataAdapter(CommandText, sql_con); GlobalVar.DS.Reset(); DB.Fill(DS); DataTable DT = DS.Tables[0]; for (int i = 0; i < 16; i++) { GlobalVar.defCars[i] = Convert.ToInt32(DT.Rows[i][0]); GlobalVar.defCoeff[i] = Convert.ToInt32(DT.Rows[i][1]); GlobalVar.defRebate[i] = Convert.ToInt32(DT.Rows[i][2]); } sql_con.Close(); }
public SQLiteCommandBuilder( SQLiteDataAdapter pAdapter ) { this.DataAdapter = pAdapter; }
public static DataTable LoadData(string table) { InitConnection(); string CommandText = "select * from " + table; DataAdapter dataAdapter = new SQLiteDataAdapter(CommandText, sqlConnection); var dataSet = new DataSet(); dataSet.Reset(); dataAdapter.Fill(dataSet); DisposeConnection(); return dataSet.Tables[0]; }
/// <summary> /// Private helper method that execute a SQLiteCommand (that returns a resultset) against the specified SQLiteTransaction and SQLiteConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// FillDataset(conn, trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new SQLiteParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SQLiteConnection</param> /// <param name="transaction">A valid SQLiteTransaction</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param> /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// </param> /// <param name="commandParameters">An array of SQLiteParamters used to execute the command</param> private static void FillDataset(SQLiteConnection connection, SQLiteTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params SQLiteParameter[] commandParameters) { if( connection == null ) throw new ArgumentNullException( "connection" ); if( dataSet == null ) throw new ArgumentNullException( "dataSet" ); // Create a command and prepare it for execution SQLiteCommand command = new SQLiteCommand(); bool mustCloseConnection = false; PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection ); // Create the DataAdapter & DataSet SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command); try { // Add the table mappings specified by the user if (tableNames != null && tableNames.Length > 0) { string tableName = "Table"; for (int index=0; index < tableNames.Length; index++) { if( tableNames[index] == null || tableNames[index].Length == 0 ) throw new ArgumentException( "The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames" ); dataAdapter.TableMappings.Add(tableName, tableNames[index]); tableName += (index + 1).ToString(); } } // Fill the DataSet using default values for DataTable names, etc dataAdapter.Fill(dataSet); // Detach the SQLiteParameters from the command object, so they can be used again command.Parameters.Clear(); if( mustCloseConnection ) connection.Close(); } finally { dataAdapter.Dispose(); } }
private void LoadData() { int tab=1; SetConnection(); sql_con.Open(); tab = tabControl1.SelectedIndex+1; if (tabControl1.SelectedIndex!= 5) { dataGrid2.Visible = false; dataGrid1.Visible = true; sql_cmd = sql_con.CreateCommand(); string CommandText = "select id,Тип,Модель,Размер,Цвет,Количество,ЦенаОптовая,ЦенаМин,ЦенаБазовая,Доп1,Доп2,Дата,ПоНакладной from korset where tab=" + tab.ToString(); DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); DT = DS.Tables[0]; //DataRow[] arr = new DataRow[3]; //DS.Tables[0].Rows.CopyTo(arr, 0); //arr[0].ItemArray[1].ToString(); dataGrid1.DataSource = DT; dataGrid1.Columns.Remove("id"); } else { dataGrid1.Visible = false; dataGrid2.Visible = true; sql_cmd = sql_con.CreateCommand(); string CommandText = "select No,Дата,Фирма,Сумма,Долг,Оплачено from document"; DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); DT = DS.Tables[0]; //DataRow[] arr = new DataRow[3]; //DS.Tables[0].Rows.CopyTo(arr, 0); //arr[0].ItemArray[1].ToString(); dataGrid2.DataSource = DT; } }
private void button1_Click(object sender, EventArgs e) { button2.Visible = true; // We use these three SQLite objects: SQLiteConnection sqlite_conn; SQLiteCommand sqlite_cmd; SQLiteDataReader sqlite_datareader; // create a new database connection: sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=False;Compress=True;"); // open the connection: sqlite_conn.Open(); // create a new SQL command: sqlite_cmd = sqlite_conn.CreateCommand(); try { // Let the SQLiteCommand object know our SQL-Query: sqlite_cmd.CommandText = "CREATE TABLE EMPLOYEE (EMP_ID varchar(20) PRIMARY KEY, EMP_NAME varchar(20) NOT NULL, JOB_TITLE varchar(20) NOT NULL);"; // Now lets execute the SQL ;D //try sqlite_cmd.ExecuteNonQuery(); } catch (SQLiteException e1) { sqlite_cmd.CommandText = "DELETE FROM EMPLOYEE"; sqlite_cmd.ExecuteNonQuery(); } finally { try { // Lets insert something into our new table: sqlite_cmd.CommandText = "INSERT INTO EMPLOYEE VALUES (2210311146, 'MONISH', 'DBA');"; // And execute this again ;D sqlite_cmd.ExecuteNonQuery(); // ...and inserting another line: sqlite_cmd.CommandText = "INSERT INTO EMPLOYEE VALUES (2210311130, 'VARSHA', 'DBA');"; // And execute this again ;D sqlite_cmd.ExecuteNonQuery(); // But how do we read something out of our table ? // First lets build a SQL-Query again: sqlite_cmd.CommandText = "SELECT * FROM EMPLOYEE;"; // Now the SQLiteCommand object can give us a DataReader-Object: sqlite_datareader = sqlite_cmd.ExecuteReader(); DataTable dt = new DataTable(); //CODE TO DISPLAY DATA ON A GRIDVIEW. SQLiteDataAdapter adp = new SQLiteDataAdapter(sqlite_cmd); adp.Fill(dt); dataGridView1.DataSource = dt; adp.Update(dt); sqlite_conn.Close(); } catch (SQLiteException se) { MessageBox.Show((se.ToString())); } } }
private XElement LoadDBConfig(string newcfg) { // SetConnection(); // sql_con.Open(); //retrieve all the names string CommandText = "select id,value from data where grp='" + newcfg + "' and tbl='" + this.sql_tbl + "'"; SQLiteDataAdapter DB2 = new SQLiteDataAdapter(CommandText, sql_con); DataSet DS2 = new DataSet(); DS2.Reset(); DB2.Fill(DS2); XElement xe = new XElement("Item"); foreach (string reg in regs) { if(reg != null) xe.Add(new XElement(reg, "0")); //sojodebug } foreach (DataRow dr in DS2.Tables[0].Rows) { if (regs.Contains(dr[0].ToString())) xe.SetElementValue(dr[0].ToString(), dr[1].ToString()); } //sql_con.Close(); return xe; }
/// <summary> /// Executes the respective command for each inserted, updated, or deleted row in the DataSet. /// </summary> /// <remarks> /// e.g.: /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); /// </remarks> /// <param name="insertCommand">A valid transact-SQL statement to insert new records into the data source</param> /// <param name="deleteCommand">A valid transact-SQL statement to delete records from the data source</param> /// <param name="updateCommand">A valid transact-SQL statement used to update records in the data source</param> /// <param name="dataSet">The DataSet used to update the data source</param> /// <param name="tableName">The DataTable used to update the data source.</param> public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName) { if( insertCommand == null ) throw new ArgumentNullException( "insertCommand" ); if( deleteCommand == null ) throw new ArgumentNullException( "deleteCommand" ); if( updateCommand == null ) throw new ArgumentNullException( "updateCommand" ); if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( "tableName" ); // Create a SQLiteDataAdapter, and dispose of it after we are done SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(); try { // Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update (dataSet,tableName); // Commit all the changes made to the DataSet dataSet.AcceptChanges(); } catch (SQLiteException E) {string strError=E.Message;} finally{dataAdapter.Dispose();} }
/// <summary> /// Execute a SQLiteCommand (that returns a resultset) against the specified SQLiteTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new SQLiteParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid SQLiteTransaction</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">An array of SQLiteParamters used to execute the command</param> /// <returns>An XmlReader containing the resultset generated by the command</returns> public static string ExecuteXml(SQLiteTransaction transaction, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters) { if( transaction == null ) throw new ArgumentNullException( "transaction" ); if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" ); // // Create a command and prepare it for execution SQLiteCommand cmd = new SQLiteCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, (SQLiteConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet SQLiteDataAdapter obj_Adapter =new SQLiteDataAdapter (cmd); DataSet ds=new DataSet(); ds.Locale =CultureInfo.InvariantCulture; obj_Adapter.Fill(ds); // Detach the SQLiteParameters from the command object, so they can be used again cmd.Parameters.Clear(); string retval= ds.GetXml(); ds.Clear(); obj_Adapter.Dispose (); return retval; }
/// <summary> /// Execute a SQLiteCommand (that returns a resultset) against the specified SQLiteConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new SQLiteParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SQLiteConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">An array of SQLiteParamters used to execute the command</param> /// <returns>An string containing the resultset generated by the command</returns> public static string ExecuteXml(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters) { if( connection == null ) throw new ArgumentNullException( "connection" ); bool mustCloseConnection = false; // Create a command and prepare it for execution SQLiteCommand cmd = new SQLiteCommand(); try { PrepareCommand(cmd, connection, (SQLiteTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection ); // Create the DataAdapter & DataSet SQLiteDataAdapter obj_Adapter =new SQLiteDataAdapter (cmd); DataSet ds=new DataSet(); ds.Locale =CultureInfo.InvariantCulture; obj_Adapter.Fill(ds); // Detach the SQLiteParameters from the command object, so they can be used again cmd.Parameters.Clear(); string retval= ds.GetXml(); ds.Clear(); obj_Adapter.Dispose (); return retval; } catch { if( mustCloseConnection ) connection.Close(); throw; } }
/// <summary> /// Execute a SQLiteCommand (that returns a resultset) against the specified SQLiteTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new SQLiteParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid SQLiteTransaction</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of SQLiteParamters used to execute the command</param> /// <returns>A dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(SQLiteTransaction transaction, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters) { if( transaction == null ) throw new ArgumentNullException( "transaction" ); if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" ); // Create a command and prepare it for execution SQLiteCommand cmd = new SQLiteCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, (SQLiteConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet //using( SQLiteDataAdapter da = new SQLiteDataAdapter(cmd) ) SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); DataSet ds = new DataSet(); ds.Locale =CultureInfo.InvariantCulture; // Fill the DataSet using default values for DataTable names, etc da.Fill(ds); // Detach the SQLiteParameters from the command object, so they can be used again cmd.Parameters.Clear(); // Return the dataset return ds; }
private bool LoadConfigFromFile() { try { string CommandText; SetConnection(); sql_con.Open(); //retrieve current configure CommandText = "select id,value from data where grp='"+this.sql_grp+"' and tbl='" + this.sql_tbl + "'"; DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); foreach (DataRow dr in DS.Tables[0].Rows) { curr_conf[dr[0].ToString()] = dr[1].ToString(); } sql_con.Close(); return true; } catch (System.Exception e) { MsgDlg.Show(e.Message); return false; } }
public bool LoadConfigFromFile() { try { string CommandText; SetConnection(); sql_con.Open(); //retrieve all the regs CommandText = "select distinct id from data where tbl='" + this.sql_tbl + "'"; DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); regs = new string[DS.Tables[0].Rows.Count]; int i = 0; foreach (DataRow dr in DS.Tables[0].Rows) { if ((dr[0].ToString() != "current") && (dr[0].ToString() != "null")) regs[i++] = dr[0].ToString(); } //retrieve all the names CommandText = "select distinct grp from data where tbl='" + this.sql_tbl+"'"; DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); XElement xe = new XElement("Item"); foreach (DataRow dr in DS.Tables[0].Rows) { if (dr[0].ToString() != "current") curr_conf[dr[0].ToString()] = LoadDBConfig(dr[0].ToString()); } //retrieve current configure CommandText = "select value from data where grp='current' and tbl='"+this.sql_tbl+"'"; DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); if (DS.Tables[0].Rows.Count > 0) cfg_name = DS.Tables[0].Rows[0][0].ToString(); else cfg_name = "default"; sql_con.Close(); return true; } catch (System.Exception e) { MessageBox.Show(e.Message); return false; } }
private static DataTable ExecuteDataTable(string text, params object[] data) { try { command.CommandText = text; command.Parameters.Clear(); for (int i = 0; i < data.Length; i++) command.Parameters.Add("@" + i.ToString(), data[0]); SQLiteDataAdapter da = new SQLiteDataAdapter(command); DataTable dt = new DataTable("DataTable"); da.Fill(dt); return dt; } catch (Exception exc) { IDE.Debug("ExecuteNonQuery!!! ", exc, text); } return null; }
private void LoadData() { SetConnection(); sql_con.Open(); sql_cmd = sql_con.CreateCommand(); string CommandText = "select id, Тип,Модель,Размер,Количество,ЦенаОптовая,ЦенаМин,ЦенаБазовая,Доп1,Доп2,Дата,ПоНакладной from korset"; DB = new SQLiteDataAdapter(CommandText, sql_con); DS.Reset(); DB.Fill(DS); DT = DS.Tables[0]; //DataRow[] arr = new DataRow[3]; //DS.Tables[0].Rows.CopyTo(arr, 0); //arr[0].ItemArray[1].ToString(); dataGridView1.DataSource = DT; sql_con.Close(); }