Total Quantity of DataGridview In C# with SQL Server

Calculate the Total Quantity of Books Based on DataGridview In C# with SQL Server

Quantity of DataGridview In C#

Manually counting of books in bookshelves may take you hours before you count it all. Worst, you might be forgetful and you need to start counting again all the books from the start. But, now you have nothing to worry about because this simple automated method will help you out. This method will be the one to calculate the quantity of books that are available. All you have to do is fill out the book names and author in the database and once you clicked the “calculate” button the total number of books will be calculated and displayed in the dataGridview.

Let’s Begin:

1. Create a database in the SQL server 2005 Express Edition and name it “dbbooks“.

2. Create a table in the database that you have created.

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblbooks](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [BooksName] [nvarchar](50) NULL,
 [BooksAuthor] [nvarchar](50) NULL,
 [qty] [int] NULL,
 CONSTRAINT [PK_tblbooks] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

3. Open Microsoft Visual Studio and create a new Windows Form Application for C#. Then, do the form just like this.

4. Go to the solution explorer, click the “code view” to fire the code editor.

5. Initialize the classes and declare the variables that you’re going to use.

Note: Put “using System.Data.SqlClient;” above the namespace to access sql server library.

 //initializes new instance 
 SqlConnection con = new SqlConnection();
 SqlDataAdapter da = new SqlDataAdapter();
 SqlCommand cmd = new SqlCommand();
 DataTable dt = new DataTable();
 //declare a variable
 string query;
 int tot = 0;

6. Create a method to display the data in the dataGridview from SQL Server database.

//a method for filling data in the datagridview
 private void LoadBooks(string query, DataGridView dtg)
 {
 //opening connection
 con.Open();
 try
 {
 //initialize a new instance of sqlcommand
 cmd = new SqlCommand();
 //set a connection used by this instance of sqlcommand
 cmd.Connection = con;
 //set the sql statement to execute at the data source
 cmd.CommandText = query;
 //initialize a new instance of sqlDataAdapter
 da = new SqlDataAdapter();
 //set the sql statement or stored procedure to execute at the data source
 da.SelectCommand = cmd;
 //initialize a new instance of DataTable
 dt = new DataTable();
 //add or resfresh rows in the certain range in the datatable to match those in the data source.
 da.Fill(dt);
 //set the data source to display the data in the datagridview
 dtg.DataSource = dt;


 }
 catch (Exception ex)
 {
 //catching error 
 MessageBox.Show(ex.Message);
 }
 //release all resources used by the component
 da.Dispose();
 //closing connection
 con.Close();
 }

7. Set up the connection between SQL server and C#.net. Then, call a method that you have created in the fist load of the form.

 private void Form1_Load(object sender, EventArgs e)
 {
 //set a connection between SQL server and Visual C#
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=dbbooks;trusted_connection=true;";
 //set up a retrieve query
 query = "Select * From tblbooks";
 LoadBooks(query, dataGridView1);//call a load method
 }

8. Go back to the design view, double-click the “Calculate” button and do the following codes for calculating the total quantity of books.

 private void button1_Click(object sender, EventArgs e)
 {
 try{
 //'declaring variable as integer to store the value of the total rows in the datagridview
 int maxrow = dataGridView1 .Rows.Count -1;
 //'getting the values of specific rows
 for (int i = 0; i < maxrow; i++)
 {
 //calculate the quantity of all books
 tot += int.Parse (dataGridView1.Rows[i].Cells["qty"].Value .ToString ());
 }
 //adding values in the max rows of the dataGridview
 dataGridView1.Rows[maxrow].Cells[2].Value = "Total";
 dataGridView1.Rows[maxrow].Cells[3].Value = tot.ToString();
 
 }
 catch(Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 }

Output:

For all students who need a programmer for your thesis system or anyone who needs a source code in any a programming languages. You can contact me @ :

Email – [email protected]
Mobile No. – 09305235027 – TNT

Download Source code