/// <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>
/// Prints Records in Database Table: For SQL Client Demo
/// </summary>
/// <param name="conn">SqlConnetion Type, has the connection settings</param>
public static void ORM_SQLClient_PrintTable(SqlConnection conn)
{
//Will Hold Database Query Result Set
List <XSDTable> tableRecords = new List <XSDTable>();
//String Padding
const int spacePadding = 15;
#region Open Database Connect, Prepare SQL
//Open a new Database Connection
conn.Open();
//Configure SQL Command to be used
const string sqlQuery = "Select Firstname, Lastname, Age From XSDTable";
SqlCommand command = new SqlCommand()
{
CommandText = sqlQuery,
Connection = conn
};
#endregion
#region Execute Query, Read Result Set, Print Results
SqlDataReader reader = command.ExecuteReader();
//Read and Store collection of records returned
while (reader.Read())
{
string firstName = reader.GetString(0);
string lastName = reader.GetString(1);
long age = reader.GetInt64(2);
XSDTable record = new XSDTable()
{
Firstname = firstName,
Lastname = lastName,
Age = age
};
tableRecords.Add(record);
}
//Print Datbase Records to the Screen
if (tableRecords.Count > 0)
{
//Print Columns Headers, using the first record from the Database Table
Console.WriteLine("{0}*{1}*{2}*"
, @"Firstname".PadLeft(spacePadding)
, @"Lastname".PadLeft(spacePadding)
, @"Age".PadLeft(spacePadding));
foreach (var tableRecord in tableRecords)
{
Console.WriteLine("{0} {1} {2}", tableRecord.Firstname.PadLeft(spacePadding)
, tableRecord.Lastname.PadLeft(spacePadding)
, tableRecord.Age.ToString().PadLeft(spacePadding));
}
}
#endregion
#region To Dispose
conn.Close();
command.Dispose();
reader.Dispose();
#endregion
}
/// <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();
}
partial void DeleteXSDTable(XSDTable instance);
partial void UpdateXSDTable(XSDTable instance);
partial void InsertXSDTable(XSDTable instance);
