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(); } } }
/// <summary> /// Generic Method to return data from sqlite database type /// </summary> /// <param name="connStr"></param> /// <param name="sql"></param> /// <returns></returns> public static DataSet GetSqliteData(string connStr, string sql) { DataSet ds = new DataSet(); using (SQLiteConnection conn = new SQLiteConnection(connStr)) { conn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn); try { da.Fill(ds); } catch (Exception) { ds = new DataSet(); } finally { da.Dispose(); conn.Close(); conn.Dispose(); } } return ds; }
/// <summary> /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values /// </summary> /// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param> /// <param name="paramList">object[] array of parameter values</param> /// <returns></returns> public static DataSet ExecuteDataSet(string commandText, params object[] paramList) { using (SQLiteConnection conn = new SQLiteConnection(connStr)) { using (SQLiteCommand cmd = new SQLiteCommand(commandText, conn)) { try { conn.Open(); if (paramList != null) { AttachParameters(cmd, commandText, paramList); } DataSet ds = new DataSet(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Dispose(); conn.Close(); return ds; } catch (Exception ex) { cmd.Dispose(); conn.Close(); throw new Exception(ex.Message); } } } }
public DataSet ExecuteDataset(SQLiteCommand cmd) { SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); da.Dispose(); return ds; }
public static DataTable GetData(string strConn, string strSql, int startRecord, int maxRecords) { DataSet ds = new DataSet(); using (SQLiteConnection conn = new SQLiteConnection(strConn)) { conn.Open(); SQLiteCommand cmd = null; SQLiteDataAdapter da = null; try { cmd = new SQLiteCommand(strSql, conn); da = new SQLiteDataAdapter { SelectCommand = cmd }; da.Fill(ds, startRecord, maxRecords, "tb"); return ds.Tables["tb"]; } catch (Exception ex) { throw new Exception("error getting data " + ex.Message); } finally { if (da != null) { da.Dispose(); } if (cmd != null) { cmd.Dispose(); } conn.Close(); } } }
public bool ReadDataTable(ref DataTable dt, string sqlGetColumnsNamesAndTypes, List<SQL_Parameter> lSQL_Parameter, ref string csError) { ProgramDiagnostic.Diagnostic.Meassure("ReadDataTable(2) START", sqlGetColumnsNamesAndTypes); //SqlConnection Conn = new SqlConnection("Data Source=razvoj1;Initial Catalog=NOS_BIH;Persist Security Info=True;User ID=sa;Password=sa;"); if (DynSettings.bPreviewSQLBeforeExecution) { string new_sql = ""; bool bChanged = false; PreviewSQLCommand(sqlGetColumnsNamesAndTypes,null,ref new_sql,ref bChanged, "ReadDataTable"); if (bChanged) { sqlGetColumnsNamesAndTypes = new_sql; } } try { switch (m_DBType) { case eDBType.MYSQL: { MySqlDataAdapter adapter = new MySqlDataAdapter(); if (Connect_Batch(ref csError)) { MySqlCommand SqlCommandcommandGetColumnsNamesAndTypes = new MySqlCommand(sqlGetColumnsNamesAndTypes, m_con_MYSQL); if (lSQL_Parameter != null) { foreach (SQL_Parameter sqlPar in lSQL_Parameter) { if (sqlPar.size > 0) { SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(sqlPar.Name, sqlPar.MySQLdbType, sqlPar.size).Value = sqlPar.Value; } else { SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(new MySqlParameter(sqlPar.Name, sqlPar.Value)).Value = sqlPar.Value; } } } adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes; adapter.Fill(dt); adapter.Dispose(); SqlCommandcommandGetColumnsNamesAndTypes.Dispose(); Disconnect_Batch(); } else { MessageBox.Show(csError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } ProgramDiagnostic.Diagnostic.Meassure("ReadDataTable(2) END", null); return true; case eDBType.MSSQL: { SqlDataAdapter adapter = new SqlDataAdapter(); if (Connect_Batch(ref csError)) { SqlCommand SqlCommandcommandGetColumnsNamesAndTypes = new SqlCommand(sqlGetColumnsNamesAndTypes, m_con_MSSQL); if (lSQL_Parameter != null) { foreach (SQL_Parameter sqlPar in lSQL_Parameter) { if (sqlPar.size > 0) { SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(sqlPar.Name, sqlPar.dbType, sqlPar.size).Value = sqlPar.Value; } else { SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(new SqlParameter(sqlPar.Name, sqlPar.Value)).Value = sqlPar.Value; } } } adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes; adapter.Fill(dt); adapter.Dispose(); SqlCommandcommandGetColumnsNamesAndTypes.Dispose(); Disconnect_Batch(); } else { MessageBox.Show(csError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } ProgramDiagnostic.Diagnostic.Meassure("ReadDataTable(2) END", null); return true; case eDBType.SQLITE: { SQLiteDataAdapter adapter = new SQLiteDataAdapter(); if (Connect_Batch(ref csError)) { SQLiteCommand SqlCommandcommandGetColumnsNamesAndTypes = new SQLiteCommand(sqlGetColumnsNamesAndTypes, m_con_SQLite); if (lSQL_Parameter != null) { foreach (SQL_Parameter sqlPar in lSQL_Parameter) { if (sqlPar.size > 0) { SQLiteParameter mySQLiteParameter = new SQLiteParameter(sqlPar.Name, sqlPar.SQLiteDbType, sqlPar.size); mySQLiteParameter.Value = sqlPar.Value; SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(mySQLiteParameter); } else { SQLiteParameter mySQLiteParameter = new SQLiteParameter(sqlPar.Name, sqlPar.Value); mySQLiteParameter.Value = sqlPar.Value; SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(mySQLiteParameter); } } } adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes; adapter.Fill(dt); adapter.Dispose(); SqlCommandcommandGetColumnsNamesAndTypes.Dispose(); Disconnect_Batch(); } else { MessageBox.Show(csError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } ProgramDiagnostic.Diagnostic.Meassure("ReadDataTable(2) END", null); return true; default: MessageBox.Show("Error eSQLType in function: public bool ReadDataTable( ..)"); ProgramDiagnostic.Diagnostic.Meassure("ReadDataTable(2) END ERROR", null); return false; } } catch (Exception ex) { Disconnect(); csError = "Error:DBConnectionControl:ReadDataTable:Ex.Message:\r\n" + ex.Message; ShowDBErrorMessage(ex.Message, null, "ExecuteNonQuerySQL"); WriteLogTable(ex); ProgramDiagnostic.Diagnostic.Meassure("ReadDataTable(2) END ERROR", null); return false; } }
public bool ReadDataSet(ref DataSet ds, string sqlGetColumnsNamesAndTypes, ref string csError) { //SqlConnection Conn = new SqlConnection("Data Source=razvoj1;Initial Catalog=NOS_BIH;Persist Security Info=True;User ID=sa;Password=sa;"); ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet START", sqlGetColumnsNamesAndTypes); if (DynSettings.bPreviewSQLBeforeExecution) { string new_sql = ""; bool bChanged = false; PreviewSQLCommand(sqlGetColumnsNamesAndTypes, null, ref new_sql, ref bChanged, "ReadDataSet"); if (bChanged) { sqlGetColumnsNamesAndTypes = new_sql; } } try { switch (m_DBType) { case eDBType.MYSQL: { MySqlDataAdapter adapter = new MySqlDataAdapter(); string sError = ""; if (Connect_Batch(ref sError)) { MySqlCommand SqlCommandcommandGetColumnsNamesAndTypes = new MySqlCommand(sqlGetColumnsNamesAndTypes, m_con_MYSQL); adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes; adapter.Fill(ds); adapter.Dispose(); SqlCommandcommandGetColumnsNamesAndTypes.Dispose(); Disconnect_Batch(); } else { MessageBox.Show(sError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null); return true; case eDBType.MSSQL: { SqlDataAdapter adapter = new SqlDataAdapter(); string sError = ""; if (Connect_Batch(ref sError)) { SqlCommand SqlCommandcommandGetColumnsNamesAndTypes = new SqlCommand(sqlGetColumnsNamesAndTypes, m_con_MSSQL); adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes; adapter.Fill(ds); adapter.Dispose(); SqlCommandcommandGetColumnsNamesAndTypes.Dispose(); Disconnect_Batch(); } else { MessageBox.Show(sError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null); return true; case eDBType.SQLITE: { SQLiteDataAdapter adapter = new SQLiteDataAdapter(); string sError = ""; if (Connect_Batch(ref sError)) { SQLiteCommand SqlCommandcommandGetColumnsNamesAndTypes = new SQLiteCommand(sqlGetColumnsNamesAndTypes, m_con_SQLite); adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes; adapter.Fill(ds); adapter.Dispose(); SqlCommandcommandGetColumnsNamesAndTypes.Dispose(); Disconnect_Batch(); } else { MessageBox.Show(sError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null); return true; default: MessageBox.Show("ERROR eSQLType in function:public bool ReadDataSet(..)"); ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null); return false; } } catch (Exception ex) { //System.Windows.Forms.MessageBox.Show("SQL ERROR:" + ex.Message); Disconnect(); ShowDBErrorMessage(ex.Message, null, "ReadDataSet"); WriteLogTable(ex); ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null); return false; } }
public override DataTable GetDataTable(string sql) { SQLiteConnection con = new SQLiteConnection(ConnStr); SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, con); try { con.Open(); DataTable dt = new DataTable(); sda.Fill(dt); return dt; } catch { return null; } finally { sda.Dispose(); con.Close(); con.Dispose(); } }
public DataTable GetWellAllDescriptorValues(string TableName) { // cExtendedList ToReturn = new cExtendedList(); SQLiteCommand mycommand = new SQLiteCommand(_SQLiteConnection); // mycommand.CommandText = "SELECT *, \"" + DescType.GetName() + "\" FROM \"" + TableName + "\""; mycommand.CommandText = "SELECT * FROM \"" + TableName + "\""; SQLiteDataReader value = mycommand.ExecuteReader(); // value.Read(); //object[] myObjectArray = new object[value.FieldCount]; //while (value.Read()) //{ // value.GetValues(myObjectArray); // int a = 1; // //ToReturn.Add((double)myObjectArray[0]); //} SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(mycommand.CommandText, _SQLiteConnection); DataSet ds = new DataSet(); dataAdapter.Fill(ds); //Get the collection of rows from the DataSet // DataRowCollection dataRowCol = ds.Tables[0].Rows; DataTable TableToReturn = ds.Tables[0]; //Add the tables available in the DB to the combo box //foreach (DataRow dr in dataRowCol) //{ // tablecombobox.Items.Add(dr["name"]); //} /*int Pos = value.GetOrdinal(DescType.GetName()); while (value.Read()) { ToReturn.Add(value.GetFloat(Pos)); } */ dataAdapter.Dispose(); dataAdapter = null; mycommand.Dispose(); mycommand = null; value.Close(); value.Dispose(); value = null; return TableToReturn; }
/// <summary> /// Executes the given query on the database. /// </summary> /// <param name="sql">The query</param> /// <returns>Result from the database.</returns> public DataTable Query(string query, bool logerror = true) { try { IsConnect(); var adapter = new SQLiteDataAdapter(); var command = Connection.CreateCommand(); command.CommandText = query; adapter.SelectCommand = command; var table = new DataTable(); adapter.Fill(table); command.Dispose(); adapter.Dispose(); return table; } catch(SQLiteException s) { Crash(s, logerror); return null; } }
private void obtenerPersonas() { conexion.Open(); SQLiteCommand command = conexion.CreateCommand(); command.CommandText = "select * from persona"; SQLiteDataAdapter ad = new SQLiteDataAdapter(command); DataTable dt = new DataTable(); ad.Fill(dt); cbPersonas.DataSource = dt; cbPersonas.DisplayMember = "NOMBRE"; cbPersonas.ValueMember = "ID"; ad.Dispose(); command.Dispose(); conexion.Close(); }
/// <summary> /// Queries the DB for a single row of data /// Example. lookup item with id of 1 /// Returns a DataRow object /// </summary> /// <returns>DataRow</returns> private static DataRow QueryFetchRow(string sql) { SQLiteCommand query = Db.CreateCommand(); query.CommandText = sql; SQLiteDataAdapter da = new SQLiteDataAdapter(query); DataTable dt = new DataTable(); try { dt.BeginLoadData(); da.Fill(dt); dt.EndLoadData(); } catch (Exception ex) { Logging.Write("Exception in QueryFetchRow: " + ex); } finally { da.Dispose(); } return dt.Rows.Cast<DataRow>().FirstOrDefault(); /* * example usage: DataRow data = new DataRow(); row = QueryFetchRow("SELECT * FROM items WHERE id = 1"); Logging.Write("row: " + row["item_id"].ToString() + " " + row["item_name"].ToString() + "\n"); */ }
private void Botón_Cargar_Click(object sender, EventArgs e) { try { this.Cursor = Cursors.WaitCursor; if (!string.IsNullOrEmpty(ComboBox_Ruta.Text) && File.Exists(ComboBox_Ruta.Text)) { ComboBox_Fila.Items.Clear(); string Texto_Conexión = "Data Source=" + ComboBox_Ruta.Text + ";New=False;Version=3"; System.Data.SQLite.SQLiteConnection Conexión_SQL = new System.Data.SQLite.SQLiteConnection(Texto_Conexión); Conexión_SQL.Open(); string Texto_Origen = "sqlite_master"; string Texto_Comando = "Select name from sqlite_master;"; // Get the main names. System.Data.SQLite.SQLiteDataAdapter Adaptador_SQL = new System.Data.SQLite.SQLiteDataAdapter(Texto_Comando, Conexión_SQL); DataSet Set_Datos = new DataSet(); Set_Datos.RemotingFormat = SerializationFormat.Binary; Adaptador_SQL.Fill(Set_Datos, Texto_Origen); if (Set_Datos.Tables != null && Set_Datos.Tables.Count > 0) { foreach (DataTable Tabla in Set_Datos.Tables) { try { if (Tabla != null && Tabla.Columns != null && Tabla.Columns.Count > 0 && Tabla.Columns[0] != null && Tabla.Rows != null && Tabla.Rows.Count > 0) { foreach (DataRow Fila in Tabla.Rows) { try { if (Fila != null && !string.IsNullOrEmpty(Fila[0] as string)) { ComboBox_Fila.Items.Add(Fila[0] as string); } } catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; continue; } } } } catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; continue; } } } Set_Datos.Dispose(); Set_Datos = null; Adaptador_SQL.Dispose(); Adaptador_SQL = null; Conexión_SQL.Close(); Conexión_SQL.Dispose(); Conexión_SQL = null; /*string Texto_Conexión = "Data Source=" + ComboBox_Ruta.Text + ";New=False;Version=3"; * SQLiteConnection Conexión_SQL = new SQLiteConnection(Texto_Conexión); * Conexión_SQL.Open(); * string Texto_Origen = "sqlite_master"; * string Texto_Comando = "Select name from sqlite_master;"; // Get the main names. * SQLiteDataAdapter Adaptador_SQL = new SQLiteDataAdapter(Texto_Comando, Conexión_SQL); * DataSet Set_Datos = new DataSet(); * Set_Datos.RemotingFormat = SerializationFormat.Binary; * Adaptador_SQL.Fill(Set_Datos, Texto_Origen); * if (Set_Datos.Tables != null && Set_Datos.Tables.Count > 0) * { * foreach (DataTable Tabla in Set_Datos.Tables) * { * try * { * if (Tabla != null && * Tabla.Columns != null && * Tabla.Columns.Count > 0 && * Tabla.Columns[0] != null && * Tabla.Rows != null && * Tabla.Rows.Count > 0) * { * foreach (DataRow Fila in Tabla.Rows) * { * try * { * if (Fila != null && !string.IsNullOrEmpty(Fila[0] as string)) * { * ComboBox_Fila.Items.Add(Fila[0] as string); * } * } * catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; continue; } * } * } * } * catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; continue; } * } * } * Set_Datos.Dispose(); * Set_Datos = null; * Adaptador_SQL.Dispose(); * Adaptador_SQL = null; * Conexión_SQL.Close(); * Conexión_SQL.Dispose(); * Conexión_SQL = null;*/ if (ComboBox_Fila.Items.Count > 0) { ComboBox_Fila.SelectedIndex = 0; } } } catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; } finally { this.Cursor = Cursors.Default; } }
public bool UpdateTable(DataTable table, string tableName) { try { TableHelper.SetDefaultColumnValues(table); var con = CONNECTION.OpenCon(); var adapter = new SQLiteDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con); var cmd = new SQLiteCommandBuilder(adapter); adapter.Update(table); cmd.Dispose(); adapter.Dispose(); CONNECTION.CloseCon(con); return true; } catch (DBConcurrencyException cex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "UpdateTable DBConcurrencyError!", Ex = cex, }); return false; } catch(Exception ex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "UpdateTable Error!", Ex = ex, }); return false; } }
/// <summary> /// Executes the dataset from a populated Command object. /// </summary> /// <param name="cmd">Fully populated SQLiteCommand</param> /// <returns>DataSet</returns> public static DataSet ExecuteDataset(SQLiteCommand cmd) { if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open(); DataSet ds = new DataSet(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Connection.Close(); cmd.Dispose(); return ds; }
private void Button_Search(object sender, RoutedEventArgs e) { SQLiteCommand search = db.CreateCommand(); String column; switch (search_combobox.Text) { case "ISBN": column = "ISBN"; break; case "Title": column = "Title"; break; case "Author": column = "Author"; break; case "Publisher": column = "Publisher"; break; default: column = "OTHER"; break; } if (!column.Equals("OTHER")) { search.CommandText = String.Format("SELECT * FROM Book WHERE {0} LIKE @Value", column); search.Prepare(); search.Parameters.AddWithValue("@Value", String.Format("%{0}%", tb_search.Text)); } else { search.CommandText = String.Format("SELECT c.Course, b.Title, b.Author, b.Publisher, b.ISBN FROM CourseBook AS c INNER JOIN Book AS b ON c.ISBN == b.ISBN WHERE c.Course LIKE @Value"); search.Prepare(); search.Parameters.AddWithValue("@Value", String.Format("%{0}%", tb_search.Text)); } SQLiteDataAdapter da = new SQLiteDataAdapter(search); DataSet ds = new DataSet(); da.Fill(ds); dataGrid.DataContext = ds.Tables[0].DefaultView; search.Dispose(); ds.Dispose(); da.Dispose(); }
/// <summary> /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values /// </summary> /// <param name="connectionString">SQLite Connection string</param> /// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param> /// <param name="paramList">object[] array of parameter values</param> /// <returns></returns> public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList) { SQLiteConnection cn = new SQLiteConnection(connectionString); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; if (paramList != null) { AttachParameters(cmd,commandText, paramList); } DataSet ds = new DataSet(); if (cn.State == ConnectionState.Closed) cn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Dispose(); cn.Close(); return ds; }
///<overloads></overloads> /// <summary> /// Shortcut method to execute dataset from SQL Statement and object[] arrray of /// parameter values /// </summary> /// <param name="cn">Connection.</param> /// <param name="commandText">Command text.</param> /// <param name="paramList">Param list.</param> /// <returns></returns> public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList) { SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; if (paramList != null) { SQLiteParameterCollection parms = DeriveParameters(cmd, paramList); //foreach (SQLiteParameter p in parms) // cmd.Parameters.Add(p); } DataSet ds = new DataSet(); if (cn.State == ConnectionState.Closed) cn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Dispose(); cn.Close(); return ds; }