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("结束");
}
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();
}
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();
}
public static List<Models.Page> GetAllPages()
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.Query<Models.Page>("").ToList();
}
}
/// <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<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();
}
}
public static List<Models.Category> GetAllCategory()
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.Query<Models.Category>("").ToList();
}
}
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 IEnumerable<HoiVien> GetCustomers()
{
var db = new PetaPoco.Database("MyConnection");
IEnumerable<HoiVien> kq;
if (TempData["dieukienloc"] == null)//truy van mac dinh
{
kq = db.Query<HoiVien>("select * from HoiVien where Disable = '0'");
}
else//Truy van khi da co tempdata(da co dieu kien loc)
{
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] == "")
{
qr = qr + string.Format(" and Disable = '0'");
}
kq = db.Query<HoiVien>(qr);
}
return kq;
}
public void GetYueSaoList()
{
var db = new PetaPoco.Database("BBTDataBaseConnection");
var hotelList = db.Query<dat_sclient_base>("select * from dat_sclient_base");
foreach (var item in hotelList)
{
Console.Write(item.c_addr);
}
}
public object GET(int id) {
var db = new PetaPoco.Database("jlapc");
var sql = PetaPoco.Sql.Builder
.Append("SELECT N.*")
.Append("FROM Novedad N")
.Append("WHERE N.NovedadID=@0", id);
var items = db.Query<dynamic>(sql);
return (new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
}
private static void FilteredQuery()
{
var petaPoco = new PetaPoco.Database("Chinook");
var customers = petaPoco.Query<Customer>("SELECT * FROM customer WHERE Country = 'United Kingdom'");
foreach (var customer in customers)
ObjectDumper.Write(customer);
}
public object getAll() {
var db = new PetaPoco.Database("jlapc");
var sql = PetaPoco.Sql.Builder
.Append("SELECT SEC.*")
.Append("FROM SubEstadoContacto SEC");
var items = db.Query<SubEstadoContacto>(sql);
JsonResult rta = new JsonResult() { Data = items, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
return rta.Data;
}
public object get(int id) {
var db = new PetaPoco.Database("jlapc");
var sql = PetaPoco.Sql.Builder
.Append("SELECT SEC.*")
.Append("FROM SubEstadoContacto SEC")
.Append("WHERE EC.SubEstadoContactoID=@0", id);
var items = db.Query<SubEstadoContacto>(sql);
return (new JsonResult() { Data = items, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
}
public object GET(int id) {
var db = new PetaPoco.Database("jlapc");
var sql = PetaPoco.Sql.Builder
.Append("SELECT C.*")
.Append("FROM Contacto C")
.Append("WHERE C.ContactoID=@0", id);
var items = db.Query<Contacto>(sql);
return (new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
}
public ActionResult Customers_Read([DataSourceRequest]DataSourceRequest request)
{
IEnumerable<HoiVien> kq;
TempData.Keep("frmc");
if (TempData["frmc"] == null)
{
var db = new PetaPoco.Database("MyConnection");
//PetaPoco.Page<HoiVien> rs = MyConnectionDB.GetInstance().Page<HoiVien>(1, 2, "select * from HoiVien");
kq = db.Query<HoiVien>("select * from HoiVien");
}
else
{
FormCollection frmc = (FormCollection)TempData["frmc"];
var db = new PetaPoco.Database("MyConnection");
//Thiet lap cau truy van dua tren dieu kien loc
string qr = "select * from HoiVien where 1=1";
if (frmc["donvi"] != "")
{
qr = qr + string.Format(" and DivisionID like N'%{0}%'", frmc["donvi"]);
}
if (frmc["diachi"] != "")
{
qr = qr + string.Format(" and Address like N'%{0}%'", frmc["diachi"]);
}
if (frmc["sodienthoai"] != "")
{
qr = qr + string.Format(" and Phone like N'%{0}%'", frmc["sodienthoai"]);
}
if (frmc["mahoivien"] != "")
{
qr = qr + string.Format(" and MemberID like N'%{0}%'", frmc["mahoivien"]);
}
if (frmc["cmnd"] != "")
{
qr = qr + string.Format(" and Identify like N'%{0}%'", frmc["cmnd"]);
}
if (frmc["fax"] != "")
{
qr = qr + string.Format(" and Fax like N'%{0}%'", frmc["fax"]);
}
if (frmc["tenhoivien"] != "")
{
qr = qr + string.Format(" and MemberName like N'%{0}%'", frmc["tenhoivien"]);
}
if (frmc["dtdd"] != "")
{
qr = qr + string.Format(" and Tel like N'%{0}%'", frmc["dtdd"]);
}
if (frmc["email"] != "")
{
qr = qr + string.Format(" and Email like N'%{0}%'", frmc["email"]);
}
kq = db.Query<HoiVien>(qr);
}
return Json(kq.ToDataSourceResult(request));
}
public object Login([FromBody] LoginRequest req) {
string conn = Config.SQLConnectionFor(req.loginname, req.password);
try {
var db = new PetaPoco.Database(conn, Config.providerName);
var sql = PetaPoco.Sql.Builder
.Append("SELECT top 1 *")
.Append("FROM Usuario U")
.Append("WHERE U.LogIn=@0", req.loginname);
var user = db.Query<dynamic>(sql).First();
DateTime expirationDate = DateTime.Now.AddSeconds(Config.authSessionExpirationLengthInSeconds);
var tokenObj = new {
E = expirationDate,
U = user.UsuarioID,
G = user.GrupoID,
P = user.PerfilID
};
var token = new JavaScriptSerializer().Serialize(tokenObj);
token = EncodeTo64(token);
var data = new {
UsuarioID = user.UsuarioID,
GrupoID = user.GrupoID,
PerfilID = user.PerfilID,
Nombre = user.Nombre,
Apellido = user.Apellido,
expirationDate = expirationDate
};
return (new JsonResult() {
Data = new LoginResponse {
ok = true,
data = data,
token = token,
error = ""
}, JsonRequestBehavior = JsonRequestBehavior.AllowGet
}).Data;
} catch (Exception e) {
return (new JsonResult() {
Data = new LoginResponse() {
ok = false,
data = new { },
token = "",
error = e.Message + "[Conn=" + conn + "]"
}, JsonRequestBehavior = JsonRequestBehavior.AllowGet
}).Data;
}
}
public object GET(int id) {
var db = new PetaPoco.Database("jlapc");
var sql = PetaPoco.Sql.Builder
.Append("SELECT T.*,G.Descripcion as GrupoDescripcion")
.Append("FROM Tema T")
.Append("INNER JOIN Grupo G on G.GrupoID = T.GrupoID")
.Append("WHERE T.TemaID=@0", id);
var items = db.Query<Tema>(sql).First();
return (new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
}
private static void SelectProcedure()
{
var petaPoco = new PetaPoco.Database("Chinook");
var customers =
petaPoco.Query<Customer>(
PetaPoco.Sql.Builder.Append("SELECT * FROM customer").Append("WHERE country=@0", "USA"));
foreach (var item in customers)
ObjectDumper.Write(item);
}
private void NotifyNewStory()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query<Entities.StorytimeUserList>("Select * From StorytimeUserList Where UserNotified = 0"))
{
var b = db2.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", a.UserId);
var storytime = db2.SingleOrDefault<Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", a.StorytimeId);
if (storytime.UserId != b.Id && b.DeviceToken != null)
{
CreatePushNotification(newstory + storytime.StorytimeTitle, b.DeviceToken);
}
a.UserNotified = true;
db2.Update(a);
}
foreach (var c in db.Query<Entities.StorytimeGroup>("Select * from StorytimeGroup Where UsersNotified = 0"))
{
var d = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", c.UserGroupId);
var storytime = db2.SingleOrDefault<Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", c.StorytimeId);
foreach (var e in db3.Query<Entities.UserGroupUser>("Select * from UserGroupUser Where UserGroupId = @0", d.UserGroupId))
{
var f = db2.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", e.UserId);
if (f != null && f.Id != storytime.UserId && f.DeviceToken != null)
{
CreatePushNotification(newstory + storytime.StorytimeTitle, f.DeviceToken);
}
}
c.UsersNotified = true;
db3.Update(c);
}
}
public static HttpResponseMessage authValidations(HttpRequestMessage request, CancellationToken cancellationToken) {
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.Unauthorized);
bool authFail = false;
setCORSHeaders(response);
var authToken = request.Headers.GetValues("auth-token").FirstOrDefault();
if (authToken == null || authToken.ToString() == "") {
response.Content = new StringContent("No se detecto un token de auth");
authFail = true;//VALIDACION TOKEN DE AUTH VACIO
}
AuthToken tokenObj = null;
try {
tokenObj = new JavaScriptSerializer().Deserialize<AuthToken>(DecodeFrom64(authToken));
} catch (Exception) {
response.Content = new StringContent("El token de auth es desconocido");
authFail = true; //VALIDACION: Token desconocido
}
if (tokenObj.E < DateTime.Now) {
response.Content = new StringContent("La fecha expiracion termino");
authFail = true; //VALIDACION DE FECHA DE EXPIRACION DE TOKEN
}
//RECUPERA EL USUARIO DE LA BD
var db = new PetaPoco.Database(Config.connectionStringName());
var sql = PetaPoco.Sql.Builder
.Append("SELECT top 1 *")
.Append("FROM Usuario U")
.Append("WHERE U.UsuarioID=@0", tokenObj.U);
var user = db.Query<Usuario>(sql).First();
if (tokenObj.G.ToString().ToLower() != user.GrupoID.ToString().ToLower()) {
response.Content = new StringContent("Intento de cambio de grupo de impl detectado");
authFail = true; //VALIDACION DE GRUPO
}
if (tokenObj.P.ToString().ToLower() != user.PerfilID.ToString().ToLower()) {
response.Content = new StringContent("Intento de cambio de perfil detectado");
authFail = true; //VALIDACION DE PERFIL
}
return authFail ? response : null;
}
public IHttpActionResult Get(string id)
{
var db = new PetaPoco.Database("AGSoftware");
System.Collections.Generic.List<Entities.StorytimePost> storytimepostlist = new List<Entities.StorytimePost>();
foreach (Entities.StorytimePost c in db.Query<Entities.StorytimePost>("Select * From StorytimePost Where StorytimeId = @0 Order By DateCreated Desc", id))
{
c.ImagePath = Providers.ImageHelper.GetImagePath(c.ImagePath);
c.ImagePath = c.ImagePath.Replace(@"\", @"/");
storytimepostlist.Add(c);
}
if (storytimepostlist.Count > 0)
return Ok(storytimepostlist);
else
return NotFound();
}
private void button1_Click(object sender, EventArgs e)
{
// Create a PetaPoco database object
var db = new PetaPoco.Database("sqlite");
fooQuery1.SetDisplay("SELECT * from foo where Id=1");
try
{
// Show all foo
foreach (var a in db.Query<foo>("SELECT * from foo where Id=1"))
{
fooQuery1.AppendDisplay("\r\n" + string.Format("{0} - {1}", a.Id, a.name));
Console.WriteLine("{0} - {1}", a.Id, a.name);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
}
}
public IHttpActionResult Get(string id)
{
var db = new PetaPoco.Database("AGSoftware");
System.Collections.Generic.List<Entities.AspNetUsers> contactlist = new List<Entities.AspNetUsers>();
foreach (var a in db.Query<Entities.ContactList>("Select * from ContactList Where UserId = @0", Storytime.Providers.UserHelper.GetUserId(this.User.Identity.Name)))
{
//todo see if there is a better way to do this with normalization and/or one connection.
var db2 = new PetaPoco.Database("AGSoftware");
var b = db2.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", a.ContactId);
contactlist.Add(b);
}
if (contactlist.Count > 0)
{
return Ok(contactlist);
}
else
{
return NotFound();
}
}
public List <NotificationDTO> GetNotificationAlertList(string sConnectionString, int inp_iLoggedInUserId, string sCalledFrom)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
List <NotificationDTO> 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 <NotificationDTO>("exec st_cmu_NotificationAlertList @inp_nLoggedInUserId,@inp_sCalledFrom,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_nLoggedInUserId = inp_iLoggedInUserId,
@inp_sCalledFrom = sCalledFrom,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).ToList <NotificationDTO>();
#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
{
}
}
public IEnumerable <RoleMasterDTO> GetList(RoleMasterDTO m_objRoleMasterDTO, string sConnectionString)
{
#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 <RoleMasterDTO>("exec st_usr_RoleMasterList @inp_iRoleId,@inp_sRoleName,@inp_sDescription,@inp_iStatusCodeId,@inp_sLandingPageURL,@inp_iUserTypeCodeId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iRoleId = m_objRoleMasterDTO.RoleId,
@inp_sRoleName = m_objRoleMasterDTO.RoleName,
@inp_sDescription = m_objRoleMasterDTO.Description,
@inp_iStatusCodeId = m_objRoleMasterDTO.StatusCodeId,
@inp_iUserTypeCodeId = m_objRoleMasterDTO.UserTypeCodeId,
}).ToList <RoleMasterDTO>();
#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 List <RoleActivityDTO> GetDetails(string sConnectionString, int m_nRoleId)
{
#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;
#endregion Out Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
var res = db.Query <RoleActivityDTO>("exec st_usr_RoleActivityDetails @inp_iRoleId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iRoleId = m_nRoleId,
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);
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
{
return(res);
}
#endregion Error Values
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
private static int SelectPartTest(string connectionString)
{
var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
return db.Query<Home>("SELECT * FROM Home where BuildYear<@0", 2000).ToList().Count();
}
public bool SaveDetails(string sConnectionString, DataTable db_tblTradingWindowEventType, int i_FinancialPeriodTypeCodeId, int i_nLoggedInUserID)
{
#region Paramters
bool bReturn = false;
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
List <TradingWindowEventDTO> res = null;
#endregion Paramters
try
{
#region 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.Size = 500;
sSQLErrMessage.Value = "";
var inp_tblTradingWindowEventType = new SqlParameter();
inp_tblTradingWindowEventType.DbType = DbType.Object;
inp_tblTradingWindowEventType.ParameterName = "@inp_tblTradingWindowEventType";
inp_tblTradingWindowEventType.TypeName = "dbo.TradingWindowEventType";
inp_tblTradingWindowEventType.SqlValue = db_tblTradingWindowEventType;
inp_tblTradingWindowEventType.SqlDbType = SqlDbType.Structured;
#endregion Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <TradingWindowEventDTO>("exec st_rul_TradingWindowFinancialYearSave @inp_tblTradingWindowEventType,@inp_iFinancialPeriodTypeCodeId,@inp_iLoggedInUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_tblTradingWindowEventType = inp_tblTradingWindowEventType,
@inp_iFinancialPeriodTypeCodeId = i_FinancialPeriodTypeCodeId,
@inp_iLoggedInUserId = i_nLoggedInUserID,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).ToList <TradingWindowEventDTO>();
#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 bool DeleteOtherEventDetails(string sConnectionString, int nTradingWindowEventID, int nLoggedInUserId)
{
#region Paramters
bool bReturn = false;
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
List <TradingWindowEventDTO> 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 <TradingWindowEventDTO>("exec st_rul_TradingWindowEventOtherDelete @inp_iTradingWindowEventId,@inp_nUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iTradingWindowEventId = nTradingWindowEventID,
@inp_nUserId = nLoggedInUserId,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).ToList <TradingWindowEventDTO>();
#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)
{
//return bReturn;
throw exp;
}
finally
{
}
return(bReturn);
}
public CODashboardDTO_OS GetDashboardDetails_OS(string sConnectionString, int nLoggedInUserId)
{
CODashboardDTO_OS res = null;
#region Paramters
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
})
{
res = db.Query <CODashboardDTO_OS>("exec st_tra_DashBoardForCO_OS @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT",
new
{
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage,
}).FirstOrDefault <CODashboardDTO_OS>();
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
{
return(res);
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
public void FillRetailStock(PetaPoco.Database db)
{
vendorRetailStocks = db.Query <VendorStockResult>("EXECUTE sp_FetchVendorRetailStock @0, @1", _xdoc.ToString(), _connectorID).ToList();
}
public List <ActivityResourceMappingDTO> GetActivityResourceMappingDetails(string i_sConnectionString, int inp_iUserInfoId)
{
List <ActivityResourceMappingDTO> 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 <ActivityResourceMappingDTO>("exec st_usr_ActivityResourceMappingList @inp_iUserInfoID, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT",
new
{
inp_iUserInfoID = inp_iUserInfoId,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList <ActivityResourceMappingDTO>();
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 bool InsertDeleteApplicability(string sConnectionString, int i_nMapToTypeCodeId, int i_nMapToId, int i_nAllEmployeeFlag, int i_nAllInsiderFlag, int i_nAllEmployeeInsiderFlag, int i_nAllCoFlag, int i_nAllCorporateInsiderFlag, int i_nAllNonEmployeeInsiderFlag, DataTable i_tblApplicabilityFilterType, DataTable i_tblNonInsEmpApplicabilityFilterType, DataTable i_tblApplicabilityIncludeExcludeUsers, int i_nLoggedInUserID, out int nOutCountOverlapPolicy)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
bool bReturn = true;
#endregion Paramters
try
{
#region Out Paramter
var nout_nCountUserAndOverlapTradingPolicy = new SqlParameter("@out_nCountUserAndOverlapTradingPolicy", System.Data.SqlDbType.Int);
nout_nCountUserAndOverlapTradingPolicy.Direction = System.Data.ParameterDirection.Output;
nout_nCountUserAndOverlapTradingPolicy.Value = 0;
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;
sout_sSQLErrMessage.Size = 1000;
var inp_tblApplicabilityFilterType = new SqlParameter();
inp_tblApplicabilityFilterType.DbType = DbType.Object;
inp_tblApplicabilityFilterType.ParameterName = "@inp_tblApplicabilityFilterType";
inp_tblApplicabilityFilterType.TypeName = "dbo.ApplicabilityFilterType";
inp_tblApplicabilityFilterType.SqlValue = i_tblApplicabilityFilterType;
inp_tblApplicabilityFilterType.SqlDbType = SqlDbType.Structured;
var inp_tblNonInsEmpApplicabilityFilterType = new SqlParameter();
inp_tblNonInsEmpApplicabilityFilterType.DbType = DbType.Object;
inp_tblNonInsEmpApplicabilityFilterType.ParameterName = "@inp_tblNonInsEmpApplicabilityFilterType";
inp_tblNonInsEmpApplicabilityFilterType.TypeName = "dbo.NonInsEmpApplicabilityFilterType";
inp_tblNonInsEmpApplicabilityFilterType.SqlValue = i_tblNonInsEmpApplicabilityFilterType;
inp_tblNonInsEmpApplicabilityFilterType.SqlDbType = SqlDbType.Structured;
var inp_tblApplicabilityIncludeExcludeUsers = new SqlParameter();
inp_tblApplicabilityIncludeExcludeUsers.DbType = DbType.Object;
inp_tblApplicabilityIncludeExcludeUsers.ParameterName = "@inp_tblApplicabilityIncludeExcludeUsers";
inp_tblApplicabilityIncludeExcludeUsers.TypeName = "dbo.ApplicabilityUserIncludeExcludeType";
inp_tblApplicabilityIncludeExcludeUsers.SqlValue = i_tblApplicabilityIncludeExcludeUsers;
inp_tblApplicabilityIncludeExcludeUsers.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 <Test_OS>("exec st_rul_ApplicabilityAssociationSave_OS @inp_nMapToTypeCodeId, @inp_nMapToId, @inp_nAllEmployeeFlag, @inp_nAllInsiderFlag, @inp_nAllEmployeeInsiderFlag ,@inp_nAllCoFlag , @inp_nAllCorporateInsiderFlag , @inp_nAllNonEmployeeInsiderFlag, @inp_tblApplicabilityFilterType, @inp_tblNonInsEmpApplicabilityFilterType, @inp_tblApplicabilityIncludeExcludeUsers, @inp_nUserId, @out_nCountUserAndOverlapTradingPolicy OUTPUT, @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_nMapToTypeCodeId = i_nMapToTypeCodeId,
@inp_nMapToId = i_nMapToId,
@inp_nAllEmployeeFlag = i_nAllEmployeeFlag,
@inp_nAllInsiderFlag = i_nAllInsiderFlag,
@inp_nAllEmployeeInsiderFlag = i_nAllEmployeeInsiderFlag, // ,@inp_nAllCoFlag , @inp_nAllCorporateInsiderFlag , @inp_nAllNonEmployeeInsiderFlag
@inp_nAllCoFlag = i_nAllCoFlag,
@inp_nAllCorporateInsiderFlag = i_nAllCorporateInsiderFlag,
@inp_nAllNonEmployeeInsiderFlag = i_nAllNonEmployeeInsiderFlag,
@inp_tblApplicabilityFilterType = inp_tblApplicabilityFilterType,
@inp_tblNonInsEmpApplicabilityFilterType = inp_tblNonInsEmpApplicabilityFilterType,
@inp_tblApplicabilityIncludeExcludeUsers = inp_tblApplicabilityIncludeExcludeUsers,
@inp_nUserId = i_nLoggedInUserID,
@out_nCountUserAndOverlapTradingPolicy = nout_nCountUserAndOverlapTradingPolicy,
@out_nReturnValue = nout_nReturnValue,
@out_nSQLErrCode = nout_nSQLErrCode,
@out_sSQLErrMessage = sout_sSQLErrMessage,
}).SingleOrDefault <Test_OS>();
#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.LastSQL.ToString(), e);
throw ex;
}
else
{
scope.Complete();
nOutCountOverlapPolicy = Convert.ToInt32(nout_nCountUserAndOverlapTradingPolicy.Value);
return(bReturn);
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
private void NotifyStoryWinner()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
var db4 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query<Entities.Storytime>("select * from storytime s where (select count(*) from storytimeseries ss where ss.storytimeid = s.storytimeid) = 10 and s.UsersNotified = 0"))
{
var b = db2.SingleOrDefault<Entities.StoryWinner>("Select top 1 sum(sp.votes) as votes, u.username, s.storytimetitle from storytimeseries ss inner join storytimepost sp on ss.StorytimeSeriesId = sp.SeriesId inner join AspNetUsers u on u.id = sp.UserId inner join storytime s on s.storytimeid = ss.storytimeid where ss.storytimeid = @0 group by u.username, s.storytimetitle order by votes desc", a.StorytimeId);
if (a.StorytimeTypeId == 1)
{
var c = db2.SingleOrDefault<Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", a.StorytimeId);
var d = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", c.UserGroupId);
foreach (var e in db3.Query<Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", d.UserGroupId))
{
if (e != null)
{
var f = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", e.UserId);
if (f != null && f.DeviceToken != null)
{
CreatePushNotification(storywinner + f.UserName + " has won the story " + a.StorytimeTitle + " with " + b.Votes + " !", f.DeviceToken);
}
}
}
}
else if (a.StorytimeTypeId == 2)
{
foreach (var g in db3.Query<Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", a.StorytimeId))
{
var h = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", g.UserId);
if (h != null && h.DeviceToken != null)
{
CreatePushNotification(storywinner + h.UserName + " has won the story " + a.StorytimeTitle + " with " + b.Votes + " !", h.DeviceToken);
}
}
}
a.UsersNotified = true;
db4.Update(a);
}
}
public IEnumerable <JobTitle> Get()
{
return(db.Query <JobTitle>(Constants.JobTitle.GetJobTitles));
}
public bool UpdateStatus(string sConnectionString, int nLoggedInUserId, int nDashboardId)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
bool bReturn = true;
int res;
#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
})
{
res = db.Query <int>("exec st_tra_DashBoardForCOUpdate @inp_nDashboardCountId,@inp_nLoggedInUserId ,@out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT",
new
{
@inp_nDashboardCountId = nDashboardId,
@inp_nLoggedInUserId = 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);
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;
}
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 bool DeleteRoleMasterDetails(string sConnectionString, int m_nRoleId, int 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;
#endregion Out Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
var res = db.Query <RoleMasterDTO>("exec st_usr_RoleMasterDelete @inp_iRoleId,@inp_nUserInfoId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iRoleId = m_nRoleId,
@inp_nUserInfoId = nLoggedInUserId,
@out_nReturnValue = nout_nReturnValue,
@out_nSQLErrCode = nout_nSQLErrCode,
@out_sSQLErrMessage = sout_sSQLErrMessage
}).ToList <RoleMasterDTO>();
//TO DO.... Exception occured while Deleting the Role.. that NULL value is recived for nout_nReturnValue.
#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();
bReturn = true;
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
return(bReturn);
}
public DMATAccountHolderDTO GetDMATHolderDetails(string sConnectionString, int nDMATAccountHolderID)
{
List <DMATAccountHolderDTO> res = null;
#region Paramters
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
})
{
res = db.Query <DMATAccountHolderDTO>("exec st_usr_DMATAccountHolderDetails @inp_iDMATAccountHolderId, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT",
new
{
inp_iDMATAccountHolderId = nDMATAccountHolderID,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage,
}).ToList <DMATAccountHolderDTO>();
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
{
return(res.FirstOrDefault());
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
/// <summary>
/// This method is used for save the DMATDetails
/// </summary>
/// <param name="i_sConnectionString">Connection string for which database</param>
/// <param name="m_objDMATDetailsDTO">DMAT Type Object</param>
/// <param name="nLoggedInUserId">Logged In User</param>
/// <returns>Boolean value based on the result</returns>
public DMATDetailsDTO SaveDMATDetails(string sConnectionString, DMATDetailsDTO m_objDMATDetailsDTO, int nLoggedInUserId)
{
List <DMATDetailsDTO> res = null;
#region Paramters
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 = "";
sSQLErrMessage.Size = 1000;
#endregion Out Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
res = db.Query <DMATDetailsDTO>("exec st_usr_DMATDetailsSave @inp_iUserInfoID, @inp_sDEMATAccountNumber, @inp_sDPBank, @inp_sDPID, @inp_sTMID, @inp_sDescription, @inp_iAccountTypeCodeId, @inp_iLoggedInUserId, @inp_iDPBankCodeId, @inp_iDmatAccStatusCodeId, @inp_iDMATDetailsID OUTPUT, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT",
new
{
inp_iUserInfoID = m_objDMATDetailsDTO.UserInfoID,
inp_sDEMATAccountNumber = m_objDMATDetailsDTO.DEMATAccountNumber,
inp_sDPBank = m_objDMATDetailsDTO.DPBank,
inp_sDPID = m_objDMATDetailsDTO.DPID,
inp_sTMID = m_objDMATDetailsDTO.TMID,
inp_sDescription = (m_objDMATDetailsDTO.Description == null ? "" : m_objDMATDetailsDTO.Description),
inp_iAccountTypeCodeId = m_objDMATDetailsDTO.AccountTypeCodeId,
inp_iLoggedInUserId = nLoggedInUserId,
inp_iDPBankCodeId = m_objDMATDetailsDTO.DPBankCodeId,
inp_iDmatAccStatusCodeId = m_objDMATDetailsDTO.DmatAccStatusCodeId,
inp_iDMATDetailsID = m_objDMATDetailsDTO.DMATDetailsID,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage,
}).ToList <DMATDetailsDTO>();
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
{
return(res.FirstOrDefault());
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
public EventDTO GetCurrentEvent(string sConnectionString, int UserInfoID = 0)
{
#region Paramters
int out_nReturnValue;
//bool bReturn = false;
int out_nSQLErrCode;
string out_sSQLErrMessage;
EventDTO 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 <EventDTO>("exec st_rul_GetCurrentEvent @inp_nUserInfoID, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT",
new
{
@inp_nUserInfoID = UserInfoID,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).FirstOrDefault <EventDTO>();
#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(res);
}
public IEnumerable <T> GetList(string sql)
{
return(db.Query <T>(sql));
}
public TradingWindowEventDTO SaveOtherEventDetails(string sConnectionString, TradingWindowEventDTO m_objTradingWindowEventDTO, int i_nLoggedInUserID)
{
#region Paramters
//bool bReturn = false;
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
TradingWindowEventDTO res = null;
#endregion Paramters
try
{
#region 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 = "";
sSQLErrMessage.Size = 1000;
#endregion Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <TradingWindowEventDTO>("exec st_rul_TradingWindowEventOtherSave @inp_iTradingWindowEventId, @inp_sTradingWindowId, @inp_iTradingWindowEventCodeId,@inp_dtResultDeclarationDate,@inp_dtWindowCloseDate,@inp_dtWindowOpenDate ,@inp_iDaysPriorToResultDeclaration,@inp_iWindowClosesBeforeHours,@inp_iWindowClosesBeforeMinutes,@inp_iDaysPostResultDeclaration,@inp_iWindowOpensAfterHours,@inp_iWindowOpensAfterMinutes ,@inp_iTradingWindowStatusCodeId, @inp_iLoggedInUserId ,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iTradingWindowEventId = m_objTradingWindowEventDTO.TradingWindowEventId,
@inp_sTradingWindowId = m_objTradingWindowEventDTO.TradingWindowId,
@inp_iTradingWindowEventCodeId = m_objTradingWindowEventDTO.TradingWindowEventCodeId,
@inp_dtResultDeclarationDate = m_objTradingWindowEventDTO.ResultDeclarationDate,
@inp_dtWindowCloseDate = m_objTradingWindowEventDTO.WindowCloseDate,
@inp_dtWindowOpenDate = m_objTradingWindowEventDTO.WindowOpenDate,
@inp_iDaysPriorToResultDeclaration = m_objTradingWindowEventDTO.DaysPriorToResultDeclaration,
@inp_iWindowClosesBeforeHours = m_objTradingWindowEventDTO.WindowClosesBeforeHours,
@inp_iWindowClosesBeforeMinutes = m_objTradingWindowEventDTO.WindowClosesBeforeMinutes,
@inp_iDaysPostResultDeclaration = m_objTradingWindowEventDTO.DaysPostResultDeclaration,
@inp_iWindowOpensAfterHours = m_objTradingWindowEventDTO.WindowOpensAfterHours,
@inp_iWindowOpensAfterMinutes = m_objTradingWindowEventDTO.WindowOpensAfterMinutes,
@inp_iTradingWindowStatusCodeId = m_objTradingWindowEventDTO.TradingWindowStatusCodeId,
@inp_iLoggedInUserId = i_nLoggedInUserID,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).SingleOrDefault <TradingWindowEventDTO>();
#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(res);
}
/// <summary>
///
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <param name="inp_iDefaulterReportID"></param>
/// <returns></returns>
public DefaulterReportOverrideDTO GetDetails(string i_sConnectionString, long inp_iDefaulterReportID)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
List <DefaulterReportOverrideDTO> res = null;
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 = "";
#endregion Output Param
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <DefaulterReportOverrideDTO>("exec st_rpt_DefaulterReportOverrideDetails @inp_iDefaulterReportID,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
inp_iDefaulterReportID = inp_iDefaulterReportID,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList <DefaulterReportOverrideDTO>();
#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.FirstOrDefault());
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
}
private static int SelectJoinTest(string connectionString)
{
var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
return db.Query<dynamic>(@"SELECT h.*
from Home h
inner join Province p on h.ProvinceId=p.Id
where p.Code=@0", 10).ToList().Count();
}
public bool InsertDeleteRoleActivities(string sConnectionString, DataTable i_tblRoleActivity, 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.Size = 500;
sout_sSQLErrMessage.Value = string.Empty;
var inp_tblRoleActivityType = new SqlParameter();
inp_tblRoleActivityType.DbType = DbType.Object;
inp_tblRoleActivityType.ParameterName = "@inp_tblRoleActivityType";
inp_tblRoleActivityType.TypeName = "dbo.RoleActivityType";
inp_tblRoleActivityType.SqlValue = i_tblRoleActivity;
inp_tblRoleActivityType.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_RoleActivitySaveDelete @inp_tblRoleActivityType,@inp_iLoggedInUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_tblRoleActivityType = inp_tblRoleActivityType,
@inp_iLoggedInUserId = i_nLoggedInUserID,
@out_nReturnValue = nout_nReturnValue,
@out_nSQLErrCode = nout_nSQLErrCode,
@out_sSQLErrMessage = sout_sSQLErrMessage,
}).SingleOrDefault <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.LastSQL.ToString(), e);
throw ex;
}
else
{
scope.Complete();
return(bReturn);
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
//return bReturn;
}
private static int Update100Test(string connectionString)
{
var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
var homes = db.Query<Home>("SELECT * FROM home WHERE BuildYear=@0", 2014).ToList();
int count = homes.Count();
foreach (var home in homes)
{
if (home != null)
{
home.BuildYear = 2015;
db.Update("Home", "Id", home);
}
}
return count;
}
/// <summary>
/// This method is used for delete the DMATDetails
/// </summary>
/// <param name="i_sConnectionString">Connection string for which database</param>
/// <param name="inp_iDMATAccountHolderID">DMATAccountHolderID</param>
/// <param name="nLoggedInUserId">Logged In User</param>
/// <returns>Boolean value based on the result</returns>
public bool DeleteDMATHolder(string i_sConnectionString, int inp_iDMATAccountHolderID, int nLoggedInUserId)
{
List <DMATAccountHolderDTO> res = null;
bool bReturn = false;
#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(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
res = db.Query <DMATAccountHolderDTO>("exec st_usr_DMATAccountHolderDelete @inp_iDMATAccountHolderId, @inp_iUserId, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT",
new
{
inp_iDMATAccountHolderId = inp_iDMATAccountHolderID,
inp_iUserId = nLoggedInUserId,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList <DMATAccountHolderDTO>();
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
{
bReturn = true;
}
}
}
catch (Exception exp)
{
bReturn = false;
throw exp;
}
finally
{
}
return(bReturn);
}
/// <summary>
/// This method is used to get applicability count for user
/// </summary>
/// <param name="sConnectionString"></param>
/// <param name="nUserId"></param>
/// <param name="nMapToTypeCodeId"></param>
/// <returns></returns>
public int UserApplicabilityCount(string sConnectionString, int nUserInfoId, int nMapToTypeCodeId)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
int res = 0;
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(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <int>("exec st_usr_UserApplicabilityCount_OS @inp_iUserInfoId,@inp_nApplicabilityType, @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iUserInfoId = nUserInfoId,
@inp_nApplicabilityType = nMapToTypeCodeId,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).SingleOrDefault <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
{
scope.Complete();
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
//return bReturn;
throw exp;
}
finally
{
}
return(res);
}
public RoleMasterDTO InsertUpdateRoleMasterDetails(string sConnectionString, RoleMasterDTO m_objRoleMasterDTO, 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
})
{
using (var scope = db.GetTransaction())
{
var res = db.Query <RoleMasterDTO>("exec st_usr_RoleMasterSave @inp_iRoleId,@inp_sRoleName,@inp_sDescription,@inp_iStatusCodeId,@inp_iUserTypeCodeId,@inp_iIsDefault,@inp_iLoggedInUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iRoleId = m_objRoleMasterDTO.RoleId,
@inp_sRoleName = m_objRoleMasterDTO.RoleName,
@inp_sDescription = m_objRoleMasterDTO.Description,
@inp_iStatusCodeId = m_objRoleMasterDTO.StatusCodeId,
@inp_iUserTypeCodeId = m_objRoleMasterDTO.UserTypeCodeId,
@inp_iIsDefault = (m_objRoleMasterDTO.IsDefault ? 1 : 0),
@inp_iLoggedInUserId = nLoggedInUserId,
@out_nReturnValue = nout_nReturnValue,
@out_nSQLErrCode = nout_nSQLErrCode,
@out_sSQLErrMessage = sout_sSQLErrMessage,
}).SingleOrDefault <RoleMasterDTO>();
#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.LastSQL.ToString(), e);
throw ex;
}
else
{
scope.Complete();
return(res);
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
private void NotifyVote()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
var db4 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query<Entities.Vote>("Select * From Vote v Where v.UserNotified = 0"))
{
var b = db2.SingleOrDefault<Entities.StorytimePost>("Select * from Storytimepost Where StorytimePostId = @0", a.StorytimePostId);
var c = db2.SingleOrDefault<Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", b.StorytimeId);
var d = db2.SingleOrDefault<Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", c.StorytimeId);
if (c.StorytimeTypeId == 1)
{
var e = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", d.UserGroupId);
foreach(var f in db3.Query<Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", e.UserGroupId))
{
if (f != null)
{
var g = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", f.UserId);
if (g != null && g.DeviceToken != null)
{
string notificationtext = newvote + " " + g.UserName + " has voted for " + b.PostText;
CreatePushNotification(newvote, g.DeviceToken);
}
}
}
}
else if(c.StorytimeTypeId == 2)
{
foreach (var h in db3.Query<Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", c.StorytimeId))
{
var i = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", h.UserId);
if (i != null && i.DeviceToken != null)
{
string notificationtext = newvote + " " + i.UserName + " has voted for " + b.PostText;
CreatePushNotification(newvote, i.DeviceToken);
}
}
}
a.UserNotified = true;
db2.Update(a);
}
}
public IEnumerable <PopulateComboDTO> GetPopulateDataTable(string sConnectionString, int inp_iGridType, int inp_iPageSize, int inp_iPageNo,
string inp_sSortField, string inp_sSortOrder, string inp_sParam1, string inp_sParam2, string inp_sParam3, string inp_sParam4,
string inp_sParam5, string inp_sParam6, string inp_sParam7, string inp_sParam8, string inp_sParam9,
string inp_sParam10, string inp_sParam11, string inp_sParam12, string inp_sParam13, string inp_sParam14,
string inp_sParam15, string inp_sParam16, string inp_sParam17, string inp_sParam18, string inp_sParam19,
string inp_sParam20, out int out_iTotalRecords, string sLookupPrefix)
{
List <PopulateComboDTO> res = null;
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
#endregion Paramters
try
{
var nTotalRecords = new SqlParameter("@out_iTotalRecords", System.Data.SqlDbType.Int);
nTotalRecords.Direction = System.Data.ParameterDirection.Output;
nTotalRecords.Value = 0;
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 <PopulateComboDTO>("exec st_com_populateCombo @inp_iComboType, @inp_sParam1, @inp_sParam2, @inp_sParam3, @inp_sParam4, @inp_sParam5"
+ ",@out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT ",
new
{
inp_iGridType,
inp_sParam1,
inp_sParam2,
inp_sParam3,
inp_sParam4,
inp_sParam5,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList <PopulateComboDTO>();
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
{
if (nTotalRecords.Value != DBNull.Value)
{
out_iTotalRecords = Convert.ToInt32(nTotalRecords.Value);
}
else
{
out_iTotalRecords = 0;
}
}
}
}
catch (Exception exp)
{
throw exp;
}
return(res);
}
private void NotifySeriesWinner()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
var db4 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query<Entities.SeriesWinner>("Select ss.StorytimeSeriesId, s.StorytimeTypeId, s.StorytimeId, s.StorytimeTitle, ss.SeriesText, u.Username, sp1.Votes From StorytimeSeries ss inner join Storytime s on s.StorytimeId = ss.StorytimeId inner join AspNetUsers u on ss.UserId = u.Id inner join StorytimePost sp1 on sp1.SeriesId = ss.StorytimeSeriesId And sp1.Votes = (Select MAX(sp2.Votes) From StorytimePost sp2 Where sp2.StorytimeId = sp1.StorytimeId) Where ss.UsersNotified = 0 And ss.DateCreated < DateAdd(hh, -24, GetDate())"))
{
if (a.StorytimeTypeId == 1)
{
var b = db2.SingleOrDefault<Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", a.StorytimeId);
var c = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", b.UserGroupId);
foreach (var d in db3.Query<Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", c.UserGroupId))
{
if (d != null)
{
var e = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", d.UserId);
if (e != null && e.DeviceToken != null)
{
string notificationtext = string.Empty;
if (a.Votes > 0)
notificationtext = serieswinner + " " + a.Username + " has won the series " + a.SeriesText + " for Story " + a.StorytimeTitle + " with " + a.Votes + " votes!";
else
notificationtext = nowinner + a.SeriesText + " for Story " + a.StorytimeTitle;
CreatePushNotification(notificationtext, e.DeviceToken);
}
}
}
}
else if (a.StorytimeTypeId == 2)
{
foreach (var f in db3.Query<Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", a.StorytimeId))
{
var g = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", f.UserId);
if (g != null && g.DeviceToken != null)
{
string notificationtext = string.Empty;
if (a.Votes > 0)
notificationtext = serieswinner + " " + a.Username + " has won the series " + a.SeriesText + " for Story " + a.StorytimeTitle + " with " + a.Votes + " votes!";
else
notificationtext = nowinner + a.SeriesText + " for Story " + a.StorytimeTitle;
CreatePushNotification(notificationtext, g.DeviceToken);
}
}
}
var storytimeseries = db4.SingleOrDefault<Entities.StorytimeSeries>("Select * from StorytimeSeries Where StorytimeSeriesId = @0", a.StorytimeSeriesId);
storytimeseries.UsersNotified = true;
db4.Update(storytimeseries);
}
}
public UserRoleDTO SaveDetails(UserRoleDTO m_objUserRoleDTO, 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 <UserRoleDTO>("exec st_usr_UserRoleSave @inp_iUserRoleID,@inp_iUserInfoID,@inp_iRoleID,@inp_nUserInfoId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iUserRoleID = m_objUserRoleDTO.UserRoleID,
@inp_iUserInfoID = m_objUserRoleDTO.UserInfoID,
@inp_iRoleID = m_objUserRoleDTO.RoleID,
@inp_nUserInfoId = nLoggedInUserId,
out_nReturnValue = nout_nReturnValue,
out_nSQLErrCode = nout_nSQLErrCode,
out_sSQLErrMessage = sout_sSQLErrMessage,
}).Single <UserRoleDTO>();
#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
{
}
}