ADO.Net – Notes

  • ADO stands for ActiveX Data Objects

ADO.NET provides two models for data access: a connected model where I can keep the connection with the database and perform data access, and another way is to get all the data in ADO.NET objects that let us perform data access on disconnected objects.

ADO Command Objects

The ADO Command object is used to execute a single query against a database. The query can perform actions like creating, adding, and retrieving, deleting or updating records

Major feature of the Command object is the ability to use stored queries and procedures with parameters

Properties

Property Description
ActiveConnectionSets or returns a definition for a connection if the connection is closed, or the current Connection object if the connection is open
CommandTextSets or returns a provider command
CommandTimeoutSets or returns the number of seconds to wait while attempting to execute a command
CommandTypeSets or returns the type of a Command object
NameSets or returns the name of a Command object
PreparedSets or returns a Boolean value that, if set to True,indicates that the command should save a prepared version of the query before the first execution
StateReturns a value that describes if the Command object is open, closed,connecting, executing or retrieving data

Methods

Method Description
Cancel Cancels an execution of a method
CreateParameter Creates a new Parameter object
Execute Executes the query, SQL statement or procedure in the CommandText property

Connection Object

The ADO Connection Object is used to create an open connection to a data source. Through this connection, you can access and manipulate a database.

The common way to access a database from inside an ASP page is to:

  1. Create an ADO connection to a database
  2. Open the database connection
  3. Create an ADO record set
  4. Open the record set
  5. Extract the data you need from the record set
  6. Close the record set
  7. Close the connection
Property Description
Attributes Sets or returns the attributes of a Connection object
CommandTimeout Sets or returns the number of seconds to wait while attempting to execute a command
ConnectionString Sets or returns the details used to create a connection to a data source
ConnectionTimeout Sets or returns the number of seconds to wait for a connection to open
CursorLocation Sets or returns the location of the cursor service
DefaultDatabase Sets or returns the default database name
IsolationLevel Sets or returns the isolation level
Mode Sets or returns the provider access permission
Provider Sets or returns the provider name
State Returns a value describing if the connection is open or closed
Version Returns the ADO version number
Method Description
BeginTrans Begins a new transaction
Cancel Cancels an execution
Close Closes a connection
CommitTrans Saves any changes and ends the current transaction
Execute Executes a query, statement, procedure or provider specific text
Open Opens a connection
OpenSchema Returns schema information from the provider about the data source
RollbackTrans Cancels any changes in the current transaction and ends the transaction

Connection String :-

// Web.config file

<connectionStrings>

  <add name=”MyConnectionString”

     connectionString =”Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;

                        Integrated Security=True;User Instance=True” />

</connectionStrings>

// Aspx.cs file

private SqlConnection con = null;
con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);

— Saving data to database with passing with Parameters —-

            sqlcon = new SqlConnection(DatabaseCredientials);

            sqlcon.Open();

            sqlcmd = new SqlCommand();

            sqlcmd.Connection = sqlcon;

            sqlcmd.CommandType = CommandType.StoredProcedure;

            sqlcmd.CommandText = “dbo.sp_Ins_Tasklog_details”;

            sqlcmd.Parameters.Add(“@TaskDetails”, SqlDbType.NVarChar, 255).Value = taskDetail;

            sqlcmd.Parameters.Add(“@Department”, SqlDbType.NVarChar, 255).Value =dept;

            sqlcmd.ExecuteNonQuery();

—-Retrieving records from DB to DS—-

            sqlcon = new SqlConnection(DatabaseCredientials);

            sqlcon.Open();

            sqlcmd = new SqlCommand();

            sqlcmd.Connection = sqlcon;

           sqlcmd.CommandType = CommandType.StoredProcedure;

            sqlcmd.CommandText = “GetSprintDetails”;

            sqlad = new SqlDataAdapter(sqlcmd);

            dsDetails = new DataSet();

            sqlad.Fill(dsDetails);

         if (ds.Tables[0].Rows.Count != 0)

Storing the Result

  • DataReader – A DataReader is an object that can be used to access the results sequentially from a database. The DataReader is used to get forward only sequential results as the query executes. This is used with the Command object (we will see the usage shortly).
  • Dataset – The Dataset can be thought of as an in-memory representation of a database. A DataSet is a disconnected data access object. The result of the query can be stored in a Dataset. The DataSetcontains DataTables. The DataTables contain DataRow and DataColumns. A DataSet or a DataTablecan be used with a Command and a DataAdapter object to store query results.
  • DataAdapter – A DataAdapter object is used to fill a DataSet/DataTable with query results. This can be thought of as the adapter between the connected and disconnected data models. A Command object will be used to execute the query and a DataAdapter will use this Command object and fill the query results coming from the database into a DataSet/DataTable.
  • DataTable represents a single table in the database. It has rows and columns. There is no much difference between dataset and datatable, dataset is simply the collection of datatables.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *