using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using CertAuth.Domain; using Microsoft.Data.SqlClient; using Microsoft.AspNetCore.Mvc.Rendering; namespace CertAuthAdmin.Models { public class UsersDA { //string connStr = "Data Source=smartcuredbserver.database.windows.net;Initial Catalog=Quartexxdb;Persist Security Info=True;User ID=SmartCureAdmin;Password=NApex135!"; //private string connStr = "Data Source=smartcuredbserver.database.windows.net;Initial Catalog=Quartexxdb;Persist Security Info=True;User ID=SmartCureAdmin;Password=NApex135!"; public List tbl { get; set; } private List Sli { get; set; } public IEnumerable GetAll() { string connStr = Globals.connStr; List Userlst; IEnumerable Userlist; Userlst = new List(); using (SqlConnection con = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand("Users_GetAll", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { User usr = new User(); usr.UserID = rdr["UserID"].ToString(); usr.LastName = rdr["LastName"].ToString(); usr.FirstName = rdr["FirstName"].ToString(); usr.UserType = rdr["UserType"].ToString(); usr.OrganizationID = rdr["OrganizationID"].ToString(); usr.TagID = rdr["TagID"].ToString(); usr.PhotoBLOB = rdr["PhotoBLOB"].ToString(); usr.EmployeeSecurity = rdr["EmployeeSecurity"].ToString(); usr.Passphrase = rdr["Passphrase"].ToString(); usr.Status = rdr["Status"].ToString(); usr.TimeStamp = (DateTime)rdr["TimeStamp"]; Userlst.Add(usr); } con.Close(); } Userlist = Userlst; return Userlist; } public IEnumerable GetAll_WD() { string connStr = Globals.connStr; List Userlst; IEnumerable Userlist; Userlst = new List(); using (SqlConnection con = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand("Users_GetAll_WD", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { User_WD usr = new User_WD(); usr.UserID = rdr["UserID"].ToString(); usr.LastName = rdr["LastName"].ToString(); usr.FirstName = rdr["FirstName"].ToString(); usr.UserType = rdr["UserType"].ToString(); usr.UsrTypeDesc = rdr["UsrTypeDesc"].ToString(); usr.OrganizationID = rdr["OrganizationID"].ToString(); usr.OrgDesc = rdr["OrgDesc"].ToString(); usr.TagID = rdr["TagID"].ToString(); usr.PhotoBLOB = rdr["PhotoBLOB"].ToString(); usr.EmployeeSecurity = rdr["EmployeeSecurity"].ToString(); usr.SecurityDesc = rdr["SecurityDesc"].ToString(); usr.Passphrase = rdr["Passphrase"].ToString(); usr.Status = rdr["Status"].ToString(); usr.StatusDesc = rdr["StatusDesc"].ToString(); usr.TimeStamp = (DateTime)rdr["TimeStamp"]; Userlst.Add(usr); } con.Close(); } Userlist = Userlst; return Userlist; } public List GetSlctItms() { tbl = GetAll().ToList(); Sli = new List(); foreach (var itm in tbl) { Sli.Add(new SelectListItem() { Value = itm.UserID, Text = itm.LastName.Trim() + ", " + itm.FirstName.Trim() }); } return Sli; } public bool InsertUser(User UsrNew) { string connStr = Globals.connStr; bool rslt = false; int rsltCnt = 0; using (SqlConnection con = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand("Users_Insert", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@userID", UsrNew.UserID); cmd.Parameters.AddWithValue("@OID", UsrNew.OID); cmd.Parameters.AddWithValue("@VendorKey", 0); cmd.Parameters.AddWithValue("@LastName", UsrNew.LastName); cmd.Parameters.AddWithValue("@FirstName", UsrNew.FirstName); cmd.Parameters.AddWithValue("@UserType", UsrNew.UserType); cmd.Parameters.AddWithValue("@OrganizationID", ""); cmd.Parameters.AddWithValue("@EmployeeSecurity", UsrNew.EmployeeSecurity); cmd.Parameters.AddWithValue("@Status", UsrNew.Status); cmd.Parameters.AddWithValue("@TimeStamp", UsrNew.TimeStamp); cmd.Parameters.AddWithValue("@TagID", ""); cmd.Parameters.AddWithValue("@PhotoBlob", ""); cmd.Parameters.AddWithValue("@PassPhrase", ""); con.Open(); try { rsltCnt = cmd.ExecuteNonQuery(); } catch (Exception ex) { rslt = false; } con.Close(); if (rsltCnt == 1) { rslt = true; } return rslt; } } public bool UpdateUser(User updUser) { string connStr = Globals.connStr; bool rslt = false; int rsltCnt = 0; using (SqlConnection con = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand("Users_Update", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@userID", updUser.UserID); cmd.Parameters.AddWithValue("@OID", updUser.OID); cmd.Parameters.AddWithValue("@VendorKey", 0); cmd.Parameters.AddWithValue("@LastName", updUser.LastName); cmd.Parameters.AddWithValue("@FirstName", updUser.FirstName); cmd.Parameters.AddWithValue("@UserType", updUser.UserType); cmd.Parameters.AddWithValue("@OrganizationID", ""); cmd.Parameters.AddWithValue("@EmployeeSecurity", updUser.EmployeeSecurity); cmd.Parameters.AddWithValue("@Status", updUser.Status); cmd.Parameters.AddWithValue("@TimeStamp", updUser.TimeStamp); cmd.Parameters.AddWithValue("@TagID", ""); cmd.Parameters.AddWithValue("@PhotoBlob", ""); cmd.Parameters.AddWithValue("@PassPhrase", ""); con.Open(); try { rsltCnt = cmd.ExecuteNonQuery(); } catch (Exception ex) { rslt = false; } con.Close(); if (rsltCnt == 1) { rslt = true; } return rslt; } } //public IEnumerable GetAll //{ // //string ConnStr = UsersDA.configuration(GetConnectionString("QuartexDB")); // public List lstUsers = new List(); // using (SqlConnection con = new SqlConnection(connStr)) // { // } //} } }