public void SprocParameterPrefixShouldBeSpecifiedManuallyWhenItIsNotSet() { using (var db = new DbManager(ProviderName.MySql)) { var person = db.SetSpCommand("GetPersonById", db.Parameter("?_ID", 1)).ExecuteObject<Person>(); Assert.IsNotNull(person); var person2 = db.SetSpCommand( "GetPersonByName", db.Parameter("?_lastName", person.LastName), db.Parameter("?_firstName", person.FirstName)) .ExecuteObject<Person>(); Assert.IsNotNull(person2); } }
IList<Person> GetPersonListSproc() { using (DbManager db = new DbManager()) { return db .SetSpCommand("Person_SelectAll") ./*[a]*/ExecuteList<Person>()/*[/a]*/; } }
void GetCustomPersonList(IList list) { using (DbManager db = new DbManager()) { db .SetSpCommand("Person_SelectAll") ./*[a]*/ExecuteList(list, typeof(Person))/*[/a]*/; } }
public List<LogEntity> GetByUserId(int userId) { using (var db = new DbManager()) { return db .SetSpCommand("GetLogsByUserId", userId) .ExecuteList<LogEntity>(); } }
public LogEntity GetById(int id) { using (var db = new DbManager()) { return db .SetSpCommand("GetLogById", id) .ExecuteObject<LogEntity>(); } }
public List<LogEntity> GetAll() { using (var db = new DbManager()) { return db .SetSpCommand("GetAllLogs") .ExecuteList<LogEntity>(); } }
// Select a person list. // public IList<Person> GetPersonList() { using (DbManager db = new DbManager()) { return db ./*[a]*/SetSpCommand/*[/a]*/("Person_SelectAll") .ExecuteList<Person>(); } }
Person GetPersonSproc2(int id) { using (DbManager db = new DbManager()) { return db .SetSpCommand("Person_SelectByKey", id) ./*[a]*/ExecuteObject<Person>()/*[/a]*/; } }
public void ReturnValueTest2() { using (DbManager db = new DbManager()) { int n = db .SetSpCommand("Scalar_ReturnParameter") .ExecuteScalar<int>(ScalarSourceType.ReturnValue); Assert.AreEqual(12345, n); } }
public Person GetPersonByID2(int id) { using (DbManager db = new DbManager()) { // Pass a parameter using the [b]params[/b] parameter of the SetSpCommand method. // return db ./*[a]*/SetSpCommand/*[/a]*/("Person_SelectByKey", /*[a]*/id/*[/a]*/) .ExecuteObject<Person>(); } }
// Select a person. // public Person GetPersonByID1(int id) { using (DbManager db = new DbManager()) { // Pass a parameter using the [b]Parameter[/b] method. // return db ./*[a]*/SetSpCommand/*[/a]*/("Person_SelectByKey", db./*[a]*/Parameter/*[/a]*/("@id", id)) .ExecuteObject<Person>(); } }
public void ParameterPrefixTest() { var oldPrefix = MySqlDataProvider.SprocParameterPrefix; MySqlDataProvider.SprocParameterPrefix = "_"; try { using (var db = new DbManager(ProviderName.MySql)) { var person = db.SetSpCommand("GetPersonById", db.Parameter("?ID", 1)).ExecuteObject<Person>(); Assert.IsNotNull(person); var person2 = db.SetSpCommand("GetPersonByName", db.CreateParameters(person)).ExecuteObject<Person>(); Assert.IsNotNull(person2); Assert.AreEqual(person, person2); } } finally { MySqlDataProvider.SprocParameterPrefix = oldPrefix; } }
public void Test() { List<Person> list = new List<Person> { new Person { FirstName = "John", LastName = "Smith", Gender = Gender.Male }, new Person { FirstName = "Jane", LastName = "Smith", Gender = Gender.Female } }; using (DbManager db = new DbManager()) { db.BeginTransaction(); // Prepare command. // db .SetSpCommand("Person_Insert", db.CreateParameters(list[0])) ./*[a]*/Prepare/*[/a]*/(); // Insert. // foreach (Person person in list) { db./*[a]*/AssignParameterValues/*[/a]*/(person); db.ExecuteNonQuery(); } // Check the result. // list = db .SetCommand( "SELECT * FROM Person WHERE LastName = @lastName", db.Parameter("@lastName", "Smith")) .ExecuteList<Person>(); Assert.GreaterOrEqual(2, list.Count); // Cleanup. // db .SetCommand( "DELETE FROM Person WHERE LastName = @lastName", db.Parameter("@lastName", "Smith")) .ExecuteNonQuery(); db.CommitTransaction(); } }
static void Main(string[] args) { using (var db = new DbManager()) { db.SetSpCommand(""); // ok db.SetInsertCommand(""); // ok db.SetUpdateCommand(""); // ok db.Prepare(); // ok db.ExecuteNonQuery(); // ok db.SetCommand(""); // ERROR: // error CS0584: Internal compiler error: Object reference not set to an instance of an object // error CS0201: Only assignment, call, increment, decrement, and new object expressions can be used as a statement } }
public void ReturnValueTest() { using (DbManager db = new DbManager()) { /* * CREATE Function Scalar_ReturnParameter() * RETURNS int * AS * BEGIN * RETURN 12345 * END */ int n = db .SetSpCommand("Scalar_ReturnParameter") ./*[a]*/ExecuteScalar/*[/a]*/<int>(/*[a]*/ScalarSourceType.ReturnValue/*[/a]*/); Assert.AreEqual(12345, n); } }
public void TestFailResultSet1() { MapResultSet[] sets = new MapResultSet[2]; sets[0] = new MapResultSet(typeof(Master)); sets[1] = new MapResultSet(typeof(Slave)); sets[0].AddRelation(sets[1], "MasterID", "ID", "Slaves"); using (DbManager db = new DbManager()) { db #if ORACLE .SetSpCommand("ResultSetTest") #else .SetCommand(SqlResultSet) #endif .ExecuteResultSet(sets); } }
public void OutputParameterAsReturnValueTest() { using (DbManager db = new DbManager()) { /* * CREATE Procedure Scalar_OutputParameter * @outputInt int = 0 output, * @outputString varchar(50) = '' output * AS * BEGIN * SET @outputInt = 12345 * SET @outputString = '54321' * END */ string returnValue = db .SetSpCommand("Scalar_OutputParameter") ./*[a]*/ExecuteScalar/*[/a]*/<string>(/*[a]*/ScalarSourceType.OutputParameter/*[/a]*/, /*[a]*/"outputString"/*[/a]*/); Assert.AreEqual("54321", returnValue); } }
public void ReturnValueTest() { using (DbManager db = new DbManager()) { /* * CREATE Function Scalar_ReturnParameter() * RETURNS int * AS * BEGIN * RETURN 12345 * END */ db .SetSpCommand("Scalar_ReturnParameter") .ExecuteNonQuery(); int n = (int)db./*[a]*/Parameter("@RETURN_VALUE").Value/*[/a]*/; Assert.AreEqual(12345, n); } }
public void TestResultSet() { MapResultSet[] sets = new MapResultSet[2]; sets[0] = new MapResultSet(typeof(Master)); sets[1] = new MapResultSet(typeof(Slave)); sets[0].AddRelation(sets[1], "MasterID", "MasterID", "Slaves"); using (DbManager db = new DbManager()) { db #if ORACLE .SetSpCommand("ResultSetTest") #else .SetCommand(SqlResultSet) #endif .ExecuteResultSet(sets); } Assert.AreEqual(7, ((Slave)(((Master)sets[0].List[0]).Slaves[1])).ID); }
public void Test() { List<Person> list = new List<Person> { new Person { FirstName = "John", LastName = "Smith", Gender = Gender.Male }, new Person { FirstName = "Jane", LastName = "Smith", Gender = Gender.Female } }; using (DbManager db = new DbManager()) { db.BeginTransaction(); // Execute. // db .SetSpCommand("Person_Insert") ./*[a]*/ExecuteForEach/*[/a]*/<Person>(list); // Check the result. // list = db .SetCommand( "SELECT * FROM Person WHERE LastName = @lastName", db.Parameter("@lastName", "Smith")) .ExecuteList<Person>(); Assert.GreaterOrEqual(2, list.Count); // Cleanup. // db .SetCommand( "DELETE FROM Person WHERE LastName = @lastName", db.Parameter("@lastName", "Smith")) .ExecuteNonQuery(); db.CommitTransaction(); } }
public void Test() { Person person = new Person(); person.FirstName = "John"; person.LastName = "Smith"; person.Gender = Gender.Male; using (DbManager db = new DbManager()) { db.BeginTransaction(); // Execute. // db .SetSpCommand("Person_Insert", db.CreateParameters(person)) ./*[a]*/ExecuteNonQuery/*[/a]*/(); // Check the result. // person = db .SetCommand( "SELECT * FROM Person WHERE LastName = @lastName", db.Parameter("@lastName", "Smith")) .ExecuteObject<Person>(); Assert.IsNotNull(person); // Cleanup. // db .SetCommand( "DELETE FROM Person WHERE LastName = @lastName", db.Parameter("@lastName", "Smith")) ./*[a]*/ExecuteNonQuery/*[/a]*/(); db.CommitTransaction(); } }
public void ParameterSymbolMayBeOmitedForSpCommand() { // I am not sure this is a good thing though // Maybe we need to be more strict on syntax and on the library users // and do not allow different syntax (omiting parameter symbol)? using (var db = new DbManager(ProviderName.MySql)) { var person = db.SetSpCommand("GetPersonById", db.Parameter("_ID", 1)).ExecuteObject<Person>(); Assert.IsNotNull(person); var person2 = db.SetSpCommand( "GetPersonByName", db.Parameter("_lastName", person.LastName), db.Parameter("_firstName", person.FirstName)) .ExecuteObject<Person>(); Assert.IsNotNull(person2); Assert.AreEqual(person, person2); var oldPrefix = MySqlDataProvider.SprocParameterPrefix; MySqlDataProvider.SprocParameterPrefix = "_"; try { var person3 = db.SetSpCommand("GetPersonById", db.Parameter("ID", 1)).ExecuteObject<Person>(); Assert.AreEqual(person, person3); } finally { MySqlDataProvider.SprocParameterPrefix = oldPrefix; } } }
public void CreateManyParametersTest() { FirstPart f = new FirstPart(); SecondPart s = new SecondPart(); f.FirstName = "John"; s.LastName = "Pupkin"; using (DbManager db = new DbManager()) { Person p = (Person)db .SetSpCommand ("Person_SelectByName", db.CreateParameters(f), db.CreateParameters(s)) .ExecuteObject(typeof(Person)); Assert.IsNotNull(p); Assert.AreEqual(f.FirstName, p.FirstName); Assert.AreEqual(s.LastName, p.LastName); } }
public void MapDataRow() { DataTable dataTable = new DataTable(); dataTable.Columns.Add("ID", typeof(int)); dataTable.Columns.Add("outputID", typeof(int)); dataTable.Columns.Add("inputOutputID", typeof(int)); dataTable.Columns.Add("str", typeof(string)); dataTable.Columns.Add("outputStr", typeof(string)); dataTable.Columns.Add("inputOutputStr", typeof(string)); DataRow dataRow = dataTable.Rows.Add(new object[]{5, 0, 10, "5", null, "10"}); using (DbManager db = new DbManager()) { db .SetSpCommand("OutRefTest", db.CreateParameters(dataRow, new string[] { "outputID", "outputStr" }, new string[] { "inputOutputID", "inputOutputStr" }, null)) .ExecuteNonQuery(dataRow); } Assert.AreEqual(5, dataRow["outputID"]); Assert.AreEqual(15, dataRow["inputOutputID"]); Assert.AreEqual("5", dataRow["outputStr"]); Assert.AreEqual("510", dataRow["inputOutputStr"]); }
public void InsertAndMapBack() { Person e = new Person(); e.FirstName = "Crazy"; e.LastName = "Frog"; e.Gender = Gender.Other; using (DbManager db = new DbManager()) { db .SetSpCommand("Person_Insert", db.CreateParameters(e, new string[] { "PersonID" }, null, null)) .ExecuteObject(e); Assert.IsTrue(e.ID > 0); // Cleanup. // db .SetSpCommand("Person_Delete", db.CreateParameters(e)) .ExecuteNonQuery(); } }
public void MapReturnValue() { ReturnParameter e = new ReturnParameter(); using (DbManager db = new DbManager()) { db .SetSpCommand("Scalar_ReturnParameter") .ExecuteNonQuery("Value", e); } Assert.AreEqual(12345, e.Value); }
public void MapOutput() { OutRefTest o = new OutRefTest(); using (DbManager db = new DbManager()) { db .SetSpCommand("OutRefTest", db.CreateParameters(o, new string[] { "outputID", "outputStr" }, new string[] { "inputOutputID", "inputOutputStr" }, null)) .ExecuteNonQuery(o); } Assert.AreEqual(5, o.outputID); Assert.AreEqual(15, o.inputOutputID); Assert.AreEqual("5", o.outputStr); Assert.AreEqual("510", o.inputOutputStr); }
public void NativeConnection() { string connectionString = DbManager.GetConnectionString(null); using (DbManager db = new DbManager(new SqlConnection(connectionString))) { db .SetSpCommand ("Person_SelectByName", db.Parameter("@firstName", "John"), db.Parameter("@lastName", "Pupkin")) .ExecuteScalar(); } }
private string GenerateAccountCode() { string sResult = string.Empty; using (DbManager db = new DbManager()) { db.SetSpCommand("Get_AccountSequence", db.Parameter("@GUID_NUMBER", "TEST")).ExecuteNonQuery(); long lData = Convert.ToInt32(db.Parameter("@RETURN_VALUE").Value); sResult = string.Format("ACCT-{0:00000}", lData); } return sResult; }
public void PrefixIsAddedWhenRetrievingParameterFromDbManager() { var oldPrefix = MySqlDataProvider.SprocParameterPrefix; MySqlDataProvider.SprocParameterPrefix = "_"; try { using (var db = new DbManager(ProviderName.MySql)) { db.SetSpCommand("GetPersonById", db.Parameter("?ID", 1)).Prepare(); foreach (var personID in new[] { 1, 2 }) { // prefix is not specified but it will be added internally before retrieving parameter from // command parameters db.Parameter("?ID").Value = personID; var person = db.ExecuteObject<Person>(); Assert.IsNotNull(person); Assert.AreEqual(personID, person.ID); // specifying prefix is also ok db.Parameter("?_ID").Value = personID; person = db.ExecuteObject<Person>(); Assert.IsNotNull(person); Assert.AreEqual(personID, person.ID); } } } finally { MySqlDataProvider.SprocParameterPrefix = oldPrefix; } }