public static async Task AddAsync(Customer customer, SupportCases supportcase)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) from customer where SSNumber = @SSN", conn))
{
conn.Open();
sqlCommand.Parameters.AddWithValue("@SSN", customer.SSNumber);
int userCount = (int)sqlCommand.ExecuteScalar();
if (userCount > 0)
{
var query = @"INSERT INTO SupportCases (CustomerNumber, Status, Description, Title, Category, Time)
VALUES(@CustomerNumber, @Status, @Description, @Title, @Category, @Time)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@CustomerNumber", customer.SSNumber);
cmd.Parameters.AddWithValue("@Status", supportcase.Status);
cmd.Parameters.AddWithValue("@Description", supportcase.Description);
cmd.Parameters.AddWithValue("@Title", supportcase.Title);
cmd.Parameters.AddWithValue("@Category", supportcase.Category);
cmd.Parameters.AddWithValue("@Time", supportcase.Time);
await cmd.ExecuteReaderAsync();
conn.Close();
}
else
{
var query = @"INSERT INTO Customer (SSNumber, Name, PhoneNumber, Email)
VALUES(@SSNumber, @Name, @PhoneNumber, @Email)
INSERT INTO SupportCases (CustomerNumber, Status, Description, Title, Category, Time)
VALUES(@CustomerNumber, @Status, @Description, @Title, @Category, @Time)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@CustomerNumber", customer.SSNumber);
cmd.Parameters.AddWithValue("@Status", supportcase.Status);
cmd.Parameters.AddWithValue("@Description", supportcase.Description);
cmd.Parameters.AddWithValue("@Title", supportcase.Title);
cmd.Parameters.AddWithValue("@Category", supportcase.Category);
cmd.Parameters.AddWithValue("@Time", supportcase.Time);
cmd.Parameters.AddWithValue("@SSNumber", customer.SSNumber);
cmd.Parameters.AddWithValue("@Name", customer.Name);
cmd.Parameters.AddWithValue("@PhoneNumber", customer.PhoneNumber);
cmd.Parameters.AddWithValue("@Email", customer.Email);
await cmd.ExecuteReaderAsync();
conn.Close();
}
}
}
}
private async void btnAddCase_Click(object sender, RoutedEventArgs e)
{
Customer customer = new Customer(Convert.ToInt32(tbSSNumber.Text), tbName.Text, Convert.ToInt32(tbPhoneNumber.Text), tbEmail.Text);
SupportCases supportcase = new SupportCases(tbDescription.Text, tbTitle.Text, tbCategory.Text);
await SharedLibrary.DataAccess.AddAsync(customer, supportcase);
tbSSNumber.Text = String.Empty;
tbName.Text = String.Empty;
tbPhoneNumber.Text = String.Empty;
tbEmail.Text = String.Empty;
tbDescription.Text = String.Empty;
tbTitle.Text = String.Empty;
tbCategory.Text = String.Empty;
}
public static List <CaseList> GetAll(string status1, string status2)
{
Customer customer = new Customer();
SupportCases supportcases = new SupportCases();
var SampleList = new List <CaseList>();
var task = Task.Run(async() => await ReadJson());
var number = task.Result.NumberOfItems;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
var query = "SELECT TOP (@Limit) * FROM SupportCases INNER JOIN Customer ON Customer.SSNumber = SupportCases.CustomerNumber WHERE SupportCases.Status IN (@Status1, @Status2) ORDER BY SupportCases.CaseNumber DESC;";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@Limit", number);
cmd.Parameters.AddWithValue("@Status1", status1);
cmd.Parameters.AddWithValue("@Status2", status2);
var result = cmd.ExecuteReader();
while (result.Read())
{
int CaseNumber = result.GetInt32(0);
int CustomerNumber = result.GetInt32(1);
string Status = result.GetString(2);
string Description = result.GetString(3);
string Title = result.GetString(4);
string Category = result.GetString(5);
DateTime Time = result.GetDateTime(6);
int SSN = result.GetInt32(7);
string Name = result.GetString(8);
int PhoneNumber = result.GetInt32(9);
string Email = result.GetString(10);
customer = new Customer(SSN, Name, PhoneNumber, Email);
supportcases = new SupportCases(CaseNumber, CustomerNumber, Status, Description, Title, Category, Time);
SampleList.Add(new CaseList(customer, supportcases));
}
conn.Close();
return(SampleList);
}
}