This blog talks about the Database Access in ASP.NET.

ASP.NET allows the following sources of data to be  accessed and used:

1.       Databases

2.       XML documents

3.       Business Objects

4.       Flat files

ADO.NET is the technology that provides the bridge between various ASP.NET control objects and the backend data source. It is an object-oriented set of labraries that allows you to interact with data sources.

Accessing a Database from an ASP Page

1.       Create an ADO connection to a  database.

2.       Open the  database connection

3.       Create an ADO recordset

4.       Open the recordset

5.       Extract the data you need from the  recordset

6.       Close the recordset

7.       Close the connection  

Data Providers

ADO.NET Data providers are class libraries that allow a common way  to interact with specific data sources or protocols. The library APIs have prefixes that indicate which  provider :

Provider Name       API Prefix                      Data Source Description

ODBC                          Odbc                            Data Sources with an ODBC interface.Normally older  data bases.

OleDb                          Oledb                          Data Sources that expose an OleDB 

                                                                          interface, i.e. Access  Or Excel.

Oracle                            Oracle                      For oracle Database

SQL                                  Sql                            For interacting with Microsoft SQL Server.

Borland                           Bdp                         Generic access to many databases such as                                                                                     Interbase, SQL Server, IBM DB2 and oracle.

The SqlConnection Object

To interact with a database, you must have a connection to it. The connection helps identify the database server, the database name, user name, password, and other parameters that are required for connecting to the data base. A connection object is used by command objects so they will know which database to execute the command on.

The SqlCommand Object

The process of interacting with a database means that you must specify the actions you want to occur. This is done with a command object. You use a command object to send SQL statements to the database. A command object uses a connection object to figure out which database to communicate with. You can use a command object alone, to execute a command directly, or assign a reference to a command object to an SqlDataAdapter, which holds a set of commands that work on a group of data as described below.

The SqlDataReader Object

Many data operations require that you only get a stream of data for reading. The data reader object allows you to obtain the results of a SELECT statement from a command object.

DataSet Object

DataSet is a disconnected orient architecture that means there is no need of active connections during work with datasets and it is a collection of DataTables and relations between tables. It is used to hold multiple tables with data. You can select data form tables, create views based on table and ask child rows over relations. Also DataSet provides you with rich features like saving data as XML and loading XML data.

SqlDataAdapter

DataAdapter  will acts as a  Bridge between Dataset and database. This dataAdapter object is used to read the data from database and bind that data to dataset. DataAdapter is  a disconnected oriented architecture.

Example:

SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["cons"].ConnectionString);
    SqlCommand cmd;

Connected Model:

try
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "update EmployeeDetail set FirstName=@FirstName,LastName=@LastName,Address=@Address,FatherName=@FatherName,MotherName=@MotherName,MobileNo=@MobileNo,DOB=@DOB,RoleId=@RoleName  where id=@id";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(newSqlParameter("@FirstName", FirstName.Text));
            cmd.Parameters.Add(newSqlParameter("@LastName", LastName.Text));
            cmd.Parameters.Add(newSqlParameter("@Address", Address.Text));
            cmd.Parameters.Add(newSqlParameter("@FatherName", FatherName.Text));
            cmd.Parameters.Add(newSqlParameter("@MotherName", MotherName.Text));
            cmd.Parameters.Add(newSqlParameter("@MobileNo", MobileNo.Text));
            cmd.Parameters.Add(newSqlParameter("@DOB", DOB.Text));
            cmd.Parameters.Add(newSqlParameter("@RoleName", Rolelist.SelectedValue));
            cmd.Parameters.Add(newSqlParameter("@id",userid));
          int i=  cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
        }
        finally
        {
           
            
            con.Close();
        }
 
        }

Disconnected model:

cmd = newSqlCommand();
        try
        {
            cmd.Connection = con;
            con.Open();
            cmd.CommandText = "select * from RoleTable";
            cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
        SqlDataAdapter da = newSqlDataAdapter(cmd);
        DataTable dt = newDataTable();
        da.Fill(dt);
        DropDownListRole.DataSource = dt;
       DropDownListRole.DataTextField = "RoleName";
        DropDownListRole.DataValueField = "RoleId";
        
        DropDownListRole.DataBind(); 
 
        }
        catch (Exception ex)
        {
        }
        finally
        {
            con.Close();
        }