/// <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 void TrimSQLiteDatabase(string filename, string tablename, int MaxRows, string TestColumnName)
{
if (!Program.ENABLE_SQLITE)
{
return;
}
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", filename);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
int ret = 0; string olddate;
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
cmd.CommandText = @"SELECT COUNT(*) AS NumberOfRows FROM " + tablename;
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "NumberOfRows"
});
//int ret = cmd.ExecuteNonQuery();
SQLiteDataReader rdr = cmd.ExecuteReader();
rdr.Read();
ret = rdr.GetInt32(0); //get the number from return parameter position 0
rdr.Close();
if (ret > MaxRows)
{
//obtain the minimum value for the test column (usually a date field)
cmd.CommandText = @"SELECT MIN(" + TestColumnName + ") AS OldestDate FROM " + tablename;
cmd.Parameters.Clear();
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "OldestDate"
});
SQLiteDataReader rdr2 = cmd.ExecuteReader();
rdr2.Read();
olddate = rdr2.GetString(0);
rdr2.Close();
//delete all rows containing that minimum test value
cmd.CommandText = @"DELETE FROM " + tablename + " WHERE " + TestColumnName + " = '" + olddate + "'";
cmd.Parameters.Clear();
cmd.ExecuteNonQuery();
}
}
Program.logEvent("Local SQLite Database Table " + tablename + " Trimmed Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
}
public Block_Edit(int id_)
{
InitializeComponent();
id = id_;
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
SQLiteCommand command = new SQLiteCommand(conn);
command.CommandText = "SELECT * FROM Block WHERE id=@id";
command.Parameters.Add(new SQLiteParameter("@id", id));
using (command)
{
using (SQLiteDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
labelName.Text = rdr.GetValue(1).ToString();
txtImieNazw.Text = rdr.GetValue(1).ToString();
}
}
}
conn.Close();
}
}
protected internal static Bitmap RetrieveImage(int imgid)
{
using (SQLiteCommand command = new SQLiteCommand("select image from images where imgid=@imgid", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@imgid", imgid));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
return null;
}
// Get the size of the image data by passing nothing to getbytes
int dataLength = (int)reader.GetBytes(reader.GetOrdinal("image"), 0, null, 0, 0);
byte[] content = new byte[dataLength];
reader.GetBytes(reader.GetOrdinal("image"), 0, content, 0, dataLength);
using (MemoryStream contentStream = new MemoryStream(content))
{
using (Bitmap streamBitmap = new Bitmap(contentStream))
{
return new Bitmap(streamBitmap);
}
}
}
}
}
public static AttendanceReport[] GetAttendaceSummaryReports(string data_path)
{
SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + data_path);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select * from AttendanceSummary";
SQLiteDataReader reader = cmd.ExecuteReader();
List<AttendanceReport> attendanceReports = new List<AttendanceReport>();
while (reader.Read())
{
int ID = reader.GetInt32(0);
string timeIN = reader.GetString(1);
string timeOUT = reader.GetString(2);
string deltaTime = reader.GetString(3);
string activity = reader.GetString(4);
string mentor = reader.GetString(5);
int index = reader.GetInt32(6);
attendanceReports.Add(new AttendanceReport(ID, timeIN, timeOUT, deltaTime, activity, mentor, index));
}
reader.Close();
conn.Close();
return attendanceReports.ToArray();
}
public static List <CoinBalanceDTO> GetCoinBalance(int idCashier, SQLiteConnection dbConnection)
{
List <CoinBalanceDTO> coinBalances = new List <CoinBalanceDTO>();
using (var dbCommand = new System.Data.SQLite.SQLiteCommand(dbConnection))
{
dbCommand.CommandText = @"
SELECT ID_COIN_BALANCE, COIN_VALUE, QUANTITY
FROM COIN_BALANCE
WHERE ID_CASHIER = @ID_CASHIER
ORDER BY COIN_VALUE DESC";
dbCommand.Parameters.AddWithValue("@ID_CASHIER", idCashier);
using (var reader = dbCommand.ExecuteReader())
{
while (reader.Read())
{
coinBalances.Add(new CoinBalanceDTO()
{
idCoinBalance = Convert.ToInt32(reader["ID_COIN_BALANCE"]),
coinValue = Convert.ToInt32(reader["COIN_VALUE"]),
quantity = Convert.ToInt32(reader["QUANTITY"])
});
}
}
}
return(coinBalances);
}
public static bool IsExistingClientByHostname(string hostname)
{
bool clientExists = false;
string connStr = ConfigurationManager.ConnectionStrings["uWiMPConnString"].ConnectionString;
SQLiteConnection conn = new SQLiteConnection(connStr);
SQLiteDataReader reader;
SQLiteCommand cmd = new SQLiteCommand("SELECT Hostname FROM `MPClients` WHERE Hostname = $Hostname", conn);
cmd.Parameters.Add("$Hostname", DbType.String, 255).Value = hostname.ToLower();
try
{
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.Default);
if (reader.HasRows)
clientExists = true;
}
catch (SQLiteException ex)
{
return false;
}
finally
{
conn.Close();
}
return clientExists;
}
private List<Result> QuerySqllite(Doc doc, string key)
{
string dbPath = "Data Source =" + doc.DBPath;
SQLiteConnection conn = new SQLiteConnection(dbPath);
conn.Open();
string sql = GetQuerySqlByDocType(doc.DBType).Replace("{0}", key);
SQLiteCommand cmdQ = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = cmdQ.ExecuteReader();
List<Result> results = new List<Result>();
while (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("name"));
string docPath = reader.GetString(reader.GetOrdinal("path"));
results.Add(new Result
{
Title = name,
SubTitle = doc.Name.Replace(".docset", ""),
IcoPath = doc.IconPath,
Action = (c) =>
{
string url = string.Format(@"{0}\{1}\Contents\Resources\Documents\{2}#{3}", docsetPath,
doc.Name+".docset", docPath, name);
string browser = GetDefaultBrowserPath();
Process.Start(browser, String.Format("\"file:///{0}\"", url));
return true;
}
});
}
conn.Close();
return results;
}
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);
}
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 List<AttachmentDetail> getWitAttachments()
{
List<AttachmentDetail> attachments;
try
{
sql_con = new SQLiteConnection(Common.localDatabasePath, true);
sql_cmd = new SQLiteCommand("select * from wit_attachments", sql_con);
sql_con.Open();
SQLiteDataReader reader = sql_cmd.ExecuteReader();
attachments = new List<AttachmentDetail>();
while (reader.Read())
{
AttachmentDetail attachment = new AttachmentDetail();
attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
attachment.fileName = StringUtils.ConvertFromDBVal<string>(reader["file_name"]);
attachment.witId = StringUtils.ConvertFromDBVal<string>(reader["wit_id"]);
attachment.fileMimeType = StringUtils.ConvertFromDBVal<string>(reader["file_mime_type"]);
attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
attachment.seqNumber = StringUtils.ConvertFromDBVal<string>(reader["seq_number"]);
attachment.extention = StringUtils.ConvertFromDBVal<string>(reader["extention"]);
attachments.Add(attachment);
}
}
catch (SQLiteException e) { throw e; }
finally { sql_con.Close(); }
return attachments;
}
public static DataTable GetDataTable(string sql)
{
DataTable dt = new DataTable();
try
{
SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db");
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
cnn.Close();
} catch {
// Catching exceptions is for communists
}
return dt;
}
//public static int Insert(string dbFile, string sql,)
public static ObservableCollection<Jewelry> GetAll()
{
ObservableCollection<Jewelry> ob = new ObservableCollection<Jewelry>();
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:/xhz/ms.db;"))
//using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=DB/ms.db;"))
{
conn.Open();
string sql = string.Format("select * from detail");
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
using (SQLiteDataReader dr1 = cmd.ExecuteReader())
{
while (dr1.Read())
{
var d = dr1;
var dd = dr1.GetValue(0);
var data = dr1.GetValue(1);
var insert = dr1.GetValue(2);
var update = dr1.GetValue(3);
}
}
conn.Close();
}
ob.Add(new Jewelry());
return ob;
}
public String GetUrl()
{
String url = "";
try
{
SQLiteConnection myConn = new SQLiteConnection(strConn);
myConn.Open();
string sql = "SELECT * FROM EMPRESA";
SQLiteCommand command = new SQLiteCommand(sql, myConn);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
url = reader["UrlCore"].ToString();
}
myConn.Close();
}
catch (Exception e)
{
Console.WriteLine("ERROR: {0}", e.ToString());
}
return url;
}
//Yollanan barkod numarası database de kayıtlı mı kontrol et.
private string[] BarcodeControl(string barkod)
{
string[] barkodDizi = new string[4];
try
{
this.baglanti.Open();
this.komut = new SQLiteCommand("SELECT * FROM ilaclar WHERE barkod='" + barkod + "';", this.baglanti); //Veritabanında böyle bir barkod kayıtlı mı?
SQLiteDataReader okunan = komut.ExecuteReader();
while (okunan.Read())
{
barkodDizi[0] = okunan[0].ToString();
barkodDizi[1] = okunan[1].ToString();
barkodDizi[2] = okunan[2].ToString();
barkodDizi[3] = okunan[3].ToString();
//MessageBox.Show("barkod: " + okunan[0] + "\nilac: " + okunan[1]);
}
okunan.Close();
komut.Dispose();
baglanti.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(barkodDizi);
}
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();
}
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);
}
}
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);
}
}
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>
/// Возвращает список неотправленных на сервер сессий чтения
/// </summary>
public List<RfidSession> GetUnshippedTags()
{
var sessions = new List<RfidSession>();
var sessionCmd = new SQLiteCommand(@"SELECT * from reading_sessions where delivery_status <> " + (int)RfidSession.DeliveryStatus.Shipped, connection);
using (var sessionReader = sessionCmd.ExecuteReader())
{
while (sessionReader.Read())
{
var session = new RfidSession
{
id = sessionReader.GetInt32(0),
time = sessionReader.GetString(1),
location = sessionReader.GetString(2),
deliveryStatus = (RfidSession.DeliveryStatus)sessionReader.GetInt32(3),
readingStatus = (RfidSession.ReadingStatus)sessionReader.GetInt32(4),
sessionMode = (RfidSession.SessionMode)sessionReader.GetInt32(5)
};
var tagCmd = new SQLiteCommand(@"SELECT * from tubes where session_id = " + session.id, connection);
using (var tagReader = tagCmd.ExecuteReader())
{
while (tagReader.Read())
{
session.tags.Add(tagReader.GetString(1));
}
}
sessions.Add(session);
}
}
return sessions;
}
public void SetZoeziBContent()
{
category = "Kuongea";
question = "Andika majibu ya salamu haya:";
questionTxtBlock.Text = question;
int count = 0;
string[] questions= new string[5];
string[] answers = new string[5];
string query = "select salamu,jibu from salamu_info";
try
{
SQLiteCommand sqliteCommand = new SQLiteCommand(query, Database.GetDatabaseConnection());
sqliteCommand.ExecuteNonQuery();
SQLiteDataReader reader = sqliteCommand.ExecuteReader();
while (reader.Read() && count < 4)
{
questions[count + 1] = reader.GetString(0);
answers[count + 1] = reader.GetString(1).ToLower();
count++;
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
qn1TxtBlock.Text += questions[1];
qn1TxtBlock.Tag = answers[1];
qn2TxtBlock.Text += questions[2];
qn2TxtBlock.Tag = answers[2];
qn3TxtBlock.Text += questions[3];
qn3TxtBlock.Tag = answers[3];
qn4TxtBlock.Text += questions[4];
qn4TxtBlock.Tag = answers[4];
}
public bool IsValid(string username)
{
var myDirectory = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
string parentDirectory = myDirectory.Parent.FullName;
using (var cn = new SQLiteConnection(string.Format(@"Data Source={0}{1} Version=3;", parentDirectory, ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString)))
{
string _sql = @"SELECT id FROM Users " +
"WHERE Name = '"+username+"';";
cn.Open();
var cmd = new SQLiteCommand(_sql, cn);
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Dispose();
cmd.Dispose();
return true;
}
else
{
reader.Dispose();
cmd.Dispose();
return false;
}
}
}
/// <summary>
/// sinartisi pou fortonei apo tin basi ola ta characteristics
/// </summary>
public static List<Characteristics> LoadCharacteristics()
{
List<Characteristics> characteristics = new List<Characteristics>();
DBOpenClose(() =>
{
string sql = "select * from characteristics";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
Characteristics characteristic = new Characteristics(reader.GetInt32(0), reader.GetString(1), reader.GetDouble(2));
characteristics.Add(characteristic);
}
}
catch (Exception ex)
{
Console.WriteLine("LoadCharacteristics " + ex.Message);
}
});
return characteristics;
}
/// <summary>
/// sinartisi pou fortonei ola ta characteristics gia ena sigkekrimeno order id
/// </summary>
public static List<Characteristics> LoadCharacteristicsInOrder(int order_id)
{
List<Characteristics> characteristics = LoadCharacteristics();
List<Characteristics> characteristicsInOrder = new List<Characteristics>();
DBOpenClose(() =>
{
string sql = "select characteristics_id from characteristicsInOrders where order_id ='" + order_id + "'";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
int characteristics_id = reader.GetInt32(0);
foreach (Characteristics ch in characteristics)
{
if (ch.Id == characteristics_id)
characteristicsInOrder.Add(ch);
}
}
}
catch (Exception ex)
{
Console.WriteLine("LoadCharacteristicsInOrder " + ex.Message);
}
});
return characteristicsInOrder;
}
public static DataTable buscaSing4()
{
DataTable tabla = new dataCredito.sing4DataTable();
using (SQLiteConnection con = new SQLiteConnection(Datos.conexion))
{
using (SQLiteCommand comando = new SQLiteCommand())
{
comando.CommandText = "select * from sing4";
comando.Connection = con;
con.Open();
SQLiteDataReader lector = comando.ExecuteReader();
while (lector.Read())
{
DataRow fila = tabla.NewRow();
fila["encabezado"] = lector.GetString(0);
fila["saludo"] = lector.GetString(1);
fila["pie"] = lector.GetString(2);
fila["asesor"] = lector.GetString(3);
fila["fecha"] = lector.GetDateTime(4);
fila["id"] = lector.GetString(5);
fila["idCliente"] = lector.GetInt32(6);
tabla.Rows.Add(fila);
}
}
con.Close();
}
return (tabla);
}
public override void update()
{
//Create a Dictionary to hold the receivers saved in the database
Dictionary<string, Receiver> receivers = new Dictionary<string, Receiver>();
//Create a new connection to the database
using (SQLiteConnection m_dbConnection = new SQLiteConnection(@"Data Source=database.sqlite;Version=3;"))
{
//Open database connection
m_dbConnection.Open();
using (SQLiteCommand command = m_dbConnection.CreateCommand())
{
//Select everything from the 'receivers' table
SQLiteCommand getReceivers = new SQLiteCommand("SELECT * FROM receivers", m_dbConnection);
SQLiteDataReader reader = getReceivers.ExecuteReader();
//Read every entry in the receivers table
while (reader.Read())
{
string name = (string)reader["name"];
Receiver battery = new Receiver((string)reader["name"], (double)reader["weight"], (int)reader["channelCount"], (int)reader["minVoltage"], (int)reader["maxVoltage"]);
//Add the battery into the dictionary using the name as the key and a new Receiver object as the value
receivers.Add(name, battery);
}
}
//Close the database connection
m_dbConnection.Close();
}
//Save the updated savedReceiver list
savedReceivers = receivers;
}
public static DataTable buscaDatosPlural2()
{
DataTable tabla = new dataCredito.datosSing2DataTable();
using (SQLiteConnection con = new SQLiteConnection(Datos.conexion))
{
using (SQLiteCommand comando = new SQLiteCommand())
{
comando.CommandText = "select * from datosPlural2";
comando.Connection = con;
con.Open();
SQLiteDataReader lector = comando.ExecuteReader();
while (lector.Read())
{
DataRow fila = tabla.NewRow();
fila["nombre"] = lector.GetString(0);
fila["direccion"] = lector.GetString(1);
fila["direccion2"] = lector.GetString(2);
fila["ciudad"] = lector.GetString(3);
fila["id"] = lector.GetString(4);
fila["idCliente"] = lector.GetInt32(5);
fila["anio"] = lector.GetInt32(6);
fila["asesor"] = lector.GetString(7);
fila["cantidad"] = lector.GetString(8);
fila["cantidadL"] = lector.GetString(9);
fila["fecha"] = lector.GetDateTime(10);
fila["meses"] = lector.GetString(11);
fila["pie"] = lector.GetString(12);
tabla.Rows.Add(fila);
}
}
con.Close();
}
return (tabla);
}
public static ArrayList GetData(string sql)
{
ArrayList rowList = new ArrayList();
try
{
SQLiteConnection myConn = new SQLiteConnection(GetConnectionString());
myConn.Open();
SQLiteCommand sqCommand = new SQLiteCommand(sql);
sqCommand.Connection = myConn;
SQLiteDataReader reader = sqCommand.ExecuteReader();
while (reader.Read())
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
rowList.Add(values);
}
myConn.Close();
reader.Close();
}
catch (Exception ex)
{
logger.Error(sql + " - " + ex.Message);
}
return rowList;
}
private DoorInfo Retrieve(string DoorID)
{
var ret = new DoorInfo();
var cs = ConfigurationManager.ConnectionStrings["DoorSource"].ConnectionString;
var conn = new SQLiteConnection(cs);
conn.Open();
var cmd = new SQLiteCommand(conn);
cmd.CommandText = "SELECT DoorID, Location, Description, EventID FROM Doors WHERE DoorID = @DoorID LIMIT 1";
cmd.Parameters.Add(new SQLiteParameter("@DoorID", DoorID));
SQLiteDataReader res = null;
try
{
res = cmd.ExecuteReader();
if (res.HasRows && res.Read())
{
ret.DoorID = DoorID;
ret.Location = res.GetString(1);
ret.Description = res.GetString(2);
ret.EventID = res.GetInt64(3);
}
return ret;
}
catch(Exception ex)
{
throw;
}
finally
{
if (null != res && !res.IsClosed)
res.Close();
}
}
public addDeletionInstructionDialog(string workingDirectory, modEditor me, SQLiteConnection conn, int editing)
{
InitializeComponent();
this.workingDirectory = workingDirectory;
this.me = me;
this.conn = conn;
this.editing = editing;
if (editing != 0)
{
string sql = "SELECT file_name, type FROM files_delete WHERE id = " + editing + " LIMIT 1";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
char[] chars = { '/' };
string[] pieces = reader["file_name"].ToString().Split(chars, 2);
if (pieces.Length == 1)
fileName.Text = pieces[0];
else if (pieces.Length == 2)
{
filePrefix.SelectedItem = pieces[0];
fileName.Text = pieces[1];
}
whatIs_Dir.Checked = reader["type"].ToString() == "dir";
whatIs_File.Checked = reader["type"].ToString() == "file";
}
}
}
public bool FindIDByName(string FirstNameToFind, string LastNameToFind)
{
SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=C:\\IDDBShared\\IDDatabase.sqlite;Version=3;");
m_dbConnection.Open();
string sql = "select * from Users";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
bool userDNE = true;
while (reader.Read())
{
string currentFirstName = Convert.ToString(reader["firstName"]);
string currentLastName = Convert.ToString(reader["lastName"]);
if (currentFirstName == FirstNameToFind && currentLastName == LastNameToFind)
{
UserName.Content = reader["firstName"] + " " + reader["lastName"];
IDNumber.Content = Convert.ToString(reader["IDNumber"]);
StartingBalance.Content = "$" + Convert.ToString(reader["beginningBalance"]);
Current_Balance.Content = "$" + Convert.ToString(reader["currentBalance"]);
string photoLocation = Convert.ToString(reader["photo"]);
IMGUserPhoto.Source = new BitmapImage(new Uri(photoLocation));
userDNE = false;
}
}
return userDNE;
}
public ObservableCollection<Jewelry> GetAll()
{
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:/xhz/ms.db;"))
//using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=DB/ms.db;"))
{
conn.Open();
string sql = string.Format("select * from detail");
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
using (SQLiteDataReader dr1 = cmd.ExecuteReader())
{
while (dr1.Read())
{
var d = dr1;
var guid = dr1.GetValue(0);
var data = dr1.GetValue(1);
//var insert = dr1.GetValue(2);
//var update = dr1.GetValue(3);
//没有把helper推送到服务器
Jewelry je1 = helper.xmlPras(data.ToString());
_OCJ.Add(je1);
}
}
conn.Close();
}
OCJ.Add(Jewelry.GetExample());
return OCJ;
}
public Dictionary<string, List<string>> ListPageCategories(long pageRevisionID)
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
using (SQLiteConnection conn = new SQLiteConnection(DatabaseManager.DatabaseEngine.ConnectionString))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(Procedures["List Categories for Page Revision"], conn);
cmd.Parameters.Add(NewParameter("@PageRevisionID", pageRevisionID, DbType.Int64));
Dictionary<string, List<string>> cats = new Dictionary<string, List<string>>();
using (SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
string catsetname = reader["CategorySetName"].ToString();
string catname = reader["CategoryName"].ToString();
List<string> catlist;
if (!cats.TryGetValue(catsetname, out catlist))
{
catlist = new List<string>();
cats.Add(catsetname, catlist);
}
catlist.Add(catname);
}
reader.Close();
return cats;
}
}
}
}
public string userKey(string username)
{
System.Console.WriteLine("----------------------------");
System.Console.WriteLine(username);
SQLiteCommand sqlite_cmd = this.conn.CreateCommand();
sqlite_cmd.CommandText = "INSERT INTO pass (login, privk, pubk) VALUES ('Michel', 'TTTEEESSSTTT', 'aaaa');";
sqlite_cmd.ExecuteNonQuery();
sqlite_cmd.CommandText = "SELECT * FROM pass where login = '******'";
SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();
// The SQLiteDataReader allows us to run through each row per loop
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("----------------------------");
//System.Console.WriteLine("DEBUG Output: '" + sqlite_datareader + "'");
string keyReader = sqlite_datareader.GetString(2);
System.Console.WriteLine(keyReader);
return(keyReader);
// OutputTextBox.Text += idReader + " '" + textReader + "' " + "\n";
}
return("");
}
public FrmEdytujKlienta(int id_)
{
InitializeComponent();
id = id_;
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
SQLiteCommand command = new SQLiteCommand(conn);
command.CommandText = "SELECT * FROM Klienci WHERE id=@id";
command.Parameters.Add(new SQLiteParameter("@id", id));
using (command)
{
using (SQLiteDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
txt_id.Text = rdr.GetValue(0).ToString();
txt_imie.Text = rdr.GetValue(1).ToString();
txt_nazwisko.Text = rdr.GetValue(2).ToString();
txt_pesel.Text = rdr.GetValue(3).ToString();
txt_nr_dowodu.Text = rdr.GetValue(4).ToString();
txt_nr_telefonu.Text = rdr.GetValue(5).ToString();
txt_email.Text = rdr.GetValue(6).ToString();
txt_miejscowosc.Text = rdr.GetValue(7).ToString();
txt_kod_pocztowy.Text = rdr.GetValue(8).ToString();
txt_ulica.Text = rdr.GetValue(9).ToString();
txt_nr_domu.Text = rdr.GetValue(10).ToString();
}
}
}
conn.Close();
}
}
public static void Test( )
{
using( var connection = new SQLiteConnection( ConnectionString ) )
{
connection.Open( );
var command = new SQLiteCommand( "CREATE TABLE IF NOT EXISTS Blaat( Id INTEGER PRIMARY KEY NOT NULL, A, B )", connection );
command.ExecuteNonQuery( );
command = new SQLiteCommand( "INSERT INTO Blaat (A,B) VALUES( 1,2 )", connection );
command.ExecuteNonQuery( );
command = new SQLiteCommand( "INSERT INTO Blaat (A,B) VALUES( 3,4 )", connection );
command.ExecuteNonQuery( );
command = new SQLiteCommand( "SELECT * FROM BLAAT", connection );
using( var reader = command.ExecuteReader( ) )
{
while( reader.Read( ) )
{
Debug.WriteLine( string.Format( "(DIRECT)VALUES --> {0}:{1},{2}", reader.GetValue( 0 ), reader.GetValue( 1 ), reader.GetValue( 2 ) ) );
}
reader.Close( );
}
//command = new SQLiteCommand( "DROP TABLE BLAAT", connection );
//command.ExecuteNonQuery();
connection.Close( );
}
}
public void addElement(string value)
{
string[] values = value.Split('|');
string moduleCode = values[0];
string moduleName = "";
SQLiteConnection con = new SQLiteConnection(Form1.dbConnectionString);
try
{
con.Open();
string query = "Select ModuleName from ModuleInformation where ModuleCode = '" + moduleCode + "'";
SQLiteCommand command = new SQLiteCommand(query, con);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
moduleName = reader.GetString(0);
}
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dataGridView1.Rows.Add(values[0], moduleName, values[1], values[2]);
}
/// <summary>
/// 得到一个表中所有字段
/// </summary>
/// <param name="serverID"></param>
/// <param name="dbName"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public List<Model.Fields> GetFields(string serverID, string dbName, string tableName)
{
List<Model.Fields> fieldsList = new List<Model.Fields>();
using (SQLiteConnection conn = new SQLiteConnection(Common.Config.GetConnectionString(serverID, dbName)))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("PRAGMA table_info({0})", tableName), conn))
{
SQLiteDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
fieldsList.Add(new Model.Fields()
{
Name = dr["name"].ToString(),
Type = dr["type"].ToString(),
IsNull = "1" != dr["notnull"].ToString(),
IsPrimaryKey = "1" == dr["pk"].ToString(),
Default = dr["dflt_value"].ToString(),
IsIdentity = "1" == dr["pk"].ToString() && "INTEGER" == dr["type"].ToString(),
DotNetType = GetFieldType(dr["type"].ToString(), "1" != dr["notnull"].ToString()),
DotNetSqlType = GetFieldSqlType(dr["type"].ToString()),
Note = "",
Length = -1
});
}
dr.Close();
dr.Dispose();
}
}
return fieldsList;
}
public SkillSet(ToonClass @class, Toon toon)
{
this.@Class = @class;
var query = string.Format("SELECT * FROM active_skills WHERE id_toon={0}", toon.PersistentID);
var cmd = new SQLiteCommand(query, DBManager.Connection);
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
int[] ActiveSkillsList = Skills.GetAllActiveSkillsByClass(this.@Class).Take(1).ToArray();
this.ActiveSkills = new ActiveSkillSavedData[6] {
new ActiveSkillSavedData { snoSkill = ActiveSkillsList[0], snoRune = -1 },
new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 },
new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 },
new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 },
new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 },
new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 }
};
this.PassiveSkills = new int[3] { -1, -1, -1 };
var insQuery = string.Format("INSERT INTO active_skills (id_toon,"+
"skill_0,skill_1,skill_2,skill_3,skill_4,skill_5,"+
"rune_0,rune_1,rune_2,rune_3,rune_4,rune_5,"+
"passive_0,passive_1,passive_2) VALUES ({0},{1},-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1 )",
toon.PersistentID, ActiveSkillsList[0]);
var insCmd = new SQLiteCommand(insQuery, DBManager.Connection);
insCmd.ExecuteNonQuery();
}
else
{
this.ActiveSkills = new ActiveSkillSavedData[6] {
new ActiveSkillSavedData { snoSkill = Convert.ToInt32(reader["skill_0"]),
snoRune = Convert.ToInt32(reader["rune_0"]) },
new ActiveSkillSavedData { snoSkill = Convert.ToInt32(reader["skill_1"]),
snoRune = Convert.ToInt32(reader["rune_1"]) },
new ActiveSkillSavedData { snoSkill = Convert.ToInt32(reader["skill_2"]),
snoRune = Convert.ToInt32(reader["rune_2"]) },
new ActiveSkillSavedData { snoSkill = Convert.ToInt32(reader["skill_3"]),
snoRune = Convert.ToInt32(reader["rune_3"]) },
new ActiveSkillSavedData { snoSkill = Convert.ToInt32(reader["skill_4"]),
snoRune = Convert.ToInt32(reader["rune_4"]) },
new ActiveSkillSavedData { snoSkill = Convert.ToInt32(reader["skill_5"]),
snoRune = Convert.ToInt32(reader["rune_5"]) }
};
this.PassiveSkills = new int[3] {
Convert.ToInt32(reader["passive_0"]),
Convert.ToInt32(reader["passive_1"]),
Convert.ToInt32(reader["passive_2"])
};
}
this.HotBarSkills = new HotbarButtonData[6] {
new HotbarButtonData { SNOSkill = ActiveSkills[0].snoSkill, Field1 = -1, ItemGBId = -1 }, // left-click
new HotbarButtonData { SNOSkill = ActiveSkills[1].snoSkill, Field1 = -1, ItemGBId = -1 }, // right-click
new HotbarButtonData { SNOSkill = ActiveSkills[2].snoSkill, Field1 = -1, ItemGBId = -1 }, // bar-1
new HotbarButtonData { SNOSkill = ActiveSkills[3].snoSkill, Field1 = -1, ItemGBId = -1 }, // bar-2
new HotbarButtonData { SNOSkill = ActiveSkills[4].snoSkill, Field1 = -1, ItemGBId = -1 }, // bar-3
new HotbarButtonData { SNOSkill = ActiveSkills[5].snoSkill, Field1 = -1, ItemGBId = -1 }, // bar-4
};
}
public static List<ServerData> GetAllServerData(SQLiteConnection database)
{
string query = String.Format("SELECT [server_id], [name], [description], [location], [image] " +
"[sensor_1_name], [sensor_2_name], [sensor_3_name], [sensor_4_name], " +
"[sensor_1_unit], [sensor_2_unit], [sensor_3_unit], [sensor_4_unit], " +
"[sensor_1_id], [sensor_2_id], [sensor_3_id], [sensor_4_id]," +
"[sensor_1_range], [sensor_2_range], [sensor_3_range], [sensor_4_range] " +
"FROM [servers]");
List<ServerData> serverList = new List<ServerData>();
lock (database)
{
using (SQLiteCommand command = new SQLiteCommand(query, database))
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SQLiteDataReader serverReader = command.ExecuteReader();
while (serverReader.Read())
{
serverList.Add(ResultToServerData(serverReader));
}
serverReader.Close();
}
database.Close();
}
return serverList;
}
public List<HistoryRow> getAll()
{
List<HistoryRow> list = new List<HistoryRow>();
Debug.WriteLine("Read history");
try
{
string sql = "select * from history order by date asc";
conn.Open();
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
list.Add(new HistoryRow(reader.GetInt16(0), reader.GetString(3), reader.GetString(2), reader.GetString(1)));
}
}
catch (SQLiteException e)
{
Debug.WriteLine("Not connected : " + e.ToString());
return list;
}
finally
{
Debug.WriteLine("End..");
conn.Close();
}
return list;
}
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);
}
/// <summary>
/// Kan udføre en selv skrevet SQL query/kommando
/// og hvis det er en select kan man selv
/// vælge man bruge columns eller position som key
/// </summary>
public static List <Dictionary <T, object> > ExecuteQuery <T>(string cmd)
{
SQLite_LIB.SQLiteDataReader sql_reader;
var result = new List <Dictionary <T, object> >();
bool isRead = false;
//Metoder der vil hente data
string[] readMethods = { "SELECT", "PRAGMA" };
//Find ud af om vi skal hente data
foreach (var item in readMethods)
{
if (cmd.ToUpper().StartsWith(item))
{
isRead = true;
break;
}
}
//SQLite_LIB.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter();
_lastQuery = cmd;
sql_cmd.CommandText = cmd;
//udfør kommando hvor man ikke venter på data
if (!isRead)
{
sql_cmd.ExecuteNonQuery();
return(null);
}
//Udfør kommando hvor man skal have data tilbage
sql_reader = sql_cmd.ExecuteReader();
while (sql_reader.Read())
{
var row = new Dictionary <T, object>();
for (int i = 0; i < sql_reader.FieldCount; i++)
{
//Find ud af om vi skal bruge columns navn eller position som key
object key;
if (typeof(T) == typeof(String))
{
key = sql_reader.GetName(i);
}
else
{
key = i;
}
row.Add((T)key, sql_reader[i]);
}
result.Add(row);
}
sql_reader.Close();
return(result);
}
public static bool ExistTable(SqliteCommand cmd, string TableName)
{
bool ret = false;
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='" + TableName + "'";
using (var reader = cmd.ExecuteReader())
{
ret = reader.HasRows;
}
return(ret);
}
public static bool Exist(SqliteCommand cmd, string TableName, int Id)
{
bool ret = false;
string sql = "SELECT * FROM `" + TableName + "` WHERE `Id`=" + Id;
cmd.CommandText = sql;
using (var rd = cmd.ExecuteReader())
{
ret = rd.HasRows;
}
return(ret);
}
private SQLiteDataReader PopulateListView()
{
SQLiteDataReader reader;
System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=db.sqlite3");
SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con);
con.Open();
com.CommandText = "Select * FROM inventory_";
reader = com.ExecuteReader();
return(reader);
}
//-------------------------------------------------------------------------------------------------------------------
//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);
}
}
public static bool Exist <T>(SqliteCommand cmd, string TableName, T Data) where T : SQLiteData
{
bool ret = false;
string sql = "SELECT * FROM `" + TableName + "` WHERE `Id`=" + Data.Id;
cmd.CommandText = sql;
using (var rd = cmd.ExecuteReader())
{
ret = rd.HasRows;
}
return(ret);
}
// 条件查询数据按字段值返回
public string[] SelectDataFromSqlite(string sql, string[] data, int len)
{
cmd.CommandText = sql;
try
{
using (SQLiteDataReader readerData = cmd.ExecuteReader())
{
while (readerData.Read())
{
for (int i = 0; i < len; i++)
{
data[i] = readerData[i].ToString();
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
return(data);
}
public static bool Check(SqliteCommand cmd, string TableName, string Where)
{
bool ret = false;
string sql = "SELECT * FROM `" + TableName + "` " + Where;
cmd.CommandText = sql;
using (var rd = cmd.ExecuteReader())
{
ret = rd.HasRows;
}
return(ret);
}
/// <summary>
///
/// </summary>
/// <param name="Gerar">True se for para gerar novo resultado, False para apenas apresentar</param>
/// <param name="Acerto">True para incrementar o resultado, False para decrementar o resultado</param>
public string geraResultado(Boolean Gerar = true, Boolean Acerto = true)
{
string Label = "";
Boolean Minimo = false;
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conexao))
{
conexao.Open();
if (Gerar)
{
com.CommandText = "Select Score FROM GameResults WHERE ID =" + idPlayer.ToString();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
while (reader.Read())
{
Minimo = Convert.ToInt32(reader["Score"]) == 0 ? true : false;
}
if ((!Minimo && !Acerto) || (Acerto))
{
com.CommandText = "UPDATE GameResults SET Score=Score" + (Acerto ? "+" : "-") + "1 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);
}
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 void SqliteConnection()
{
string createQuery = @"CREATE TABLE IF NOT EXISTS
[TABLE1] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)";
System.Data.SQLite.SQLiteConnection.CreateFile("testFile.txt");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=testFile.txt"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
cmd.ExecuteReader();
}
}
}
public static SQLiteDataReader ExecuteDataReader(string sql, string db)
{
string datasource = db;// Application.StartupPath + "/nongli.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 = "******";
conn.ConnectionString = connstr.ToString();
conn.Open();
//取出数据
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
return(reader);
}
private List <string> RetrieveLateIDNumbers()
{
///
///Retrieve unfinished transactions from LOCAL SQLite DATABASE
///
List <string> CardNumbers = new List <string>();
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
cmd.CommandText = @"SELECT * FROM RES_KEY WHERE (ID_CARD_NO_IN is null or ID_CARD_NO_IN = '')";
SQLiteDataReader r = cmd.ExecuteReader();
while (r.Read())
{
DateTime Datetime = Convert.ToDateTime(r["OUT_DATETIME"]);
//Select Card records which have a Date of today, yesterday or two days ago. This way records which have been accidentally created will not continue to send reminders forever
if ((Datetime.Date == DateTime.Today) || (Datetime.Date == DateTime.Today - new TimeSpan(1, 0, 0, 0)) || (Datetime.Date == DateTime.Today - new TimeSpan(2, 0, 0, 0)))
{
CardNumbers.Add(Convert.ToString(r["ID_CARD_NO_OUT"]));
}
}
dbConn.Close();
}
Program.logEvent("Local SQLite Database Transaction Inserted Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
return(CardNumbers);
}
public void ReadFromWearableAppUsedAPIs()
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=" + kPublicNativeAPIDbFile))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
// Re-open file
conn.Open();
string queryText = "select [AppName], [APIName] from " + kWearableAPITable;
cmd.CommandText = queryText;
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["AppName"] + " : " + reader["APIName"]);
}
conn.Close();
}
}
}
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);
}
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);
}
private void button1_Click(object sender, EventArgs e)
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=testFile.txt"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = textBox1.Text;
cmd.ExecuteNonQuery();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
textBox2.Text = (reader[0].ToString());
}
}
}
}
}
public void GivenADatabaseCreator_WhenICreateADatabase_ThenTheDatabaseIsPopulated()
{
TestPrerequisite(GivenADatabaseCreator_WhenICreateADatabase_ThenTheDatabaseIsCreated);
using (var connection = new SQLiteConnection("Data Source=" + _databasePath + ";Version=3;"))
{
connection.Open();
string[] tablesToVerify = { "Quotes", "Sources", "Words" };
foreach (string oneTable in tablesToVerify)
{
string query = "SELECT 1 FROM " + oneTable;
using (var command = new System.Data.SQLite.SQLiteCommand(query, connection))
{
// the real test here is Assert.DoesNotThrow()
command.ExecuteReader();
}
}
connection.Close();
}
}
