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