public static void Raw_StoredProcedure( IKLink link )
{
Console.WriteLine( "\n===== Invoking a Stored Procedure through a Raw Command..." );
// Inserting an employee through a stored procedure...
var cmd = link.Raw(
"EXEC employee_insert @FirstName = {0}, @LastName = {1}",
"James", "Bond" );
Console.WriteLine( "\n> Command => {0}", cmd );
object[] list = cmd.ToArray();
foreach( var obj in list ) Console.WriteLine( "\n> Record => {0}", obj );
cmd.Dispose();
// Deleting to leave everything as it was...
foreach( var obj in list ) {
dynamic d = obj;
string id = d.Id; // We are implicitly using the default table...
var delete = link.Raw( "DELETE FROM Employees WHERE Id = {0}", id );
Console.WriteLine( "\n> Command => {0}", delete );
int n = delete.Execute();
Console.WriteLine( "\n> Result => {0}", n );
delete.Dispose();
}
}
public static void Raw_Query( IKLink link )
{
Console.WriteLine( "\n===== Raw Query..." );
var cmd = link.Raw( "SELECT * FROM Employees WHERE BirthDate >= {0}", new CalendarDate( 1969, 1, 1 ) );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var obj in cmd ) Console.WriteLine( "\n> Record => {0}", obj );
cmd.Dispose();
}
public static void Raw_Query_MultipleTables( IKLink link )
{
Console.WriteLine( "\n===== Raw Query with Multiple Tables..." );
var cmd = link.Raw(
"SELECT * FROM Employees AS Emp, Countries as Ctry WHERE Emp.BirthDate >= {0} AND Emp.CountryId = Ctry.Id",
new CalendarDate( 1969, 1, 1 ) );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var obj in cmd ) Console.WriteLine( "\n> Record => {0}", obj );
cmd.Dispose();
}
public static void Raw_Delete_JamesBond_NonEnumerate( IKLink link )
{
Console.WriteLine( "\n===== Raw Delete James Bond but with no Enumeration..." );
var cmd = link.Raw( "DELETE FROM Employees WHERE Id = {0}", "007" );
Console.WriteLine( "\n> Command => {0}", cmd );
int n = cmd.Execute();
Console.WriteLine( "\n> Result => {0}", n );
cmd.Dispose();
}
public static void Raw_Insert_JamesBond_Enumerate( IKLink link )
{
Console.WriteLine( "\n===== Raw Insert James Bond with Enumerate..." );
// Note that to enumerate in a non-SELECT Raw command, we need to explicitly include "OUTPUT INSERTED.*"...
var cmd = link.Raw(
"INSERT INTO Employees ( Id, FirstName, LastName, CountryId, BirthDate, JoinDate, Photo )"
+ " OUTPUT INSERTED.*"
+ " VALUES ( {0}, {1}, {2}, {3}, {4}, {5}, {6} )",
"007", "James", "Bond", "uk", new CalendarDate( 1969, 1, 1 ), null, new byte[] { 0, 0, 7 } );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var obj in cmd ) Console.WriteLine( "\n> Record => {0}", obj );
cmd.Dispose();
}
public static void Nested_UpdateReaders( IKLink link )
{
Console.WriteLine( "\n===== Nested Updates..." );
KCommandRaw raw = link.Raw();
Console.WriteLine( "\n== SUSPENDING THE CONSTRAINTS..." );
raw.Set( "ALTER TABLE Countries NOCHECK CONSTRAINT ALL" ); raw.Execute();
raw.Set( "ALTER TABLE Employees NOCHECK CONSTRAINT ALL" ); raw.Execute();
var cmd = link.Update( x => x.Countries )
.Where( x => x.Id == "es" )
.Column( x => x.Id = "es#" );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var ctry in cmd.ConvertBy( rec => {
CountryTable table = new CountryTable(); dynamic d = rec;
table.Id = d.Id;
table.Name = d.Name;
table.RegionId = d.RegionId;
_Nested_UpdateReaders_Employees( link );
return table;
} ) ) Console.WriteLine( "\n> Country => {0}", ctry );
cmd.Dispose();
Console.WriteLine( "\n== REACTIVATING THE CONSTRAINTS..." );
raw.Set( "ALTER TABLE Countries CHECK CONSTRAINT ALL" ); raw.Execute();
raw.Set( "ALTER TABLE Employees CHECK CONSTRAINT ALL" ); raw.Execute();
}
