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