public IDbDataParameter[] GetParameters(DbManager db, object obj) { var parameters = new IDbDataParameter[_parameters.Count]; for (var i = 0; i < _parameters.Count; i++) { var info = _parameters[i]; //parameters[i] = db.Parameter(info.ParameterName, info.MemberMapper.GetValue(obj)); var mmi = info.MemberMapper.MapMemberInfo; var val = info.MemberMapper.GetValue(obj); if (val == null && mmi.Nullable/* && mmi.NullValue == null*/) { //replace value with DbNull val = DBNull.Value; } if (mmi.IsDbTypeSet) { parameters[i] = mmi.IsDbSizeSet ? db.Parameter(info.ParameterName, val, info.MemberMapper.DbType, mmi.DbSize) : db.Parameter(info.ParameterName, val, info.MemberMapper.DbType); } else { parameters[i] = db.Parameter(info.ParameterName, val); } } return parameters; }
public static Common GetCommon(int roadId, double km) { using (DbManager db = new DbManager()) return db.SetCommand(@"SELECT TOP 1 r.road_titul as RoadName, r.Kroad, " + "o.org_name as ServiceName, o.org_phone as ServicePhone, o.road_pos_beg as ServiceKmBeg, o.road_pos_end as ServiceKmEnd, " + "g.org_name as GIBDDName, g.org_phone as GIBDDPhone, g.org_address as GIBDDAddress " + "FROM Sys_road r LEFT JOIN GP_org_road o ON (r.Kroad=o.Kroad AND o.road_pos_beg <= @km AND o.road_pos_end >= @km)" + "LEFT JOIN GP_gibdd_road g ON (r.Kroad=g.Kroad AND g.road_pos_beg <= @km AND @km <= g.road_pos_end) WHERE r.Kroad=@Kroad", db.Parameter("@Kroad", roadId), db.Parameter("@km", km)).ExecuteObject<Common>(); }
public void SetValueTest() { using (DbManager db = new DbManager()) { db.SetCommand("SELECT @par * 2", db./*[a]*/Parameter/*[/a]*/("@par", DbType.Int32)); db./*[a]*/Parameter("@par").Value/*[/a]*/ = 2; Assert.AreEqual(4, db.ExecuteScalar<int>()); } }
public void AssignParameterTest() { using (DbManager db = new DbManager()) { int n = db .SetCommand("SELECT @par1 + @par2", db./*[a]*/Parameter/*[/a]*/("@par1", 2), db./*[a]*/Parameter/*[/a]*/("@par2", 2)) .ExecuteScalar<int>(); Assert.AreEqual(4, n); } }
public Person CreatePerson(DbManager db) { int id = db ./*[a]*/SetCommand/*[/a]*/(@" INSERT INTO Person ( LastName, FirstName, Gender) VALUES (@LastName, @FirstName, @Gender) SELECT Cast(SCOPE_IDENTITY() as int) PersonID", db./*[a]*/Parameter/*[/a]*/("@LastName", "Frog"), db./*[a]*/Parameter/*[/a]*/("@FirstName", "Crazy"), db./*[a]*/Parameter/*[/a]*/("@Gender", Map.EnumToValue(Gender.Male))) .ExecuteScalar<int>(); return GetPersonByID(db, id); }
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); } }
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(); } }
// Insert, Update, and Delete a person. // public Person GetPersonByID(DbManager db, int id) { return db ./*[a]*/SetCommand/*[/a]*/("SELECT * FROM Person WHERE PersonID = @id", db./*[a]*/Parameter/*[/a]*/("@id", id)) .ExecuteObject<Person>(); }
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 Insert() { using (DbManager db = new DbManager()) { _id = db .SetCommand(@" INSERT INTO Person ( FirstName, LastName, Gender ) VALUES ( @FirstName, @LastName, @Gender ) SELECT Cast(SCOPE_IDENTITY() as int)", db.Parameter("@FirstName", "Crazy"), db.Parameter("@LastName", "Frog"), db.Parameter("@Gender", Map.EnumToValue(Gender.Unknown))) .ExecuteScalar<int>(); } }
private int Insert() { using (DbManager db = new DbManager()) { return db .SetCommand(@" INSERT INTO Person ( FirstName, LastName, Gender ) VALUES ( @FirstName, @LastName, @Gender ) SELECT Cast(SCOPE_IDENTITY() as int)", db.Parameter("@FirstName", "Crazy"), db.Parameter("@LastName", "Frog"), db.Parameter("@Gender", "U")) .ExecuteScalar<int>(); } }
Person GetPersonSqlText(int id) { using (DbManager db = new DbManager()) { return db .SetCommand("SELECT * FROM Person WHERE PersonID = @id", db.Parameter("@id", id)) ./*[a]*/ExecuteObject<Person>()/*[/a]*/; } }
public void Delete() { using (DbManager db = new DbManager()) { db .SetCommand("DELETE FROM Person WHERE PersonID = @id", db.Parameter("@id", _id)) .ExecuteNonQuery(); } }
Person GetPersonByID(int id) { using (DbManager db = new DbManager()) { return db .SetCommand("SELECT * FROM Person WHERE PersonID = @id", db.Parameter("@id", id)) .ExecuteObject<Person>(); } }
Person GetPersonSproc1(int id) { using (DbManager db = new DbManager()) { return db .SetSpCommand("Person_SelectByKey", db.Parameter("@id", id)) ./*[a]*/ExecuteObject<Person>()/*[/a]*/; } }
string GetFirstName(int id) { using (DbManager db = new DbManager()) { return db .SetCommand("SELECT FirstName FROM Person WHERE PersonID = @id", db.Parameter("@id", id)) ./*[a]*/ExecuteScalar/*[/a]*/<string>(); } }
public static DataTable GetData(string source, DateTime? from, int id) { var dataTable = new DataTable(); switch (source) { case "image": if (from == null) { if (id == 0) { var sql = "SELECT TOP 10 kimage, kcamera, load_time FROM gp_image order by load_time desc"; using (DbManager db = new DbManager()) return db.SetCommand(sql).ExecuteDataTable(); } else { var sql = "SELECT TOP 10 kimage, kcamera, load_time FROM gp_image where kcamera=@id order by load_time desc"; using (DbManager db = new DbManager()) return db.SetCommand(sql, db.Parameter("@id", id)).ExecuteDataTable(); } } else { if (id == 0) { var sql = "SELECT TOP 10 kimage, kcamera, load_time FROM gp_image where load_time <= @from order by load_time desc"; using (DbManager db = new DbManager()) return db.SetCommand(sql, db.Parameter("@from", from)).ExecuteDataTable(); } else { var sql = "SELECT TOP 10 kimage, kcamera, load_time FROM gp_image where load_time <= @from and kcamera=@id order by load_time desc"; using (DbManager db = new DbManager()) return db.SetCommand(sql, db.Parameter("@from", from), db.Parameter("@id", id)) .ExecuteDataTable(); } } } return dataTable; }
// BLToolkit data access method. // public List<Person> /*[a]*/GetList/*[/a]*/(Gender gender) { /*[a]*/using/*[/a]*/ (/*[a]*/DbManager/*[/a]*/ db = new DbManager(/*[a]*/"DemoConnection"/*[/a]*/)) { return db ./*[a]*/SetCommand/*[/a]*/( "SELECT * FROM Person WHERE Gender = @gender", db./*[a]*/Parameter/*[/a]*/("@gender", /*[a]*/Map.EnumToValue/*[/a]*/(gender))) ./*[a]*/ExecuteList/*[/a]*/<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 Person CreatePerson(DbManager db) { int id = db ./*[a]*/SetSpCommand/*[/a]*/("Person_Insert", db./*[a]*/Parameter/*[/a]*/("@LastName", "Frog"), db./*[a]*/Parameter/*[/a]*/("@MiddleName", null), db./*[a]*/Parameter/*[/a]*/("@FirstName", "Crazy"), db./*[a]*/Parameter/*[/a]*/("@Gender", Map.EnumToValue(Gender.Male))) .ExecuteScalar<int>(); return GetPersonByID(db, id); }
public void DirectTest() { using (DbManager db = new DbManager()) { string paramName = (string) db.DataProvider.Convert("name", ConvertType.NameToQueryParameter); string fieldName = (string) db.DataProvider.Convert("FirstName", ConvertType.NameToQueryField); db.SetCommand(string.Format("SELECT {0} = {1} FROM Person WHERE PersonID = 1", paramName, fieldName) , db.OutputParameter(paramName, DbType.String, 50)) .ExecuteNonQuery(); Assert.AreEqual("John", db.Parameter(paramName).Value); } }
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 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 PatientHistory FindById(int id) { using (var db = new DbManager()) { return db .SetCommand(@"select ph.*,p.Name PatientName,d.Name DoctorName from PatientHistory ph inner join Patient p on ph.PatientID = p.ID inner join Doctor d on ph.DoctorID = d.ID WHERE ph.ID = @id", db.Parameter("@id", id)) .ExecuteObject<PatientHistory>(); } }
public IDbDataParameter[] GetParameters(DbManager db, object[] key) { if (_parameters.Count != key.Length) throw new DataAccessException("Parameter list does match key list."); var parameters = new IDbDataParameter[_parameters.Count]; for (var i = 0; i < _parameters.Count; i++) { var info = _parameters[i]; parameters[i] = db.Parameter(info.ParameterName, key[i]); } return parameters; }
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 IDbDataParameter[] GetParameters(DbManager db, object obj) { var parameters = new IDbDataParameter[Parameters.Count]; for (var i = 0; i < Parameters.Count; i++) { var info = Parameters[i]; //parameters[i] = db.Parameter(info.ParameterName, info.MemberMapper.GetValue(obj)); var mmi = info.MemberMapper.MapMemberInfo; var val = info.MemberMapper.GetValue(obj); if (val == null && mmi.Nullable/* && mmi.NullValue == null*/) { //replace value with DbNull val = DBNull.Value; } if (mmi.IsDbTypeSet) { parameters[i] = mmi.IsDbSizeSet ? db.Parameter(info.ParameterName, val, info.MemberMapper.DbType, mmi.DbSize) : db.Parameter(info.ParameterName, val, info.MemberMapper.DbType); } else { parameters[i] = val != DBNull.Value ? db.Parameter(info.ParameterName, val) : db.Parameter(info.ParameterName, val, info.MemberMapper.GetDbType()); } if (mmi.KeyGenerator is SequenceKeyGenerator && ActionName == "InsertWithIdentity") { parameters[i] = db.OutputParameter(info.ParameterName, val); } } return parameters; }
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 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(); } }
public void ExecuteObject2Sql() { using (DbManager db = new DbManager()) { DataTypeSqlTest dt = (DataTypeSqlTest)db .SetCommand("SELECT * FROM DataTypeTest WHERE DataTypeID = " + db.DataProvider.Convert("id", ConvertType.NameToQueryParameter), db.Parameter("id", 2)) .ExecuteObject(typeof(DataTypeSqlTest)); TypeAccessor.WriteConsole(dt); } }