private void TestForm_Shown(object sender, EventArgs e)
{
IDbAdapter adapter = new SQLiteDataAdapter();
DataService = new DataService {Adapter = adapter};
Builder = new SQLBuilder(DataService.Adapter);
Builder.CreateStructure();
List<Equipment> users = DataService.GetAllForModel(ModelExtensions.CreateInstance<Equipment>);
Grid.DataSource = users;
}
internal static void fill(string queryString, DataTable toReturn)
{
using (var conn = new SQLiteConnection(SQLite.connString)) {
conn.Open();
using (SQLiteDataAdapter da = new SQLiteDataAdapter(queryString, conn)) {
da.Fill(toReturn);
}
conn.Close();
}
}
private void getAcntPeriod()
{
SQLiteCommand sqLiteCommand1 = new SQLiteCommand();
sqLiteCommand1.CommandText = @"Select acnt_period, StatementDate from configuration";
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
SQLiteDataReader dr = sqLiteCommand1.ExecuteReader();
dr.Read();
this.textBox1.Text = dr.GetInt32(0).ToString();
this.textBox4.Text = getFridaysDate(dr.GetInt32(0)).ToString("dd-MMM-yyyy");
sqLiteConnection1.Close();
sqLiteCommand1.CommandText = @"select t_week_id from acnt_period where t_date = date('now','localtime')";
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
SQLiteDataReader dr1 = sqLiteCommand1.ExecuteReader();
dr1.Read();
this.textBox6.Text = dr1.GetInt32(0).ToString();
this.textBox5.Text = getFridaysDate(dr1.GetInt32(0)).ToString("dd-MMM-yyyy");
sqLiteConnection1.Close();
string cmd = @"select strftime('%d-%m-%Y',t_timestamp) as '{0}',
t_week_id as '{1}' ,count(*) as '{2}'
from invoice_header h, customer_trans t, configuration c
where invoice_number = t_src_id and t_week_id > (acnt_period - 3)
group by Date(t_timestamp), t_week_id";
string sqlcmd = String.Format(cmd, "Entry Date","Accounting Period", "Invoices");
sqLiteConnection1.Open();
SQLiteDataAdapter da = new SQLiteDataAdapter(sqlcmd.ToString(), sqLiteConnection1);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader;
dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader;
dataGridView1.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader;
dataGridView1.Width = dataGridView1.Columns[0].Width + dataGridView1.Columns[1].Width + dataGridView1.Columns[2].Width + 5;
dataGridView1.ReadOnly = true;
sqLiteConnection1.Close();
}
/// <summary>
/// 执行一个查询语句，返回一个包含查询结果的DataTable
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
{
try
{
OpenConnection();
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
data.TableName = "datatable";
adapter.Fill(data);
return(data);
}
}
finally
{
CloseConnection();
}
}
/// <summary>
/// 获取数据
/// </summary>
/// <param name="filePath"></param>
/// <param name="command"></param>
/// <param name="tablename"></param>
/// <returns></returns>
public static DataSet GetDs(string filePath, string command, string tablename = "")
{
DataSet dataSet = new DataSet();
SQLiteConnection cn = new SQLiteConnection("data source=" + filePath);
cn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(command, cn))
{
using (SQLiteDataAdapter sQLiteDataAdapter = new SQLiteDataAdapter(cmd))
{
if (string.Empty.Equals(tablename))
{
sQLiteDataAdapter.Fill(dataSet);
}
else
{
sQLiteDataAdapter.Fill(dataSet, tablename);
}
}
}
cn.Close();
return(dataSet);
}
public void FillDataGrid()
{
try
{
SQLiteConnection conn;
conn = new SQLiteConnection(@"Data Source=D:\Software Testing and QA\Database\MainDatabase.db;Version=3;");
conn.Open();
string sql = "SELECT * FROM student;";
SQLiteCommand command = new SQLiteCommand(sql, conn);
command.ExecuteNonQuery();
SQLiteDataAdapter dataAdp = new SQLiteDataAdapter(command);
DataTable dt = new DataTable("student");
dataAdp.Fill(dt);
student_list.ItemsSource = dt.DefaultView;
dataAdp.Update(dt);
conn.Close();
}
catch (Exception ex)
{
string msg = ex.GetType().Name + " : " + ex.Message;
MessageBox.Show(msg);
}
}
private void loadSellerInfo()
{
using (SQLiteConnection connection = new SQLiteConnection(Tools.DataTools.getConnectionString))
{
using (SQLiteCommand sqliteCommand = new SQLiteCommand("Select * From OrderedItems", connection))
{
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqliteCommand))
{
connection.Open();
DataRow dRow;
DataSet dSet = new DataSet();
adapter.Fill(dSet);
dRow = dSet.Tables[0].Rows[Form1.SelectedIndex];
txtbxsellerName.Text = dRow[25].ToString();
txtbxsellerTrackService.Text = dRow[26].ToString();
txtbxExtraInfo.Text = dRow[27].ToString();
}
}
connection.Close();
}
}
public DataTable selectQuery(string query)
{
SQLiteDataAdapter ad;
DataTable dt = new DataTable();
try
{
SQLiteCommand cmd;
sqlite.Open();
sqlite.EnableExtensions(true);
sqlite.LoadExtension("SQLite.Interop.dll", "sqlite3_json_init");
cmd = sqlite.CreateCommand();
cmd.CommandText = query;
ad = new SQLiteDataAdapter(cmd);
ad.Fill(dt);
}
catch (SQLiteException ex)
{
Console.WriteLine("SQL Error: " + ex.Message);
}
sqlite.Close();
return(dt);
}
public static DataTable listarConvenio(string filtro = "")
{
//Estrutura da tabela
DataTable dataTable = new DataTable();
try
{
SQLiteCommand cmd = new SQLiteCommand(
string.Format("select * from vw_convenio {0}", filtro), BancoDados.ConectarBD());
//Intermediario recebe a respota do comandos sql enviado
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd);
//Preencher com a estrutura do select enviado com as tuplas
dataAdapter.Fill(dataTable);
}
catch (SQLiteException erro)
{
Funcao.GravarLog("BancoDados.listarMovimento(string filtro = '')() : " + erro.Message.ToString());
}
return(dataTable);
}
public DataTable Source()
{
using (var connection = new SQLiteConnection("Data Source=recibo"))
{
connection.Open();
try
{
SQLiteCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT usuario FROM usuario";
SQLiteDataAdapter adap = new SQLiteDataAdapter(cmd);
ds.Clear();
adap.Fill(ds);
dt = ds.Tables[0];
connection.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return(dt);
}
}
/// <summary>
/// 查询数据集
/// </summary>
/// <param name="cn">连接.</param>
/// <param name="commandText">查询语句.</param>
/// <param name="paramList">object参数列表.</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText, params object[] paramList)
{
SQLiteCommand cmd = Conn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
AttachParameters(cmd, commandText, paramList);
}
DataSet ds = new DataSet();
if (Conn.State == ConnectionState.Closed)
{
Conn.Open();
}
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
Conn.Close();
return(ds);
}
public Int64 GetUserNoByFileNo(Int64 fileNo)
{
using (var cn = GetOpenConnection())
{
var sql = @"
select UP.UserNo
from File as F
join SubmissionDetail as SD on F.FileNo = SD.FileNo
join UserProfile as UP on SD.UserNo = UP.UserNo
where F.FileNo = @FileNo";
SQLiteDataAdapter da = new SQLiteDataAdapter(sql, cn);
da.SelectCommand.Parameters.AddWithValue("@FileNo", fileNo);
DataTable dt = new DataTable();
da.Fill(dt);
cn.Close();
var result = dt.Rows[0].Field <Int64>("UserNo");
return(result);
}
}
public static int GetSeqConvenio()
{
int retorno = -1;
try
{
string script = "SELECT seq FROM sqlite_sequence WHERE name = 'convenio'";
StringBuilder sql;
sql = new StringBuilder(script);
SQLiteDataAdapter da = new SQLiteDataAdapter(sql.ToString(), BancoDados.ConectarBD());
DataSet ds = new DataSet();
da.Fill(ds, "param");
var qtdLinhas = ds.Tables[0].Rows.Count.ToString();
retorno = int.Parse(qtdLinhas) > 0 ? int.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString()) + 1 : 1;
}
catch (SQLiteException erro)
{
retorno = -1;
Funcao.GravarLog("BancoDados.GetSeqUsuario() : " + erro.Message.ToString());
}
return(retorno);
}
public DataTable ReadTable(string Query)
{
DataTable dTable = new DataTable();
if (Connection.State != ConnectionState.Open)
{
ErrorMsg = "Open Database";
return(null);
}
try
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter(Query, Connection);
adapter.Fill(dTable);
}
catch (SQLiteException ex)
{
ErrorMsg = ex.Message;
return(null);
}
return(dTable);
}
public void DeleteAlbum(string AlbumName)
{
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("SELECT * FROM Album", connection);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
DataTable albums = dataSet.Tables[0];
IEnumerable <DataRow> albumsQuery =
from album in albums.AsEnumerable().AsParallel()
where album["Title"].ToString() == AlbumName
select album;
DataRow albumRow = albumsQuery.ToArray()[0];
albumRow.Delete();
SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(dataAdapter);
dataAdapter.Update(dataSet);
}
internal void fillDatagrid(DataGridView dataGridView)
{
// Remplir
SQLiteDataAdapter da = new SQLiteDataAdapter("SELECT * FROM FILMS;", connexion);
DataSet ds = new DataSet();
da.Fill(ds, "FILMS");
dataGridView.DataSource = ds.Tables["FILMS"];
dataGridView.Columns[0].Visible = false; // ID
dataGridView.Columns[1].Visible = false; // Chemin
dataGridView.Columns[2].Width = 200; // Titre
dataGridView.Columns[3].Width = 100; // Realisateur
dataGridView.Columns[4].Width = 100; // Acteurs
dataGridView.Columns[5].Width = 100; // Genres
dataGridView.Columns[6].Width = 100; // Nationalite
dataGridView.Columns[7].Width = 200; // Resume
dataGridView.Columns[8].Width = 100; // Date sortie
dataGridView.Columns[9].Width = 50; // Etat
dataGridView.Columns[10].Width = 50; // Flags
dataGridView.Columns[11].Width = 100; // Tag
dataGridView.Columns[12].Width = 400; // Affiche
}
public IEnumerable <Items> GetItems()
{
List <Items> items = new List <Items>();
using (SQLiteConnection conread = new SQLiteConnection("Data Source=" + dbPath))
{
conread.Open();
SQLiteDataAdapter DB = new SQLiteDataAdapter("select * from Item", conread);
DataSet DS = new DataSet();
DB.Fill(DS, "Items");
foreach (DataRow row in DS.Tables["Items"].Rows)
{
items.Add(new Items()
{
Id = Convert.ToInt32(row["Id"]),
SubCategoryId = Convert.ToInt32(row["SubCategoryId"]),
Name = row["Name"].ToString(),
Description = row["Description"].ToString()
});
}
}
return(items);
}
public List <string> getFilters(int id_dominio_ngestor, int id_sistemas_tipo)
{
List <string> tipos_servico_f = new List <string>();
SQLiteConnection conexao = new SQLiteConnection(_managerConnectionString);
conexao.Open();
SQLiteCommand command = new SQLiteCommand($"SELECT tipo_servico FROM ordens_de_servicos WHERE id_dominio_ngestor = {id_dominio_ngestor} AND id_sistemas_tipo = {id_sistemas_tipo}", conexao);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
DataTable datatable = new DataTable();
da.Fill(datatable);
tipos_servico_f.Clear();
tipos_servico_f.Add("Todos");
for (int i = 0; i < datatable.Rows.Count; i++)
{
if (!tipos_servico_f.Contains(datatable.Rows[i][0].ToString()))
{
tipos_servico_f.Add(datatable.Rows[i][0].ToString());
}
}
conexao.Close();
return(tipos_servico_f);
}
public DataTable SelectFromFilters(int id_dominio_ngestor, string os_selected_f, int id_sistemas_tipo)
{
if (os_selected_f == "Todos")
{
return(selectAllSemPaginate(id_dominio_ngestor, id_sistemas_tipo));
}
try
{
SQLiteConnection conexao = new SQLiteConnection(_managerConnectionString);
conexao.Open();
SQLiteCommand command = new SQLiteCommand($"SELECT * FROM ordens_de_servicos WHERE id_dominio_ngestor = '{id_dominio_ngestor}' AND id_sistemas_tipo = {id_sistemas_tipo} AND tipo_servico = '{os_selected_f}'", conexao);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
DataTable datatable = new DataTable();
da.Fill(datatable);
return(datatable);
conexao.Close();
}
catch { }
return(null);
}
//doesn't handle opening and closing the connection, need to do manually
private void UpdateDecalDataGrid(SQLiteConnection DBConnection)
{
//establish database connection
try
{
//fill columns and stuff
SQLiteCommand cmd = new SQLiteCommand("select rowid,* from decals", DBConnection);
cmd.ExecuteNonQuery();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable table = new DataTable("tbl_category");
adapter.Fill(table);
this.DGDatabase.DataSource = table.DefaultView;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error (What the heck did you do?!)");
this.Close();
}
}
/// <summary>
/// 页面数据加载
/// </summary>
/// <param name="sqlString">查询SQL语句</param>
/// <returns></returns>
public DataTable LoadInfo(string sqlString)
{
SQLiteDataAdapter sQLiteDataAdapter;
DataTable dt = new DataTable();
try
{
sqliteCom.CommandText = sqlString;
sqliteCom.Connection = sqliteCon;
sqliteCon.Open();
sQLiteDataAdapter = new SQLiteDataAdapter(sqliteCom);
sQLiteDataAdapter.Fill(dt);
return(dt);
}
catch (Exception e)
{
return(dt = null);
}
finally
{
sqliteCon.Close();
}
}
public System.Data.DataSet ExecuteReader(string sql)
{
System.Data.DataSet ds = new System.Data.DataSet();
SQLiteDataAdapter dadapter = new SQLiteDataAdapter();
dadapter.SelectCommand = new SQLiteCommand(sql, _Conn);
dadapter.SelectCommand.CommandTimeout = _CommandTimeOut;
dadapter.Fill(ds);
foreach (System.Data.DataTable table in ds.Tables)
{
foreach (System.Data.DataColumn col in table.Columns)
{
if (col.ColumnName.StartsWith("[") && col.ColumnName.EndsWith("]"))
{
col.ColumnName = col.ColumnName.Substring(1, col.ColumnName.Length - 2);
}
}
}
return(ds);
}
private void AdjustmentEdt_Load(object sender, EventArgs e)
{
this.invoice_header_unpaidTableAdapter.Fill(this.dataSet2.invoice_header_unpaid);
this.iNVOICE_HEADERTableAdapter.Fill(this.dataSet2.INVOICE_HEADER);
this.customerTableAdapter.Fill(this.dataSet2.customer);
this.customer_transTableAdapter.Fill(this.dataSet2.customer_trans);
// Invoice header binding source
invoice_header_unpaidBindingSource.Filter = String.Format("cust_id = '{0}'", ((DataRowView)customerBindingSource.DataSource).Row["cust_id"]);
// Load the drop down list for Invoices with custom invoice list
sqLiteConnection1.Open();
string cmd = String.Format("select 0 invoice_number, 'Customer Level' invoice_text from customer where cust_id = '{0}' union select invoice_number, invoice_number || ' - ' ||ifnull(docket_number,0) || ' - ' || ifnull(date(invoice_date),' ') || ' - ' || invoice_unpaid as invoice_text from invoice_header where cust_id = '{0}' and invoice_unpaid != 0 order by 1", ((DataRowView)customerBindingSource.DataSource).Row["cust_id"]);
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd, sqLiteConnection1);
DataSet ds = new DataSet();
da.Fill(ds, "inv");
invoice_header_unpaidComboBox.DataSource = ds.Tables["inv"];
invoice_header_unpaidComboBox.DisplayMember = ds.Tables["inv"].Columns[1].ToString();
invoice_header_unpaidComboBox.ValueMember = ds.Tables["inv"].Columns[0].ToString();
sqLiteConnection1.Close();
// Load the drop down list for Payments with custom payments list
sqLiteConnection1.Open();
string cmd1 = String.Format("select 0 recpt_number, 'Customer Level' payment_text from customer where cust_id = '{0}' union select recpt_number, recpt_number || ' - ' || ifnull(date(recpt_date), ' ') || ' - ' || amount as payment_text from INVOICE_RECIEPTS where cust_id = '{0}' and amount != 0 order by 1", ((DataRowView)customerBindingSource.DataSource).Row["cust_id"]);
SQLiteDataAdapter da1 = new SQLiteDataAdapter(cmd1, sqLiteConnection1);
DataSet ds1 = new DataSet();
da1.Fill(ds1, "pay");
paymentCB.DataSource = ds1.Tables["pay"];
paymentCB.DisplayMember = ds1.Tables["pay"].Columns[1].ToString();
paymentCB.ValueMember = ds1.Tables["pay"].Columns[0].ToString();
sqLiteConnection1.Close();
comboBox1.Text = comboBox1.Items[0].ToString();
// Get the Current Accounting Period
Vectra.DataSet2TableAdapters.configurationTableAdapter configDA = new Vectra.DataSet2TableAdapters.configurationTableAdapter();
CurrentAcntPeriod = configDA.getCurrentAcntPeriod().ToString();
}
/// <summary>
/// 执行数据库查询，返回DataSet对象
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="commandText">执行语句或存储过程名</param>
/// <param name="commandType">执行类型</param>
/// <param name="cmdParms">SQL参数对象</param>
/// <returns>DataSet对象</returns>
public static DataSet ExecuteDataSet(string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
{
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, cmdParms);
try
{
SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
sda.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
return(ds);
}
private void BtnDisplay_Click(object sender, EventArgs e)
{
if (TxtUpdateID.Text == "")
{
LblUpdateMsg.Text = "Enter ID to Display";
}
else
{
SQLiteConnection ConnectDb = new SQLiteConnection("Data Source = SWAT_PAD_ITERATION.sqlite3");
ConnectDb.Open();
string query = "SELECT * FROM SWAT_Iterations where ID = '" + TxtUpdateID.Text + "'";
SQLiteDataAdapter DataAdptr = new SQLiteDataAdapter(query, ConnectDb);
DataTable Dt = new DataTable();
DataAdptr.Fill(Dt);
string value;
foreach (DataRow row in Dt.Rows) //there is only one row here
{
value = row[1].ToString();
TxtUpdateProjectName.Text = value;
value = row[2].ToString();
TxtUpdateIterationNo.Text = value;
value = row[3].ToString();
TxtUpdateParameters.Text = value;
value = row[4].ToString();
TxtUpdateRemarks.Text = value;
value = row[5].ToString();
TxtUpdateFindings.Text = value;
value = row[6].ToString();
TxtUpdateFinalVerdict.Text = value;
}
ConnectDb.Close();
LblUpdateMsg.Text = "Selected ID Displayed" + " : " + TxtUpdateProjectName.Text + " : " + TxtUpdateIterationNo.Text;
}
}
public bool SignIn(User userInfo)
{
try
{
if (sqlite_conn.State == ConnectionState.Open || sqlite_conn.State == ConnectionState.Executing)
{
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'Employee';";
SQLiteDataReader reader = sqlite_cmd.ExecuteReader();
while (reader.Read())
{
string CommandText = "SELECT EmployeeNumber, Password FROM Employee";
DB = new SQLiteDataAdapter(CommandText, SingletonDB.GetDBConnection());
DS.Reset();
DB.Fill(DS);
DT = DS.Tables[0];
string find = "EmployeeNumber = '" + userInfo.EmployeeNumber + "' AND Password = '******'";
DataRow[] foundRows = DT.Select(find);
if (foundRows.Length > 0)
{
return(true);
}
}
return(false);
}
}
catch (Exception)
{
throw;
}
return(false);
}
private void vacationButton_Click(object sender, EventArgs e)
{
SQLiteConnection sql_con = new SQLiteConnection(@"Data Source=D:\4семестр\прПР\PaymentSystem\systemDb.db; Version=3;");
sql_con.Open();
DataTable dTable = new DataTable();
string sqlQuery;
string hours = "0";
sqlQuery = string.Format("SELECT * FROM Records WHERE id=\"{0}\";", User.id);
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlQuery, sql_con);
adapter.Fill(dTable);
if (dTable.Rows.Count > 0)
{
hours = dTable.Rows[0][4].ToString();
}
else
{
return;
}
int temp = Convert.ToInt32(hours);
if (temp > 150)
{
string updatecorect = "update Records set lastVacation=0 where id=" + User.id;
SQLiteCommand updatec = new SQLiteCommand(updatecorect, sql_con);
updatec.ExecuteNonQuery();
MessageBox.Show("Success! Happy vacations");
return;
}
else
{
MessageBox.Show("You have not entered a password");
return;
}
}
public DataTable getUser(string username, string password)
{
sqlite.Open();
DataTable dt = new DataTable();
DataTable salt = new DataTable();
//if username in DB, get salt
string saltquery = "SELECT salt FROM user WHERE username= @username";
SQLiteCommand saltcmd = new SQLiteCommand(saltquery, sqlite);
saltcmd.Parameters.AddWithValue("@username", username);
SQLiteDataAdapter saltDA = new SQLiteDataAdapter(saltcmd);
saltDA.Fill(salt);
Array saltArray = salt.Select();
foreach (DataRow i in saltArray)
{
if (i[0].ToString() != "")
{
string saltedPassword = String.Concat(password, i[0]);
string saltedHashedPassword = saltedPassword.GetHashCode().ToString();
//perform 2nd query
string query = "SELECT username, password FROM user WHERE username= @username AND password= @password";
SQLiteCommand cmd = new SQLiteCommand(query, sqlite);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", saltedHashedPassword);
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(dt);
}
}
return(dt);
}
public Collection <Prova> Listar()
{
Collection <Prova> colecao = new Collection <Prova>();
using (SQLiteCommand comando = conexao.Buscar().CreateCommand())
{
comando.CommandType = System.Data.CommandType.Text;
comando.CommandText = "Select idProva, t.IdTurma, m.IDMateria, a.idAluno, t.NomeTurma, a.NomeAluno,m.nomeMateria, p.NotaProva1, p.NotaProva2, " +
"p.NotaProva3, MediaProva from prova p inner join aluno a on a.idaluno = p.idAluno " +
"inner join turma t on t.IdTurma = a.IdTurma " +
"inner join materia m on m.idmateria = p.idmateria ";
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(comando))
{
DataTable tabela = new DataTable();
adapter.Fill(tabela);
foreach (DataRow row in tabela.Rows)
{
Prova prova = new Prova
{
IDProva = int.Parse(row["IDProva"].ToString()),
NotaProva1 = double.Parse(row["NotaProva1"].ToString()),
NotaProva2 = double.Parse(row["NotaProva2"].ToString()),
NotaProva3 = double.Parse(row["NotaProva3"].ToString()),
MediaProva = double.Parse(row["MediaProva"].ToString()),
IDAluno = int.Parse(row["IDAluno"].ToString()),
IDMateria = int.Parse(row["IDMateria"].ToString())
};
colecao.Add(prova);
}
}
}
return(colecao);
}
private void stanokComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (stanokComboBox.SelectedIndex == 0)
{
stanokComboBox.SelectedIndex = -1;
}
if (stanokComboBox.SelectedIndex > 0)
{
if (typeComboBox.SelectedIndex == 0)
{
SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + Environment.CurrentDirectory + @"\data.db; Version=3;");
conn.Open();
DataTable data = new DataTable();
SQLiteCommand cmd = new SQLiteCommand("select naibDiametr, naibDlina from vnutrenniyStanok where name = '" + stanokComboBox.SelectedValue + "'", conn);
SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd);
ad.Fill(data);
conn.Dispose();
diamInt.Content = "<" + data.Rows[0][0].ToString();
dlinaInt.Content = "<" + data.Rows[0][1].ToString();
}
else
{
SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + Environment.CurrentDirectory + @"\data.db; Version=3;");
conn.Open();
DataTable data = new DataTable();
SQLiteCommand cmd = new SQLiteCommand("select naibDiam, naibDlina from naruzhniyStanok where name = '" + stanokComboBox.SelectedValue + "'", conn);
SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd);
ad.Fill(data);
conn.Dispose();
diamInt.Content = "<" + data.Rows[0][0].ToString();
dlinaInt.Content = "<" + data.Rows[0][1].ToString();
}
}
}
private DataTable ProcurarDados()
{
string sql = "SELECT "
+ "EVENTO.NOME AS 'NOME EVENTO'"
+ ",LISTA_PRESENCA.*"
+ "FROM "
+ "TB_LISTA_PRESENCA AS LISTA_PRESENCA"
+ "INNER JOIN "
+ "TB_EVENTO AS EVENTO "
+ "ON LISTA_PRESENCA.ID_EVENTO = EVENTO.ID_EVENTO"
+ "WHERE "
+ "EVENTO.NOME LIKE '%" + txtBusca.Text + "%' "
+ "OR LISTA_PRESENCA.NOME LIKE '%" + txtBusca.Text + "%'";
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn))
{
try
{
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
catch (SQLiteException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
}
private void LoadComboBoxes()
{
DataSet state_carriers = new DataSet();
SQLiteConnection sqlite_conn;
SQLiteCommand sqlite_cmd;
SQLiteDataAdapter sqlite_data_adapter;
sqlite_conn = new SQLiteConnection(database_conn_string, true);
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "SELECT strStateID, strStateFull FROM tState; SELECT strCarrierName, lngCarrierID FROM tPhoneCarriers";
sqlite_data_adapter = new SQLiteDataAdapter();
sqlite_data_adapter.SelectCommand = sqlite_cmd;
try
{
sqlite_data_adapter.Fill(state_carriers);
cboState.ItemsSource = state_carriers.Tables[0].DefaultView;
cboState.DisplayMemberPath = state_carriers.Tables[0].Columns["strStateFull"].ToString();
cboState.SelectedValuePath = state_carriers.Tables[0].Columns["strStateID"].ToString();
cboCarrier.ItemsSource = state_carriers.Tables[1].DefaultView;
cboCarrier.DisplayMemberPath = state_carriers.Tables[1].Columns["strCarrierName"].ToString();
cboCarrier.SelectedValuePath = state_carriers.Tables[1].Columns["lngCarrierID"].ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Database Connection Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
sqlite_conn.Close();
sqlite_cmd.Dispose();
sqlite_data_adapter.Dispose();
}
}
public static DataSet getAllBookkingsMadeByCustomer(int pCustomerId)
{
//queryString = "Select Date_Of_Booking, Date, Price, Name, Length, Section, Row, Number" +
// " From Bookings, Seats, Showings, Plays" +
// " Where Customers.Customer_Id = Bookings.Customer_Id" +
// " And Bookings.Booking_Id = Seats.Bookings_Id" +
// " And Seats.Showing_Id = Showings.Showing_Id" +
// " And Showings.Play_Id = Plays.Play_Id" +
// " And Customers.Customer_Id = @customerId";
//SQLiteCommand command = new SQLiteCommand(queryString, this.getConnectionString());
//this.getConnectionString().Open();
//command.Parameters.AddWithValue("@customerId", pCustomerId);
//sqlReader = command.ExecuteReader();
//this.getConnectionString().Close();
//return sqlReader;
DataSet dataSet = new DataSet();
queryString = "Select Bookings.Booking_Id, Date_Of_Booking, Total_Amount, Paid, Name, Length, Date, Section, Row, Number" +
" From Bookings, Seats, Showings, Plays , Customers" +
" Where Customers.Customer_Id = Bookings.Customer_Id" +
" And Bookings.Booking_Id = Seats.Booking_Id" +
" And Seats.Showing_Id = Showings.Showing_Id" +
" And Showings.Play_Id = Plays.Play_Id" +
" And Customers.Customer_Id = @customerId";
using (SQLiteConnection connection = new SQLiteConnection(SqlClassBase.getConnectionString()))
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
SQLiteCommand command = new SQLiteCommand(queryString, connection);
command.Parameters.AddWithValue("@customerId", pCustomerId);
adapter.SelectCommand = command;
adapter.Fill(dataSet);
}
return(dataSet);
}
public static CellInStock Restore(string in_x, string in_y, SkuInStock in_skuInStock)
{
using SQLiteConnection conn = ConnectionRegistry.Instance.OpenNewConnection();
using SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select *" +
" from cell_in_stock" +
" where point_of_sale_id = @in_pointOfSaleId" +
" and article_id = @in_articleId" +
" and x = @in_x" +
" and y = @in_y";
cmd.Parameters.Add(new SQLiteParameter("@in_pointOfSaleId", in_skuInStock.PointOfSale.Id));
cmd.Parameters.Add(new SQLiteParameter("@in_articleId", in_skuInStock.Article.Id));
cmd.Parameters.Add(new SQLiteParameter("@in_x", in_x));
cmd.Parameters.Add(new SQLiteParameter("@in_y", in_y));
DataTable table = new DataTable();
using (SQLiteDataAdapter a = new SQLiteDataAdapter(cmd))
a.Fill(table);
if (table.Rows.Count == 0)
{
return(null);
}
else if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
return(CellInStock.Restore((int)(long)row["id"], UnixEpoch.ToDateTime((long)row["modified"]), in_skuInStock, in_x, in_y, (int)(long)row["amount"]));
}
else
{
throw new ApplicationException("CellInStock.Restore(string in_x, string in_y, SkuInStock in_skuInStock) returned more than 1 row.");
}
}
public bool Connect()
{
try
{
ds = new DataSet();
using (connection = new SQLiteConnection("Data source=autobase.db"))
{
connection.Open();
da = new SQLiteDataAdapter("SELECT name FROM sqlite_master WHERE type='table';", connection);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
string table_name = dt.Rows[i]["name"].ToString();
if (table_name != "sqlite_sequence")
{
string command = "SELECT * FROM " + table_name;
da = new SQLiteDataAdapter(command, connection);
da.Fill(ds, table_name);
}
}
connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show("Помилка завантаження бази даних" + (char)13 + "Програма буде закрита");
return false;
}
return true;
}
/// <summary>
/// Turn a datatable into a table in the temporary database for the connection
/// </summary>
/// <param name="cnn">The connection to make the temporary table in</param>
/// <param name="table">The table to write out</param>
/// <param name="dest">The temporary table name to write to</param>
private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest)
{
StringBuilder sql = new StringBuilder();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteCommand cmd = cnn.CreateCommand())
using (DataTable source = new DataTable())
{
sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest));
string separator = String.Empty;
SQLiteConnectionFlags flags = cnn.Flags;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType);
string typeName = SQLiteConvert.DbTypeToTypeName(cnn, dbtypeName, flags);
sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator);
separator = ", ";
}
sql.Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest));
using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd))
{
builder.DataAdapter = adp;
adp.Fill(source);
foreach (DataRow row in table.Rows)
{
object[] arr = row.ItemArray;
source.Rows.Add(arr);
}
adp.Update(source);
}
}
}
/// <summary>
/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
/// </summary>
/// <remarks>
/// e.g.:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
/// </remarks>
/// <param name="insertCommand">A valid SQL statement to insert new records into the data source</param>
/// <param name="deleteCommand">A valid SQL statement to delete records from the data source</param>
/// <param name="updateCommand">A valid SQL statement used to update records in the data source</param>
/// <param name="dataSet">The DataSet used to update the data source</param>
/// <param name="tableName">The DataTable used to update the data source.</param>
public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
// Create a SQLiteDataAdapter, and dispose of it after we are done
using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())
{
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update(dataSet, tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}
}
private static void AddSQLiteParameters(SQLiteDataAdapter dAdapter)
{
foreach (SQLiteParameter param in parameters)
dAdapter.SelectCommand.Parameters.Add(param);
}
public DBAdapter(int cType, object cLink,string Query)
{
this.cType = cType;
this.cLink = cLink;
switch (cType)
{
case 0:
Adapter = new MySqlDataAdapter(Query, (MySqlConnection)cLink);
break;
case 1:
Adapter = new SqlDataAdapter(Query, (SqlConnection)cLink);
break;
case 2:
Adapter = new SQLiteDataAdapter(Query, (SQLiteConnection)cLink);
break;
}
}
/// <summary>
/// Executes the dataset from a populated Command object.
/// </summary>
/// <param name="cmd">Fully populated SQLiteCommand</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(SQLiteCommand cmd)
{
if (cmd.Connection.State == ConnectionState.Closed)
cmd.Connection.Open();
DataSet ds = new DataSet();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Connection.Close();
cmd.Dispose();
return ds;
}
public bool renew_table(string table_name)
{
try
{
using (connection = new SQLiteConnection("Data source=autobase.db"))
{
connection.Open();
da = new SQLiteDataAdapter("SELECT * FROM " + table_name, connection);
ds.Tables[table_name].Rows.Clear();
ds.Tables[table_name].Columns.Clear();
da.Fill(ds, table_name);
connection.Close();
}
}
catch {return false; }
return true;
}
/// <summary>
/// Execute XmlReader with complete Command
/// </summary>
/// <param name="command">SQLite Command</param>
/// <returns>XmlReader</returns>
public static XmlReader ExecuteXmlReader(IDbCommand command)
{
// open the connection if necessary, but make sure we
// know to close it when we�re done.
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
// get a data adapter
SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command);
DataSet ds = new DataSet();
// fill the data set, and return the schema information
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds);
// convert our dataset to XML
StringReader stream = new StringReader(ds.GetXml());
command.Connection.Close();
// convert our stream of text to an XmlReader
return new XmlTextReader(stream);
}
/// <summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
/// </summary>
/// <param name="connectionString">SQLite Connection string</param>
/// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList)
{
SQLiteConnection cn = new SQLiteConnection(connectionString);
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
AttachParameters(cmd, commandText, paramList);
}
DataSet ds = new DataSet();
if (cn.State == ConnectionState.Closed)
cn.Open();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
cn.Close();
return ds;
}
