public Int32 Insert(BOL.users obj)
{
if (obj != null)
{
try
{
conn = new MySqlConnection(ConnectionString);
conn.Open();
trans = conn.BeginTransaction();
bll_users.Insert(obj, conn, trans);
Int32 users = bll_utility.GetMaxId("users", "Userid", conn, trans);
trans.Commit();
conn.Close();
return(users);
}
catch
{
trans.Rollback();
conn.Close();
}
}
return(0);
}
public DataSet Select(BOL.users obj, MySqlConnection conn, MySqlTransaction trans)
{
if (obj != null)
{
StringBuilder qry = new System.Text.StringBuilder();
qry.Append(@"SELECT `userid`, `firstname`, `lastname`, `roleid`, `status`, `username`, `password`, `email`, `mobile`,
`createdby`, `createdon`, `modifyby`, `modifyon`, `isdeleted` FROM `users` WHERE ");
if (obj.Userid > 0)
{
qry.Append("`userid` = " + obj.Userid + " AND");
}
if (!string.IsNullOrEmpty(obj.Firstname))
{
qry.Append("`firstname` = '" + obj.Firstname + "' AND");
}
if (!string.IsNullOrEmpty(obj.Lastname))
{
qry.Append("`Lastname` = '" + obj.Lastname + "' AND");
}
if (!string.IsNullOrEmpty(obj.Mobile))
{
qry.Append("`Mobile` = '" + obj.Mobile + "' AND");
}
qry = qry.Remove(qry.Length - 3, qry.Length);
return(dbconnect.GetDataset(conn, trans, qry.ToString()));
}
return(null);
}
public void Initialize()
{
manager_users = new Manager.users();
manager_status = new Manager.status();
manager_roles = new Manager.roles(); utility = new Utility();
bol_statuses = new List <BOL.status>();
bol_roles = new List <BOL.roles>(); bol_users = new BOL.users();
}
public Int32 Delete(BOL.users obj, MySqlConnection conn, MySqlTransaction trans)
{
if (obj != null)
{
try
{
string qry = @"DELETE FROM `users` WHERE `userid`= " + obj.Userid;
dbconnect.ExecuteNonQuery(conn, trans, qry, null);
return(1);
}
catch
{
}
}
return(0);
}
public Int32 Save()
{
bol_users = new BOL.users();
manager_users = new Manager.users();
bol_users.Firstname = txtFirstName.Text;
bol_users.Lastname = txtLastName.Text;
bol_users.Email = txtEmail.Text;
bol_users.Mobile = txtMobile.Text;
bol_users.Isdeleted = "F";
bol_users.Status = Convert.ToInt32(ddlStatus.SelectedValue);
bol_users.Createdby = Convert.ToInt32(Session["userid"]);
bol_users.Createdon = DateTime.Parse(System.DateTime.Now.ToString("dd/MMM/yyyy"));
bol_users.Username = txtUserName.Text;
bol_users.Password = txtPassword.Text;
bol_users.Roleid = Convert.ToInt32(ddlRoles.SelectedValue);
bol_users.Userid = manager_users.Insert(bol_users);
return(bol_users.Userid);
}
public Int32 Update(BOL.users obj, MySqlConnection conn, MySqlTransaction trans)
{
if (obj != null)
{
try
{
string qry = @"UPDATE `users` SET `firstname` = '" + obj.Firstname + "',`lastname` = '" + obj.Lastname + "',roleid` = " + obj.Roleid +
",'status' = " + obj.Status + ",'username' = " + obj.Username + ",'password'= " + obj.Password + ",`email`='" + obj.Email + "',mbile` = '" + obj.Mobile + "'`createdby` = " + obj.Createdby +
",`createdon` = " + obj.Createdon + ",`isdeleted` = '" + obj.Isdeleted + "',`modifyby` = " + obj.Modifyby + ",`modifyon` =" +
obj.Modifyon + " WHERE `userid`=" + obj.Userid;
dbconnect.GetScalar(conn, trans, qry, null);
return(1);
}
catch
{
}
}
return(0);
}
public Int32 Insert(BOL.users obj, MySqlConnection conn, MySqlTransaction trans)
{
if (obj != null)
{
try
{
string qry = @"INSERT INTO `users`(`firstname`, `lastname`, `roleid`, `status`, `username`, `password`, `email`,
`mobile`, `createdby`, `createdon`, `modifyby`, `modifyon`, `isdeleted`)
VALUES ('" + obj.Firstname + "','" + obj.Lastname + "'," + obj.Roleid + "," + obj.Status + ",'" + obj.Username +
"','" + obj.Password + "','" + obj.Email + "','" + obj.Mobile + "'," + obj.Createdby + "," + "STR_TO_DATE('" + obj.Createdon + "', '%c/%e/%Y %r')" +
"," + obj.Modifyby + ",STR_TO_DATE('" + obj.Modifyon + "', '%c/%e/%Y %r'),'F')";
dbconnect.GetScalar(conn, trans, qry, null);
return(1);
}
catch
{
throw new Exception();
}
}
return(0);
}
public Int32 Delete(BOL.users obj)
{
if (obj != null)
{
try
{
conn = new MySqlConnection(ConnectionString);
conn.Open();
trans = conn.BeginTransaction();
bll_users.Delete(obj, conn, trans);
trans.Commit();
conn.Close();
return(obj.Userid);
}
catch
{
trans.Rollback();
conn.Close();
}
}
return(0);
}
public List <BOL.users> Select(BOL.users obj)
{
if (obj != null)
{
conn = new MySqlConnection(ConnectionString);
conn.Open();
trans = conn.BeginTransaction();
DataSet ds = bll_users.Select(obj, conn, trans);
trans.Commit();
conn.Close();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
List <BOL.users> userses = new List <BOL.users>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
BOL.users c = new BOL.users();
if (dr["Userid"] != null && Convert.ToInt32(dr["Userid"]) > 0)
{
c.Userid = Convert.ToInt32(Convert.ToString(dr["Userid"]));
}
if (dr["Email"] != null && !string.IsNullOrEmpty(Convert.ToString(dr["Email"])))
{
c.Email = Convert.ToString(dr["Email"]);
}
if (dr["Firstname"] != null && !string.IsNullOrEmpty(Convert.ToString(dr["Firstname"])))
{
c.Firstname = Convert.ToString(dr["Firstname"]);
}
if (dr["Lastname"] != null && !string.IsNullOrEmpty(Convert.ToString(dr["Lastname"])))
{
c.Lastname = Convert.ToString(dr["Lastname"]);
}
if (dr["Password"] != null && !string.IsNullOrEmpty(Convert.ToString(dr["Password"])))
{
c.Password = Convert.ToString(dr["Password"]);
}
if (dr["Username"] != null && !string.IsNullOrEmpty(Convert.ToString(dr["Username"])))
{
c.Username = Convert.ToString(dr["Username"]);
}
if (dr["Roleid"] != null && Convert.ToInt32(dr["Roleid"]) > 0)
{
c.Roleid = Convert.ToInt32(Convert.ToString(dr["Roleid"]));
}
if (dr["Status"] != null && Convert.ToInt32(dr["Status"]) > 0)
{
c.Status = Convert.ToInt32(Convert.ToString(dr["Status"]));
}
if (dr["Createdby"] != null && Convert.ToInt32(dr["Createdby"]) > 0)
{
c.Createdby = Convert.ToInt32(Convert.ToString(dr["Createdby"]));
}
if (dr["Createdon"] != null && !string.IsNullOrEmpty(Convert.ToString(dr["Createdon"])))
{
c.Createdon = Convert.ToDateTime(Convert.ToString(dr["Createdon"]));
}
if (dr["Isdeleted"] != null && !string.IsNullOrEmpty(Convert.ToString(dr["Isdeleted"])))
{
c.Isdeleted = Convert.ToString(dr["Isdeleted"]);
}
if (dr["Modifyby"] != null && Convert.ToInt32(dr["Modifyby"]) > 0)
{
c.Modifyby = Convert.ToInt32(Convert.ToString(dr["Modifyby"]));
}
if (dr["Modifyon"] != null && !string.IsNullOrEmpty(Convert.ToString(dr["Modifyon"])))
{
c.Modifyon = Convert.ToDateTime(Convert.ToString(dr["Modifyon"]));
}
userses.Add(c);
}
return(userses);
}
return(null);
}
return(null);
}