public static void CreateCase(CaseDetailsModel casedetail)
{
try
{
using (SqlConnection con = new SqlConnection(connstr))
{
string query = "INSERT INTO [dbo].[Case] (caseid,casereceiveddate, category, summary, subject, casetype, casename) VALUES(@caseid, @casereceiveddate, @category, @summary, @subject, @casetype, @casename)";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
cmd.Parameters.AddWithValue("@caseid", casedetail.caseid);
cmd.Parameters.AddWithValue("@casename", casedetail.casename.Trim());
cmd.Parameters.AddWithValue("@casereceiveddate", casedetail.casereceiveddate.Trim());
cmd.Parameters.AddWithValue("@summary", casedetail.summary.Trim());
cmd.Parameters.AddWithValue("@subject", casedetail.subject.Trim());
cmd.Parameters.AddWithValue("@category", "Reschedule");
cmd.Parameters.AddWithValue("@casetype", casedetail.casetype.Trim());
casedetail.id = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
}
} catch (Exception ex) {
}
}
public static CaseDetailsModel GetCase(String id)
{
CaseDetailsModel caseDetail = new CaseDetailsModel();
try
{
using (SqlConnection con = new SqlConnection(connstr))
{
string query = "Select * from [dbo].[Case] where caseid = " + id;
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
caseDetail.caseid = Convert.ToString(rd.GetSqlValue(0));
caseDetail.casereceiveddate = Convert.ToString(rd.GetSqlValue(1));
caseDetail.summary = Convert.ToString(rd.GetSqlValue(2)).Trim();
caseDetail.subject = Convert.ToString(rd.GetSqlValue(3)).Trim();
caseDetail.casetype = Convert.ToString(rd.GetSqlValue(4));
caseDetail.casename = Convert.ToString(rd.GetSqlValue(5));
caseDetail.category = Convert.ToString(rd.GetSqlValue(7));
caseDetail.state = Convert.ToString(rd.GetSqlValue(8));
caseDetail.policestation = Convert.ToString(rd.GetSqlValue(9));
caseDetail.penalcode = Convert.ToString(rd.GetSqlValue(10));
caseDetail.prosecutor = Convert.ToString(rd.GetSqlValue(11));
caseDetail.policereport = Convert.ToString(rd.GetSqlValue(12));
caseDetail.jurisdiction = Convert.ToString(rd.GetSqlValue(13));
caseDetail.createdby = Convert.ToString(rd.GetSqlValue(14));
caseDetail.assignedto = Convert.ToString(rd.GetSqlValue(15));
caseDetail.courthouse = Convert.ToString(rd.GetSqlValue(16));
caseDetail.submittedby = Convert.ToString(rd.GetSqlValue(17));
caseDetail.status = Convert.ToString(rd.GetSqlValue(18));
caseDetail.casecharge = Convert.ToString(rd.GetSqlValue(19));
caseDetail.classification = Convert.ToString(rd.GetSqlValue(20));
}
con.Close();
}
}
}
catch (Exception ex)
{
}
return(caseDetail);
}
private static void update(CaseDetailsModel casedetail)
{
try
{
using (SqlConnection con = new SqlConnection(connstr))
{
string query = "Update [dbo].[Case] set category = @category where caseid = @caseid";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
cmd.Parameters.AddWithValue("@caseid", casedetail.caseid);
cmd.Parameters.AddWithValue("@category", casedetail.category.Trim());
cmd.ExecuteScalar();
con.Close();
}
}
}
catch (Exception ex)
{
}
}
public static List <CaseDetailsModel> GetCases()
{
CaseDetailsModel caseDetail;
List <CaseDetailsModel> caseList = new List <CaseDetailsModel>();
try
{
using (SqlConnection con = new SqlConnection(connstr))
{
string query = "Select * from [dbo].[Case]";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
caseDetail = new CaseDetailsModel();
caseDetail.caseid = Convert.ToString(rd.GetSqlValue(0));
caseDetail.casereceiveddate = Convert.ToString(rd.GetSqlValue(1));
caseDetail.casename = Convert.ToString(rd.GetSqlValue(5)).Trim();
caseDetail.subject = Convert.ToString(rd.GetSqlValue(3)).Trim();
caseDetail.summary = Convert.ToString(rd.GetSqlValue(2)).Trim();
caseDetail.category = Convert.ToString(rd.GetSqlValue(7));
caseDetail.casetype = Convert.ToString(rd.GetSqlValue(4)).Trim();
caseList.Add(caseDetail);
}
con.Close();
}
}
}
catch (Exception ex)
{
}
return(caseList);
}