/// <summary>
/// Instantiates the object and sets properties based on the field name. Only returns the first row.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="cmd"></param>
/// <param name="load">A method that will create an object and fill it. If null, the object will be instantiated based on its type using the ClassFactory (must have a default ctor). If this returns null, it will not be added to the results.</param>
/// <returns></returns>
public static IEnumerable <T> GetObjects <T>(this BaDatabase db, DbCommand cmd, Func <DbDataReader, T> load = null) where T : class
{
var results = new List <T>();
// If load doesn't have a value, use the default loader.
if (load == null)
{
var props = TypeDescriptor.GetProperties(typeof(T));
load = (row) =>
{
var obj = ClassFactory.CreateObject <T>();
FillObject(row, obj, props);
return(obj);
};
}
db.ExecuteReader(cmd, (row) =>
{
var result = load(row);
if (result != null)
{
results.Add(result);
}
return(true);
});
return(results);
}
/// <summary>
/// Instantiates the object and sets properties based on the field name. Only returns the first row.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="cmd"></param>
/// <param name="load">A method that will create an object and fill it. If null, the object will be instantiated based on its type using the ClassFactory (must have a default ctor). If this returns null, it will not be added to the results.</param>
/// <returns></returns>
public async static Task <IEnumerable <T> > GetObjectsAsync <T>(this BaDatabase db, DbCommand cmd, Func <DbDataReader, Task <T> > load = null) where T : class
{
var results = new List <T>();
// If load doesn't have a value, use the default loader.
if (load == null)
{
var props = TypeDescriptor.GetProperties(typeof(T));
load = async(row) =>
{
var obj = ClassFactory.CreateObject <T>();
FillObject(row, obj, props);
return(await Task.FromResult(obj).ConfigureAwait(false));
};
}
await db.ExecuteReaderAsync(cmd, async (row) =>
{
var result = await load(row).ConfigureAwait(false);
if (result != null)
{
results.Add(result);
}
return(true);
}).ConfigureAwait(false);
return(results);
}
/// <summary>
/// Inserts a new record into the table.
/// </summary>
/// <param name="db"></param>
/// <param name="tableName">Name of the table to insert into.</param>
/// <param name="values">The values that will be added to the table. Can be anything that can be converted to a property bag.</param>
/// <returns>The newly inserted row.</returns>
public static dynamic Insert(this BaDatabase db, string tableName, object values)
{
var cmd = db.Connection.CreateCommand();
PrepareInsertCmd(cmd, tableName, values);
return(db.GetDynamic(cmd));
}
/// <summary>
/// Shows how to simply update a single value without bothering to fetch data from the
/// database first.
/// </summary>
public static void UpdateBirthDate()
{
var p = new Person();
p.Id = 1002;
p.BirthDate = DateTime.Now.AddYears(-10);
// Validate the data before saving it (only validates the fields that are set).
var errs = p.Validate();
if (errs.Count() > 0)
{
Console.WriteLine("ERROR!!!");
foreach (var err in errs)
{
Console.WriteLine(err.ErrorMessage);
}
}
else
{
using (var db = BaDatabase.Create("MyDatabase"))
{
db.Save(p);
var p2 = db.Get <Person>(new { p.Id }).FirstOrDefault();
Console.WriteLine($"[{p2.Id}] {p2.FirstName} {p2.LastName}, born {p2.BirthDate:D}.");
}
}
}
/// <summary>
/// Deletes the records with the given key.
/// </summary>
/// <param name="db"></param>
/// <param name="tableName">Name of the table to remove records from.</param>
/// <param name="key">Key of the record to delete. Can be anything that can be converted to a property bag.</param>
/// <returns></returns>
public static int Delete(this BaDatabase db, string tableName, object key)
{
var cmd = db.Connection.CreateCommand();
PrepareDeleteCmd(cmd, tableName, key);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// Shows how to use GetDynamic for stored procedures.
/// </summary>
public static void ExtractDataFromSproc()
{
using (var db = BaDatabase.Create("MyDatabase"))
{
// NOTE: GetObjects is an extension method from BizArk.Data.SprocExt.
var bart = db.GetDynamic("MyPeopleSproc", new { Id = 1003 });
}
}
/// <summary>
/// Deletes the records with the given key.
/// </summary>
/// <param name="db"></param>
/// <param name="tableName">Name of the table to remove records from.</param>
/// <param name="key">Key of the record to delete. Can be anything that can be converted to a property bag.</param>
/// <returns></returns>
public async static Task <int> DeleteAsync(this BaDatabase db, string tableName, object key)
{
var conn = await db.GetConnectionAsync();
var cmd = conn.CreateCommand();
PrepareDeleteCmd(cmd, tableName, key);
return(await db.ExecuteNonQueryAsync(cmd).ConfigureAwait(false));
}
/// <summary>
/// Shows how to save data.
/// </summary>
public static void SaveData()
{
using (var db = BaDatabase.Create("MyDatabase"))
{
var cmd = new SqlCommand("UPDATE Person SET BirthDate = @BirthDate WHERE PersonID = @PersonID");
cmd.AddParameters(new { BirthDate = DateTime.Now.AddYears(-10) });
db.ExecuteNonQuery(cmd);
}
}
/// <summary>
/// Shows how to use GetDynamics to extract data.
/// </summary>
/// <remarks>
/// This is the recommended way to extract data into dynamic objects. Dynamic objects are
/// particularly useful when you are simply taking data from the database and stuffing it
/// into a response (such as a JSON blob in an API request or using it in a CSHTML file).
/// </remarks>
public static void ExtractDynamicData()
{
using (var db = BaDatabase.Create("MyDatabase"))
{
var cmd = new SqlCommand("SELECT * FROM Person");
// NOTE: GetDynamics is an extension method from BizArk.Data.ExtractExt.
var people = db.GetDynamics(cmd);
}
}
/// <summary>
/// Inserts a new record into the table.
/// </summary>
/// <param name="db"></param>
/// <param name="tableName">Name of the table to insert into.</param>
/// <param name="values">The values that will be added to the table. Can be anything that can be converted to a property bag.</param>
/// <returns>The newly inserted row.</returns>
public async static Task <dynamic> InsertAsync(this BaDatabase db, string tableName, object values)
{
var conn = await db.GetConnectionAsync().ConfigureAwait(false);
var cmd = conn.CreateCommand();
PrepareInsertCmd(cmd, tableName, values);
return(await db.GetDynamicAsync(cmd).ConfigureAwait(false));
}
/// <summary>
/// Shows how to use a BizArk style repository.
/// </summary>
public void ExtensionRepository()
{
using (var db = BaDatabase.Create("MyDatabase"))
{
// Cast BaDatabase to an IBaRepository so that when we use it, we don't have a
// bunch of BaDatabase methods cluttering the interface.
var rep = (IBaRepository)db;
rep.GetPerson(1002);
}
}
/// <summary>
/// Shows how to use TryTransaction.
/// </summary>
public static void TryTransaction()
{
using (var db = BaDatabase.Create("MyDatabase"))
{
db.TryTransaction(() =>
{
// Some complex database updates
// NOTE: There is no need to call commit or rollback. It's handled for you.
});
}
}
/// <summary>
/// Gets the first row as a dynamic object.
/// </summary>
/// <param name="db"></param>
/// <param name="cmd"></param>
/// <returns></returns>
public async static Task <dynamic> GetDynamicAsync(this BaDatabase db, DbCommand cmd)
{
dynamic result = null;
await db.ExecuteReaderAsync(cmd, async (row) =>
{
result = DbDataReaderToDynamic(row);
return(await Task.FromResult(false).ConfigureAwait(false));
}).ConfigureAwait(false);
return(result);
}
/// <summary>
/// Gets the first row as a dynamic object.
/// </summary>
/// <param name="db"></param>
/// <param name="cmd"></param>
/// <returns></returns>
public static dynamic GetDynamic(this BaDatabase db, DbCommand cmd)
{
dynamic result = null;
db.ExecuteReader(cmd, (row) =>
{
result = DbDataReaderToDynamic(row);
return(false);
});
return(result);
}
/// <summary>
/// Shows how to use BeginTransaction.
/// </summary>
public static void BeginTransaction()
{
using (var db = BaDatabase.Create("MyDatabase"))
using (var trans = db.BeginTransaction())
{
// Some complex database updates
// NOTE: Must call commit, but rollback is called automatically if you don't call
// Commit before the transaction is disposed.
trans.Commit();
}
}
/// <summary>
/// Returns the results of the SQL command as a list of dynamic objects.
/// </summary>
/// <param name="db"></param>
/// <param name="cmd"></param>
/// <returns></returns>
public async static Task <IEnumerable <dynamic> > GetDynamicsAsync(this BaDatabase db, DbCommand cmd)
{
var results = new List <dynamic>();
await db.ExecuteReaderAsync(cmd, async (row) =>
{
var result = DbDataReaderToDynamic(row);
results.Add(result);
return(await Task.FromResult(true).ConfigureAwait(false));
}).ConfigureAwait(false);
return(results);
}
/// <summary>
/// Returns the results of the SQL command as a list of dynamic objects.
/// </summary>
/// <param name="db"></param>
/// <param name="cmd"></param>
/// <returns></returns>
public static IEnumerable <dynamic> GetDynamics(this BaDatabase db, DbCommand cmd)
{
var results = new List <dynamic>();
db.ExecuteReader(cmd, (row) =>
{
var result = DbDataReaderToDynamic(row);
results.Add(result);
return(true);
});
return(results);
}
internal static DbCommand PrepareSprocCmd(this BaDatabase db, string sprocName, object parameters)
{
var cmd = db.Connection.CreateCommand();
cmd.CommandText = sprocName;
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
cmd.AddParameters(parameters);
}
return(cmd);
}
private static DataTable GetSchema()
{
if (sSchema == null)
{
// Use a short-lived database connection so that it doesn't get caught up in any transactions.
// Since sSchema is static, it should only need to happen once.
using (var db = BaDatabase.Create("MyDatabase"))
{
sSchema = db.GetSchema("Person");
}
}
return(sSchema);
}
internal static async Task <DbCommand> PrepareSprocCmdAsync(this BaDatabase db, string sprocName, object parameters)
{
var conn = await db.GetConnectionAsync().ConfigureAwait(false);
var cmd = conn.CreateCommand();
cmd.CommandText = sprocName;
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
cmd.AddParameters(parameters);
}
return(cmd);
}
static void Main(string[] args)
{
// Registering databases is optional. You can create a SqlServerDatabase directly from
// the connection string if you prefer.
var factory = new SqlServerDbFactory("Server=localhost\\SqlExpress;Database=BizArkTest;Trusted_Connection=Yes;");
BaDatabase.Register("MyDatabase", factory.Create);
//NOTE: If you want to run this project, the database is located in the BizArk.Data.Tests project (Data directory).
BaTableObjectExamples.CreatePerson();
//BaTableObjectExamples.UpdateBirthDate();
Console.WriteLine("Press any key to exit.");
Console.ReadKey(true);
}
/// <summary>
/// Shows how to use GetObjects to extract data.
/// </summary>
/// <remarks>
/// This is the recommended way to extract data into strongly typed objects.
/// </remarks>
public static void ExtractStronglyTypedData()
{
using (var db = BaDatabase.Create("MyDatabase"))
{
var cmd = new SqlCommand("SELECT * FROM Person");
// NOTE: GetObjects is an extension method from BizArk.Data.ExtractExt.
var people = db.GetObjects <Person>(cmd, rdr =>
{
return(new Person()
{
Id = rdr.GetInt("Id"),
FirstName = rdr.GetString("FirstName"),
LastName = rdr.GetString("LastName"),
BirthDate = rdr.GetDateTime("BirthDate")
});
});
}
}
/// <summary>
/// Shows how to use ExecuteReader to extract data.
/// </summary>
/// <remarks>
/// This is the most basic way to extract data. Typically you would use one of the other
/// extraction methods, such as GetObjects().
/// </remarks>
public static void ExtractDataUsingExecuteReader()
{
using (var db = BaDatabase.Create("MyDatabase"))
{
var cmd = new SqlCommand("SELECT * FROM Person");
var people = new List <Person>();
db.ExecuteReader(cmd, (rdr) =>
{
var p = new Person()
{
Id = rdr.GetInt32(0),
FirstName = rdr.GetString(1),
LastName = rdr.GetString(2),
BirthDate = rdr.GetDateTime(3)
};
people.Add(p);
return(true);
});
}
}
/// <summary>
/// Instantiates the object and sets properties based on the field name. Only returns the first row.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="cmd"></param>
/// <param name="load">A method that will create an object and fill it. If null, the object will be instantiated based on its type using the ClassFactory (must have a default ctor).</param>
/// <returns></returns>
public async static Task <T> GetObjectAsync <T>(this BaDatabase db, DbCommand cmd, Func <IDataReader, Task <T> > load = null) where T : class
{
T obj = null;
await db.ExecuteReaderAsync(cmd, async (row) =>
{
if (load != null)
{
obj = await load(row).ConfigureAwait(false);
return(false);
}
// Load doesn't have a value, so use the default loader.
obj = ClassFactory.CreateObject <T>();
var props = TypeDescriptor.GetProperties(typeof(T));
FillObject(row, obj, props);
return(false);
}).ConfigureAwait(false);
return(obj);
}
/// <summary>
/// Instantiates the object and sets properties based on the field name. Only returns the first row.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="cmd"></param>
/// <param name="load">A method that will create an object and fill it. If null, the object will be instantiated based on its type using the ClassFactory (must have a default ctor).</param>
/// <returns></returns>
public static T GetObject <T>(this BaDatabase db, DbCommand cmd, Func <IDataReader, T> load = null) where T : class
{
T obj = null;
db.ExecuteReader(cmd, (row) =>
{
if (load != null)
{
obj = load(row);
return(false);
}
// Load doesn't have a value, so use the default loader.
obj = ClassFactory.CreateObject <T>();
var props = TypeDescriptor.GetProperties(typeof(T));
FillObject(row, obj, props);
return(false);
});
return(obj);
}
/// <summary>
/// Shows how to use a strongly typed BaTableObject, including validation and saving it to
/// the database.
/// </summary>
public static void CreatePerson()
{
var p = new Person();
p.FirstName = "Bartlet";
p.LastName = "Simpson";
p.BirthDate = DateTime.Now.AddYears(-10);
// Validate the data before saving it.
var errs = p.Validate();
if (errs.Count() > 0)
{
Console.WriteLine("ERROR!!!");
foreach (var err in errs)
{
Console.WriteLine(err.ErrorMessage);
}
}
else
{
using (var db = BaDatabase.Create("MyDatabase"))
using (var trans = db.BeginTransaction())
{
// NOTE: Save is an extension method from BizArk.Data.SqlServer.BaTableExt.
db.Save(p);
// When Save is called on a new record, the object is updated with the values
// from the database after inserting. So the object will have the identity
// field (and any other defaults that are set on INSERT).
var p2 = db.Get <Person>(new { p.Id }).FirstOrDefault();
Console.WriteLine($"[{p2.Id}] {p2.FirstName} {p2.LastName}, born {p2.BirthDate:D}.");
trans.Commit();
}
}
}
public void RegisterDbFactoryTest()
{
Assert.ThrowsException <InvalidOperationException>(() =>
{
BaDatabase.Create("test");
});
BaDatabase.Register("test", () =>
{
return(new SqlServerDatabase("test"));
});
var db = BaDatabase.Create("test");
Assert.IsNotNull(db);
Assert.IsInstanceOfType(db, typeof(SqlServerDatabase));
BaDatabase.Unregister("test");
Assert.ThrowsException <ArgumentException>(() =>
{
BaDatabase.Create("test");
});
}
public void RegisterSqlServerDbFactoryTest()
{
var connStr1 = "db=test1";
var connStr2 = "db=test2";
var factory = new SqlServerDbFactory(connStr1);
BaDatabase.Register("test1", factory.Create);
factory = new SqlServerDbFactory(connStr2);
BaDatabase.Register("test2", factory.Create);
var db = BaDatabase.Create("test1") as SqlServerDatabase;
Assert.IsNotNull(db);
Assert.AreEqual(connStr1, db.ConnectionString);
db = BaDatabase.Create("test2") as SqlServerDatabase;
Assert.IsNotNull(db);
Assert.AreEqual(connStr2, db.ConnectionString);
BaDatabase.Unregister("test1");
BaDatabase.Unregister("test2");
}
/// <summary>
/// Executes the query, and returns the first column of the first row in the result
/// set returned by the query. Additional columns or rows are ignored.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="sprocName">Name of the stored procedure to call.</param>
/// <param name="parameters">An object that contains the properties to add as SQL parameters to the SQL command.</param>
/// <param name="dflt"></param>
/// <returns></returns>
public static T ExecuteScalar <T>(this BaDatabase db, string sprocName, object parameters = null, T dflt = default(T))
{
var cmd = db.PrepareSprocCmd(sprocName, parameters);
return(db.ExecuteScalar(cmd, dflt));
}
/// <summary>
/// Executes the query, and returns the first column of the first row in the result
/// set returned by the query. Additional columns or rows are ignored.
/// </summary>
/// <param name="db"></param>
/// <param name="sprocName">Name of the stored procedure to call.</param>
/// <param name="parameters">An object that contains the properties to add as SQL parameters to the SQL command.</param>
/// <param name="dflt"></param>
/// <returns></returns>
public static async Task <object> ExecuteScalarAsync(this BaDatabase db, string sprocName, object parameters = null, object dflt = null)
{
var cmd = db.PrepareSprocCmd(sprocName, parameters);
return(await db.ExecuteScalarAsync(cmd, dflt).ConfigureAwait(false));
}