public async Task Load(SQLiteConnection connection) { using (var command = new SQLiteCommand("SELECT * FROM `Tracks`", connection)) { var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { PlayableBase track; using (var xmlReader = reader.GetTextReader(8)) track = (PlayableBase) _serializer.Deserialize(xmlReader); track.Title = reader.GetString(0); track.Artist = _artistProvider.ArtistDictionary[reader.ReadGuid(1)]; var albumGuid = reader.ReadGuid(2); if (albumGuid != Guid.Empty) track.Album = _albumsProvider.Collection[albumGuid]; track.Guid = reader.ReadGuid(3); track.LastTimePlayed = reader.GetDateTime(4); track.MusicBrainzId = reader.GetValue(5)?.ToString(); track.Duration = XmlConvert.ToTimeSpan(reader.GetString(6)); var coverId = reader.ReadGuid(7); if (coverId != Guid.Empty) track.Cover = _imageProvider.Collection[coverId]; Collection.Add(track.Guid, track); Tracks.Add(track); } } _connection = connection; }
public async Task<List<InventurItem>> GetDataAsync() { //using (var db = new InventurContext()) //{ // return await (from i in db.InventurItems // where i.Exported == false // orderby i.ChangedAt descending // select i).ToListAsync(); //} using (var command = new SQLiteCommand($"select * from {TABNAME} where Exported=0 orderby ChangedAt DESC", _dbTool.ConnectDb())) { using (var reader = await command.ExecuteReaderAsync()) { var items = new List<InventurItem>(); if (await reader.ReadAsync()) { while (await reader.ReadAsync()) { var i = new InventurItem { ID = Convert.ToInt32(reader["ID"]), CreatedAt = Convert.ToDateTime(reader["CreatedAt"]), ChangedAt = Convert.ToDateTime(reader["ChangedAt"]), EANCode = reader["EANCode"].ToString(), Amount = Convert.ToInt32(reader["Amount"]), Exported = Convert.ToBoolean(reader["Exported"]) }; items.Add(i); } } return items; } } }
/// <summary> /// Removes the old entries (entries that have been in the database more than the specified time) from the database. /// </summary> /// <param name="olderThan"> The number of days in the database after which the entry is considered old... </param> public static void CleanNewsOlderThan(int olderThan) { try { using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString)) { sqLiteConnection.Open(); DateTime olderDate = DateTime.Now; TimeSpan daySpan = new TimeSpan(olderThan, 0, 0, 0); olderDate = olderDate.Subtract(daySpan); SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection) { CommandText = "DELETE " + "FROM NEWS_STORAGE " + "WHERE NEWSITEM_AQUISITION_DATE <= ?" }; sqLiteCommand.Parameters.AddWithValue(null, olderDate); sqLiteCommand.ExecuteNonQuery(); sqLiteConnection.Close(); } } catch (Exception ex) { ErrorMessageBox.Show(ex.Message, ex.ToString()); Logger.ErrorLogger("error.txt", ex.ToString()); } }
public static void addAccount(string name, string passwd, string url, string provider, string blogname) { string conn_str = "Data Source=" + path + pwd_str; SQLiteConnection conn = new SQLiteConnection(conn_str); conn.Open(); SQLiteCommand cmd = new SQLiteCommand(); String sql = "Insert INTO BlogAccount(BlogUrl,BlogName,Provider,AccountName,Password) Values(@url,@blogname,@provider,@name,@password)"; cmd.CommandText = sql; cmd.Connection = conn; cmd.Parameters.Add(new SQLiteParameter("url", url)); cmd.Parameters.Add(new SQLiteParameter("blogname", blogname)); cmd.Parameters.Add(new SQLiteParameter("provider", provider)); cmd.Parameters.Add(new SQLiteParameter("name", name)); cmd.Parameters.Add(new SQLiteParameter("password", passwd)); cmd.ExecuteNonQuery(); conn.Close(); }
private void button3_Click(object sender, EventArgs e) { ///enters selected row into a string variable string country = dataGridView2.CurrentCell.Value.ToString(); ///gets team table from database string ViewTable = "SELECT * FROM 'Athlete Information' WHERE \"TeamName\" = '" + country + "';"; using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); ///opens DB if (con.State == ConnectionState.Open) /// if connection.Open was successful { ///this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } }
public string AddComment(NewsFeedModel comment) { string status = ""; string query; DataTable dt1 = new DataTable(); SQLiteCommand cmd; try { dbConn.Open(); query = "insert into news_feed values (" + comment.UserId + ", '" + comment.FirstName + "', '" + comment.LastName + "', '" + comment.Comment + "', "+ comment.Month + ", " + comment.Day + ", " + comment.Year + ", " + comment.Hour + ", " + comment.Minute + ")"; cmd = new SQLiteCommand(query, dbConn); cmd.ExecuteNonQuery(); status = "Comment Added"; dbConn.Close(); } catch (SQLiteException ex) { Console.Write(ex.ToString()); status = ex.ToString(); dbConn.Close(); } return status; }
private static int InsertNewBook( string filePath, string title, string author, DateTime publicationDate, string isbn) { SQLiteConnection connection = GetConnection(filePath); connection.Open(); using (connection) { SQLiteCommand insertBookCommand = new SQLiteCommand( @"INSERT INTO Books (BookTitle, BookAuthor, PublicationDate, ISBN) VALUES (@bookTitle, @bookAuthor, @publicationDate, @isbn)", connection); insertBookCommand.Parameters.AddWithValue("@bookTitle", title); insertBookCommand.Parameters.AddWithValue("@bookAuthor", author); insertBookCommand.Parameters.AddWithValue("@publicationDate", publicationDate); insertBookCommand.Parameters.AddWithValue("@isbn", isbn); int rowsAffected = insertBookCommand.ExecuteNonQuery(); return rowsAffected; } }
private void DeleteCardBase() { if (dataGridView1.SelectedRows.Count > 0) { id = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString()); if (dataGridView1.RowCount != 0) index = dataGridView1.SelectedRows[0].Index; DialogResult result = MessageBox.Show("Czy na pewno chcesz usunąć bazę karty numer " + id + "? \n\nOperacji nie można cofnąć.", "Ważne", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (result == DialogResult.Yes) using (SQLiteConnection conn = new SQLiteConnection(connString)) { conn.Open(); SQLiteCommand command = new SQLiteCommand(conn); command.CommandText = "DELETE FROM [CardBase] WHERE id = @id"; command.Parameters.Add(new SQLiteParameter("@id", id)); command.ExecuteNonQuery(); conn.Close(); Odswierz(); if (dataGridView1.RowCount != 0) { if (index == dataGridView1.RowCount) dataGridView1.CurrentCell = dataGridView1.Rows[index - 1].Cells[0]; else dataGridView1.CurrentCell = dataGridView1.Rows[index].Cells[0]; } } } }
protected void Page_Load(object sender, EventArgs e) { LabelError.Text = ""; try { string sql = "SELECT email FROM users WHERE RoleId = 'Admin'"; SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = new SQLiteConnection(Code.DAL.ConnectionString); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = sql; DataTable dt = Code.DAL.ExecuteCmdTable(cmd); if (dt.Rows.Count > 0) { PanelAlreadySetup.Visible = true; PanelNotSetup.Visible = false; } else { PanelAlreadySetup.Visible = false; PanelNotSetup.Visible = true; } } catch (Exception ex) { LabelError.Text = ex.Message; } }
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); } }
/** Void function adds female to database for the event @param string for score entry @param string for particular event score derived from @param string for first name of athlete @param string for last name of athlete */ public void EnterScore(double entry, string selectedEvent, string Fname, string Lname) { //!< Creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //!< Opens DB //!< Checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //!< Adds entry from event to the specific event string sql = String.Format("UPDATE \"" + selectedEvent + "\" SET Score = " + entry + " WHERE \"Athlete First Name\" = '" + Fname + "' AND \"Athlete Last Name\" = '" + Lname + "';"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } } MessageBox.Show("Score has been entered into " + Fname + " " + Lname + "'s score"); }
public void deleteAthlete(string Fname, string Lname) { //!< Creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //!< Opens DB //!< Checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //!< Adds entry to athlete information string sql = String.Format("DELETE FROM 'Athlete Information' WHERE AthleteFirstName = '" + Fname + "' AND AthleteLastName = '" + Lname + "';"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Entry has been deleted from database!"); con.Close(); } }
/** Void function adds male to database for the event @param string for athlete's team name @param string for first name of athlete @param string for last name of athlete */ public void registerAthlete(string Fname, string Lname, string Gender, string team) { //!< Creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //!< Opens DB //!< Checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //!< Adds entry to athlete information string sql = String.Format("INSERT INTO 'Athlete Information' ('TeamName' , 'AthleteFirstName', 'AthleteLastName', 'Gender') " + "VALUES ('" + team + "', '" + Fname + "', '" + Lname + "', '" + Gender + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Entry has been entered into Database!"); con.Close(); } }
public void DeleteTeam(string TeamName) { //!< Impeded sql statement to delete value from database string DeleteDB = "DELETE FROM 'team information' WHERE TeamName = '" + TeamName + "';"; using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //!< Opens DB if (con.State == ConnectionState.Open) //!< If connection, open was successful { //!< This sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(DeleteDB, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } con.Close(); MessageBox.Show(TeamName + " has been removed from database!"); } }
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 void TeamForm_Load(object sender, EventArgs e) { string ViewTable = "SELECT * FROM 'Team Information'";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } }
private void button1_Click(object sender, EventArgs e) { //object decleration Team AddTeam = new Team(); AddTeam.countryName = textBox1.Text;//gets team from text box //calls member function that adds team name to DB AddTeam.AddTeam(AddTeam.countryName); //this updates the table with added entry string ViewTable = "SELECT * FROM 'Team Information'";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } }
/// <summary> /// Сохраняет изменения в таблице /// </summary> /// <param name="question">вопрос</param> public void SaveOrUpdate(SQLiteConnection conn, QuestionResult question) { if (question.ID == 0) { int ID = indexService.NextID(TABLE_NAME); using (SQLiteCommand insertSQL = new SQLiteCommand("insert into QUESTION_RESULT (ID, QuestionTitle, TEST_RESULT_ID) values (@ID, @QuestionTitle, @TEST_RESULT_ID)", conn)) { insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle); insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID); insertSQL.Parameters.AddWithValue("@ID", ID); insertSQL.ExecuteNonQuery(); question.ID = ID; } } else { using (SQLiteCommand insertSQL = new SQLiteCommand("UPDATE QUESTION_RESULT SET QuestionTitle = @QuestionTitle, TEST_RESULT_ID = @TEST_RESULT_ID WHERE ID = @ID", conn)) { insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle); insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID); insertSQL.Parameters.AddWithValue("@ID", question.ID); insertSQL.ExecuteNonQuery(); } } }
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture) { using (SQLiteConnection conn = new SQLiteConnection("StaticConfig.DataSource")) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); int countCurrent = 0; string sqlCommandCurrent = String.Format("select count(*) from QuestionInfo where Q_Num={0};", value);//判断当前题号是否存在 try { Int32.Parse((string)value); countCurrent = sh.ExecuteScalar<int>(sqlCommandCurrent); conn.Close(); if (countCurrent > 0) { return "更新"; } else { return "保存"; } } catch (Exception) { return "保存"; } } } }
/** Function to add a team to be officially registered @param string for the Team's name (which is country name) */ public void AddTeam(string TeamName) { //!< Impeded sql statement to insert value into database string InsertDB = "INSERT INTO 'Team Information' ('TeamName') VALUES('" + TeamName + "');"; using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //!< Opens DB if (con.State == ConnectionState.Open) //!< If connection, open was successful { //!< This sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(InsertDB, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } con.Close(); MessageBox.Show(TeamName + " has been added to database!"); } }
public SQLiteCommand CreateAddProductCommand(SQLiteConnection conn, SQLiteTransaction transaction) { var cmd = new SQLiteCommand(_sql, conn, transaction); CreateParameters(cmd); return cmd; }
//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); }
public static void crear() { SQLiteConnection.CreateFile("db/interna.wpdb"); ObjConnection.Open(); string sql1 = "create table cliente " + "(id INTEGER PRIMARY KEY AUTOINCREMENT," + "nombre VARCHAR(20)," + "direccion VARCHAR(20)," + "telefono VARCHAR(20)" + ")"; SQLiteCommand command1 = new SQLiteCommand(sql1, ObjConnection); command1.ExecuteNonQuery(); string sql = "create table equipo " + "(id INTEGER PRIMARY KEY AUTOINCREMENT," + "marca VARCHAR(20)," + "modelo VARCHAR(20)," + "color VARCHAR(20)," + "foto VARCHAR(20)," + "fechaIng VARCHAR(20)," + "fechaEnt VARCHAR(20)," + "pagado INTEGER DEFAULT 0," + "arreglado INTEGER DEFAULT 0," + "entregado INTEGER DEFAULT 0," + "precio FLOAT," + "obser VARCHAR(40),"+ "id_cliente INTEGER," + "FOREIGN KEY(id_cliente) REFERENCES cliente(id)" + ")"; SQLiteCommand command = new SQLiteCommand(sql, ObjConnection); command.ExecuteNonQuery(); ObjConnection.Close(); }
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); }
private void button2_Click(object sender, EventArgs e) { //stores name of athlete seleceted from DB string DeleteFName = dataGridView1.SelectedCells[1].Value.ToString(); string DeleteLName = dataGridView1.SelectedCells[2].Value.ToString(); //object created to delete entry Registration DelAthlete = new Registration(); //member function deletes athlete from registration DelAthlete.deleteAthlete(DeleteFName, DeleteLName); //this will call a refreshed table string ViewTable = "SELECT * FROM 'Athlete Information'";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } }
public void CreateDatabase() { using (SQLiteConnection conn = new SQLiteConnection(DBConfig.DataSource)) { SQLiteConnection.CreateFile(DBConfig.DatabaseFile); //System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); //connstr.DataSource = DBConfig.DataSource; ////connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护 //conn.ConnectionString = connstr.ToString(); conn.Open(); //创建表 System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.Connection = conn; System.Data.SQLite.SQLiteHelper help = new SQLiteHelper(cmd); SQLiteTable table = new SQLiteTable("X"); table.Columns.Add(new SQLiteColumn("ID", ColType.Integer, true, true, true, null)); table.Columns.Add(new SQLiteColumn("Title", ColType.Text)); table.Columns.Add(new SQLiteColumn("ReferURL", ColType.Text)); table.Columns.Add(new SQLiteColumn("CoverURL", ColType.Text)); table.Columns.Add(new SQLiteColumn("CoverURL2", ColType.Text)); table.Columns.Add(new SQLiteColumn("CoverFile", ColType.Text)); table.Columns.Add(new SQLiteColumn("CoverFile2", ColType.Text)); table.Columns.Add(new SQLiteColumn("IsMovie", ColType.Boolean)); table.Columns.Add(new SQLiteColumn("LocalPath", ColType.Text)); table.Columns.Add(new SQLiteColumn("DatePublish", ColType.Text)); table.Columns.Add(new SQLiteColumn("Tag", ColType.Text)); help.CreateTable(table); conn.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(); } }
private void button5_Click(object sender, EventArgs e) { if (textBox13.Text != "" && textBox14.Text != "" && textBox15.Text != "" && textBox16.Text != "") { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBPInc.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "INSERT INTO Proveedor Values ('" + textBox13.Text + "', '" + textBox14.Text + "', '" + textBox15.Text + "', '" + textBox16.Text + "')"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); textBox13.Text = ""; textBox14.Text = ""; textBox15.Text = ""; textBox16.Text = ""; } else { MessageBox.Show("Faltaron campos por llenar"); } }
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 void CreateLocalDatabase() { try { System.Data.SQLite.SQLiteConnection.CreateFile(@"Databases/EmployeesDB.db"); Console.WriteLine("Succesfully Created Local Database"); Console.WriteLine("Creating tables..."); string EmployeesTableQuery = @"CREATE TABLE [Employees] ( [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, [FullName] TEXT NOT NULL, [BirthDay] TEXT NOT NULL, [Qualification] TEXT, [FirstDay] TEXT NOT NULL )"; using (connection = new System.Data.SQLite.SQLiteConnection(@"data source=Databases/EmployeesDB.db")) { System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(connection); connection.Open(); command.CommandText = EmployeesTableQuery; command.ExecuteNonQuery(); connection.Close(); } } catch (Exception exception) { Console.WriteLine(exception); } }
public static DataSet ExecuteDataSet(string SqlRequest, SQLiteConnection Connection) { DataSet dataSet = new DataSet(); dataSet.Reset(); SQLiteCommand cmd = new SQLiteCommand(SqlRequest, Connection); try { Connection.Open(); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd); dataAdapter.Fill(dataSet); } catch (SQLiteException ex) { Log.Write(ex); //Debug.WriteLine(ex.Message); throw; // пересылаем исключение на более высокий уровень } finally { Connection.Dispose(); } return dataSet; }
private void button2_Click(object sender, EventArgs e) { try { folio = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString()); nombre = dataGridView1.SelectedRows[0].Cells[1].Value.ToString(); sexo = dataGridView1.SelectedRows[0].Cells[2].Value.ToString(); edad = Int32.Parse(dataGridView1.SelectedRows[0].Cells[3].Value.ToString()); estadocivil = dataGridView1.SelectedRows[0].Cells[5].Value.ToString(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "DELETE FROM Expediente WHERE [Folio] = " + folio + " AND [Nombre] = '" + nombre + "' AND [Sexo] = '" + sexo + "' AND [Edad] = " + edad + " AND [Estadocivil] = '" + estadocivil + "'"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Expediente eliminado exitosamente"); } catch { MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/// <summary> /// Constructor creates a database if it doesn't exist and creates the database's tables /// </summary> public DataBaseRepository() { // "using " keywords ensures the object is properly disposed. using (var conn = new SQLiteConnection(Connectionstring)) { try { if (!File.Exists(Startup.dbSource)) { SQLiteConnection.CreateFile(Startup.dbSource); } conn.Open(); //Create a Table string query = $"create table IF NOT EXISTS {TableName} (Id INTEGER PRIMARY KEY, Date VARCHAR NOT NULL DEFAULT CURRENT_DATE, Title nvarchar(255) not null, Content nvarchar(1000) Not NULL) "; SQLiteCommand command = new SQLiteCommand(query, conn); command.ExecuteNonQuery(); } //TODO: Handle try catch better cath a more specific error type. catch (SQLiteException ex ) { Console.WriteLine(ex.ToString()); } conn.Close(); } }
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; }
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(); } }
internal static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, SQLiteTransaction transaction, CommandType commandType, string commandText, SQLiteParameter[] commandParameters, out bool mustCloseConnection) { if (command == null) throw new ArgumentNullException("command"); if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText"); if (connection.State == ConnectionState.Open) mustCloseConnection = false; else { mustCloseConnection = true; connection.Open(); } command.Connection = connection; command.CommandText = commandText; if (transaction != null) { if (transaction.Connection == null) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); command.Transaction = transaction; } command.CommandType = commandType; if (commandParameters != null) AttachParameters(command, commandParameters); return; }
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); }
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 ResultItem GetResultItem(int riderNo) { SQLiteCommand query = new SQLiteCommand(); query.CommandText = "SELECT rider_no, rider_first, rider_last, rider_dob, phone, email, member FROM [" + Year + "_rider] WHERE rider_no = @noparam;"; query.CommandType = System.Data.CommandType.Text; query.Parameters.Add(new SQLiteParameter("@noparam", riderNo)); query.Connection = ClubConn; SQLiteDataReader reader = DoTheReader(query); ResultItem item = new ResultItem(); while (reader.Read()) { item.BackNo = reader.GetInt32(0); item.RiderNo = reader.GetInt32(1); item.Rider = reader.GetString(2) + " " + reader.GetString(3); item.HorseNo = reader.GetInt32(4); item.Horse = reader.GetString(5); item.ShowNo = reader.GetInt32(6); item.ShowDate = reader.GetString(7); item.ClassNo = reader.GetInt32(8); item.ClassName = reader.GetString(9); item.Place = reader.GetInt32(10); item.Time = reader.GetDecimal(11); item.Points = reader.GetInt32(12); item.PayIn = reader.GetDecimal(13); item.PayOut = reader.GetDecimal(14); } reader.Close(); ClubConn.Close(); return item; }
public Database() { string _dbPath = "MyDatabase.db3"; // Instanciation de notre connexion SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source=database.sqlite;Version=3;"); // Utilisation de l'API en mode synchrone this.conn = sqlite_conn; sqlite_conn.Open(); SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand(); // Let the SQLiteCommand object know our SQL-Query: sqlite_cmd.CommandText = "CREATE TABLE IF NOT EXISTS pass (id INT PRIMARY KEY, login varchar(100), privk varchar(100), pubk varchar(100)) ;"; // Now lets execute the SQL ;-) sqlite_cmd.ExecuteNonQuery(); /* // Utilisation de l'API asynchrone * SQLiteAsyncConnection connAsync = new SQLiteAsyncConnection("myDb.db3"); * connection.CreateTableAsync<Personne>();*/ }
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 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(""); }
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; }
//static private int _OrderTotal = 0; static public int GetRowCounts(FormMain.dbType type) { int?count = 0; SQLiteConnection con = GetConnection(); { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand("SET ARITHABORT ON", con)) { try { if (type == FormMain.dbType.Order) { com.CommandText = Order.GetRowCountCommandText; } else { com.CommandText = Quote.GetRowCountCommandText; } object o = com.ExecuteScalar(); count = Convert.ToInt32(o); } catch// (Exception ex) { } } } //if (type == FormMain.dbType.Order && count.HasValue) //{ // _OrderTotal = count.Value; //} return((count == null) ? 0 : count.Value); }
//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; }
static public void DeletePO(FormMain.dbType type, string PO) { SQLiteConnection con = GetConnection(); { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand("SET ARITHABORT ON", con)) { try { if (type == FormMain.dbType.Order) { com.CommandText = Order.DeletePOCommandText(PO); } else { com.CommandText = Quote.DeletePOCommandText(PO); } com.ExecuteNonQuery(); //if (type == FormMain.dbType.Order) // Sql._OrderTotal--; } catch (SqlException ex) { MessageBox.Show("Database error: " + ex.Message); } } } }
/// <summary> /// Takes a GIS model and a file and writes the model to that file. /// </summary> /// <param name="model"> /// The GisModel which is to be persisted. /// </param> /// <param name="fileName"> /// The name of the file in which the model is to be persisted. /// </param> public void Persist(GisModel model, string fileName) { Initialize(model); PatternedPredicate[] predicates = GetPredicates(); if ( File.Exists(fileName)) { File.Delete(fileName); } using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3")) { mDataConnection.Open(); mDataCommand = mDataConnection.CreateCommand(); CreateDataStructures(); using (mDataTransaction = mDataConnection.BeginTransaction()) { mDataCommand.Transaction = mDataTransaction; CreateModel(model.CorrectionConstant, model.CorrectionParameter); InsertOutcomes(model.GetOutcomeNames()); InsertPredicates(predicates); InsertPredicateParameters(model.GetOutcomePatterns(), predicates); mDataTransaction.Commit(); } mDataConnection.Close(); } }
public void CreateDatabase() { string createQuery = @"CREATE TABLE IF NOT EXISTS [Mytable]( [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Name] NVARCHAR(50) NULL, [Location] NVARCHAR(30) NULL)"; System.Data.SQLite.SQLiteConnection.CreateFile("sample.db3"); using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=sample.db3")) { using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); cmd.CommandText = createQuery; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Mytable(Name,Location) values('Fisch','Irgendwo')"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * from Mytable"; using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader["Name"] + ":" + reader["Location"]); } } conn.Close(); Console.ReadLine(); } } }
public static void ChapterFinished(string mangaTitle) { try { using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString)) { sqLiteConnection.Open(); SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection) { CommandText = "UPDATE READING_LIST " + "SET READ_CURRENT_CHAPTER = READ_CURRENT_CHAPTER + 1, READ_LAST_TIME = ? " + "WHERE MANGA_ID = ?" }; sqLiteCommand.Parameters.AddWithValue(null, DateTime.Now); sqLiteCommand.Parameters.AddWithValue(null, GetMangaId(mangaTitle)); sqLiteCommand.ExecuteNonQuery(); sqLiteConnection.Close(); } } catch (Exception ex) { ErrorMessageBox.Show(ex.Message, ex.ToString()); Logger.ErrorLogger("error.txt", ex.ToString()); } }
public static void CreateTable <T>(SqliteCommand cmd, string TableName) where T : SQLiteData { var props = typeof(T).GetProperties().Where(x => x.Name != "Id" && x.CanRead && x.CanWrite && !Attribute.IsDefined(x, typeof(SqlIgnoreAttribute))).ToList(); if (props.Count > 0) { var sb = new StringBuilder(); var sb2 = new StringBuilder(); sb.AppendLine("CREATE TABLE IF NOT EXISTS `" + TableName + "`"); sb.AppendLine("("); sb.AppendLine(" `Id` INTEGER PRIMARY KEY AUTOINCREMENT,"); foreach (var p in props) { sb2.AppendLine(" `" + p.Name + "` " + GetTypeText(p) + ","); } var vs = sb2.ToString(); vs = vs.Substring(0, vs.Length - 3); sb.AppendLine(vs); sb.AppendLine(");"); cmd.CommandText = sb.ToString(); cmd.ExecuteNonQuery(); } }
/// <summary> /// 创建表 /// </summary> /// <param name="connection"></param> /// <param name="cmdText"></param> private static void CreateTable(SQLiteConnection connection, string cmdText) { System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = cmdText; cmd.Connection = connection; cmd.ExecuteNonQuery(); }
public static void Delete(SqliteCommand cmd, string TableName, string Where) { string sql = "DELETE FROM `" + TableName + "`\r\n" + Where; cmd.CommandText = sql; cmd.ExecuteNonQuery(); }
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 DataSet CheckTableExists(SQLiteConnection m_dbConnection, string tableName, Logger logger) //{ // DataSet myDataSet = new DataSet(); // try // { // string query = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{tableName}';"; // //SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;"); // //m_dbConnection.Open(); // SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(query, m_dbConnection); // //myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // myAdapter.Fill(myDataSet, "Records"); // //m_dbConnection.Close(); // } // catch (Exception excp) // { // logger.Error("Error while retrieving from sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace); // } // return myDataSet; //} public bool InsertRecord(System.Data.SQLite.SQLiteConnection m_dbConnection, string query, Logger logger) { bool isRecordInserted = false; try { //SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;"); //m_dbConnection.Open(); //string isErrorString = IsError ? "1" : "0"; //string query = $"INSERT INTO {tableName}(FlatFileRowNumber, IsError) VALUES({flatFileRecordNumber},{isErrorString})"; System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(m_dbConnection); command.CommandText = query; command.ExecuteNonQuery(); //m_dbConnection.Close(); isRecordInserted = true; } catch (Exception excp) { logger.Error("Error while inserting sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace); } return(isRecordInserted); }
/// <summary> /// Connect to the SQLite database. /// </summary> /// <param name="close">If true : Close database connection. If false : Open database connection.</param> /// <returns>SQLiteCommand cmd if database is getting opened.</returns> /// <returns>Null if database is getting closed.</returns> internal static SQLiteCommand ConnectToDatabase(bool close) { bool createTable = false; if (!File.Exists("GLdb.db3")) { CreateDatabase(); createTable = true; } System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=GLdb.db3"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn); if (!close) { conn.Open(); if (createTable) { CreateTable(cmd); InsertPlatform(cmd); } return(cmd); } else { cmd = null; conn.Close(); return(cmd); } }
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); }
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 bool insertRecord(string sql) { connectToDb("Invoice.db"); SQLiteCommand command = new SQLiteCommand(sql, dbConnection); try { command.ExecuteNonQuery(); } catch(SQLiteException e ) { if (e.ErrorCode == 1) { MessageBox.Show("Unable to find database. Program will exit."); Environment.Exit(0); } else { MessageBox.Show("Value already in database"); //exception 19 if more need to be added } return false; } dbConnection.Close(); return true; }
public DataManager(string dbFilePath) { if (string.IsNullOrEmpty(dbFilePath) == true) { throw new ArgumentNullException("dbFilePath"); } _fileExists = File.Exists(CommonConst.DBFileName); _dbFilePath = dbFilePath; // DBとの接続を開始する。 _conn = new SQLiteConnection("Data Source=" + CommonConst.DBFileName); // DBに接続する。 _conn.Open(); _command = _conn.CreateCommand(); // DBファイルが新規作成された場合 if (_fileExists == false) { // 空のテーブルを作成する。 this.CreateNewTables(); _fileExists = true; } // アプリケーション設定 _applicationSettings.amountSplitCharacter = this.GetAmountsSplitCharacter(); _applicationSettings.commentSplitCharacter = this.GetCommentSplitCharacter(); }
// DELETE SESSION in db public void deleteSession(SessionLocation _sess) { String strSql = "DELETE FROM session WHERE id_klant = @param1"; this.setTheConnection(); try { this.conn.Open(); this.comm = this.conn.CreateCommand(); this.comm.CommandText = strSql; this.comm.CommandType = CommandType.Text; this.comm.Parameters.Add(new SQLiteParameter("@param1", _sess.getKlantId())); this.comm.ExecuteNonQuery(); } catch (SQLiteException ee) { Console.Write(ee.ToString()); } finally { this.conn.Close(); } }