public void SetCommandWorksCorrectlyWithSprocParameterPrefixSet() { var oldPrefix = MySqlDataProvider.SprocParameterPrefix; MySqlDataProvider.SprocParameterPrefix = "_"; try { using (var db = new DbManager(ProviderName.MySql)) { var person = db.SetCommand( "SELECT * FROM Person WHERE PersonID = ?PersonID", db.Parameter("?PersonID", 1)) .ExecuteObject<Person>(); Assert.IsNotNull(person); Assert.AreEqual(1, person.ID); var person2 = db.SetCommand( "SELECT * FROM Person WHERE FirstName = ?firstName AND LastName = ?lastName", db.CreateParameters(person)) .ExecuteObject<Person>(); Assert.IsNotNull(person2); Assert.AreEqual(person, person2); } } finally { MySqlDataProvider.SprocParameterPrefix = oldPrefix; } }
public void Test() { const int testValue = 0; NullableInt ni = new NullableInt(); ni.I = testValue; ni.II = testValue; using (DbManager db = new DbManager()) { IDbDataParameter[] ps = db.CreateParameters(ni); foreach (IDbDataParameter p in ps) { switch (p.ParameterName) { case "@I": Assert.AreEqual(testValue, p.Value); break; case "@II": Assert.AreEqual(testValue, p.Value); break; } } } }
public void CompositeObjectTest() { using (DbManager db = new DbManager()) { IDbDataParameter[] prms = db.CreateParameters(new Entity()); Assert.AreEqual(3, prms.Length); } }
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(); } }
int InsertPerson(Person person) { using (DbManager db = new DbManager()) { return db .SetCommand(@" INSERT INTO Person ( LastName, FirstName, MiddleName, Gender) VALUES (@LastName, @FirstName, @MiddleName, @Gender) SELECT Cast(SCOPE_IDENTITY() as int)", db.CreateParameters(person)) .ExecuteScalar<int>(); } }
void UpdatePerson(Person person) { using (DbManager db = new DbManager()) { db .SetCommand(@" UPDATE Person SET LastName = @LastName, FirstName = @FirstName, MiddleName = @MiddleName, Gender = @Gender WHERE PersonID = @PersonID", db.CreateParameters(person)) .ExecuteNonQuery(); } }
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 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 CreateParametersTest() { IDbDataParameter[] parameters; var p = (Person)TypeAccessor.CreateInstance(typeof(Person)); p.ID = 12345; p.First.Name = "Crazy"; p.Last.Name = "Frog"; p.Name = "Froggy"; p.Type = typeof(DbManager); using (var db = new DbManager()) { parameters = db.CreateParameters(p); } Assert.IsNotNull(parameters); Assert.AreEqual(7, parameters.Length); foreach (var parameter in parameters) Console.WriteLine("{0}: {1}", parameter.ParameterName, parameter.Value); }
public void CreateParametersStructTest() { var dbInfo = new DBInfo { TimeValue = DateTime.Now }; using (var db = new DbManager()) { var parameters = db.CreateParameters(dbInfo); Assert.IsNotNull(parameters); Assert.AreEqual(1, parameters.Length); Assert.AreEqual(dbInfo.TimeValue, parameters[0].Value); } }
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 CreateParametersTest() { using (var db = new DbManager()) { var dt = new DataTypeTest { ID = 12345, Binary_ = new byte[2] {1, 2}, #if !ORACLE Boolean_ = true, Guid_ = Guid.Empty, #endif Byte_ = 250, Bytes_ = new byte[] { 2, 1 }, DateTime_ = DateTime.Now, Decimal_ = 9876543210.0m, Double_ = 12345.67890, Int16_ = 12345, Int32_ = 1234567890, Int64_ = 1234567890123456789, Money_ = 99876543210.0m, Single_ = 1234.0f, String_ = "Crazy Frog", Char_ = 'F', SByte_ = 123, //UInt16_ = 65432, //UInt32_ = 4000000000, //UInt64_ = 12345678901234567890, #if !SQLCE Stream_ = new MemoryStream(5), Xml_ = new XmlTextReader(new StringReader("<xml/>")), #endif }; var parameters = db.CreateParameters(dt); Assert.IsNotNull(parameters); Assert.AreEqual(ObjectMapper<DataTypeTest>.Instance.Count, parameters.Length); foreach (MemberMapper mm in ObjectMapper<DataTypeTest>.Instance) { var paramName = (string)db.DataProvider.Convert(mm.Name, db.GetConvertTypeToParameter()); var p = parameters.First(obj => obj.ParameterName == paramName); Assert.IsNotNull(p); Assert.AreEqual(mm.GetValue(dt), p.Value, mm.MemberName); } } }
public void CreateParametersSqlTest() { using (DbManager db = new DbManager()) { DataTypeSqlTest dt = new DataTypeSqlTest(); dt.ID = 12345; dt.Binary_ = new SqlBinary(new byte[2] {1, 2}); dt.Boolean_ = new SqlBoolean(1); dt.Byte_ = new SqlByte(250); dt.DateTime_ = new SqlDateTime(DateTime.Now); dt.Decimal_ = new SqlDecimal(9876543210.0m); dt.Double_ = new SqlDouble(12345.67890); dt.Guid_ = new SqlGuid(Guid.Empty); dt.Int16_ = new SqlInt16(12345); dt.Int32_ = new SqlInt32(1234567890); dt.Int64_ = new SqlInt64(1234567890123456789); dt.Money_ = new SqlMoney(99876543210.0m); dt.Single_ = new SqlSingle(1234.0f); dt.String_ = new SqlString("Crazy Frog"); #if !SQLCE dt.Bytes_ = new SqlBytes(new byte[2] {2, 1}); dt.Chars_ = new SqlChars(new char[2] {'B', 'L'}); dt.Xml_ = new SqlXml(new XmlTextReader(new StringReader("<xml/>"))); #endif var parameters = db.CreateParameters(dt); Assert.IsNotNull(parameters); Assert.AreEqual(ObjectMapper<DataTypeSqlTest>.Instance.Count, parameters.Length); foreach (MemberMapper mm in ObjectMapper<DataTypeSqlTest>.Instance) { var pName = (string)db.DataProvider.Convert(mm.Name, db.GetConvertTypeToParameter()); var p = Array.Find(parameters, obj => obj.ParameterName == pName); Assert.IsNotNull(p); Assert.AreEqual(mm.GetValue(dt), p.Value); } } }
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 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 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 Person UpdatePerson(DbManager db, Person person) { db ./*[a]*/SetSpCommand/*[/a]*/("Person_Update", db./*[a]*/CreateParameters/*[/a]*/(person)) .ExecuteNonQuery(); return GetPersonByID(db, person.ID); }
public void CreateParametersTest() { using (DbManager db = new DbManager()) { DataTypeTest dt = new DataTypeTest(); dt.ID = 12345; dt.Binary_ = new byte[2] {1, 2}; #if !ORACLE dt.Boolean_ = true; dt.Guid_ = Guid.Empty; #endif dt.Byte_ = 250; dt.Bytes_ = new byte[2] {2, 1}; dt.DateTime_ = DateTime.Now; dt.Decimal_ = 9876543210.0m; dt.Double_ = 12345.67890; dt.Int16_ = 12345; dt.Int32_ = 1234567890; dt.Int64_ = 1234567890123456789; dt.Money_ = 99876543210.0m; dt.Single_ = 1234.0f; dt.String_ = "Crazy Frog"; dt.Char_ = 'F'; dt.SByte_ = 123; dt.UInt16_ = 65432; dt.UInt32_ = 4000000000; dt.UInt64_ = 12345678901234567890; #if !SQLCE dt.Stream_ = new MemoryStream(5); dt.Xml_ = new XmlTextReader(new StringReader("<xml/>")); dt.XmlDoc_ = new XmlDocument(); dt.XmlDoc_.LoadXml("<xmldoc/>"); #endif IDbDataParameter[] parameters = db.CreateParameters(dt); Assert.IsNotNull(parameters); Assert.AreEqual(ObjectMapper<DataTypeTest>.Instance.Count, parameters.Length); foreach (MemberMapper mm in ObjectMapper<DataTypeTest>.Instance) { string paramName = (string)db.DataProvider.Convert(mm.Name, db.GetConvertTypeToParameter()); IDbDataParameter p = Array.Find(parameters, delegate(IDbDataParameter obj) { return obj.ParameterName == paramName; }); Assert.IsNotNull(p); Assert.AreEqual(mm.GetValue(dt), p.Value); } } }
public void CreateParametersTest() { Person person = new Person(); person.FirstName = "John"; person.LastName = "Smith"; person.Gender = Gender.Male; using (DbManager db = new DbManager()) { db.BeginTransaction(); // Prepare command. // int id = db .SetSpCommand("Person_Insert", db./*[a]*/CreateParameters/*[/a]*/(person)) .ExecuteScalar<int>(); // Check the result. // person = db .SetCommand( "SELECT * FROM Person WHERE PersonID = @id", db.Parameter("@id", id)) .ExecuteObject<Person>(); Assert.IsNotNull(person); // Cleanup. // db .SetCommand( "DELETE FROM Person WHERE PersonID = @id", db.Parameter("@id", id)) .ExecuteNonQuery(); db.CommitTransaction(); } }
public Person UpdatePerson(DbManager db, Person person) { db ./*[a]*/SetCommand/*[/a]*/(@" UPDATE Person SET LastName = @LastName, FirstName = @FirstName, Gender = @Gender WHERE PersonID = @PersonID", db./*[a]*/CreateParameters/*[/a]*/(person)) .ExecuteNonQuery(); return GetPersonByID(db, person.ID); }