ado.net
TRANSCRIPT
ADO.NETADO.NET
By K.Yesu Ratnam
What is ADO.NETWhat is ADO.NETADO.NET is not a different
technology, in simple terms you can think of a set of classes that can be used to interact with data sources like databases and xml files
This data can, then be consumed in any .net application.
ADO.net stands for Microsoft ActiveX Data Objects.
The following are , a few of the different types of .net applications that use ADO.NET to connect to a database,execute commands and retrieve data.
Asp.net Web applications Console Applications Window Applications
Dot Net Data ProvidersDot Net Data ProvidersSqlServer System.Data.SqlClient
Oracle System.Data.Oracle
Odbc System.Data.Odbc
Oledb System.Data.Oledb
ADO.NETADO.NETCONNECTION ORIENTED MODELDISCONNECTED ORIENTED
MODEL
CONNECTION ORIENTED CONNECTION ORIENTED MODELMODELWhenever an application uses
the connection oriented model to interact with the db then the connectivity between the application and the database has to be maintained always.
Connection OrientedConnection Oriented
Whenever an user executes any statement other than a select then command object can be binded directly to the application
If the user executes a select statement then dataReader is used to bind the result to the application
Application db
ConnectionCommandData Reader
DATA PROVIDERS
Disconnected Oriented Disconnected Oriented Model in ADO.netModel in ADO.netWhen the user interacting with the db using
this model then while performing the manipulations or navigations on the data connectivity between the application and the database is not required
Note: When ever the data is been updated on to the database then the connectivity is required in the disconnected model.
DISCONNECTED MODELDISCONNECTED MODEL
DatabaseDataSetApplication
Data Adapter Connection
Data View
Data Providers
This is available in client system
ADO.NET
DISCONNECTED MODEL CONNECTION ORIENTED MODEL
CONNECTION
DATA ADAPTER
DATA SET
DATA VIEW
UI
CONNECTION
COMMAND
DATA READER
UI
Used if the data has to be filtered,sorted or if the data has to be projected in page-wise
Used if the statement is select statement
ADO.NET OBJECTSADO.NET OBJECTSConnectionCommandDataReaderDataSetDataAdapter
Connection ObjectConnection ObjectTo interact with a database.Syntax:xxConnection con=new
xxConnection(“providerinfo”);Example(creating connection with Oledb):OledbConnection con=new
OledbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Documents and Settings/Meghana/Desktop/KRISHNA.mdb“);
Command ObjectCommand ObjectSend sql statements to the data
baseSyntax:
xxCommand cmd=new xxCommand(“sql stmt”);
Example: OleDbCommand cmd1 = new
OleDbCommand("select * from student", con);
DataReader ObjectDataReader ObjectIts allows you to obtain the
results of a select statement from a Command object.
For performance reasons, the data returned from a data reader is a fast forward only stream of data.
DataSet ObjectDataSet ObjectDataset objects are in-memory
representation of data. They contain multiple data table objects.
Which contain columns and rows,just like normal data base tables.
Cont..Cont..One of the most common way to
use a dataset is to load it from a relational database when the program starts, use various controls to display the data and let the user manipulate it interactively and the save the changes back to database when the program ends.
Data Adapter ObjectData Adapter ObjectThe data adapter makes it easy
to you to accomplish these things by helping to manage data in a disconnected mode.
The data adapter fills a DataSet object when reading the data and writes in a single batch when persistence changes back to the data base.
Connect to Data Base using Connect to Data Base using Oledb Data ProviderOledb Data ProviderSteps to Connect to the database 1.Using System.Data.Oledb 2.Establish the connection using
OledbConnection(“parameters”) 3.OledbCommand(“sqlstmt”); 4.Execute query
These four steps are mandatory to establish and insert the data into database.
Insert Data Into DatabaseInsert Data Into Database1.OleDbConnection con = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Documents and Settings/Meghana/Desktop/KRISHNA.mdb");
2. con.Open(); 3. OleDbCommand cmd=new OleDbCommand("insert into student
values('"+TextBox1 .Text+"','"+TextBox2 .Text+"','"+TextBox3.Text+"','"+TextBox4 .Text+"','"+TextBox5 .Text+"')",con);
4. cmd.ExecuteNonQuery();
Retrieve Data From Retrieve Data From DatabaseDatabaseOleDbCommand cmd1 = new
OleDbCommand("select * from student", con);
OleDbDataReader or = cmd1.ExecuteReader();
GridView1.DataSource = or; GridView1.DataBind();
Design the pageDesign the pageWith following fields First Name Last Name Qualification Mobile Location
Source CodeSource Code OleDbConnection con = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Documents and Settings/Meghana/Desktop/KRISHNA.mdb");
con.Open(); OleDbCommand cmd = new OleDbCommand("insert into
student values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox5.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "')", con);
cmd.ExecuteNonQuery(); TextBox1.Text = ""; TextBox2.Text = ""; TextBox3.Text = ""; TextBox4.Text = ""; TextBox5.Text = ""; con.Close(); Label5 .Text=" The data is stored successfully";
ADO.NET
SQL-SERVER ORACLE OleDB Providers ODBC providers
SQL Connection
SQL Command
SQL Datareader
SQL DataAdapter
Oracle Connection
Oracle Command
Oracle DataReader
Oracle DataAdapter
OleDB Connection
OleDB Command
OleDB Data Provider
OleDB DataAdapter
ODBC Connection
ODBC Command
ODBC DataProvider
ODBC DataAdapter
System.data.SqlClient
System.data.OracleClient
System.data.Oledb
System.data.ODBC
Syntax to define the Object◦ Dim objectName as new xxxConnection(“ProviderInfo”)
where xx can be either SQL,Oracle,Oledb or ODBC Provider Info
◦ To connect to MS-Access 2000 above versions Provider=microsoft.jet.oledb.4;datasource=databaseName.mdb
◦ To connect to SQL-Server db Provider=sqloledb.1;userid=sa;password=;database=database
name;datasource=servername Note if SQL Connection is used then provider=providername is
not required.◦ To Connect to ORACLE
Provider = oracleoledb.oracle;userid=scott;pwd=tiger;datasource = servername
OR Provider = msdaora.1;……. Note if oracle connection is used then provider= provider name
is not required.
To define Command Object ◦ Dim objectName as new xxxCommand([SQL
Statement,connection object/Connection String]) To define DataReader
◦ Dim objectName as xxxDataReader To define DataAdapter
◦ Dim objectName as xxxDataAdapter(Select Statement,<Connection Object / Connection String>)
◦ When ever the DataAdapter is defined using the above syntax then only the command relevant for the SelectCommand will be defined and in order to use the above commands they have to be build explicitly.
To define DataSet ◦ Dim objectName as new DataSet()
To define DataView ◦ Dim objectName as new
DataView(datasetName.DataMemberName)
Working with query to retrieve Working with query to retrieve resultresult
using System.Data.SqlClient;public partial class login : System.Web.UI.Page{ SqlConnection conn = new SqlConnection("data source=.;initial catalog=nutan;integrated security=sspi"); SqlCommand cmd; SqlDataReader dr; protected void Page_Load(object sender, EventArgs e) { conn.Open(); Response.Write("connected"); } protected void Button1_Click(object sender, EventArgs e) { cmd = new SqlCommand("select * from mylogin", conn); dr = cmd.ExecuteReader(); while (dr.Read()) { if (dr.GetValue(0).ToString() == TextBox1.Text && dr.GetValue(1).ToString() == TextBox2.Text) { Response.Redirect("Home.aspx"); break; }
} }}
All operationsAll operations
private void button2_Click(object sender, EventArgs e) { OleDbCommand cmd = new OleDbCommand("update emp set salary='"+textBox4 .Text+"',location='"+textBox5 .Text +"' where empid='"+textBox1 .Text +"' ", con); con.Open(); button1.Enabled = true; button4.Enabled = true; textBox1.Enabled = true; textBox1.Text = ""; textBox2.Enabled = true; textBox2.Text = ""; textBox3.Text = ""; textBox3.Enabled = true; textBox4.Text = ""; textBox5.Text = ""; int x=cmd.ExecuteNonQuery(); if(x>0) MessageBox.Show("Yes your data is updated"); else MessageBox.Show("Sorry No Records");
con.Close();
}
private void button1_Click(object sender, EventArgs e) { con.Open(); OleDbCommand cmd = new OleDbCommand("insert into emp
values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox4.Text + "')", con);
cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("Data Inserted Success fully"); textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; textBox5.Text = "";
}
private void button3_Click(object sender, EventArgs e) { button1.Enabled = false; button4.Enabled = false; OleDbCommand cmd = new OleDbCommand("select * from emp where
empid='"+textBox1 .Text +"'", con); con.Open(); OleDbDataReader dr=cmd.ExecuteReader(); while (dr.Read()) { textBox2.Text = dr.GetValue(1).ToString(); textBox3.Text = dr.GetValue(2).ToString(); textBox4.Text = dr.GetValue(3).ToString(); textBox5.Text = dr.GetValue(4).ToString(); textBox1.Enabled = false; textBox2.Enabled = false; textBox3.Enabled = false; button2.Enabled = true;
} con.Close(); }
private void button4_Click(object sender, EventArgs e) { con.Open(); OleDbCommand cmd = new OleDbCommand("delete from emp
where empid='" + textBox1.Text + "'", con); cmd.ExecuteNonQuery(); MessageBox.Show("The record is Deleted"); textBox1.Text = ""; con.Close();
}