Showing Some Data In ASP.NET From SQL Server Database Using SELECT Query

Create an ASP.NET Project in Visual Studio

• Click New Project.
  
  
• Select ASP.NET Empty Web Application and set name of your project.
  
  
  
  
• Then press OK.
  
  
• In solution explorer Add a WebForm and set a name of this “Test.aspx” or set name anything you want.
  
  
• Double click in Test.aspx and you will see some source and design of the web form where you can design your webform using HTML, CSS, JavaScript, etc.
  
  
• In Design mode from the Tool Box add a “GridView” in webform.
  
  
  
  
• Or write this code in source of HTML section where data is shown from the database.
  
  1. <asp:GridView ID="GridView1" runat="server"> </asp:GridView>

Create Database in SQL Server

• Open “SQL Server Management Studio”.
  
  
• Giver username and password or keep in Windows Authentication Mode and click Connect.
  
  
• Add a New Database, set name “Sample” or set your desired name.
  
  
  
  
• Add a Table set Column Name StudentId(Primary key), StudentName, StudentDept, CGPA.
  
  
• Click “Ctrl+S” and set table name “Student” or you can set your desired name.
  
  
  
  
• Right click on “Student.dbo” and click “Edit Top 200 Rows”, then you can add some data automatically.
  
  
  
  
• This is done with SQL server. Now in Visual Studio connect this SQL database in ASP.NET web form.

Connect to SQL Server using Code

• First task is connection to SQL server. Many ways to connect to the database. I will show you the simple and easiest way.
  
  
• Open “web.config” from solution explorer.
  
  
• Under the <system.web> tag create <connectionStrings> </connectionStrings>.
  
  
• In this tag add an <add> tag and write name of the database (You can write anything), ProviderName=System.Data.SqlClient that indicates data provider of SQL server. ConnectionString will differ on the base secured or windows authentication mode. When secured mode then you have to provide server, database, user id and password:
  
  1. connectionString="server=172.16.7.40;database=Sample;User Id=student;Password=student;”
  Or in Windows authentication mode:
  
  1. connectionString="server=. ; database=MidtermProject; Trusted_Connection=Yes;"
• The whole connection string code is given below:
  
  1. <connectionStrings>
  2. <add name="DBCS" providerName="System.Data.SqlClient" connectionString="server=. ; database=MidtermProject; Trusted_Connection=Yes;" />
  3. </connectionStrings>

Connect to Database and Perform Select query

• Now you have to connect to <conncectionStrings> form “web.config” in code. Open Test.aspx.cs.
  
  
• Add these namespaces on the top:
  
  1. using System.Configuration;
  2. using System.Data.SqlClient;
• In page load function you have to build a connection to database in the following way:
  
  1. string connStr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
  2. SqlConnection conn = new SqlConnection(connStr);
• Then open the connection: conn.Open();
  
  
• Write the command and perform the SQL command using ExecuteReader() function because you have to read only and give you an associative array that you store in SqlDataReader instances.
  
  1. string sql = "SELECT * from Student";
  2. SqlCommand cmd = new SqlCommand(sql, conn);
  3. SqlDataReader reader = cmd.ExecuteReader();

Show the values in web form

• A grid view was in “Test.aspx” form; the name i “GridView1”. In this grid view you can show data in webform in the following way:
  
  1. GridView1.DataSource = reader;
  2. GridView1.DataBind();
• Then close the connection:conn.Close();
  
  The whole snapshot:
  
  

Then Read

• Finally run the application using “ctrl+F5”.
  
  
• Then a browser will open.
  
  
• WOW! Code compiled successfully and data is shown in the web browser.
  
  

Thank you so much for reading the article.