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; }
/// <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 insertdb(Message m) { string _connstr = getdb(); string ins_ = "insert into mail (from_,to_,subject_,body_,dot_,reci_dt)values(?,?,?,?,?,?);"; using (SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(_connstr)) { conn.Open(); try { using (SQLiteCommand cmd = new SQLiteCommand(ins_, conn)) { cmd.Parameters.AddWithValue("@from_", m.from); cmd.Parameters.AddWithValue("@to_", m.to); cmd.Parameters.AddWithValue("@subject_", m.subject); cmd.Parameters.AddWithValue("@body_", m.body); cmd.Parameters.AddWithValue("@dot_", m.dot); cmd.Parameters.AddWithValue("@reci_dt", DateTime.Now.ToString("yyyyMMddHHmmss")); cmd.ExecuteNonQuery(); } } catch (Exception ep) { Console.WriteLine(ep.Message); } conn.Close(); } }
/// <summary> /// Constructs the transaction object, binding it to the supplied connection /// </summary> /// <param name="connection">The connection to open a transaction on</param> /// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param> internal SQLiteTransaction(SQLiteConnection connection, bool deferredLock) { _cnn = connection; _version = _cnn._version; _level = (deferredLock == true) ? IsolationLevel.ReadCommitted : IsolationLevel.Serializable; if (_cnn._transactionLevel++ == 0) { try { using (SQLiteCommand cmd = _cnn.CreateCommand()) { if (!deferredLock) cmd.CommandText = "BEGIN IMMEDIATE"; else cmd.CommandText = "BEGIN"; cmd.ExecuteNonQuery(); } } catch (SQLiteException) { _cnn._transactionLevel--; _cnn = null; throw; } } }
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(); }
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]; } } } }
public static DataTable GetAllContacts(string sqlitefile) { DataTable dt = new DataTable("Contact"); try { SQLiteConnection con = new SQLiteConnection("data source=" + sqlitefile); dt = ExecuteNonQueryDt("Select * From Contact Order By ContactID", con); return dt; } catch(Exception ex) { using (FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "\\" + System.DateTime.Now.Date.ToString("dd-MMM-yyyy") + "_Log.txt", FileMode.Append, FileAccess.Write)) { StreamWriter streamWriter = new StreamWriter(file); streamWriter.WriteLine(System.DateTime.Now + " - " + "GetAllContacts" + " - " + ex.Message.ToString()); streamWriter.Close(); } if (File.Exists(sqlitefile)) { File.Delete(sqlitefile); } return dt; } }
static void Main(string[] args) { // Create sqlite connection SQLiteConnection connection = new SQLiteConnection(string.Format(@"Data Source={0}\SimpleDatabase.s3db", Environment.CurrentDirectory)); // Open sqlite connection connection.Open(); // Get all rows from example_table SQLiteDataAdapter db = new SQLiteDataAdapter("SELECT * FROM Names", connection); // Create a dataset DataSet ds = new DataSet(); // Fill dataset db.Fill(ds); // Create a datatable DataTable dt = new DataTable("Names"); dt = ds.Tables[0]; // Close connection connection.Close(); // Print table foreach (DataRow row in dt.Rows) { Console.WriteLine(string.Format("{0} {1}", row["Firstname"], row["Surname"])); } Console.ReadLine(); }
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"; } } }
static public void SQLiteLoadCurrenciesFromDatabase() { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=MSBase.sqlite;Version=3;"); conn.Open(); //sql_command = "INSERT INTO MSCurrencies (MSCurrencyId, MSCurrencyDate, MSCurrencyType, MSCurrencyCode, MSCurrencyNominal, MSCurrencyName, MSCurrencyValue) " // + "VALUES ( 0, 1532044620, '', 'AZN', 1, '', 1);"; SQLiteCommand cmd = new SQLiteCommand("", conn); cmd.CommandText = "SELECT MSCurrencyId, MSCurrencyDate, MSCurrencyType, MSCurrencyCode, MSCurrencyNominal, MSCurrencyName, MSCurrencyValue" + " FROM MSCurrencies"; try { SQLiteDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Program.currency.MSCurrencyId = int.Parse(dr["MSCurrencyId"].ToString()); Program.currency.MSCurrencyDate = DateTime.Parse((string)dr["MSCurrencyDate"]); Program.currency.MSCurrencyType = (string)dr["MSCurrencyType"]; Program.currency.MSCurrencyCode = (string)dr["MSCurrencyCode"]; Program.currency.MSCurrencyNominal = int.Parse(dr["MSCurrencyNominal"].ToString()); Program.currency.MSCurrencyName = (string)dr["MSCurrencyName"]; Program.currency.MSCurrencyValue = float.Parse(dr["MSCurrencyValue"].ToString()); Program.currencies.Add(new MSCurrency(Program.currency)); } dr.Close(); } catch (SQLiteException ex) { Console.WriteLine(ex.Message); } conn.Close(); }
/** 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!"); } }
/** 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(); }
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; }
static public void SQLiteLoadCategoriesFromDatabase() { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=MSBase.sqlite;Version=3;"); conn.Open(); //sql_command = "INSERT INTO MSCategories (MSCategoryId, MSIO, MSName, MSAccountId, MSColor, MSImage) " // + "VALUES ( 0, '+', 'Зарплата', 1, 1, ';)');"; SQLiteCommand cmd = new SQLiteCommand("", conn); cmd.CommandText = "SELECT MSCategoryId, MSIO, MSName, MSAccountId, MSColor, MSImage FROM MSCategories"; try { SQLiteDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Program.category.MSCategoryId = int.Parse(dr["MSCategoryId"].ToString()); Program.category.MSIO = ((string)dr["MSIO"])[0]; Program.category.MSName = (string)dr["MSName"]; Program.category.MSAccountId = int.Parse(dr["MSAccountId"].ToString()); Program.category.MSColor = (ConsoleColor)(int.Parse(dr["MSColor"].ToString())); Program.category.MSImage = (string)dr["MSImage"]; Program.categories.Add(new MSCategory(Program.category)); } dr.Close(); } catch (SQLiteException ex) { Console.WriteLine(ex.Message); } }
public bool IfGetReaderSchema(string tableName, System.Data.SQLite.SQLiteConnection conn, string sql) { cmd = new SQLiteCommand(); cmd.CommandText = sql; cmd.Connection = conn; SQLiteDataReader reader = cmd.ExecuteReader(); if (!reader.HasRows) { if (!reader.IsClosed) { reader.Close(); reader.Dispose(); } return(false); } else { if (!reader.IsClosed) { reader.Close(); reader.Dispose(); } return(true); } }
public static void GetItemsFromDatabase() { using ( System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + inputDir + ".db3")) { con.Open(); using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { com.CommandText = "SELECT Spectrum.ID,peptide,charge,path FROM Spectrum, Files Where Spectrum.codex = Files.codex"; using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader()) { while (reader.Read()) { //store id and peptide into an object holding those values //holderForPairs.Add(reader["id"].ToString() + "\t" + reader["peptide"].ToString()); inputs.Add(new InputListItems(reader["ID"].ToString(), reader["peptide"].ToString(), reader["charge"].ToString(), reader["path"].ToString())); } } } con.Close(); // Close the connection to the database } new MakeCombinations(inputs); }
//public 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; }
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(); } }
private void Consulta_Load(object sender, EventArgs e) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;"; DataSet DS = new DataSet(); SQLiteConnection con = new SQLiteConnection(connString); con.Open(); SQLiteDataAdapter DA = new SQLiteDataAdapter("select * from Expediente", con); DA.Fill(DS, "Expediente"); dataGridView1.DataSource = DS.Tables["Expediente"]; con.Close(); dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells; int i = 0; foreach (DataGridViewColumn c in dataGridView1.Columns) { i += c.Width; } if ((i + dataGridView1.RowHeadersWidth + 2) > 616) { dataGridView1.Width = 616; } else { dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2; } }
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 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); } }
protected void ExecuteWrite(string query, Dictionary <string, object> args) { try { using (connection = new System.Data.SQLite.SQLiteConnection(@"data source=Databases/EmployeesDB.db")) { connection.Open(); using (var cmd = new SQLiteCommand(query, connection)) { foreach (var pair in args) { cmd.Parameters.AddWithValue(pair.Key, pair.Value); } Console.WriteLine("Changes were made in " + cmd.ExecuteNonQuery().ToString() + " places."); } connection.Close(); } } catch (Exception e) { Console.WriteLine(e); } }
/// <summary>Initialization ctor</summary> /// <param name="file">Name of database file, will be created if not existing</param> public WebCache(string file = "Cache.dat") { _File = file; if (!System.IO.File.Exists(_File)) { SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File); try { connection.Open(); string[] inits = new string[]{ "CREATE TABLE [InetCache] ([Id] INTEGER PRIMARY KEY, [Timestamp] INTEGER, [Keyword] TEXT NOT NULL, [Content] TEXT NOT NULL)", "CREATE INDEX [IDX_InetCache] ON [InetCache] ([Keyword])" }; SQLiteCommand command = connection.CreateCommand(); foreach (string cmd in inits) { command.CommandText = cmd; command.ExecuteNonQuery(); } } finally { connection.Close(); } } }
private void Reporte_Load_1(object sender, EventArgs e) { Sistema_Caritas.CrystalReport1 objRpt = new Sistema_Caritas.CrystalReport1(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); String ConnStr = @"Data Source=" + appPath + @"\DBpinc.s3db ;Version=3;"; System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr); //String Query1 = "SELECT * FROM Ventashechas Where NVenta = '" + nventas + "'"; String Query1 = "Select NVenta as NVenta, ArticuloID as ArticuloID, Nombrearticulo, Cantidad, Fecha as Fecha, Total as Total, Ventatotal as Ventatotal FROM (SELECT * FROM Ventashechas, Ventas Where Ventashechas.NVenta = Ventas.NVenta) Where NVenta = '" + nventas + "'"; System.Data.SQLite.SQLiteDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter(Query1, ConnStr); DataSet Ds = new DataSet(); // here my_dt is the name of the DataTable which we // created in the designer view. adapter.Fill(Ds, "DataTable1"); // Setting data source of our report object objRpt.SetDataSource(Ds); // Binding the crystalReportViewer with our report object. this.crystalReportViewer2.ReportSource = objRpt; }
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; }
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); } }
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 "保存"; } } } }
private void bt_CreateDataSource_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_DataSourceFilePath.Text)) { MessageBox.Show("请选择路径."); return; } if (string.IsNullOrEmpty(txt_DataSourceFileName.Text)) { MessageBox.Show("请输入文件名."); return; } instance.SetDataSourcePath(Path.Combine(txt_DataSourceFilePath.Text, txt_DataSourceFileName.Text)); var conn = new System.Data.SQLite.SQLiteConnection(Path.Combine(txt_DataSourceFilePath.Text, txt_DataSourceFileName.Text)); conn.SetPassword("123456"); var parameters = new List <string[]>(); parameters.Add(new[] { "fileID", "varchar", "(50)" }); parameters.Add(new[] { "fileMD5", "varchar", "(32)" }); parameters.Add(new[] { "fileName", "varchar", "(200)" }); parameters.Add(new[] { "filePath", "varchar", "(1000)" }); parameters.Add(new[] { "fileInfo", "blob", "(20480)" }); parameters.Add(new[] { "parentID", "varchar", "(50)" }); instance.CreateTable("FileInfo", parameters.ToArray()); Add(new Model(Guid.NewGuid(), "根目录")); InitDataSource(); InitUI(false); }
private bool CreateDatabase() { SQLiteConnection tempConnection = null; try { SQLiteConnection.CreateFile(ConfigurationManager.AppSettings["dbPath"]); tempConnection = new SQLiteConnection("Data Source=" + ConfigurationManager.AppSettings["dbPath"] + ";Version=3;"); tempConnection.Open(); SQLiteCommand command = tempConnection.CreateCommand(); command.CommandText = "CREATE TABLE pelaaja (id INTEGER PRIMARY KEY AUTOINCREMENT, etunimi TEXT NOT NULL, sukunimi TEXT NOT NULL, seura TEXT NOT NULL, hinta FLOAT NOT NULL, kuva_url TEXT NULL)"; command.ExecuteNonQuery(); tempConnection.Close(); } catch(Exception e) { errors.Add("Tietokannan luominen epäonnistui"); return false; } finally { if(tempConnection != null) tempConnection.Close(); } return true; }
public AbstractDatabase(string db_path) { conn_ = new SQLiteConnection("Data Source=" + db_path); tables_ = new Dictionary <string, AbstractTable>(); transaction_ = null; DBFilePath = db_path; }
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 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 SQLiteCommand CreateAddProductCommand(SQLiteConnection conn, SQLiteTransaction transaction) { var cmd = new SQLiteCommand(_sql, conn, transaction); CreateParameters(cmd); return cmd; }
public void Test(SQLiteConnection conn) { SQLiteCommand sqlite_cmd = this.conn.CreateCommand(); sqlite_cmd.CommandText = "INSERT INTO pass (login, privk, pubk) VALUES ('Michel', 'TTTEEESSSTTT' , 'Testpub');"; sqlite_cmd.ExecuteNonQuery(); sqlite_cmd.CommandText = "SELECT * FROM pass"; 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 loginReader = sqlite_datareader.GetString(1); string keyReader = sqlite_datareader.GetString(2); System.Console.WriteLine(loginReader); // OutputTextBox.Text += idReader + " '" + textReader + "' " + "\n"; } }
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 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 void Clear(string name) { int procCount = Process.GetProcessesByName("firefox").Length; if (procCount > 0) throw new ApplicationException(string.Format("There are {0} instances of Firefox still running", procCount)); try { using (var conn = new SQLiteConnection("Data Source=" + GetFirefoxCookiesFileName())) { conn.Open(); SQLiteCommand command = conn.CreateCommand(); command.CommandText = "delete from moz_cookies where name='" + name + "'"; int count = command.ExecuteNonQuery(); } } catch (SQLiteException ex) { if ( !(ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Busy) || ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Locked))) throw new ApplicationException("The Firefox cookies.sqlite file is locked"); } }
public void Execute(Action <SqliteConnection, SqliteCommand, SqliteTransaction> ExcuteQuery) { Lock = true; using (var conn = new SqliteConnection() { ConnectionString = this.ConnectString }) { conn.Open(); using (var cmd = conn.CreateCommand()) { using (var trans = conn.BeginTransaction()) { try { ExcuteQuery(conn, cmd, trans); trans.Commit(); } catch (Exception ex) { trans.Rollback(); } } } conn.Close(); } Lock = false; }
public static DataTable ExecuteNonQueryDt(string cmdText, SQLiteConnection con) { DataTable dt = new DataTable("Table"); try { using (con) { SQLiteDataAdapter da = new SQLiteDataAdapter(cmdText, con); con.Open(); da.Fill(dt); con.Close(); } return dt; } catch (Exception ex) { using (FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "\\" + System.DateTime.Now.Date.ToString("dd-MMM-yyyy") + "_Log.txt", FileMode.Append, FileAccess.Write)) { StreamWriter streamWriter = new StreamWriter(file); streamWriter.WriteLine(System.DateTime.Now + " - " + "ExecuteNonQueryDt" + " - " + ex.Message.ToString()); streamWriter.Close(); } return dt; } }
public DataSet GetTotalRecordsInTable(System.Data.SQLite.SQLiteConnection m_dbConnection, string query, Logger logger) { DataSet myDataSet = new DataSet(); try { //string query = $"SELECT count(1) TotalRecords FROM {tableName};"; //records = m_dbConnection.Query<ProcessedDetails1>(query).Count; //System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(m_dbConnection); //command.CommandText = query; //SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;"); //m_dbConnection.Open(); System.Data.SQLite.SQLiteDataAdapter myAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, m_dbConnection); ////myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; myAdapter.Fill(myDataSet, "Records"); //if (myDataSet.Tables[0].Rows.Count > 0) //{ // records = Convert.ToInt32(myDataSet.Tables[0].Rows[0][0].ToString()); //} //m_dbConnection.Close(); } catch (Exception excp) { logger.Error("Error while retrieving from sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace); } return(myDataSet); }
public static DataTable GetClients(string sqlitefile) { SQLiteConnection con = new SQLiteConnection("data source=" + sqlitefile); DataTable dt = new DataTable("CompanyInfo"); dt = ExecuteNonQueryDt("SELECT * FROM CompanyInfo", con); return dt; }
//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> /// 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 System.Data.SQLite.SQLiteConnection OpenDBConnection1(string DBName) { System.Data.SQLite.SQLiteConnection m_dbConnection = new System.Data.SQLite.SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;"); m_dbConnection.Open(); return(m_dbConnection); }
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()); } }
/// <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); } }
private static SQLiteConnection GetConnection(string filePath) { SQLiteConnection connection = new SQLiteConnection( string.Format("Data Source={0};Version=3;", filePath)); return connection; }
private void ReporteSalidasBitacoraComedor_Load(object sender, EventArgs e) { CrystalReport6 objRpt = new CrystalReport6(); string appPath = Path.GetDirectoryName(Application.ExecutablePath); String ConnStr = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"; System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr); String Query1 = "SELECT * FROM Salidas"; System.Data.SQLite.SQLiteDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter(Query1, ConnStr); DataSet Ds = new DataSet(); // here my_dt is the name of the DataTable which we // created in the designer view. adapter.Fill(Ds, "DataTable5"); // Setting data source of our report object objRpt.SetDataSource(Ds); // Binding the crystalReportViewer with our report object. this.crystalReportViewer1.ReportSource = objRpt; objRpt.Refresh(); }
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(); } }
static void Main(string[] args) { using(var conn = new SQLiteConnection(@"Data Source=dnd35.db")) { //conn.Open(); //string query = @"SELECT * FROM skill;"; //var command = conn.CreateCommand(); //command.CommandText = query; //command.Connection = conn; //var reader = command.ExecuteReader(); //var domains = new List<Skill>(); //while (reader.Read()) //{ // var values = reader.GetValues(); // domains.Add(Map(values)); //} //var domainRepository = new DomainRepository(); //foreach(var domain in domains) //{ // domainRepository.Create(domain); //} } }
/// <summary>Write data with a given key to cache</summary> /// <param name="key">Key of data to be stored</param> /// <param name="data">Data to be stored</param> public void Store(string key, string data) { SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File); try { DateTime now = DateTime.Now; connection.Open(); SQLiteCommand command = connection.CreateCommand(); command.CommandText = "DELETE FROM [InetCache] WHERE [Keyword]=?"; command.Parameters.Add("kw", DbType.String).Value = key; command.ExecuteNonQuery(); // out with the old command = connection.CreateCommand(); command.CommandText = "INSERT INTO [InetCache] ([Timestamp], [Keyword], [Content]) VALUES (?, ?, ?)"; command.Parameters.Add("ts", DbType.Int32).Value = now.Year * 10000 + now.Month * 100 + now.Day; command.Parameters.Add("kw", DbType.String).Value = key; command.Parameters.Add("dt", DbType.String).Value = data; command.ExecuteNonQuery(); // in with the new } finally { connection.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(); } } }
// backgroundWorkerView // private async Task SetupAccount() { //BinanceDefaults.SetDefaultLogOutput(Console.Out); using (var client = new BinanceClient()) { var accountInfo = await client.GetAccountInfoAsync(10000000); IEnumerable <BinanceBalance> candlearray = accountInfo.Data.Balances.Select(e => e).Where(e => (e.Total + e.Locked) > 0); } if (!System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory + "\\Database.sqlite")) { SQLiteConnection.CreateFile("Database.sqlite"); } _conn = new SQLiteConnection("Data Source=Database.sqlite;Version=3;"); _conn.Open(); try { string sql = "CREATE TABLE IF NOT EXISTS TICKERS (Symbol VARCHAR(20), active INT)"; SQLiteCommand command = new SQLiteCommand(sql, _conn); await command.ExecuteNonQueryAsync(); sql = "CREATE TABLE IF NOT EXISTS ORDERS (Symbol VARCHAR(20),Scanned DATE, Price decimal, Volume decimal, TakerBuyBaseAssetVolume decimal, TakerBuyQuoteAssetVolume decimal, NumberOfTrades INT, Hr8Av INT, NowPercent decimal, Min15Percent decimal, GreenCandles decimal)"; command = new SQLiteCommand(sql, _conn); await command.ExecuteNonQueryAsync(); sql = "CREATE VIEW IF NOT EXISTS [V_Symbols] AS SELECT COUNT(Symbol), Symbol FROM ORDERS GROUP BY Symbol ORDER By COUNT(Symbol) DESC;"; await command.ExecuteNonQueryAsync(); } catch (Exception ex) { //_conn.Close(); } }
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 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(); }
public static void loaddb() { string _connstr = getdb(); string ins_ = "select rowid,from_,to_,subject_,body_,dot_ from mail where rowid>" + maxrowid + ";"; using (SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(_connstr)) { conn.Open(); try { using (SQLiteDataReader dr = new SQLiteCommand(ins_, conn).ExecuteReader()) { while (dr.Read()) { Message m = new Message(); maxrowid = dr.GetInt32(0); m.from = dr.GetString(1); m.to = dr.GetString(2); m.subject = dr.GetString(3); m.body = dr.GetString(4); m.dot = dr.GetBoolean(5); _mails.Add(m); } } } catch (Exception ep) { Console.WriteLine(ep.Message); } conn.Close(); } }