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"));
}
}
