/// <summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
/// </summary>
/// <param name="cn">Connection.</param>
/// <param name="commandText">Command text.</param>
/// <param name="paramList">Param list.</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
{
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
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);
}
private void fnInitOpt()
{
string strSQL = @"SELECT isCopy FROM InitOpt";
SQLiteConnection conn = new SQLiteConnection(@"Data Source = .\Contents.db");
conn.Open();
SQLiteDataAdapter Ap = new SQLiteDataAdapter(strSQL, conn);
DataTable dtOpt = new DataTable();
Ap.Fill(dtOpt);
if (dtOpt.Rows[0]["isCopy"].ToString().ToUpper().Equals("Y"))
{
rdoCopy.Checked = true;
}
else
{
rdoCopy.Checked = false;
}
Ap.Dispose();
conn.Dispose();
}
public static string GetDataBySQL(SQLiteConnection cn, string sSQL, out DataTable rs)
{
string sErr = "";
SQLiteDataAdapter xAd = null;
rs = null;
try
{
xAd = new SQLiteDataAdapter(sSQL, cn);
rs = new DataTable();
xAd.Fill(rs);
}
catch (Exception ex)
{
sErr = string.Format("Error encountered. sSQL: {0}", sSQL)
+ "\r\n" + ex.Message + "\r\n" + ex.StackTrace;
goto Get_Out;
}
Get_Out:
if (xAd != null)
{
xAd.Dispose();
xAd = null;
}
if (sErr != "")
{
sErr += "\r\nSQL=" + sSQL
+ "\r\nRoutine=" + MethodInfo.GetCurrentMethod().ReflectedType.Name + "." + MethodInfo.GetCurrentMethod().ToString();
}
return(sErr);
}
/// <summary>
/// 查询数据集
/// </summary>
/// <param name="dbPath">数据库地址</param>
/// <param name="commandText">查询语句.</param>
/// <param name="paramList">object参数列表.</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string dbPath, string commandText, params object[] paramList)
{
dbfilePath = dbPath;
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);
}
private void LoadJudgesList()
{
if (cbCourtsList.EditValue != null)
{
try
{
string query = "SELECT * FROM Judges WHERE CourtID = @CourtID";
var conn = new SQLiteConnection(MainForm.mainForm.connectionDictionaryData);
var cmd = new SQLiteCommand(query, conn);
cmd.Parameters.AddWithValue("@CourtID", cbCourtsList.GetColumnValue("ID"));
conn.Open();
var da = new SQLiteDataAdapter(cmd);
var dataTable = new DataTable();
da.Fill(dataTable);
conn.Close();
da.Dispose();
lbJudges.DataSource = dataTable;
}
catch (SQLiteException ex)
{
errorCollector.ApplicationEvent(MainForm.mainForm.UserID, 1, ex.Message, "LoadJudgesList()", false);
}
}
}
/// <summary>
/// Reload (or not) the database, then group the data
/// </summary>
/// <param name="ShouldReloadData"></param>
private void LoadDatabase(bool ShouldReloadData = true)
{
if (ShouldReloadData)
{
listView1.Columns.Clear();
listView1.Items.Clear();
SQLiteDataAdapter Adapter = new SQLiteDataAdapter(
"SELECT RefSousFamille, RefFamille, Nom FROM SousFamilles",
DbConnect.GetInstance().GetConnection());
DataTable Dt = new DataTable();
Adapter.Fill(Dt);
listView1.KeyDown += OnListViewKeyDown;
listView1.Sorting = SortOrder.Ascending;
listView1.ColumnClick += OnColumnClick;
listView1.ListViewItemSorter = new ListViewItemComparer();
//Create columns
listView1.Columns.Add("RefSousFamille");
listView1.Columns.Add("RefFamille");
listView1.Columns.Add("Nom");
//Add items
for (int I = 0; I < Dt.Rows.Count; I++)
{
DataRow Dr = Dt.Rows[I];
ListViewItem ListItem = new ListViewItem(Dr["RefSousFamille"].ToString());
ListItem.SubItems.Add(Dr["RefFamille"].ToString());
ListItem.SubItems.Add(Dr["Nom"].ToString());
ListItem.Tag = new SubFamily(
Convert.ToInt64(Dr["RefSousFamille"].ToString()),
Convert.ToInt64(Dr["RefFamille"].ToString()),
Dr["Nom"].ToString()
);
listView1.Items.Add(ListItem);
}
Adapter.Dispose();
}
else
{
listView1.Groups.Clear();
}
//Resize columns
listView1.Columns[0].Width = -2;
listView1.Columns[1].Width = -2;
listView1.Columns[2].Width = -2;
if (_SortColumn == 0 || _SortColumn == 2) //If should group or not
{
listView1.ShowGroups = false;
}
else
{
//Create groups
listView1.ShowGroups = true;
foreach (ListViewItem ListItem in listView1.Items)
{
ListItem.Group = null;
ListViewGroup Group = null;
foreach (ListViewGroup GroupTest in listView1.Groups)
{
if (GroupTest.Header == ListItem.SubItems[_SortColumn].Text)
{
Group = GroupTest;
break;
}
}
if (Group == null)
{
Group = new ListViewGroup();
Group.Name = ListItem.SubItems[_SortColumn].Text;
Group.Header = ListItem.SubItems[_SortColumn].Text;
listView1.Groups.Add(Group);
}
Group = listView1.Groups[ListItem.SubItems[_SortColumn].Text];
Group.Items.Add(ListItem);
ListItem.Group = Group;
}
}
}
private void btDataBase_Click(object sender, EventArgs e)
{
//connection = new SQLiteConnection(@"data source=./mukavemet.db");
connection = new SQLiteConnection("data source=" + dbfile);
if (clbxProductFilter.Enabled)
{
btProductFilter.PerformClick();
}
if (clbxUserFilter.Enabled)
{
btUserFilter.PerformClick();
}
try
{
string query;
connection.Open();
query = "SELECT * FROM mukayit";
if (chbPickDate.Checked)
{
DateTime dtFromDate = dtpFrom.Value.Date;
DateTime dtToDate = dtpTo.Value.Date.AddDays(1) -
TimeSpan.FromSeconds(1);
string fromDate = dtFromDate.ToString("yyyy-MM-dd HH:mm:ss");
string toDate = dtToDate.ToString("yyyy-MM-dd HH:mm:ss");
query = query + " WHERE Tarih BETWEEN \'" +
fromDate + "\' AND \'" + toDate + "\'";
}
if (!rbBoth.Checked)
{
if (rbBendOnly.Checked)
{
mesType = "Eğilme";
}
else
{
mesType = "Basınç";
}
if (chbPickDate.Checked)
{
query = query + " AND \"Ölçüm Türü\" IS \"" + mesType + "\"";
}
else
{
query = query + " WHERE \"Ölçüm Türü\" IS \"" + mesType + "\"";
}
}
if (chbProductFilter.Checked)
{
if (selectedProducts.Length != 0)
{
StringBuilder sbProducts = new StringBuilder();
for (int i = 0; i < selectedProducts.Length; i++)
{
sbProducts.Append('\"');
sbProducts.Append(selectedProducts[i]);
sbProducts.Append('\"');
if (i != selectedProducts.Length - 1)
{
sbProducts.Append(',');
}
}
if (chbPickDate.Checked || !rbBoth.Checked)
{
query = query + " AND \"Ürün Cinsi\" IN (" + sbProducts.ToString() + ")";
}
else
{
query = query + " WHERE \"Ürün Cinsi\" IN (" + sbProducts.ToString() + ")";
}
}
else
{
MessageBox.Show("Kullanıcı filtreleme açık fakat hiç kullanıcı seçilmedi.\nTüm kullanıcılar gösterilecek.");
}
}
if (chbUserFilter.Checked)
{
if (selectedUsers.Length != 0)
{
StringBuilder sbUsers = new StringBuilder();
for (int i = 0; i < selectedUsers.Length; i++)
{
sbUsers.Append('\"');
sbUsers.Append(selectedUsers[i]);
sbUsers.Append('\"');
if (i != selectedUsers.Length - 1)
{
sbUsers.Append(',');
}
}
if (chbProductFilter.Checked ||
chbPickDate.Checked || !rbBoth.Checked)
{
query = query + " AND \"Ölçümü Yapan\" IN (" +
sbUsers.ToString() + ")";
}
else
{
query = query + " WHERE \"Ölçümü Yapan\" IN (" +
sbUsers.ToString() + ")";
}
}
else
{
MessageBox.Show("Ürün filtreleme açık fakat hiç ürün " +
"seçilmedi.\nTüm ürünler gösterilecek.");
}
}
SQLiteCommand command = new SQLiteCommand(query, connection);
DataTable dt = new DataTable();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(dt);
adapter.Dispose();
dgwKayit.Enabled = true;
dgwKayit.Visible = true;
dgwKayit.DataSource = dt;
connection.Close();
dgwKayit.ColumnHeadersDefaultCellStyle.Font = new Font(
dgwKayit.ColumnHeadersDefaultCellStyle.Font.FontFamily,
12f,
FontStyle.Bold);
dgwKayit.DefaultCellStyle.Font = new Font(
dgwKayit.DefaultCellStyle.Font.FontFamily,
10.8f,
FontStyle.Regular);
dgwKayit.AutoResizeRows();
dgwKayit.AutoResizeColumns();
dgwKayit.BackgroundColor = System.Drawing.Color.White;
dgwKayit.FirstDisplayedScrollingRowIndex = dgwKayit.RowCount - 1;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
connection.Close();
}
}
static void Main(string[] args)
{
MySqlite db = new MySqlite("mydb.db3");
db.connect();
try
{
db.executeQuery("create table users(" +
"id integer primary key autoincrement," +
"username varchar(32)," +
"password varchar(128))");
} catch (SQLiteException e) {
Console.WriteLine("{0}", e.Message);
}
string[,] users = new string[50, 2];
for (int n = 0; n < 50; n++)
{
users[n, 0] = "user_" + n.ToString();
users[n, 1] = "pass_" + n.ToString();
}
db.fastInserts("users", "NULL, ?,?", users);
DataSet dataSet = db.select("select * from users;");
foreach (DataTable t in dataSet.Tables)
{
Console.WriteLine("Tabela {0} zawiera {1} wiersze", t.TableName, t.Rows.Count);
foreach (DataRow r in t.Rows)
{
Console.Write("-> ");
foreach (DataColumn c in t.Columns)
{
Console.Write("{0}={1}, ", c.ColumnName, r[c.ColumnName]);
}
Console.WriteLine();
}
}
dataSet.Dispose();
SQLiteDataAdapter da = db.getAdapter("select * from users;", true);
DataSet ds = db.getData(da);
DataRow[] drows = ds.Tables[0].Select("username = '******'");
for (int i = 0; i < drows.Length; i++)
{
drows[i].BeginEdit();
drows[i]["password"] = "******";
drows[i].EndEdit();
}
Console.WriteLine("zmodyfikowano: {0} wierszy", da.Update(ds));
ds.Dispose();
da.Dispose();
dataSet = db.select("select * from users;");
foreach (DataTable t in dataSet.Tables)
{
Console.WriteLine("Tabela {0} zawiera {1} wiersze", t.TableName, t.Rows.Count);
foreach (DataRow r in t.Rows)
{
Console.Write("-> ");
foreach (DataColumn c in t.Columns)
{
Console.Write("{0}={1}, ", c.ColumnName, r[c.ColumnName]);
}
Console.WriteLine();
}
}
db.close();
Console.ReadLine();
}
/// <summary>
/// Reload (or not) the database, then group the data
/// </summary>
/// <param name="ShouldReloadData"></param>
private void LoadDatabase(bool ShouldReloadData = true)
{
if (ShouldReloadData)
{
listView1.Columns.Clear();
listView1.Items.Clear();
SQLiteDataAdapter Adapter = new SQLiteDataAdapter("SELECT Marques.RefMarque AS RefMarque, SousFamilles.RefSousFamille AS RefSousFamille, Articles.RefArticle AS RefArticle, Articles.Description AS Description, Articles.PrixHT AS PrixHT, Articles.Quantite AS Quantite, SousFamilles.Nom AS SousFamille, Familles.Nom AS Famille, Marques.Nom AS Marque FROM Articles LEFT JOIN Marques ON Articles.RefMarque = Marques.RefMarque LEFT JOIN SousFamilles ON Articles.RefSousFamille = SousFamilles.RefSousFamille LEFT JOIN Familles ON SousFamilles.RefFamille = Familles.RefFamille", DbConnect.GetInstance().GetConnection());
DataTable Dt = new DataTable();
Adapter.Fill(Dt);
listView1.KeyDown += OnListViewKeyDown;
listView1.Sorting = SortOrder.Ascending;
listView1.ColumnClick += OnColumnClick;
listView1.ListViewItemSorter = new ListViewItemComparer();
//Create columns
listView1.Columns.Add("RefArticle");
listView1.Columns.Add("Description");
listView1.Columns.Add("Family");
listView1.Columns.Add("Sub Family");
listView1.Columns.Add("Brand");
listView1.Columns.Add("Price excluding VAT (€)");
listView1.Columns.Add("Quantity");
//Add items
for (int I = 0; I < Dt.Rows.Count; I++)
{
DataRow Dr = Dt.Rows[I];
ListViewItem ListItem = new ListViewItem(Dr["RefArticle"].ToString());
ListItem.SubItems.Add(Dr["Description"].ToString());
ListItem.SubItems.Add(Dr["Famille"].ToString());
ListItem.SubItems.Add(Dr["SousFamille"].ToString());
ListItem.SubItems.Add(Dr["Marque"].ToString());
ListItem.SubItems.Add(Dr["PrixHT"].ToString());
ListItem.SubItems.Add(Dr["Quantite"].ToString());
ListItem.Tag = new Article(
Dr["RefArticle"].ToString(),
Dr["Description"].ToString(),
Convert.ToInt64(Dr["RefSousFamille"].ToString()),
Convert.ToInt64(Dr["RefMarque"].ToString()),
double.Parse(Dr["PrixHT"].ToString()),
Convert.ToInt64(Dr["Quantite"].ToString())
);
listView1.Items.Add(ListItem);
}
Adapter.Dispose();
}
else
{
listView1.Groups.Clear();
}
//Resize columns
listView1.Columns[0].Width = -2;
listView1.Columns[1].Width = -2;
listView1.Columns[2].Width = -2;
listView1.Columns[3].Width = -2;
listView1.Columns[4].Width = -2;
listView1.Columns[5].Width = -2;
listView1.Columns[6].Width = -2;
if (_SortColumn == 0 || _SortColumn == 1) //Hide or show groups
{
listView1.ShowGroups = false;
}
else
{
listView1.ShowGroups = true;
foreach (ListViewItem ListItem in listView1.Items) //Set group for each item
{
ListItem.Group = null;
ListViewGroup Group = null;
foreach (ListViewGroup GroupTest in listView1.Groups)
{
if (GroupTest.Header == ListItem.SubItems[_SortColumn].Text)
{
Group = GroupTest;
break;
}
}
if (Group == null)
{
Group = new ListViewGroup();
Group.Name = ListItem.SubItems[_SortColumn].Text;
Group.Header = ListItem.SubItems[_SortColumn].Text;
listView1.Groups.Add(Group);
}
Group = listView1.Groups[ListItem.SubItems[_SortColumn].Text];
Group.Items.Add(ListItem);
ListItem.Group = Group;
}
}
}
private void alocare_loc()
{
timer3.Enabled = false;
numar_cartela = this.textBox1.Text;
// verifica locuri autobuz (sterge locurile ocupare din array)
DataTable dataTable = new DataTable();
string query = "SELECT loc FROM transport WHERE loc!='' AND ruta='" + Form1.ruta + "' AND schimb='" + Form1.sch + "' AND transport='" + Form1.tran + "' AND SUBSTR(data_transport,0,11) = DATE('NOW')";
SQLiteCommand cmd = new SQLiteCommand(query, sqlLiteCon);
sqlLiteCon.Open();
SQLiteDataAdapter dadapter = new SQLiteDataAdapter(cmd);
dadapter.Fill(dataTable);
sqlLiteCon.Close();
dadapter.Dispose();
foreach (DataRow row in dataTable.Rows)
{
locAfisat = Convert.ToInt32(row["loc"].ToString());
locuri_bus = locuri_bus.Where(val => val != locAfisat).ToArray();
locuri_rezerva = locuri_rezerva.Where(val => val != locAfisat).ToArray();
}
numar_angajat = "";
negasit = "";
loc_gasit_deja = 0;
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader = new SQLiteCommand("SELECT numar FROM personal WHERE numar_cartela='" + this.textBox1.Text + "'", sqlLiteCon).ExecuteReader();
bool flag1 = sqlDataReader.Read();
if (flag1)
{
numar_angajat = sqlDataReader.GetValue(0).ToString();
numar_angajat_rezerva = numar_angajat;
}
sqlLiteCon.Close();
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader1 = new SQLiteCommand("SELECT transport.ruta, transport.transport, transport.schimb FROM personal LEFT JOIN transport ON transport.numar_angajat=personal.numar WHERE personal.numar_cartela='" + this.textBox1.Text + "' AND transport.ruta='" + Form1.ruta + "' AND transport.schimb='" + Form1.sch + "' AND transport.transport='" + Form1.tran + "'", sqlLiteCon).ExecuteReader();
bool flag2 = sqlDataReader1.Read();
if (flag2)
{
ruta = sqlDataReader1.GetValue(0).ToString();
transport = sqlDataReader1.GetValue(1).ToString();
schimb = sqlDataReader1.GetValue(2).ToString();
}
sqlLiteCon.Close();
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader2 = new SQLiteCommand("SELECT loc FROM transport WHERE loc!='' AND numar_angajat='" + numar_angajat + "' AND numar_angajat!='' AND ruta='" + Form1.ruta + "' AND schimb='" + Form1.sch + "' AND transport='" + Form1.tran + "'AND SUBSTR(data_transport,0,11) = DATE('NOW')", sqlLiteCon).ExecuteReader();
bool flag3 = sqlDataReader2.Read();
if (flag3)
{
locAfisat = Convert.ToInt32(sqlDataReader2.GetValue(0).ToString());
locuri_bus = locuri_bus.Where(val => val != locAfisat).ToArray();
}
else
{
locAfisat = 0;
}
sqlLiteCon.Close();
// verifica daca cartela este alocata unui angajat si daca acesta nu are loc ocupat deja pe ziua curenta
if (numar_angajat != "")
{
if (ruta == Form1.ruta && transport == Form1.tran && schimb == Form1.sch && locAfisat != 0)
{
timer1.Enabled = true;
loc_afisat();
loc_gasit_deja = 1;
}
numar_angajat = "";
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader3 = new SQLiteCommand("SELECT personal.numar FROM personal LEFT JOIN rute ON rute.numar_angajat=personal.numar WHERE rute.nevoie_transport LIKE 'da' AND rute.ruta='" + Form1.ruta + "' AND rute.schimb='" + Form1.sch + "' AND CURRENT_TIMESTAMP BETWEEN rute.data AND rute.data_final AND personal.numar_cartela='" + this.textBox1.Text + "'", sqlLiteCon).ExecuteReader();
bool flag4 = sqlDataReader3.Read();
if (flag4)
{
numar_angajat = sqlDataReader3.GetValue(0).ToString();
}
sqlLiteCon.Close();
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader4 = new SQLiteCommand("SELECT transport.ruta, transport.transport, transport.schimb FROM personal LEFT JOIN transport ON transport.numar_angajat=personal.numar LEFT JOIN rute ON rute.numar_angajat=personal.numar WHERE rute.nevoie_transport LIKE 'da' AND rute.ruta='" + Form1.ruta + "' AND transport.schimb='" + Form1.sch + "' AND transport.transport='" + Form1.tran + "' AND CURRENT_TIMESTAMP BETWEEN rute.data AND rute.data_final AND personal.numar_cartela='" + this.textBox1.Text + "'", sqlLiteCon).ExecuteReader();
bool flag5 = sqlDataReader4.Read();
if (flag5)
{
ruta = sqlDataReader4.GetValue(0).ToString();
transport = sqlDataReader4.GetValue(1).ToString();
schimb = sqlDataReader4.GetValue(2).ToString();
}
sqlLiteCon.Close();
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader5 = new SQLiteCommand("SELECT loc FROM transport WHERE numar_angajat='" + numar_angajat + "' AND ruta='" + Form1.ruta + "' AND schimb='" + Form1.sch + "' AND transport='" + Form1.tran + "' AND SUBSTR(data_transport,0,11) = DATE('NOW')", sqlLiteCon).ExecuteReader();
bool flag6 = sqlDataReader5.Read();
if (flag6)
{
locAfisat = Convert.ToInt32(sqlDataReader5.GetValue(0).ToString());
locuri_bus = locuri_bus.Where(val => val != locAfisat).ToArray();
}
else
{
locAfisat = 0;
}
sqlLiteCon.Close();
// alocare loc autobuz daca persoana nu are loc alocat deja pe ziua curenta
if (numar_angajat != "")
{
if (ruta == Form1.ruta && transport == Form1.tran && schimb == Form1.sch && locAfisat != 0)
{
timer1.Enabled = true;
loc_afisat();
}
else
{
if (locuri_bus.Length != 0)
{
loc = locuri_bus[0];
locuri_bus = locuri_bus.Skip(1).ToArray();
timer1.Enabled = true;
loc_gasit();
}
else
{
loc_negasit();
}
}
}
else
{
if (loc_gasit_deja != 1)
{
loc_negasit();
}
}
}
else // daca cartela nu este alocata
{
if (Form1.ruta.Contains("Collect"))
{
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader3 = new SQLiteCommand("SELECT loc, ruta, transport, schimb FROM transport WHERE numar_cartela='" + this.textBox1.Text + "' AND ruta='" + Form1.ruta + "' AND schimb='" + Form1.sch + "' AND transport='" + Form1.tran + "' AND SUBSTR(data_transport,0,11) = DATE('NOW')", sqlLiteCon).ExecuteReader();
bool flag4 = sqlDataReader3.Read();
if (flag4)
{
ruta = sqlDataReader3.GetValue(1).ToString();
transport = sqlDataReader3.GetValue(2).ToString();
schimb = sqlDataReader3.GetValue(3).ToString();
locAfisat = Convert.ToInt32(sqlDataReader3.GetValue(0).ToString());
locuri_bus = locuri_bus.Where(val => val != locAfisat).ToArray();
}
else
{
locAfisat = 0;;
}
sqlLiteCon.Close();
if (ruta == Form1.ruta && transport == Form1.tran && schimb == Form1.sch && locAfisat != 0)
{
timer1.Enabled = true;
loc_afisat();
loc_gasit_deja = 1;
}
else
{
negasit = "DA";
timer1.Enabled = true;
if (locuri_bus.Length != 0)
{
loc = locuri_bus[0];
locuri_bus = locuri_bus.Skip(1).ToArray();
timer1.Enabled = true;
loc_gasit();
}
else
{
loc_liber = loc_liber + 1;
if (locuri_rezerva.Length != 0)
{
if (loc_liber <= nr_locuri_rezerva)
{
loc = locuri_rezerva[0];
locuri_rezerva = locuri_rezerva.Skip(1).ToArray();
loc_gasit();
}
}
else
{
loc_lipsa();
}
}
}
}
else
{
cartela_nealocata();
}
}
}
public DataSet GetDatasetFromSQL(string SQLCommandText, Dictionary <string, object> sqlParams, bool throwErrors)
{
DataSet dsResult = null;
SQLiteConnection dbBConnection = null;
SQLiteCommand query = null;
SQLiteDataAdapter dataAdapter = null;
try
{
// Protect this section with our ReaderWriterLockSlim that handles concurrency issues for us
try
{
// Use a read lock for this operation
_SQLiteDBReadWriteLocker.EnterReadLock();
// Open database connection if we're not inside a transaction session
if (_Session_sqlTransaction == null)
{
dbBConnection = new SQLiteConnection(_DBConnectStr);
dbBConnection.Open();
}
// Build SQL command
query = new SQLiteCommand();
if (_Session_sqlTransaction == null)
{
query.Connection = dbBConnection;
}
else
{
query.Connection = _Session_sqlConnection;
}
query.CommandText = SQLCommandText;
query.CommandType = CommandType.Text;
// Build and populate SQL command parameters
SetQueryParameters(query, sqlParams);
// Fill dataset via the SQL command
dsResult = new DataSet();
dataAdapter = new SQLiteDataAdapter(query);
dataAdapter.Fill(dsResult);
}
finally
{
// Release the read lock
if (_SQLiteDBReadWriteLocker.IsReadLockHeld)
{
_SQLiteDBReadWriteLocker.ExitReadLock();
}
}
}
catch (Exception ex)
{
LogMessage(TraceLevel.Error, ex.ToString() + "; " + BuildQueryInfoForLogging(query), System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name, System.Threading.Thread.CurrentThread.ManagedThreadId);
if (throwErrors)
{
throw ex;
}
}
finally
{
if (query != null)
{
query.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
/*
* if (dsResult != null)
* dsResult.Dispose();
*/
// If not inside a transaction session, free local db connection
if (_Session_sqlTransaction == null)
{
if (dbBConnection != null)
{
dbBConnection.Close();
dbBConnection.Dispose();
}
}
}
// return the result
return(dsResult);
}
public List <LoginFieldS> getInfoFromChrome(string colName, string username, string title, string action)
{
try
{
string dbPath = Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData);
string db_way = dbPath + "/HIOdb.sqlite";
string db_field = "logins";
List <LoginFieldS> lp = new List <LoginFieldS>();
string sql;
string colNameWithoutSlash = "";
string ConnectionString = "data source=" + db_way + ";New=True;UseUTF16Encoding=True";
DataTable DB = new DataTable();
if (colName.Substring(colName.Length - 1) == "/")
{
colNameWithoutSlash = colName.Substring(0, colName.Length - 1);
}
else
{
colNameWithoutSlash = colName;
}
if (title != "")
{
sql = string.Format("SELECT DISTINCT * FROM {0} where (action_url=\"{1}\" or origin_url=\"{2}\" or origin_url=\"{3}/\") and username_value=\"{4}\" and title=\"{5}\" ", db_field, (action == "" || action == null) ? "{}" : action, colNameWithoutSlash, colNameWithoutSlash, username, title);
}
else if ((username == "" || username == null) && colName != "*")
{
sql = string.Format("SELECT DISTINCT * FROM {0} where action_url=\"{1}\" or origin_url=\"{2}\" or origin_url=\"{3}/\" ", db_field, (action == "" || action == null) ? "{}" : action, colNameWithoutSlash, colNameWithoutSlash);
}
else if (colName == "*" && username != "")
{
sql = string.Format("SELECT DISTINCT * FROM {0} where username_value LIKE '{1}%' ", db_field, username.ToString());
}
else if (colName == "*")
{
sql = string.Format("SELECT DISTINCT * FROM {0} ", db_field);
}
else
{
sql = string.Format("SELECT DISTINCT * FROM {0} where (action_url=\"{1}\" or origin_url=\"{2}\" or origin_url=\"{3}/\") and username_value=\"{4}\"", db_field, (action == "" || action == null) ? "{}" : action, colNameWithoutSlash, colNameWithoutSlash, username);
}
using (SQLiteConnection connect = new SQLiteConnection(ConnectionString))
{
SQLiteCommand command = new SQLiteCommand(sql, connect);
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(DB);
int rows = DB.Rows.Count;
for (int i = 0; i < rows; i++)
{
//byte[] byteArray = (byte[])DB.Rows[i][3];
// byte[] decrypted = DPAPI.Decrypt(byteArray, entropy, out description);
lp.Add(new LoginFieldS {
url = (DB.Rows[i][0] == DBNull.Value) ? string.Empty : DB.Rows[i][0].ToString(), userName = (DB.Rows[i][2] == DBNull.Value) ? string.Empty : (string)DB.Rows[i][2], password = (DB.Rows[i][3] == DBNull.Value) ? string.Empty : (string)DB.Rows[i][3], title = (DB.Rows[i][4] == DBNull.Value) ? string.Empty : (string)DB.Rows[i][4], appID = (DB.Rows[i][5] == DBNull.Value) ? string.Empty : DB.Rows[i][5].ToString(), last_used = (DB.Rows[i][6] == DBNull.Value) ? string.Empty : DB.Rows[i][6].ToString(), rowid = (DB.Rows[i][7] == DBNull.Value) ? string.Empty : DB.Rows[i][7].ToString()
});
}
adapter.Dispose();
connect.Close();
}
// Writer.Close();
return(lp);
}
catch (Exception ex)
{
return(null);
}
}
private void loadConf(string confFile)
{
Dictionary <string, string> confKeyValuePairs = new Dictionary <string, string>();
string db_file = "";
// Load conf file
try
{
StreamReader sr = new StreamReader(confFile);
string line;
while (!sr.EndOfStream)
{
line = sr.ReadLine();
if (line.Contains("="))
{
string[] keyValue = line.Split('=');
confKeyValuePairs.Add(keyValue[0].Trim(), keyValue[1].Trim());
}
}
sr.Close();
if (!confKeyValuePairs.TryGetValue(Constants.DBFILE, out db_file))
{
System.Windows.Forms.MessageBox.Show("No se encontraron datos de EERR");
return;
}
}
catch (FileNotFoundException ex)
{
System.Windows.Forms.MessageBox.Show("El aplicativo require archivo de configuración (.ini).");
}
sqlite = new SQLiteConnection("Data Source=" + db_file);
SQLiteDataAdapter ad;
System.Data.DataTable dt = new System.Data.DataTable();
try
{
SQLiteCommand cmd;
sqlite.Open(); //Initiate connection to the db
// First load ITEMS
cmd = sqlite.CreateCommand();
cmd.CommandText = Constants.QUERY_ITEMS;
ad = new SQLiteDataAdapter(cmd);
ad.Fill(dt); //fill the datasource
DataRow[] rows = dt.Select();
for (int i = 0; i < rows.Length; i++)
{
items.Add((string)rows[i][Constants.ITEMS_1], (string)rows[i][Constants.ITEMS_2]);
}
ad.Dispose();
dt.Dispose();
rows = null;
// Second load AREA
cmd = sqlite.CreateCommand();
cmd.CommandText = Constants.QUERY_AREA;
ad = new SQLiteDataAdapter(cmd);
dt = new System.Data.DataTable();
ad.Fill(dt); //fill the datasource
rows = dt.Select();
for (int i = 0; i < rows.Length; i++)
{
string[] marca_agrup = new string[2];
marca_agrup[0] = (string)rows[i][Constants.AREA_2];
marca_agrup[1] = (string)rows[i][Constants.AREA_3];
area.Add((string)rows[i][Constants.AREA_1], marca_agrup);
}
ad.Dispose();
// Third load EERR
cmd = sqlite.CreateCommand();
cmd.CommandText = Constants.QUERY_EERR; //set the passed query
ad = new SQLiteDataAdapter(cmd);
dt = new System.Data.DataTable();
ad.Fill(dt); //fill the datasource
rows = dt.Select();
for (int i = 0; i < rows.Length; i++)
{
string[] prefix_desc = new string[2];
prefix_desc[0] = (string)rows[i][Constants.EERR_1];
prefix_desc[1] = (string)rows[i][Constants.EERR_2];
eerr.Add(prefix_desc);
}
ad.Dispose();
}
catch (SQLiteException ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
sqlite.Close();
}
public static DataTable ReadTable()
{
//string conString = "Data Source=Trips;Version=3;";
SQLiteConnection conn = new SQLiteConnection(conString);
SQLiteConnection con = null;
SQLiteCommand command = null;
SQLiteDataAdapter da = null;
DataTable dt = new DataTable();
try
{
string sql = string.Format("select * from Trips");
command = new SQLiteCommand(sql, conn);
da = new SQLiteDataAdapter(command);
da.Fill(dt);
}
catch { }
finally
{
if (da != null)
{
da.Dispose();
}
if (command != null)
{
command.Dispose();
}
if (con != null)
{
con.Close();
}
}
DataTable newTable = TableCenter.getTestTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = newTable.NewRow();
row["ProductNumber"] = dt.Rows[i]["ProductNumber"];
row["Item"] = dt.Rows[i]["Item"];
row["Time"] = dt.Rows[i]["Time"];
row["Result"] = dt.Rows[i]["Result"];
row["Type"] = dt.Rows[i]["Type"];
row["Ir"] = dt.Rows[i]["Ir"];
row["Tr"] = dt.Rows[i]["Tr"];
row["I"] = dt.Rows[i]["I"];
row["T"] = dt.Rows[i]["T"];
//if (i >= 3 && i < dt.Rows.Count - 1)
//{
// continue;
//}
//row["DeviceCode"] = "DW45";
//row["FactoryCode"] = "Shihlin Electric";
//row["ProductNumber"] = dt.Rows[i]["ProductNumber"];
//row["ProductDate"] = dt.Rows[i]["ProductDate"];
//row["ProductNumber"] = dt.Rows[i]["ProductNumber"];
//row["BreakType"] = "XSIC-P3G1";
//row["Inm"] = dt.Rows[i]["Inm"];
//row["In"] = dt.Rows[i]["In"];
//row["Imcr"] = dt.Rows[i]["Imcr"];
//row["Version"] = dt.Rows[i]["Version"];
//row["ControllerType"] = dt.Rows[i]["ControllerType"];
//row["Fn"] = dt.Rows[i]["Fn"];
//row["SaveTime"] = DateTime.Now;
//if (dt.Rows[i]["Inm"].ToString() == "1")
//{
// row["Inm"] = "2000";
//}
newTable.Rows.Add(row);
}
return(newTable);
}
//修改数据表记录
public static void UpdateDeviceTable()
{
SQLiteConnection con = null;
SQLiteCommand command = null;
SQLiteDataAdapter oda = null;
SQLiteCommandBuilder ocb = null;
try
{
con = new SQLiteConnection(conString);
command = con.CreateCommand();
command.CommandText = "select * from TestDevices";
oda = new SQLiteDataAdapter(command);
ocb = new SQLiteCommandBuilder(oda);
oda.UpdateCommand = ocb.GetUpdateCommand();
oda.InsertCommand = ocb.GetInsertCommand();
oda.DeleteCommand = ocb.GetDeleteCommand();
byte[] rcv = ACBTester.genDeviceInfo();
DataTable dt = TableCenter.getDeviceTable();
DataRow deviceRow = dt.NewRow();
deviceRow["DeviceCode"] = "DW45";
deviceRow["FactoryCode"] = "Shihlin Electirc";
deviceRow["ProductNumber"] = (rcv[7] * 256 + rcv[8]).ToString("D4") + (rcv[9] * 256 + rcv[10]).ToString("D4");
deviceRow["ProductDate"] = new DateTime(rcv[11] * 256 + rcv[12], rcv[13], rcv[14]);
deviceRow["Inm"] = rcv[17] * 256 + rcv[18];
deviceRow["In"] = rcv[19] * 256 + rcv[20];
deviceRow["Imcr"] = rcv[21] + rcv[22];
int Inm = rcv[17] * 256 + rcv[18];
int nInm = Inm == 2000 ? 1 : (Inm == 3200 ? 2 : 3);
deviceRow["BreakType"] = string.Format("XSIC-P{0}G{1}", rcv[16], nInm);
deviceRow["Version"] = "";
deviceRow["ControllerType"] = "";
deviceRow["Fn"] = rcv[27] * 256 + rcv[28];
deviceRow["SaveTime"] = DateTime.Now;
dt.Rows.Add(deviceRow);
int n = oda.Update(dt);
}
catch
{ }
finally
{
if (ocb != null)
{
ocb.Dispose();
}
if (oda != null)
{
oda.Dispose();
}
if (command != null)
{
command.Dispose();
}
if (con != null)
{
con.Close();
}
}
}
public List <LoginFieldS> getInfoFromDB(string colName, string username, string title)
{
List <LoginFieldS> lp = new List <LoginFieldS>();
try
{
Debug.Print("getInfoFromDB");
string db_field = "logins";
string sql = "";
string colNameWithoutSlash = "";
DataTable DB = new DataTable();
if (colName.Substring(colName.Length - 1) == "/")
{
colNameWithoutSlash = colName.Substring(0, colName.Length - 1);
}
else
{
colNameWithoutSlash = colName;
}
if (colName.Substring(colName.Length - 1) == "/")
{
colNameWithoutSlash = colName.Substring(0, colName.Length - 1);
}
else
{
colNameWithoutSlash = colName;
}
if (title != "")
{
sql = string.Format("SELECT DISTINCT * FROM {0} where ( origin_url=\"{1}\" or origin_url=\"{2}/\") and username_value=\"{3}\" and title=\"{4}\" ", db_field, encode.Base64Encode(colNameWithoutSlash), encode.Base64Encode(colNameWithoutSlash), encode.Base64Encode(username), encode.Base64Encode(title));
}
else if ((username == "" || username == null) && colName != "*")
{
sql = string.Format("SELECT DISTINCT * FROM {0} where origin_url=\"{1}\" or origin_url=\"{2}/\" ", db_field, encode.Base64Encode(colNameWithoutSlash), encode.Base64Encode(colNameWithoutSlash));
}
else if (colName == "*" && username != "")
{
//sql = string.Format("SELECT DISTINCT * FROM {0} where username_value LIKE '{1}%' ", db_field, username.ToString());
}
else if (colName == "*")
{
sql = string.Format("SELECT DISTINCT * FROM {0} ", db_field);
}
else
{
sql = string.Format("SELECT DISTINCT * FROM {0} where ( origin_url=\"{1}\" or origin_url=\"{2}/\") and username_value=\"{3}\"", db_field, encode.Base64Encode(colNameWithoutSlash), encode.Base64Encode(colNameWithoutSlash), encode.Base64Encode(username));
// sql = string.Format("SELECT DISTINCT * FROM {0} where (action_url=\"{1}\" or origin_url=\"{2}\" or origin_url=\"{3}/\") and username_value=\"{4}\"", db_field, (action == "" || action == null) ? "{}" : action, colNameWithoutSlash, colNameWithoutSlash, username);
}
using (SQLiteConnection connect = new SQLiteConnection(ConnectionString))
{
connect.Open();
SQLiteCommand command = new SQLiteCommand(sql, connect);
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(DB);
int rows = DB.Rows.Count;
for (int i = 0; i < rows; i++)
{
//byte[] byteArray = (byte[])DB.Rows[i][3];
// byte[] decrypted = DPAPI.Decrypt(byteArray, entropy, out description);
lp.Add(new LoginFieldS {
url = (DB.Rows[i][0] == DBNull.Value) ? string.Empty : encode.Base64Decode(DB.Rows[i][0].ToString()), userName = (DB.Rows[i][2] == DBNull.Value) ? string.Empty : encode.Base64Decode((string)DB.Rows[i][2]), title = (DB.Rows[i][4] == DBNull.Value) ? string.Empty : encode.Base64Decode((string)DB.Rows[i][4]), appID = (DB.Rows[i][5] == DBNull.Value) ? string.Empty : encode.Base64Decode(DB.Rows[i][5].ToString()), last_used = (DB.Rows[i][6] == DBNull.Value) ? string.Empty : DB.Rows[i][6].ToString(), rowid = (DB.Rows[i][7] == DBNull.Value) ? string.Empty : DB.Rows[i][7].ToString(), imageData = (DB.Rows[i][8] == DBNull.Value) ? null : (byte[])DB.Rows[i][8]
});
}
adapter.Dispose();
connect.Close();
}
// Writer.Close();
return(lp);
}
catch (Exception ex)
{
return(lp);
}
}
/// <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;
}
/// <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;
}
/// <summary>
/// Fetching user passwords from browser
/// </summary>
/// <param name="bas">using for get handle hid device</param>
/// <returns>List of users</returns>
public List <LoginFieldS> getDataFromChrome()
{
List <LoginFieldS> userlist = new List <LoginFieldS>();
try
{
string path = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
var profiles = FindProfileName(path);
foreach (var profile in profiles)
{
string db_way = path + "\\Google\\Chrome\\User Data\\" + profile + "\\Login Data";
string db_way2 = path + "\\Google\\Chrome\\User Data\\" + profile + "\\Login Data.hio";
File.Copy(db_way, db_way2, true);
string db_field = "logins";
string sql;
byte[] entropy = null;
string description;
DataBase dbLocal = new DataBase();
string ConnectionString = "data source=" + db_way2 + ";New=True;UseUTF16Encoding=True";
DataTable DB = new DataTable();
sql = string.Format("SELECT DISTINCT * FROM {0}", db_field);
using (SQLiteConnection connect = new SQLiteConnection(ConnectionString))
{
SQLiteCommand command = new SQLiteCommand(sql, connect);
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(DB);
int rows = DB.Rows.Count;
for (int i = 0; i < rows; i++)
{
Trace.WriteLine("DB.Rows.Count: " + rows);
byte[] byteArray = (byte[])DB.Rows[i][5];
byte[] decrypted = DPAPI.Decrypt(byteArray, entropy, out description);
if (((string)DB.Rows[i][7]).Split(':')[0] == "android")
{
continue;
}
string username = DB.Rows[i][3].ToString().GetUTF8String(64);
string password = (new UTF8Encoding(true).GetString(decrypted)).ToString().GetUTF8String(64);
string title = HIOStaticValues.getTitleNameURI(DB.Rows[i][7].ToString()).GetUTF8String(64);
string url = HIOStaticValues.getDomainNameURI(DB.Rows[i][7].ToString()).GetUTF8String(256);
if (password != "" && (string)DB.Rows[i][3] != "")
{
if (dbLocal.getInfoFromDB(url, username, "").Count == 0)
{
userlist.Add(new LoginFieldS {
password = new UTF8Encoding(true).GetString(decrypted), userName = (string)DB.Rows[i][3], title = title, url = url
});
}
}
}
adapter.Dispose();
connect.Close();
}
}
return(userlist);
}
catch (Exception ex)
{
throw ex;
}
}
private void frmAdminView_Load(object sender, EventArgs e)
{
// VIEW AIRPORTS
string mySQL = $@"SELECT * FROM airport";
SQLiteDataAdapter Da = new SQLiteDataAdapter(mySQL, conn);
DataTable ds = new DataTable();
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
Da.Fill(ds);
Da.Dispose();
dgvAirports.DataSource = ds;
dgvAirports.Columns["airportID"].Visible = true;
dgvAirports.Columns["departAirportID"].Visible = false;
dgvAirports.Columns["destAirportID"].Visible = false;
dgvAirports.RowHeadersVisible = false;
dgvAirports.ColumnHeadersVisible = true;
dgvAirports.Columns["country"].Width = (dgvAirports.Width / 2);
dgvAirports.Columns["city"].Width = (dgvAirports.Width / 2);
dgvAirports.ReadOnly = true;
conn.Close();
// VIEW BOOKINGS
string mySQL2 = $@"SELECT * FROM booking";
SQLiteDataAdapter Da2 = new SQLiteDataAdapter(mySQL2, conn);
DataTable ds2 = new DataTable();
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
Da2.Fill(ds2);
Da2.Dispose();
dataGridViewBookings.DataSource = ds2;
dataGridViewBookings.Columns["bookingID"].Visible = false;
dataGridViewBookings.Columns["bookingRef"].Visible = true;
dataGridViewBookings.Columns["customerID"].Visible = false;
dataGridViewBookings.Columns["bookingDate"].Visible = false;
dataGridViewBookings.Columns["dateFlight1"].Visible = true;
dataGridViewBookings.Columns["deptFlight1"].Visible = true;
dataGridViewBookings.Columns["destFlight1"].Visible = true;
dataGridViewBookings.Columns["dateFlight2"].Visible = true;
dataGridViewBookings.Columns["deptFlight2"].Visible = true;
dataGridViewBookings.Columns["destFlight2"].Visible = true;
dataGridViewBookings.Columns["seatAllocationID"].Visible = false;
dataGridViewBookings.RowHeadersVisible = false;
dataGridViewBookings.ColumnHeadersVisible = true;
dataGridViewBookings.ReadOnly = true;
conn.Close();
}
public DataSet ExecuteDataSet(string commandText,
List <CommandParameter> parameterList, string[] tableNameArray)
{
SQLiteCommand cmd = null;
SQLiteDataAdapter adapter = null;
try
{
_connection.Open();
cmd = new SQLiteCommand(commandText, _connection);
foreach (CommandParameter item in parameterList)
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = item.ParameterName;
parameter.Value = item.Value;
cmd.Parameters.Add(parameter);
}
adapter = new SQLiteDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds != null && ds.Tables.Count > 0)
{
for (int i = 0; i < ds.Tables.Count; i++)
{
if (tableNameArray.Length <= i)
{
break;
}
ds.Tables[i].TableName = tableNameArray[i];
}
}
return(ds);
}
catch (Exception exception)
{
string logMessage = commandText;
if (parameterList != null)
{
logMessage += Environment.NewLine + JsonConvert.SerializeObject(parameterList);
}
_log.Error(logMessage, exception);
throw exception;
}
finally
{
_connection.Close();
if (cmd != null)
{
cmd.Dispose();
}
if (adapter != null)
{
adapter.Dispose();
}
}
}
public void Form1_Load(object sender, EventArgs e)
{
this.TopMost = true;
this.FormBorderStyle = FormBorderStyle.None;
this.WindowState = FormWindowState.Maximized;
int diferenta = this.Width - this.cbRuta.Size.Width;
this.cbRuta.Location = new System.Drawing.Point(diferenta / 2, 1 * this.Height / 6);
this.cbSch.Location = new System.Drawing.Point(diferenta / 2, 2 * this.Height / 6);
this.cbTran.Location = new System.Drawing.Point(diferenta / 2, 3 * this.Height / 6);
this.btnOk.Location = new System.Drawing.Point(this.Width / 2 - 139, 4 * this.Height / 6);
this.btnOk.Size = new System.Drawing.Size(250, this.Height / 6);
this.button1.Location = new System.Drawing.Point(this.Width - 108, 20);
try
{
DataTable dataTableRuta = new DataTable();
string query = "SELECT DISTINCT ruta FROM rute WHERE ruta != 'N/A' AND ruta != '' ORDER BY ruta";
SQLiteCommand cmd = new SQLiteCommand(query, sqlLiteCon);
sqlLiteCon.Open();
SQLiteDataAdapter dadapter = new SQLiteDataAdapter(cmd);
dadapter.Fill(dataTableRuta);
sqlLiteCon.Close();
dadapter.Dispose();
foreach (DataRow row in dataTableRuta.Rows)
{
this.cbRuta.Items.AddRange(new object[] { row["ruta"] });
}
}
catch (Exception)
{
if (MessageBox.Show("Aplicația nu are acces la internet! \nAplicația nu găsește baza de date locală! \nPentru a funcționa vă rugăm restartați aplicația. \n \nDoriți să o restartați acum?", "Atenție !!!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
restart = "DA";
Application.Restart();
}
else
{
timer1.Enabled = true;
}
}
try
{
DataTable dataTableTran = new DataTable();
string query1 = "SELECT DISTINCT autobuz FROM autobuz";
SQLiteCommand cmd1 = new SQLiteCommand(query1, sqlLiteCon);
sqlLiteCon.Open();
SQLiteDataAdapter dadapter1 = new SQLiteDataAdapter(cmd1);
dadapter1.Fill(dataTableTran);
sqlLiteCon.Close();
dadapter1.Dispose();
foreach (DataRow row in dataTableTran.Rows)
{
this.cbTran.Items.AddRange(new object[] { row["autobuz"] });
}
}
catch (Exception)
{
}
try
{
sqlLiteCon.Open();
pc = Environment.MachineName;
SQLiteDataReader sqlDataReader = new SQLiteCommand("SELECT autobuz, ruta FROM autobuz WHERE pc='" + pc + "'", sqlLiteCon).ExecuteReader();
bool flag = sqlDataReader.Read();
if (flag)
{
this.cbRuta.SelectedItem = sqlDataReader.GetValue(1).ToString();
this.cbRuta.ForeColor = System.Drawing.Color.Black;
this.cbTran.SelectedItem = sqlDataReader.GetValue(0).ToString();
this.cbTran.ForeColor = System.Drawing.Color.Black;
}
sqlLiteCon.Close();
}
catch (Exception)
{
}
TimeSpan start1a = new TimeSpan(24, 0, 0);
TimeSpan end1a = new TimeSpan(06, 0, 0);
TimeSpan start1b = new TimeSpan(14, 30, 0);
TimeSpan end1b = new TimeSpan(16, 0, 0);
TimeSpan start2a = new TimeSpan(08, 0, 0);
TimeSpan end2a = new TimeSpan(14, 30, 0);
TimeSpan start2b = new TimeSpan(23, 0, 0);
TimeSpan end2b = new TimeSpan(24, 0, 0);
TimeSpan start3a = new TimeSpan(06, 0, 0);
TimeSpan end3a = new TimeSpan(06, 30, 0);
TimeSpan start3b = new TimeSpan(17, 0, 0);
TimeSpan end3b = new TimeSpan(23, 0, 0);
TimeSpan startAdmina = new TimeSpan(06, 30, 0);
TimeSpan endAdmina = new TimeSpan(08, 0, 0);
TimeSpan startAdminb = new TimeSpan(16, 0, 0);
TimeSpan endAdminb = new TimeSpan(17, 0, 0);
bool TimeBetween(DateTime datetime, TimeSpan start, TimeSpan end)
{
TimeSpan now = datetime.TimeOfDay;
if (start < end)
{
return(start <= now && now <= end);
}
return(!(end < now && now < start));
}
bool sch1a = TimeBetween(DateTime.Now, start1a, end1a);
bool sch1b = TimeBetween(DateTime.Now, start1b, end1b);
bool sch2a = TimeBetween(DateTime.Now, start2a, end2a);
bool sch2b = TimeBetween(DateTime.Now, start2b, end2b);
bool sch3a = TimeBetween(DateTime.Now, start3a, end3a);
bool sch3b = TimeBetween(DateTime.Now, start3b, end3b);
bool schAdmina = TimeBetween(DateTime.Now, startAdmina, endAdmina);
bool schAdminb = TimeBetween(DateTime.Now, startAdminb, endAdminb);
if (sch1a || sch1b)
{
this.cbSch.SelectedItem = "1";
this.cbSch.ForeColor = System.Drawing.Color.Black;
}
if (sch2a || sch2b)
{
this.cbSch.SelectedItem = "2";
this.cbSch.ForeColor = System.Drawing.Color.Black;
}
if (sch3a || sch3b)
{
this.cbSch.SelectedItem = "3";
this.cbSch.ForeColor = System.Drawing.Color.Black;
}
if (schAdmina || schAdminb)
{
this.cbSch.SelectedItem = "Administrativ";
this.cbSch.ForeColor = System.Drawing.Color.Black;
}
}
private void scanare_cartela()
{
if (alocare_locuri != "DA")
{
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader3 = new SQLiteCommand("SELECT locuri_rezerva FROM autobuz WHERE autobuz='" + Form1.tran + "'", sqlLiteCon).ExecuteReader();
bool flag3 = sqlDataReader3.Read();
if (flag3)
{
nr_locuri_rezerva = Convert.ToInt32(sqlDataReader3.GetValue(0).ToString());
}
sqlLiteCon.Close();
locuri_rezerva = new int[nr_locuri_rezerva];
for (int i = 0, nr = 1; i < nr_locuri_rezerva; i++, nr += 1)
{
locuri_rezerva[i] = nr;
}
}
//
DataTable dataTable = new DataTable();
string query = "SELECT loc FROM transport WHERE loc!='' AND ruta='" + Form1.ruta + "' AND schimb='" + Form1.sch + "' AND transport='" + Form1.tran + "' AND SUBSTR(data_transport,0,11) = DATE('NOW')";
SQLiteCommand cmd = new SQLiteCommand(query, sqlLiteCon);
sqlLiteCon.Open();
SQLiteDataAdapter dadapter = new SQLiteDataAdapter(cmd);
dadapter.Fill(dataTable);
sqlLiteCon.Close();
dadapter.Dispose();
foreach (DataRow row in dataTable.Rows)
{
locAfisat = Convert.ToInt32(row["loc"].ToString());
locuri_rezerva = locuri_rezerva.Where(val => val != locAfisat).ToArray();
}
///
timer3.Enabled = false;
numar_cartela = nr_cartela;
// selecteaza angajatul
numar_angajat = "";
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader = new SQLiteCommand("SELECT numar FROM personal WHERE numar_cartela='" + nr_cartela + "'", sqlLiteCon).ExecuteReader();
bool flag1 = sqlDataReader.Read();
if (flag1)
{
numar_angajat = sqlDataReader.GetValue(0).ToString();
numar_angajat_rezerva = numar_angajat;
}
sqlLiteCon.Close();
// daca are cartela
if (numar_angajat != "")
{
loc = 0;
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader2 = new SQLiteCommand("SELECT loc FROM rute WHERE nevoie_transport LIKE 'da' AND loc!='' AND ruta='" + Form1.ruta + "' AND schimb='" + Form1.sch + "' AND DATE('NOW') BETWEEN SUBSTR(data,0,11) AND SUBSTR(data_final,0,11) AND numar_angajat='" + numar_angajat + "'", sqlLiteCon).ExecuteReader();
bool flag2 = sqlDataReader2.Read();
if (flag2)
{
loc = Convert.ToInt32(sqlDataReader2.GetValue(0).ToString());
}
sqlLiteCon.Close();
if (loc != 0)
{
timer1.Enabled = true;
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader3 = new SQLiteCommand("SELECT loc FROM transport WHERE loc!='' AND numar_angajat='" + numar_angajat + "' AND numar_angajat!='' AND ruta='" + Form1.ruta + "' AND schimb='" + Form1.sch + "' AND transport='" + Form1.tran + "'AND SUBSTR(data_transport,0,11) = DATE('NOW')", sqlLiteCon).ExecuteReader();
bool flag3 = sqlDataReader3.Read();
if (flag3)
{
locAfisat = Convert.ToInt32(sqlDataReader3.GetValue(0).ToString());
}
else
{
locAfisat = 0;
}
sqlLiteCon.Close();
if (locAfisat != 0) //if (scanati.Contains(numar_angajat))
{
locAfisat = loc;
loc_afisat();
}
else
{
loc_gasit();
}
}
else // daca se scaneaza a doua oara dupa ce l-ai acceptat
{
sqlLiteCon.Open();
SQLiteDataReader sqlDataReader3 = new SQLiteCommand("SELECT loc FROM transport WHERE loc!='' AND numar_angajat='" + numar_angajat + "' AND numar_angajat!='' AND ruta='" + Form1.ruta + "' AND schimb='" + Form1.sch + "' AND transport='" + Form1.tran + "'AND SUBSTR(data_transport,0,11) = DATE('NOW')", sqlLiteCon).ExecuteReader();
bool flag3 = sqlDataReader3.Read();
if (flag3)
{
locAfisat = Convert.ToInt32(sqlDataReader3.GetValue(0).ToString());
}
else
{
locAfisat = 0;
}
sqlLiteCon.Close();
if (locAfisat != 0)
{
timer1.Enabled = true;
loc_afisat();
}
else
{
loc_negasit();
}
}
}
else // daca nu are cartela alocata
{
cartela_nealocata();
}
}
public ObservableCollection <QuoteItem> LoadQuoteItemData()
{
var items = new ObservableCollection <QuoteItem>();
try
{
connection = new SQLiteConnection(connection_String);
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = string.Format("SELECT * FROM QTE_ITEMS");
adapter = new SQLiteDataAdapter(cmd);
ds = new DataSet();
adapter.Fill(ds, "QTE_ITEM");
foreach (DataRow dr in ds.Tables[0].Rows)
{
int cleaned_qty = 0;
double cleaned_rate = 0.00;
int cleaned_grouping = 1;
bool cleaned_taxable = false;
bool cleaned_discountable = false;
bool cleaned_printable = false;
double cleaned_line_total = 0.00;
double cleaned_tax_total = 0.00;
int cleaned_tab_index = 0;
int cleaned_row_index = 0;
if (Int32.TryParse(dr[0].ToString(), out cleaned_qty))
{
}
if (Double.TryParse(dr[2].ToString(), out cleaned_rate))
{
}
if (Int32.TryParse(dr[4].ToString(), out cleaned_grouping))
{
}
if (Boolean.TryParse(dr[5].ToString(), out cleaned_taxable))
{
}
if (Boolean.TryParse(dr[6].ToString(), out cleaned_discountable))
{
}
if (Boolean.TryParse(dr[7].ToString(), out cleaned_printable))
{
}
if (Double.TryParse(dr[9].ToString(), out cleaned_line_total))
{
}
if (Double.TryParse(dr[10].ToString(), out cleaned_tax_total))
{
}
if (Int32.TryParse(dr[11].ToString(), out cleaned_tab_index))
{
}
if (Int32.TryParse(dr[12].ToString(), out cleaned_row_index))
{
}
items.Add(new QuoteItem
{
qty = cleaned_qty,
item = dr[1].ToString(),
rate = cleaned_rate,
descr = dr[3].ToString(),
grouping = cleaned_grouping,
//taxable = cleaned_taxable,
//discountable = cleaned_discountable,
//printable = cleaned_printable,
taxable = (dr[5].ToString().ToLower() == "1" ? true : false),
discountable = (dr[6].ToString().ToLower() == "1" ? true : false),
printable = (dr[7].ToString().ToLower() == "1" ? true : false),
jobno = dr[8].ToString(),
line_total = cleaned_line_total,
tax_total = cleaned_tax_total,
tab_index = cleaned_tab_index,
row_index = cleaned_row_index
});
//Trace.WriteLine(dr[1].ToString() + " created in quote_items");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
ds = null;
adapter.Dispose();
connection.Close();
connection.Dispose();
}
return(items);
}
private DataTable GetDataTable(string sqlCommand)
{
if (Conn != null)
{
try {
var ad = new SQLiteDataAdapter(sqlCommand, Conn);
try {
var ds = new DataSet();
ad.Fill(ds);
var tbOri = ds.Tables[0];
var tbNew = tbOri.Clone();
//以两个list记录列数据类型是/否byte[]的序列号;
var bsIndex = new List <int>();
var nbsIndex = new List <int>();
for (int i = 0; i < tbNew.Columns.Count; i++)
{
if (tbNew.Columns[i].DataType == typeof(byte[]))
{
tbNew.Columns[i].DataType = typeof(string);
bsIndex.Add(i);
}
else
{
nbsIndex.Add(i);
}
}
for (int ri = 0; ri < tbOri.Rows.Count; ri++)
{
var newRow = tbNew.NewRow();
bsIndex.ForEach(p => {
var bs = tbOri.Rows[ri][p] as byte[];
if (bs != null)
{
if (bs.Length < 24)
{
newRow[p] = bs.ConvertToHexFormat();
}
else
{
newRow[p] = $"{FindResourceString("BinaryData")}({FindResourceString("BinaryLength")}:{bs.Length})";
}
}
});
nbsIndex.ForEach(p => {
newRow[p] = tbOri.Rows[ri][p];
});
tbNew.Rows.Add(newRow);
}
return(tbNew);
}
catch {
throw;
}
finally {
ad.Dispose();
}
}
catch (Exception ex) {
Logger.WriteLine($"{nameof(NormalDBShowerViewModel)}");
CDFCMessageBox.Show($"{ex.Message}");
}
}
return(null);
}
public ObservableCollection <QuoteHeader> LoadQuoteHeaderData()
{
var headers = new ObservableCollection <QuoteHeader>();
try
{
connection = new SQLiteConnection(connection_String);
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = string.Format("SELECT * FROM QTE_HDR ORDER BY jobno");
adapter = new SQLiteDataAdapter(cmd);
ds = new DataSet();
adapter.Fill(ds, "QTE_HDR");
foreach (DataRow dr in ds.Tables[0].Rows)
{
int cleaned_days = 0;
double cleaned_value = 0.00;
if (Int32.TryParse(dr[8].ToString(), out cleaned_days))
{
}
if (Double.TryParse(dr[17].ToString(), out cleaned_value))
{
}
//if(!string.IsNullOrEmpty(dr[1].ToString())){ cleaned_qt_date= DateTime.Parse(dr[1].ToString());}
//if(!string.IsNullOrEmpty(dr[16].ToString())){ cleaned_est_start_date= DateTime.Parse(dr[16].ToString());}
//if(!string.IsNullOrEmpty(dr[17].ToString())){ cleaned_est_stop_date= DateTime.Parse(dr[17].ToString());}
headers.Add(new QuoteHeader
{
quoteno = dr[0].ToString(),
jobno = dr[0].ToString(),
qt_date = dr[1].ToString(),
cust = dr[2].ToString(),
cust_contact = dr[3].ToString(),
cust_phone = dr[4].ToString(),
cust_email = dr[5].ToString(),
loc = dr[6].ToString(),
salesman = dr[7].ToString(),
days_est = cleaned_days,
status = dr[9].ToString(),
jobtype = dr[10].ToString(),
pipe_line_size = dr[11].ToString(),
pipe_length = dr[12].ToString(),
pressure = dr[13].ToString(),
endclient = dr[14].ToString(),
supervisor = dr[15].ToString(),
est_start_date = dr[16].ToString(),
est_stop_date = dr[17].ToString(),
value = cleaned_value
});
//Trace.WriteLine(dr[0].ToString() + " created in quote_headers");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
ds = null;
adapter.Dispose();
connection.Close();
connection.Dispose();
}
return(headers);
}
private void SetDataBase(string dbPath)
{
SQLiteDataAdapter adapter = null;
try
{
this.lblDBPath.Text = dbPath;
strConn = string.Format(@"Data Source={0};Version=3;", dbPath);
string sql = "Select * from TB_Tree; Select * from TB_Line;";
adapter = new SQLiteDataAdapter(sql, strConn);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
DataTable dt1 = ds.Tables[1];
totalLine = Convert.ToInt16(dt1.Rows[0][0]);
totalSection = Convert.ToInt16(dt1.Rows[0][1]);
cmbLine.Items.Clear();
cmbSection.Items.Clear();
cmbLine.Items.Add("전체");
cmbSection.Items.Add("전체");
//// cmbLine에 라인번호 셋팅
for (int i = 0; i < totalLine; i++)
{
cmbLine.Items.Add(i + 1);
cmbLine2.Items.Add(i + 1);
}
if (cmbLine.Items.Count > 0)
{
cmbLine.SelectedIndex = 0;
cmbLine2.SelectedIndex = 0;
}
//// cmbLine에 섹션번호 셋팅
for (int i = 0; i < totalSection; i++)
{
cmbSection.Items.Add(i + 1);
cmbSection2.Items.Add(i + 1);
}
if (cmbSection.Items.Count > 0)
{
cmbSection.SelectedIndex = 0;
cmbSection2.SelectedIndex = 0;
}
if (dt != null)
{
this.dgvTree.DataSource = dt.DefaultView;
}
//// 기본 정보 설정
this.txt_lineNo.Text = totalLine.ToString();
this.txt_SectionNo.Text = totalSection.ToString();
this.txtEmail.Text = dt1.Rows[0][2].ToString();
_xmlConfig.XmlWriteStr("Config/DB_PATH", dbPath);
}
catch (Exception ex)
{
Log.Instance.ErrorLog(ex);
}
finally
{
adapter.Dispose();
adapter = null;
}
}
public static UltimoRegistro BuscaUltimoRegistro(int Matricula)
{
UltimoRegistro ultimoRegistro = new UltimoRegistro();
if (Database.CN.State == ConnectionState.Closed)
{
Database.StringConexao = @"Data Source = C:\Program Files\Autentica Softwares\ATCHoras\Dados\Dados.db" + ";Version=3";
Database.CN.ConnectionString = Database.StringConexao;
Database.CN.Open();
}
string Comando;
SQLiteCommand CMD = new SQLiteCommand();
DataTable DT = new DataTable();
SQLiteDataAdapter DA = new SQLiteDataAdapter();
DataRow DR;
Comando = "Select * from Obras";
try
{
CMD.CommandText = Comando;
CMD.Connection = Database.CN;
DA.SelectCommand = CMD;
DA.Fill(DT);
}
catch
{
ultimoRegistro.Erro = "Erro de servidor, Falha ao Consultar o banco de dados";
return(ultimoRegistro);
}
ultimoRegistro.obras = new Obras[DT.Rows.Count];
if (DT.Rows.Count != 0)
{
for (int i = 0; i < (DT.Rows.Count); i++)
{
Obras obras = new Obras();
DR = DT.Rows[i];
obras.Ano = Convert.ToInt32(DR[0]);
obras.Codigo = Convert.ToInt32(DR[1]);
obras.Nome = DR[2].ToString();
obras.Descricao = DR[3].ToString();
obras.MatriculaGerente = Convert.ToInt32(DR[4]);
obras.Descricao = DR[5].ToString();
ultimoRegistro.obras[i] = obras;
}
}
DA.Dispose();
DT.Dispose();
CMD.Dispose();
DA = new SQLiteDataAdapter();
DT = new DataTable();
CMD = new SQLiteCommand();
Comando = "Select * from Funcionarios where NivelAcesso = 5";
try
{
CMD.CommandText = Comando;
CMD.Connection = Database.CN;
DA.SelectCommand = CMD;
DA.Fill(DT);
}
catch (Exception e)
{
ultimoRegistro.Erro = "Erro de servidor, Falha ao Consultar o banco de dados";
return(ultimoRegistro);
}
ultimoRegistro.gerentes = new Funcionario[DT.Rows.Count];
if (DT.Rows.Count != 0)
{
for (int i = 0; i < (DT.Rows.Count); i++)
{
Funcionario funcionario = new Funcionario();
DR = DT.Rows[i];
funcionario.Nome = DR[0].ToString();
funcionario.Usuario = DR[1].ToString();
funcionario.Matricula = Convert.ToInt32(DR[2]);
funcionario.Departamento = DR[3].ToString();
funcionario.Funcao = DR[4].ToString();
funcionario.Senha = DR[5].ToString();
funcionario.IndiceBiometria = Convert.ToInt32(DR[6]);
funcionario.NivelAcesso = Convert.ToInt32(DR[7]);
funcionario.Status = DR[8].ToString();
funcionario.Email = DR[9].ToString();
funcionario.Telefone = DR[10].ToString();
ultimoRegistro.gerentes[i] = funcionario;
}
}
DA.Dispose();
DT.Dispose();
CMD.Dispose();
DA = new SQLiteDataAdapter();
DT = new DataTable();
CMD = new SQLiteCommand();
Comando = "Select * from Atividades";
try
{
CMD.CommandText = Comando;
CMD.Connection = Database.CN;
DA.SelectCommand = CMD;
DA.Fill(DT);
}
catch
{
ultimoRegistro.Erro = "Erro de servidor, Falha ao Consultar o banco de dados";
return(ultimoRegistro);
}
ultimoRegistro.atividades = new Atividades[DT.Rows.Count];
if (DT.Rows.Count != 0)
{
for (int i = 0; i < (DT.Rows.Count); i++)
{
Atividades atividades = new Atividades();
DR = DT.Rows[i];
atividades.Codigo = Convert.ToInt32(DR[0]);
atividades.Descricao = DR[1].ToString();
atividades.FuncoesCapacitadas = DR[2].ToString();
ultimoRegistro.atividades[i] = atividades;
}
}
DA.Dispose();
DT.Dispose();
CMD.Dispose();
DA = new SQLiteDataAdapter();
DT = new DataTable();
CMD = new SQLiteCommand();
Comando = "Select CodigoLancamento, AnoObra, CodigoObra, MatriculaGerente, Atividade from Horas where MatriculaFuncionario = " + Matricula + " Order By CodigoLancamento DESC";
try
{
CMD.CommandText = Comando;
CMD.Connection = Database.CN;
DA.SelectCommand = CMD;
DA.Fill(DT);
}
catch (Exception e)
{
ultimoRegistro.Erro = "Erro de servidor, Falha ao Consultar o banco de dados";
return(ultimoRegistro);
}
if (DT.Rows.Count != 0)
{
DR = DT.Rows[0];
ultimoRegistro.anoUltimaObra = Convert.ToInt32(DR[1]);
ultimoRegistro.codUltimaObra = Convert.ToInt32(DR[2]);
ultimoRegistro.matriculaUltimoGerente = Convert.ToInt32(DR[3]);
ultimoRegistro.ultimaAtividade = DR[4].ToString();
}
ultimoRegistro.Erro = "Sucesso!";
return(ultimoRegistro);
}
/// <summary>
///
/// </summary>
/// <param name="DbFilePath">数据库相对URL</param>
/// <returns></returns>
public static DataTable GetLogs(string DbFilePath
, string bTime = "", string eTime = "", string Type = "", string UserID = ""
, string UserName = "", string UserIP = "", string Message = "", string Data = ""
)
{
string DbPath = LogDbPathRoot + "\\" + DbFilePath;
SQLiteConnection conn = new SQLiteConnection("Data Source=" + DbPath + ";Pooling=true;Max Pool Size=100;");
conn.Open();
string sql = @"SELECT ID,Time,Level,Type,UserID,UserName,UserIP,Message FROM Logs";
List <string> whereList = new List <string>();
List <SQLiteParameter> parList = new List <SQLiteParameter>();
#region where par
if (!string.IsNullOrEmpty(bTime))
{
whereList.Add(@" Time >= @bTime ");
parList.Add(new SQLiteParameter("@bTime", bTime));
}
if (!string.IsNullOrEmpty(eTime))
{
whereList.Add(@" Time <= @eTime ");
parList.Add(new SQLiteParameter("@eTime", eTime));
}
if (!string.IsNullOrEmpty(Type))
{
whereList.Add(@" Type like '%'||@Type||'%' ");
parList.Add(new SQLiteParameter("@Type", Type));
}
if (!string.IsNullOrEmpty(UserID))
{
whereList.Add(@" UserID=@UserID ");
parList.Add(new SQLiteParameter("@UserID", UserID));
}
if (!string.IsNullOrEmpty(UserName))
{
whereList.Add(@" UserName like '%'||@UserName||'%' ");
parList.Add(new SQLiteParameter("@UserName", UserName));
}
if (!string.IsNullOrEmpty(UserIP))
{
whereList.Add(@" UserIP like '%'||@UserIP||'%' ");
parList.Add(new SQLiteParameter("@UserIP", UserIP));
}
if (!string.IsNullOrEmpty(Message))
{
whereList.Add(@" Message like '%'||@Message||'%' ");
parList.Add(new SQLiteParameter("@Message", Message));
}
if (!string.IsNullOrEmpty(Data))
{
whereList.Add(@" Data like '%'||@Data||'%' ");
parList.Add(new SQLiteParameter("@Data", Data));
}
#endregion
if (whereList.Count > 0)
{
sql += @" where " + string.Join(" and ", whereList);
}
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction();
try
{
if (parList.Count > 0)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parList.ToArray());
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable data = new DataTable();
adapter.Fill(data);
transaction.Commit();
adapter.Dispose();
return(data);
}
catch (Exception exp)
{
transaction.Rollback();
throw exp;
}
finally
{
transaction.Dispose();
cmd.Connection.Close();
}
}
}
/// <summary>
/// Execute a commandText (that returns a resultset and takes no parameters) against the database
/// </summary>
/// <param name="commandText">SQL command</param>
/// <returns>Returns a datatable containing the resultset generated by the command</returns>
/// <example>
/// DataTable dt= DatabaseConnection.ExecuteTable("SELECT * FROM emp");
/// </example>
public DataTable ExecuteDataTable(string commandText)
{
//if application is exiting the don't perform DB process
if (this.isAppClosing)
{
return(null);
}
if (!DoesDBConnectionExists())
{
return(null);
}
//creating the dataAdapter object to execute the dataTable.
SQLiteDataAdapter dataAdapter = null;
DataTable dt = new DataTable();
try
{
//open the data adapter.
dataAdapter = new SQLiteDataAdapter(commandText, _connection);
//load the data into the dataTable.
dt.BeginLoadData();
dataAdapter.Fill(dt);
dt.EndLoadData();
}
catch (Exception ex)
{
if (ex.Message.ToUpper().Trim() == "ORA-03114: NOT CONNECTED TO ORACLE" || ex.Message.ToUpper().Trim() == "ORA-12571: TNS:PACKET WRITER FAILURE")
{
////Try to db Re_Connected...
//if (MsgBoxNepali.Show("*]^fa];;Fu sg]S;g ePsf] %}g\\, s] tkfO{ km]<L *]^fa];;Fu sg]S;g ug]{ xf] M ", MessageBoxButtons.YesNo) == DialogResult.Yes)
//{
// DisconnectDatabase();
// if (!ConnectDatabase())
// {
// // MsgBoxNepali.Show("*]^fa];;Fu sg]S;g x'g ;s]g ", MessageBoxButtons.OK);
// //.Show("*]^fa];;Fu sg]S;g x'g ;s]g ", MessageBoxButtons.OK);
// this.Dispose();
// }
//}
//else
//{
// this.Dispose(); //if Re-connect not do the project dispose....
//}
}
LogTrace.WriteErrorLog(this.GetType().Name, MethodBase.GetCurrentMethod().Name, ex.ToString());
LogTrace.WriteDebugLog(this.GetType().Name, MethodBase.GetCurrentMethod().Name, commandText);
throw ex;
}
finally
{
commandText = string.Empty;
dataAdapter.Dispose();
dataAdapter = null;
}
return(dt);
}