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();

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();

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”)

Leave a Reply

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

6 + 2 =