public Services GetService(int id) { Services ins = new Services(); //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT * FROM Services WHERE ServiceId =" + id, con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins.ServiceId = Convert.ToInt32(drI["ServiceId"]); ins.Service = drI["Service"].ToString(); ins.Query = drI["Query"].ToString(); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ClientId = Convert.ToInt32(drI["ClientId"]); ins.DeviceUserId = Convert.ToInt32(drI["DeviceUserId"]); } } drI.Close(); con.Close(); return ins; }
public Services AddServices(Services ins) { string strTrx = "AddService"; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); //...Command Interface... SqlCommand cmdI = con.CreateCommand(); SqlTransaction trx; trx = con.BeginTransaction(strTrx); cmdI.Connection = con; cmdI.Transaction = trx; try { //...Insert Record... cmdI.CommandText = "f_Admin_Insert_Service"; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@ClientId", ins.ClientId); cmdI.Parameters.AddWithValue("@DeviceUserId", ins.DeviceUserId); cmdI.Parameters.AddWithValue("@Service", ins.Service); cmdI.Parameters.AddWithValue("@Query", ins.Query); cmdI.Parameters.AddWithValue("@ModifiedDate", ins.ModifiedDate); // int, // int //...Return new ID ins.ServiceId = (int)cmdI.ExecuteScalar(); trx.Commit(); cmdI.Connection.Close(); } catch (SqlException ex) { if (trx != null) trx.Rollback(); //throw ex; //Console.WriteLine("Rollback Exception Type: {0}", ex.GetType()); //Console.WriteLine(" Message: {0}", ex.Message); } finally { //Check for close and respond accordingly if (con.State != ConnectionState.Closed) { con.Close(); } //Clean up con.Dispose(); cmdI.Dispose(); trx.Dispose(); } return ins; }
public Services UpdateServices(Services ins) { //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); SqlCommand cmdI = con.CreateCommand(); cmdI.Connection = con; //...Update Record... cmdI.Parameters.Clear(); cmdI.CommandText = "f_Admin_Update_Services"; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@ServiceId", ins.ServiceId); cmdI.Parameters.AddWithValue("@ClientId", ins.ClientId); cmdI.Parameters.AddWithValue("@DeviceUserId", ins.DeviceUserId); cmdI.Parameters.AddWithValue("@Service", ins.Service); cmdI.Parameters.AddWithValue("@Query", ins.Query); cmdI.Parameters.AddWithValue("@ModifiedDate", ins.ModifiedDate); cmdI.ExecuteNonQuery(); cmdI.Connection.Close(); return ins; }
public List<Services> GetServices(int ClientId) { List<Services> lsServicesList = new List<Services>(); Services ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT S.*, D.Name, D.Surname, D.Phone, D.Email FROM Services S INNER JOIN DeviceUsers D ON S.DeviceUserId = D.DeviceUserId WHERE S.ClientId=" + ClientId, con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new Services(); ins.ServiceId = Convert.ToInt32(drI["ServiceId"]); ins.Service = drI["Service"].ToString(); ins.Query = drI["Query"].ToString(); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ClientId = Convert.ToInt32(drI["ClientId"]); ins.DeviceUserId = Convert.ToInt32(drI["DeviceUserId"]); ins.UserName = drI["Name"].ToString() + " " + drI["Surname"].ToString(); ins.Phone = drI["Phone"].ToString(); ins.Email = drI["Email"].ToString(); lsServicesList.Add(ins); } } drI.Close(); con.Close(); return lsServicesList; }
public ActionResult RequestService(int DeviceUserId, string ServiceRequest, string Query) { DeviceUserRepository DevURep = new DeviceUserRepository(); ServicesRepository ServRep = new ServicesRepository(); Services service = new Services(); DeviceUser user = DevURep.GetDeviceUser(DeviceUserId); service.ClientId = user.ClientId; service.DeviceUserId = DeviceUserId; service.Service = ServiceRequest; service.Query = Query; service.ModifiedDate = DateTime.Now; StringBuilder w = new StringBuilder(); w.Append(user.Name).Append(" ").Append(user.Surname).Append("\n"); w.Append("Address: ").Append(user.Address).Append("\n"); w.Append("Contact Details: Phone - ").Append(user.Phone).Append(" Email - ").Append(user.Email).Append("\n"); w.Append("Request: ").Append(service.Service); w.Append("\n").Append(service.Query); Services ins = ServRep.AddServices(service); if (ins.ServiceId != 0) { //Send email Functions f = new Functions(); //Get Client Service Mail List List<Settings> mails = setRep.GetSettings("email", user.ClientId); if (mails.Count >= 1) { StringBuilder maillist = new StringBuilder(); foreach (Settings setting in mails) { maillist.Append(setting.Value).Append(","); } maillist.Remove(maillist.Length - 1, 1); f.SendEmail(w.ToString(), maillist.ToString(), service.Service); } return Content("Success", "text/html"); } else { return Content("Fail", "text/html"); } }