How to make database connectivity in asp.net

[rapaneda01]

Hello,
i m looking for the database connectivity in asp.net with sql server 2005 or ms access 2007?
please anybody solve my query.
thanks in advance, looking for your response.

[kvprajapati]

>please anybody solve my query.

Please post your code here so somebody can help you.

[mustaffa hasan]

Opens the DataAdapter Configuration wizard

Choose the New Connection Button

Click on the change button to change the Data Source Option to(SQL Server or MS Access(OLEDB))

Choose the Server name in the Combo Box

Check The Connection(Test Connection)

Click Ok

[omar isaid]

I designed and implemented database connectivity between .net application and Microsoft Access database and .net application and Microsoft SQL Server database , either 2005 or 2008
How to access the DataBase take place in two approaches :
1 - Disconnected Mode

This approache retrieve data from the DataBase and store it at a DataSet either typed or untyped DataSet , and the programmer fill the DataSet from the DataBase to retrieve data .and to update the DataBase this occur by submit the changes to the DataSet

2 - connected Mode

each Sql transaction (select,insert,delete,update query) against database either Microsoft Access DataBase or Microsoft Sql Server DataBase needs to open and close connection against the DataBase

[omar isaid]

using System;
using System.Collections.Generic;
using System.Linq;
//the below code is designed and implemented by Omar Adnan Isaid
//at : 1 December 2011
//below code intended to add a row at a DataBase TestDB 
//at the Page load method , not real scenario .But , considered 
//as an educational scenario
//the below code is tested and debloyed , at SQL Server 208 
//enterprise edition and Web Developer 2010 , worked successfully 
//100 %


using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

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

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            //the below code will insert values into an Sql DataBase called TestDB
            //using the Disconnected DataAccess approach

            //the below defines the connection String for the DataBase 
            //used by the command
            // Data Source : the server name where DataBase created
            //Initial Cataloga DataBase Name 
            //Integrated Security : The security to access the DataBase , can be 
            // 1- Windows Authentication : not recommended in public webSite
            // 2 - Sql Server authentication : not recommended where DataBase have 
            //sensative information used at intranet application only
            //the below connectionString defined for DataBase supported by 
            //Windows authentication
            SqlConnection con = new SqlConnection("Data Source=OMAR-PC\\PRIMARYDB;" +
                "Initial Catalog=TestDB;" + 
                "Integrated Security=SSPI");
           
            //values to be inserted at the table Table_1 , at the DataBase 
            //TestDB
            int id = 1;
            string name = "Ahmed";
            
            //to do update or  select operation , simply you need to fill below 
            //string variable with the update or select operation
            string sql = "INSERT INTO Table_1 VALUES(" + id + ",'" + name + "')";


            SqlCommand com = new SqlCommand(sql,con);

            //if you try to open already open connection 
            //then an SqlException will be thrown
            if (con.State !=  ConnectionState.Open)
            { 
               //no worry to open connection
                con.Open();
            
            }

            //you can use : 
            // 1 - com.ExecuteNonQuery (); : there is no result to return 
            //from the DataBase in Insert , Delete , Update Transaction
            // 2 -  com.ExecuteScalar (); a single value will return from 
            //the dataBase , usually in case of aggregate functions
            // 3 - com.ExecuteReader () and this little bit more 
            //detailed story that 
            // A - an instance of SqlDataReader will be used to store the 
            //result of com.ExecuteReader () , and com.ExecuteReader () 
            //is read - only forward stream , each row is retrieved by 
            //SqlDataReader.Read method
            
            //the below AffectedRow variable represented the  number of new 
            //inserted rows 
            //and the execution of com.ExecuteNonQuery(); against the 
            //DataBase will take place , may be failed due to a certain proble 
            //and so an exception will thrown and no action is taken against the 
            //DataBase
            int AffectedRow=com.ExecuteNonQuery();
            //if you try to close already close connection 
            //then nothing will occur
            con.Close();
        }
        catch(SqlException ex)
        {
            //an Exception may be thrown may be at : 
            // 1 - execute the command against the DataBase
            // 2 - open or close the connection 
            // 3 - another purpose
            Response.Write("SqlException Thrown : " + ex.Message);
        }
        catch (Exception ex)
        {
            Response.Write("General Exception : " + ex.Message);
        }        
    }
}

[omar isaid]

Now the below code to acces the Sql Server DataBase , to access Microsoft Access DataBase you need to do the below modifications and simply you can access the Access DataBase :

1 - line 18 replaced with

using System.Data.OleDb;

2 - you need to replace line 40 with

OleDbConnection con1 = new OleDbConnection("Data Source=OMAR-PC\\PRIMARYDB;" +
                 "Initial Catalog=TestDB;" +
                 "Integrated Security=SSPI");

3 -replace line 54 with the below

OleDbCommand com = new OleDbCommand(sql, con);

4 - at line 88
replace sqlException with OleDbException