Category Archives: ASP.Net – SQL Server

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

ASP.Net Database Connection using LINQ

LINQ in ASP.Net – Language-Integrated Query
LINQ is a microsoft language-integrated Query tool used in connectivity with database server. The LINQ is available in version .Net Framework 3.5 or higher version.
we can use LINQ in visual studio 2008 or higher version of vs2008.

LINQ is a managed language that use query and table as an object. LINQ provide drag and drop system to use sql table and stored procedures to use in web application.

In this asp.net article we will learn LINQ with an example to integrate LINQ to SQL database (dbml classes) in ASP.Net using C# language.
We learn in this tutorial create dbml classes, connecting to sql server database, bind Table and Stored Procedures.

Here, In this asp.net connectivity example we will learn asp.net with sql server connection using LINQ method. In this asp.net connectivity example we will use Visual Studio 2010 and SQL Server Management Studio 2008.
ASP.Net LINQ Example Video tutorial

The step for connection ASP.Net and SQL Server Database.

Step 1 – Open Visual Studio 2010 Create web application

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

Step 3 – Create new Table and Stored Procedure in Database

Step 4 – Add LINQ to SQL Classes (DataClasses.dbml) in App_Code folder.

Step 5 – Open Server Explorer and Connect Database

Step 6 – Drag and drop Sql Table and  Stored  Procedures to DataClasses.dbml

Step 7 – Declare DataClassesDataContext for connectivity

 Step 1 – Open Visual Studio 2010 Create web application

Create New web application in visual studio 2010 and design a web form with three Textbox Control, one Button control and a GridView control for display data.

Here, we first Insert Data to SQL-Server and then retrieve data and display data in to GridView control using LINQ.

The asp.net web page LINQ example design layout is:

ASP.Net - Sql Server connection using LINQ method.

ASP.Net – Sql Server database connection using LINQ method.

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

In our previous sql tutorials we have already learned to CREATE DATABASE in sql server management studio 2008.
In this ASP.Net LINQ example we have created new database in sql server 2008 with table and stored procedure.

Database Name = MyExample

Step 3 – Create new Table and Stored Procedure in Database

After creating database create table and create stored procedure for insert data and select data.
learn about CREATE STORED PROCEDURE in sql server management studio 2008.

Database Name = MyExample

Table Name = UserMst

Select Stored Procedure = USERMST_SELECT

Insert Stored Procedure = USERMST_INSERT

The sql USERMST_SELECT stored procedure :

CREATE PROCEDURE [dbo].[USERMST_SELECT]
AS
BEGIN

SELECT * FROM UserMst
END

The sql USERMST_INSERT stored procedure :

CREATE PROCEDURE [dbo].[USERMST_INSERT]

@NAME AS NVARCHAR(256),
@CITY AS NVARCHAR(256),
@EMAIL AS NVARCHAR(256)
AS
BEGIN

INSERT INTO UserMst VALUES(@NAME,@CITY,@EMAIL)
END

Step 4 – Add LINQ to SQL Classes (DataClasses.dbml) in App_Code folder.

Now, After design web form Right Click on Solution Explorer —> Add New Item —> LINQ to SQL Classes

Add LINQ to SQL Classes with name DataClasses1.dbml in to App_Code folder in Solution Explorer.

ASP.Net - Sql Server connection using LINQ method.

ASP.Net – Sql Server database connection using LINQ method.

 Step 5 – Open Server Explorer and Connect Database

Now, Open Server Explorer for SQL-Database connection.

Here, we have Database named MyExample and two stored procedure for Insert data and Select data.

Data Connection —> Add Connection select Microsoft SQL Server.

ASP.Net - Sql Server connection using LINQ method.

ASP.Net – Sql Server connection using LINQ method.

Select database server name from below screen. we have use here SQL  Server, Select Microsoft SQL Server from below screen.

ASP.Net - Sql Server connection using LINQ method.

ASP.Net – Sql Server connection using LINQ method.

Write sql server name and select database for connection. In this asp.net example write “.\SQLExpress” in server name option and select database “MyExample” in database select option.

ASP.Net - Sql Server connection using LINQ method.

ASP.Net – Sql Server connection using LINQ method.

After successfully connect sql server we have below screen shows sql table and stored procedure which is already created in sq l server management studio 2008.

The SQL Table = UserMst
Select Stored Procedure = USERMST_SELECT
Insert Stored Procedure = USERMST_INSERT

 

ASP.Net - Sql Server connection using LINQ method.

ASP.Net – Sql Server connection using LINQ method.

Step 6 – Drag and drop Sql Table and  Stored  Procedures to DataClasses.dbml

After doing this Open the LINQ to SQL Classes DataClasses.dbml then Drag and Drop SQL Table and SQL strode procedure in it like shows below screen.

ASP.Net - Sql Server connection using LINQ method.

ASP.Net – Sql Server connection using LINQ method.

 

Step 7 – Declare DataClassesDataContext for connectitivty

Now, write the C# server side code for Insert data to sql server and then Select data from SQL Server.

write below code on Button Click event at code behind page:

DataClassesDataContext ctx = new DataClassesDataContext();
ctx.USERMST_INSERT(txtname.Text, txtcity.Text, txtemail.Text);

GridView1.DataSource = ctx.USERMST_SELECT();
GridView1.DataBind();

The output of ASP.Net LINQ Connection Example.

ASP.Net - Sql Server connection using LINQ method.

ASP.Net – Sql Server connection using LINQ method.

I hope this LINQ connectivity with ASP.Net and SQL Server will help you..

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

Windows Application Connectivity with SQL-Server using DataAdapter in ASP.Net C#

Here, In this asp.net tutorials we will learn to make connection between SQL-Server and Windows Application in ASP.Net.

We can use many server for connectivity, here we will learn sql server database connection in asp.net. ms sql server is most popular database server.There are  many names of SQL-Server like mssql server, ms sql server, sql server client, database server, mssql server…..

We can do this connectivity with multiple methods.

Connection between Windows Application and SQL-Server using Wizard (TableAdapter Method)

Connection between Windows Application and SQL-Server using DataAdapter and SQLConnection Method

below screen show the output of ms sql server sql server database connection in asp.net.

Connectivity Between Windows Application SQL-Server using DataAdater and SQLConnection

Connectivity Between Windows Application SQL-Server using DataAdater and SQLConnection

First Import this NameSpace for SQLConnectivity in ASP.Net

using System.Data.SqlClient;

Write below code on INSERT Button  in ASP.Net

private void btninsert_Click(object sender, EventArgs e)
{
string  strconn = “Data Source=.\\NEWSQLEXPRESS;Initial Catalog=’DEMO’;Integrated Security=True”;

SqlConnection SqlConn = new SqlConnection(strconn);
SqlDataAdapter SqlAdapter = new SqlDataAdapter(“insert into UserMst values (‘” + txtname.Text +”‘,'”+  txtcity.Text +”‘)”, SqlConn);
DataTable DT = new DataTable();
SqlAdapter.Fill(DT);

//Select Data from Database for Bind Gridview
SqlDataAdapter SqlSelectAdapter =new SqlDataAdapter(“Select * from UserMst”, SqlConn);
SqlSelectAdapter.Fill(DT);
GvData.DataSource = DT;
}

I hope you will enjoy this sql server database connection in asp.net, database connection in vb.net with sql server.

Windows Application Connectivity with SQL-Server using Wizard in ASP.Net C#

Here, In this asp.net tutorials we will learn to make connection between SQL-Server and Windows Application in ASP.Net.

We can use many server for connectivity, here we will learn sql server database connection in asp.net. ms sql server is most popular database server.There are  many names of SQL-Server like mssql server, ms sql server, sql server client, database server, mssql server…..

We can do this connectivity with multiple methods.

Connection between Windows Application and SQL-Server using Wizard (TableAdapter Method)

Connection between Windows Application and SQL-Server using DataAdapter and SQLConnection Method

Before, going to ahead we need to create DataBase in SQL-Server,

Here, some step to create new sql-server Database in SQL-Management Studio.

STEP 1 – Open SQL-Server Management Studion and Create a New Database..

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

After opening SQL Server Create New DataBase

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

Write your New Database Name which you want to create in SQL-Server….and Click OK Button to Create New DataBase.

Connectivity Between Windows Application SQL-ServerConnectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

Now Create New Table in Database..

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

 Create Column in your Table and Save Table with UserMst name.

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

After Creating Database and Table in SQL-Server, open the Visual Studio and Create New Windows Application in ASP.Net.

Open Visual Studio —> File –>  New —> Project

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

Here, Select the Windows Form Application and write your Project Name, We create project named DEMO Here.

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

After Creating project you have a one Windows Form…..

Now, Here we create connection using Wizard so, Create New Folder at Solution Explorer and Create New DataSet in that DS Folder.

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

Open the DataSet1 and create New TableAdapter by Right clicking on DataSet1.

Then give your server name for new connection, just select your Database like below screen…

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

Then Click Next button and Select Use Existing Stored Procedure option on below screen.

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

Select the Stored Procedure for Select and Insert which are already you created in sql server.

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

Now,  Remove the Fill a DataTable option and Next….

Connectivity Between Windows Application SQL-Server

Connectivity Between Windows Application SQL-Server

After Finishing this connectivity wizard, open your windows form and design like below:

Here, we have two TextBox Control Named txtname,txtcity and one Insert Button and one GridView Control.

wwwindows7

Write Below code on INSERT Button Click Event:

private void btninsert_Click(object sender, EventArgs e)
{
DS.DataSet1.USERMST_SELECTDataTable UDT = new DEMO.DS.DataSet1.USERMST_SELECTDataTable();
DS.DataSet1TableAdapters.USERMST_SELECTTableAdapter UAdapter = new DEMO.DS.DataSet1TableAdapters.USERMST_SELECTTableAdapter();

int inst = UAdapter.Insert(txtname.Text, txtcity.Text);
txtname.Text = “”;
txtcity.Text = “”;

UDT = UAdapter.SelectData();
GvData.DataSource = UDT;
}

I hope you will enjoy this asp.net tutorials of  sql server database connection in asp.net, database connection in vb.net with sql server.

Connection Between ASP.Net and SQL-Server using SQLCommand

In this ASP.Net tutorials we are going to learn How Make connection between ASP.Net and SQL-Server using SQLCommand.

There are many method to make connection between ASP.Net and SQL-Server.

1.Connection using TableAdapter and StoredProcedure thought Wizards.
2. Connection using SQLCommand  and SQLDataAdapter using StoredProcedure .
3. Connection using SQLDataAdapter and SQLConnection writing query in webpage.

Here, We will learn to make connection between ASP.Net and SQL-Server using SQLDataAdapter, SQLConnection and SQLComand using SQL StoredProcedure.

STEP 1 – Create new website in ASP.Net. Write below code for Design Webpage:

<table>
<tr>
<td>
&nbsp;</td>
<td>
<asp:Button ID=”btnsave” runat=”server” Font-Bold=”True”
onclick=”btnsave_Click” Text=”GET DATA” />
</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
<asp:GridView ID=”GridView1″ runat=”server”>
</asp:GridView>
</td>
</tr>
</table>

– The ASP.Net Design Output is:

Connection between ASP.Net and SQL-Server using SQLCommand and StoredProcedure

Connection between ASP.Net and SQL-Server using SQLCommand and StoredProcedure

STEP 2 – Now Open the SQL-Server and Create a New DataBase.

sql server

sql server

– Now Open the SQL-Server and Create New Database like below:

sql server

sql server

– Now, After Creating a New Database create a New Table like Below:

Create a New Table in SQL-Server

Create a New Table in SQL-Server

 

Create a New Table in SQL-Server

Create a New Table in SQL-Server

– Now create a New StoredProcedure in SQL-SERVER like below:

Create a New StoredProcedure in SQL-Server

Create a New StoredProcedure in SQL-Server

– Create a Stored Procedure for SELECT:

Create a New StoredProcedure in SQL-Server

Create a New StoredProcedure in SQL-Server

STEP 3 -After doing this things write below code for connectivity.

Import below namespace first:

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

– Write below asp.net code on button click event for connectivity:

 string strconn = “Data Source=.\\SQLEXPRESS;Initial Catalog=’BOOK’;Integrated Security=True”;

SqlConnection sqlconn = new SqlConnection(strconn);
SqlCommand sqlcmd = new SqlCommand(“AddressBook_SELECT”, sqlconn);

SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataTable DT = new DataTable();
adapter.Fill(DT);

GridView1.DataSource = DT;
GridView1.DataBind();

– The ASP.Net Connection Output like:

Connection between ASP.Net and SQL-Server using SQLCommand and StoredProcedure

Connection between ASP.Net and SQL-Server using SQLCommand and StoredProcedure

– I hope you will enjoy this ASP.Net tutorial for Connectivity.

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

ASP.Net with SQL-Server Connection

  • There are many method for connecting ASP.Net Application to SQL-Server.
  • We are discussing basic three method below for connection.
    Method I
    Step 1 –   Import  Namespace

                using System.Data.Sql;

using System.Data.SqlClient;


Step 2 – Delcare SqlConnection
(Make Connection between Asp.Net Application to Sql Server)
string  strconn = “Data Source=COMPUTERNAME\\SQLEXPRESS;Initial Catalog=’DataBaseName’;Integrated Security=True”;

SqlConnection SqlConn = new SqlConnection(strconn);


Step 3 – Declare SqlDataAdapter
(SqlDataAdapter is a Bridge between DataBase and DataSet/DataTable)
SqlDataAdapter SqlAdapter = new SqlDataAdapter(“select * from Table”, SqlConn);


Step 4 – Declare DataSet/DataTable and fill the data from DataAdapter
DataTable DT = new DataTable();
            SqlAapter.Fill(DT);



Method II

string strconn = “Data Source=COMPUTER_1\\SQLEXPRESS;Initial Catalog=ok;Integrated Security=True”;

            SqlConnection sqlconn = new SqlConnection(strconn);
            SqlCommand sqlcmd = new SqlCommand(“USP_LOGIN_SELECT”,sqlconn);

             SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);


Note :- USP_LOGIN_SELECT == Stored Procedure Name
(Stored Procedure
is a precompiled set of Structured Query Language (SQL) statements)



Method III

Step 1 – Add App_Code Folder  and Create New DataSet  in App_Code Folder.


Step 2 – Create New TableAdapter in Dataset Add Bind Your All Stored Procedure to it.


Step 3 –  Create DataTable Variable From DataSet
            DataSet1.TestDataTable TestDT = new DataSet1.TestDataTable();


Step 4 – Create TableAdapter From DataSet        
DataSet1TableAdapters.TestTableAdapter TestAdapter = new DataSet1TableAdapters.TestTableAdapter();
 
Step 5- Get Data in DataTable using TableAdapter.

For Select
TestDT = TestAdapter.SelectTest();


For Insert
TestDT = TestAdapter.Insert(“Pass Parameter to Stored Procedure for Insert”)


For Delete
TestDT = TestAdapter.Delete(“Pass Parameter for Delete Record like ID”)


For Update
TestDT = TestAdapter.Update(“Pass Parameter for Update Record”)