/// <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(); } }