Post
Ask Question

How To Connect SQL Database In ASP.NET Using C# And Insert And View The Data Using Visual Studio 2015

Introduction

This article explains how to connect the SQL database into ASP.net using C# language and how to insert and view the data, using Visual Studio 2015 Update 3.

Requirements
  • Visual Studio 2015 Update 3
  • ASP.NET 4.5.2
  • SQL Server
If you want to connect to the SQL database into ASP.NET, using C#, it should follow the steps given below.

Step 1

Now, Open Visual Studio 2015 Update 3, go to the File >> New >> Project or use the shortcut key "Ctrl+Shift +N".


Step 2

Here, select Visual C# >> Web >> ASP.NET Web Application. Finally, click "OK" button.


Step 3

Here, you can select the template for your ASP.NET Application. We are choosing "Empty" here. Now, click OK button.


Step 4

Now, open the project and look for the Solution Explorer.

 
 
Here, open the default .aspx. If you want a Webform, you can add the page (Web Form). Add+New item (Ctrl+Shift+A).

 
Now, we can create a login page, using ASP.NET code. You need to follow the drag and drop method. Here, we already created the login page.



Step 5
         
Now, open the project. If you want a SQL Server database, you can add the page (SQL Server database). Add+New item (Ctrl+Shift+A).

Here, you can select Visual C# and choose SQL Server database. Afterwards, click "OK" button.

 
Here, open the new Window and click YES button.
 
 
Now, add this to the database in our project.

 
Step 6

Now, we can go to the Server Explorer and add your database. You can click the Tables and afterwards, click Add New Table.

 
Now, open the new table and you can fill the data, which is like (studentname, password) and afterwards, you click the Update .

 
Here, click database in update and subsequently click update the database.

 
Here, the database is updated.



Here, the database and data are added.


Now, we can click  on the right click and click Show Table Data.
 
 
Now, the data is stored.
 
 
Step 7

Now, you can add SQL Data Source. Drag and drop method. Here, click Configure Data Source

 
Now, we can choose your database and click NEXT button.
 


Now, you can select the ConnectionString and Click NEXT button


Now, we can choose Specify columns from a table or view and afterwards, click Next button.


Now, click Test Query.


Here, add the data and click Finish button.

 
Step 8

Now, you can go to CS(C# code) page and you will write the C# code.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data.SqlClient;  
  8. using System.Configuration;  
  9.   
  10. namespace DatabaseConnectivity  
  11. {  
  12.     public partial class loginpage  System.Web.UI.Page  
  13.     {  
  14.         protected void Page_Load(object sender, EventArgs e)  
  15.         {  
  16.            if(IsPostBack)  
  17.             {  
  18.                 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegiConnectionString"].ConnectionString);  
  19.                 conn.Open();  
  20.                 string checkuser = "select count(*) from RegisterDataBase where StudentName='"+TextBox1.Text+"'";  
  21.                 SqlCommand cmd = new SqlCommand(checkuser, conn);  
  22.                 int temp = Convert.ToInt32(cmd.ExecuteScalar().ToString());  
  23.   
  24.                 if (temp == 1)  
  25.                 {  
  26.                     Response.Write("Student Already Exist");  
  27.                 }  
  28.   
  29.                 conn.Close();  
  30.             }  
  31.                
  32.             }  
  33.   
  34.         protected void Button1_Click(object sender, EventArgs e)  
  35.         {  
  36.             try  
  37.             {  
  38.   
  39.                 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegiConnectionString"].ConnectionString);  
  40.                 conn.Open();  
  41.                 string insertQuery = "insert into RegisterDataBase(StudentName,Passwords,EmailId,Department,College)values (@studentname,@passwords,@emailid,@department,@college)";  
  42.                 SqlCommand cmd = new SqlCommand(insertQuery, conn);  
  43.                 cmd.Parameters.AddWithValue("@studentname", TextBox1.Text);  
  44.                 cmd.Parameters.AddWithValue("@passwords", TextBox2.Text);  
  45.                 cmd.Parameters.AddWithValue("@emailid", TextBox3.Text);  
  46.                 cmd.Parameters.AddWithValue("@department", TextBox4.Text);  
  47.                 cmd.Parameters.AddWithValue("@college", TextBox5.Text);  
  48.                 cmd.ExecuteNonQuery();  
  49.   
  50.                 Response.Write("Student registeration Successfully!!!thank you");  
  51.   
  52.                 conn.Close();  
  53.   
  54.             }  
  55.             catch (Exception ex)  
  56.             {  
  57.                 Response.Write("error" + ex.ToString());  
  58.             }  
  59.         }  
  60.     }  
  61. } 
Now, you can see the Loginpage.aspx code.
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="loginpage.aspx.cs" Inherits="DatabaseConnectivity.loginpage" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http//www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title></title>  
  8.     <link href="stylepage.css" type="text/css" rel="stylesheet" />  
  9.     <style type="text/css">  
  10.         .auto-style1 {  
  11.             width 100%;  
  12.         }  
  13.     </style>  
  14. </head>  
  15. <body>  
  16.     <form id="form1" runat="server">  
  17.     <div id="title">  
  18.     <h1>REGISTER PAGE</h1>  
  19.     </div>  
  20.         <div id ="teble"></div>  
  21.     <table class="auto-style1">  
  22.         <tr>  
  23.             <td>  
  24.                 <aspLabel ID="Label1" runat="server" Text="StudentName"></aspLabel></td>  
  25.             <td>  
  26.                 <aspTextBox ID="TextBox1" runat="server"></aspTextBox></td>  
  27.         </tr>  
  28.         <tr>  
  29.             <td>  
  30.                 <aspLabel ID="Label2" runat="server" Text="Password"></aspLabel></td>  
  31.             <td>  
  32.                 <aspTextBox ID="TextBox2" runat="server"></aspTextBox></td>  
  33.         </tr>  
  34.         <tr>  
  35.             <td>  
  36.                 <aspLabel ID="Label3" runat="server" Text="EmailId"></aspLabel></td>  
  37.             <td>  
  38.                 <aspTextBox ID="TextBox3" runat="server"></aspTextBox></td>  
  39.         </tr>  
  40.         <tr>  
  41.             <td>  
  42.                 <aspLabel ID="Label4" runat="server" Text="Department"></aspLabel></td>  
  43.             <td>  
  44.                 <aspTextBox ID="TextBox4" runat="server"></aspTextBox></td>  
  45.         </tr>  
  46.         <tr>  
  47.             <td>  
  48.                 <aspLabel ID="Label5" runat="server" Text="College"></aspLabel></td>  
  49.             <td>  
  50.                 <aspTextBox ID="TextBox5" runat="server"></aspTextBox></td>  
  51.         </tr>  
  52.     </table>  
  53.     <div id="button">  
  54.         <aspButton ID="Button1" runat="server" Text="submit" OnClick="Button1_Click" BackColor="Yellow" />  
  55.     </div>  
  56.         <div id="sim"></div>  
  57.         <aspSqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStringsRegiConnectionString %>" SelectCommand="SELECT * FROM [RegisterDataBase]"></aspSqlDataSource>  
  58.           
  59.         <div id="grid">  
  60.             <aspGridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None">  
  61.                 <AlternatingRowStyle BackColor="White" ForeColor="#284775" />  
  62.                 <Columns>  
  63.                     <aspBoundField DataField="Id" HeaderText="Id" SortExpression="Id" />  
  64.                     <aspBoundField DataField="StudentName" HeaderText="StudentName" SortExpression="StudentName" />  
  65.                     <aspBoundField DataField="Passwords" HeaderText="Passwords" SortExpression="Passwords" />  
  66.                     <aspBoundField DataField="EmailId" HeaderText="EmailId" SortExpression="EmailId" />  
  67.                     <aspBoundField DataField="Department" HeaderText="Department" SortExpression="Department" />  
  68.                     <aspBoundField DataField="College" HeaderText="College" SortExpression="College" />  
  69.                 </Columns>  
  70.                 <EditRowStyle BackColor="#999999" />  
  71.                 <FooterStyle BackColor="#5D7B9D" -Bold="True" ForeColor="White" />  
  72.                 <HeaderStyle BackColor="#5D7B9D" -Bold="True" ForeColor="White" />  
  73.                 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />  
  74.                 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />  
  75.                 <SelectedRowStyle BackColor="#E2DED6" -Bold="True" ForeColor="#333333" />  
  76.                 <SortedAscendingCellStyle BackColor="#E9E7E2" />  
  77.                 <SortedAscendingHeaderStyle BackColor="#506C8C" />  
  78.                 <SortedDescendingCellStyle BackColor="#FFFDF8" />  
  79.                 <SortedDescendingHeaderStyle BackColor="#6F8DAE" />  
  80.             </aspGridView>  
  81.         </div>  
  82.               
  83.             <div id="last">  
  84.             <h3>Developed by  
  85.                       Muthuramalingam Duraipandi</h3>  
  86.         </div>  
  87.     </form>  
  88.     </body>  
  89. </html> 
Step 9

Here, you need to run any Browser and after a few minutes, you will get some output. Now, we can insert the data into your database.


Step 10

Now, we can added the GridView. Drag and drop method needs to be used.

 
Now, you can choose the  data source, it is sqlDataSource.
 

Here, the database data is added to GridView.



Step 11

Here, you need to run any Browser and after a few minutes, you will get an output. Now, we can view the data.


Similar Articles