The first thing you will need to do when interacting with a database is to create a connection. The connection tells the rest of the ADO.NET code which database it is talking to. It manages all of the low level logic associated with the specific database protocols.
You must include the using System.Data.SqlClient; namespace to your code. The System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
Creating a SqlConnection Object:
A SqlConnection is an object, just like any other C# object. Most of them time, you just declare and instantiate the SqlConnection all at the same time, as shown below.
SqlConnection cn = new SqlConnection(@"Data Source=.local; Intial Catalog= testdatabase;Integrated Security=SSPI");
Data source=(local); - this means that we want to connect to the sql server instance located on the local machine.
Initial catalog=testdatabase; - This means the database we want to first connect to is named "testdatabase.
Integrated security=SSPI; - This means we want to connect using windows authentication.
To Create a Database connection Fellow below steps:
Step 1: Open visual studio 2010 click on File->New->Project.
Select ASP.NET Empty Web Application-> Give name to project as you like. I give here to my project name as " connection ". Next click on OK Button
Now right click on project --> "Add" and choose "Add New Item".
Step 4:
Select the "Web Form" item, give it a file name and click Add
Step 5:
Now you will get one blank web form. Add two text boxes, two labels and one button.
web form code:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="home.aspx.cs" Inherits="connecton.home" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Connection in Visual studio 2010</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Lable1" runat="server" Text="Student RollNo"></asp:Label>
<asp:TextBox runat="server" ID="Studentnum" ></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Text="Student Name"></asp:Label>
<asp:TextBox ID="stdname" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="Click Me" onclick="Button1_Click" />
<br />
</div>
</form>
</body>
</html>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Connection in Visual studio 2010</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Lable1" runat="server" Text="Student RollNo"></asp:Label>
<asp:TextBox runat="server" ID="Studentnum" ></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Text="Student Name"></asp:Label>
<asp:TextBox ID="stdname" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="Click Me" onclick="Button1_Click" />
<br />
</div>
</form>
</body>
</html>
Step 6:
Now you have to add database to your Project:
Right click on project --> "Add" and choose "Add New Item".
Select the "Sql server Database" item, give database name and click Add
Step 7:
When you clicked on Add it will ask you as
You are attempting to add a special file type(.mdf) to an asp>net web site. in general, to use this type of item in your site. You should place it in the ‘app_data’ folder. do you want to place the file in the ‘app_data’ folder
Click on Yes.
Step 8:
Click on Server Explorer you will find your database in server explorer. Click on "+" database:
Right click on Tables --> Add new Table
Step 9:
Enter table columns names and their data type.
Step 10:
Give a table name in popup window:
Create a Table using Query:
CREATE TABLE studentinfo
(
studentno int,
studentname varchar(50)
)
CREATE TABLE studentinfo
(
studentno int,
studentname varchar(50)
)
Step11:
This Is very important step in this topic.
Right click on your database in server explorer select properties : In properties box you will find connection string. Copy That total connection string and give it as a parameter to a SqlConnection string.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace connecton
{
public partial class home : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e){
SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename='c:\documents and settings\rajendar reddy\my documents\visual studio 2010\Projects\connecton\connecton\App_Data\connection.mdf';Integrated Security=True;User Instance=True");
cn.Open(); // Connection will be open here.
SqlCommand cmd = new SqlCommand("insert into studentinfo values(" + Convert.ToInt32(Studentnum.Text) + ",'" + stdname.Text + "')", cn);
cmd.ExecuteNonQuery(); // It Excutes SqlCommand
cn.Close(); // connection closing
Response.Write("<script>alert('inserted successfully')</script>"); // it shows the message
}
}
}
SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename='c:\documents and settings\rajendar reddy\my documents\visual studio 2010\Projects\connecton\connecton\App_Data\connection.mdf';Integrated Security=True;User Instance=True");
cn.Open(); // Connection will be open here.
SqlCommand cmd = new SqlCommand("insert into studentinfo values(" + Convert.ToInt32(Studentnum.Text) + ",'" + stdname.Text + "')", cn);
cmd.ExecuteNonQuery(); // It Excutes SqlCommand
cn.Close(); // connection closing
Response.Write("<script>alert('inserted successfully')</script>"); // it shows the message
}
}
}
Step 12:
Debug Home form:
Step 13:
Enter Values in text boxes
Out Put:
