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