ASP.Net – SQL Server Database Connection using SqlDataAdapter method.

ASP.Net – SQL Server Database Connectivity using SqlDataAdapter method.

In this ASP.Net tutorials we are going to learn how make connection between ASP.Net and SQL-Server using SqlDataAdapter method.

If we want to make dynamic web application, then we must have to use database and we need to make connection between our application and database server.There are many database server available in market, in this asp.net example we will learn SQL Server connectivity with our asp.net web application.

There are many methods or  we can say many way we can make connection between asp.net application and sql server database.
There are many method to make connection between ASP.Net and SQL-Server database.

The main method are connection using SqlDataAdapter, asp.net – sql connection using SqlCommand, asp.net – sql connection using Table Adapter method, asp.net – sql connection using LINQ method.
ASP.Net – SQL Server Database Connectivity Video Tutorial.

Here, In this asp.net connectivity example we will learn asp.net with sql server connection using SqlDataAdapter method. In this asp.net connectivity example  we will use Visual Studio 2010 and SQL Server Management Studio 2008.

The step for connectivity between ASP.Net and SQL Server Database.

Step 1 – Open Visual Studio 2010 Create web application

Step 2 – Add new web form and design web form

Step 3 – Open SQL Server Management Studio 2008 Create new database

Step 4 – Create new Table in Database

Step 5 – For connection add two namespace – using system.data.sqlclient; using system.data;

Step 6 - Declare SqlConnection object for connectivity

Step 7 - Declare SqlDataAdapter object for write sql query

Step 8 – Declare DataTable object for store data from database.

STEP 1 - Open Visual Studio 2010 Create empty web application.

ASP.Net - SQL Server connection using SqlDataAdapter method.

ASP.Net – SQL Server connection using SqlDataAdapter method.

Step 2 – Add new web form and design web form for asp.net example.

below the html design source code for asp.net example.

The ASP.Net – SQL Server Connection Example output of design layout is :

ASP.Net - SQL Server connection using SqlDataAdapter method.

ASP.Net – SQL Server connection using SqlDataAdapter method.

Now after designing asp.net web form, create database in sql server management studio 2008.

Step 3 – Open SQL Server Management Studio 2008 Create new database

ASP.Net - SQL Server connection using SqlDataAdapter method.

ASP.Net – SQL Server connection using SqlDataAdapter method.

Step 4 – Create new Table in Database

Here, we have created database “MyExample”. Now create new table with some columns in “MyExample” database. In out previous sql post we have already learned to create new database and create new table in sql server management studio 2008.

ASP.Net - SQL Server connection using SqlDataAdapter method.

ASP.Net – SQL Server connection using SqlDataAdapter method.

Here, in asp.net example we have created table “UserMst” with four columns ID, Name, City and Email. The ID column of UserMst table is a primary key and auto increment column and the rest of three are nvarchar() datatype columns.

ASP.Net - SQL Server connection using SqlDataAdapter method.

ASP.Net – SQL Server connection using SqlDataAdapter method.

Now, up to this we have finished design we application and create database and table. The asp.net web application and sql server database are ready to connect each other. now we have to make some programming to connect sql server with asp.net web application.

Step 5 - Add namespace

In asp.net example for SqlConnection, SqlDataAdpater and DataTable object we need to add two namespace in c# coding page.

using System.Data.SqlClient;
using System.Data;

Step 6 - Declare SqlConnection object for connectivity

SqlConnection  - SqlConnection object is used to handles database connection with asp.net and sql server. SqlConnection initiates a connection to our sql server database.

We have to declare SqlConnection object with sql connection string like below.

SqlConnection SQlConn = new SqlConnection(“Data Source=SqlServer_Name; Initial Catalog=Database_Name; Integrated Security=true”);

SqlConnection SQlConn = new SqlConnection(“Data Source=.\\SQLExpress; Initial Catalog=MyExample; Integrated Security=true”);

Step 7 - Declare SqlDataAdapter object for write sql query

SqlDataAdapter – SqlDataAdapter is a bright between Database and DataSet or DataTable. SqlDataAdapter is handles the operation like insert, update data to database and retrieve, delete data from database.

Here, we can write select sql statement in SqlDataAdapter for select data from UserMst table.

SqlDataAdapter SQLAdapterr = new SqlDataAdapter(“Select * from UserMst”, SQlConn);

Step 8 – Declare DataTable object for store data from DataAdapter.

DataTable DT = new DataTable();
SQLAdapter.Fill(DT);

Now, lets understand insert data to database and select data from database with an asp.net example.  Here we have design asp.net web form with three textbox for Name, City and Email and one Button control for insert data in to database then retrieve data and display data in to gridview control in below example.

ASP.Net - SQL Server connection using SqlDataAdapter method.

ASP.Net – SQL Server connection using SqlDataAdapter method.

- Write below code on Button Click Event:

protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection SQlConn = new SqlConnection(“Data Source=.\\SQLExpress; Initial Catalog=MyExample; Integrated Security=true”);
SqlDataAdapter SQLAdapter = new SqlDataAdapter(“insert into UserMst values(‘”+txtname.Text+”‘,’”+txtcity.Text+”‘,’”+txtemail.Text+”‘)”, SQlConn);
DataTable DT = new DataTable();
SQLAdapter.Fill(DT);

SqlDataAdapter SQLAdapterr = new SqlDataAdapter(“Select * from UserMst”, SQlConn);
SQLAdapterr.Fill(DT);
GridView1.DataSource = DT;
GridView1.DataBind();

}

The ASP.Net connectivity example output is :

ASP.Net - SQL Server connection using SqlDataAdapter method.

ASP.Net – SQL Server connection using SqlDataAdapter method.

I hope this asp.net sql server connection example will help you……

Download ASP.Net SqlDataAdapter Source Code Example
asp.net example

Leave a Reply

Your email address will not be published. Required fields are marked *

6 + 4 =


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">