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

Leave a Reply

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

9 + 7 =


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="">