IMP ADO.NET Architecture

ADO.NET Architecture

http://www.allaboutcomputerprogramming.com/adonet-architecture.html

ADO.NET Architecture ADO.NET is a data access technology from Microsoft .Net Framework , which provides communication between relational and non-relational systems through a common set of components . ADO.NET consist of a set of Objects that expose data access services to the .NET environment. ADO.NET is built for disconnected architecture , so it enables truly disconnected Data Access and Data Manipulation through its Dataset Object, which is completely independent from the Data Source. The two key components of ADO.NET are Data Providers and DataSet . The .Net Framework includes mainly three Data Providers for ADO.NET. They are the Microsoft SQL Server Data Provider, OLEDBData Provider and ODBC Data Provider. SQL Server uses theSqlConnection object , OLEDB uses the OleDbConnection Object and ODBC uses OdbcConnection Object respectively. The four Objects from the .Net Framework provide the functionality of Data Providers in the ADO.NET. They are Connection Object, Command Object , DataReader Object and DataAdapter Object. The Connection Object provides physical connection to the Data Source. The Command Object uses to perform SQL statement or stored procedure to be executed at the Data Source. The DataReader Object is a stream-based , forward-only, read-only retrieval of query results from the Data Source, which do not update the data. Finally the DataAdapter Object , which populate a Dataset Object with results from a Data Source . DataSet provides a disconnected representation of result sets from the Data Source, and it is completely independent from the Data Source. DataSet provides much greater flexibility when dealing with related Result Sets. DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects. The DataTable contains a collection of DataRow and DataCoulumn Object which contains Data. The DataAdapter Object provides a bridge between the DataSet and the Data Source  Connection String is a normal String representation which contains Database connection information to establish the connection between Datbase and the Application. The Connection String includes parameters such as the name of the driver, Server name and Database name , as well as security information such as user name and password. Data providers use a connection string containing a collection of parameters to establish the connection with the database. The .NET Framework provides mainly three data providers: Microsoft SQL Server, OLEDB and ODBC. Here you can see how to make connection string to these ADO.NET Data Providers.

Invalid ad code! Yllix media

How to get a Connection String and How to Create a Connection

Step1-> Create a Sql Database

   i)Go to Website->Click Add New Item->Select Sql Database-> Add

Step2:Create a Table

i)Go to View->Select Server Explorer->Under Database connection double click the Database that you have created->Right Click on Tables->Add new table->Name the table example “Product”->create table.

Step 3->now simply select the table from the server explorer section and drag it to the design part.

Step4->Now go to solution explorer and you will see a “Web.Config” file is Created.

Step 5-> Double click the web.config file.

You will see a source code like this:

<configuration>
<appSettings/>
<connectionStrings>
 providerName="System.Data.SqlClient" />
<add name="DatabaseConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>
From here we can see that the Connection string is: “DatabaseConnectionString1”

Now to create a connection.

Here we can see: Button1-named “Sql connection” is used Label1->is used for displaying the result during runtime.Here all the coding is written under the Button1. *Note-> using System.Data.SqlClient is recommended is order to establish a connection Source code

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class adonet : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

  String mysqlconstring = System.Configuration.ConfigurationManager.ConnectionStrings["DatabaseConnectionString1"].ConnectionString;

 SqlConnection con = new SqlConnection(mysqlconstring);

        con.Open();

        Label1.Text = "Connected";

    }

OutPut

ADO.NET Command The Command Object in ADO.NET executes SQL statements and Stored Procedures against the data source specified in the Connection Object. The Command Object required an instance of a Connection Object for executing the SQL statements. That is, for retrieving data or execute an SQL statement against a Data Source , you have to create a Connection Object and open a connection to the Data Source, and assign the open connection to the connection property of the Command Object. When the Command Object return result set , a Data Readeris used to retrieve the result set. ADO.NET ExecuteNonQuery in SqlCommand Object  ExecuteNonQuery() is one of the most frequently used method in SqlCommand Object and is used for executing statements that do not return result set. ExecuteNonQuery() performs Data Definition tasks as well as Data Manipulation tasks also. The Data Definition tasks like creating Stored Procedures and Views perform by ExecuteNonQuery() . Also Data Manipulation tasks like Insert , Update and Delete perform by ExecuteNonQuery(). ADO.NET ExecuteReader in SqlCommand Object  ExecuteReader() in SqlCommand Object send the SQL statements to Connection Object and populate a SqlDataReader Object based on the SQL statement. When the ExecuteReader method in SqlCommand Object execute , it instantiate a SqlClient.SqlDataReader Object The SqlDataReader Object is a stream-based , forward-only, read-only retrieval of query results from the Data Source, which do not update the data. The SqlDataReader cannot be created directly from code, they created only by calling the ExecuteReader method of a Command Object. Summary ADO.NET is the .NET technology for interacting with data sources.  You have several Data Providers, which allow communication with different data sources, depending on the protocols they use or what the database is.  Regardless, of which Data Provider used, you'll use a similar set of objects to interact with a data source.  The SqlConnection object lets you manage a connection to a data source.  SqlCommand objects allow you to talk to a data source and send commands to it.  To have fast forward-only read access to data, use the SqlDataReader.  If you want to work with disconnected data, use a DataSet and implement reading and writing to/from the data source with a SqlDataAdapter.