private void Form1_Load(object sender, EventArgs e) { var db = new PetaPoco.Database("tencentcloud"); //To query a scalar foreach(var a in db.Query<article>("select * from articles")) { listBox1.Items.Add(string.Format("{0}-{1}", a.article_id, a.title)); } listBox1.Items.Add("\r\n"); long count = db.ExecuteScalar<long>("select Count(*) from articles"); listBox1.Items.Add(string.Format("count: {0}",count )); listBox1.Items.Add("\r\n"); //@0 代表占位符 SingleOrDefault var abc = db.SingleOrDefault<article>("select * from articles where article_id=@0",1); listBox1.Items.Add(abc); listBox1.Items.Add("\r\n"); //Paged Fetches 分页 var result = db.Page<article>(1, 3, "select * from articles where draft=1 order by date_created "); foreach (var temp in result.Items) { listBox1.Items.Add(string.Format("title: {0}", temp.title)); } listBox1.Items.Add("\r\n"); listBox1.Items.Add("结束"); }
private static void Crud() { var petaPoco = new PetaPoco.Database("Chinook"); var customer = new Customer { FirstName = "Ian", LastName = "Russell", Email = "*****@*****.**" }; petaPoco.Insert(customer); var id = customer.CustomerId; customer = petaPoco.Single<Customer>(id); ObjectDumper.Write(customer); customer.Country = "United Kingdom"; petaPoco.Update(customer); customer = petaPoco.Single<Customer>(id); ObjectDumper.Write(customer); petaPoco.Delete<Customer>(id); customer = petaPoco.SingleOrDefault<Customer>(id); ObjectDumper.Write(customer); }
public IHttpActionResult Post([FromUri] string id, [FromBody]System.Collections.Generic.List<Entities.Contact> contactlist) { if (contactlist != null) { var db = new PetaPoco.Database("AGSoftware"); List<Entities.Contact> newcontactlist = new List<Entities.Contact>(); foreach (Entities.Contact contact in contactlist) { var iscontact = db.SingleOrDefault<Entities.AspNetUsers>("Select * From ASPNetUsers Where PhoneNumber = @0", contact.PhoneNumber); if (iscontact != null) { newcontactlist.Add(new Entities.Contact(iscontact.Id, iscontact.PhoneNumber, contact.FirstName, contact.LastName)); } } if (newcontactlist.Count > 0) return Ok(newcontactlist); else return NotFound(); } else { return BadRequest("Contact List was empty"); } }
public IHttpActionResult Get(string id) { var db = new PetaPoco.Database("AGSoftware"); var db2 = new PetaPoco.Database("AGSoftware"); System.Collections.Generic.List<Entities.StorytimePost> storytimepostlist = new List<Entities.StorytimePost>(); string UserId = ""; foreach (Entities.StorytimePost c in db.Query<Entities.StorytimePost>("Select * From StorytimePost Where SeriesId = @0 Order By DateCreated Desc", id)) { c.ImagePath = Providers.ImageHelper.GetImagePath(c.ImagePath); c.ImagePath = c.ImagePath.Replace(@"\", @"/"); UserId = Providers.UserHelper.GetUserId(this.User.Identity.Name); var voted = db2.SingleOrDefault<Entities.Vote>("Select * From Vote Where StorytimePostId = @0 And UserId = @1", new object []{c.StorytimePostId, UserId}); if (voted != null) c.Voted = true; else c.Voted = false; if (c.UserId == UserId) c.UserPostedImage = true; c.PhoneNumber = Providers.UserHelper.GetPhoneNumberById(c.UserId); storytimepostlist.Add(c); } if (storytimepostlist.Count > 0) return Ok(storytimepostlist); else return NotFound(); }
public JsonResult Delete(IList<Route> data) { bool success = false; string message = "Delete method failed"; if (data != null) { using (var db = new PetaPoco.Database("MissNancy")) { foreach (var item in data) { item.EditDate = DateTime.Now; item.Active = false; db.Save("tblRoutes", "RouteKey", item); } success = true; message = "Route(s) deleted successfully"; } } return Json(new { success, message }); }
/// <summary> /// /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> public void dataBind(int pageIndex = 1, int pageSize = 10, string startTime = null, string endTime = null) { PetaPoco.Sql sql = new PetaPoco.Sql(); sql.Select("m.[description] as name,a.*").From("t_MachineStop_rd a"); sql.LeftJoin("Point_Machine_Map m").On("m.pointname=a.pointname"); if (!String.IsNullOrEmpty(startTime)) { sql.Where("a.starttime>@0", startTime); } if (!String.IsNullOrEmpty(endTime)) { sql.Where("a.starttime<@0", endTime); } List<int> machineids = new List<int>(); for (int i = 0; i < chkboxlist.Items.Count; i++) { if (chkboxlist.Items[i].Selected) { machineids.Add(int.Parse(chkboxlist.Items[i].Value)); } } if (machineids.Count > 1) sql.Where("m.machineid in (@0)", machineids); else if (machineids.Count == 1) sql.Where("m.machineid=@0", machineids[0]); sql.OrderBy("a.starttime desc"); var db = new PetaPoco.Database("dbconn"); PetaPoco.Page<BootRecordSelect> pageitems = db.Page<BootRecordSelect>(pageIndex, pageSize, sql); rpt_RulelogS_Des.DataSource = pageitems.Items; rpt_RulelogS_Des.DataBind(); AspNetPager1.RecordCount = (int)pageitems.TotalItems; }
// // GET: /Classes/Details/5 public ActionResult Details(int id) { var db = new PetaPoco.Database("MissNancy"); var myClass = db.SingleOrDefault<Classes>("SELECT * FROM tblClasses WHERE ClassKey = @0", id); return View(myClass); }
public void TestMethodTable() { var db = new PetaPoco.Database(connectionString, providerName); var keyObjectStore = new SqlKeyObjectStore(db); using (var myTable = keyObjectStore.Table<string>("myTable")) { myTable.Empty(); var firstId = myTable.Insert("foo"); var secondId = myTable.Insert("bar"); var thirdId = myTable.Insert("THREE"); var second = myTable.Select(secondId); Assert.AreEqual("bar", second); myTable.Delete(secondId); var secondIsDeleted = myTable.Select(secondId); Assert.IsNull(secondIsDeleted); var all = myTable.All(); Assert.AreEqual(2, all.Count()); } }
public IHttpActionResult Post() { var file = HttpContext.Current.Request.Files[0]; if (file != null) { string filename = Guid.NewGuid().ToString() + file.FileName; string pic = System.IO.Path.GetFileName(filename); string path = System.IO.Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Content/Upload"), pic); // file is uploaded file.SaveAs(path); var db = new PetaPoco.Database("AGSoftware"); Entities.StorytimePost storytimepost = new Entities.StorytimePost(); storytimepost.DateCreated = System.DateTime.Now; storytimepost.ImagePath = filename; storytimepost.PostText = HttpContext.Current.Request.Form["posttext"]; storytimepost.UserId = Storytime.Providers.UserHelper.GetUserId(HttpContext.Current.User.Identity.Name); storytimepost.StorytimeId = int.Parse(HttpContext.Current.Request.Form["storytimeid"]); storytimepost.SeriesId = int.Parse(HttpContext.Current.Request.Form["SeriesId"]); db.Insert(storytimepost); return Ok(storytimepost.StorytimePostId); } else { return BadRequest("File upload missing."); } }
public void InitDataBase(string connectionString) { var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient"); db.Execute(Const.DBCreateScript); foreach (var province in ProvinceData.GetProvinces()) { db.Insert("Province", "Id", new{Name = province.Name, Code = province.Code}); } var provinces = db.Query<dynamic>(@"SELECT * from Province").ToList(); BulkUploadToSql bulk = BulkUploadToSql.Load( HomeData.GetHomes() .Select( i => new Bulk.Home { AddTime = DateTime.Now, BuildYear = i.BuildYear, City = i.City, Description = i.Description, Price = i.Price, Surface = i.Surface, ProvinceId = provinces.First(j => j.Code == i.HomeProvince.Code).Id, }), "Home", 10000, connectionString); bulk.Flush(); }
public void TestMethodNestedOwners() { var db = new PetaPoco.Database(connectionString, providerName); var keyObjectStore = new SqlKeyObjectStore(db,keyObjectTableName:"TableWithIndexes"); var ownerStore1 = keyObjectStore.GetStore("owner1"); var ownerStore11 = ownerStore1.GetStore("owner11"); var ownerStore12 = ownerStore1.GetStore("owner1"); // this owner1 is actually owner1.owner1 var defaultStoreTable = ownerStore1.Table<string>("table"); var owner1Table = ownerStore11.Table<string>("table"); var owner2Table = ownerStore12.Table<string>("table"); defaultStoreTable.Empty(); owner1Table.Empty(); owner2Table.Empty(); owner1Table.Insert("Foo"); var getFromDefaultStore = defaultStoreTable.All(); var getFromOwner1 = owner1Table.All(); var getFromOwner2 = owner2Table.All(); Assert.AreEqual(0, getFromDefaultStore.Count()); Assert.AreEqual(1, getFromOwner1.Count()); Assert.AreEqual(0, getFromOwner2.Count()); }
public static List<Models.Category> GetAllCategory() { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { return db.Query<Models.Category>("").ToList(); } }
public JsonResult Create(List<Bus> data) { bool success = false; string message = "Create method failed"; if (data != null) { using (var db = new PetaPoco.Database("MissNancy")) { foreach (var item in data) { item.CreateDate = DateTime.Now; item.EditDate = DateTime.Now; db.Save("tblBuses", "BusKey", item); } success = true; message = "Jesus Saves and so did this method"; } } return Json(new { data, success, message }); }
/// <summary> /// 删除掉帖子的所有标签 /// </summary> /// <param name="fid"></param> public static void DeleteTarg(int fid) { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { db.Execute("DELETE FROM jexus_tags_relation WHERE fid=@0", fid); } }
public static User GetUserByOpenID(string openid) { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { return db.SingleOrDefault<Models.User>("WHERE openid=@0 ", openid); } }
/// <summary> /// 根据登录名 /// </summary> /// <param name="userName"></param> /// <returns></returns> public static User GetUser(string userName) { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { return db.SingleOrDefault<Models.User>("WHERE username=@0 ", userName); } }
public static void UpdateUser(Models.User u) { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { db.Save(u); } }
public static Models.UserGroup GetUserGroup(int id) { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { return db.SingleOrDefault<Models.UserGroup>("WHERE gid=@0 ", id); } }
/// <summary> /// 获取所有的 /// </summary> /// <returns></returns> public static List<Settings> GetAllSetting() { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { return db.Query<Models.Settings>("").ToList(); } }
public static List<Models.Page> GetAllPages() { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { return db.Query<Models.Page>("").ToList(); } }
public static List<string> GetAllTag() { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { return db.Query<string>("SELECT tag_title FROM jexus_tags ORDER BY tag_title DESC").ToList(); } }
private void SelectAll() { // Create a PetaPoco database object var db = new PetaPoco.Database("sqlite"); string query = "SELECT * FROM foo"; StringBuilder sb = new StringBuilder(); sb.AppendLine(query); sb.AppendLine("--------------"); try { // Show all foo foreach (var a in db.Query<foo>(query)) { sb.AppendLine(string.Format("{0} - {1}", a.Id, a.name)); } } catch (Exception ex) { sb.AppendLine(ex.Message); sb.Append(ex.StackTrace); } this.richTextBox1.Text = sb.ToString(); }
/// <summary> /// 获取配置 /// </summary> /// <param name="title"></param> /// <returns></returns> public static Settings GetSetting(string title) { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { return db.SingleOrDefault<Models.Settings>("WHERE title=@0", title); } }
private void button3_Click(object sender, EventArgs e) { try { // Create a PetaPoco database object var db = new PetaPoco.Database("sqlite"); // find the (presumably) most recently created foo int id = db.ExecuteScalar<int>("SELECT max(id) from foo"); // Get a record var foo = db.SingleOrDefault<foo>("SELECT * FROM foo WHERE Id=@0", id); // Change it foo.name = "PetaPoco changed your name!"; // Save it db.Update("foo", "Id", foo); } catch (Exception ex) { Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace); } this.fooQuery1.Refresh(); }
public IEnumerable<HoiVien> GetCustomers() { var db = new PetaPoco.Database("MyConnection"); IEnumerable<HoiVien> kq; if (TempData["dieukienloc"] == null) { //PetaPoco.Page<HoiVien> rs = MyConnectionDB.GetInstance().Page<HoiVien>(1, 2, "select * from HoiVien"); kq = db.Query<HoiVien>("select * from HoiVien where Disable = '0'"); } else { TempData.Keep("dieukienloc"); string[] frmc = (string[])TempData["dieukienloc"]; //Thiet lap cau truy van dua tren dieu kien loc string qr = "select * from HoiVien where 1=1"; if (frmc[0] != "") { qr = qr + string.Format(" and DivisionID like N'%{0}%'", frmc[0]); } if (frmc[1] != "") { qr = qr + string.Format(" and Address like N'%{0}%'", frmc[1]); } if (frmc[2] != "") { qr = qr + string.Format(" and Phone like N'%{0}%'", frmc[2]); } if (frmc[3] != "") { qr = qr + string.Format(" and MemberID like N'%{0}%'", frmc[3]); } if (frmc[4] != "") { qr = qr + string.Format(" and Identify like N'%{0}%'", frmc[4]); } if (frmc[5] != "") { qr = qr + string.Format(" and Fax like N'%{0}%'", frmc[5]); } if (frmc[6] != "") { qr = qr + string.Format(" and MemberName like N'%{0}%'", frmc[6]); } if (frmc[7] != "") { qr = qr + string.Format(" and Tel like N'%{0}%'", frmc[7]); } if (frmc[8] != "") { qr = qr + string.Format(" and Email like N'%{0}%'", frmc[8]); } if (frmc[9] == "false") { qr = qr + string.Format(" and Disable = '0'"); } kq = db.Query<HoiVien>(qr); } return kq; }
public static void DeleteForum(int id) { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { db.Delete<Models.Forums>("WHERE fid=@0", id); } }
private static Entities.AspNetUsers GetUserById(string id) { var db = new PetaPoco.Database("AGSoftware"); var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", id); return a; }
public void TestMethodSetGetObject() { var db = new PetaPoco.Database(connectionString, providerName); var x = new SqlKeyObjectStore(db); x.Store("foo", "foo"); var s = x.Get<string>("foo"); Assert.AreEqual("foo", s); }
private static Entities.AspNetUsers GetUser(string username) { var db = new PetaPoco.Database("AGSoftware"); var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where UserName = @0", username); return a; }
public static int AddForum(Models.Forums form) { using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection")) { int id = Convert.ToInt32(db.Insert(form)); return id; } }
/// <summary> /// This method is used to get Closing balance of annual period of that user. /// </summary> /// <param name="sConnectionString"></param> /// <returns></returns> public double GetClosingBalanceOfAnnualPeriod(string sConnectionString, int nUserInfoId, int nUserInfoIdRelative, int nSecurityTypeCodeId) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; int res; double out_nClosingBalance; PetaPoco.Database db = null; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; var nClosingBalance = new SqlParameter("@out_nnClosingBalance", System.Data.SqlDbType.Float); nClosingBalance.Direction = System.Data.ParameterDirection.Output; nClosingBalance.Value = 0; #endregion Out Paramter using (db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <int>("exec st_tra_GetClosingBalanceOfAnnualPeriod @inp_nUserInfoId, @inp_nUserInfoIdRelative, @inp_nSecurityTypeCodeId, @out_nClosingBalance OUTPUT, @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_nUserInfoId = nUserInfoId, @inp_nUserInfoIdRelative = nUserInfoIdRelative, @inp_nSecurityTypeCodeId = nSecurityTypeCodeId, @out_nClosingBalance = nClosingBalance, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).Single <int>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookUpPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { out_nClosingBalance = Convert.ToDouble(nClosingBalance.Value); scope.Complete(); } #endregion Error Values } } } catch (Exception exp) { throw exp; } finally { } return(out_nClosingBalance); }
/// <summary> /// This method is used to lastest period end disclosure year code /// </summary> /// <param name="sConnectionString"></param> /// <returns></returns> public int GetLastestPeriodEndPeriodCode(string sConnectionString) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; int res; int out_nLatestPeriodEndCode; PetaPoco.Database db = null; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; var nLatestPeriodEndCode = new SqlParameter("@out_nLatestPeriodEndCode", System.Data.SqlDbType.Int); nLatestPeriodEndCode.Direction = System.Data.ParameterDirection.Output; nLatestPeriodEndCode.Value = 0; #endregion Out Paramter using (db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <int>("exec st_tra_PeriodEndDisclosureCurrentDisclosurePeriodCode @inp_nReturnSelect, @out_nLatestPeriodEndCode OUTPUT, @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_nReturnSelect = 1, @out_nLatestPeriodEndCode = nLatestPeriodEndCode, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).Single <int>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookUpPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { out_nLatestPeriodEndCode = Convert.ToInt32(nLatestPeriodEndCode.Value); scope.Complete(); } #endregion Error Values } } } catch (Exception exp) { throw exp; } finally { } return(out_nLatestPeriodEndCode); }
/// <summary> /// This method is used to get impact on securities held post to acquisition. /// </summary> /// <param name="sConnectionString"></param> /// <param name="nTransTypeCodeId"></param> /// <param name="nModeOfAcquisCodeId"></param> /// <returns></returns> public int GetImpactOnPostQuantity(string sConnectionString, int nTransTypeCodeId, int nModeOfAcquisCodeId, int nSecurityTypeCodeId) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; int res; int @out_nImpactOnPostQuantity; PetaPoco.Database db = null; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; var nImpactOnPostQuantity = new SqlParameter("@out_nImpactOnPostQuantity", System.Data.SqlDbType.Float); nImpactOnPostQuantity.Direction = System.Data.ParameterDirection.Output; nImpactOnPostQuantity.Value = 0; #endregion Out Paramter using (db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <int>("exec st_tra_GetImpactOnPostQuantity @nTransTypeCodeId, @nModeOfAcquisCodeId, @nSecurityTypeCodeId, @out_nImpactOnPostQuantity OUTPUT, @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @nTransTypeCodeId = nTransTypeCodeId, @nModeOfAcquisCodeId = nModeOfAcquisCodeId, @nSecurityTypeCodeId = nSecurityTypeCodeId, @out_nImpactOnPostQuantity = nImpactOnPostQuantity, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).Single <int>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookUpPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { out_nImpactOnPostQuantity = Convert.ToInt32(nImpactOnPostQuantity.Value); scope.Complete(); } #endregion Error Values } } } catch (Exception exp) { throw exp; } finally { } return(out_nImpactOnPostQuantity); }
public RolRepository(PetaPoco.Database database) : base(database) { }
/// <summary> /// Get Menu List as per the logged user id. /// </summary> /// <Author>Tushar Tekawade</Author> /// <CreatedDate>30 Jan 2015</CreatedDate> /// <Modifed> /// 1. /// /// </Modifed> /// <param name="UserID">Login User ID</param> /// <param name="sConnectionString">Database connection string</param> /// <returns>Menu List</returns> public IEnumerable <MenuMasterDTO> GetMenuMasterList(int UserID, string sConnectionString) { List <MenuMasterDTO> res = null; string sErrCode = string.Empty; #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; #endregion Paramters try { var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; // nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { res = db.Query <MenuMasterDTO>("exec st_mst_MenuMasterList_Tushar @UserID,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { UserID, out_nReturnValue = nReturnValue, out_nSQLErrCode = nSQLErrCode, out_sSQLErrMessage = sSQLErrMessage }).ToList <MenuMasterDTO>(); if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookupPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } } } catch (Exception exp) { throw exp; } return(res); }
public FrontendMenuRoleDAL(string dbName) { ConnectionStringName = dbName; _db = new PetaPoco.Database(ConnectionStringName); }
public PhuongTienChuaChay_BaoDuongDAL() { ConnectionStringName = "DefaultConnection"; _db = new PetaPoco.Database(ConnectionStringName); }
public ComCodeDTO SaveDetails(string sConnectionString, ComCodeDTO m_objComCodeDTO) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; ComCodeDTO res = null; #endregion Paramters bool isSave = true; //flag to check if allow to save records try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; #endregion Out Paramter //check if Code is set visible false then check code is already used in other table by delete and rollback delete tranascation //if delete is sucessful then allow to set code visible false else show error that code can't be set false if (m_objComCodeDTO.CodeID != 0 && !m_objComCodeDTO.IsVisible) { var nReturnValue2 = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue2.Direction = System.Data.ParameterDirection.Output; nReturnValue2.Value = 0; var nSQLErrCode2 = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode2.Direction = System.Data.ParameterDirection.Output; nSQLErrCode2.Value = 0; var sSQLErrMessage2 = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage2.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage2.Value = ""; using (var db2 = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope2 = db2.GetTransaction()) { res = db2.Query <ComCodeDTO>("exec st_com_CodeDelete @inp_iCodeID,@inp_nUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_iCodeID = m_objComCodeDTO.CodeID, @inp_nUserId = m_objComCodeDTO.LoggedInUserId, @out_nReturnValue = nReturnValue2, @out_nSQLErrCode = nSQLErrCode2, @out_sSQLErrMessage = sSQLErrMessage2 }).SingleOrDefault <ComCodeDTO>(); if (Convert.ToInt32(nReturnValue2.Value) != 0) { // return value is not 0 means there is reference key on other table and can't delete // so do NOT allow user to change visibility of code isSave = false; out_nReturnValue = 10052; //error msg - Cannot set code to invisible because code is already in use string sReturnValue = sLookUpPrefix + out_nReturnValue; Exception e = new Exception(); e.Data[0] = sReturnValue; //if (nSQLErrCode.Value != System.DBNull.Value) //{ // out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); // e.Data[1] = out_nSQLErrCode; //} //if (sSQLErrMessage.Value != System.DBNull.Value) //{ // out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); // e.Data[2] = out_sSQLErrMessage; //} Exception ex = new Exception(db2.LastSQL.ToString(), e); throw ex; } else { // return value is 0 means DB records can be deleted and there is no dependencey on code // so allow user to change visibility of code isSave = true; } } } } //check flag to save or not if (isSave) { using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <ComCodeDTO>("exec st_com_CodeSave @inp_iCodeID,@inp_sCodeName,@inp_iCodeGroupId,@inp_sDescription,@inp_bIsVisible,@inp_bIsActive,@inp_sDisplayCode,@inp_iParentCodeId,@inp_nUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_iCodeID = m_objComCodeDTO.CodeID, @inp_sCodeName = m_objComCodeDTO.CodeName, @inp_iCodeGroupId = m_objComCodeDTO.CodeGroupId, @inp_sDescription = m_objComCodeDTO.Description, @inp_bIsVisible = m_objComCodeDTO.IsVisible, @inp_bIsActive = m_objComCodeDTO.IsActive, @inp_sDisplayCode = m_objComCodeDTO.DisplayCode, @inp_iParentCodeId = m_objComCodeDTO.ParentCodeId, @inp_nUserId = m_objComCodeDTO.LoggedInUserId, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).SingleOrDefault <ComCodeDTO>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookUpPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { scope.Complete(); return(res); } #endregion Error Values } } } } catch (Exception exp) { throw exp; } finally { } return(res); }
public IEnumerable <ComCodeDTO> GetList(ComCodeDTO m_objComCodeDTO, string sConnectionString) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; List <ComCodeDTO> res = null; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; #endregion Out Paramter using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <ComCodeDTO>("exec st_com_CodeList @inp_iCodeID,@inp_sCodeName,@inp_iCodeGroupId,@inp_sDescription,@inp_bIsVisible,@inp_nIsActive,@inp_iDisplayOrder,@inp_sDisplayCode,@inp_iParentCodeId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_iCodeID = m_objComCodeDTO.CodeID, @inp_sCodeName = m_objComCodeDTO.CodeName, @inp_iCodeGroupId = m_objComCodeDTO.CodeGroupId, @inp_sDescription = m_objComCodeDTO.Description, @inp_bIsVisible = m_objComCodeDTO.IsVisible, @inp_nIsActive = m_objComCodeDTO.IsActive, @inp_iDisplayOrder = m_objComCodeDTO.DisplayOrder, @inp_sDisplayCode = m_objComCodeDTO.DisplayCode, @inp_iParentCodeId = m_objComCodeDTO.ParentCodeId, out_nReturnValue = nReturnValue , out_nSQLErrCode = nSQLErrCode , out_sSQLErrMessage = sSQLErrMessage }).ToList <ComCodeDTO>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookUpPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { scope.Complete(); } #endregion Error Values } } } catch (Exception exp) { throw exp; } finally { } return(res); }
public SQLiteConfigurationStore(PetaPoco.Database database) : base(database) { }
/// <summary> /// This method is used for the AutoComplete Upsi details. /// <param name="sConnectionString">DB Connection String</param> /// <param name="m_objCompanyDTO"></param> /// <returns></returns> public List <UpsiDTO> AutoCompleteList(string i_sConnectionString, Hashtable HT_SearchParam) { List <UpsiDTO> res = null; #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; PetaPoco.Database db = null; #endregion Paramters try { var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; //nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <UpsiDTO>("exec st_UP_UPSIAutoList @inp_sAction, @inp_sCompanyName, @inp_sCompanyAddress, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT", new { inp_sAction = HT_SearchParam["Action"], inp_sCompanyName = HT_SearchParam["CompanyName"], inp_sCompanyAddress = HT_SearchParam["CompanyAddress"], out_nReturnValue = nReturnValue, out_nSQLErrCode = nSQLErrCode, out_sSQLErrMessage = sSQLErrMessage }).ToList <UpsiDTO>(); if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); //string sReturnValue = sLookupPrefix + out_nReturnValue; //e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { scope.Complete(); return(res); } } } } catch (Exception exp) { throw exp; } }
public async Task RunAsync(int iterations) { using (var connection = GetOpenConnection()) { #pragma warning disable IDE0017 // Simplify object initialization #pragma warning disable RCS1121 // Use [] instead of calling 'First'. var tests = new Tests(); // Linq2SQL Try(() => { var l2scontext1 = GetL2SContext(connection); tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq2Sql: Normal"); var l2scontext2 = GetL2SContext(connection); var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id)); tests.Add(id => compiledGetPost(l2scontext2, id), "Linq2Sql: Compiled"); var l2scontext3 = GetL2SContext(connection); tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).First(), "Linq2Sql: ExecuteQuery"); }, "LINQ-to-SQL"); // Entity Framework Try(() => { var entityContext = new EFContext(connection); tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework"); var entityContext2 = new EFContext(connection); tests.Add(id => entityContext2.Database.SqlQuery <Post>("select * from Posts where Id = {0}", id).First(), "Entity Framework: SqlQuery"); var entityContext3 = new EFContext(connection); tests.Add(id => entityContext3.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework: No Tracking"); }, "Entity Framework"); // Entity Framework Core Try(() => { var entityContext = new EFCoreContext(ConnectionString); tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework Core"); var entityContext2 = new EFCoreContext(ConnectionString); tests.Add(id => entityContext2.Posts.FromSql("select * from Posts where Id = {0}", id).First(), "Entity Framework Core: FromSql"); var entityContext3 = new EFContext(connection); tests.Add(id => entityContext3.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework Core: No Tracking"); }, "Entity Framework Core"); // Dapper Try(() => { var mapperConnection = GetOpenConnection(); tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Query (buffered)"); tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Query (non-buffered)"); tests.Add(id => mapperConnection.QueryFirstOrDefault <Post>("select * from Posts where Id = @Id", new { Id = id }), "Dapper: QueryFirstOrDefault"); var mapperConnection2 = GetOpenConnection(); tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Dynamic Query (buffered)"); tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Dynamic Query (non-buffered)"); tests.Add(id => mapperConnection2.QueryFirstOrDefault("select * from Posts where Id = @Id", new { Id = id }), "Dapper: Dynamic QueryFirstOrDefault"); // dapper.contrib var mapperConnection3 = GetOpenConnection(); tests.Add(id => mapperConnection3.Get <Post>(id), "Dapper.Contrib"); }, "Dapper"); // Massive Try(() => { var massiveModel = new DynamicModel(ConnectionString); var massiveConnection = GetOpenConnection(); tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Massive: Dynamic ORM Query"); }, "Massive"); // PetaPoco Try(() => { // PetaPoco test with all default options var petapoco = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient"); petapoco.OpenSharedConnection(); tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Normal"); // PetaPoco with some "smart" functionality disabled var petapocoFast = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient"); petapocoFast.OpenSharedConnection(); petapocoFast.EnableAutoSelect = false; petapocoFast.EnableNamedParams = false; petapocoFast.ForceDateTimesToUtc = false; tests.Add(id => petapocoFast.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Fast"); }, "PetaPoco"); // NHibernate Try(() => { var nhSession1 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession1.CreateSQLQuery(@"select * from Posts where Id = :id") .SetInt32("id", id) .List(), "NHibernate: SQL"); var nhSession2 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession2.CreateQuery(@"from Post as p where p.Id = :id") .SetInt32("id", id) .List(), "NHibernate: HQL"); var nhSession3 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession3.CreateCriteria <Post>() .Add(Restrictions.IdEq(id)) .List(), "NHibernate: Criteria"); var nhSession4 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession4 .Query <Post>() .First(p => p.Id == id), "NHibernate: LINQ"); var nhSession5 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession5.Get <Post>(id), "NHibernate: Session.Get"); }, "NHibernate"); // Simple.Data Try(() => { var sdb = Simple.Data.Database.OpenConnection(ConnectionString); tests.Add(id => sdb.Posts.FindById(id).FirstOrDefault(), "Simple.Data"); }, "Simple.Data"); // Belgrade Try(() => { var query = new Belgrade.SqlClient.SqlDb.QueryMapper(ConnectionString); tests.AsyncAdd(id => query.ExecuteReader("SELECT TOP 1 * FROM Posts WHERE Id = " + id, reader => { var post = new Post(); post.Id = reader.GetInt32(0); post.Text = reader.GetString(1); post.CreationDate = reader.GetDateTime(2); post.LastChangeDate = reader.GetDateTime(3); post.Counter1 = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4); post.Counter2 = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5); post.Counter3 = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6); post.Counter4 = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7); post.Counter5 = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8); post.Counter6 = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9); post.Counter7 = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10); post.Counter8 = reader.IsDBNull(11) ? null : (int?)reader.GetInt32(11); post.Counter9 = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12); }), "Belgrade Sql Client"); }, "Belgrade Sql Client"); //Susanoo Try(() => { var susanooDb = new DatabaseManager(connection); var susanooPreDefinedCommand = CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text) .DefineResults <Post>() .Realize(); var susanooDynamicPreDefinedCommand = CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text) .DefineResults <dynamic>() .Realize(); tests.Add(Id => CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text) .DefineResults <Post>() .Realize() .Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Cache Retrieval"); tests.Add(Id => CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text) .DefineResults <dynamic>() .Realize() .Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Cache Retrieval"); tests.Add(Id => susanooDynamicPreDefinedCommand .Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Static"); tests.Add(Id => susanooPreDefinedCommand .Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Static"); }, "Susanoo"); //ServiceStack's OrmLite: Try(() => { var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider); var db = dbFactory.Open(); tests.Add(id => db.SingleById <Post>(id), "ServiceStack.OrmLite: SingleById"); }, "ServiceStack.OrmLite"); // Hand Coded Try(() => { var postCommand = new SqlCommand() { Connection = connection, CommandText = @"select Id, [Text], [CreationDate], LastChangeDate, Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id" }; var idParam = postCommand.Parameters.Add("@Id", SqlDbType.Int); tests.Add(id => { idParam.Value = id; using (var reader = postCommand.ExecuteReader()) { reader.Read(); var post = new Post(); post.Id = reader.GetInt32(0); post.Text = reader.GetNullableString(1); post.CreationDate = reader.GetDateTime(2); post.LastChangeDate = reader.GetDateTime(3); post.Counter1 = reader.GetNullableValue <int>(4); post.Counter2 = reader.GetNullableValue <int>(5); post.Counter3 = reader.GetNullableValue <int>(6); post.Counter4 = reader.GetNullableValue <int>(7); post.Counter5 = reader.GetNullableValue <int>(8); post.Counter6 = reader.GetNullableValue <int>(9); post.Counter7 = reader.GetNullableValue <int>(10); post.Counter8 = reader.GetNullableValue <int>(11); post.Counter9 = reader.GetNullableValue <int>(12); } }, "Hand Coded"); #if !NETSTANDARD1_3 var table = new DataTable { Columns = { { "Id", typeof(int) }, { "Text", typeof(string) }, { "CreationDate", typeof(DateTime) }, { "LastChangeDate", typeof(DateTime) }, { "Counter1", typeof(int) }, { "Counter2", typeof(int) }, { "Counter3", typeof(int) }, { "Counter4", typeof(int) }, { "Counter5", typeof(int) }, { "Counter6", typeof(int) }, { "Counter7", typeof(int) }, { "Counter8", typeof(int) }, { "Counter9", typeof(int) }, } }; tests.Add(id => { idParam.Value = id; object[] values = new object[13]; using (var reader = postCommand.ExecuteReader()) { reader.Read(); reader.GetValues(values); table.Rows.Add(values); } }, "DataTable via IDataReader.GetValues"); #endif }, "Hand Coded"); // Subsonic isn't maintained anymore - doesn't import correctly //Try(() => // { // // Subsonic ActiveRecord // tests.Add(id => 3SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault"); // // Subsonic coding horror // SubSonic.tempdbDB db = new SubSonic.tempdbDB(); // tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList<Post>(), "SubSonic Coding Horror"); //}, "Subsonic"); //// BLToolkit - doesn't import correctly in the new .csproj world //var db1 = new DbManager(GetOpenConnection()); //tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList<Post>(), "BLToolkit"); Console.WriteLine(); Console.WriteLine("Running..."); await tests.RunAsync(iterations).ConfigureAwait(false); #pragma warning restore RCS1121 // Use [] instead of calling 'First'. #pragma warning restore IDE0017 // Simplify object initialization } }
public bool DelegationDetailsSaveDelete(string sConnectionString, DataTable i_tblDelegationDetails, int i_nLoggedInUserID) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; bool bReturn = true; #endregion Paramters try { #region Out Paramter var nout_nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nout_nReturnValue.Direction = System.Data.ParameterDirection.Output; //nout_nReturnValue.Value = 0; var nout_nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nout_nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nout_nSQLErrCode.Value = 0; var sout_sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.NVarChar); sout_sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sout_sSQLErrMessage.Value = string.Empty; var inp_tblDelegationDetailsType = new SqlParameter(); inp_tblDelegationDetailsType.DbType = DbType.Object; inp_tblDelegationDetailsType.ParameterName = "@inp_tblDelegationDetailsType"; inp_tblDelegationDetailsType.TypeName = "dbo.RoleActivityType"; inp_tblDelegationDetailsType.SqlValue = i_tblDelegationDetails; inp_tblDelegationDetailsType.SqlDbType = SqlDbType.Structured; #endregion Out Paramter using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { var res = db.Query <int>("exec st_usr_DelegationDetailsSaveDelete @inp_tblDelegationDetailsType,@inp_iLoggedInUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_tblDelegationDetailsType = inp_tblDelegationDetailsType, @inp_iLoggedInUserId = i_nLoggedInUserID, @out_nReturnValue = nout_nReturnValue, @out_nSQLErrCode = nout_nSQLErrCode, @out_sSQLErrMessage = sout_sSQLErrMessage, }).Single <int>(); #region Error Values if (Convert.ToInt32(nout_nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nout_nReturnValue.Value); string sReturnValue = sLookupPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nout_nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nout_nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sout_sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sout_sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } bReturn = false; Exception ex = new Exception(db.LastCommand.ToString(), e); throw ex; } else { scope.Complete(); return(bReturn); } #endregion Error Values } } } catch (Exception exp) { throw exp; } finally { } //return bReturn; }
/// <summary> /// This method is used to get period's start and end date in year /// </summary> /// <param name="sConnectionString"></param> /// <param name="YearCode"></param> /// <param name="PeriodCode"></param> /// <returns></returns> public Dictionary <String, Object> GetPeriodStarEndDate(string sConnectionString, int YearCode, int PeriodCode, int PeriodType) { var PeriodStartEndDate = new Dictionary <String, object>(); #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; int res; DateTime? dtDate = null; DateTime out_dtStartDate; DateTime out_dtEndDate; PetaPoco.Database db = null; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; var dtPeriodStartDate = new SqlParameter("@out_dtStartDate", System.Data.SqlDbType.DateTime); dtPeriodStartDate.Direction = System.Data.ParameterDirection.Output; dtPeriodStartDate.Value = ""; var dtPeriodEndDate = new SqlParameter("@out_dtEndDate", System.Data.SqlDbType.DateTime); dtPeriodEndDate.Direction = System.Data.ParameterDirection.Output; dtPeriodEndDate.Value = ""; #endregion Out Paramter using (db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <int>("exec st_tra_PeriodEndDisclosureStartEndDate2 @inp_nYearCode,@inp_nPeriodCode,@inp_dtDate,@inp_iPeriodType,@inp_nReturnSelect, @out_dtStartDate OUTPUT,@out_dtEndDate OUTPUT,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_nYearCode = YearCode, @inp_nPeriodCode = PeriodCode, @inp_dtDate = dtDate, @inp_iPeriodType = PeriodType, @inp_nReturnSelect = 1, @out_dtStartDate = dtPeriodStartDate, @out_dtEndDate = dtPeriodEndDate, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).Single <int>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookUpPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { out_dtStartDate = Convert.ToDateTime(dtPeriodStartDate.Value); out_dtEndDate = Convert.ToDateTime(dtPeriodEndDate.Value); scope.Complete(); PeriodStartEndDate.Add("start_date", out_dtStartDate); PeriodStartEndDate.Add("end_date", out_dtEndDate); } #endregion Error Values } } } catch (Exception exp) { throw exp; } finally { } return(PeriodStartEndDate); }
/// <summary> /// This method is used for get user info list by selected User. /// </summary> /// <param name="i_sConnectionString"></param> /// <param name="i_objUserInfoDTO">User Type Object</param> /// <returns>User Information List</returns> public UpsiDTO GetUserInfo(string i_sConnectionString, int UserInfo) { #region Paramters UpsiDTO lstUserInfo = null; string sErrCode = string.Empty; int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; PetaPoco.Database db = null; #endregion Paramters try { #region Output Param var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; // nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; sSQLErrMessage.Size = 500; #endregion Output Param // db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient"); using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { lstUserInfo = db.Query <UpsiDTO>("exec st_UpsiUserList @inp_iUserInfoId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { inp_iUserInfoId = UserInfo, out_nReturnValue = nReturnValue, out_nSQLErrCode = nSQLErrCode, out_sSQLErrMessage = sSQLErrMessage }).SingleOrDefault(); if (Convert.ToInt32(nReturnValue.Value) != 0) { #region Return Error Code Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); //string sReturnValue = sLookupPrefix + out_nReturnValue; //e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; #endregion Return Error Code } else { scope.Complete(); } } } } catch (Exception exp) { throw exp; } return(lstUserInfo); }
public bool Delete(int m_nDelegationMasterId, string sConnectionString, int nLoggedInUserId) { #region Paramters bool bReturn; int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; #endregion Out Paramter using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { var res = db.Query <DelegationMasterDTO>("exec st_usr_DelegationMasterDelete @inp_nDelegationId, @inp_nLoggedInUserId, @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { inp_nDelegationId = m_nDelegationMasterId, inp_nLoggedInUserId = nLoggedInUserId, out_nReturnValue = nReturnValue, out_nSQLErrCode = nSQLErrCode, out_sSQLErrMessage = sSQLErrMessage }).ToList <DelegationMasterDTO>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookupPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } bReturn = false; Exception ex = new Exception(db.LastCommand.ToString(), e); throw ex; } else { bReturn = true; } #endregion Error Values } } catch (Exception exp) { throw exp; } finally { } return(bReturn); }
public PhuongTienChuaChay_BaoDuongDAL(string dbName) { ConnectionStringName = dbName; _db = new PetaPoco.Database(ConnectionStringName); }
public List <RoleActivityDTO> GetDelegationActivityDetails(string sConnectionString, int m_nDelegationID, int m_nUserInfoIdFrom, int m_nUserInfoIdTo) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; #endregion Paramters try { #region Out Paramter var nout_nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nout_nReturnValue.Direction = System.Data.ParameterDirection.Output; //nout_nReturnValue.Value = 0; var nout_nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nout_nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nout_nSQLErrCode.Value = 0; var sout_sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.NVarChar); sout_sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sout_sSQLErrMessage.Value = string.Empty; #endregion Out Paramter using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { var res = db.Query <RoleActivityDTO>("exec st_usr_DelegationActivityDetails @inp_nDelegationMasterID, @inp_nUserInfoIdFrom, @inp_nUserInfoIdTo, @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_nDelegationMasterID = m_nDelegationID, @inp_nUserInfoIdFrom = m_nUserInfoIdFrom, @inp_nUserInfoIdTo = m_nUserInfoIdTo, out_nReturnValue = nout_nReturnValue, out_nSQLErrCode = nout_nSQLErrCode, out_sSQLErrMessage = sout_sSQLErrMessage, }).ToList <RoleActivityDTO>(); #region Error Values if (Convert.ToInt32(nout_nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nout_nReturnValue.Value); e.Data[0] = out_nReturnValue; if (nout_nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nout_nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sout_sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sout_sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastCommand.ToString(), e); throw ex; } else { return(res); } #endregion Error Values } } catch (Exception exp) { throw exp; } finally { } }
public QuestionsService(PetaPoco.Database db, IMapper mapper) { this.Database = db; this.Mapper = mapper; }
public DelegationMasterDTO SaveDetails(DelegationMasterDTO m_objDelegationMasterDTO, int m_nPartialSave, string sConnectionString, int nLoggedInUserId) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; #endregion Paramters try { #region Out Paramter var nout_nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nout_nReturnValue.Direction = System.Data.ParameterDirection.Output; nout_nReturnValue.Value = 0; var nout_nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nout_nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nout_nSQLErrCode.Value = 0; var sout_sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.NVarChar); sout_sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sout_sSQLErrMessage.Value = string.Empty; #endregion Out Paramter using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { var res = db.Query <DelegationMasterDTO>("exec st_usr_DelegationMasterSave @inp_sDelegationId,@inp_iUserInfoIdFrom,@inp_iUserInfoIdTo,@inp_dtDelegationFrom,@inp_dtDelegationTo,@inp_nPartialSave,@inp_iLoggedInUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_sDelegationId = m_objDelegationMasterDTO.DelegationId, @inp_dtDelegationFrom = m_objDelegationMasterDTO.DelegationFrom, @inp_dtDelegationTo = m_objDelegationMasterDTO.DelegationTo, @inp_iUserInfoIdFrom = m_objDelegationMasterDTO.UserInfoIdFrom, @inp_iUserInfoIdTo = m_objDelegationMasterDTO.UserInfoIdTo, @inp_nPartialSave = m_nPartialSave, @inp_iLoggedInUserId = nLoggedInUserId, out_nReturnValue = nout_nReturnValue, out_nSQLErrCode = nout_nSQLErrCode, out_sSQLErrMessage = sout_sSQLErrMessage, }).SingleOrDefault <DelegationMasterDTO>(); #region Error Values if (Convert.ToInt32(nout_nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nout_nReturnValue.Value); string sReturnValue = sLookupPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nout_nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nout_nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sout_sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sout_sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastCommand.ToString(), e); throw ex; } else { return(res); } #endregion Error Values } } catch (Exception exp) { throw exp; } }
/// <summary> /// This method is used to save Upsi Sharing Info List /// </summary> /// <param name="sConnectionString"></param> /// <param name="objPreclearanceRequestNonImplCompanyDTO"></param> /// <returns></returns> public UpsiDTO SaveUpsiList(string sConnectionString, DataTable dt_UpsiList) { #region Paramters UpsiDTO res = null; string sErrCode = string.Empty; PetaPoco.Database db = null; int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; var inp_tblUpsiList = new SqlParameter(); inp_tblUpsiList.DbType = DbType.Object; inp_tblUpsiList.ParameterName = "@inp_tblUpsiTempSharingDataType"; inp_tblUpsiList.TypeName = "dbo.UpsiTempSharingDataType"; inp_tblUpsiList.SqlValue = dt_UpsiList; inp_tblUpsiList.SqlDbType = SqlDbType.Structured; #endregion Out Paramter using (db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Fetch <UpsiDTO>("exec st_usr_UpsiTempDataDetails @inp_tblUpsiTempSharingDataType, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT", new { @inp_tblUpsiTempSharingDataType = inp_tblUpsiList, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).SingleOrDefault(); if (Convert.ToInt32(nReturnValue.Value) != 0) { #region Error Code Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); //string sReturnValue = sLookupPrefix + out_nReturnValue; //e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; #endregion Error Code } else { scope.Complete(); return(res); } } } } catch (Exception exp) { throw exp; } //return res; }
/// <summary> /// This method is used for the saving Upsi Publish Date. /// <param name="sConnectionString">DB Connection String</param> /// <param name="m_objCompanyDTO"></param> /// <returns>if save then return true else return false</returns> public bool SaveDetails(string i_sConnectionString, UpsiDTO objUpsiTempSharingData) { #region Paramters UpsiDTO res = null; int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; bool bReturn = false; PetaPoco.Database db = null; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; #endregion Out Paramter using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { //var res = db.Query<UpsiTempSharingData>("exec st_usr_UpsiTempDataDetails @inp_sCompanyName,@inp_sAddress,@inp_sCategory,@inp_sReason,@inp_sComments,@inp_sPAN, @inp_sName, @inp_sPhone, @inp_sEmailId, @inp_sSharingDate,@inp_sPublishDate,inp_sUsernfoId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", res = db.Query <UpsiDTO>("exec st_usr_UpsiPublishdate @inp_iUserInfoId,@inp_nUpsi_id,@inp_nPublishDate,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { inp_iUserInfoId = objUpsiTempSharingData.UserInfoId, inp_nUpsi_id = objUpsiTempSharingData.UPSIDocumentId, inp_nPublishDate = objUpsiTempSharingData.PublishDate, out_nReturnValue = nReturnValue, out_nSQLErrCode = nSQLErrCode, out_sSQLErrMessage = sSQLErrMessage }).SingleOrDefault(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookupPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); bReturn = false; throw ex; } else { scope.Complete(); bReturn = true; } //return bReturn; #endregion Error Values return(bReturn); } } } catch (Exception exp) { throw exp; } }
public void Run(int iterations) { using (var connection = Program.GetOpenConnection()) { var tests = new Tests(); var l2scontext1 = GetL2SContext(connection); tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq 2 SQL"); var l2scontext2 = GetL2SContext(connection); var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id)); tests.Add(id => compiledGetPost(l2scontext2, id), "Linq 2 SQL Compiled"); var l2scontext3 = GetL2SContext(connection); tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).First(), "Linq 2 SQL ExecuteQuery"); var entityContext = new EFContext(connection); tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity framework"); var entityContext2 = new EFContext(connection); tests.Add(id => entityContext2.Database.SqlQuery <Post>("select * from Posts where Id = {0}", id).First(), "Entity framework SqlQuery"); //var entityContext3 = new EFContext(connection); //tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity framework CompiledQuery"); //var entityContext4 = new EFContext(connection); //tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity framework ESQL"); var entityContext5 = new EFContext(connection); tests.Add(id => entityContext5.Posts.AsNoTracking().First(p => p.Id == id), "Entity framework No Tracking"); var mapperConnection = Program.GetOpenConnection(); tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Mapper Query (buffered)"); tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Mapper Query (non-buffered)"); var mapperConnection2 = Program.GetOpenConnection(); tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dynamic Mapper Query (buffered)"); tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dynamic Mapper Query (non-buffered)"); // dapper.contrib var mapperConnection3 = Program.GetOpenConnection(); tests.Add(id => mapperConnection3.Get <Post>(id), "Dapper.Cotrib"); var massiveModel = new DynamicModel(Program.ConnectionString); var massiveConnection = Program.GetOpenConnection(); tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Dynamic Massive ORM Query"); // PetaPoco test with all default options var petapoco = new PetaPoco.Database(Program.ConnectionString, "System.Data.SqlClient"); petapoco.OpenSharedConnection(); tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Normal)"); // PetaPoco with some "smart" functionality disabled var petapocoFast = new PetaPoco.Database(Program.ConnectionString, "System.Data.SqlClient"); petapocoFast.OpenSharedConnection(); petapocoFast.EnableAutoSelect = false; petapocoFast.EnableNamedParams = false; petapocoFast.ForceDateTimesToUtc = false; tests.Add(id => petapocoFast.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Fast)"); // Subsonic ActiveRecord tests.Add(id => SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault"); // Subsonic coding horror SubSonic.tempdbDB db = new SubSonic.tempdbDB(); tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList <Post>(), "SubSonic Coding Horror"); // NHibernate var nhSession1 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession1.CreateSQLQuery(@"select * from Posts where Id = :id") .SetInt32("id", id) .List(), "NHibernate SQL"); var nhSession2 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession2.CreateQuery(@"from Post as p where p.Id = :id") .SetInt32("id", id) .List(), "NHibernate HQL"); var nhSession3 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession3.CreateCriteria <Post>() .Add(Restrictions.IdEq(id)) .List(), "NHibernate Criteria"); var nhSession4 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession4 .Query <Post>() .Where(p => p.Id == id).First(), "NHibernate LINQ"); var nhSession5 = NHibernateHelper.OpenSession(); tests.Add(id => nhSession5.Get <Post>(id), "NHibernate Session.Get"); // bltoolkit var db1 = new DbManager(Program.GetOpenConnection()); tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList <Post>(), "BLToolkit"); // Simple.Data var sdb = Simple.Data.Database.OpenConnection(Program.ConnectionString); tests.Add(id => sdb.Posts.FindById(id), "Simple.Data"); //Susanoo var susanooDb = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString"); var susanooDb2 = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString"); var susanooPreDefinedCommand = CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text) .DefineResults <Post>() .Realize("PostById"); var susanooDynamicPreDefinedCommand = CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text) .DefineResults <dynamic>() .Realize("DynamicById"); tests.Add(Id => CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text) .DefineResults <Post>() .Realize("PostById") .Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Cache Retrieval"); tests.Add(Id => CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text) .DefineResults <dynamic>() .Realize("DynamicById") .Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Cache Retrieval"); tests.Add(Id => susanooDynamicPreDefinedCommand .Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Static"); tests.Add(Id => susanooPreDefinedCommand .Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Static"); // Soma // DISABLED: assembly fail loading FSharp.PowerPack, Version=2.0.0.0 // var somadb = new Soma.Core.Db(new SomaConfig()); // tests.Add(id => somadb.Find<Post>(id), "Soma"); //ServiceStack's OrmLite: OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance; //Using SQL Server IDbCommand ormLiteCmd = Program.GetOpenConnection().CreateCommand(); tests.Add(id => ormLiteCmd.QueryById <Post>(id), "OrmLite QueryById"); // HAND CODED var postCommand = new SqlCommand(); postCommand.Connection = connection; postCommand.CommandText = @"select Id, [Text], [CreationDate], LastChangeDate, Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id"; var idParam = postCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int); tests.Add(id => { idParam.Value = id; using (var reader = postCommand.ExecuteReader()) { reader.Read(); var post = new Post(); post.Id = reader.GetInt32(0); post.Text = reader.GetNullableString(1); post.CreationDate = reader.GetDateTime(2); post.LastChangeDate = reader.GetDateTime(3); post.Counter1 = reader.GetNullableValue <int>(4); post.Counter2 = reader.GetNullableValue <int>(5); post.Counter3 = reader.GetNullableValue <int>(6); post.Counter4 = reader.GetNullableValue <int>(7); post.Counter5 = reader.GetNullableValue <int>(8); post.Counter6 = reader.GetNullableValue <int>(9); post.Counter7 = reader.GetNullableValue <int>(10); post.Counter8 = reader.GetNullableValue <int>(11); post.Counter9 = reader.GetNullableValue <int>(12); } }, "hand coded"); DataTable table = new DataTable { Columns = { { "Id", typeof(int) }, { "Text", typeof(string) }, { "CreationDate", typeof(DateTime) }, { "LastChangeDate", typeof(DateTime) }, { "Counter1", typeof(int) }, { "Counter2", typeof(int) }, { "Counter3", typeof(int) }, { "Counter4", typeof(int) }, { "Counter5", typeof(int) }, { "Counter6", typeof(int) }, { "Counter7", typeof(int) }, { "Counter8", typeof(int) }, { "Counter9", typeof(int) }, } }; tests.Add(id => { idParam.Value = id; object[] values = new object[13]; using (var reader = postCommand.ExecuteReader()) { reader.Read(); reader.GetValues(values); table.Rows.Add(values); } }, "DataTable via IDataReader.GetValues"); tests.Run(iterations); } }
static void Main(string[] args) { var testObjectContext = new Destrier.Test.TestObjectContext(); string QUERY = new Query <TestObject>().Limit(LIMIT).QueryBody; string CONNECTION_STRING = Destrier.DatabaseConfigurationContext.DefaultConnectionString; Func <List <TestObject> > rawAction = () => { var provider = Destrier.DatabaseConfigurationContext.DefaultProviderFactory; var list = new List <TestObject>(); using (var conn = provider.CreateConnection()) { conn.ConnectionString = DatabaseConfigurationContext.DefaultConnectionString; conn.Open(); using (var cmd = provider.CreateCommand()) { cmd.Connection = conn; cmd.CommandText = QUERY; cmd.CommandType = System.Data.CommandType.Text; using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { var testObject = ReflectionHelper.GetNewObject <TestObject>(); testObject.Id = dr.GetInt32(0); testObject.Active = dr.GetBoolean(1); testObject.Name = dr.GetString(2); testObject.NullName = !dr.IsDBNull(3) ? dr.GetString(3) : null; testObject.Created = dr.GetDateTime(4); testObject.Modified = !dr.IsDBNull(5) ? (DateTime?)dr.GetDateTime(5) : null; testObject.NullableId = !dr.IsDBNull(6) ? (int?)dr.GetInt32(6) : null; testObject.ReferencedObjectId = !dr.IsDBNull(7) ? dr.GetInt32(7) : default(Int32); testObject.Type = (TestObjectTypeId)dr.GetInt32(8); testObject.NullableType = !dr.IsDBNull(9) ? (Int32?)dr.GetInt32(9) : null; testObject.SingleChar = dr.GetString(10); testObject.Single = (Single)dr.GetDouble(11); testObject.Double = !dr.IsDBNull(12) ? dr.GetDouble(12) : default(Double); testObject.NullableDouble = !dr.IsDBNull(13) ? (Double?)dr.GetDouble(13) : null; testObject.Guid = !dr.IsDBNull(14) ? dr.GetGuid(14) : default(Guid); testObject.NullableGuid = !dr.IsDBNull(15) ? (Guid?)dr.GetGuid(15) : null; list.Add(testObject); } } } } return(list); }; Func <List <TestObject> > ormLiteAction = () => { var dbFactory = new ServiceStack.OrmLite.OrmLiteConnectionFactory(CONNECTION_STRING, ServiceStack.OrmLite.SqlServerDialect.Provider); using (System.Data.IDbConnection db = dbFactory.OpenDbConnection()) { return(ServiceStack.OrmLite.ReadConnectionExtensions.Select <TestObject>(db, q => q.Limit(LIMIT))); } }; Func <List <TestObject> > destrierAction = () => { return(new Destrier.Query <TestObject>().Limit(LIMIT).StreamResults().ToList()); }; Func <List <TestObject> > destrierRawQueryAction = () => { return(new Destrier.Query <TestObject>(QUERY).StreamResults().ToList()); }; Func <List <TestObject> > petaPocoAction = () => { var db = new PetaPoco.Database(DatabaseConfigurationContext.DefaultConnectionString, "System.Data.SqlClient"); return(db.Query <TestObject>(QUERY).ToList()); }; Func <List <TestObject> > efAction = () => { var db = new TestObjectContext(); return(db.Objects.AsNoTracking().Take(LIMIT).ToList()); }; Func <List <TestObject> > dapperAction = () => { using (var conn = Destrier.DatabaseConfigurationContext.DefaultProviderFactory.CreateConnection()) { conn.ConnectionString = CONNECTION_STRING; conn.Open(); return(conn.Query <TestObject>(QUERY).ToList()); } }; //------------------------------------------------------------------------------------------- //------------------------------------------------------------------------------------------- //Run Tests //------------------------------------------------------------------------------------------- //------------------------------------------------------------------------------------------- var stopwatch = new System.Diagnostics.Stopwatch(); Console.WriteLine("ORM Test Suite"); for (int x = 0; x < Console.WindowWidth; x++) { Console.Write("="); } Console.WriteLine(); var testSteps = new Dictionary <string, Func <List <TestObject> > >() { { "Raw Reader", rawAction }, { "Destrier", destrierAction }, { "Destrier (Raw Query)", destrierRawQueryAction }, { "PetaPoco", petaPocoAction }, { "ServiceStack ORMLite", ormLiteAction }, { "Dapper", dapperAction }, { "EF6", efAction }, }; var results = new List <Int64>(); foreach (var kvp in testSteps) { stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); var queryResults = kvp.Value(); stopwatch.Stop(); if (!queryResults.Any()) { throw new Exception("No results."); } } foreach (var kvp in testSteps) { results = new List <long>(); for (int x = 0; x < TRIALS; x++) { stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); var queryResults = kvp.Value(); stopwatch.Stop(); results.Add(stopwatch.ElapsedMilliseconds); if (!queryResults.Any()) { throw new Exception("No results."); } } Console.Write(kvp.Key); int spaces = 25 - kvp.Key.Length; for (int x = 0; x < spaces; x++) { Console.Write(" "); } Console.Write(String.Format("\tFirst Result: {0}ms", results.First())); Console.WriteLine(String.Format("\tAvg: {0}ms", results.Average())); } Console.WriteLine(); testObjectContext.EnsureDestroyDataStore(); }
public AplicacionRepository(PetaPoco.Database database) : base(database) { }
public Job() : base("/Job") { this.RequiresAuthentication(); // db reference var db = new PetaPoco.Database("LocalSQLite"); // datatable functions Get["/dt"] = parameters => { var jobs = db.Query <Model.JobView>("select j.JobID, j.MonitorID, m.Name, m.Type, j.StartTime, j.EndTime, j.FinalState, j.Info from Job J, Monitor m WHERE j.MonitorID = m.MonitorID;"); return(Response.AsJson(new { aaData = jobs })); }; Get["/dt/{id}"] = parameters => { Model.JobView j = db.Single <Model.JobView>("select j.JobID, j.MonitorID, m.Name, m.Type, j.StartTime, j.EndTime, j.FinalState, j.Batch, j.Info from Job J, Monitor m WHERE j.MonitorID = m.MonitorID AND JobID = @JobID", new { JobID = parameters.id }); return(Response.AsJson(new { aaData = j })); }; Get["/dt/{type}/{id}"] = parameters => { string MonitorType = parameters.type.ToString(); switch (MonitorType.ToUpper()) { case "EVENTVWR": //TODO: limited to 50 to avoid generating too much data - needs to be paged IEnumerable <Model.Eventvwr> Eventvwrdata = db.Query <Model.Eventvwr>("select * from Eventvwr WHERE JobID = @JobID", new { JobID = parameters.id }); return(Response.AsJson(new { aaData = Eventvwrdata })); case "IIS": //TODO: limited to 50 to avoid generating too much data - needs to be paged IEnumerable <Model.IIS> IISdata = db.Query <Model.IIS>("select * from IIS WHERE JobID = @JobID limit 50", new { JobID = parameters.id }); return(Response.AsJson(new { aaData = IISdata })); default: log.WarnFormat("Type ({0}) not supported in Nancy Module for Job", MonitorType); break; } var jobs = db.Query <Model.JobView>("select j.JobID, j.MonitorID, m.Name, m.Type, j.StartTime, j.EndTime, j.FinalState, j.Info from Job J, Monitor m WHERE j.MonitorID = m.MonitorID AND j.JobId=@id;", new { id = parameters.id }); return(Response.AsJson(new { aaData = jobs })); }; Get["/vanilla"] = parameters => { var jobs = db.Query <Model.JobView>("select j.JobID, j.MonitorID, m.Name, m.Type, j.StartTime, j.EndTime, j.FinalState, j.Info from Job J, Monitor m WHERE j.MonitorID = m.MonitorID;"); return(View["Web\\Views\\Job-vanilla.html", new { jobs = jobs }]); }; Get["/"] = parameters => { return(View["Web\\Views\\Job.html", baseModel]); }; Get["/{id}"] = parameters => { /* * IEnumerable<Model.JobView> jobs = db.Query<Model.JobView>("select j.JobID, j.MonitorID, m.Name, m.Type, j.StartTime, j.EndTime, j.FinalState, j.Info from Job J, Monitor m WHERE j.MonitorID = m.MonitorID AND j.JobId=@id;",new { id = parameters.id }); * Model.JobView job = jobs.FirstOrDefault(); */ Model.JobView jobView = db.Single <Model.JobView>("select j.JobID, j.MonitorID, m.Name, m.Type, j.StartTime, j.EndTime, j.FinalState, j.Batch, j.Info from Job J, Monitor m WHERE j.MonitorID = m.MonitorID AND j.JobId=@id;", new { id = parameters.id }); if (jobView.Batch == 1) { baseModel.jobs = jobView; return(View["Web\\Views\\JobBatch.html", baseModel]); } else { // switch on the type of monitor switch (jobView.Type.ToUpper()) { case "EVENTVWR": IEnumerable <Model.Eventvwr> Eventvwrdata = db.Query <Model.Eventvwr>("select * from Eventvwr WHERE JobID = @JobID", jobView); baseModel.jobs = jobView; baseModel.Results = Eventvwrdata; return(View["Web\\Views\\JobEventvwr.html", baseModel]); // new { jobs = job, Results = Eventvwrdata }]; case "IIS": IEnumerable <Model.IIS> IISdata = db.Query <Model.IIS>("select * from IIS WHERE JobID = @JobID", jobView); baseModel.jobs = jobView; baseModel.Results = IISdata; return(View["Web\\Views\\JobIIS.html", baseModel]); // new { jobs = job, Results = IISdata }]; default: log.WarnFormat("Type ({0}) not supported in Nancy Module for Job", jobView.Type); break; } } return("ah, didn't expect you to be here!"); }; }
public int ValidateOTPDetails(string i_sConnectionString, int OTPConfigMasterId, int i_nUserInfo, string OTPCode) { #region Paramters int res = 0; string sErrCode = string.Empty; PetaPoco.Database db = null; int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; #endregion Paramters try { #region Output Param var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Size = 500; sSQLErrMessage.Value = ""; #endregion Output Param using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <int>("exec st_usr_ValidateOTPDetailsByUserId @inp_OTPConfigurationSettingMasterID, @inp_UserInfoId, @inp_OTPCode, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT", new { inp_OTPConfigurationSettingMasterID = OTPConfigMasterId, inp_UserInfoId = i_nUserInfo, inp_OTPCode = OTPCode, out_nReturnValue = nReturnValue, out_nSQLErrCode = nSQLErrCode, out_sSQLErrMessage = sSQLErrMessage }).Single <int>(); if (Convert.ToInt32(nReturnValue.Value) != 0) { #region Error Code Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookupPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; #endregion Error Code } else { scope.Complete(); return(res); } } } } catch (Exception exp) { throw exp; } }
public bool Delete(string sConnectionString, int nLoggedInUserId, int nCodeID) { #region Paramters bool bReturn = false; int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; List <ComCodeDTO> res = null; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; #endregion Out Paramter using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <ComCodeDTO>("exec st_com_CodeDelete @inp_iCodeID,@inp_nUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_iCodeID = nCodeID, @inp_nUserId = nLoggedInUserId, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).ToList <ComCodeDTO>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookUpPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } bReturn = false; Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { scope.Complete(); bReturn = true; } #endregion Error Values } } } catch (Exception exp) { throw exp; } finally { } return(bReturn); }
public FrontendMenuRoleDAL() { ConnectionStringName = "DefaultConnection"; _db = new PetaPoco.Database(ConnectionStringName); }
protected override List <t_ware> GetData() { using (var db = new PetaPoco.Database()) { return(db.Fetch <t_ware>("where IsUsing=1")); } }