private void draw()
{
//string drawQuery = "select top 1 name from LotContestents where ObjectGuid = '" + objectGuid + "' and LotDrawNumber = '" + rdr["ActiveDrawNumber"].ToString() + "' ORDER BY NEWID()";
string lotNumberSelectQuery = "select * from LotObjectsInWorld where ObjectGuid = '" + objectGuid + "'";
string winnerUpdateSql = "";
string lotNumber = "";
string winner = "";
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + HttpContext.Current.Server.MapPath("~/App_Data/Raffle.db")))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = lotNumberSelectQuery; // Set CommandText to our query that will create the table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
if (reader.Read())
{
string drawQuery = "select name from LotContestents where ObjectGuid = '" + objectGuid + "' and LotDrawNumber = '" + reader["LotDrawNumber"].ToString() + "' ORDER BY RANDOM() LIMIT 1";
lotNumber = reader["LotDrawNumber"].ToString();
reader.Close();
com.CommandText = drawQuery;
using (System.Data.SQLite.SQLiteDataReader reader1 = com.ExecuteReader())
{
if (reader1.Read())
{
winnerUpdateSql = "update LotDrawHistory set Winner = '" + reader1[0].ToString() + "' where ObjectGuid = '" + objectGuid + "' and LotDrawNumber = '" + lotNumber + "'";
winner = reader1[0].ToString();
reader1.Close();
com.CommandText = winnerUpdateSql;
com.ExecuteNonQuery();
pageResponse.Write(winner.Replace("$", " "));
pageResponse.Flush();
pageResponse.SuppressContent = true;
}
else
{
winnerUpdateSql = "update LotDrawHistory set Winner = '$$$' where ObjectGuid = '" + objectGuid + "' and LotDrawNumber = '" + lotNumber + "'";
reader1.Close();
com.CommandText = winnerUpdateSql;
com.ExecuteNonQuery();
pageResponse.Write("$$$"); // code for no winners
pageResponse.Flush();
pageResponse.SuppressContent = true;
}
}
}
}
con.Close();
}
}
this.closeRaffle(true);
}
private Boolean check(Boolean dontEmitResponse = false)
{
Boolean retValue = false;
string queryGetActiveRaffle = "select * from LotObjectsInWorld where ObjectGuid = '" + objectGuid + "'";
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + HttpContext.Current.Server.MapPath("~/App_Data/Raffle.db")))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = queryGetActiveRaffle; // Set CommandText to our query that will create the table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
if (reader.Read())
{
string sqlReadContestant = "select * from LotContestents where ObjectGuid = '" + objectGuid + "' and LotDrawNumber = '" + reader["LotDrawNumber"].ToString() + "' and Name = '" + name + "'";
reader.Close();
com.CommandText = sqlReadContestant;
using (System.Data.SQLite.SQLiteDataReader reader1 = com.ExecuteReader())
{
if (reader1.Read())
{
retValue = true;
if (!dontEmitResponse)
{
pageResponse.Write("ALREADYEXISTS");
pageResponse.Flush();
pageResponse.SuppressContent = true;
}
}
else
{
retValue = false;
if (!dontEmitResponse)
{
pageResponse.Write("DOESNOTEXISTS");
pageResponse.Flush();
pageResponse.SuppressContent = true;
}
}
}
}
}
}
con.Close();
}
return(retValue);
}
public List <AccountVerticalData> GetAccountQuarterWiseData(string Month, string Year, string AccountName)
{
List <Account> accountList = new List <Account>();
Account account;
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(Class1.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = "Select AccountID,AccountName,AccountDescription FROM Accounts"; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
account = new Account();
account.AccountID = Convert.ToInt32(reader["AccountID"]);
account.AccountName = Convert.ToString(reader["AccountName"]);
account.AccountDescription = Convert.ToString(reader["AccountDescription"]);
accountList.Add(account);
}
}
con.Close(); // Close the connection to the database
}
}
return(accountList);
}
catch (Exception ex)
{
return(null);
}
}
public List <String> checkProjects()
{
List <String> list = new List <String>();
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM LASTPROJECTS";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["address"]);
if (!File.Exists(reader["address"].ToString()))
{
list.Add(reader["address"].ToString());
}
}
}
con.Close();
}
}
if (list.Count > 0)
{
removeProject(list);
}
return(getLastProjects());
}
private void btn_cancel_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Sei sicuro di voler uscire? Le modifiche apportatate non verranno salvate",
"Attenzione", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
//return to stored data
if (preChargeNeed)
{
tableRate.Clear();
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
string command = "Select * from Rata where CodIscritto='" + (Ntess) + "'";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableRate.Rows.Add(reader["Costo"], reader["PagamentoAvv"], reader["DataPagam"]);
}
}
conn.Close();
}
}
}
this.Close();
}
}
/// <summary>
/// Returns the list of all the articles
/// </summary>
/// <returns>list of all the articles</returns>
public List <Models.Article> Get_Articles_List()
{
List <Models.Article> Articles = new List <Models.Article>();
System.Data.SQLite.SQLiteCommand cmd = SQL_Connection.CreateCommand();
cmd.CommandText = "SELECT RefArticle, Description, SousFamilles.Nom as SousFamille, m.Nom as Marque, PrixHT, Quantite FROM Articles natural join SousFamilles inner join Marques m on m.RefMarque = Articles.refMarque";
System.Data.SQLite.SQLiteDataReader Articles_Reader = cmd.ExecuteReader();
int Article_Index = Articles_Reader.GetOrdinal("RefArticle");
int Description_Index = Articles_Reader.GetOrdinal("Description");
int Sub_Familly_Index = Articles_Reader.GetOrdinal("SousFamille");
int Brand_Index = Articles_Reader.GetOrdinal("Marque");
int Price_Index = Articles_Reader.GetOrdinal("PrixHT");
int Quantity_Index = Articles_Reader.GetOrdinal("Quantite");
while (Articles_Reader.Read())
{
Models.Article A = new Models.Article();
A.Ref_Article = Articles_Reader.GetString(Article_Index);
A.Description = Articles_Reader.GetString(Description_Index);
A.Sub_Familly_Name = Articles_Reader.GetString(Sub_Familly_Index);
A.Brand_Name = Articles_Reader.GetString(Brand_Index);
A.Price_HT = Articles_Reader.GetFloat(Price_Index);
A.Quantity = Articles_Reader.GetInt32(Quantity_Index);
Articles.Add(A);
}
return(Articles);
}
private void chargeMessages()
{
int i = 0;
messageList.Clear();
clearStrings();
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
string command = "SELECT * FROM Messaggio JOIN User ON Messaggio.Id=User.Id ORDER BY Messaggio.Cod DESC";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (i == 10)
{
break;
}
messageList.Add(new Tuple <int, string, string, string>(Int16.Parse(reader["Cod"].ToString()), reader["Username"].ToString(), reader["Oggetto"].ToString(), reader["Text"].ToString()));
i++;
}
while (i < 10)
{
messageList.Add(new Tuple <int, string, string, string>(0, "Nessuno", "Nessuno", "Vuoto"));
i++;
}
}
conn.Close();
}
}
}
/// <summary>
/// Returns the article from its reference
/// </summary>
/// <param name="refArticle"></param>
/// <returns>the article</returns>
public Models.Article Get_Article(string refArticle)
{
Models.Article Article = new Models.Article();
System.Data.SQLite.SQLiteCommand cmd = SQL_Connection.CreateCommand();
cmd.CommandText = "SELECT RefArticle, Description, SousFamilles.Nom as SousFamille, m.Nom as Marque, PrixHT, Quantite FROM Articles natural join SousFamilles inner join Marques m on m.RefMarque = Articles.refMarque WHERE RefArticle = ?";
System.Data.SQLite.SQLiteParameter Id_Parameter = new System.Data.SQLite.SQLiteParameter();
Id_Parameter.Value = refArticle;
cmd.Parameters.Add(Id_Parameter);
System.Data.SQLite.SQLiteDataReader Articles_Reader = cmd.ExecuteReader();
int Article_Index = Articles_Reader.GetOrdinal("RefArticle");
int Description_Index = Articles_Reader.GetOrdinal("Description");
int Sub_Familly_Index = Articles_Reader.GetOrdinal("SousFamille");
int Brand_Index = Articles_Reader.GetOrdinal("Marque");
int Price_Index = Articles_Reader.GetOrdinal("PrixHT");
int Quantity_Index = Articles_Reader.GetOrdinal("Quantite");
if (Articles_Reader.Read())
{
Models.Article A = new Models.Article();
Article.Ref_Article = Articles_Reader.GetString(Article_Index);
Article.Description = Articles_Reader.GetString(Description_Index);
Article.Sub_Familly_Name = Articles_Reader.GetString(Sub_Familly_Index);
Article.Brand_Name = Articles_Reader.GetString(Brand_Index);
Article.Price_HT = Articles_Reader.GetFloat(Price_Index);
Article.Quantity = Articles_Reader.GetInt32(Quantity_Index);
}
return(Article);
}
private void displayPersonDetail(string personNo)
{
//
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr);
conn.Open();
//
string sql_findInfo = string.Format("select * from RentPersonInfo where personCardNum = '{0}'", personNo);
//
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql_findInfo;
cmd.Connection = conn;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
//借书人姓名
string personName = reader.GetString(0);
string personNum = reader.GetString(1);
string mobile = reader.GetString(3);
label_personDetail.Text = "借书人详细信息：" + "\r\n" + "\r\n" + "姓名：" + personName + "\r\n" + "\r\n" + "身份证号：" + personNum + "\r\n" + "\r\n" + "手机号：" + mobile;
}
//
reader.Close();
cmd.Dispose();
conn.Close();
conn.Dispose();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
private List <Stavka> LstStavki()
{
string joinQuery =
@"SELECT Stavka, Opis FROM [Stavki] AS S ORDER BY Stavka;";
List <Stavka> _lstStavka = new List <Stavka>();
string connStr = "Data Source = E:\\vs2017\\svPloter\\eOFIdata.db; Version = 3;";
//System.Data.SQLite.SQLiteConnection.CreateFile("Data Source = Config\\Data\\eOFIdata.db; Version = 3;");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(connStr))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = joinQuery; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
Stavka s = new Stavka();
s.eKod = reader["Stavka"].ToString();
s.Vrska = reader["Opis"].ToString();
_lstStavka.Add(s);
}
}
con.Close(); // Close the connection to the database
}
}
return(_lstStavka);
}
private void UnLoad_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrWhiteSpace(Rwebidtxt.Text))
{
MessageBox.Show("Error in WebId Field, Please Check it again.", "UnLoading Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
else
{
// MessageBox.Show("Thanks, This Part is UnderDevelopment, Please Wait for The next Update", "Work In Progress", MessageBoxButton.OK, MessageBoxImage.Information);
if (!string.IsNullOrWhiteSpace(Rwebidtxt.Text))
{
sqlcon.Open();
//MessageBox.Show(sqlcon.ToString());
string query = "Select password from Record where EmailID='" + Rwebidtxt.Text + "';";
System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(query, sqlcon);
com.ExecuteNonQuery();
System.Data.SQLite.SQLiteDataReader dr = com.ExecuteReader();
dr.Read();
Rpasstxt.Text = dr["Password"].ToString();
//MessageBox.Show("Congrats , You have successefully Loaded the Data ", "Data Loaded ", MessageBoxButton.OK, MessageBoxImage.Information);
sqlcon.Close();
}
}
}
// GET api/values
public IEnumerable <string> Get()
{
//return strings;
strings.Clear();
string selectQuery = @"SELECT * FROM MyTable";
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|/databaseFile2.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); // Open the connection to the database
com.CommandText = selectQuery; // Set CommandText to our query that will select all rows from the table
com.ExecuteNonQuery(); // Execute the query
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
strings.Add(reader["Key"] + ":" + reader["Value"]); // Display the value of the key and value column for every row
}
}
conn.Close(); // Close the connection to the database
}
}
return(strings);
}
public List <Currency> GetCurrencyList()
{
List <Currency> currencyList = new List <Currency>();
Currency currency;
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(BaseDbContext.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = "Select CurrencyID,CurrencyName,CurrencyFactor FROM Currencies"; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
currency = new Currency();
currency.CurrencyID = Convert.ToInt32(reader["CurrencyID"]);
currency.CurrencyName = Convert.ToString(reader["CurrencyName"]);
currency.CurrencyConversionFactor = Convert.ToDecimal(reader["CurrencyFactor"]);
currencyList.Add(currency);
}
}
con.Close(); // Close the connection to the database
}
}
return(currencyList);
}
catch (Exception ex)
{
return(null);
}
}
private List <string> getColFromTable(string colName, string tableName)
{
List <string> POs = new List <string>();
System.Data.SQLite.SQLiteConnection con = Sql.GetConnection();
//using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + this._dbasePath))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = "Select " + colName + " FROM " + tableName;
try
{
//con.Open();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
POs.Add(reader[colName] as string);
}
}
}
catch (SqlException ex)
{
MessageBox.Show(this, "Database error: " + ex.Message);
}
}
}
return(POs);
}
public KeyValuePair <int, int>[] GetForwardLinks(int itemId)
{
if (_getOutLinks == null)
{
return(new KeyValuePair <int, int> [0]);
}
_getOutLinks.Parameters[0].Value = itemId;
using (var rdr = _getOutLinks.ExecuteReader())
{
if (!rdr.Read())
{
return(new KeyValuePair <int, int> [0]);
}
var links = new List <KeyValuePair <int, int> >();
for (var i = 0; i < rdr.FieldCount; i += 2)
{
if (rdr.IsDBNull(i))
{
continue;
}
links.Add(new KeyValuePair <int, int>(
rdr.GetInt32(i),
rdr.GetInt32(i + 1))
);
}
return(links.ToArray());
}
}
private void loadInfo()
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
string command = "Select * from Iscritto left join Certificato on Iscritto.CodIscritto = Certificato.CodIscritto where Iscritto.CodIscritto = '" + nTessera + "'";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
tb_nome.Text = reader["Nome"].ToString();
tb_cognome.Text = reader["Cognome"].ToString();
tb_dataN.Text = reader["DataN"].ToString();
tb_residenza.Text = reader["Residenza"].ToString();
tb_via.Text = reader["Via"].ToString();
tb_recapito.Text = reader["Recapito"].ToString();
tb_nTessera.Text = reader["Ntessera"].ToString();
tb_dataIn.Text = reader["DataIn"].ToString();
tb_dataFine.Text = reader["DataFine"].ToString();
tb_costo.Text = reader["Costo"].ToString();
tb_ingressi.Text = reader["NIngressi"].ToString();
tb_pres.Text = reader["Presente"].ToString();
tb_dataScad.Text = reader["DataScadenza"].ToString();
}
}
conn.Close();
}
}
}
public List <ParticularsSubType> GetParticularsSubTypeList()
{
List <ParticularsSubType> subtypeList = new List <ParticularsSubType>();
ParticularsSubType subType;
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(BaseDbContext.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = "Select SubTypeID,SubTypeName FROM ParticularsSubType"; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
subType = new ParticularsSubType();
subType.SubTypeID = Convert.ToInt32(reader["SubTypeID"]);
subType.SubTypeName = Convert.ToString(reader["SubTypeName"]);
subtypeList.Add(subType);
}
}
con.Close(); // Close the connection to the database
}
}
return(subtypeList);
}
catch (Exception ex)
{
return(null);
}
}
public List <Particulars> GetParticularList()
{
List <Particulars> verticalList = new List <Particulars>();
Particulars vertical;
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(BaseDbContext.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = "Select ParticularID,ParticularName FROM Particulars"; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
vertical = new Particulars();
vertical.ParticularsID = Convert.ToInt32(reader["ParticularID"]);
vertical.ParticularsName = Convert.ToString(reader["ParticularName"]);
verticalList.Add(vertical);
}
}
con.Close(); // Close the connection to the database
}
}
return(verticalList);
}
catch (Exception ex)
{
return(null);
}
}
public SqLiteReader(string filepath, string tablename)
{
_tableName = tablename;
_connection = new System.Data.SQLite.SQLiteConnection("Data Source=" + filepath + ";Version=3;");
List <Int64> ids = new List <Int64>();
int idx = 0;
Open();
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand("SELECT rowid FROM '" + _tableName + "'", _connection))
{
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
object tmp = reader[0]; //why does "rowid" not work?
ids.Add((Int64)tmp);
}
}
}
}
Close();
_rowIds = ids.ToArray();
_columnNames = ColumnNames();
}
public static DataTable ExecuteCmdTable(System.Data.SQLite.SQLiteCommand cmd)
{
System.Data.ConnectionState origSate = cmd.Connection.State;
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
DataTable dt;
System.Data.SQLite.SQLiteDataReader dr;
dt = new DataTable();
dr = cmd.ExecuteReader();
dt.Load(dr);
dr.Close();
dr = null;
if (origSate == ConnectionState.Closed)
{
cmd.Connection.Close();
}
return(dt);
}
private List <string> getPartsList()
{
List <string> parts = new List <string>();
System.Data.SQLite.SQLiteConnection con = Sql.GetConnection();
//using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + _dbasePath))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = "Select * FROM PartsTable";
try
{
//con.Open();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
string c = (reader["Category"] as string) + "|" + (reader["Description"] as string) + "|" + (reader["Price"] as string);
parts.Add(c);
}
}
}
catch (SqlException ex)
{
MessageBox.Show(this, "Database error: " + ex.Message);
}
}
}
return(parts);
}
public bool isTableExists(string tablename)
{
Int32 count = 0;
string SQL = "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = '" + tablename + "'";
try
{
using (System.Data.SQLite.SQLiteConnection connection = new System.Data.SQLite.SQLiteConnection(ConnectionString, true))
{
connection.Open();
using (System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(SQL, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
if ((reader != null) && (reader.Read()))
{
count = reader.GetInt32(0);
}
}
}
connection.Close();
}
return(count > 0);
}
catch (Exception ex)
{
return(false);
}
}
public List <BU> GetBUList()
{
List <BU> accountList = new List <BU>();
BU account;
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(BaseDbContext.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = "Select BUID,BUName,BUDescription FROM BU"; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
account = new BU();
account.BUID = Convert.ToInt32(reader["BUID"]);
account.BUName = Convert.ToString(reader["BUName"]);
account.BUDescription = Convert.ToString(reader["BUDescription"]);
accountList.Add(account);
}
}
con.Close(); // Close the connection to the database
}
}
return(accountList);
}
catch (Exception ex)
{
return(null);
}
}
private void cb_selCorsoMod_SelectedIndexChanged(object sender, EventArgs e)
{
string selectedItem = cb_selCorsoMod.SelectedItem.ToString();
string codSelected = cb_selCorsoMod.SelectedItem.ToString().Split(':')[1].Substring(0, 2);
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
string command = "SELECT * From Corso where Id='" + codSelected + "'";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
tb_NomeMod.Text = reader["Nome"].ToString();
tb_dataInMod.Text = reader["DataIn"].ToString();
tb_dataFineMod.Text = reader["DataFin"].ToString();
}
}
conn.Close();
}
}
}
private List <string> getCompanyList()
{
List <string> COs = new List <string>();
System.Data.SQLite.SQLiteConnection con = Sql.GetConnection();
//using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + _dbasePath))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = "Select * FROM CompanyTable";
try
{
//con.Open();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
string c = (reader["Company"] as string) + "|" + (reader["Street1"] as string) + "|" + (reader["Street2"] as string) + "|" + (reader["City"] as string) + "|" + (reader["State"] as string) + "|" + (reader["Zip"] as string) + "|" + (reader["Phone"] as string) + "|" + (reader["Fax"] as string);
COs.Add(c);
}
}
}
catch (SqlException ex)
{
MessageBox.Show(this, "Database error: " + ex.Message);
}
}
}
return(COs);
}
public object[] Row(int index, string[] columnNames)
{
List <object> row = new List <object>();
string cmdstring = "SELECT [" + columnNames[0] + "]";
for (int i = 1; i < columnNames.Count(); i++)
{
cmdstring += ",[" + columnNames[i] + "]";
}
int idx = 0;
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(cmdstring + " FROM [" + _tableName + "] WHERE rowid=" + _rowIds[index], _connection))
{
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
foreach (string s in columnNames)
{
row.Add(reader[s]);
}
}
}
}
}
return(row.ToArray());
}
public List<String> getLastProjects()
{
List<String> list = new List<String>();
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM LASTPROJECTS";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["address"]);
list.Add(reader["address"].ToString());
}
}
con.Close();
}
}
return list;
}
static void Main1(string[] args)
{
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
Console.WriteLine(connstr.ToString());
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES('dotnetthink','mypassword')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n").Append("password:").Append(reader.GetString(1));
}
Console.WriteLine(sb.ToString());
}
private static void player_leaderboard(ChatMessage c)
{
long chan_id = get_channel_id(c.Channel);
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = @"SELECT nickname, points
FROM players
WHERE chan_id = @chanid
ORDER BY points DESC LIMIT 5";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@chanid", chan_id);
long pos = 1;
string list = "Leaderboard for #" + c.Channel + ": ";
using (System.Data.SQLite.SQLiteDataReader r = com.ExecuteReader())
{
while (r.Read())
{
list = list + " (" + pos + ") " + ((string)r["nickname"]).Trim() + " - " + r["points"] + ", ";
pos++;
}
}
// then tell the player their position
com.CommandText = @"SELECT count(*) AS rank
FROM players
WHERE chan_id = @chanid AND points > (SELECT points from players where nickname = @nickname)
ORDER BY points DESC";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@nickname", c.Username);
com.Parameters.AddWithValue("@chanid", chan_id);
object res = com.ExecuteScalar();
long rank = 0;
if (res != null)
{
rank = (long)res;
}
com.CommandText = @"SELECT count(*) from players where chan_id = @chanid";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@chanid", chan_id);
res = com.ExecuteScalar();
long total = 0;
if (res != null)
{
total = (long)res;
}
con.Close();
cl.SendWhisper(c.Username, list + " you are ranked " + (rank != 0?rank:total) + "/" + total);
}
verb("Leaderboard req from " + c.Username);
}
private void Btn_startPersonInfo_Click(object sender, System.EventArgs e)
{
listview_personInfos.Items.Clear();
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr);
conn.Open();
//
string sql_findInfo = "select * from RentPersonInfo order by personCardNum";
if (cb_personCondition.SelectedIndex == 1)
{
sql_findInfo = string.Format("select * from RentPersonInfo where personName = '{0}'", tb_personCondition.Text);
}
else if (cb_personCondition.SelectedIndex == 2)
{
sql_findInfo = string.Format("select * from RentPersonInfo where personCardNum = '{0}'", tb_personCondition.Text);
}
else if (cb_personCondition.SelectedIndex == 3)
{
sql_findInfo = string.Format("select * from RentPersonInfo where mobile = '{0}'", tb_personCondition.Text);
}
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql_findInfo;
cmd.Connection = conn;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
this.listview_personInfos.BeginUpdate();
while (reader.Read())
{
string personName = reader.GetString(0);
string personNum = reader.GetString(1);
string personCardNo = reader.GetString(2);
string mobile = reader.GetString(3);
//
ListViewItem lvi = new ListViewItem();
lvi.Text = personName;
lvi.SubItems.Add(personNum);
lvi.SubItems.Add(personCardNo);
lvi.SubItems.Add(mobile);
if (personName.Contains("已销卡"))
{
lvi.ForeColor = Color.ForestGreen;
}
listview_personInfos.Items.Add(lvi);
}
this.listview_personInfos.EndUpdate();
}
//
reader.Close();
cmd.Dispose();
conn.Close();
conn.Dispose();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
private void Btn_checkPersonCanRent_Click(object sender, System.EventArgs e)
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr);
conn.Open();
//
string sql = string.Format("select bookValue from RentBookInfo where personCardNum = '{0}'", tb_checkPersonCanRent.Text);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
//
int iCount = 0;
double value = 0.0;
if (reader.HasRows)
{
while (reader.Read())
{
value += System.Double.Parse(reader.GetString(0));
++iCount;
}
}
//
reader.Close();
cmd.Dispose();
conn.Close();
conn.Dispose();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
if (value >= 100 && value < 200 && iCount < 3)
{
string currentval = string.Format("读者当前已经借出总价大于100元的书,但还小于200元({0}元)，请注意", value);
System.Windows.Forms.MessageBox.Show(currentval, "提示");
return;
}
else if (value >= 200)
{
System.Windows.Forms.MessageBox.Show("读者当前已经借出总价大于200元的书，不可再借！", "提示");
return;
}
else if (iCount >= 3)
{
System.Windows.Forms.MessageBox.Show("读者当前已经借出3本书，不可再借！", "提示");
return;
}
else
{
string currentval = string.Format("读者当前可以借书！（{0}元）", value);
System.Windows.Forms.MessageBox.Show(currentval, "提示");
return;
}
}
public Question ReadQuestion(int id)
{
Question question = new Question();
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = "SELECT q.*,c.Name as categoryName,c.inbuilt as inbuilt,c.active as active" +
"FROM questions q, categories c WHERE c.id = q.categoryid AND id=@id";
SqliteParameter parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = id;
command.Parameters.Add(parameter);
using (SqliteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
question.Id = Convert.ToInt32(reader["id"]);
question.Title = (string)reader["title"];
question.Category = new Category();
question.Category.Id = Convert.ToInt32(reader["categoryid"]);
question.Category.InBuilt = Convert.ToBoolean(reader["inbuilt"]);
question.Category.Active = Convert.ToBoolean(reader["active"]);
question.Category.Name = (string)reader["categoryName"];
question.Answer = (string)reader["answer"];
question.AskCount = Convert.ToInt32(reader["askcount"]);
question.EasinessFactor = Convert.ToDouble(reader["easinessfactor"]);
question.Interval = Convert.ToInt32(reader["interval"]);
question.LastAsked = new DateTime(Convert.ToInt64(reader["lastasked"]));
question.NextAskOn = new DateTime(Convert.ToInt64(reader["nextaskon"]));
question.Order = Convert.ToInt32(reader["order"]);
question.PreviousInterval = Convert.ToInt32(reader["previousinterval"]);
question.ResponseQuality = Convert.ToInt32(reader["responsequality"]);
}
}
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with ReadQuestion({0}): \n{1}", id, e);
}
return(question);
}
public static string[] GetList(string startsWith)
{
List<string> ret = new List<string>();
using (System.Data.SQLite.SQLiteConnection conn = LocalStorage.DataBaseUtility.GetConnection())
{
string SQL = "SELECT DISTINCT screenname FROM users WHERE screenname LIKE @startswith";
using (System.Data.SQLite.SQLiteCommand comm = new System.Data.SQLite.SQLiteCommand(SQL, conn))
{
comm.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@startswith", startsWith + '%'));
conn.Open();
using (System.Data.SQLite.SQLiteDataReader r = comm.ExecuteReader())
{
while (r.Read())
{
ret.Add(r.GetString(0));
}
}
conn.Clone();
}
}
return ret.ToArray();
}
static void Main(string[] args)
{
// get remote records
//GetUnclaimedBugs();
// compare datatime with DB
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connStr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connStr.DataSource = datasource;
conn.ConnectionString = connStr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "Create table test(username varchar(20), password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "Insert into test values('dontnetthiink', 'passwd')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "select * from test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while(reader.Read())
{
sb.Append("username: "******"\n")
.Append("passwd:").Append(reader.GetString(1));
}
Console.WriteLine(sb.ToString());
// insert latest records
}
private void FillDeadline(System.Data.DataRow row)
{
if (!row.IsNull("deadline_id"))
{
long deadlineID = (long)row["deadline_id"];
using (var automatic = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("SELECT deadlines.deadline, events.ID FROM deadlines LEFT JOIN events ON deadlines.ID=events.deadline_id WHERE deadlines.ID=@id", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", deadlineID));
var reader = command.ExecuteReader();
if (reader.Read())
{
DeadlineDatePicker.SelectedDate = reader.GetDateTime(0);
}
}
}
}
public System.Data.SQLite.SQLiteDataReader ExecuteReader(string SQL, System.Data.DbType[] ParaType, object[] ParaValue)
{
try
{
odCommSelect = new System.Data.SQLite.SQLiteCommand(SQL, odc);
odCommSelect.CommandTimeout = 60 * 60 * 24;
if (ParaType != null)
{
for (int i = 0; i < ParaType.Length; i++)
{
odCommSelect.Parameters.Add("Para" + i, ParaType[i]).Value = ParaValue[i];
}
}
odc.Open();
return odCommSelect.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
CCLog.WriteLog(ex);
throw ex;
}
}
public string TestDb()
{
var sb = new StringBuilder();
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AccountConnection"].ConnectionString;
using (var con = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM AspNetRoles";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
sb.AppendLine(reader["Id"] + " : " + reader["Name"]);
}
}
con.Close(); // Close the connection to the database
}
return sb.ToString();
}
private void Save()
{
// --------------------------------------------------
// Deadline
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("INSERT INTO deadlines(deadline) VALUES(@deadline); SELECT last_insert_rowid()", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@deadline", DeadlineDatePicker.SelectedDate));
var reader = command.ExecuteReader();
if (reader.Read())
{
row["deadline_id"] = reader.GetInt64(0);
}
else
{
row["deadline_id"] = System.DBNull.Value;
}
}
taskDataAdapter.Update(dataSet, "event");
// --------------------------------------------------
// Tags
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
var currentTags = new System.Collections.Generic.List<string>(Util.SplitTags(TagsTextBox.Text));
var whereInTuple = Util.SqlParametersList(currentTags);
var rows = new System.Collections.Generic.List<System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>>();
{
var command = new System.Data.SQLite.SQLiteCommand(@"
SELECT events_tags.ID, events_tags.event_id, events_tags.tag_id, tags.name
FROM events_tags LEFT JOIN tags ON tags.ID = events_tags.tag_id
WHERE events_tags.event_id=@id AND tags.name IN(" + whereInTuple.Item1 + @")
UNION ALL
SELECT NULL, NULL, ID, name
FROM tags
WHERE ID NOT IN(SELECT tag_id FROM events_tags WHERE event_id=@id) AND name IN(" + whereInTuple.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple.Item2)
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
var tuple = new System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>(null, null, null, "");
System.Nullable<long> tasksTagsID = null;
System.Nullable<long> taskID = null;
System.Nullable<long> tagID = null;
string name = "";
if (!reader.IsDBNull(0))
tasksTagsID = reader.GetInt64(0);
if (!reader.IsDBNull(1))
taskID = reader.GetInt64(1);
if (!reader.IsDBNull(2))
tagID = reader.GetInt64(2);
if (!reader.IsDBNull(3))
name = reader.GetString(3);
rows.Add(System.Tuple.Create(tasksTagsID, taskID, tagID, name));
}
}
// delete all old tasks_tags not need for new tags
{
var oldTasksTagsIDs = new System.Collections.Generic.List<long>();
foreach (var tuple in rows)
{
if (tuple.Item1.HasValue)
oldTasksTagsIDs.Add(tuple.Item1.Value);
}
var whereInTuple2 = Util.SqlParametersList(oldTasksTagsIDs);
var command = new System.Data.SQLite.SQLiteCommand("DELETE FROM events_tags WHERE event_id=@id AND ID NOT IN(" + whereInTuple2.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple2.Item2)
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
// link existing new tags
foreach (var tuple in rows)
{
if (!tuple.Item1.HasValue && tuple.Item3.HasValue)
{
var tagID = tuple.Item3.Value;
long newTasksTagsID = Util.InsertInto(connection, "events_tags", System.Tuple.Create("event_id", id), System.Tuple.Create("tag_id", tagID));
}
}
// create and link new tags
{
var newTags = new System.Collections.Generic.List<string>();
foreach (var tagName in currentTags)
{
bool found = false;
foreach (var row in rows)
{
if (row.Item4 == tagName)
{
found = true;
break;
}
}
if (!found)
{
long newTagID = Util.InsertInto(connection, "tags", System.Tuple.Create("name", tagName));
long newTasksTagsID = Util.InsertInto(connection, "events_tags", System.Tuple.Create("event_id", id), System.Tuple.Create("tag_id", newTagID));
}
}
}
// Alerts
foreach (System.Data.DataRow row in dataSet.Tables["alerts"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["event_id"] = id;
}
alertsDataAdapter.Update(dataSet, "alerts");
dataSet.Tables["alerts"].Clear();
alertsDataAdapter.Fill(dataSet, "alerts");
}
// --------------------------------------------------
// Clean state
IsDirty = false;
try
{
this.parent.MassReloadEvents();
}
catch
{
}
}
private void UpdateLoop()
{
int count = 0;
int seconds = int.Parse(_config.Config.UpdateRate);
int maxcount = int.Parse(_config.Config.LastNum);
try {
string con = string.Format(@"Data Source={0}\Twitch\Databases\ExternalSubDB.sqlite; Version=3;", _config.Config.Ankhdir);
System.Data.SQLite.SQLiteConnection _sqlcon = new System.Data.SQLite.SQLiteConnection(con);
_sqlcon.Open();
System.Data.SQLite.SQLiteCommand _cmd = new System.Data.SQLite.SQLiteCommand(_sqlcon);
_cmd.CommandText = "Select User from externalsub";
while (true)
{
System.Threading.Thread.Sleep(1000 * seconds);
System.Data.SQLite.SQLiteDataReader dr = _cmd.ExecuteReader();
if (_subs.Count > 0)
_subs.Clear();
while (dr.Read())
{
_subs.Add(dr[0]);
}
if (_subs.Count > count)
{
//write out
int i = 0;
int end = _subs.Count;
if (_subs.Count - maxcount > 0)
i = _subs.Count - maxcount;
string output = "";
for ( /*nothing*/ ; i < end; i++)
{
output += (string)_subs[i] + ",";
}
count = _subs.Count;
output = output.Remove(output.Length - 1);
try
{
System.IO.Stream stream = System.IO.File.Open(_config.Config.Outputpath + "\\hcgwab.txt", System.IO.FileMode.Create);
stream.Write(ASCIIEncoding.ASCII.GetBytes(output), 0, ASCIIEncoding.ASCII.GetBytes(output).Length);
stream.Close();
}
catch (Exception e)
{
}
}
dr.Close();
}
} catch(Exception ee)
{
//bleh who gives a shit.
MessageBox.Show("Exception Occurred", ee.Message);
}
}
public string RicettarioDb()
{
var sb = new StringBuilder();
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RicettarioConnection"].ConnectionString;
using (var con = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM WeekSchedule";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
sb.AppendLine(reader["Id"] + " : " + reader["Name"]);
}
}
con.Close(); // Close the connection to the database
}
return sb.ToString().Substring(0, 300);
}
private void SaveBadgeForItem(String itemPath, String badgePath, Boolean isClear = false) {
var m_dbConnection = new System.Data.SQLite.SQLiteConnection("Data Source=" + this._DBPath + ";Version=3;");
m_dbConnection.Open();
if (isClear) {
var command3 = new System.Data.SQLite.SQLiteCommand("DELETE FROM badges WHERE Path=@Path", m_dbConnection);
command3.Parameters.AddWithValue("Path", itemPath);
command3.ExecuteNonQuery();
} else {
var command1 = new System.Data.SQLite.SQLiteCommand("SELECT * FROM badges WHERE Path=@Path", m_dbConnection);
command1.Parameters.AddWithValue("Path", itemPath);
var Reader = command1.ExecuteReader();
var sql = Reader.Read()
? @"UPDATE badges SET Collection = @Collection, Badge = @Badge WHERE Path = @Path"
: @"INSERT INTO badges (Path, Collection, Badge) VALUES (@Path, @Collection, @Badge)";
var command2 = new System.Data.SQLite.SQLiteCommand(sql, m_dbConnection);
command2.Parameters.AddWithValue("Path", itemPath);
command2.Parameters.AddWithValue("Collection", Path.GetFileName(Path.GetDirectoryName(badgePath)));
command2.Parameters.AddWithValue("Badge", Path.GetFileName(badgePath));
command2.ExecuteNonQuery();
Reader.Close();
}
m_dbConnection.Close();
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
_connectionString = connectionString;
string[] s1 = _connectionString.Split(new[] { "Data Source=", "," }, StringSplitOptions.RemoveEmptyEntries);
bool needsUTFConverted = false;
_fileName = Path.GetFileName(s1[0]);
if (s1[0].EndsWith(";"))
{
_fileName = Path.GetFileNameWithoutExtension(s1[1].Substring(7, s1[1].Length - 7)) + "utf8.db";
_connectionString = "Data Source=file://" + _fileName;
s1 = new string[1] { "file://" + _fileName };
needsUTFConverted = true;
_hadToConvert = true;
}
if (_fileName == s1[0]) //Only add this if we arn't an absolute path already
_connectionString = _connectionString.Replace("Data Source=", "Data Source=" + Util.BasePathCombine("") + "\\");
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
var migrationManager = new MigrationManager(this, migratorName, validateTables);
migrationManager.DetermineOperation();
migrationManager.ExecuteOperation();
connection.Close();
if (needsUTFConverted && _hadToConvert)
{
string file = connectionString.Split(new[] { "Data Source=", "," }, StringSplitOptions.RemoveEmptyEntries)[1].Substring(7);
if (File.Exists(file))
{
//UTF16 db, gotta convert it
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + file + ";version=3;UseUTF16Encoding=True");
conn.Open();
var RetVal = new List<string>();
using (var cmd = new System.Data.SQLite.SQLiteCommand("SELECT name FROM Sqlite_master", conn))
{
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
RetVal.Add(rdr.GetValue(i).ToString());
}
}
}
}
foreach (string table in RetVal)
{
if (TableExists(table) && !table.StartsWith("sqlite") && !table.StartsWith("idx_") && table != "aurora_migrator_version")
{
var retVal = new List<object[]>();
using (var cmd = new System.Data.SQLite.SQLiteCommand("SELECT * FROM " + table, conn))
{
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
List<object> obs = new List<object>();
for (int i = 0; i < reader.FieldCount; i++)
{
Type r = reader[i].GetType();
if (r == typeof(DBNull))
obs.Add(null);
else
obs.Add(reader[i].ToString());
}
retVal.Add(obs.ToArray());
}
}
}
try
{
if(retVal.Count > 0)
InsertMultiple(table, retVal);
}
catch { }
}
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
//创建一个数据库文件
//’dotnetthink','mypassword'
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES('dotnetthink', 'mypassword')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
/// <summary>
/// Gets the badges from the folder Badges located in the .EXE's directory and the badges from SQLite database
/// </summary>
/// <returns></returns>
private Dictionary<String, Dictionary<IListItemEx, List<string>>> LoadBadgesData() {
var result = new Dictionary<String, Dictionary<IListItemEx, List<string>>>();
var badgesDirectory = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "Badges");
var badgesIshellItem = FileSystemListItem.ToFileSystemItem(this._ShellListView.LVHandle, badgesDirectory);
foreach (var item in badgesIshellItem.Where(w => w.IsFolder)) {
var innerDict = new Dictionary<IListItemEx, List<string>>();
foreach (var badgeItem in item.Where(w => w.Extension.ToLowerInvariant() == ".ico")) {
innerDict.Add(badgeItem, new List<String>());
}
result.Add(item.DisplayName, innerDict);
}
try {
var m_dbConnection = new System.Data.SQLite.SQLiteConnection($"Data Source={this._DBPath};Version=3;");
m_dbConnection.Open();
var command1 = new System.Data.SQLite.SQLiteCommand("SELECT * FROM badges", m_dbConnection);
var Reader = command1.ExecuteReader();
while (Reader.Read()) {
var values = Reader.GetValues();
var path = values.GetValues("Path").Single();
var collectionName = values.GetValues("Collection").Single();
var badgeName = values.GetValues("Badge").Single();
var badgeDBItem = FileSystemListItem.ToFileSystemItem(this._ShellListView.LVHandle, Path.Combine(badgesDirectory, collectionName, badgeName));
var collectionDict = result[collectionName];
var collectionItemKey = collectionDict.Keys.SingleOrDefault(w => w.ParsingName.Equals(badgeDBItem.ParsingName));
if (collectionItemKey != null) {
result[collectionName][collectionItemKey].Add(path);
}
}
Reader.Close();
} catch (Exception) {
}
return result;
}
private void FillTags(long taskID)
{
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("SELECT tags.name FROM tasks_tags INNER JOIN tags ON tasks_tags.tag_id=tags.ID WHERE tasks_tags.task_id=@id", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", taskID));
var reader = command.ExecuteReader();
var builder = new System.Text.StringBuilder();
while (reader.Read())
{
builder.AppendFormat("[{0}]", reader[0]);
}
TagsTextBox.Text = builder.ToString();
}
}
private void FillDeadline(System.Data.DataRow row)
{
FillEventsComboBox();
if (!row.IsNull("deadline_id"))
{
long deadlineID = (long) row["deadline_id"];
using (var automatic = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("SELECT deadlines.deadline, events.ID FROM deadlines LEFT JOIN events ON deadlines.ID=events.deadline_id WHERE deadlines.ID=@id", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", deadlineID));
var reader = command.ExecuteReader();
if (reader.Read())
{
DeadlineDatePicker.SelectedDate = reader.GetDateTime(0);
if (reader.IsDBNull(1))
{
DeadlineIsSelectedDateRadioButton.IsChecked = true;
}
else
{
DeadlineIsEventRadioButton.IsChecked = true;
EventsComboBox.SelectedValue = reader.GetInt64(1);
}
}
}
}
}
private bool Save()
{
System.Data.SQLite.SQLiteTransaction transaction = null;
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
try
{
transaction = connection.BeginTransaction();
// --------------------------------------------------
// Deadline
if (DeadlineIsSelectedDateRadioButton.IsChecked.Value)
{
var command = new System.Data.SQLite.SQLiteCommand("INSERT INTO deadlines(deadline) VALUES(@deadline); SELECT last_insert_rowid()", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@deadline", DeadlineDatePicker.SelectedDate));
var reader = command.ExecuteReader();
if (reader.Read())
{
row["deadline_id"] = reader.GetInt64(0);
}
}
else if (DeadlineIsEventRadioButton.IsChecked.Value)
{
row["deadline_id"] = (EventsComboBox.SelectedValue != null) ? EventsComboBox.SelectedValue : System.DBNull.Value;
}
else
{
row["deadline_id"] = System.DBNull.Value;
}
taskDataAdapter.Update(dataSet, "task");
// --------------------------------------------------
// Tags
var currentTags = new System.Collections.Generic.List<string>(Util.SplitTags(TagsTextBox.Text));
var whereInTuple = Util.SqlParametersList(currentTags);
var rows = new System.Collections.Generic.List<System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>>();
{
var command = new System.Data.SQLite.SQLiteCommand(@"
SELECT tasks_tags.ID, tasks_tags.task_id, tasks_tags.tag_id, tags.name
FROM tasks_tags LEFT JOIN tags ON tags.ID = tasks_tags.tag_id
WHERE tasks_tags.task_id=@id AND tags.name IN(" + whereInTuple.Item1 + @")
UNION ALL
SELECT NULL, NULL, ID, name
FROM tags
WHERE ID NOT IN(SELECT tag_id FROM tasks_tags WHERE task_id=@id) AND name IN(" + whereInTuple.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple.Item2)
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
var tuple = new System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>(null, null, null, "");
System.Nullable<long> tasksTagsID = null;
System.Nullable<long> taskID = null;
System.Nullable<long> tagID = null;
string name = "";
if (!reader.IsDBNull(0))
tasksTagsID = reader.GetInt64(0);
if (!reader.IsDBNull(1))
taskID = reader.GetInt64(1);
if (!reader.IsDBNull(2))
tagID = reader.GetInt64(2);
if (!reader.IsDBNull(3))
name = reader.GetString(3);
rows.Add(System.Tuple.Create(tasksTagsID, taskID, tagID, name));
}
}
// delete all old tasks_tags not need for new tags
{
var oldTasksTagsIDs = new System.Collections.Generic.List<long>();
foreach (var tuple in rows)
{
if (tuple.Item1.HasValue)
oldTasksTagsIDs.Add(tuple.Item1.Value);
}
var whereInTuple2 = Util.SqlParametersList(oldTasksTagsIDs);
var command = new System.Data.SQLite.SQLiteCommand("DELETE FROM tasks_tags WHERE task_id=@id AND ID NOT IN(" + whereInTuple2.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple2.Item2)
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
// link existing new tags
foreach (var tuple in rows)
{
if (!tuple.Item1.HasValue && tuple.Item3.HasValue)
{
var tagID = tuple.Item3.Value;
long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", tagID));
}
}
// create and link new tags
{
var newTags = new System.Collections.Generic.List<string>();
foreach (var tagName in currentTags)
{
bool found = false;
foreach (var row in rows)
{
if (row.Item4 == tagName)
{
found = true;
break;
}
}
if (!found)
{
long newTagID = Util.InsertInto(connection, "tags", System.Tuple.Create("name", tagName));
long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", newTagID));
}
}
}
// --------------------------------------------------
// Alerts
foreach (System.Data.DataRow row in dataSet.Tables["alerts"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["task_id"] = id;
}
alertsDataAdapter.Update(dataSet, "alerts");
dataSet.Tables["alerts"].Clear();
alertsDataAdapter.Fill(dataSet, "alerts");
// --------------------------------------------------
// Sub-Tasks
foreach (System.Data.DataRow row in dataSet.Tables["sub_tasks"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["child_of"] = id;
}
subTasksDataAdapter.Update(dataSet, "sub_tasks");
dataSet.Tables["sub_tasks"].Clear();
subTasksDataAdapter.Fill(dataSet, "sub_tasks");
// --------------------------------------------------
// Clean state
IsDirty = false;
transaction.Commit();
}
catch (System.Data.SQLite.SQLiteException e)
{
if (transaction != null)
transaction.Rollback();
switch (e.ErrorCode)
{
case System.Data.SQLite.SQLiteErrorCode.Constraint:
Util.ShowFieldMustBeUniqueMessage(this, Util.ExtractColumnName(e.Message));
break;
}
}
}
try
{
this.parent.MassReloadTasks();
}
catch
{
}
return !IsDirty;
}
protected System.Data.DataTable Update(System.Data.DataTable xdt, string SourceTable, System.Data.SQLite.SQLiteTransaction odTrans)
{
ConnectionState OldConnState = odc.State;
try
{
System.Data.DataTable dtColumns = new System.Data.DataTable();
System.Data.DataTable dt;
if (odc.State == ConnectionState.Closed) odc.Open();
odCommSelect = new System.Data.SQLite.SQLiteCommand("select * from " + SourceTable + " where 1 = 2", odc);
odCommSelect.Transaction = odTrans;
System.Data.SQLite.SQLiteDataReader read = odCommSelect.ExecuteReader();
dt = read.GetSchemaTable();
System.Data.DataColumn c = new System.Data.DataColumn();
c.DataType = System.Type.GetType("System.Int16");
c.ColumnName = "type";
dtColumns.Columns.Add(c);
System.Data.DataColumn c1 = new System.Data.DataColumn();
c1.DataType = System.Type.GetType("System.String");
c1.ColumnName = "name";
dtColumns.Columns.Add(c1);
System.Data.DataColumn c2 = new System.Data.DataColumn();
c2.DataType = System.Type.GetType("System.Int32");
c2.ColumnName = "length";
dtColumns.Columns.Add(c2);
System.Data.DataColumn c3 = new System.Data.DataColumn();
c3.DataType = System.Type.GetType("System.Byte");
c3.ColumnName = "prec";
dtColumns.Columns.Add(c3);
System.Data.DataColumn c4 = new System.Data.DataColumn();
c4.DataType = System.Type.GetType("System.Byte");
c4.ColumnName = "scale";
dtColumns.Columns.Add(c4);
DataRow dr;
foreach (DataRow r in dt.Rows)
{
dr = dtColumns.NewRow();
dr["type"] = 0;
dr["name"] = r[0].ToString();
dr["length"] = Convert.ToInt32(r[2]);
dr["prec"] = Convert.ToInt32(r[3]);
dr["scale"] = Convert.ToInt32(r[4]);
dtColumns.Rows.Add(dr);
}
read.Close();
dtColumns.PrimaryKey = new System.Data.DataColumn[] { dtColumns.Columns["name"] };
string Insert, Values, Where, Update, Delete;
int length;
byte prec, scale;
System.Data.DataRow row;
odCommInsert = new System.Data.SQLite.SQLiteCommand();
odCommInsert.Connection = odc;
odCommInsert.Transaction = odTrans;
oda.InsertCommand = odCommInsert;
odCommUpdate = new System.Data.SQLite.SQLiteCommand();
odCommUpdate.Connection = odc;
odCommUpdate.Transaction = odTrans;
oda.UpdateCommand = odCommUpdate;
odCommDelete = new System.Data.SQLite.SQLiteCommand();
odCommDelete.Connection = odc;
odCommDelete.Transaction = odTrans;
oda.DeleteCommand = odCommDelete;
Insert = "INSERT INTO " + SourceTable + " ( ";
Values = " VALUES( ";
Update = "UPDATE " + SourceTable + " SET ";
Delete = "DELETE FROM " + SourceTable;
foreach (System.Data.DataColumn dc in xdt.Columns)
{
Insert += "[" + dc.ColumnName + "],";
Values += "?,";
Update += "[" + dc.ColumnName + "]=?,";
row = dtColumns.Rows.Find(dc.ColumnName);
if (row == null) throw new Exception("无效列名：" + dc.ColumnName);
prec = (byte)row["prec"];
scale = (byte)row["scale"];
length = (int)row["length"];
odCommInsert.Parameters.Add(new System.Data.SQLite.SQLiteParameter(dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Current, null));
odCommUpdate.Parameters.Add(new System.Data.SQLite.SQLiteParameter(dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Current, null));
}
Insert = Insert.Remove(Insert.Length - 1, 1); // 消除最后的","号
Values = Values.Remove(Values.Length - 1, 1); // 消除最后的","号
Update = Update.Remove(Update.Length - 1, 1); // 消除最后的","号
// 构造Where语句及其参数
Where = " WHERE (1=1) ";
if (xdt.PrimaryKey.Length == 0)
{
foreach (System.Data.DataColumn dc in xdt.Columns)
{
row = dtColumns.Rows.Find(dc.ColumnName);
if (row == null) throw new Exception("无效列名：" + dc.ColumnName);
prec = (byte)row["prec"];
scale = (byte)row["scale"];
length = (int)row["length"];
if (length == 2147483647) continue;
Where += " AND ([" + dc.ColumnName + "]=?)";
odCommUpdate.Parameters.Add(new System.Data.SQLite.SQLiteParameter("Original_" + dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Original, null));
odCommDelete.Parameters.Add(new System.Data.SQLite.SQLiteParameter("Original_" + dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Original, null));
}
}
else
{
foreach (System.Data.DataColumn dc in xdt.PrimaryKey)
{
row = dtColumns.Rows.Find(dc.ColumnName);
if (row == null) throw new Exception("无效列名：" + dc.ColumnName);
prec = (byte)row["prec"];
scale = (byte)row["scale"];
length = (int)row["length"];
Where += " AND ([" + dc.ColumnName + "]=?)";
odCommUpdate.Parameters.Add(new System.Data.SQLite.SQLiteParameter("Original_" + dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Original, null));
odCommDelete.Parameters.Add(new System.Data.SQLite.SQLiteParameter("Original_" + dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Original, null));
}
}
odCommInsert.CommandText = Insert + ") " + Values + ")";
odCommUpdate.CommandText = Update + Where;
odCommDelete.CommandText = Delete + Where;
oda.Update(xdt);
}
catch (Exception ex)
{
CCLog.WriteLog(ex);
throw ex;
}
finally
{
if (OldConnState == ConnectionState.Closed) odc.Close();
}
return xdt;
}
public IList<Question> ListQuestions()
{
IList<Question> list = new List<Question>();
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = "SELECT q.*,c.Name as categoryName,c.inbuilt as inbuilt,c.active as active FROM "+
"questions q, categories c WHERE c.id = q.categoryid";
using (SqliteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Question question = new Question();
question.Id = Convert.ToInt32(reader["id"]);
question.Title = (string)reader["title"];
question.Category = new Category();
question.Category.Id = Convert.ToInt32(reader["categoryid"]);
question.Category.InBuilt = Convert.ToBoolean(reader["inbuilt"]);
question.Category.Active = Convert.ToBoolean(reader["active"]);
question.Category.Name = (string)reader["categoryName"];
question.Answer = (string)reader["answer"];
question.AskCount = Convert.ToInt32(reader["askcount"]);
question.EasinessFactor = Convert.ToDouble(reader["easinessfactor"]);
question.Interval = Convert.ToInt32(reader["interval"]);
question.LastAsked = new DateTime(Convert.ToInt64(reader["lastasked"]));
question.NextAskOn = new DateTime(Convert.ToInt64(reader["nextaskon"]));
question.Order = Convert.ToInt32(reader["order"]);
question.PreviousInterval = Convert.ToInt32(reader["previousinterval"]);
question.ResponseQuality = Convert.ToInt32(reader["responsequality"]);
list.Add(question);
}
}
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with ListQuestions(): \n{0}", e);
}
return list;
}
public IList<Question> QuestionsForCategory(Category category)
{
IList<Question> list = new List<Question>();
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = "SELECT * FROM questions WHERE categoryid = @categoryid";
SqliteParameter parameter = new SqliteParameter("@categoryid", DbType.Int32);
parameter.Value = category.Id;
command.Parameters.Add(parameter);
using (SqliteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Question question = new Question();
question.Id = Convert.ToInt32(reader["id"]);
question.Title = (string)reader["title"];
question.Category = category;
question.Answer = (string)reader["answer"];
question.AskCount = Convert.ToInt32(reader["askcount"]);
question.EasinessFactor = Convert.ToDouble(reader["easinessfactor"]);
question.Interval = Convert.ToInt32(reader["interval"]);
question.LastAsked = new DateTime(Convert.ToInt64(reader["lastasked"]));
question.NextAskOn = new DateTime(Convert.ToInt64(reader["nextaskon"]));
question.Order = Convert.ToInt32(reader["order"]);
question.PreviousInterval = Convert.ToInt32(reader["previousinterval"]);
question.ResponseQuality = Convert.ToInt32(reader["responsequality"]);
list.Add(question);
}
}
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with QuestionsForCategory({0}): \n{1}", category.Id,e);
}
return list;
}
public Question ReadQuestion(int id)
{
Question question = new Question();
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = "SELECT q.*,c.Name as categoryName,c.inbuilt as inbuilt,c.active as active"+
"FROM questions q, categories c WHERE c.id = q.categoryid AND id=@id";
SqliteParameter parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = id;
command.Parameters.Add(parameter);
using (SqliteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
question.Id = Convert.ToInt32(reader["id"]);
question.Title = (string)reader["title"];
question.Category = new Category();
question.Category.Id = Convert.ToInt32(reader["categoryid"]);
question.Category.InBuilt = Convert.ToBoolean(reader["inbuilt"]);
question.Category.Active = Convert.ToBoolean(reader["active"]);
question.Category.Name = (string)reader["categoryName"];
question.Answer = (string)reader["answer"];
question.AskCount = Convert.ToInt32(reader["askcount"]);
question.EasinessFactor = Convert.ToDouble(reader["easinessfactor"]);
question.Interval = Convert.ToInt32(reader["interval"]);
question.LastAsked = new DateTime(Convert.ToInt64(reader["lastasked"]));
question.NextAskOn = new DateTime(Convert.ToInt64(reader["nextaskon"]));
question.Order = Convert.ToInt32(reader["order"]);
question.PreviousInterval = Convert.ToInt32(reader["previousinterval"]);
question.ResponseQuality = Convert.ToInt32(reader["responsequality"]);
}
}
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with ReadQuestion({0}): \n{1}", id,e);
}
return question;
}
public IList<Category> ListCategories()
{
IList<Category> list = new List<Category>();
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = "SELECT id,name,inbuilt,active FROM categories";
using (SqliteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Category category = new Category();
category.Id = reader.GetInt32(0);
category.Name = reader.GetString(1);
category.InBuilt = reader.GetBoolean(2);
category.Active = reader.GetBoolean(3);
list.Add(category);
}
}
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred while listing categories: \n{0}", e);
}
return list;
}
public Category ReadCategory(int id)
{
Category category = new Category();
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = "SELECT * FROM categories WHERE id=@id";
SqliteParameter parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = id;
command.Parameters.Add(parameter);
using (SqliteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
category.Id = Convert.ToInt32(reader["categoryid"]);
category.InBuilt = Convert.ToBoolean(reader["inbuilt"]);
category.Name = (string)reader["categoryName"];
category.Active = Convert.ToBoolean(reader["active"]);
}
}
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with ReadCategory({0}): \n{1}", id, e);
}
return category;
}