C# BLL.DBManager类代码示例

        private int GetLastMemoNumber()
        {
            DBManager manager = new DBManager();
            SqlConnection connection = manager.Connection();
            string selectQ = "select MAX(Memo_No) from Deliveri_Memo_Counter";
            SqlCommand command = new SqlCommand(selectQ, connection);

            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                if (reader[0].ToString().Equals(""))
                {
                    memoNumver = 1;
                }
                else
                {
                    memoNumver = Convert.ToInt16(reader[0]);
                }

            }

            connection.Close();
            connection.Open();
           



            return memoNumver;
        }

        public void GetAllOrders()
        {
            try
            {

                DBManager manager = new DBManager();
                SqlConnection connection = manager.Connection();

                string selectQuery = "SELECT * From Orders";
                SqlCommand cmd = new SqlCommand(selectQuery, connection);
                
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                dataTable = new DataTable();
                adapter.Fill(dataTable);
                BindingSource binding = new BindingSource();
                binding.DataSource = dataTable;
                dataGridView1.DataSource = dataTable;
                adapter.Update(dataTable);
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.RowIndex >= 0)
                {
                    DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex];
                    string serialNo = row.Cells["S.No"].Value.ToString();
                    DBManager manager=new DBManager();
                    SqlConnection connection = manager.Connection();
                    string query = "delete from Books Where [S.No]='" + serialNo + "'";
                    SqlCommand delCommand=new SqlCommand(query,connection);
                    connection.Open();

                    System.Windows.Forms.DialogResult result =
                        MessageBox.Show("Do you wnat to delete the selected book?", "Message", MessageBoxButtons.YesNo,
                            MessageBoxIcon.Question);
                    if (result == DialogResult.Yes)
                    {
                        delCommand.ExecuteNonQuery();
                        MessageBox.Show("deleted", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        TestBook(); 
                    }


 
                }
            }
            catch (Exception exception)
            {

                MessageBox.Show(exception.StackTrace);
            }
        }

        public string SaveOrder(Order anOrder)
        {
            try
            {


                if (anOrder.Quantity <= 0)
                {
                    MessageBox.Show("Enter the book quantity", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else if (anOrder.Advance < 0)
                {
                    MessageBox.Show("Enter the advance ammount", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    //int quantity =;
                     double total = anOrder.Quantity*anOrder.UnitPrice;
                    double due = total - anOrder.Advance;
                    DBManager manager = new DBManager();
                    SqlConnection connection = manager.Connection();
                    string insertQuery = "INSERT INTO Orders values(@name,@phone,@bname,@bwriter,@bEdition,@type,@bPrint,@buyUnitprice,@bQuantity,@unitprice,@total,@advance,@due,@odrderDate,@supplyDate)";

                    //string insertQuery = "INSERT INTO Orders values('" + anOrder.CustomerName + "','" +
                    //                     anOrder.CustomerPhone + "','" + anOrder.BookName + "','" + anOrder.WriterName +
                    //                     "','" + anOrder.Edition + "','" + anOrder.BookPrint + "','" + anOrder.BuyUnitPrice + "','" + anOrder.Quantity + "','" + anOrder.UnitPrice +
                    //                     "','" + total + "','" + anOrder.Advance + "','" + due + "','" +
                    //                     DateTime.Now.ToShortDateString() + "','" + anOrder.SupplyDate + "')";
                    SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
                    connection.Open();
                    insertCommand.Parameters.Clear();
                    insertCommand.Parameters.AddWithValue("@name", anOrder.CustomerName);
                    insertCommand.Parameters.AddWithValue("@phone", anOrder.CustomerPhone);
                    insertCommand.Parameters.AddWithValue("@bname", anOrder.BookName);
                    insertCommand.Parameters.AddWithValue("@bwriter", anOrder.WriterName);
                    insertCommand.Parameters.AddWithValue("@bEdition", anOrder.Edition);
                    insertCommand.Parameters.AddWithValue("@type", anOrder.TypeOfBook);
                    insertCommand.Parameters.AddWithValue("@bPrint", anOrder.BookPrint);
                    insertCommand.Parameters.AddWithValue("@buyUnitprice", anOrder.BuyUnitPrice);
                    insertCommand.Parameters.AddWithValue("@bQuantity", anOrder.Quantity);
                    insertCommand.Parameters.AddWithValue("@unitprice", anOrder.UnitPrice);
                    insertCommand.Parameters.AddWithValue("@total", total);
                    insertCommand.Parameters.AddWithValue("@advance", anOrder.Advance);
                    insertCommand.Parameters.AddWithValue("@due", due);
                    insertCommand.Parameters.AddWithValue("@odrderDate", DateTime.Now.ToShortDateString());
                    insertCommand.Parameters.AddWithValue("@supplyDate", anOrder.SupplyDate);
                   
                    int i = insertCommand.ExecuteNonQuery();
                    
                }
                

            }
            catch (Exception exception)
            {

                MessageBox.Show(exception.Message);
            }
            return " Order Saved";
        }

         public int  LastAddedInvestlNo()
        {
            int a = lastAdded;
            InvestmentGateway inGateway = new InvestmentGateway();
            Invest invest = new Invest();
            
            DBManager manager = new DBManager();
            SqlConnection connection = manager.Connection();
           
            string selectQuery = "SELECT Id From InvestmentCost";
            SqlCommand cmd = new SqlCommand(selectQuery, connection);
            connection.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            List<int> totalId = new List<int>();

            while (reader.Read())
            {
                int  aId = Convert.ToInt16(reader[0]);

                
                totalId.Add(aId);
            }
             if (totalId.Count.Equals(0))
             {
                 return 0;
             }
             else
             {
                 int x = totalId.Max();
                 return x;
             }

           
        }

        public void TestBook()
        {
            try
            {
                DBManager manager1 = new DBManager();
                SqlConnection connection = manager1.Connection();
                string seletQuery = "select * from Books Order by Name";
                SqlCommand selectCmd = new SqlCommand(seletQuery, connection);
                connection.Open();

                SqlDataAdapter myAdapter = new SqlDataAdapter();
                myAdapter.SelectCommand = selectCmd;

                dt = new DataTable();
                myAdapter.Fill(dt);
                BindingSource bSource = new BindingSource();
                bSource.DataSource = dt;
                dataGridView1.DataSource = dt;
                myAdapter.Update(dt);



                //return null;
            }
            catch (Exception obj)
            {

                throw new Exception("Error", obj);
            }
        }

        public void SaveSelfOrder(SelfOrder selfOrder)
        {

            
        

            if (HasThisBookalradyinList(selfOrder.BName,selfOrder.BWriter,selfOrder.BEdition) == false)
            {
                DBManager manager = new DBManager();
                SqlConnection connection = manager.Connection();
                connection.Close();
                string selectQuery = "Insert into Self_Order values('" + selfOrder.BName + "','" +
                                     selfOrder.BWriter + "','" + selfOrder.BEdition + "','" + selfOrder.Quantity +
                                     "','" + DateTime.Now.ToShortDateString() + "')";

                SqlCommand cmd = new SqlCommand(selectQuery, connection);
                connection.Open();
                int i = cmd.ExecuteNonQuery();
                MessageBox.Show("Saved", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);


                
            }

            else
            {
                MessageBox.Show("This book already include.","Message",MessageBoxButtons.OK,MessageBoxIcon.Warning);
            }

        }

 private void searchButton(object sender, EventArgs e)
 {
     DBManager manger = new DBManager();
     List<Book> books = GetALLBooks();
   
     
 }

        public void GetAllBook()
        {
            
            try
            {
                DBManager manager = new DBManager();
                SqlConnection connection = manager.Connection();
                string query = "select * from Books";
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    string name = reader[1].ToString();
                    //comboBox1.Items.Add(name);

                }
            }
            catch (Exception exception)
            {

                MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
           
            
        }

        private void showTotalButton_Click(object sender, EventArgs e)
        {
            DBManager manager = new DBManager();
            SqlConnection connection = manager.Connection();
            string selectQuery =
                "select SUM(From_Book), sum(From_Photocopy),sum(From_Others),sum(Total_Income) from Income";
            connection.Open();
            SqlCommand cmd = new SqlCommand(selectQuery, connection);
            SqlDataReader reader = cmd.ExecuteReader();
            List<double> sellBooks = new List<double>();
            while (reader.Read())
            {
                try
                {

                    double fbook = Convert.ToInt16(reader[0]);
                    double fphotocopy = Convert.ToDouble(reader[1]);
                    double fothers = Convert.ToDouble(reader[2]);
                    double total = Convert.ToDouble(reader[3]);


                    bookTextBox.Text = fbook.ToString();
                    photocopyTextBox.Text = fphotocopy.ToString();
                    othersTextBox.Text = fothers.ToString();
                    sumofAllTextBox.Text = total.ToString();
                }
                catch (Exception)
                {

                    MessageBox.Show("NO data found", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

            }
        }

        private void saveButton_Click(object sender, EventArgs e)
        {
            try
            {
                SearchAndEdit Search = new SearchAndEdit();
                DBManager manager = new DBManager();
                Book aBook = new Book();
                aBook.SerialNo = serialTextBox.Text;
                aBook.BookName = nameTextBox.Text;
                aBook.AuthorName = writernameTextBox.Text;
                aBook.Edition = comboBox1.SelectedItem.ToString();
                aBook.TypeOfBook = b_type;
                aBook.Quantity = Convert.ToInt16(quantityTextBox.Text);
                aBook.UnitPrice = Convert.ToDouble(uintPriceTextBox.Text);
                aBook.PurchasesDate =dateTimePiker.Text;

                string status =manager.SaveBookS(aBook);
                MessageBox.Show(status,"Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
                ClearAllText();
               serialTextBox.Text=LastAddedInvestlNo().ToString();
               Search.GetALLBooks();
               
            }
                catch(FormatException)
            {
                MessageBox.Show("Please fill up every field properly","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);

                }
            catch (Exception)
            {

                MessageBox.Show("Book id must be unique.","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
            }
        }

       public void LoadAllBook()    
        {
            try
            {
                DBManager manager = new DBManager();
                SqlConnection connection = manager.Connection();

                string selectQuery = "SELECT * From Books";
                SqlCommand cmd = new SqlCommand(selectQuery, connection);
                //connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                dataTable = new DataTable();
                adapter.Fill(dataTable);
                BindingSource binding = new BindingSource();
                binding.DataSource = dataTable;
                dataGridView1.DataSource = dataTable;
                adapter.Update(dataTable);


            }
            catch ( SqlException exception)
            {

                MessageBox.Show(exception.Message);
            }
        }

       public void SaveTempOrer(TempOrder aOrder)
       {
           try
           {
               DBManager maneageManager = new DBManager();
               SqlConnection connection1 = maneageManager.Connection();
               string query = "Insert into Temp_Order_Hold values(@serial,@customerName,@mobile,@bookName,@writerName,@edition,@type,@print,@quantity,@unitPrice,@total,@advance,@due,@supplydate,@memoNumber)";
               SqlCommand command = new SqlCommand(query, connection1);
               command.Parameters.Clear();
               command.Parameters.AddWithValue("@serial", aOrder.SerialNo);
               command.Parameters.AddWithValue("@customerName", aOrder.CustomerName);
               command.Parameters.AddWithValue("@mobile", aOrder.MobileNo);
               command.Parameters.AddWithValue("@bookName", aOrder.BookName);
               command.Parameters.AddWithValue("@writerName", aOrder.WriterName);
               command.Parameters.AddWithValue("@edition", aOrder.Edition);
               command.Parameters.AddWithValue("@type", aOrder.Type);
               command.Parameters.AddWithValue("@print", aOrder.Print);
               command.Parameters.AddWithValue("@quantity", aOrder.Quantity);
               command.Parameters.AddWithValue("@unitPrice", aOrder.Unitprice);
               command.Parameters.AddWithValue("@total", aOrder.Total);
               command.Parameters.AddWithValue("@advance", aOrder.Advance);
               command.Parameters.AddWithValue("@due", aOrder.Due);
               command.Parameters.AddWithValue("@supplydate", aOrder.SupplyDate);
               command.Parameters.AddWithValue("@memoNumber", aOrder.MemoNumber);
               connection1.Open();
               command.ExecuteNonQuery();
               MessageBox.Show("Saved", "Save Message", MessageBoxButtons.OK, MessageBoxIcon.Information);

           }
           catch (Exception exception)
           {

               MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }
       }

        public void GetAllOrders1()
        {
            try
            {
                DBManager manager = new DBManager();
                SqlConnection connection = manager.Connection();

                string selectQuery = "SELECT Quantity From Orders";
                SqlCommand cmd = new SqlCommand(selectQuery, connection);
                connection.Open()
                    ;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string q = reader[0].ToString();
                     if (q.Equals(""))
                    {
                        qunatityOfRemaingBook = 0;
                    }
                    else
                    {
                        qunatityOfRemaingBook = Convert.ToInt16(q);
                    }
                }
                connection.Close();
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message);
            }
        }

        public int LastAddedInvestlNo()
        {
           
            OrderGateway gateway=new OrderGateway();
            
            DBManager manager = new DBManager();
            SqlConnection connection = manager.Connection();
           

            string selectQuery = "SELECT Order_No From Orders";
            SqlCommand cmd = new SqlCommand(selectQuery, connection);
            connection.Open();  
            SqlDataReader reader = cmd.ExecuteReader();
            List<int> totalId = new List<int>();

            while (reader.Read())
            {
                int aId = Convert.ToInt16(reader[0]);

               
                totalId.Add(aId);
            }
            if (totalId.Count<=0)
            {
                return 0;
            }
            else
            {
                int x = totalId.Max();

                return x;  
            }
           

        }