public string ExecuteQuery(string sql_query, SQLiteParameter[] parameters, out SQLiteDataReader reader)
{
string res = string.Empty;
reader = null;
_connection.Open();
_command = _connection.CreateCommand();
_command.CommandText = sql_query;
if (parameters != null)
{
_command.Parameters.Clear();
_command.Parameters.AddRange(parameters);
}
try
{
reader = _command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
res = CreateExceptionMessage(ex);
}
return res;
}
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();
}
}
/* Returns a Set using a SQLiteDataReader and a dictionary mapping the attributes to a index.
*/
private Set SetExtractor(SQLiteDataReader reader, Dictionary<string, int> attributeIndexDict)
{
return new Set(reader.GetInt32(attributeIndexDict["day"]), reader.GetInt32(attributeIndexDict["month"]),
reader.GetInt32(attributeIndexDict["year"]), reader.GetInt32(attributeIndexDict["setSeqNum"]),
reader.GetInt32(attributeIndexDict["activityId"]), reader.GetDouble(attributeIndexDict["weight"]),
reader.GetInt32(attributeIndexDict["secs"]), reader.GetInt32(attributeIndexDict["reps"]));
}
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();
}
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");
}
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 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);
}
/// <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);
}
protected override Exemplar InitEntryByReader(System.Data.SQLite.SQLiteDataReader reader)
{
Exemplar exemplar = new Exemplar();
ulong id = System.Convert.ToUInt64(reader.GetInt32(reader.GetOrdinal("id")));
string loanPeriodAsString = reader.GetString(reader.GetOrdinal("loanPeriod"));
DateTime loanPeriod = new DateTime();
if (loanPeriodAsString != null || loanPeriodAsString != "")
{
loanPeriod = DateTime.Parse(loanPeriodAsString);
}
string stateString = reader.GetString(reader.GetOrdinal("state"));
BookStates state = (BookStates)Enum.Parse(typeof(BookStates), stateString, true);
string signatur = reader.GetString(reader.GetOrdinal("signatur"));
ulong customerId = System.Convert.ToUInt64(reader.GetInt32(reader.GetOrdinal("customerID")));
ulong bookId = System.Convert.ToUInt64(reader.GetInt32(reader.GetOrdinal("bookID")));
exemplar.ExemplarId = id;
exemplar.LoanPeriod = loanPeriod;
exemplar.State = state;
exemplar.Signatur = signatur;
exemplar.BookId = bookId;
return(exemplar);
}
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();
}
public List<Tuple<string, string, string>> GetAuthorBooks(string id)
{
var authorBooks = new List<Tuple<string, string, string>>();
m_dbConnection = new SQLiteConnection("Data Source=" + db + ";Version=3;");
m_dbConnection.Open();
string sql = "SELECT * FROM books WHERE auteur = " + id;
command = new SQLiteCommand(sql, m_dbConnection);
reader = command.ExecuteReader();
string title = "";
string picture = "";
string synopsis = "";
while (reader.Read())
{
title = reader["title"].ToString();
picture = "../" + reader["picture"].ToString();
synopsis = reader["synopsis"].ToString();
authorBooks.Add(new Tuple<string, string, string>(title, picture, synopsis));
}
m_dbConnection.Close();
return authorBooks;
}
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 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());
}
/// <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);
}
/// <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 printToConsole(SQLiteDataReader readerDB, int index)
{
if (readerDB.IsDBNull(index))
{
//return "NULL";
return "";
}
else
{
String dataObject = readerDB.GetFieldType(index).ToString();
switch (dataObject)
{
case "System.Int32":
return readerDB.GetInt32(index).ToString();
case "System.DateTime":
DateTime date = readerDB.GetDateTime(index);
return Convert.ToString(date);
case "System.String":
return readerDB.GetString(index);
default:
return "Unknown";
}
}
}
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();
}
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);
}
}
private Person BuildUserStructure(SQLiteDataReader reader)
{
string name = reader.GetString(0);
string fullName = reader.GetString(1);
long personId = reader.GetInt64(2);
return new Person(name, fullName, personId);
}
/* Returns a Activity using a SQLiteDataReader and a dictionary mapping the attributes to a index.
*/
private Activity ActivityExtractor(SQLiteDataReader reader, Dictionary<string, int> attributeIndexDict)
{
return new Activity(reader.GetInt32(attributeIndexDict["activityId"]),
reader.GetInt32(attributeIndexDict["seqNum"]), reader.GetString(attributeIndexDict["workoutName"]),
reader.GetInt32(attributeIndexDict["numSets"]), reader.GetInt32(attributeIndexDict["workoutVersion"]),
reader.GetString(attributeIndexDict["exerciseName"]), reader.GetInt32(attributeIndexDict["exerciseVersion"]));
}
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();
}
/// <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 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 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 ResourceRecord(SQLiteDataReader rpReader)
{
Time = DateTimeUtil.FromUnixTime(Convert.ToUInt64(rpReader["time"])).LocalDateTime.ToString();
Fuel = Convert.ToInt32(rpReader["fuel"]);
FuelDifference = Convert.ToInt32(rpReader["fuel_diff"]);
Bullet = Convert.ToInt32(rpReader["bullet"]);
BulletDifference = Convert.ToInt32(rpReader["bullet_diff"]);
Steel = Convert.ToInt32(rpReader["steel"]);
SteelDifference = Convert.ToInt32(rpReader["steel_diff"]);
Bauxite = Convert.ToInt32(rpReader["bauxite"]);
BauxiteDifference = Convert.ToInt32(rpReader["bauxite_diff"]);
InstantConstruction = Convert.ToInt32(rpReader["instant_construction"]);
InstantConstructionDifference = Convert.ToInt32(rpReader["instant_construction_diff"]);
Bucket = Convert.ToInt32(rpReader["bucket"]);
BucketDifference = Convert.ToInt32(rpReader["bucket_diff"]);
DevelopmentMaterial = Convert.ToInt32(rpReader["development_material"]);
DevelopmentMaterialDifference = Convert.ToInt32(rpReader["development_material_diff"]);
ImprovementMaterial = Convert.ToInt32(rpReader["improvement_material"]);
ImprovementMaterialDifference = Convert.ToInt32(rpReader["improvement_material_diff"]);
}
internal DbChannel(SignalSource source, SQLiteDataReader r, IDictionary<string, int> field,
DataRoot dataRoot, IDictionary<string,bool> encryptionInfo)
{
this.SignalSource = source;
this.RecordIndex = r.GetInt32(field["channel_handle"]);
this.Bits = r.GetInt32(field["list_bits"]);
bool isTv = (Bits & BITS_Tv) != 0;
bool isRadio = (Bits & BITS_Radio) != 0;
bool isAnalog = (source & SignalSource.Analog) != 0;
if (isAnalog && !isTv)
{
this.IsDeleted = true;
return;
}
if (isTv) this.SignalSource |= SignalSource.Tv;
if (isRadio) this.SignalSource |= SignalSource.Radio;
this.Lock = (Bits & BITS_Locked) != 0;
this.OldProgramNr = r.GetInt32(field["channel_number"]);
this.Favorites = this.ParseFavorites(Bits);
if (isAnalog)
this.ReadAnalogData(r, field);
else
this.ReadDvbData(r, field, dataRoot, encryptionInfo);
}
public DB_Table()
{
l = Log.getLog();
lock (lockVar)
{
if (sql_con == null)
{
count_ref = 0;
String s = "Data Source=";
s += nome_file_db + ";Versione=3;";
if (!File.Exists(nome_file_db))
{
l.log("DB non esistente. Devo crearlo");
SQLiteConnection.CreateFile(nome_file_db);
DB_Table.sql_con = new SQLiteConnection(s);
DB_Table.sql_con.Open();
Crea_DB();
l.log("DB creato");
}
else
{
DB_Table.sql_con = new SQLiteConnection(s);
DB_Table.sql_con.Open();
}
}
count_ref++;
this.reader = null;
}
}
/// <summary>Will add GSMs from db to the given Experiment</summary>
/// <param name="experiment">Experiment to have its GSMs property updated</param>
public static void AddExperimentGSMToExperimentFromDb(Experiment experiment, SQLiteDataReader reader)
{
ExperimentGSM gsm = new ExperimentGSM(reader["gsm_id"].ToString(), reader["gsm_value"].ToString());
gsm.Id = Convert.ToInt32(reader["id"].ToString());
gsm.ExperimentId = reader["experiment_id"].ToString();
experiment.GSMs.Add(gsm);
}
// 最原始的列出数据
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);
}
}
}
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);
}
/// <summary>Will add a DatasetTableRow from db to the given Experiment</summary>
/// <param name="experiment">Experiment to have its GSMs property updated</param>
public static void AddDatasetTableRowToExperimentFromDb(Experiment experiment, SQLiteDataReader reader)
{
DatasetTableRow dtr = new DatasetTableRow(reader["id_ref"].ToString(), reader["identifier"].ToString(), reader["value"].ToString());
dtr.Id = Convert.ToInt32(reader["id"].ToString());
dtr.ExperimentId = reader["experiment_id"].ToString();
experiment.DatasetTable.Add(dtr);
}
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();
}
}
}
protected void ReadDvbData(SQLiteDataReader r, IDictionary<string, int> field, DataRoot dataRoot,
IDictionary<string, bool> encryptionInfo)
{
string longName, shortName;
this.GetChannelNames(r.GetString(field["channel_label"]), out longName, out shortName);
this.Name = longName;
this.ShortName = shortName;
this.RecordOrder = r.GetInt32(field["channel_order"]);
this.FreqInMhz = (decimal)r.GetInt32(field["frequency"]) / 1000;
int serviceType = r.GetInt32(field["dvb_service_type"]);
this.ServiceType = serviceType;
this.OriginalNetworkId = r.GetInt32(field["onid"]);
this.TransportStreamId = r.GetInt32(field["tsid"]);
this.ServiceId = r.GetInt32(field["sid"]);
int bits = r.GetInt32(field["list_bits"]);
this.Favorites = this.ParseFavorites(bits);
if ((this.SignalSource & SignalSource.Sat) != 0)
{
int satId = r.GetInt32(field["sat_id"]);
var sat = dataRoot.Satellites.TryGet(satId);
if (sat != null)
{
this.Satellite = sat.Name;
this.SatPosition = sat.OrbitalPosition;
int tpId = satId * 1000000 + (int)this.FreqInMhz;
var tp = dataRoot.Transponder.TryGet(tpId);
if (tp != null)
{
this.SymbolRate = tp.SymbolRate;
}
}
}
this.Encrypted = encryptionInfo.TryGet(this.Uid);
}
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);
}
internal SortieRecord(SQLiteDataReader rpReader)
{
SortieID = Convert.ToInt64(rpReader["id"]);
var rMapID = Convert.ToInt32(rpReader["map"]);
Map = MapService.Instance.GetMasterInfo(rMapID);
var rEventMapDifficulty = (EventMapDifficultyEnum)Convert.ToInt32(rpReader["difficulty"]);
IsEventMap = rEventMapDifficulty != EventMapDifficultyEnum.None;
if (IsEventMap)
EventMapDifficulty = rEventMapDifficulty;
Step = Convert.ToInt32(rpReader["step"]);
Node = Convert.ToInt32(rpReader["node"]);
NodeWikiID = MapService.Instance.GetNodeWikiID(rMapID, Node);
EventType = (SortieEventType)Convert.ToInt32(rpReader["type"]);
if (EventType == SortieEventType.NormalBattle)
BattleType = (BattleType)Convert.ToInt32(rpReader["subtype"]);
if (EventType == SortieEventType.NormalBattle || EventType == SortieEventType.BossBattle)
Time = DateTimeUtil.FromUnixTime(Convert.ToUInt64(rpReader["extra_info"])).LocalDateTime.ToString();
ID = Convert.ToInt64(rpReader["extra_info"]);
Update(rpReader);
}
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);
}
}
internal void Update(SQLiteDataReader rpReader)
{
var rBattleRank = rpReader["rank"];
if (rBattleRank != DBNull.Value)
{
BattleRank = (BattleRank)Convert.ToInt32(rBattleRank);
OnPropertyChanged(nameof(BattleRank));
}
var rDroppedShip = rpReader["dropped_ship"];
if (rDroppedShip != DBNull.Value)
{
DroppedShip = KanColleGame.Current.MasterInfo.Ships[Convert.ToInt32(rDroppedShip)];
OnPropertyChanged(nameof(DroppedShip));
}
IsBattleDetailAvailable = Convert.ToBoolean(rpReader["battle_detail"]);
OnPropertyChanged(nameof(IsBattleDetailAvailable));
var rHeavilyDamagedShipIDs = rpReader["heavily_damaged"];
if (rHeavilyDamagedShipIDs != DBNull.Value)
{
HeavilyDamagedShips = ((string)rpReader["heavily_damaged"]).Split(',').Select(r => KanColleGame.Current.MasterInfo.Ships[int.Parse(r)]).ToList();
OnPropertyChanged(nameof(HeavilyDamagedShips));
}
}
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());
}
internal void Sync(long rowid)
{
IsValid = false;
_command.Parameters[0].Value = rowid;
_reader = _command.ExecuteReader();
_reader.Read();
}
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 Assessment(SQLiteDataReader results)
{
this._id = results[idColumn] as Int64?;
this._title = results[titleColumn] as String;
this._weight = results[weightColumn] as Int64?;
this._type = results[typeColumn] as String;
this._result = results[resultColumn] as Int64?;
}
public Module(SQLiteDataReader results)
{
this._id = results[idColumn] as Int64?;
this._name = results[nameColumn] as String;
this._code = results[codeColumn] as String;
this._credits = results[creditsColumn] as Int64?;
this._year = results[yearColumn] as Int64?;
}
/// <summary>
/// Преобразует ответ в "вопрос"
/// </summary>
/// <param name="reader">Ответ из БД</param>
/// <returns>вопрос</returns>
private Question ToQuestion(SQLiteDataReader reader)
{
Question question = new Question();
question.ID = Convert.ToInt32(reader["ID"]);
question.Value = Convert.ToString(reader["Value"]);
question.Subject = new Subject(Convert.ToInt32(reader["Subject_ID"]));
return question;
}
private static Lexicon ToLexicon(SQLiteDataReader reader)
{
var result = new Lexicon();
result.Id = Convert.ToInt32(reader["id"].ToString());
result.Word = reader["word"].ToString();
result.Translator = reader["translator"].ToString();
return result;
}
private static CompanyModuleModel ReadCompanyModuleData(System.Data.SQLite.SQLiteDataReader reader)
{
CompanyModuleModel item = new CompanyModuleModel();
item.ID = GetInt(reader["ID"]);
item.CompanyID = GetInt(reader["CompanyID"]);
item.ModuleID = GetInt(reader["ModuleID"]);
return(item);
}
/// <summary>
/// Execute a Select query
/// </summary>
/// <param name="query">Select query to execute</param>
/// <returns>Query result as an SQLiteDataReader</returns>
public static SQLiteDataReader Select(string query)
{
SQLiteCommand command = ConnectToDatabase(false); //open db connection
command.CommandText = query;
System.Data.SQLite.SQLiteDataReader reader = command.ExecuteReader();
_ = ConnectToDatabase(true); //close db connection Using Ignore(_) as we don't care what it returns.
return(reader);
}
// 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.
}
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 + "';");
}
}
//-------------------------------------------------------------------------------------------------------------------
//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);
}
}
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();
}
/// <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 static List <CheckinRecordInfo> SimpleQuery(int pageIndex, int pageSize, string key, out int total)
{
string sql = string.Format("select * from CheckRecordTable");
System.Data.SQLite.SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql);
total = Looper(reader).Count;
sql = string.Format(
"select * from CheckRecordTable where (Operation like '%{0}%' " +
"or StudentName like '%{0}%')" +
"limit {1} offset {2}",
key, pageSize, (pageIndex - 1) * pageSize);
reader = SQLiteControl.ExecuteReader(sql);
List <CheckinRecordInfo> results = Looper(reader);
reader.Close();
return(results);
}
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 override bool ExecuteStatement()
{
this.m_com.Transaction = (SQLiteTransaction)this.m_pdo.Transaction;
this.m_com.CommandTimeout = (int)this.m_pdo.GetAttribute(PDO.ATTR_TIMEOUT, 30);
this.m_dr = this.m_com.ExecuteReader();
return true;
}
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 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 static QueryRow Read(SQLiteDataReader reader)
{
var result = new QueryRow();
reader.GetValues();
for (var i = 0; i < reader.FieldCount; i++)
{
result.queryDefinition.Fields.Add(
reader.GetName(i),
new FieldDefinition(
reader.GetName(i),
reader.GetFieldType(i).ToSqlDbType(),
false,
false
)
);
result.values.Add(
reader.GetName(i),
reader.GetValue(i)
);
}
return result;
}
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();
}
