Exemple #1
0
        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);
        }