public static void UpdateTable(string query, SQLiteConnection dbConnection, DataTable table) { //object maxId = null; //if (query.ToLower().Contains("from traffic_object")) // maxId = GetScalar("Select max(obj_id) From traffic_object", dbConnection); using (SQLiteTransaction transaction = dbConnection.BeginTransaction()) { using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, dbConnection)) { using (SQLiteCommand command = dbConnection.CreateCommand()) { command.Transaction = transaction; command.CommandText = query; adapter.SelectCommand = command; using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder()) { builder.DataAdapter = adapter; adapter.Update(table); transaction.Commit(); } } } } }
/// <summary> /// Commence une transaction sql isolée /// ATTENTION LES OUVERTURES ET FERMETURES DE LA BASE NE SERONT PLUS AUTOMATIQUES /// </summary> public override bool BeginTransaction(string transactionName = null) { if (conn == null) { this.Open(); } if (this.transac != null) { return(false); // postgres authorise qu'une transaction } transac = conn.BeginTransaction(); return(true); }
protected override void SetupDependencies() { base.SetupDependencies(); _streamId = Guid.NewGuid(); _events = new[] {"event 1", "event 2", "event 3", "event 4", "event 5"}; using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); var trans = connection.BeginTransaction(); var cmd = new SQLiteCommand("insert into EventStore (StreamId, Sequence, EventData) values (@StreamId, @Sequence, @EventData)") {Connection = connection, Transaction = trans}; var sequenceGenerator = new SequenceGenerator(_streamId); cmd.Parameters.AddWithValue("@StreamId", _streamId); cmd.Parameters.Add("@Sequence", DbType.Int32); cmd.Parameters.Add("@EventData", DbType.Binary); foreach (var @event in _events) { var stream = new MemoryStream(); _formatter.Serialize(stream, @event); cmd.Parameters["@Sequence"].Value = sequenceGenerator.NextSequence(); cmd.Parameters["@EventData"].Value = stream.ToArray(); cmd.ExecuteNonQuery(); } trans.Commit(); } }
public List<MediaDeviceModelItem> GetAllMediaDevices() { List<MediaDeviceModelItem> lst = new List<MediaDeviceModelItem>(); using (var conn = new SQLiteConnection( @"Data Source=" + Configuration.GetSqliteDbPath(DbName))) { conn.Open(); using (var cmd = new SQLiteCommand(conn)) { using (var transaction = conn.BeginTransaction()) { lst = SelectMediaDevices(cmd); transaction.Commit(); } } conn.Close(); } return lst; }
public static bool addServer(string newServer, int port) { using (SQLiteConnection cnn = new SQLiteConnection(connectionString)) { foreach (KeyValuePair<string, int> server in getServers()) { if (server.Key == newServer) { return false; } } cnn.Open(); int i = 0; using (SQLiteTransaction myTransaction = cnn.BeginTransaction()) { using (SQLiteCommand servCommand = new SQLiteCommand(cnn)) { SQLiteParameter nameParam = new SQLiteParameter(); SQLiteParameter portParam = new SQLiteParameter(); servCommand.CommandText = "INSERT INTO Servers (URL, PORT) VALUES (?, ?)"; nameParam.Value = newServer; portParam.Value = port; servCommand.Parameters.Add(nameParam); servCommand.Parameters.Add(portParam); i = servCommand.ExecuteNonQuery(); } myTransaction.Commit(); } return i != 0; } }
public static bool addChannel(string newChannel, string server) { using (SQLiteConnection cnn = new SQLiteConnection(connectionString)) { foreach (string channel in getChannels(server)) { if (channel == newChannel) { return false; } } cnn.Open(); int i = 0; using (SQLiteTransaction myTransaction = cnn.BeginTransaction()) { using (SQLiteCommand chanCommand = new SQLiteCommand(cnn)) { SQLiteParameter chanParam = new SQLiteParameter(); SQLiteParameter servParam = new SQLiteParameter(); chanCommand.CommandText = "INSERT INTO Channels (SERVER, NAME) VALUES (?, ?)"; servParam.Value = server; chanParam.Value = newChannel; chanCommand.Parameters.Add(servParam); chanCommand.Parameters.Add(chanParam); i = chanCommand.ExecuteNonQuery(); } myTransaction.Commit(); } return i != 0; } }
public void GetSQL( String ConnectionString ) { SQLiteConnection Connection = new SQLiteConnection( ConnectionString ); Connection.Open(); using ( SQLiteTransaction Transaction = Connection.BeginTransaction() ) using ( SQLiteCommand Command = new SQLiteCommand( Connection ) ) { Command.CommandText = "SELECT english, PointerRef FROM Text ORDER BY PointerRef"; SQLiteDataReader r = Command.ExecuteReader(); while ( r.Read() ) { String SQLText; try { SQLText = r.GetString( 0 ).Replace( "''", "'" ); } catch ( System.InvalidCastException ) { SQLText = null; } int PointerRef = r.GetInt32( 1 ); if ( !String.IsNullOrEmpty( SQLText ) ) { if ( PointerRef % 16 == 0 ) { int i = ( PointerRef / 16 ) - 2; Lines[i].SName = SQLText; } else if ( PointerRef % 16 == 4 ) { int i = ( ( PointerRef - 4 ) / 16 ) - 2; Lines[i].SENG = SQLText; } } } Transaction.Rollback(); } return; }
/// <summary> /// Executes sql that doesn't return datatable, will throw exception on any sql errors /// </summary> /// <param name="sqlcommand"></param> /// <param name="connectionStr"></param> /// <returns></returns> public static int ExecuteSQL(string sqlcommand, string connectionStr) { using (SQLiteConnection con = new SQLiteConnection(connectionStr)) { con.Open(); SQLiteTransaction trans = con.BeginTransaction(); try { SQLiteCommand command = con.CreateCommand(); command.Transaction = trans; command.CommandText = sqlcommand; int result = command.ExecuteNonQuery(); trans.Commit(); Logger.LogDebug("sqlite nonquerry: " + sqlcommand + " ;result = " + result); return result; } catch (Exception _e) { Logger.LogError("ExecuteSQL error", THIS, _e); try { trans.Rollback(); } catch (Exception _ee) { Logger.LogError("ExecuteSQL rollback error", THIS, _ee); } throw; } } }
/// <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 Initialize() { using (var rConnection = new SQLiteConnection(@"Data Source=Roaming\Preferences\Main.db; Page Size=8192").OpenAndReturn()) using (var rTransaction = rConnection.BeginTransaction()) { using (var rCommand = rConnection.CreateCommand()) { rCommand.CommandText = "CREATE TABLE IF NOT EXISTS metadata(key TEXT PRIMARY KEY NOT NULL, value) WITHOUT ROWID; " + "INSERT OR IGNORE INTO metadata(key, value) VALUES('version', 1); " + "CREATE TABLE IF NOT EXISTS preference(key TEXT PRIMARY KEY NOT NULL, value) WITHOUT ROWID; " + "INSERT OR REPLACE INTO preference(key, value) VALUES('main.version', @version);"; rCommand.Parameters.AddWithValue("@version", ProductInfo.AssemblyVersionString); rCommand.ExecuteNonQuery(); } rTransaction.Commit(); } Connection = CoreDatabase.Connection; using (var rCommand = Connection.CreateCommand()) { rCommand.CommandText = "ATTACH @filename AS preference;"; rCommand.Parameters.AddWithValue("@filename", new FileInfo(@"Roaming\Preferences\Main.db").FullName); rCommand.ExecuteNonQuery(); } }
/// <summary> /// 对SQLite数据库执行Insert操作,并返回rowID。 /// </summary> /// <param name="sql">要执行的Insert SQL语句</param> /// <param name="parameters">执行Insert语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns>RowID</returns> public static int ExcuteInsertReturnRowID(string sql, SQLiteParameter[] parameters = null) { int rowID = -1; int affectedRows; using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (DbTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql; if (parameters != null) { command.Parameters.AddRange(parameters); } affectedRows = command.ExecuteNonQuery(); } transaction.Commit(); } if (affectedRows == 0) { return rowID; } string getRowIDSql = "select last_insert_rowid()"; using (SQLiteCommand getRowIDCmd = new SQLiteCommand(getRowIDSql, connection)) { SQLiteDataAdapter adapter = new SQLiteDataAdapter(getRowIDCmd); DataTable data = new DataTable(); adapter.Fill(data); rowID = Convert.ToInt32(data.Rows[0][0]); } } return rowID; }
public void RunScript(String script, bool inTransaction = false) { SQLiteConnection conn = new SQLiteConnection(String.Format("Data source={0}", fileName)); conn.Open(); using (conn) { SQLiteTransaction tran = null; if (inTransaction) conn.BeginTransaction(); try { String[] commands = script.Split(new String[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries); foreach (String command in commands) { String s = command;//.Replace("\r\n", ""); if (!String.IsNullOrEmpty(s)) { new SQLiteCommand(s, conn, tran).ExecuteNonQuery(); } } if (inTransaction) tran.Commit(); } catch (Exception e) { if (inTransaction) tran.Rollback(); throw e; } } }
private int batchExecuteSQL(string[] sql) { int buffer = 0; SQLiteConnection _connection = new SQLiteConnection(this._connectionString); _connection.Open(); SQLiteTransaction trans = _connection.BeginTransaction(); SQLiteCommand cmd = _connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.Transaction = trans; try { for (int i = 0; i < sql.Length; i++) { cmd.CommandText = sql[i]; cmd.ExecuteNonQuery(); buffer++; //触发事件 if (buffer == NotifyAfter) { if (RowsCopied != null) { RowsCopied(this, new SQLiteRowsCopiedEventArgs(buffer, sql.Length)); } buffer = 0; } } if (buffer != 0) { if (RowsCopied != null) { RowsCopied(this, new SQLiteRowsCopiedEventArgs(buffer, sql.Length)); } buffer = 0; } //提交事务,只有所有的数据都没有问题才提交事务. trans.Commit(); //异步压缩,分析数据库,确保所得的分析是最佳的. ThreadPool.QueueUserWorkItem(new WaitCallback((object o) => { StaticSQLiteHelper.ExecuteNonQuery("VACUUM ANALYZE"); })); } catch (SQLiteException) { trans.Rollback(); return 0; } finally { trans.Dispose(); cmd.Dispose(); } return sql.Length; }
// Replaced with Sqlite db //private readonly Dictionary<Guid, List<EventDescriptor>> _current = new Dictionary<Guid, List<EventDescriptor>>(); public void SaveEvents(Guid aggregateId, IEnumerable<Event> events, int expectedVersion) { List<EventDescriptor> eventDescriptors = GetEventsByAggregateId(aggregateId); if(eventDescriptors.Any() && eventDescriptors.Last().Version != expectedVersion && expectedVersion != -1) { throw new ConcurrencyException(); } var i = expectedVersion; using (var sqliteConnection = new SQLiteConnection(_sqLiteConnectionString)) { sqliteConnection.Open(); using (var sqliteTransaction = sqliteConnection.BeginTransaction()) { try { foreach (var @event in events) { i++; @event.Version = i; SaveEvent(new EventDescriptor(aggregateId, @event, i), sqliteTransaction); _publisher.Publish(@event); } sqliteTransaction.Commit(); } catch (Exception) { sqliteTransaction.Rollback(); throw; } } } }
public static String GetJapanese( String ConnectionString, int ID ) { String SQLText = ""; SQLiteConnection Connection = new SQLiteConnection( ConnectionString ); Connection.Open(); using ( SQLiteTransaction Transaction = Connection.BeginTransaction() ) using ( SQLiteCommand Command = new SQLiteCommand( Connection ) ) { Command.CommandText = "SELECT string FROM Japanese WHERE ID = " + ID.ToString(); SQLiteDataReader r = Command.ExecuteReader(); while ( r.Read() ) { try { SQLText = r.GetString( 0 ).Replace( "''", "'" ); } catch ( System.InvalidCastException ) { SQLText = ""; } } Transaction.Rollback(); } Connection.Close(); return SQLText; }
public SQLiteTransaction(SQLiteDatabase database, IsolationLevel level, SQLiteSettings settings) { _database = database; _settings = settings; _connection = _database.ConnectionPool.GetConnection(); _transaction = _connection.BeginTransaction(level); }
//---------------------------------------------------------------------- //should be fastest bulk update //pass in the DB, and two variables to update with it (string, float , float) public void UpdateDB(string _emotion, float _xval, float _yval)//how do we want passed? { SQLiteTransaction trans; string SQL = "INSERT INTO Emotions (emotion, Xvalue, Yvalue) VALUES (emotions='" + _emotion + "',Xvalue='" + _xval + "', Yvalue='" + _yval + "',"; SQLiteCommand myCMD = new SQLiteCommand(SQL); myCMD.Connection = sqlite_conn; sqlite_conn.Open(); trans = sqlite_conn.BeginTransaction(); int retval = 0; try { retval = myCMD.ExecuteNonQuery(); if (retval == 1) { System.Windows.Forms.MessageBox.Show("Row Inserted"); } else { System.Windows.Forms.MessageBox.Show("Row NOT Inserted"); } } catch (Exception ex) { trans.Rollback(); } finally { trans.Commit(); myCMD.Dispose(); sqlite_conn.Close(); } }
/// <summary> /// Run the list command for input connection /// </summary> /// <param name="connection"></param> /// <param name="list"></param> void RunSqlCommand(System.Data.SQLite.SQLiteConnection connection, List <string> list) { int i = 0; using (System.Data.SQLite.SQLiteTransaction trans = connection.BeginTransaction()) { System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(connection); foreach (string commandText in list) { try { if (commandText.Trim().Length == 0) { continue; } command.CommandText = commandText; command.ExecuteNonQuery(); i++; } catch (Exception ex) { Console.WriteLine(ex.Message); } } trans.Commit(); } list.Clear(); }
internal SQLiteEnlistment(SQLiteConnection cnn, Transaction scope) { _transaction = cnn.BeginTransaction(); _scope = scope; _scope.EnlistVolatile(this, System.Transactions.EnlistmentOptions.None); }
public static int ExecuteNonQuery(string sql, SQLiteParameter[] parameters = null, string connectionString = "") { if (string.IsNullOrEmpty(connectionString)) connectionString = ConnectionString; int affectedRows; using (var connection = new SQLiteConnection(connectionString)) { connection.Open(); using (var transaction = connection.BeginTransaction()) { using (var command = new SQLiteCommand(connection)) { command.CommandText = sql; if (parameters != null) { command.Parameters.AddRange(parameters); } affectedRows = command.ExecuteNonQuery(); } transaction.Commit(); } } return affectedRows; }
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; }
/// <summary> /// 不带参数的事务执行 /// </summary> /// <param name="dt">需要操作的表</param> /// <param name="commandText">SQL命令字符串</param> /// <returns></returns> public static int TransExecuteNonQuery(DataTable dt, string commandText) { //加入了详细的任务列表 using (SQLiteConnection conn = new SQLiteConnection(connStr)) { int result = 0; conn.Open(); using (System.Data.SQLite.SQLiteTransaction trans = conn.BeginTransaction()) { using (SQLiteCommand cmd = new SQLiteCommand(conn)) { cmd.Transaction = trans; cmd.CommandText = commandText; try { foreach (DataRow dr in dt.Rows) { result = cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { MessageBox.Show(ex.Message); trans.Rollback(); } } } return result; } }
//add data private void button2_Click(object sender, EventArgs e) { string dbConnectionString = "Data Source=test.db"; using (SQLiteConnection _db_connect = new SQLiteConnection(dbConnectionString)) { _db_connect.Open(); using (SQLiteTransaction trans = _db_connect.BeginTransaction()) { SQLiteCommand cmd = _db_connect.CreateCommand(); // インサート文 cmd.CommandText = "INSERT INTO Test (Name, Age) VALUES (@Name, @Age)"; // パラメータのセット cmd.Parameters.Add("Name", System.Data.DbType.String); cmd.Parameters.Add("Age", System.Data.DbType.Int64); // recordの追加 cmd.Parameters["Name"].Value = "田中"; cmd.Parameters["Age"].Value = 25; cmd.ExecuteNonQuery(); cmd.Parameters["Name"].Value = "高橋"; cmd.Parameters["Age"].Value = 30; cmd.ExecuteNonQuery(); // コミット trans.Commit(); _db_connect.Close(); MessageBox.Show("レコードを追加しました。", "タイトル", MessageBoxButtons.OK, MessageBoxIcon.Information); } } }
private static void addSomeParents(string connectionString) { using (SQLiteConnection myConnection = new SQLiteConnection()) { myConnection.ConnectionString = connectionString; myConnection.Open(); using (SQLiteTransaction myTransaction = myConnection.BeginTransaction()) { using (SQLiteCommand myCommand = new SQLiteCommand(myConnection)) { for (int i = 1; i < 11; i++) { myCommand.CommandText = @"insert into tblParents(BirthDate, Name, LastName) values(@BirthDate, @Name, @LastNam)"; myCommand.Parameters.AddWithValue("@Name", "Parent" + i); myCommand.Parameters.AddWithValue("@LastNam", "LM" + i); myCommand.Parameters.AddWithValue("@BirthDate", DateTime.Now.AddYears(-i * 10)); myCommand.ExecuteNonQuery(); } } myTransaction.Commit(); } } }
public static void FormatDatabase( string Filename, string FilenameGracesJapanese, int maxCharsPerLine ) { //CleanGracesJapanese("Data Source=" + FilenameGracesJapanese); //CleanDatabase( "Data Source=" + Filename ); //return; GraceNoteDatabaseEntry[] entries = GraceNoteDatabaseEntry.GetAllEntriesFromDatabase( "Data Source=" + Filename, "Data Source=" + FilenameGracesJapanese ); SQLiteConnection conn = new SQLiteConnection( "Data Source=" + Filename ); conn.Open(); SQLiteTransaction transaction = conn.BeginTransaction(); foreach ( GraceNoteDatabaseEntry e in entries ) { //e.TextEN = e.TextEN; if ( e.Status == -1 ) { continue; } //e.TextEN = e.TextEN.Trim(); e.TextEN = FormatString( e.TextEN, maxCharsPerLine ); SqliteUtil.Update( transaction, "UPDATE Text SET english = ? WHERE ID = ?", new object[] { e.TextEN, e.ID } ); } transaction.Commit(); conn.Close(); return; }
/// <summary> ///使用事例: /// </summary> /// <param name="connectionString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters) { SQLiteCommand cmd = new SQLiteCommand(); using (SQLiteConnection sqlconn = new SQLiteConnection(connectionString)) { PrepareCommand(cmd, sqlconn, null, cmdType, cmdText, commandParameters); SQLiteTransaction trans = sqlconn.BeginTransaction(); try { cmd.Transaction = trans; int val = cmd.ExecuteNonQuery(); trans.Commit(); //cmd.Parameters.Clear(); return val; } catch (Exception ex) { trans.Rollback(); sqlconn.Close(); throw ex; } } }
public static void AddSomeTblBlogsTestRecords(string connectionString, IList<string> imagesPath) { using (SQLiteConnection myConnection = new SQLiteConnection()) { myConnection.ConnectionString = connectionString; myConnection.Open(); using (SQLiteTransaction myTransaction = myConnection.BeginTransaction()) { using (SQLiteCommand myCommand = new SQLiteCommand(myConnection)) { foreach (var itemPath in imagesPath) { myCommand.CommandText = @"insert into tblBlogs(url, name, thumbnail, NumberOfPosts, AddDate) values(@url, @name, @thumbnail, @NumberOfPosts, @AddDate)"; var name = Path.GetFileNameWithoutExtension(itemPath); myCommand.Parameters.AddWithValue("@url", "www.blog" + name + ".com"); myCommand.Parameters.AddWithValue("@name", "blog" + name); var data = File.ReadAllBytes(itemPath); myCommand.Parameters.AddWithValue("@thumbnail", data); myCommand.Parameters.AddWithValue("@NumberOfPosts", 10); myCommand.Parameters.AddWithValue("@AddDate", DateTime.Now); myCommand.ExecuteNonQuery(); } } myTransaction.Commit(); } } }
/// <summary> /// Imports the data from <paramref name="filePath"/> to the SQLite database specified in <paramref name="connectionString"/>. /// </summary> /// <param name="filePath">The filename (including the path) from which to read.</param> /// <param name="importMembershipData">if set to <c>true</c> import membership data.</param> /// <param name="importGalleryData">if set to <c>true</c> import gallery data.</param> /// <param name="connectionString">The connection string to the SQLite database.</param> internal static void ImportData(string filePath, bool importMembershipData, bool importGalleryData, string connectionString) { if (String.IsNullOrEmpty(filePath)) throw new ArgumentNullException("filePath"); if (String.IsNullOrEmpty(connectionString)) throw new ArgumentNullException("connectionString"); SQLiteTransaction tran = null; SQLiteConnection cn = new SQLiteConnection(connectionString); try { cn.Open(); tran = cn.BeginTransaction(); ClearData(importMembershipData, importGalleryData, cn); using (DataSet ds = GenerateDataSet(filePath)) { if (importMembershipData) { InsertApplications(ds, cn); InsertProfiles(ds, cn); InsertRoles(ds, cn); InsertUsers(ds, cn); InsertUsersInRoles(ds, cn); } if (importGalleryData) { InsertGalleries(ds, cn); InsertAlbums(ds, cn); InsertRolesAlbums(ds, cn); InsertMediaObjects(ds, cn); InsertMediaObjectMetadata(ds, cn); InsertGalleryRoles(ds, cn); InsertAppErrors(ds, cn); } } tran.Commit(); } catch { if (tran != null) tran.Rollback(); throw; } }
//Real execute function private TimeSpan ExecuteInternal(SQLiteConnection connection, SQLiteCommand command, int batchSize, int rowsRequested) { Stopwatch swatch = Stopwatch.StartNew(); //Execute this loop while we do not have reached the requested number of rows int rowsProcessed = 0; while (rowsProcessed < rowsRequested) { //Counts how many rows we have processed already for this batch int rowsInBatchProcessed = 0; //Open a transaction and use it for the current batch using (SQLiteTransaction transaction = connection.BeginTransaction()) { while (rowsProcessed < rowsRequested && rowsInBatchProcessed < batchSize) { FillParameters(command); command.ExecuteNonQuery(); rowsInBatchProcessed++; rowsProcessed++; } transaction.Commit(); } } swatch.Stop(); return swatch.Elapsed; }
public static bool ExecuteNoQueryTran(List<String> listSql) { using (SQLiteConnection conn = new SQLiteConnection(connectionString)) { sLastErr = ""; conn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = conn; SQLiteTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; cmd.CommandType = CommandType.Text;; try { for (int n = 0; n < listSql.Count; n++) { string strsql = listSql[n]; if (strsql.Trim().Length > 1) { sLastErr = strsql; cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); return true; } catch (SQLiteException e) { tx.Rollback(); sLastErr = e.Message + sLastErr; return false; } } }
public static void ReadDatabase( StringFile StringFile, string ConnectionString ) { SQLiteConnection Connection = new SQLiteConnection( ConnectionString ); Connection.Open(); StringFile.Strings = new List<bscrString>(); using ( SQLiteTransaction Transaction = Connection.BeginTransaction() ) using ( SQLiteCommand Command = new SQLiteCommand( Connection ) ) { Command.CommandText = "SELECT english, PointerRef FROM Text ORDER BY PointerRef"; SQLiteDataReader r = Command.ExecuteReader(); while ( r.Read() ) { String SQLText; try { SQLText = r.GetString( 0 ).Replace( "''", "'" ); } catch ( System.InvalidCastException ) { SQLText = ""; } int PointerRef = r.GetInt32( 1 ); bscrString b = new bscrString(); b.Position = (uint)PointerRef; b.String = SQLText; StringFile.Strings.Add( b ); } Transaction.Rollback(); } return; }
public static List<object[]> GenericSqliteSelect( SQLiteConnection Connection, string statement, IEnumerable<object> parameters ) { List<object[]> retval = null; using ( SQLiteTransaction Transaction = Connection.BeginTransaction() ) using ( SQLiteCommand Command = new SQLiteCommand( Connection ) ) { Command.CommandText = statement; foreach ( object p in parameters ) { SQLiteParameter sqp = new SQLiteParameter(); sqp.Value = p; Command.Parameters.Add( sqp ); } SQLiteDataReader rd = Command.ExecuteReader(); List<object[]> objs = new List<object[]>(); while ( rd.Read() ) { object[] obja = new object[rd.FieldCount]; for ( int i = 0; i < rd.FieldCount; ++i ) { obja[i] = rd.GetValue( i ); } objs.Add( obja ); } retval = objs; Transaction.Commit(); } return retval; }
public void CreateTable(string name, IEnumerable <string> columns, bool @override = true) { if (m_Connection == null || m_Connection.State == ConnectionState.Closed || String.IsNullOrEmpty(name)) { return; } string columnString = ""; foreach (string column in columns) { columnString += (columnString.Length > 0 ? ", " : "") + column; } string sqlString = (@override ? "DROP TABLE IF EXISTS " + name + "; CREATE TABLE " : "CREATE TABLE IF NOT EXISTS ") + name + "(" + columnString + ");"; using (SQLiteTransaction transaction = m_Connection.BeginTransaction()) try { using (SQLiteCommand command = new SQLiteCommand(m_Connection)) { command.CommandText = sqlString; command.ExecuteNonQuery(); } } catch { transaction.Rollback(); } finally { transaction.Commit(); } }
private static void addSomeKids(string connectionString) { using (SQLiteConnection myConnection = new SQLiteConnection()) { myConnection.ConnectionString = connectionString; myConnection.Open(); using (SQLiteTransaction myTransaction = myConnection.BeginTransaction()) { using (SQLiteCommand myCommand = new SQLiteCommand(myConnection)) { var rnd = new Random(); for (int i = 1; i < 100; i++) { myCommand.CommandText = @"insert into tblKids(ParentId, BirthDate, Name) values(@ParentId, @BirthDate, @Name)"; myCommand.Parameters.AddWithValue("@Name", "Kid" + i); myCommand.Parameters.AddWithValue("@ParentId", rnd.Next(1, 11)); myCommand.Parameters.AddWithValue("@BirthDate", DateTime.Now.AddYears(-i)); myCommand.ExecuteNonQuery(); } } myTransaction.Commit(); } } }
public DbTransaction GetTransction() { //throw new NotImplementedException(); if (_connection != null) { if (_connection.State == ConnectionState.Closed) { _connection.Open(); } return((DbTransaction)_connection.BeginTransaction()); } return(null); }
public void SaveDictionaryToDatabaseNewConnection([ItemNotNull][JetBrains.Annotations.NotNull] List <Dictionary <string, object> > values, [JetBrains.Annotations.NotNull] string tableName, [JetBrains.Annotations.NotNull] HouseholdKey householdKey) { string sql = "Insert into " + tableName + "("; string fields = ""; string parameters = ""; foreach (KeyValuePair <string, object> pair in values[0]) { fields += pair.Key + ","; parameters += "@" + pair.Key + ","; } fields = fields.Substring(0, fields.Length - 1); parameters = parameters.Substring(0, parameters.Length - 1); sql += fields + ") VALUES (" + parameters + ")"; string dstFileName = GetFilenameForHouseholdKey(householdKey); using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + dstFileName + ";Version=3;Synchronous=OFF;Journal Mode=WAL;")) { conn.Open(); using (var transaction = conn.BeginTransaction()) { var command = conn.CreateCommand(); command.CommandText = sql; foreach (Dictionary <string, object> row in values) { if (row.Count != values[0].Count) { throw new LPGException("Incorrect number of columns"); } command.Parameters.Clear(); foreach (KeyValuePair <string, object> pair in row) { string parameter = "@" + pair.Key; command.Parameters.AddWithValue(parameter, pair.Value); } command.ExecuteNonQuery(); } transaction.Commit(); } conn.Close(); } }
//[JetBrains.Annotations.NotNull] //public string ReturnMainSqlPath() => _filenameByHouseholdKey[Constants.GeneralHouseholdKey].Filename; private static void SaveDictionaryToDatabase([NotNull][ItemNotNull] List <Dictionary <string, object> > values, [NotNull] string tableName, [NotNull] System.Data.SQLite.SQLiteConnection conn) { if (values.Count == 0) { return; } //figure out sql var firstrow = values[0]; string sql = "Insert into " + tableName + "("; string fields = ""; string parameters = ""; foreach (KeyValuePair <string, object> pair in firstrow) { fields += pair.Key + ","; parameters += "@" + pair.Key + ","; } fields = fields.Substring(0, fields.Length - 1); parameters = parameters.Substring(0, parameters.Length - 1); sql += fields + ") VALUES (" + parameters + ")"; //execute the sql using (var transaction = conn.BeginTransaction()) { using (var command = conn.CreateCommand()) { command.CommandText = sql; foreach (Dictionary <string, object> row in values) { if (row.Count != firstrow.Count) { throw new LPGException("Incorrect number of columns"); } command.Parameters.Clear(); foreach (KeyValuePair <string, object> pair in row) { string parameter = "@" + pair.Key; command.Parameters.AddWithValue(parameter, pair.Value); } command.ExecuteNonQuery(); } } transaction.Commit(); } }
public int?UpdateDtfast(DataTable Dt, string commandText) { //if application is exiting the don't perform DB process if (this.isAppClosing) { return(null); } if (!DoesDBConnectionExists()) { return(null); } try { using (var cmd = new SQLiteCommand(_connection)) { using (var transaction = _connection.BeginTransaction()) { SQLiteDataAdapter da = new SQLiteDataAdapter(commandText, _connection); SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da); da.UpdateCommand = cb.GetUpdateCommand(); da.InsertCommand = cb.GetInsertCommand(); da.DeleteCommand = cb.GetDeleteCommand(); da.Update(Dt); transaction.Commit(); } } } catch (Exception ex) { LogTrace.WriteErrorLog(this.GetType().Name, MethodBase.GetCurrentMethod().Name, String.Format("Error in query . " + commandText + ex.StackTrace)); throw; } finally { commandText = string.Empty; } return(Dt.Rows.Count); }
/// <summary> /// Ctor /// </summary> public Bot() { SDB = new SQLiteConnection(); SDB.ConnectionString = BuildConString(@"D:\AIML\ConsoleBot\bin\Debug\AIML.aidb", 8192, 3, 8192, 8192, true, false); SDB.Open(); using (SQLiteTransaction tr = SDB.BeginTransaction()) { using (SQLiteCommand cmd = SDB.CreateCommand()) { cmd.CommandText = "PRAGMA temp_store = 1"; cmd.ExecuteNonQuery(); cmd.CommandText = "PRAGMA count_changes = OFF"; cmd.ExecuteNonQuery(); cmd.CommandText = "PRAGMA page_size=8192"; cmd.ExecuteNonQuery(); } } this.setup(); }
/// <summary> /// Adds the specified user names to the specified roles for the configured applicationName. /// </summary> /// <param name="usernames">A string array of user names to be added to the specified roles.</param> /// <param name="roleNames">A string array of the role names to add the specified user names to.</param> public override void AddUsersToRoles(string[] usernames, string[] roleNames) { foreach (string roleName in roleNames) { if (!RoleExists(roleName)) { throw new ProviderException("Role name not found."); } } foreach (string username in usernames) { if (username.IndexOf(',') > 0) { throw new ArgumentException("User names cannot contain commas."); } foreach (string RoleName in roleNames) { if (IsUserInRole(username, RoleName)) { throw new ProviderException("User is already in role."); } } } SQLiteTransaction tran = null; SQLiteConnection cn = GetDBConnectionForRole(); try { if (cn.State == ConnectionState.Closed) { cn.Open(); } if (!IsTransactionInProgress()) { tran = cn.BeginTransaction(); } using (SQLiteCommand cmd = cn.CreateCommand()) { cmd.CommandText = "INSERT INTO " + USERS_IN_ROLES_TB_NAME + " (UserId, RoleId)" + " SELECT u.UserId, r.RoleId" + " FROM " + USER_TB_NAME + " u, " + ROLE_TB_NAME + " r" + " WHERE (u.LoweredUsername = $Username) AND (u.ApplicationId = $ApplicationId)" + " AND (r.LoweredRoleName = $RoleName) AND (r.ApplicationId = $ApplicationId)"; SQLiteParameter userParm = cmd.Parameters.Add("$Username", DbType.String, MAX_USERNAME_LENGTH); SQLiteParameter roleParm = cmd.Parameters.Add("$RoleName", DbType.String, MAX_ROLENAME_LENGTH); cmd.Parameters.AddWithValue("$ApplicationId", _applicationId); foreach (string username in usernames) { foreach (string roleName in roleNames) { userParm.Value = username.ToLowerInvariant(); roleParm.Value = roleName.ToLowerInvariant(); cmd.ExecuteNonQuery(); } } // Commit the transaction if it's the one we created in this method. if (tran != null) { tran.Commit(); } } } catch { if (tran != null) { tran.Rollback(); } throw; } finally { if (tran != null) { tran.Dispose(); } if (!IsTransactionInProgress()) { cn.Dispose(); } } }
/// <summary> /// Removes a role from the data source for the configured applicationName. /// </summary> /// <param name="roleName">The name of the role to delete.</param> /// <param name="throwOnPopulatedRole">If true, throw an exception if <paramref name="roleName"/> has one or more members and do not delete <paramref name="roleName"/>.</param> /// <returns> /// true if the role was successfully deleted; otherwise, false. /// </returns> public override bool DeleteRole(string roleName, bool throwOnPopulatedRole) { if (!RoleExists(roleName)) { throw new ProviderException("Role does not exist."); } if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0) { throw new ProviderException("Cannot delete a populated role."); } SQLiteTransaction tran = null; SQLiteConnection cn = GetDBConnectionForRole(); try { if (cn.State == ConnectionState.Closed) { cn.Open(); } if (!IsTransactionInProgress()) { tran = cn.BeginTransaction(); } using (SQLiteCommand cmd = cn.CreateCommand()) { cmd.CommandText = "DELETE FROM " + USERS_IN_ROLES_TB_NAME + " WHERE (RoleId IN" + " (SELECT RoleId FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName))"; cmd.Parameters.AddWithValue("$RoleName", roleName.ToLowerInvariant()); cmd.ExecuteNonQuery(); } using (SQLiteCommand cmd = cn.CreateCommand()) { cmd.CommandText = "DELETE FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName AND ApplicationId = $ApplicationId"; cmd.Parameters.AddWithValue("$RoleName", roleName.ToLowerInvariant()); cmd.Parameters.AddWithValue("$ApplicationId", _applicationId); cmd.ExecuteNonQuery(); } // Commit the transaction if it's the one we created in this method. if (tran != null) { tran.Commit(); } } catch { if (tran != null) { tran.Rollback(); } throw; } finally { if (tran != null) { tran.Dispose(); } if (!IsTransactionInProgress()) { cn.Dispose(); } } return(true); }
/// <summary> /// Removes the specified user names from the specified roles for the configured applicationName. /// </summary> /// <param name="usernames">A string array of user names to be removed from the specified roles.</param> /// <param name="roleNames">A string array of role names to remove the specified user names from.</param> public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames) { foreach (string roleName in roleNames) { if (!RoleExists(roleName)) { throw new ProviderException("Role name not found."); } } foreach (string username in usernames) { foreach (string roleName in roleNames) { if (!IsUserInRole(username, roleName)) { throw new ProviderException("User is not in role."); } } } SQLiteTransaction tran = null; SQLiteConnection cn = GetDBConnectionForRole(); try { if (cn.State == ConnectionState.Closed) { cn.Open(); } if (!IsTransactionInProgress()) { tran = cn.BeginTransaction(); } using (SQLiteCommand cmd = cn.CreateCommand()) { cmd.CommandText = "DELETE FROM " + USERS_IN_ROLES_TB_NAME + " WHERE UserId = (SELECT UserId FROM " + USER_TB_NAME + " WHERE LoweredUsername = $Username AND ApplicationId = $ApplicationId)" + " AND RoleId = (SELECT RoleId FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName AND ApplicationId = $ApplicationId)"; SQLiteParameter userParm = cmd.Parameters.Add("$Username", DbType.String, MAX_USERNAME_LENGTH); SQLiteParameter roleParm = cmd.Parameters.Add("$RoleName", DbType.String, MAX_ROLENAME_LENGTH); cmd.Parameters.AddWithValue("$ApplicationId", _applicationId); foreach (string username in usernames) { foreach (string roleName in roleNames) { userParm.Value = username.ToLowerInvariant(); roleParm.Value = roleName.ToLowerInvariant(); cmd.ExecuteNonQuery(); } } // Commit the transaction if it's the one we created in this method. if (tran != null) { tran.Commit(); } } } catch { if (tran != null) { tran.Rollback(); } throw; } finally { if (tran != null) { tran.Dispose(); } if (!IsTransactionInProgress()) { cn.Dispose(); } } }
/// <summary> /// Executes the command. /// </summary> /// <param name="dbCommand">The current sql command.</param> /// <param name="commandText">The command text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>-1 if command execution failed.</returns> public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText, CommandType commandType, string connectionString, params DbParameter[] values) { // Initial connection objects. dbCommand = null; Int32 returnValue = -1; SqliteClient.SQLiteConnection sqlConnection = null; SqliteClient.SQLiteTransaction sqlTransaction = null; try { // Create a new connection. using (sqlConnection = new SqliteClient.SQLiteConnection(connectionString)) { // Open the connection. sqlConnection.Open(); // Start a new transaction. sqlTransaction = sqlConnection.BeginTransaction(); // Create the command and assign any parameters. dbCommand = new SqliteClient.SQLiteCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer( ConnectionContext.ConnectionDataType.SqlDataType, commandText), sqlConnection); dbCommand.CommandType = commandType; dbCommand.Transaction = sqlTransaction; if (values != null) { foreach (SqliteClient.SQLiteParameter sqlParameter in values) { dbCommand.Parameters.Add(sqlParameter); } } // Execute the command. returnValue = dbCommand.ExecuteNonQuery(); // Commit the transaction. sqlTransaction.Commit(); // Close the database connection. sqlConnection.Close(); } // Return true. return(returnValue); } catch (Exception ex) { try { // Attempt to roll back the transaction. if (sqlTransaction != null) { sqlTransaction.Rollback(); } } catch { } // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (sqlConnection != null) { sqlConnection.Close(); } } }
public void BeginTransaction() { transaction_ = conn_.BeginTransaction(); }
static void Main(string[] args) { //To store the index and column name from the file Dictionary <int, string> Columns = new Dictionary <int, string>(); //To store datatypes Dictionary <string, string> ColumnDataTypes = new Dictionary <string, string>(); ColumnDataTypes["id"] = "integer"; ColumnDataTypes["dt"] = "datetime"; ColumnDataTypes["product_id"] = "integer"; ColumnDataTypes["amount"] = "double"; // Define delimiter characters char[] delimiterChars = { '\t' }; //Create Sample data string createQuery = @" create table if not exists products(id integer not null primary key, name text); insert into products (name) values ('A'); insert into products (name) values ('B'); insert into products (name) values ('C'); insert into products (name) values ('D'); insert into products (name) values ('E'); insert into products (name) values ('F'); insert into products (name) values ('G'); create table if not exists orders( id integer not null CHECK(TYPEOF(id) = 'integer'), dt datetime CHECK(JULIANDAY(dt) IS NOT NULL), product_id integer CHECK(TYPEOF(product_id) = 'integer'), amount real CHECK(amount > 0 AND TYPEOF(amount) = 'real'), foreign key(product_id) references products(id) );"; System.Data.SQLite.SQLiteConnection.CreateFile("myDB.db3"); using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=myDB.db3;foreign keys=true;")) { using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); cmd.CommandText = createQuery; cmd.ExecuteNonQuery(); // Read file from App_Data folder string[] lines = System.IO.File.ReadAllLines(Directory.GetCurrentDirectory() + @"../../../App_Data/import.txt"); cmd.CommandText = "INSERT INTO orders ("; // Identify the column order from first row of the import file string[] elements = lines[0].Split(delimiterChars); for (int i = 0; i < elements.Length; i++) { Columns[i] = elements[i]; cmd.CommandText = cmd.CommandText + elements[i] + ", "; } cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 2); cmd.CommandText = cmd.CommandText + ") VALUES ("; string temp = cmd.CommandText; //Create Insert Statements //As insert itself is slow, it is better to use transaction using (SQLiteTransaction tr = conn.BeginTransaction()) { int j = 0; cmd.Transaction = tr; for (int i = 1; i < lines.Length; i++) { try { cmd.CommandText = temp; elements = lines[i].Split(delimiterChars); for (j = 0; j < elements.Length; j++) { switch (ColumnDataTypes[Columns[j]]) { case "double": double columnDouble = Convert.ToDouble(elements[j]); cmd.CommandText = cmd.CommandText + "@VALUE" + j + ", "; cmd.Parameters.AddWithValue("@VALUE" + j, columnDouble); break; case "integer": int columnInt = Int32.Parse(elements[j]); cmd.CommandText = cmd.CommandText + "@VALUE" + j + ", "; cmd.Parameters.AddWithValue("@VALUE" + j, columnInt); break; default: cmd.CommandText = cmd.CommandText + "@VALUE" + j + ", "; cmd.Parameters.AddWithValue("@VALUE" + j, elements[j]); break; } } // Insert values, try catch exceptions cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 2); cmd.CommandText = cmd.CommandText + ")"; try { cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("On line " + (i + 1) + " caught exception: " + ex.Message + "\nThe Insert Statement: " + cmd.CommandText + "\nValues: " + string.Join(", ", elements) + "\n"); } } catch (Exception ex) { Console.WriteLine("On line " + (i + 1) + " caught exception: " + ex.Message + "\nColumn: " + Columns[j] + ", value: " + elements[j]); break; } } tr.Commit(); } cmd.CommandText = "Select * from orders"; cmd.ExecuteNonQuery(); using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader()) { Console.WriteLine("ID | Datetime |Product_ID|Amount|"); while (reader.Read()) { Console.WriteLine(reader["ID"] + " | " + reader["dt"] + " | " + reader["product_id"] + " | " + reader["amount"]); } } // SQL query 1 cmd.CommandText = @"select a.product_id, count(*) as cnt, sum(amount) as sm from orders a where strftime('%m', dt) = '10' and strftime('%Y', dt) = '2017' group by product_id;"; cmd.ExecuteNonQuery(); using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader()) { Console.WriteLine("\n1.Query"); Console.WriteLine(" product_id | count | Sum "); while (reader.Read()) { Console.WriteLine(reader["product_id"] + " | " + reader["cnt"] + " | " + " | " + reader["sm"]); } } // SQL query 2.a cmd.CommandText = @"select a.product_id from orders a where strftime('%m', dt) = strftime('%m', date('now')) and strftime('%Y', dt) = strftime('%Y', date('now')) and product_id not in (select product_id from orders where strftime('%m', dt) = strftime('%m', date('now', '-1 month')) and strftime('%Y', dt) = strftime('%Y', date('now')) ) group by product_id ;"; cmd.ExecuteNonQuery(); using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader()) { Console.WriteLine("2.a query\nproduct_id "); while (reader.Read()) { Console.WriteLine(reader["product_id"]); } } // SQL query 2.b cmd.CommandText = @"select a.product_id from orders a where strftime('%m', dt) = strftime('%m', date('now', '-1 month')) and strftime('%Y', dt) = strftime('%Y', date('now')) and product_id not in (select product_id from orders where strftime('%m', dt) = strftime('%m', date('now')) and strftime('%Y', dt) = strftime('%Y', date('now')) ) group by product_id ;"; cmd.ExecuteNonQuery(); using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader()) { Console.WriteLine("2.b query\nproduct_id "); while (reader.Read()) { Console.WriteLine(reader["product_id"]); } } //SQL query 3 cmd.CommandText = @"select strftime('%m', a.dt) || '/' || strftime('%Y', a.dt) as Period, a.product_id, max(sm) as Mx, max(sm)*100 /b.total as Percentage from (select dt, product_id, sum(amount) as sm from orders group by strftime('%Y', dt), strftime('%m', dt), product_id) a left join (select strftime('%m', dt) as month, strftime('%Y', dt) as year, sum(amount) as total from orders group by strftime('%Y', dt), strftime('%m', dt)) b on strftime('%m',a.dt) = b.month and strftime('%Y',a.dt) = b.year group by strftime('%m', a.dt), strftime('%Y', a.dt);" ; cmd.ExecuteNonQuery(); using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader()) { Console.WriteLine("\n3. "); Console.WriteLine(" Period | Product_id | Sum | Percentage |"); while (reader.Read()) { Console.WriteLine(reader["Period"] + " | " + reader["product_id"] + " | " + reader["Mx"] + " | " + reader["Percentage"]); } } conn.Close(); } } Console.WriteLine("Press any key to exit."); Console.ReadLine(); }
static void Main(string[] args) { Database databaseObject = new Database(); /* * // * INSERT INTO DATABASE * // */ string createQuery = @"CREATE TABLE IF NOT EXISTS [Mytable] ( [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Name] NVARCHAR(2048) NULL, [Gender] NVARCHAR(2048) 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,Gender) values('Kenneth', 'Male')"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Mytable (Name,Gender) values('Monique', 'Female')"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Mytable (Name,Gender) values('Jimmy-Crack-Corn', 'Bird')"; cmd.ExecuteNonQuery(); /* * // * SELECT FROM DATABASE * // */ cmd.CommandText = "SELECT * from Mytable"; using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader["Name"] + " : " + reader["Gender"]); } conn.Close(); } } /* * DELETE FROM DATABASE * */ using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn)) { SQLiteTransaction trans; conn.ConnectionString = @"data source = C:\Users\Kenneth D Clark\Documents\Visual Studio 2017\Projects\Ch27Lab\Ch27Lab\bin\Debug\sample.db3"; conn.Open(); trans = conn.BeginTransaction(); cmd.Connection = conn; cmd.CommandText = "DELETE FROM Mytable WHERE name = @Name"; cmd.Parameters.AddWithValue("@Name", "Jimmy-Crack-Corn"); cmd.ExecuteNonQuery(); trans.Commit(); } } Console.ReadLine(); }
private static void DoCreateDatabaseSchema() { SQLiteConnection.CreateFile(dataBaseFile); var sqLiteConnection = new SQLiteConnection(string.Format("Data Source={0}", dataBaseFile)); sqLiteConnection.Open(); using (DbTransaction dbTrans = sqLiteConnection.BeginTransaction()) { using (DbCommand sqLiteCommand = sqLiteConnection.CreateCommand()) { const string eventsTables = @" CREATE TABLE Events ( [AggregateId] [uniqueidentifier] not null, [EventData] [binary] not null, [Version] [int] not null ); "; sqLiteCommand.CommandText = eventsTables; sqLiteCommand.ExecuteNonQuery(); } dbTrans.Commit(); } sqLiteConnection.Close(); }