static void runDeleteOperation1()
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table <Color> colors = dbContext.GetTable <Color>();
var queriedColors = from c in colors
where c.ID > 3
select c;
foreach (Color color in queriedColors)
{
colors.DeleteOnSubmit(color);
}
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}
static void runDeleteOperation1()
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table<Color> colors = dbContext.GetTable<Color>();
var queriedColors = from c in colors
where c.ID > 3
select c;
foreach (Color color in queriedColors)
colors.DeleteOnSubmit(color);
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}
public static IEnumerable <object[]> ExecuteQuery(
this DbLinq.Data.Linq.DataContext ctx, string query)
{
using (System.Data.Common.DbCommand cmd = ctx.Connection.CreateCommand())
{
cmd.CommandText = query;
ctx.Connection.Open();
using (System.Data.Common.DbDataReader rdr =
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
object[] res = new object[rdr.FieldCount];
rdr.GetValues(res);
yield return(res);
}
}
}
}
static void runSelectOperation1()
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table <Color> colors = dbContext.GetTable <Color>();
var queriedColors = from c in colors
select c;
foreach (var color in queriedColors)
{
Console.WriteLine("ID: {0}, Name: {1}, HexChars: {2}, Description: {3}, HexCode: {4}", color.ID, color.Name, color.HexChars, (null == color.Description ? "null" : color.Description), (null == color.HexCode ? "null" : string.Format("0x{0:X}", color.HexCode)));
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}
static void runUpdateOperation1()
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table<Color> colors = dbContext.GetTable<Color>();
// for an existing record.
{
try
{
Color queriedColor = (from c in colors
//where string.Equals(c.Name, "Color #01") // not working
where c.Name == "Color #01"
select c).First();
//select c).Single();
queriedColor.HexChars = "0F0F0F";
}
catch (InvalidOperationException ex)
{
Console.WriteLine("DB query error: {0}", ex.Message);
}
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
// a new record is not inserted.
{
try
{
Color queriedColor = (from c in colors
//where string.Equals(c.Name, "Color #20") // not working
where c.Name == "Color #20"
select c).First();
//select c).Single();
queriedColor.HexChars = "0F0F0F";
}
catch (InvalidOperationException ex)
{
Console.WriteLine("DB query error: {0}", ex.Message);
}
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}
static void runSelectOperation1()
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table<Color> colors = dbContext.GetTable<Color>();
var queriedColors = from c in colors
select c;
foreach (var color in queriedColors)
Console.WriteLine("ID: {0}, Name: {1}, HexChars: {2}, Description: {3}, HexCode: {4}", color.ID, color.Name, color.HexChars, (null == color.Description ? "null" : color.Description), (null == color.HexCode ? "null" : string.Format("0x{0:X}", color.HexCode)));
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}
static void runInsertOperation1()
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table<Color> colors = dbContext.GetTable<Color>();
//Color magenta = new Color { Name = "megenta", HexChars = "ff00ff", Description = null, HexCode = null };
Color magenta = new Color { Name = "megenta", HexChars = "ff00ff", Description = "it is a magenta", HexCode = 0xFF00FF };
colors.InsertOnSubmit(magenta);
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table<Color> colors = dbContext.GetTable<Color>();
for (int i = 0; i < 16; ++i)
colors.InsertOnSubmit(new Color() { Name = string.Format("Color #{0:D2}", i + 1), HexChars = string.Format("{0:X6}", i + 1) });
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}
static void runInsertOperation1()
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table <Color> colors = dbContext.GetTable <Color>();
//Color magenta = new Color { Name = "megenta", HexChars = "ff00ff", Description = null, HexCode = null };
Color magenta = new Color {
Name = "megenta", HexChars = "ff00ff", Description = "it is a magenta", HexCode = 0xFF00FF
};
colors.InsertOnSubmit(magenta);
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table <Color> colors = dbContext.GetTable <Color>();
for (int i = 0; i < 16; ++i)
{
colors.InsertOnSubmit(new Color()
{
Name = string.Format("Color #{0:D2}", i + 1), HexChars = string.Format("{0:X6}", i + 1)
});
}
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}
static void runUpdateOperation1()
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
using (DbLinq.Data.Linq.DataContext dbContext = new DbLinq.Data.Linq.DataContext(connection))
{
DbLinq.Data.Linq.Table <Color> colors = dbContext.GetTable <Color>();
// for an existing record.
{
try
{
Color queriedColor = (from c in colors
//where string.Equals(c.Name, "Color #01") // not working
where c.Name == "Color #01"
select c).First();
//select c).Single();
queriedColor.HexChars = "0F0F0F";
}
catch (InvalidOperationException ex)
{
Console.WriteLine("DB query error: {0}", ex.Message);
}
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
// a new record is not inserted.
{
try
{
Color queriedColor = (from c in colors
//where string.Equals(c.Name, "Color #20") // not working
where c.Name == "Color #20"
select c).First();
//select c).Single();
queriedColor.HexChars = "0F0F0F";
}
catch (InvalidOperationException ex)
{
Console.WriteLine("DB query error: {0}", ex.Message);
}
try
{
dbContext.SubmitChanges();
}
catch (System.Data.Linq.ChangeConflictException)
{
foreach (System.Data.Linq.ObjectChangeConflict conflict in dbContext.ChangeConflicts)
{
foreach (System.Data.Linq.MemberChangeConflict changeConflict in conflict.MemberConflicts)
{
Console.WriteLine("Conflict Details:");
Console.WriteLine("\tOriginal value retrieved from database: {0}", changeConflict.OriginalValue);
Console.WriteLine("\tCurrent value in database: {0}", changeConflict.DatabaseValue);
Console.WriteLine("\tOriginal value in memory: {0}", changeConflict.CurrentValue);
}
conflict.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
}
}
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}