/// <summary> /// Prints Records in Database Table: For DataContext Demo /// </summary> /// <param name="dataContext"></param> public static void ORM_DataContext_PrintTable(ZeroCoolTableClassesDataContext dataContext) { if (dataContext != null) { int spacePadding = 15; var resultSet = from people in dataContext.XSDTables select people; if (resultSet.Any()) { //Create object of POCO XSDTable firstRow = new XSDTable(); //Print Columns Headers, using the first record from the Database Table Console.WriteLine("{0}*{1}*{2}*" , nameof(firstRow.Firstname).PadLeft(spacePadding) , nameof(firstRow.Lastname).PadLeft(spacePadding) , nameof(firstRow.Age).PadLeft(spacePadding)); //Loop Through each database record and print it foreach (var record in resultSet) { Console.WriteLine("{0}|{1}|{2}|" , record.Firstname.PadLeft(spacePadding) , record.Lastname.PadLeft(spacePadding) , record.Age.ToString().PadLeft(spacePadding)); } } } }
/// <summary> /// Sets the Database Table back to the original record /// </summary> public static void ResetDatabaseTable() { ZeroCoolTableClassesDataContext x = new ZeroCoolTableClassesDataContext(); x.XSDTables.DeleteAllOnSubmit(x.XSDTables); x.XSDTables.InsertOnSubmit(new XSDTable() { Firstname = "Derrick", Lastname = "Ward", Age = 25 }); try { x.SubmitChanges(); } catch (Exception e) { Console.WriteLine("Unable to Reset Database: " + e); } }
/// <summary> /// Showcases the Datacontext version of Object Relational Mapping /// </summary> public static void ORM_DataContext_Demo() { /* High Level Generic Steps: * 1) Create a Database in SQL Server * 2) Right-Click Project -> Add New Item -> Add a LinqToSQL Classes "Item" to your Project * A) Add database tables to the designer, by dragging what you want from the sql server object explorer * 3) Reading from a Database Table * A) Instantiate your DataContext Object * B) Use LinQ to Aggregate a SQL Result Set from the Database * I) Result set will be stored in an IQueryable collection. * -Collection would stored items of the type of your Database Table * ...To Be Continued */ //Gets all the tables in the Data Context ZeroCoolTableClassesDataContext x = new ZeroCoolTableClassesDataContext(); //Reading Database Table and Printing Records ClearAndDisplayMessage("Reading Database Table and Printing Records:"); ORM_DataContext_PrintTable(x); ContinueOn(); #region Record Insert ClearAndDisplayMessage("Now I will insert a record..\nPrinting Database Table Records now:"); XSDTable testRecord = new XSDTable(); testRecord.Firstname = "Joe"; testRecord.Lastname = "Robert"; testRecord.Age = 25; x.XSDTables.InsertOnSubmit(testRecord); try { x.SubmitChanges(); } catch (Exception e) { Console.WriteLine("<DataContext>: Unable to Insert Record. \n" + e); } #endregion ORM_DataContext_PrintTable(x); ContinueOn(); #region Record Update ClearAndDisplayMessage("Now I will Update a record..Can you guess which :)\nPrinting Database Table Records now:"); XSDTable recordToModify = x.XSDTables.Where(y => y.Firstname == "Derrick" && y.Lastname == "Ward").FirstOrDefault(); recordToModify.Lastname = "Kyle-" + recordToModify.Lastname; try { x.SubmitChanges(); } catch (Exception e) { Console.WriteLine("<DataContext>: Unable to Modify Record. \n" + e); } #endregion ORM_DataContext_PrintTable(x); ContinueOn(); #region Record Update ClearAndDisplayMessage("Now I will Put the record back :)\nPrinting Database Table Records now:"); recordToModify = x.XSDTables.Where(y => y.Firstname == "Derrick" && y.Lastname == "Kyle-Ward").FirstOrDefault(); recordToModify.Lastname = recordToModify.Lastname.Substring(recordToModify.Lastname.IndexOf("Kyle-") + ("Kyle-").Length); try { x.SubmitChanges(); } catch (Exception e) { Console.WriteLine("<DataContext>: Unable to Modify Record back to its original. \n" + e); } #endregion ORM_DataContext_PrintTable(x); ContinueOn(); #region Delete Record ClearAndDisplayMessage("Now I will Delete a record..Can you guess which :)\nPrinting Database Table Records now:"); recordToModify = x.XSDTables.Where(y => y.Firstname == "Joe" && y.Lastname == "Robert").FirstOrDefault(); x.XSDTables.DeleteOnSubmit(recordToModify); try { x.SubmitChanges(); } catch (Exception e) { Console.WriteLine("<DataContext>: Unable to Delete Record.\n" + e); } #endregion ORM_DataContext_PrintTable(x); ContinueOn(); //Dispose of Resources x.Dispose(); }