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