public List <QuoteHeader> GetQuoteHeaderRepo()
{
List <QuoteHeader> header_list = new List <QuoteHeader>();
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
if (conn == null)
{
throw new Exception("Connection String is Null. Set the value of Connection String in ->Properties-?Settings.settings");
}
SQLiteCommand query = new SQLiteCommand("SELECT * FROM QTE_HDR", conn);
conn.Open();
SQLiteDataAdapter sqlDataAdapter = new SQLiteDataAdapter(query);
DataTable dt = new DataTable();
sqlDataAdapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
QuoteHeader q = new QuoteHeader();
q.jobno = row["jobno"].ToString();
//if(!string.IsNullOrEmpty(row["qt_date"].ToString())){ q.qt_date = DateTime.Parse(row["qt_date"].ToString());}
q.qt_date = row["qt_date"].ToString();
q.cust = row["cust"].ToString();
q.cust_contact = row["cust_contact"].ToString();
q.cust_phone = row["cust_phone"].ToString();
q.cust_email = row["cust_email"].ToString();
q.loc = row["loc"].ToString();
q.salesman = row["salesman"].ToString();
q.days_est = row["days_est"] is DBNull ? 0 : Convert.ToInt32(row["days_est"]);
q.status = row["status"].ToString();
q.pipe_line_size = row["pipe_line_size"].ToString();
q.pipe_length = row["pipe_length"].ToString();
q.pressure = row["pressure"].ToString();
q.endclient = row["endclient"].ToString();
q.supervisor = row["supervisor"].ToString();
//if(!string.IsNullOrEmpty(row["est_start_date"].ToString())){ q.est_start_date = DateTime.Parse(row["est_start_date"].ToString());}
q.est_start_date = row["est_start_date"].ToString();
//if(!string.IsNullOrEmpty(row["est_stop_date"].ToString())){ q.est_stop_date = DateTime.Parse(row["est_stop_date"].ToString());}
q.est_stop_date = row["est_stop_date"].ToString();
q.value = row["value"] is DBNull ? 0 : Convert.ToDouble(row["value"]);
header_list.Add(q);
}
return(header_list);
}
}
public void addNewQuoteHeaderItem(QuoteHeader NewquoteHeaderItem)
{
try
{
connection = new SQLiteConnection(connectionString);
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = string.Format("SELECT * FROM QTE_HDR");
adapter = new SQLiteDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "QTE_HDR");
DataRow HeaderTableRow = ds.Tables["QTE_HDR"].NewRow();
HeaderTableRow["jobno"] = NewquoteHeaderItem.jobno;
HeaderTableRow["qt_date"] = NewquoteHeaderItem.qt_date;
HeaderTableRow["cust"] = NewquoteHeaderItem.cust;
HeaderTableRow["cust_contact"] = NewquoteHeaderItem.cust_contact;
HeaderTableRow["cust_phone"] = NewquoteHeaderItem.cust_phone;
HeaderTableRow["cust_email"] = NewquoteHeaderItem.cust_email;
HeaderTableRow["loc"] = NewquoteHeaderItem.loc;
HeaderTableRow["salesman"] = NewquoteHeaderItem.salesman;
HeaderTableRow["days_est"] = NewquoteHeaderItem.days_est;
HeaderTableRow["status"] = NewquoteHeaderItem.status;
HeaderTableRow["jobtype"] = NewquoteHeaderItem.jobtype;
HeaderTableRow["pipe_line_size"] = NewquoteHeaderItem.pipe_line_size;
HeaderTableRow["pipe_length"] = NewquoteHeaderItem.pipe_length;
HeaderTableRow["pressure"] = NewquoteHeaderItem.pressure;
HeaderTableRow["endclient"] = NewquoteHeaderItem.endclient;
HeaderTableRow["supervisor"] = NewquoteHeaderItem.supervisor;
HeaderTableRow["est_start_date"] = NewquoteHeaderItem.est_start_date;
HeaderTableRow["est_stop_date"] = NewquoteHeaderItem.est_stop_date;
HeaderTableRow["value"] = NewquoteHeaderItem.value;
adapter.InsertCommand = new SQLiteCommandBuilder(adapter).GetInsertCommand();
ds.Tables["QTE_HDR"].Rows.Add(HeaderTableRow);
adapter.Update(ds, "QTE_HDR");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
connection.Close();
connection.Dispose();
}
}
public void deleteQuoteHeaderRecord(QuoteHeader quoteRecord)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
if (conn == null)
{
throw new Exception("Connection String is Null.");
}
SQLiteCommand query = new SQLiteCommand("deleteRecord", conn);
conn.Open();
query.CommandType = CommandType.StoredProcedure;
SQLiteParameter param1 = new SQLiteParameter("jobno", SqlDbType.VarChar);
param1.Value = quoteRecord.jobno;
query.Parameters.Add(param1);
query.ExecuteNonQuery();
}
}
public void updateQuoteHeaderRecord(QuoteHeader quoteRecord)
{
}
public void addNewRecord(QuoteHeader quoteRecord)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
if (conn == null)
{
throw new Exception("Connection String is Null. Set the value of Connection String in MovieCatalog->Properties-?Settings.settings");
}
else if (quoteRecord == null)
{
throw new Exception("The passed argument 'movieRecord' is null");
}
SQLiteCommand query = new SQLiteCommand("addRecord", conn);
conn.Open();
query.CommandType = CommandType.StoredProcedure;
SQLiteParameter param1 = new SQLiteParameter("jobno", SqlDbType.VarChar);
SQLiteParameter param2 = new SQLiteParameter("qt_date", SqlDbType.VarChar);
SQLiteParameter param3 = new SQLiteParameter("cust", SqlDbType.VarChar);
SQLiteParameter param4 = new SQLiteParameter("cust_contact", SqlDbType.VarChar);
SQLiteParameter param5 = new SQLiteParameter("cust_phone", SqlDbType.VarChar);
SQLiteParameter param6 = new SQLiteParameter("cust_email", SqlDbType.VarChar);
SQLiteParameter param7 = new SQLiteParameter("loc", SqlDbType.VarChar);
SQLiteParameter param8 = new SQLiteParameter("salesman", SqlDbType.VarChar);
SQLiteParameter param9 = new SQLiteParameter("days_est", SqlDbType.Int);
SQLiteParameter param10 = new SQLiteParameter("status", SqlDbType.VarChar);
SQLiteParameter param11 = new SQLiteParameter("pipe_line_size", SqlDbType.VarChar);
SQLiteParameter param12 = new SQLiteParameter("pipe_length", SqlDbType.VarChar);
SQLiteParameter param13 = new SQLiteParameter("pressure", SqlDbType.VarChar);
SQLiteParameter param14 = new SQLiteParameter("endclient", SqlDbType.VarChar);
SQLiteParameter param15 = new SQLiteParameter("supervisor", SqlDbType.VarChar);
SQLiteParameter param16 = new SQLiteParameter("est_start_date", SqlDbType.VarChar);
SQLiteParameter param17 = new SQLiteParameter("est_end_date", SqlDbType.VarChar);
SQLiteParameter param18 = new SQLiteParameter("value", SqlDbType.Real);
param1.Value = quoteRecord.jobno;
param2.Value = quoteRecord.qt_date;
param3.Value = quoteRecord.cust;
param4.Value = quoteRecord.cust_contact;
param5.Value = quoteRecord.cust_phone;
param6.Value = quoteRecord.cust_email;
param7.Value = quoteRecord.loc;
param8.Value = quoteRecord.salesman;
param9.Value = quoteRecord.days_est;
param10.Value = quoteRecord.status;
param11.Value = quoteRecord.pipe_line_size;
param12.Value = quoteRecord.pipe_length;
param13.Value = quoteRecord.pressure;
param14.Value = quoteRecord.endclient;
param15.Value = quoteRecord.supervisor;
param16.Value = quoteRecord.est_startdate;
param17.Value = quoteRecord.est_enddate;
param18.Value = quoteRecord.value;
query.Parameters.Add(param1);
query.Parameters.Add(param2);
query.Parameters.Add(param3);
query.Parameters.Add(param4);
query.Parameters.Add(param5);
query.Parameters.Add(param6);
query.Parameters.Add(param7);
query.Parameters.Add(param8);
query.Parameters.Add(param9);
query.Parameters.Add(param10);
query.Parameters.Add(param11);
query.Parameters.Add(param12);
query.Parameters.Add(param13);
query.Parameters.Add(param14);
query.Parameters.Add(param15);
query.Parameters.Add(param16);
query.Parameters.Add(param17);
query.Parameters.Add(param18);
query.ExecuteNonQuery();
}
}
