How to Load Data in the DataGridview Using C# and SQL Server Database

This Tutorial will show you how to load data in the DataGridView Using C#.Net and SQL Server 2005 Express Edition. This method will help you to display the data in the DatagridView from the database. You can also control the data that you are going to retrieve in the database.

To start with:

Create a database in the SQL Server 2005 express edition and name it "dbuser". After creating database, set the following query for creating a table in the database that you have created.
  1. /****** Object: Table [dbo].[tbluser] Script Date: 06/23/2016 08:36:33 ******/
  2. SET ANSI_NULLS ON
  3. GO
  4. SET QUOTED_IDENTIFIER ON
  5. GO
  6. CREATE TABLE [dbo].[tbluser](
  7. [ID] [INT] IDENTITY(1,1) NOT NULL,
  8. [Name] [nvarchar](50) NULL,
  9. [UNAME] [nvarchar](50) NULL,
  10. [PASS] [nvarchar](MAX) NULL,
  11. [UTYPE] [NCHAR](20) NULL,
  12. CONSTRAINT [PK_tbluser] PRIMARY KEY CLUSTERED
  13. (
  14. [ID] ASC
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  16. ) ON [PRIMARY]
Now, open Visual Studio 2008 and create a new windows form application. Then, drag a button and datagridview in the form. fig1 After setting up the form, go to the solution explorer and hit "view code". fig2 In the code editor, declare all the classes that are needed.
  1. //initialize all classes
  2. SqlConnection sql_con = new SqlConnection();
  3. SqlDataAdapter sql_da = new SqlDataAdapter();
  4. SqlCommand sql_cmd = new SqlCommand();
  5. DataTable dt = new DataTable();
After that, create a connection between SQL server and C#.net.
  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. //bridge between sql server to c#
  4. sql_con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=userdb;trusted_connection=true;";
  5. }
Go back to the design view, double click the "Load" button and do the following codes in the method.
  1. //opening connection
  2. sql_con.Open();
  3. try
  4. {
  5. //initialize a new instance of sqlcommand
  6. sql_cmd = new SqlCommand();
  7. //set a connection used by this instance of sqlcommand
  8. sql_cmd.Connection = sql_con;
  9. //set the sql statement to execute at the data source
  10. sql_cmd.CommandText = "Select ID, Name, UNAME as 'Username',UTYPE as 'Role' FROM tbluser";
  11.  
  12. //initialize a new instance of sqlDataAdapter
  13. sql_da = new SqlDataAdapter();
  14. //set the sql statement or stored procedure to execute at the data source
  15. sql_da.SelectCommand = sql_cmd;
  16. //initialize a new instance of DataTable
  17. dt = new DataTable();
  18. //add or resfresh rows in the certain range in the datatable to match those in the data source.
  19. sql_da.Fill(dt);
  20. //set the data source to display the data in the dataGridView
  21. dataGridView1.DataSource = dt;
  22. }
  23. catch (Exception ex)
  24. {
  25. //catching error
  26. MessageBox.Show(ex.Message);
  27. }
  28. //release all resources used by the component
  29. sql_da.Dispose();
  30. //clossing connection
  31. sql_con.Close();

Output

Output For all students who need a programmer for your thesis system or anyone who needs a source code in any programming languages. You can contact me @ : Email – [email protected] Mobile No. – 09305235027 – TNT

Add new comment