/// <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); } }
/// <summary> /// 检索 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public static DataTable GetTable(string strSql) { DataTable table = new DataTable(); SQLiteDataAdapter adapter = new SQLiteDataAdapter(strSql, Conn); adapter.Fill(table); return table; }
/// <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; }
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; } }
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; } }
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(); }
/// <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; }
//------------------------------------------------------------------------------------------------------------------- //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 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 bool Update(int orderID, string pymntID, string state, string amount, string description, string updatedAt) { bool isSuccess = false; int rowsAffacted = 0; StringBuilder sqliteQueryUpdate = new StringBuilder(); sqliteQueryUpdate.Append("UPDATE orders "); sqliteQueryUpdate.Append("SET "); sqliteQueryUpdate.Append("payment_id = @payment_id, "); sqliteQueryUpdate.Append("state = @state, "); sqliteQueryUpdate.Append("amount = @amount, "); sqliteQueryUpdate.Append("description = @description, "); sqliteQueryUpdate.Append("updated_at = @updated_at "); sqliteQueryUpdate.Append("WHERE "); sqliteQueryUpdate.Append("id = @id"); SQLiteDataAdapter sqliteDataAdapterUpdate = new SQLiteDataAdapter(); sqliteDataAdapterUpdate.UpdateCommand = new SQLiteCommand(); sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@payment_id", pymntID); sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@state", state); sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@amount", amount); sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@description", description); sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@updated_at", updatedAt); sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@id", orderID); dataAccessObject = new DataAccessLayer(); rowsAffacted = dataAccessObject.Update(sqliteQueryUpdate.ToString(), sqliteDataAdapterUpdate); if (rowsAffacted > 0) { isSuccess = true; } return isSuccess; }
/// <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; }
/// <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; }
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 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 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 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."); } }
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; }
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; }
/// <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; }
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(); }
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; }
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); }
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 DataTable GetUser(string email) { DataTable datTable = new DataTable(); StringBuilder sqliteQuerySelect = new StringBuilder(); sqliteQuerySelect.Append("SELECT "); sqliteQuerySelect.Append("id, "); sqliteQuerySelect.Append("email, "); sqliteQuerySelect.Append("encrypted_password, "); sqliteQuerySelect.Append("sign_in_count, "); sqliteQuerySelect.Append("current_sign_in_at, "); sqliteQuerySelect.Append("last_sign_in_at, "); sqliteQuerySelect.Append("last_sign_in_ip, "); sqliteQuerySelect.Append("created_at, "); sqliteQuerySelect.Append("updated_at, "); sqliteQuerySelect.Append("credit_card_id, "); sqliteQuerySelect.Append("credit_card_description "); sqliteQuerySelect.Append("FROM users "); sqliteQuerySelect.Append("WHERE email = @email"); SQLiteDataAdapter sqliteDataAdapterSelect = new SQLiteDataAdapter(); sqliteDataAdapterSelect.SelectCommand = new SQLiteCommand(); sqliteDataAdapterSelect.SelectCommand.Parameters.AddWithValue("@email", email); dataAccessObject = new DataAccessLayer(); datTable = dataAccessObject.Select(sqliteQuerySelect.ToString(), sqliteDataAdapterSelect); return datTable; }
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; }
/// <summary> /// 根据配置文件中所配置的数据库类型 /// 来创建相应数据库适配器对象 /// </summary> /// <returns></returns> public static IDbDataAdapter CreateDataAdapter() { IDbDataAdapter adapter = null; switch (DbHelper.DbType) { case DatabaseType.SqlServer: adapter = new SqlDataAdapter(); break; case DatabaseType.Oracle: adapter = new OracleDataAdapter(); break; case DatabaseType.MySql: adapter = new MySqlDataAdapter(); break; case DatabaseType.Access: adapter = new OleDbDataAdapter(); break; case DatabaseType.SQLite: adapter = new SQLiteDataAdapter(); break; default: throw new Exception("数据库类型目前不支持!"); } return adapter; }
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; }
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; } } }
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; } }
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); }
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(); } }
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 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(); } } }
private void textBox2_TextChanged(object sender, EventArgs e) { string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Almacen Where ArticuloID = '" + textBox2.Text + "'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); label6.Text = ""; label5.Text = ""; //textBox3.Text = "0"; label10.Text = ""; label12.Text = ""; for (int i = 0; i < dTable.Rows.Count; i++) { DataRow Row = dTable.Rows[i]; label6.Text = Row["Cantidadexistencia"].ToString(); label5.Text = Row["Precioventa"].ToString(); label12.Text = Row["Nombrearticulo"].ToString(); } }
private void button1_Click(object sender, EventArgs e) { if (dataGridView1.Rows.Count != 0) { DialogResult resultado = MessageBox.Show("Esta seguro que desea eliminar toda la venta?", "Seguro?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation); if (resultado == DialogResult.Yes) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "Delete From Ventas Where [NVenta] = " + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + ""; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); cmd.CommandText = "Delete From Ventashechas Where [NVenta] = " + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + ""; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db"; dataGridView1.Left = 247; //create the database query string query = "Select * From Ventas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } } else { MessageBox.Show("Tiene que elegir una venta para eliminarlo"); } }
private void button3_Click(object sender, EventArgs e) { if (comboBox1.SelectedIndex == 1) { string numero; try { numero = dataGridView2.SelectedRows[0].Cells[0].Value.ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para borrar cmd.CommandText = "DELETE FROM Salidas WHERE [Numero] = " + numero; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Salida eliminada exitosamente"); appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "select * from Salidas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView2.DataSource = bSource; dAdapter.Update(dTable); } catch { MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
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 textBox1_TextChanged(object sender, EventArgs e) { if (textBox1.Text != "") { string appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBUC.s3db"; string query = ""; query = "SELECT Nombre,Usuario,TipoDeUsuario from Usuarios WHERE Usuario LIKE '%" + textBox1.Text + "%'"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } else { string appPath = Path.GetDirectoryName(Application.ExecutablePath); //create the connection string string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBUC.s3db"; string query = ""; query = "SELECT Nombre,Usuario,TipoDeUsuario from Usuarios"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); BindingSource bSource = new BindingSource(); bSource.DataSource = dTable; dataGridView1.DataSource = bSource; dAdapter.Update(dTable); } }
private void button1_Click(object sender, EventArgs e) { ///create the connection string string appPath2 = Path.GetDirectoryName(Application.ExecutablePath); ///create the connection string string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Usuarios"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); bool usuarioexistente = false; bool admin = false; for (int i = 0; i < dTable.Rows.Count; i++) { DataRow Row = dTable.Rows[i]; if (Row["Usuario"].ToString() == textBox1.Text && Row["Contrasena"].ToString() == textBox2.Text) { usuarioexistente = true; if (Row["TipoDeUsuario"].ToString() == "Administrador") { admin = true; AdminAvailable(true); this.Close(); } break; } } if (usuarioexistente == false) { MessageBox.Show("Usuario o contraseña incorrecta"); } else if (usuarioexistente == true && admin == false) { MessageBox.Show("La cuenta utilizada no es de un administrador"); } }
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; }
/// <summary> /// Disposes a TableAdapter generated by SQLite Designer /// </summary> /// <param name="disposing"></param> /// <param name="adapter"></param> /// <param name="commandCollection"></param> /// <remarks>You must dispose all the command, /// otherwise the file remains locked and cannot be accessed /// (for example, for reading or deletion)</remarks> public static void DisposeTableAdapter( bool disposing, System.Data.SQLite.SQLiteDataAdapter adapter, IEnumerable <System.Data.SQLite.SQLiteCommand> commandCollection) { if (disposing) { DisposeSQLiteTableAdapter(adapter); foreach (SQLiteCommand currentCommand in commandCollection) { currentCommand.Dispose(); } } }
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); } }
/// <summary> /// 创建Database对象 /// </summary> public static Database CreateDatabase(string strconn, DBType DBType) { //strconn = UBase.DES_Decrypt(strconn);//数据库连接字符串,DES解密 #region Sqllite if (DBType == DBType.SqlLite) { strconn = string.Format("Data Source={0}", strconn); System.Data.SQLite.SQLiteDataAdapter mysqlda = new System.Data.SQLite.SQLiteDataAdapter(); mysqlda.SelectCommand = new System.Data.SQLite.SQLiteCommand(); mysqlda.SelectCommand.Connection = new SQLiteConnection(strconn); return(new Database(mysqlda, DBType.SqlLite)); } #endregion #region MYSQL if (DBType == DBType.MySql) { //Host=127.0.0.1;UserName=root;Password=123;Database=huizhan;Port=4002;CharSet=utf8;Allow Zero Datetime=true; MySql.Data.MySqlClient.MySqlDataAdapter mysqlda = new MySql.Data.MySqlClient.MySqlDataAdapter(); mysqlda.SelectCommand = new MySql.Data.MySqlClient.MySqlCommand(); mysqlda.SelectCommand.Connection = new MySql.Data.MySqlClient.MySqlConnection(strconn); return(new Database(mysqlda, DBType.MySql)); } #endregion #region MSSQL if (DBType == DBType.MSSQL) { SqlDataAdapter sqlda = new SqlDataAdapter(); sqlda.SelectCommand = new SqlCommand(); sqlda.SelectCommand.Connection = new SqlConnection(strconn); return(new Database(sqlda, DBType.MSSQL)); } #endregion #region Oracle if (DBType == DBType.Oracle) { MySql.Data.MySqlClient.MySqlDataAdapter mysqlda = new MySql.Data.MySqlClient.MySqlDataAdapter(); mysqlda.SelectCommand = new MySql.Data.MySqlClient.MySqlCommand(); mysqlda.SelectCommand.Connection = new MySql.Data.MySqlClient.MySqlConnection(strconn); return(new Database(mysqlda, DBType.MySql)); } #endregion return(null); }
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); }
public void YaziciBilgileriniKaydet(int ModulID, DataTable dtYaziciBil) { using (da = new System.Data.SQLite.SQLiteDataAdapter("select * from YaziciAyarlari where ModulID = @ModulID", sqlKonneksin)) { da.InsertCommand = new System.Data.SQLite.SQLiteCommand(@"insert into YaziciAyarlari (RaporDizaynID, ModulID, YaziciAdi, KagitKaynagi, KagitKaynagiIndex --, RenkliMi, KagitTipi, CiftTarafliMi , Aciklama) values (@RaporDizaynID, @ModulID, @YaziciAdi, @KagitKaynagi, @KagitKaynagiIndex --, @RenkliMi, @KagitTipi, @CiftTarafliMi , @Aciklama)", sqlKonneksin); da.InsertCommand.Parameters.Add("@ModulID", System.Data.DbType.Int16).Value = ModulID; da.InsertCommand.Parameters.Add("@RaporDizaynID", System.Data.DbType.String, 0, "RaporDizaynID"); da.InsertCommand.Parameters.Add("@YaziciAdi", System.Data.DbType.String, 0, "YaziciAdi"); da.InsertCommand.Parameters.Add("@KagitKaynagi", System.Data.DbType.String, 0, "KagitKaynagi"); da.InsertCommand.Parameters.Add("@KagitKaynagiIndex", System.Data.DbType.Int16, 0, "KagitKaynagiIndex"); //da.InsertCommand.Parameters.Add("@RenkliMi", System.Data.DbType.Boolean, 0, "RenkliMi"); //da.InsertCommand.Parameters.Add("@KagitTipi", System.Data.DbType.String, 0, "KagitTipi"); //da.InsertCommand.Parameters.Add("@CiftTarafliMi", System.Data.DbType.Int16, 0, "CiftTarafliMi"); da.InsertCommand.Parameters.Add("@Aciklama", System.Data.DbType.String, 0, "Aciklama"); da.UpdateCommand = new System.Data.SQLite.SQLiteCommand(@"update YaziciAyarlari set RaporDizaynID = @RaporDizaynID, ModulID = @ModulID, YaziciAdi = @YaziciAdi, KagitKaynagi = @KagitKaynagi, KagitKaynagiIndex = @KagitKaynagiIndex --, RenkliMi = @RenkliMi, KagitTipi = @KagitTipi, CiftTarafliMi = @CiftTarafliMi , Aciklama = @Aciklama where RaporDizaynID = @RaporDizaynID", sqlKonneksin); da.UpdateCommand.Parameters.Add("@ID", System.Data.DbType.Int32, 0, "ID"); da.UpdateCommand.Parameters.Add("@ModulID", System.Data.DbType.Int16).Value = ModulID; da.UpdateCommand.Parameters.Add("@RaporDizaynID", System.Data.DbType.String, 0, "RaporDizaynID"); da.UpdateCommand.Parameters.Add("@YaziciAdi", System.Data.DbType.String, 0, "YaziciAdi"); da.UpdateCommand.Parameters.Add("@KagitKaynagi", System.Data.DbType.String, 0, "KagitKaynagi"); da.UpdateCommand.Parameters.Add("@KagitKaynagiIndex", System.Data.DbType.Int16, 0, "KagitKaynagiIndex"); //da.UpdateCommand.Parameters.Add("@RenkliMi", System.Data.DbType.Boolean, 0, "RenkliMi"); //da.UpdateCommand.Parameters.Add("@KagitTipi", System.Data.DbType.String, 0, "KagitTipi"); //da.UpdateCommand.Parameters.Add("@CiftTarafliMi", System.Data.DbType.Int16, 0, "CiftTarafliMi"); da.UpdateCommand.Parameters.Add("@Aciklama", System.Data.DbType.String, 0, "Aciklama"); da.DeleteCommand = new System.Data.SQLite.SQLiteCommand("delete from YaziciAyarlari where RaporDizaynID = @RaporDizaynID", sqlKonneksin); da.DeleteCommand.Parameters.Add("@RaporDizaynID", System.Data.DbType.Int32, 0, "RaporDizaynID"); da.Update(dtYaziciBil); } }
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 (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."); } } }