private void frmCreateIncident_Load(object sender, EventArgs e) { TechSupportDB.SetConnectionString(ConfigurationManager. ConnectionStrings["SportsPro.Properties.Settings.TechSupportConnectionString"]. ConnectionString); PopulateComboBoxes(); }
private void frmOpenIncidents_Load(object sender, EventArgs e) { TechSupportDB.SetConnectionString(ConfigurationManager. ConnectionStrings["SportsPro.Properties.Settings.TechSupportConnectionString"]. ConnectionString); //TechSupportDB.SetConnectionString("Data Source=localhost;Initial Catalog=TechSupportx;Integrated Security=True"); PopulateIncidentsList(); }
private void frmTechnicianIncidents_Load(object sender, EventArgs e) { TechSupportDB.SetConnectionString(ConfigurationManager. ConnectionStrings["SportsPro.Properties.Settings.TechSupportConnectionString"]. ConnectionString); technicianList = TechnicianDB.GetTechnicianList(); nameComboBox.DataSource = technicianList; nameComboBox.SelectedIndex = 0; }
public static IEnumerable GetAllTechnicians() { SqlConnection connection = new SqlConnection(TechSupportDB.GetConnectionString()); string select = "SELECT TechID, Name FROM Technicians ORDER BY Name"; SqlCommand cmd = new SqlCommand(select, connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return(reader); }
public static IEnumerable GetCustomerIncidents(int CustomerID) { SqlConnection connection = new SqlConnection(TechSupportDB.GetConnectionString()); string select = "SELECT IncidentID, ProductCode, DateOpened, DateClosed, Title, Description FROM Incidents WHERE TechID IS NOT NULL AND CustomerID=@CustomerID"; SqlCommand cmd = new SqlCommand(select, connection); cmd.Parameters.AddWithValue("CustomerID", CustomerID); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return(reader); }
public static IEnumerable GetOpenIncidents(int TechID) { SqlConnection connection = new SqlConnection(TechSupportDB.GetConnectionString()); string select = "SELECT DateOpened, ProductCode, Name FROM Incidents JOIN Customers ON Incidents.CustomerID = Customers.CustomerID WHERE DateClosed IS NULL AND TechID=@TechID ORDER BY DateOpened"; SqlCommand cmd = new SqlCommand(select, connection); cmd.Parameters.AddWithValue("TechID", TechID); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return(reader); }
public static IEnumerable GetAllTechinicians() { SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string sel = "SELECT TechID,Name , Email, Phone " + "FROM Technicians ORDER BY Name"; SqlCommand cmd = new SqlCommand(sel, con); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return(rdr); }
public List <Incident> GetOpenIncidents() { SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); string selectStatement = "Select ProductCode, DateOpened,Customers.Name,Technicians.Name AS Technician,Title FROM Incidents left outer join Technicians on Incidents.TechID = Technicians.TechID inner JOIN Customers on Incidents.CustomerID = Customers.CustomerID WHERE DateClosed is NULL"; aCommand.CommandText = selectStatement; aCommand.CommandType = CommandType.Text; List <Incident> listOfIncidents = new List <Incident>(); try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader (CommandBehavior.CloseConnection); while (aReader.Read()) { Incident anIncident = new Incident(); anIncident.ProductCode = aReader["ProductCode"].ToString(); anIncident.DateOpened = (DateTime)aReader["DateOpened"]; anIncident.CustomerName = aReader["Technician"].ToString(); anIncident.TechName = aReader["Technician"].ToString(); anIncident.Title = aReader["Title"].ToString(); listOfIncidents.Add(anIncident); } return(listOfIncidents); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } }
public static IEnumerable GetCustomerList() { SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string sel = "SELECT CustomerID, Name " + "FROM Customers " + "ORDER BY Name"; SqlCommand cmd = new SqlCommand(sel, con); con.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return(dr); }
protected void Button1_Click(object sender, EventArgs e) { Random rnd = new Random(); int id = rnd.Next(1, 10000); string incident2 = Session["Chosen"].ToString(); string comment = TextBox1.Text.ToString() + " - " + Session["name"].ToString(); string statement = "INSERT INTO Forum (ForumID, comment, IncidentID) VALUES (" + "'" + id + "'" + ", " + "'" + comment + "'" + ", " + "'" + incident2 + "'" + ")"; SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); SqlCommand cmd = new SqlCommand(statement, con); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); Response.Redirect("IncidentForum.aspx"); }
public static IEnumerable GetCustomerIncidents(int CustomerID) { SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string sel = "SELECT IncidentID, ProductCode, " + "DateOpened, DateClosed, Title, Description " + "FROM Incidents " + "WHERE CustomerID = @CustomerID " + "AND DateClosed IS NULL"; SqlCommand cmd = new SqlCommand(sel, con); cmd.Parameters.AddWithValue("CustomerID", CustomerID); con.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return(dr); }
public static IEnumerable GetCustomersWithIncidents() { SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string sel = "SELECT CustomerID, Name " + "FROM Customers " + "WHERE CustomerID IN " + "(SELECT DISTINCT CustomerID " + "FROM Incidents " + "WHERE DateClosed IS NULL) " + "ORDER BY Name"; SqlCommand cmd = new SqlCommand(sel, con); con.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return(dr); }
protected void Button1_Click(object sender, EventArgs e) { Boolean taken = true; string address = TextBox1.Text.ToString(); string email = TextBox2.Text.ToString(); string password = TextBox3.Text.ToString(); string name = TextBox9.Text.ToString() + " " + TextBox10.Text.ToString(); string state = TextBox5.Text.ToString(); string city = TextBox6.Text.ToString(); string zipcode = TextBox7.Text.ToString(); string phonenum = TextBox8.Text.ToString(); string role = "customer"; Random rnd = new Random(); int customerID = rnd.Next(1000, 10000); SqlConnection con1 = new SqlConnection(TechSupportDB.GetConnectionString()); con1.Open(); string checker = "SELECT * FROM Customers WHERE Email = " + "'" + email + "'"; SqlCommand comand1 = new SqlCommand(checker, con1); SqlDataReader reader1 = comand1.ExecuteReader(CommandBehavior.CloseConnection); if (reader1.HasRows) { con1.Close(); errLabel.Text = "Email Already in Use"; } else { string statement = "INSERT INTO Customers (Name, Address, City, State, ZipCode, Phone, Email, Password, Role) VALUES (" + "'" + name + "'" + ", " + "'" + address + "'" + ", " + "'" + city + "'" + ", " + "'" + state + "'" + ", " + "'" + zipcode + "'" + ", " + "'" + phonenum + "'" + ", " + "'" + email + "'" + ", " + "'" + password + "'" + ", " + "'" + role + "'" + ")"; SqlConnection con2 = new SqlConnection(TechSupportDB.GetConnectionString()); SqlCommand comand2 = new SqlCommand(statement, con2); con2.Open(); SqlDataReader reader2 = comand2.ExecuteReader(CommandBehavior.CloseConnection); con2.Close(); Session["Role"] = "customer"; Session["Name"] = name; Session["Eamil"] = email; string AccountSid = "ACb5e12563bfb0848030ce8b4218a4f468"; string AuthToken = "09396ddfe43280a9c661820d7abdfac8"; TwilioRestClient twilio = new TwilioRestClient(AccountSid, AuthToken); twilio.SendMessage("17085057088", phonenum, "Welcome to SportPro " + name + " Your Email is: " + email + " and Password is: " + password + " You will need these for future Logins!", ""); Response.Redirect("ContactUs.aspx"); } return; }
protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string name = DropDownList1.Text.ToString(); string sel = "UPDATE customers " + "SET Role = 'admin' " + "WHERE name = " + "'" + name + "'"; SqlCommand cmd = new SqlCommand(sel, con); con.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dr.Read(); DropDownList1.DataBind(); Label1.Text = "Success!"; return; }
public static IEnumerable GetOpenTechIncidents(int techID) { SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string sel = "SELECT DateOpened, ProductCode, Name " + "FROM Incidents JOIN Customers " + "ON Incidents.CustomerID = Customers.CustomerID " + "WHERE DateClosed IS NULL " + "AND TechID = @TechID " + "ORDER BY DateOpened"; SqlCommand cmd = new SqlCommand(sel, con); cmd.Parameters.AddWithValue("TechID", techID); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return(rdr); }
public static IEnumerable GetOpenTechIncidents(int techID) { SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string sel = "SELECT Customers.Name,Incidents.ProductCode,Incidents.DateOpened,Incidents.TechID " + " FROM Customers INNER JOIN " + " Incidents ON Customers.CustomerID = Incidents.CustomerID " + " WHERE(Incidents.TechID = @TechID)" + " ORDER BY Incidents.DateOpened DESC"; SqlCommand cmd = new SqlCommand(sel, con); cmd.Parameters.AddWithValue("TechID", techID); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return(rdr); }
public bool ProductRegistration(int customerID, string productCode) { Registration aRegistration = new Registration(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = "Select CustomerID, ProductCode FROM Registrations WHERE CustomerID = @CustomerID AND ProductCode = @ProductCode"; aCommand.CommandType = CommandType.Text; aCommand.Parameters.AddWithValue("@CustomerID", customerID); aCommand.Parameters.AddWithValue("@ProductCode", productCode); try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection); if (aReader.Read()) { aRegistration.CustomerID = (int)aReader["CustomerID"]; aRegistration.ProductCode = aReader["ProductCode"].ToString(); return(true); } else { aRegistration = null; return(false); } } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } }
public Product GetProductName(String productCode) { Product aProduct = new Product(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = " select Name from Products Where ProductCode = @ProductCode "; aCommand.CommandType = CommandType.Text; aCommand.Parameters.AddWithValue("@ProductCode", productCode); try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection); if (aReader.Read()) { aProduct.Name = aReader["Name"].ToString(); aProduct.ProductCode = aReader["ProductCode"].ToString(); } else { aProduct = null; } aReader.Close(); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } return(aProduct); }
public Customer GetCustomerName(int customerID) { Customer aCustomer = new Customer(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = " select Name from Customers Where CustomerID = @CustomerID "; aCommand.CommandType = CommandType.Text; aCommand.Parameters.AddWithValue("@CustomerID", customerID); try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection); if (aReader.Read()) { aCustomer.Name = aReader["Name"].ToString(); } else { aCustomer = null; } aReader.Close(); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } return(aCustomer); }
public List <Customer> GetCustomerList() { List <Customer> listOfCustomers = new List <Customer>(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = "SELECT CustomerID, Name FROM Customers"; aCommand.CommandType = CommandType.Text; try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader (CommandBehavior.CloseConnection); while (aReader.Read()) { Customer aCustomer = new Customer(); aCustomer.CustomerID = (int)aReader["CustomerID"]; aCustomer.Name = aReader["Name"].ToString(); listOfCustomers.Add(aCustomer); } aReader.Close(); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } return(listOfCustomers); }
public List <Product> GetProductsList() { List <Product> listOfProducts = new List <Product>(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = "SELECT Name, ProductCode FROM Products"; aCommand.CommandType = CommandType.Text; try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection); while (aReader.Read()) { Product aProduct = new Product(); aProduct.Name = aReader["Name"].ToString(); //aProduct.Name = aReader["ProductCode"].ToString();\ aProduct.ProductCode = aReader["ProductCode"].ToString(); listOfProducts.Add(aProduct); } aReader.Close(); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } return(listOfProducts); }
protected void btnGetIncidents_Click(object sender, EventArgs e) { int customerID = Convert.ToInt32(txtCustomerID.Text.ToString()); SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string selectStatement = "SELECT * " + "FROM Incidents " + "WHERE DateClosed IS NOT NULL AND CustomerID = @CustomerID"; SqlCommand command = new SqlCommand(selectStatement, con); command.Parameters.AddWithValue("CustomerID", customerID); con.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); if (reader.HasRows) { lstIncidents.Items.Add(new ListItem("--Select an incident--", "None")); while (reader.Read()) { Partial_Incident incident = new Partial_Incident(); incident.IncidentID = Convert.ToInt32(reader["IncidentID"]); incident.ProductCode = reader["ProductCode"].ToString(); incident.DateClosed = Convert.ToDateTime(reader["DateClosed"]); incident.Title = reader["Title"].ToString(); lstIncidents.Items.Add(new ListItem( incident.CustomerIncidentDisplay(), incident.IncidentID.ToString())); } lstIncidents.SelectedIndex = 0; lblNoIncidents.Text = ""; this.EnableControls(true); lstIncidents.Focus(); } else { lblNoIncidents.Text = "There are no incidents for that customer."; this.EnableControls(false); } }
protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string name = DropDownList1.Text.ToString(); string sel = "SELECT Phone " + "FROM Customers " + "WHERE Name = " + "'" + name + "'"; SqlCommand cmd = new SqlCommand(sel, con); con.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dr.Read(); string number = dr["Phone"].ToString(); string AccountSid = "ACb5e12563bfb0848030ce8b4218a4f468"; string AuthToken = "09396ddfe43280a9c661820d7abdfac8"; string message = TextBox1.Text.ToString(); TwilioRestClient twilio = new TwilioRestClient(AccountSid, AuthToken); twilio.SendMessage("17085057088", number, message, ""); TextBox1.Text = String.Empty; return; }
protected void dtlCustomers_Deleted(object sender, DetailsViewDeletedEventArgs e) { string ID = (dtlCustomers.Rows[0].Cells[1].Text.ToString()); int ID1 = Int32.Parse(ID); SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString()); string sel = "DELETE " + "FROM Customers " + "WHERE CustomerID = '" + ID1 + "'"; SqlCommand cmd = new SqlCommand(sel, con); con.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); con.Close(); if (e.Exception != null) { lblErrorMessage.Text = "A database error has occurred. "; e.ExceptionHandled = true; } else { maintainCustomers.DataBind(); } }