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 void G11_TwoSequencesInTable()
{
Northwind dbo = CreateDB();
NorthwindG11 db = new NorthwindG11(dbo.Connection);
db.ExecuteCommand(@"create sequence rid_id1_seq");
db.ExecuteCommand(@"create sequence rid_reanr_seq");
#if INGRES
db.ExecuteCommand(@"create table Rid ( id int primary key DEFAULT rid_id1_seq.nextval, reanr int DEFAULT rid_reanr_seq.nextval)");
#else
db.ExecuteCommand(@"create temp table Rid ( id int primary key DEFAULT nextval('rid_id1_seq'), reanr int DEFAULT nextval('rid_reanr_seq'))");
#endif
DbLinq.Data.Linq.Table <NorthwindG11.Rid> Rids = db.GetTable <NorthwindG11.Rid>();
var Rid = new NorthwindG11.Rid();
Rid.Reanr = 22;
Exception e = null;
db.Rids.InsertOnSubmit(Rid);
Rid = new NorthwindG11.Rid();
Rid.Reanr = 23;
db.Rids.InsertOnSubmit(Rid);
try
{
db.SubmitChanges();
}
catch (Exception ex)
{
e = ex;
}
db.ExecuteCommand("drop table rid");
db.ExecuteCommand("drop sequence rid_reanr_seq");
db.ExecuteCommand("drop sequence rid_id1_seq");
if (e != null)
{
throw e;
}
Assert.AreEqual(2, Rid.Id);
Assert.AreEqual(23, Rid.Reanr);
}
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 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);
}
}