/// <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 Reporte_Load_1(object sender, EventArgs e)
{
Sistema_Caritas.CrystalReport1 objRpt = new Sistema_Caritas.CrystalReport1();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
String ConnStr = @"Data Source=" + appPath + @"\DBpinc.s3db ;Version=3;";
System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr);
//String Query1 = "SELECT * FROM Ventashechas Where NVenta = '" + nventas + "'";
String Query1 = "Select NVenta as NVenta, ArticuloID as ArticuloID, Nombrearticulo, Cantidad, Fecha as Fecha, Total as Total, Ventatotal as Ventatotal FROM (SELECT * FROM Ventashechas, Ventas Where Ventashechas.NVenta = Ventas.NVenta) Where NVenta = '" + nventas + "'";
System.Data.SQLite.SQLiteDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter(Query1, ConnStr);
DataSet Ds = new DataSet();
// here my_dt is the name of the DataTable which we
// created in the designer view.
adapter.Fill(Ds, "DataTable1");
// Setting data source of our report object
objRpt.SetDataSource(Ds);
// Binding the crystalReportViewer with our report object.
this.crystalReportViewer2.ReportSource = objRpt;
}
private void 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.");
}
}
}
