public void selectData(List<String> where1, List<String> where2, String tableName, String[] orderBy)
{
command.CommandText = "SELECT * from ";
command.CommandText += tableName;
if (where1.Count > 0)
{
command.CommandText += " WHERE";
for (int i = 0; i < where1.Count; i++)
{
command.CommandText += " " + where1.ElementAt(i) + "=" + where2.ElementAt(i) + " AND";
}
command.CommandText = command.CommandText.Substring(0, command.CommandText.Length - 3);
where1.RemoveRange(0, where1.Count);
where2.RemoveRange(0, where2.Count);
}
command.CommandText += " ORDER BY ";
command.CommandText += orderBy;
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Console.WriteLine("ID : " + DB_PrintToConsole(dataReader, 0) + " ");
Console.WriteLine("Source Site : " + DB_PrintToConsole(dataReader, 1) + " ");
Console.WriteLine("Source ID : " + DB_PrintToConsole(dataReader, 2) + " ");
Console.WriteLine("Circle Name : " + DB_PrintToConsole(dataReader, 3) + " ");
Console.WriteLine("Date : " + DB_PrintToConsole(dataReader, 4) + " ");
Console.WriteLine("Title : " + DB_PrintToConsole(dataReader, 5) + " ");
Console.WriteLine("Title Altern : " + DB_PrintToConsole(dataReader, 6) + " ");
Console.WriteLine("Language : " + DB_PrintToConsole(dataReader, 7) + " ");
Console.WriteLine("Data Type : " + DB_PrintToConsole(dataReader, 8));
Console.WriteLine();
}
}
public Report()
{
InitializeComponent();
// Connect to database file
sql_con = new SQLiteConnection("Data Source=" + applicationPath + "\\ExpenseTracker.db;Version=3;New=False;Compress=True;");
sql_cmd = new SQLiteCommand();
sql_con.Open();
sql_cmd.Connection = sql_con;
sql_cmd.CommandText = "SELECT * FROM Month";
sql_reader = sql_cmd.ExecuteReader();
while (sql_reader.Read())
{
dataGridView.Rows.Add(
sql_reader.GetInt32(0),
CustomDate.GetThaiMonth(sql_reader.GetInt32(1)),
sql_reader.GetDecimal(2).ToString("#,#0.00#"),
sql_reader.GetDecimal(3).ToString("#,#0.00#"),
sql_reader.GetDecimal(4).ToString("#,#0.00#")
);
}
sql_reader.Close();
dataGridView.ClearSelection();
}
internal void Sync(long rowid)
{
IsValid = false;
_command.Parameters[0].Value = rowid;
_reader = _command.ExecuteReader();
_reader.Read();
}
public void getEntireDB(ref List <string> movie_list, ref List <string> actor_list)
{
com.CommandText = "Select * FROM Movies";
movies.Clear();
actors.Clear();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
if (!String.IsNullOrEmpty(reader["movie"].ToString()))
{
movies.Add(reader["movie"].ToString());
}
}
}
com.CommandText = "Select * FROM Actors";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
if (!String.IsNullOrEmpty(reader["actor"].ToString()))
{
actors.Add(reader["actor"].ToString());
}
}
}
movie_list = movies.Distinct().ToList();
actor_list = actors.Distinct().ToList();
}
/// <summary>
/// 读取列表
/// </summary>
/// <param name="sqlSelect">查询语句</param>
/// <param name="ReadDataDelegate">读取委托</param>
public void ReadTable(string sqlSelect, Func <System.Data.SQLite.SQLiteDataReader, bool> ReadDataDelegate)
{
try
{
using (var conn = new System.Data.SQLite.SQLiteConnection())
{
var connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand())
{
//var collect = new List<Tuple<string, int>>();
cmd.Connection = conn;
//string sql = "SELECT * from control_authority";
cmd.CommandText = sqlSelect;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//collect.Add(new Tuple<string, int>(reader.GetString(0), reader.GetInt32(1)));
if (!ReadDataDelegate(reader))
{
break;
}
}
conn.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public string updateMovieName(string movie, string updated_movie)
{
movie = movie.Trim();
updated_movie = updated_movie.Trim();
com.CommandText = "Select movie FROM Movies WHERE movie = '" + movie + "'";
movies.Clear();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
if (!String.IsNullOrEmpty(reader["movie"].ToString()))
{
movies.Add(reader["movie"].ToString());
}
}
}
if (movies.Count().Equals(0)) //movie not found in imdb
{
return("movie not found in imdb");
}
com.CommandText = "UPDATE Movies SET movie = '" + updated_movie + "' WHERE movie = '" + movie + "'";
com.ExecuteNonQuery();
com.CommandText = "UPDATE MovieAttributes SET movie = '" + updated_movie + "' WHERE movie = '" + movie + "'";
com.ExecuteNonQuery();
return("success");
}
static void Main(string[] args)
{
string createQuery = @"CREATE TABLE IF NOT EXISTS
[Mytable](
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[NAME] NVARCHAR(2048) NOT NULL,
[GENDER] NVARCHAR(2048) NOT NULL)";
System.Data.SQLite.SQLiteConnection.CreateFile("simple.db3");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=database.simple.db3"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO mytable(NAME,GENDER) values('alex','male')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO mytable(NAME,GENDER) values('diane','female')";
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from Mytable";
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["NAME"] + ":" + reader["GENDER"]);
}
conn.Close();
}
}
}
Console.ReadLine();
}
public string updateActorName(string actor, string updated_actor)
{
actor = actor.Trim();
updated_actor = updated_actor.Trim();
com.CommandText = "Select actor FROM Actors WHERE actor = '" + actor + "'";
actors.Clear();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
if (!String.IsNullOrEmpty(reader["actor"].ToString()))
{
actors.Add(reader["actor"].ToString());
}
}
}
if (actors.Count().Equals(0)) //actor not found in imdb
{
return("actor not found in imdb");
}
com.CommandText = "UPDATE Actors SET actor = '" + updated_actor + "' WHERE actor = '" + actor + "'";
com.ExecuteNonQuery();
com.CommandText = "UPDATE MovieAttributes SET actor = '" + updated_actor + "' WHERE actor = '" + actor + "'";
com.ExecuteNonQuery();
return("success");
}
public string addMovie(string movie, string year)
{
// search DB to see if movie exists
// update DB accordingly
movie = movie.Trim();
com.CommandText = "Select * FROM Movies WHERE movie = '" + movie + "'";
com.ExecuteNonQuery();
movies.Clear();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
if (!String.IsNullOrEmpty(reader["movie"].ToString()))
{
movies.Add(reader["movie"].ToString());
}
}
}
if (movies.Count().Equals(0))
{
// Add entry into imdb
com.CommandText = "INSERT INTO Movies (movie,year) Values ('" + movie + "', '" + year + "')";
com.ExecuteNonQuery();
return("success");
}
return("movie already in imdb");
}
// 最原始的列出数据
public List <String[]> LocalGetDataFromOriginalSQL(String sql, String[] query)
{
using (SQLiteConnection conn = new SQLiteConnection(LocalConnStr))
{
using (SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn))
{
cmdCreateTable.CommandText = sql;
conn.Open();
System.Data.SQLite.SQLiteDataReader reader = cmdCreateTable.ExecuteReader();
String[] resultsStringArray = new String[query.Length];
List <String[]> resultsStringList = new List <String[]>();
while (reader.Read())
{
for (int i = 0; i < query.Length; i++)
{
resultsStringArray[i] = reader[i].ToString();
}
resultsStringList.Add(resultsStringArray);
resultsStringArray = new String[query.Length];
}
reader.Close();
return(resultsStringList);
}
}
}
private void Charts_Load(object sender, EventArgs e)
{
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
#region 读取状态
//获取表名
var tbListDT = sh.GetTableList();
List <string> tbList = new List <string>();
for (int i = 0; i < tbListDT.Rows.Count; i++)
{
tbList.Add(tbListDT.Rows[i][0].ToString());
}
//获取状态
for (int i = 0; i < tbList.Count; i++)
{
string listItem = tbList[i];
comboBox2.Items.Add(listItem);
}
#endregion
#region 读取测点列表
try
{
string tableName = tbList[0];
List <string> columnName = new List <string>();
string sql = "PRAGMA table_info([" + tableName + "]);";
SQLiteCommand cmd2 = new SQLiteCommand(sql, conn);
System.Data.SQLite.SQLiteDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
columnName.Add(dr[1].ToString());
}
dr.Close();
conn.Close();
var colName = new List <string>();
for (int i = 3; i < columnName.Count; i++)
{
checkedListBox1.Items.Add(columnName[i].ToString());
}
#endregion
}
catch (Exception)
{
}
conn.Close();
}
}
//温度范围
textBox2.Text = Global.tempLimitLow.ToString();
textBox3.Text = Global.tempLimitHigh.ToString();
}
/// <summary>
/// 获得所有群
/// </summary>
public static List <exRoom> GetRooms()
{
List <exRoom> Rooms = new List <exRoom>();
string sql = "select * from Rooms";
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null);
if (dr != null)
{
while (dr.Read())
{
exRoom Room = new exRoom();
{
Room.RoomID = Convert.ToString(dr["RoomID"]);
Room.RoomName = Convert.ToString(dr["RoomName"]);
Room.Notice = Convert.ToString(dr["Notice"]);
Room.UserIDs = Convert.ToString(dr["Users"]);
Room.OrderID = Convert.ToInt32(dr["OrderID"]);
Room.CreateUserID = Convert.ToString(dr["CreateUserID"]);
}
Rooms.Add(Room);
}
dr.Close();
}
dr.Dispose();
return(Rooms);
}
public Int32 SaveNewPlayer(String PlayerName)
{
idPlayer = 0;
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conexao))
{
conexao.Open();
com.CommandText = "INSERT INTO GameResults (PlayerName, Score) Values ('" + PlayerName + "','0')";
com.ExecuteNonQuery();
//Recupera o novo registro
com.CommandText = "Select * FROM GameResults WHERE PlayerName ='" + PlayerName + "'"; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
idPlayer = Convert.ToInt32(reader["ID"]);
Console.WriteLine(reader["PlayerName"] + " : " + reader["Score"]); // Display the value of the key and value column for every row
}
}
conexao.Close(); // Close the connection to the database
}
return(idPlayer);
}
private int findNextToPay()
{
try
{
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 count(*) FROM Rata WHERE DATE(DataPagam) <= DATE('now','+'||14||' days') and DATE(DataPagam) >= DATE('now') and (PagamentoAvv='NO')";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(reader.GetInt32(0)); //ritorna la prima colonna, quella avente il count
}
}
conn.Close();
}
}
return(0);
}
catch
{
return(-1);
}
}
/// <summary>
///
/// </summary>
/// <param name="certo">Quantidade respostas certas</param>
/// <param name="errado">Quantidade respostas erradas</param>
public string mostraComRespostas(int certo, int errado)
{
string Label = "";
int pontos = 0;
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conexao))
{
conexao.Open();
com.CommandText = "Select Score FROM GameResults WHERE ID =" + idPlayer.ToString();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
while (reader.Read())
{
pontos = Convert.ToInt32(reader["Score"].ToString());
}
int update = certo - errado;
update = pontos + update < 0 ? 0 : update;
com.CommandText = "UPDATE GameResults SET Score=Score+" + update + " WHERE ID=" + idPlayer.ToString();
com.ExecuteNonQuery();
com.CommandText = "Select PlayerName, Score FROM GameResults WHERE ID =" + idPlayer.ToString();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
while (reader.Read())
{
Label = reader["PlayerName"].ToString() + " - " + reader["Score"].ToString();
}
conexao.Close(); // Close the connection to the database
}
return(Label);
}
public static CompanyModel[] GetCompanyList()
{
List <CompanyModel> results = new List <CompanyModel>();
using (var conn = GetConnection())
{
try
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from Company;";
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
results.Add(ReadCompanyData(reader));
}
}
catch (Exception ex)
{
Utility.LogHelper.WriteError("Encript.SqlLite", "GetCompanyList", ex);
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(results.ToArray());
}
public List<Tuple<string, string, string, string, string>> GetAllBooks()
{
var books = new List<Tuple<string, string, string, string, string>>();
m_dbConnection = new SQLiteConnection("Data Source=" + db + ";Version=3;");
m_dbConnection.Open();
string sql = "SELECT * FROM books";
command = new SQLiteCommand(sql, m_dbConnection);
reader = command.ExecuteReader();
string id = "";
string title = "";
string auteur = "";
string picture = "";
string synopsis = "";
while (reader.Read())
{
id = reader["id"].ToString();
title = reader["title"].ToString();
auteur = reader["auteur"].ToString();
picture = reader["picture"].ToString();
synopsis = reader["synopsis"].ToString();
books.Add(new Tuple<string, string, string, string, string>(id, title, auteur, picture, synopsis));
}
reader.Close();
m_dbConnection.Close();
return books;
}
public void CreateDatabase()
{
string createQuery = @"CREATE TABLE IF NOT EXISTS
[Mytable](
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Name] NVARCHAR(50) NULL,
[Location] NVARCHAR(30) NULL)";
System.Data.SQLite.SQLiteConnection.CreateFile("sample.db3");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=sample.db3"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Mytable(Name,Location) values('Fisch','Irgendwo')";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * from Mytable";
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["Name"] + ":" + reader["Location"]);
}
}
conn.Close();
Console.ReadLine();
}
}
}
public static Statistic GetUserStatistic(int userId)
{
Statistic statistic;
connection.Open();
command = new SQLiteCommand("SELECT * FROM 'Statistic' WHERE user_id = @param1;", connection);
command.Parameters.Add(new SQLiteParameter("@param1", userId));
reader = command.ExecuteReader();
List<ExerciseResultInfo> results = new List<ExerciseResultInfo>();
ExerciseResultInfo resultInfo;
while (reader.Read())
{
int level = Convert.ToInt32(reader["level"]);
int exerciseId = Convert.ToInt32(reader["exercise_id"]);
int assesment = Convert.ToInt32(reader["assesment"]);
DateTime dateOfPassing = Convert.ToDateTime(reader["dateOfPass"]);
int speed = Convert.ToInt32(reader["speed"]);
int errors = Convert.ToInt32(reader["errors"]);
resultInfo = new ExerciseResultInfo(exerciseId, dateOfPassing, errors, assesment, speed);
results.Add(resultInfo);
}
statistic = new Statistic(userId, results);
connection.Close();
return statistic;
}
public static CompanyModuleModel[] GetCompanyModuleList(CompanyModel data)
{
List <CompanyModuleModel> results = new List <CompanyModuleModel>();
using (var conn = GetConnection())
{
try
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from CompanyModule where CompanyID=@CompanyID;";
cmd.Parameters.Add("CompanyID", DbType.Int32).Value = data.CompanyID;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
results.Add(ReadCompanyModuleData(reader));
}
}
catch (Exception ex)
{
Utility.LogHelper.WriteError("Encript.SqlLite", "DeleteCompany", ex);
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(results.ToArray());
}
private int findNOCert()
{
try
{
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 count(*) FROM Certificato WHERE Presente='NO';";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(reader.GetInt32(0)); //ritorna la prima colonna, quella avente il count
}
}
conn.Close();
}
}
return(0);
}
catch
{
return(-1);
}
}
public string addActor(string actor, string age)
{
// search DB to see if actor exists
// update DB accordingly
actor = actor.Trim();
com.CommandText = "Select * FROM Actors WHERE actor = '" + actor + "'";
com.ExecuteNonQuery();
actors.Clear();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
if (!String.IsNullOrEmpty(reader["actor"].ToString()))
{
actors.Add(reader["actor"].ToString());
}
}
}
if (actors.Count().Equals(0))
{
// Add entry into imdb
com.CommandText = "INSERT INTO Actors (actor,age) Values ('" + actor + "', '" + age + "')";
com.ExecuteNonQuery();
return("success");
}
return("actor already in imdb");
}
public string[] LocalGetOneRowDataById(string table, string[] query, string id)
{
// ORDER BY id ASC
string innerSQL = "";
for (int i = 0; i < query.Length; i++)
{
innerSQL += query[i] + ",";
}
if (!innerSQL.Equals(""))
{
innerSQL = innerSQL.Substring(0, innerSQL.Length - 1); // 去掉最后的逗号
}
string sql = "SELECT " + innerSQL + " FROM " + table + " WHERE id='" + id + "'"; //建表语句
LocalDbOpen();
SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, localSqlConnectionCommand);
cmdCreateTable.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmdCreateTable.ExecuteReader();
string[] resultsStringArray = new string[query.Length];
while (reader.Read())
{
for (int i = 0; i < query.Length; i++)
{
resultsStringArray[i] = reader[query[i]].ToString();
}
}
reader.Close();
LocalDbClose();
return(resultsStringArray);
}
/// <summary>
/// 获得所有分组集合
/// </summary>
public static List <exGroup> GetGroups()
{
List <exGroup> Groups = new List <exGroup>();
string sql = "select * from Groups order by orderID ";
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null);
if (dr != null)
{
while (dr.Read())
{
exGroup group = new exGroup();
{
group.GroupID = Convert.ToString(dr["GroupID"]);
group.GroupName = Convert.ToString(dr["GroupName"]);
group.SuperiorID = Convert.ToString(dr["SuperiorID"]);
group.OrderID = Convert.ToInt32(dr["orderID"]);
}
Groups.Add(group);
}
dr.Close();
}
dr.Dispose();
return(Groups);
}
public static void GetItemsFromDatabase()
{
using (
System.Data.SQLite.SQLiteConnection con =
new System.Data.SQLite.SQLiteConnection("data source=" + inputDir + ".db3"))
{
con.Open();
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = "SELECT Spectrum.ID,peptide,charge,path FROM Spectrum, Files Where Spectrum.codex = Files.codex";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
//store id and peptide into an object holding those values
//holderForPairs.Add(reader["id"].ToString() + "\t" + reader["peptide"].ToString());
inputs.Add(new InputListItems(reader["ID"].ToString(), reader["peptide"].ToString(), reader["charge"].ToString(), reader["path"].ToString()));
}
}
}
con.Close(); // Close the connection to the database
}
new MakeCombinations(inputs);
}
public ICollection <IFinancialProduct> UpdateTheListView(ICollection <IFinancialProduct> listProducts)
{
try
{
connection.Open();
cmd = new System.Data.SQLite.SQLiteCommand(connection);
//cmd.Prepare();
//connection.CreateTable<Stock>();
//connection.CreateTable<Fund>();
cmd.CommandText = "SELECT * FROM Stock";
try
{
allRead = cmd.ExecuteReader();
while (allRead.Read())
{
listProducts.Add(new Stock(allRead.GetInt32(0), allRead.GetString(1), allRead.GetString(2), allRead.GetString(3)));
//listProducts.Add(new Stock((int)allRead["Id"], (string)allRead["categoria"], (string)allRead["name"], (string)allRead["code"])); //Isso não converte.
}
}
catch (Exception e)
{
MessageBox.Show("Erro ao montar a lista de ações: " + e.Message);
return(null);
}
cmd = new System.Data.SQLite.SQLiteCommand(connection);
//cmd.Prepare();
//connection.CreateTable<Stock>();
//connection.CreateTable<Fund>();
cmd.CommandText = "SELECT * FROM Fund";
try
{
allRead = cmd.ExecuteReader();
while (allRead.Read())
{
listProducts.Add(new Fund(allRead.GetInt32(0), allRead.GetString(1), allRead.GetString(2), allRead.GetString(3), allRead.GetString(4)));
//listProducts.Add(new Fund(
//(int)allRead["Id"],
//(string)allRead["categoria"],
//(string)allRead["name"],
//(string)allRead["code"])); //Isso não converte.
}
}
catch (Exception e)
{
MessageBox.Show("Erro ao montar a lista de Fundos: " + e.Message);
return(null);
}
}
catch (Exception e)
{
MessageBox.Show("Erro ao acessar Banco de Dados: " + e);
return(null);
}
finally
{
connection.Close();
}
return(listProducts);
}
public static List<Course> generateAllRelatedPreRequisites(Course course)
{
List<Course> preRequisiteCourses = new List<Course>();
String queryStatement = @"WITH RECURSIVE
pre_req_courses(n) AS (
VALUES('" + course.id + @"')
UNION
SELECT COURSEID FROM PreRequisite, pre_req_courses
WHERE PreRequisite.FOLLOWID=pre_req_courses.n
)
SELECT COURSEID FROM PreRequisite
WHERE PreRequisite.FOLLOWID IN pre_req_courses";
try
{
dbConnection.Open();
sqlCommand = new SQLiteCommand(queryStatement, dbConnection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
String preReqCourseId = dataReader["COURSEID"].ToString();
String readPreReqCourse = "SELECT * FROM Course WHERE ID = '" + preReqCourseId + "'";
SQLiteCommand sqlCommandTemp = new SQLiteCommand(readPreReqCourse, dbConnection);
SQLiteDataReader dataReaderTemp = sqlCommandTemp.ExecuteReader();
Course preReq = null;
while (dataReaderTemp.Read())
{
preReq = new Course(dataReaderTemp["ID"].ToString(),
(int)dataReaderTemp["YR"],
(int)dataReaderTemp["SEM"],
dataReaderTemp["NAME"].ToString(),
dataReaderTemp["DESC"].ToString(),
(int)dataReaderTemp["POINTS"],
dataReaderTemp["ACADEMICORG"].ToString(),
dataReaderTemp["ACADEMICGROUP"].ToString(),
dataReaderTemp["COURSECOMP"].ToString(),
dataReaderTemp["GRADINGBASIS"].ToString(),
dataReaderTemp["TYPOFFERED"].ToString(),
dataReaderTemp["REMARKS"].ToString(),
dataReaderTemp["CAREERID"].ToString());
}
if (preReq != null)
{
Logger.Info("[DatabaseConnection::getPrerequisiteCourse()] PreRequisite Course Information:");
Logger.Info("[DatabaseConnection::getPrerequisiteCourse()]" + "\nID: " + preReq.id + "\nNAME: " + preReq.name + "\nDESC: " + preReq.description);
preRequisiteCourses.Add(preReq);
}
}
dbConnection.Close();
}
catch (Exception e)
{
Logger.Error("DatabaseConnection::generateAllRelatedPreRequisites() " + e.Message);
}
return preRequisiteCourses;
}
public int getIdByUsername(string _username)
{
this.clearCmd();
cmd.CommandText = "Select id from client where username='******';";
reader = cmd.ExecuteReader();
reader.Read();
return reader.GetInt16(0);
}
public JsonTable(SQLiteDataReader results, string tableName)
{
tableData = new JArray();
this.tableName = tableName;
while (results.Read())
tableData.Add(new JsonRow(results).toJsonObject());
}
// READ
public List <Student> DatabaseRead()
{
// Log4Net
Logger logs = new Logger();
List <Student> students = new List <Student>();
try
{
string database_file_path = @"Data Source=.\MemberDB.db";
using (var con = new SQLiteConnection(database_file_path))
{
using (var cmd = new SQLiteCommand(con))
{
con.Open(); // Open the connection
cmd.CommandText = "Select * FROM Members"; // Select all rows
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
// Read the database
while (reader.Read())
{
// Add whats read from the database to a list so it can be accessed later
students.Add(new Student()
{
id = Convert.ToInt32(reader["id"]),
firstname = Convert.ToString(reader["Firstname"]),
surname = Convert.ToString(reader["Surname"]),
doornum = Convert.ToString(reader["DoorNum"]),
streetname = Convert.ToString(reader["StreetName"]),
city = Convert.ToString(reader["City"]),
state = Convert.ToString(reader["State"]),
postcode = Convert.ToInt32(reader["PostCode"]),
belt = Convert.ToString(reader["Belt"]),
accounttype = Convert.ToString(reader["AccountType"]),
joindate = Convert.ToString(reader["JoinDate"]),
active = Convert.ToString(reader["Active"]),
});
}
}
}
}
logs.log(Environment.NewLine, "Info");
logs.log("Database Read Successfully!", "Info");
}
catch (Exception ex)
{
logs.log(Environment.NewLine, "Info");
logs.log(String.Format("Database Read Error! - " + ex.Message), "Error");
logs.log(String.Format("More Info - " + ex.ToString()), "Error");
}
return(students); // Send the updated students list back containing all the database data.
}
private void FrmAddGroup_Load(object sender, EventArgs e)
{
btnAdd.Enabled = false;
lbError.ForeColor = Color.DarkRed;
pnMain.BackColor = Color.White;
cmdSearch = new SQLiteCommand("SELECT `group` FROM groups WHERE `id`=@id", Gib.con);
cmdSearch.Parameters.AddWithValue("@id", Gib.curGroup);
reader = cmdSearch.ExecuteReader();
reader.Read();
tbGroup.Text = reader["group"].ToString();
}
//-------------------------------------------------------------------------------------------------------------------
//prints out the whole table in meesage box/ can do console output
public void ReadOutTable()//read out the whole table
{
sqlite_cmd.CommandText = "SELECT * FROM Emotions";
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
// Print out the content of the text field
// System.Console.WriteLine(sqlite_datareader["text"]); //used for console output
string myReader = sqlite_datareader.GetString(0);
System.Windows.MessageBox.Show(myReader);
}
}
private Guest[] HydrateGuests(SQLiteDataReader reader) {
var guests = new List<Guest>();
while (reader.Read()) {
var guest = new Guest();
guest.Name = reader.GetString(1);
guest.ReservationDate = DateTime.Parse(reader.GetString(2));
guest.Attending = bool.Parse(reader.GetString(3));
guest.Underage = bool.Parse(reader.GetString(4));
guests.Add(guest);
}
return guests.ToArray();
}
public void newPassword(string oldpw, string newpw)
{
SQLiteCommand command = new SQLiteCommand("SELECT * FROM data", db);
System.Data.SQLite.SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string website = (string)reader[0];
string pw = Cipher.Decrypt((string)reader[2], oldpw);
execute("UPDATE data SET password='******' WHERE url='" + website + "';");
}
}
private List<Model.Task> GetTasks(SQLiteDataReader reader)
{
List<Model.Task> list = new List<Model.Task>();
while (reader.Read())
{
var task = new Model.Task();
task.ID = reader[0].ToString();
task.Title = reader[1].ToString();
task.Description = reader[2].ToString();
task.CreationDate = DateTime.Parse(reader[3].ToString());
list.Add(task);
}
return list;
}
public string[] getLatestMsgLog()
{
this.clearCmd();
List<string> msgLogList=new List<string>();
int AllowedMsgAmount = (int)ServerConfigManager.MyConfigs["MaxPullMsgLogAmount"];
cmd.CommandText = "Select * from msgLog ORDER BY sendTimestamp ASC LIMIT "+AllowedMsgAmount.ToString()+";";
reader = cmd.ExecuteReader();
while (reader.Read())
{
msgLogList.Add( System.Uri.EscapeDataString(this.getUsernameById(reader.GetInt16(1)))+","+System.Uri.EscapeDataString(Convert.ToString(reader.GetDateTime(3)))+","+System.Uri.EscapeDataString(reader.GetString(2)));
}
return msgLogList.ToArray();
}
public Dictionary<String, long> GetHighScores()
{
Dictionary<String, long> topResults = new Dictionary<String, long>();
dbConnection.Open();
reader = this.selectTopScores.ExecuteReader();
while (reader.Read())
{
topResults.Add(reader["name"].ToString(), (long)reader["result"]);
}
reader.Dispose();
dbConnection.Close();
return topResults;
}
public string[] getLatestMsgFromUser(string uid)
{
this.clearCmd();
string username = this.getUsernameById(Convert.ToInt16(uid));
string[] msgAr = new string[3];
msgAr[0] = username;
cmd.CommandText="Select msg,sendTimestamp from msgLog where userid="+uid+" ORDER BY sendTimestamp DESC LIMIT 0,1";
reader=cmd.ExecuteReader();
reader.Read();
msgAr[1] = Convert.ToString(reader.GetDateTime(1));
msgAr[2] = reader.GetString(0);
return msgAr;
}
static void Main(string[] args)
{
string createQuery = @"CREATE TABLE IF NOT EXISTS
[TableExample] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Name] NVARCHAR(2048) NULL)";
string createTmpTable = @"create table tmpTable
as
select * from TableExample"; /* vulnerable statement (CVE-2018-8740) */
System.Data.SQLite.SQLiteConnection.CreateFile("example.db");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=example.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO TableExample(Name) values('first user')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO TableExample(Name) values('second user')";
cmd.ExecuteNonQuery();
cmd.CommandText = createTmpTable;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO tmpTable(Id, Name) values('3','tmp user')";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * from tmpTable";
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("Displaying Temporary table with 'create table [table name] as' syntax ");
while (reader.Read())
{
Console.WriteLine(reader["Id"] + ". " + reader["Name"]);
}
conn.Close();
Console.WriteLine("end \nPlease refer (CVE-2018-8740)");
}
}
}
Console.ReadLine();
}
public float ReturnTotals(string command)
{
mycommand = new SQLiteCommand(connector);
mycommand.CommandText = command;
reader = mycommand.ExecuteReader();
reader.Read();
float tempValue = 0.0f;
if (!DBNull.Value.Equals(reader[0]))
{
tempValue = System.Convert.ToSingle(reader[0]);
}
reader.Close();
return tempValue;
}
//TODO: Doesn't accept single quote
//TODO: improve focus lost checking
//TODO: Consider making the database with unique index key!! so you can delete an item based on table selection/ or
//TODO: change the delete method to accept string from the grid.
//TODO: Make delete button dissapear when nothing is selected/ or make it read delete parameter from the grid.
//TODO: Make append/change function.
//TODO: Test github new master branch
public void CountRecords()
{
string command = "SELECT * FROM PasterData";
sqlite_connection.Open();
SQLiteCommand sqlite_command = sqlite_connection.CreateCommand();
sqlite_command.CommandText = command;
sqlite_datareader = sqlite_command.ExecuteReader();
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
string myreader = sqlite_datareader.GetString(0);
if (_maxNumber < Convert.ToInt32(myreader))
{
_maxNumber = Convert.ToInt32(myreader);
}
_count++;
}
}
/// <summary>
/// Reads limits from the baseLimits table in the database and constructs a list of them
/// </summary>
/// <returns>List of BaseLimits</returns>
public List<Models.BaseSettlement> getLimit()
{
List<Models.BaseSettlement> Limits = new List<Models.BaseSettlement>();
SetConnection();
OpenDB();
DB = ExecuteReader("select * from baseLimits");
while (DB.Read())
{
Limits.Add(new Models.BaseSettlement((Int64)DB["Limit_ID"], (String)DB["Settlement"], (Int64)DB["Limit"]));
}
CloseDB();
return Limits;
}
/// <summary>
/// Reads Black Markets from the baseBlackMarkets table in the database and constructs a list of them
/// </summary>
/// <returns>List of BaseLimits</returns>
public List<Models.BaseBlackMarket> getBlackMarket()
{
List<Models.BaseBlackMarket> BMs = new List<Models.BaseBlackMarket>();
SetConnection();
OpenDB();
DB = ExecuteReader("select * from baseBlackMarkets");
while (DB.Read())
{
BMs.Add(new Models.BaseBlackMarket((Int64)DB["ID"], (String)DB["BlackMarket"], (Int64)DB["Limit"]));
}
CloseDB();
return BMs;
}
protected void Button2_Click(object sender, EventArgs e)
{
con = new SQLiteConnection("Data Source=e:\\db\\project.s3db");
con.Open();
SQLiteCommand command = new SQLiteCommand("Select * from train", con);
reader = command.ExecuteReader();
//SQLiteDataAdapter ad=new SQLiteDataAdapter(,con);
//GridView gv = new GridView();
while (reader.Read())
{
trainno_list.Items.Add(reader.GetValue(0).ToString());
}
bookdiv.Style.Value = "visibility:visible";
con.Close();
}
/// <summary>
/// 获取用户资料
/// </summary>
/// <param name="UserID"></param>
/// <returns></returns>
public static UserVcard GetUserVcard(string UserID)
{
UserVcard card = null;
string sql = "select Vcard from UsersVcard where UserID=@UserID";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] {
new System.Data.SQLite.SQLiteParameter("@UserID", UserID)
};
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, parameters);
if (dr != null && dr.Read())
{
card = Factory.CreateInstanceObject(Convert.ToString(dr["Vcard"])) as UserVcard;
dr.Close();
}
dr.Dispose();
return(card);
}
public void CreateChart(ZedGraphControl zgc)
{
GraphPane myPane = zgc.GraphPane;
PointPairList lstIncome = new PointPairList();
PointPairList lstExpense = new PointPairList();
// Set the title and axis labels
myPane.Title.FontSpec.Family = "Browallia New";
myPane.Title.FontSpec.Size = 24;
myPane.Title.Text = "สรุป";
myPane.XAxis.Title.FontSpec.Family = "Browallia New";
myPane.XAxis.Title.FontSpec.Size = 16;
myPane.XAxis.Title.Text = "เดือน";
myPane.XAxis.Type = AxisType.Text;
myPane.YAxis.Title.FontSpec.Family = "Browallia New";
myPane.YAxis.Title.FontSpec.Size = 24;
myPane.YAxis.Title.Text = "จำนวนเงิน";
// Load data for this month
sql_cmd.CommandText = "SELECT Month, TotalIncome, TotalExpense FROM Month WHERE Year = '" + CustomDate.GetThaiYear(DateTime.Today.Year) + "'";
sql_reader = sql_cmd.ExecuteReader();
while (sql_reader.Read())
{
monthsLabel.Add(CustomDate.GetThaiMonth(sql_reader.GetInt32(0)));
lstIncome.Add(0, sql_reader.GetDouble(1));
lstExpense.Add(0, sql_reader.GetDouble(2));
}
sql_reader.Close();
myPane.XAxis.Scale.FontSpec.Family = "Browallia New";
myPane.XAxis.Scale.FontSpec.Size = 16;
myPane.XAxis.Scale.TextLabels = monthsLabel.ToArray();
BarItem myCurve = myPane.AddBar("รายรับ", lstIncome, Color.Blue);
BarItem myCurve2 = myPane.AddBar("รายจ่าย", lstExpense, Color.Red);
myPane.Chart.Fill = new Fill(Color.White, Color.FromArgb(255, 255, 166), 45.0F);
myPane.YAxis.Scale.Max += myPane.YAxis.Scale.MajorStep;
//BarItem.CreateBarLabels(myPane, false, "#,#0.00#", "Tahoma", 10, Color.Black, true, false, false);
zgc.AxisChange();
}
public AlertLog()
{
InitializeComponent();
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM ALERTLOG;";
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
var MsgNum = sqlite_datareader["msgid"].ToString();
var RdsNum = sqlite_datareader["rdsid"].ToString();
var SendTime = sqlite_datareader["alerttime"].ToString();
var ClientId = sqlite_datareader["clientid"].ToString();
var CheckTime = sqlite_datareader["checktime"].ToString();
txtLog.AppendText(string.Format("第{0}號訊息_訊息代碼:{1}_發送時間:{2}_手機ID:{3}_確認時間:{4}\r\n", MsgNum, RdsNum, SendTime, ClientId, CheckTime));
}
sqlite_datareader.Close();
sqlite_conn.Close();
}
public Message ReadMail(string userMail, int rowNumber)
{
// Declare a variable for the converted results.
Message result;
// Open the database.
dbConnection.Open();
// Retrieve all records from the table called "mails" for the specified usermail.
dbCommand.CommandText = "SELECT * FROM mails WHERE address='" + userMail + "';";
// Execute the newly created command.
dbQuery = dbCommand.ExecuteReader();
// Read the retrieved query, and convert the result to bytes from the current string.
while (dbQuery.Read())
{
// Check if the current row is the one specified.
if (dbQuery.StepCount == rowNumber)
{
// Convert the result to bytes and then to a message and put this into the message variable.
result = new Message(Convert.FromBase64String(dbQuery["rawmessage"].ToString()));
// Close the query-reader again.
dbQuery.Close();
// Close the database again.
dbConnection.Close();
// Break out of the loop by returning the converted mathing result.
return result;
}
}
// Close the query-reader again.
dbQuery.Close();
// Close the database again.
dbConnection.Close();
// Return a null if nothing is found.
return null;
}
public string getNome()
{
string Label = "";
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conexao))
{
conexao.Open();
com.CommandText = "Select PlayerName FROM GameResults WHERE ID =" + idPlayer.ToString();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
while (reader.Read())
{
Label = reader["PlayerName"].ToString();
}
conexao.Close(); // Close the connection to the database
}
return(Label);
}
public void findMovies(string actor, ref List <string> movie_list)
{
actor = actor.TrimEnd();
com.CommandText = "Select * FROM MovieAttributes WHERE actor = '" + actor + "'";
com.ExecuteNonQuery();
movies.Clear();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
if (!String.IsNullOrEmpty(reader["movie"].ToString()))
{
movies.Add(reader["movie"].ToString());
}
}
}
movie_list = movies;
}
public void findActors(string movie, ref List <string> actor_list)
{
movie = movie.Trim();
com.CommandText = "Select * FROM MovieAttributes WHERE movie = '" + movie + "'";
com.ExecuteNonQuery();
actors.Clear();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
if (!String.IsNullOrEmpty(reader["actor"].ToString()))
{
actors.Add(reader["actor"].ToString());
}
}
}
actor_list = actors;
}
private void resList_DoubleClick(object sender, EventArgs e)
{
int choosed = codeNum[resList.SelectedIndex];
string sql = "select * from kotob where id = " + choosed.ToString();
sqlite.SQLiteCommand s_com = new sqlite.SQLiteCommand(sql, s_con);
sqlite.SQLiteDataReader reader = s_com.ExecuteReader();
reader.Read();
resName.Text = reader["name"].ToString();
resType.Text = reader["type"].ToString();
resTrans.Text = reader["translator"].ToString();
resAuthor.Text = reader["author"].ToString();
resCode.Text = reader["code"].ToString();
resNum.Text = choosed.ToString();
Clipboard.SetText(resNum.Text);
}
protected void Button3_Click(object sender, EventArgs e)
{
con = new SQLiteConnection("Data Source=e:\\db\\project.s3db");
con.Open();
SQLiteCommand command= new SQLiteCommand("Select * from train where trainno="+trainno_list.SelectedValue,con);
reader=command.ExecuteReader();
String str;
if(reader.Read())
if (Int32.Parse(TextBox1.Text) <= reader.GetInt16(3))
{
str = "tickets.aspx?trainno=" + trainno_list.SelectedValue + "&type=" + type.SelectedValue + "&ticketno=" + TextBox1.Text;
con.Close();
Response.Redirect(str);
}
else
err.InnerText = TextBox1.Text+" Tickets not avaliable. Please check and enter again";
con.Close();
}
private void Form6_Load(object sender, EventArgs e)
{
comboBox1.Text = "";
var m_dbConnection = new SQLiteConnection("Data Source=28cm_db.sqlite;Version=3;");
int N = 0;
sql = "SELECT COUNT(rowid) FROM Groups";
command = new SQLiteCommand(sql, m_dbConnection);
m_dbConnection.Open();
reader = command.ExecuteReader();
if (reader.Read())
N = Convert.ToInt32(reader["COUNT(rowid)"]);
reader.Close();
m_dbConnection.Close();
for (int i = 1; i <= N; i++)
{
sql = "SELECT name FROM Groups WHERE rowid = ('" + i + "')";
command = new SQLiteCommand(sql, m_dbConnection);
m_dbConnection.Open();
reader = command.ExecuteReader();
if (reader.Read())
{
group_name = Convert.ToString(reader["name"]);
}
reader.Close();
m_dbConnection.Close();
comboBox1.Items.Add(group_name);
}
}
public override bool Get1stTransaction(string tableName, ref int timestamp, ref string transType, ref string orderType, ref decimal price, ref decimal quantity, ref decimal balance, ref string remark)
{
bool result = false;
using (SQLiteCommand cmd = con.CreateCommand())
{
cmd.CommandText = string.Format("SELECT * from {0} ORDER BY timestamp ASC;", tableName);
iteratorTransReader = cmd.ExecuteReader();
if (iteratorTransReader.Read())
{
timestamp = Convert.ToInt32(iteratorTransReader["timestamp"]);
transType = iteratorTransReader["trans_type"].ToString();
orderType = iteratorTransReader["order_type"].ToString();
price = Convert.ToDecimal(iteratorTransReader["price"]);
quantity = Convert.ToDecimal(iteratorTransReader["quantity"]);
balance = Convert.ToDecimal(iteratorTransReader["balance"]);
remark = iteratorTransReader["remark"].ToString();
result = true;
}
else result = false;
}
return result;
}
public void execute(ref ObservableCollection<TagedItem> _TagedItemCollection)
{
sqliteCon.Open();
createCommand = new SQLiteCommand(query, sqliteCon);
createCommand.ExecuteNonQuery();
dataReader = createCommand.ExecuteReader();
_TagedItemCollection.Clear();
while (dataReader.Read())
{
_TagedItemCollection.Add(new TagedItem
{
FileName = dataReader.GetString(2),
FileExension = dataReader.GetString(3),
ModifyDate = dataReader.GetString(4)
});
}
sqliteCon.Close();
}
public List<Tuple<string, string, string>> GetAllAuthors()
{
var authors = new List<Tuple<string, string, string>>();
m_dbConnection = new SQLiteConnection("Data Source=" + db + ";Version=3;");
m_dbConnection.Open();
string sql = "SELECT * FROM authors";
command = new SQLiteCommand(sql, m_dbConnection);
reader = command.ExecuteReader();
string id = "";
string name = "";
string picture = "";
while (reader.Read())
{
id = reader["id"].ToString();
name = reader["name"].ToString();
picture = reader["picture"].ToString();
authors.Add(new Tuple<string, string, string>(id, name, picture));
}
reader.Close();
m_dbConnection.Close();
return authors;
}
public string GetIconName(UInt16 RcpId)
{
string ret = "";
try
{
_SQLiteConnection.Open();
string qury = string.Format("select * from tb_recipe where ID={0}", RcpId);
SQLiteCommand command = new SQLiteCommand(qury, _SQLiteConnection);
_reader = command.ExecuteReader();
while (_reader.Read())
ret = _reader["IconName"].ToString();
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
_reader.Close();
_SQLiteConnection.Close();
}
return ret;
}
public List<UserInfo> ReadUserInfo()
{
// Create a list for the user info.
List<UserInfo> listUserInfo = new List<UserInfo>();
// Open the database.
dbConnection.Open();
// Retrieve all records from the table called "mailaddresses".
dbCommand.CommandText = "SELECT * FROM mailaddresses;";
// Execute the newly created command.
dbQuery = dbCommand.ExecuteReader();
// Read the retrieved query, and write the results to the newly created list.
while (dbQuery.Read())
listUserInfo.Add(new UserInfo
{
userMail = dbQuery["address"].ToString(),
password = dbQuery["password"].ToString(),
autoLogin = dbQuery["autologin"].ToString()
});
// Close the query-reader again.
dbQuery.Close();
// Close the database again.
dbConnection.Close();
// Return the created list.
return listUserInfo;
}
public SendAlertForm()
{
InitializeComponent();
// Open
sqlite_conn.Open();
// 要下任何命令先取得該連結的執行命令物件
sqlite_cmd = sqlite_conn.CreateCommand();
// 查詢Status表單，取告警代號、告警說明
sqlite_cmd.CommandText = "SELECT * FROM Status";
// 執行查詢Status塞入 sqlite_datareader
sqlite_datareader = sqlite_cmd.ExecuteReader();
RDSStsDescList = new List<string>();
// 一筆一筆列出查詢的資料
while (sqlite_datareader.Read())
{
// Print out the content of the RDSStsID field:
RDSStsID = sqlite_datareader["RDSStsID"].ToString();
RDSStsDesc = sqlite_datareader["RDSStsDesc"].ToString();
cboWarn.Items.Add(RDSStsID);
RDSStsDescList.Add(RDSStsDesc);
//MessageBox.Show(s);
}
sqlite_datareader.Close();
cboWarn.SelectedIndex = 0;
//結束，關閉資料庫連線
sqlite_conn.Close();
}