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

Submitted by janobe on Friday, June 24, 2016 - 09:53.

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.

/****** Object:  Table [dbo].[tbluser]    Script Date: 06/23/2016 08:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbluser](
        [ID] [INT] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](50) NULL,
        [UNAME] [nvarchar](50) NULL,
        [PASS] [nvarchar](MAX) NULL,
        [UTYPE] [NCHAR](20) NULL,
 CONSTRAINT [PK_tbluser] 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]

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.

//initialize all classes
        SqlConnection sql_con = new SqlConnection();
        SqlDataAdapter sql_da = new SqlDataAdapter();
        SqlCommand sql_cmd = new SqlCommand();
        DataTable dt = new DataTable();

After that, create a connection between SQL server and C#.net.

 private void Form1_Load(object sender, EventArgs e)
  {
            //bridge between sql server to c#
            sql_con.ConnectionString = "Data  Source=.\\SQLEXPRESS;Database=userdb;trusted_connection=true;";
  }

Go back to the design view, double click the "Load" button and do the following codes in the method.

 //opening connection
            sql_con.Open();
            try
            {
                //initialize a new instance of sqlcommand
                sql_cmd = new SqlCommand();
                //set a connection used by this instance of sqlcommand
                sql_cmd.Connection = sql_con;
                //set the sql statement to execute at the data source
                sql_cmd.CommandText = "Select ID, Name, UNAME as 'Username',UTYPE as 'Role' FROM tbluser";
 
                //initialize a new instance of sqlDataAdapter
                sql_da = new SqlDataAdapter();
                //set the sql statement or stored procedure to execute at the data source
                sql_da.SelectCommand = sql_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.
                sql_da.Fill(dt);
                //set the data source to display the data in the dataGridView
                dataGridView1.DataSource = dt;
            }
            catch (Exception ex)
            {
                //catching error 
                MessageBox.Show(ex.Message);
            }
            //release all resources used by the component
            sql_da.Dispose();
            //clossing connection
            sql_con.Close();

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

818 views