public void ImportCustomersfromdb()
{
ofDialog = new OpenFileDialog();
ofDialog.DefaultExt = "db";
if (DialogResult.OK == ofDialog.ShowDialog())
{
string query;
DataTable dds = new DataTable();
using SQLiteConnection iconn = new SQLiteConnection("Data Source=" + ofDialog.FileName + ";Version=3;");
{
query = "select * from customers";
SQLiteDataAdapter dd = new SQLiteDataAdapter(query, iconn);
iconn.Open();
dd.Fill(dds); // dds has records from customertable
}
customersTable cBLL;
foreach (DataRow dr in dds.Rows)
{
cBLL = new customersTable();
cBLL.name = dr[1].ToString();
cBLL.email = dr[2].ToString();
cBLL.contact = dr[4].ToString();
cBLL.address = dr[5].ToString() + " " + dr[6].ToString() + "\r\n" + dr[7].ToString() + ", New York " + dr[8].ToString();
cBLL.taxable = Convert.ToInt32(dr[3].ToString());
customers.Insert(cBLL);
}
MessageBox.Show("All Set!");
}
}
public bool Insert(customersTable dc)
{
//Creting SQL Connection First
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Create and Boolean value and set its default value to false
bool isSuccess = false;
try
{
//Write SQL Query to Insert Details of Customer
string sql = "INSERT INTO tbl_customers (taxable, name, email, contact, address, city, state, zip) VALUES (@taxable, @name, @email, @contact, @address, @city, @state, @zip)";
//SQl Command to Pass the values to query and execute
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//Passing the calues using Parameters
cmd.Parameters.AddWithValue("@taxable", dc.taxable);
cmd.Parameters.AddWithValue("@name", dc.name);
cmd.Parameters.AddWithValue("@email", dc.email);
cmd.Parameters.AddWithValue("@contact", dc.contact);
cmd.Parameters.AddWithValue("@address", dc.address);
cmd.Parameters.AddWithValue("@city", dc.city);
cmd.Parameters.AddWithValue("@state", dc.state);
cmd.Parameters.AddWithValue("@zip", dc.zip);
//Open DAtabaseConnection
conn.Open();
//Int variable to check whether the query is executed successfully or not
int rows = cmd.ExecuteNonQuery();
//If the query is executed successfully then the value of rows will be greater than 0 else it will be less than 0
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public customersTable SearchDealerCustomerForTransaction(string keyword)
{
//Create an object for DeaCustBLL class
customersTable dc = new customersTable();
//Create a DAtabase Connection
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Create a DAta Table to hold the value temporarily
DataTable dt = new DataTable();
try
{
//Write a SQL Query to Search Customer Based on Keywords
string sql = "SELECT * from tbl_customers WHERE id LIKE @keyword OR name LIKE @keyword";
//Create a Sql Data Adapter to Execute the Query
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
adapter.SelectCommand.Parameters.AddWithValue("@keyword", keyword);
adapter.SelectCommand.Parameters.AddWithValue("@keyword", keyword);
//Open the DAtabase Connection
conn.Open();
//Transfer the data from SqlData Adapter to DAta Table
adapter.Fill(dt);
//If we have values on dt we need to save it in dealerCustomer BLL
if (dt.Rows.Count > 0)
{
dc.name = dt.Rows[0]["name"].ToString();
dc.email = dt.Rows[0]["email"].ToString();
dc.contact = dt.Rows[0]["contact"].ToString();
dc.address = dt.Rows[0]["address"].ToString();
dc.city = dt.Rows[0]["city"].ToString();
dc.state = dt.Rows[0]["state"].ToString();
dc.zip = dt.Rows[0]["zip"].ToString();
dc.taxable = Convert.ToInt32(dt.Rows[0]["taxable"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + nameof(CustomersDAL.SearchDealerCustomerForTransaction));
}
finally
{
//Close Database connection
conn.Close();
}
return(dc);
}
public bool Update(customersTable dc)
{
//SQL Connection for Database Connection
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Create Boolean variable and set its default value to false
bool isSuccess = false;
try
{
//SQL Query to update data in database
string sql = "UPDATE tbl_customers SET taxable=@taxable, name=@name, email=@email, contact=@contact, address=@address, city=@city, state=@state, zip=@zip WHERE id=@id";
//Create SQL Command to pass the value in sql
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//Passing the values through parameters
cmd.Parameters.AddWithValue("@taxable", dc.taxable);
cmd.Parameters.AddWithValue("@name", dc.name);
cmd.Parameters.AddWithValue("@email", dc.email);
cmd.Parameters.AddWithValue("@contact", dc.contact);
cmd.Parameters.AddWithValue("@address", dc.address);
cmd.Parameters.AddWithValue("@id", dc.id);
cmd.Parameters.AddWithValue("@city", dc.city);
cmd.Parameters.AddWithValue("@state", dc.state);
cmd.Parameters.AddWithValue("@zip", dc.zip);
//open the Database Connection
conn.Open();
//Int varialbe to check if the query executed successfully or not
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Delete(customersTable dc)
{
//SQLiteConnection for Database Connection
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Create a Boolean Variable and set its default value to false
bool isSuccess = false;
try
{
//SQL Query to Delete Data from dAtabase
string sql = "DELETE FROM tbl_customers WHERE id=@id";
//SQL command to pass the value
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//Passing the value
cmd.Parameters.AddWithValue("@id", dc.id);
//Open DB Connection
conn.Open();
//integer variable
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public int GetTaxableFromName(string Name)
{
//First Create an Object of DeaCust BLL and REturn it
customersTable dc = new customersTable();
//SQL Conection here
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Data TAble to Holdthe data temporarily
DataTable dt = new DataTable();
try
{
//SQL Query to Get id based on Name
string sql = "SELECT taxable FROM tbl_customers WHERE id=@name OR name=@name";
//Create the SQL Data Adapter to Execute the Query
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
adapter.SelectCommand.Parameters.AddWithValue("@name", Name);
adapter.SelectCommand.Parameters.AddWithValue("@name", Name);
conn.Open();
//Passing the CAlue from Adapter to DAtatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Pass the value from dt to DeaCustBLL dc
dc.taxable = int.Parse(dt.Rows[0]["taxable"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dc.taxable);
}