// The dictionary key is built from a recordset field value ('@' prefix). // Dictionary<int, Person> GetPersonDictionary2() { using (DbManager db = new DbManager()) { return db .SetCommand("SELECT * FROM Person") ./*[a]*/ExecuteDictionary/*[/a]*/<int, Person>(/*[a]*/"@PersonID"/*[/a]*/); } }
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() { 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 Test() { using (DbManager db = new DbManager()) { List<Person> list = db .SetCommand(@" SELECT ps.*, d.Taxonomy, p.Diagnosis, CASE WHEN d.PersonID IS NOT NULL THEN 'D' WHEN p.PersonID IS NOT NULL THEN 'P' END as PersonType FROM Person ps LEFT JOIN Doctor d ON d.PersonID = ps.PersonID LEFT JOIN Patient p ON p.PersonID = ps.PersonID ORDER BY ps.PersonID") .ExecuteList<Person>(); Assert.AreEqual(list[0].GetType(), /*[a]*/typeof(Doctor)/*[/a]*/); Assert.AreEqual(list[1].GetType(), /*[a]*/typeof(Patient)/*[/a]*/); if (list.Count > 2) Assert.AreEqual(list[2].GetType(), typeof(Person)); } }
public void Test1() { List<Parent> parents = new List<Parent>(); MapResultSet[] sets = new MapResultSet[2]; sets[0] = new MapResultSet(typeof(Parent), parents); sets[1] = new MapResultSet(typeof(Child)); sets[0].AddRelation(sets[1], "ParentID", "ParentID", "Children"); sets[1].AddRelation(sets[0], "ParentID", "ParentID", "Parent"); using (DbManager db = new DbManager()) { db .SetCommand(_parentChildquery) .ExecuteResultSet(sets); } foreach (Parent p in parents) { Assert.That(p.IsDirty == false); foreach (Child c in p.Children) Assert.That(c.IsDirty == false); } }
public void DeleteByMemoID(int MemoID) { using (DbManager dbm = new DbManager()) { dbm.SetCommand(string.Format("DELETE FROM GENMEMODTL WHERE GENMEMOID = {0}", MemoID)).ExecuteNonQuery(); } }
public void Test() { using (DbManager db = new DbManager()) { List<SimpleObject> list = new List<SimpleObject>(); db #if MSSQL || SQLCE .SetCommand(@" SELECT 0 as [Key], 'value0' as Value UNION SELECT 1 as [Key], 'value1' as Value UNION SELECT 2 as [Key], 'value2' as Value") #else // ORACLE || FIREBIRD || ACCESS .SetCommand(@" SELECT 0 as ""Key"", 'value0' as ""Value"" FROM Dual UNION SELECT 1 as ""Key"", 'value1' as ""Value"" FROM Dual UNION SELECT 2 as ""Key"", 'value2' as ""Value"" FROM Dual") #endif .ExecuteList<SimpleObject>(list); Assert.IsTrue(list.Count > 0); } }
protected override void TearDown() { using (var dbManager = new DbManager("PerformanceTest")) dbManager .SetCommand("TRUNCATE TABLE Simplests") .ExecuteNonQuery(); }
public void Test() { const string path = @"..\..\..\..\Data\Create Scripts\SqlCe.sql"; using (DbManager db = new DbManager()) { string cmd = string.Empty; string term = "GO"; foreach (string s in File.ReadAllLines(path)) { string line = s.TrimEnd(); if (!line.EndsWith(term)) { cmd += line + Environment.NewLine; continue; } line = line.Substring(0, line.Length - term.Length).Trim(); Console.WriteLine("Executing script:"); Console.WriteLine(cmd + line); db .SetCommand(cmd + line) .ExecuteNonQuery() ; Console.WriteLine("Succeeded."); cmd = string.Empty; } } }
public void DeleteByMemoNo(string MemoNo) { using (DbManager dbm = new DbManager()) { dbm.SetCommand(string.Format("DELETE FROM PRICEPOINT WHERE MEMONO = '{0}'", MemoNo)).ExecuteNonQuery(); } }
// Select a person list. // public IList<Person> GetPersonList() { using (DbManager db = new DbManager()) { return db ./*[a]*/SetCommand/*[/a]*/("SELECT * FROM Person") .ExecuteList<Person>(); } }
IList<Person> GetPersonListSqlText() { using (DbManager db = new DbManager()) { return db .SetCommand("SELECT * FROM Person") ./*[a]*/ExecuteList<Person>()/*[/a]*/; } }
List<string> GetNameList2() { using (DbManager db = new DbManager()) { return db .SetCommand("SELECT * FROM Person") ./*[a]*/ExecuteScalarList/*[/a]*/<string>(/*[a]*/"FirstName"/*[/a]*/); } }
private void button4_Click(object sender, EventArgs e) { string sql = textBox1.Text.TrimEnd(); if (sql.Length == 0) return; ; DbManager mgr = new DbManager(); mgr.SetCommand(sql).ExecuteNonQuery(); MessageBox.Show("表创建完成"); }
Dictionary<int, string> GetNameDictionary() { using (DbManager db = new DbManager()) { return db .SetCommand("SELECT * FROM Person") ./*[a]*/ExecuteScalarDictionary/*[/a]*/<int, string>(/*[a]*/"PersonID"/*[/a]*/, /*[a]*/"FirstName"/*[/a]*/); } }
// Complex dictionary key. // Dictionary</*[a]*/CompoundValue/*[/a]*/, Person> GetPersonDictionary3() { using (DbManager db = new DbManager()) { return db .SetCommand("SELECT * FROM Person") ./*[a]*/ExecuteDictionary/*[/a]*/<Person>(new /*[a]*/MapIndex("FirstName", "LastName")/*[/a]*/); } }
public void Delete() { using (DbManager db = new DbManager()) { db .SetCommand("DELETE FROM Person WHERE PersonID = @id", db.Parameter("@id", _id)) .ExecuteNonQuery(); } }
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]*/; } }
Person GetPersonByID(int id) { using (DbManager db = new DbManager()) { return db .SetCommand("SELECT * FROM Person WHERE PersonID = @id", db.Parameter("@id", id)) .ExecuteObject<Person>(); } }
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>(); }
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 DateTime GetServerDate() { DateTime ServerDate = DateTime.Now; string sResult = string.Empty; using (DbManager db = new DbManager()) { ServerDate = Convert.ToDateTime(db.SetCommand("select getdate() as CurrentDate").ExecuteScalar()); } return ServerDate; }
// 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>(); } }
public void Test() { using (DbManager db = new DbManager()) { DataTable dt = db .SetCommand("SELECT * FROM Person") ./*[a]*/ExecuteDataTable/*[/a]*/(); Assert.AreNotEqual(0, dt.Rows.Count); } }
public void ExceptionTest() { using (DbManager db = new DbManager()) { db.OperationException += HandleOperationException; db .SetCommand("SELECT * FROM NoSuchTableEverExist") .ExecuteDataSet(); } }
public List<PatientHistory> FindAll() { 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") .ExecuteList<PatientHistory>(); } }
public void ScalarDictionaryTest3() { using (var db = new DbManager()) { var table = db .SetCommand("SELECT * FROM Person") .ExecuteScalarDictionary(0, typeof(int), 1, typeof(string)); Assert.IsNotNull(table); Assert.IsTrue(table.Count > 0); } }
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); } }