ado.net and stored procedures
DESCRIPTION
ADO.Net and Stored Procedures. By Radhika Malladi. Introduction. ADO.Net : database API, used for managed applications It consists of a set of classes Integrates with XML It provides components for creating distributed, data-sharing applications - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/1.jpg)
ByRadhika Malladi
![Page 2: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/2.jpg)
IntroductionADO.Net : database API, used for managed
applications
It consists of a set of classesIntegrates with XMLIt provides components for creating
distributed, data-sharing applicationsIt is used to connect to data source for
retrieving, updating and manipulating data.
![Page 3: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/3.jpg)
Data providers are used to connect to the database, to execute commands and retrieving results
ADO.Net provides two data providers
SQL Server .Net provider – Interfaces MicrosoftSQLServer with Managed applications(System.data.SqlClient)
OLE DB .Net provider – Interfaces databases with UnManaged OLE DB providers(System.data.OleDb)
Introduction cont.
![Page 4: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/4.jpg)
ADO.Net is designed to meet the following goals:
1. Leverage current ADO knowledge2. Support N-tier programming model
("Any number of levels arranged above another, each serving distinct and separate tasks.“)
Done by using Data Set because Data Set works well with all providers
3. Integrating XML
Design Goals
![Page 5: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/5.jpg)
Two main components:
1. Data Set – disconnected architecture of ADO.Net
2. .Net data provider components – for data manipulation and read-only access to dataConnection – to make connections to databaseCommand – access to database commandsData Reader – provides stream of data from data sourceData Adapter – bridge between Data Set object and
data source
ADO.Net Components
![Page 6: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/6.jpg)
Architecture
![Page 7: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/7.jpg)
Connection :SqlConnection conn = new SqlConnection
("server=localhost;database=a1;uid=sa;pwd=");
SqlConnection conn = new SqlConnection ("server=xyz\malladi;database=a1;uid=sa;pwd=");
SqlConnection conn = new SqlConnection ("server=xyz\malladi;database=a1;uid=sa;pwd=;min pool size=10;max pool size=50;connect timeout=10;Integrated Security = false;Pooling = true;");
OleDbConnection conn = new OleDbConnection ("provider=sqloledb;data source=localhost;OLE DB Services=-2" +
"initial catalog=a1;user id=sa;password=");
.Net provider Components
![Page 8: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/8.jpg)
Command:SqlCommand cmd = new SqlCommand ("select * from users", conn);
Properties: CommadTimeout , CommandText
Methods:ExecuteNonQuery() – This returns number of rows affected
Ex: Insert, Delete, Update (no return values)
ExecuteScalar() – single row, single column valuesEx: count, min, max, sum, avg etc and to retrive BLOBs
ExecuteReader()
.Net Components cont.
![Page 9: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/9.jpg)
Data Reader:
SqlDataReader reader = cmd.ExecuteReader ();
ExecuteReader() – obtains query results quickly for read-only.Ex: Select
reader.close();
Methods:GetName – retrieve field namesGetValue – retrieve field values(returns obj)GetOrdinal – converts field name into numeric index
.Net Components cont.
![Page 10: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/10.jpg)
Data Adapter:
SqlDataAdapter adapter = new SqlDataAdapter ("select * from users", "server=localhost;database=a1;uid=sa;pwd=");
.Net Components cont.
![Page 11: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/11.jpg)
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Inetpub\\wwwroot\\Assign2\\App_Data\\a2.mdf;Integrated Security=True;User Instance=True");
try { con.Open(); SqlCommand cmd = new SqlCommand("select role from users where username = '" +
TextBox1.Text.Trim() + "' and password = '" + TextBox2.Text.Trim() + "'", con); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { role = dr.GetString(dr.GetOrdinal("role")); break; } } catch (SqlException ex) { Response.Write(ex.Message); } finally { con.Close(); }
Example (ExecuteReader)
![Page 12: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/12.jpg)
SqlConnection connection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=c:\\inetpub\\wwwroot\\Assign2\\App_Data\\a2.mdf;Integrated Security=True;User Instance=True");
try{ connection.Open(); StringBuilder builder = new StringBuilder(); builder.Append("select count (*) from users where username = \'"); builder.Append(username); builder.Append("\' and cast (rtrim (password) as varbinary) = cast (\'"); builder.Append(password); builder.Append("\' as varbinary)"); SqlCommand command = new SqlCommand(builder.ToString(),connection); int count = (int)command.ExecuteScalar(); return (count > 0); } catch (SqlException) { return false; } finally { connection.Close(); }
Example (ExecuteScalar)
![Page 13: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/13.jpg)
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Inetpub\\wwwroot\\Assign2\\App_Data\\a2.mdf;Integrated Security=True;User Instance=True");
try { con.Open(); SqlCommand cmd = new SqlCommand("insert into users
(username,password,role)values('" + TextBox6.Text.Trim() + "','" + TextBox7.Text.Trim() + "','" + DropDownList1.SelectedValue + "')", con);
int noofrows = cmd.ExecuteNonQuery(); if (noofrows != 0) Response.Write("user created successfully"); } catch (SqlException ex) { Response.Write(ex.Message); } finally { con.Close(); }
Example (ExecuteNonQuery)
![Page 14: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/14.jpg)
Set based access – captures query into memory and supports traversal through result set
This has 2 classesData Set : in-memory databaseData Adapter : bridge between Data Set and
data sourcePerform database queries create DataTables containing query resultsCapable of writing changes made to data tables back
to database
Data Set and Data Adapters
![Page 15: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/15.jpg)
Two versions: SqlDataAdapter and OleDbDataAdapter
Main Methods of Data Adapter are Fill and Update
Namespace – system.data.common.DbDataAdapter
Data Adapter
![Page 16: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/16.jpg)
Fill:
SqlDataAdapter adapter = new SqlDataAdapter ("select * from users", "server=localhost;database=a1;uid=sa;pwd="); DataSet ds = new DataSet ();
adapter.Fill (ds, “users");
Update :adapter.Update (table);
Builder for Insert, Update, Delete:SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
Data Adapter cont.
![Page 17: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/17.jpg)
DataSet object – supports disconnected, distributed data with ADO.Net
Can be used with multiple and differing data sources
It represents complete set of data like related tables, constraints and relationships among tables
Data Set
![Page 18: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/18.jpg)
Data Set cont.
![Page 19: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/19.jpg)
DataTable Collection : contains collection of tables
DataRelationCollection: contains relationships of tables
ExtendingProperties: Property Collection where customized information can be placedEx: Date/Time when data is generated
Data Set cont.
![Page 20: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/20.jpg)
SqlDataAdapter adapter = new SqlDataAdapter ("select * from titles", "server=localhost;database=pubs;uid=sa;pwd=");
DataSet ds = new DataSet (); adapter.Fill (ds, "Titles"); // Create a new DataRow DataTable table = ds.Tables["Titles"]; DataRow row = table.NewRow (); // Initialize the DataRow row["title_id"] = "JP1001"; row["title"] = "Programming Microsoft .NET"; row["price"] = "59.99"; row["ytd_sales"] = "1000000"; row["type"] = "business"; row["pubdate"] = "May 2002"; // Add the DataRow to the DataTable table.Rows.Add (row);
Example to insert records
![Page 21: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/21.jpg)
DataRow[] rows = table.Select ("title_id = 'JP1001'");DataRow[] rows = table.Select ("price < 10.00");DataRow[] rows = table.Select ("pubdate >= '#1/1/2000#'"
);DataRow[] rows = table.Select ("state in ('ca', 'tn', 'wa')"); DataRow[] rows = table.Select ("state like 'ca*'");DataRow[] rows = table.Select ("isnull (state, 0) = 0");DataRow[] rows = table.Select ("state = 'tn' and zip like '37
*'");
Examples to select records
![Page 22: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/22.jpg)
SqlDataAdapter adapter = new SqlDataAdapter ("select * from titles", "server=localhost;database=pubs;uid=sa;pwd=");
DataSet ds = new DataSet (); adapter.Fill (ds, "Titles");DataRow[] rows = table.Select ("ytd_sales > 10000");foreach (DataRow row in rows) row["price"] = (decimal) row["price"] + 10.00m;
Example to update records
![Page 23: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/23.jpg)
SqlDataAdapter adapter = new SqlDataAdapter ("select * from titles", "server=localhost;database=pubs;uid=sa;pwd=");
DataSet ds = new DataSet (); adapter.Fill (ds, "Titles"); DataRow[] rows = table.Select
("ytd_sales < 10000 OR isnull (ytd_sales, 0) = 0"); foreach (DataRow row in rows) row.Delete ();
Example to delete records
![Page 24: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/24.jpg)
SqlDataAdapter adapter = new SqlDataAdapter ("select * from titles", "server=localhost;database=pubs;uid=sa;pwd=");
SqlCommandBuilder builder = new SqlCommandBuilder (adapter); DataSet ds = new DataSet (); adapter.Fill (ds, "Titles"); // Insert a record DataTable table = ds.Tables["Titles"]; DataRow row = table.NewRow (); row["title_id"] = "JP1001"; row["title"] = "Programming Microsoft .NET"; row["price"] = 59.99m; row["ytd_sales"] = 1000000; row["type"] = "business"; row["pubdate"] = new DateTime (2002, 5, 1); table.Rows.Add (row); // Update the database adapter.Update (table);
Example for Updating the database
![Page 25: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/25.jpg)
User defined command added to a database
Executes faster as they are already complied
Improves performance
ADO.Net supports stored procedures
An Example of stored procedure is..
Stored Procedures
![Page 26: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/26.jpg)
CREATE PROCEDURE proc_TransferFunds @Amount money, @From char (10), @To char (10) AS BEGIN TRANSACTION UPDATE Accounts SET Balance = Balance @Amount
WHERE Account_ID = @From IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION RETURN END UPDATE Accounts SET Balance = Balance + @Amount WHERE Account_ID = @To IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION RETURN END COMMIT TRANSACTION GO
![Page 27: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/27.jpg)
SqlConnection conn = new SqlConnection ("server=localhost;database=mybank;uid=sa;pwd="); try {
conn.Open ();
SqlCommand cmd = new SqlCommand ("proc_TransferFunds", conn);
cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add ("@amount", 1000); cmd.Parameters.Add ("@from", 1111); cmd.Parameters.Add ("@to", 2222); cmd.ExecuteNonQuery (); }
catch (SqlException ex) { // TODO: Handle the exception }
finally { conn.Close (); }
![Page 28: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/28.jpg)
SqlConnection conn = new SqlConnection ("server=localhost;database=mybank;uid=sa;pwd=");
try { conn.Open (); // Debit $1,000 from account 1111 SqlCommand cmd = new SqlCommand
("update accounts set balance = balance -1000 where account_id = '1111'", conn);
cmd.ExecuteNonQuery (); // Credit $1,000 to account 2222 cmd.CommandText = "update accounts set balance = " +
"balance + 1000 where account_id = '2222'"; cmd.ExecuteNonQuery (); } catch (SqlException ex) { // TODO: Handle the exception } finally { conn.Close (); }
Transacted Commands Example
![Page 29: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/29.jpg)
SqlTransaction trans = null; SqlConnection conn = new SqlConnection ("server=localhost;database=mybank;uid=sa;pwd="); try { conn.Open ();
// Start a local transaction trans = conn.BeginTransaction (IsolationLevel.Serializable); // Create and initialize a SqlCommand object SqlCommand cmd = new SqlCommand (); cmd.Connection = conn; cmd.Transaction = trans; // Debit $1,000 from account 1111 cmd.CommandText = "update accounts set balance =
balance - 1000 where account_id = '1111'"; cmd.ExecuteNonQuery (); // Credit $1,000 to account 2222 cmd.CommandText = "update accounts set balance = " +
"balance + 1000 where account_id = '2222'"; cmd.ExecuteNonQuery (); // Commit the transaction (commit changes) trans.Commit (); } catch (SqlException) { // Abort the transaction (roll back changes) if (trans != null)
trans.Rollback (); } finally { conn.Close (); }
Transacted Commands Example
![Page 30: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/30.jpg)
SqlConnection conn = new SqlConnection ("server=localhost;database=mybank;uid=sa;pwd=");
try { conn.Open (); // Create and initialize a SqlCommand object SqlCommand cmd = new SqlCommand("update accounts set balance = bala
nce + @amount " + "where account_id = @id", conn); cmd.Parameters.Add ("@amount", SqlDbType.Money); cmd.Parameters.Add ("@id", SqlDbType.Char); // Debit $1,000 from account 1111 cmd.Parameters["@amount"].Value = -1000; cmd.Parameters["@id"].Value = "1111"; cmd.ExecuteNonQuery (); // Credit $1,000 to account 2222 cmd.Parameters["@amount"].Value = 1000; cmd.Parameters["@id"].Value = "2222"; cmd.ExecuteNonQuery (); } catch (SqlException ex) { // TODO: Handle the exception } finally { conn.Close (); }
Parameterized Commands Example
![Page 31: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/31.jpg)
ADO.Net by Alex HomerProgramming Microsoft .Net by Jeff
Prosisehttp://msdn2.microsoft.com/en-us/library/
e80y5yhx(VS.71).aspxhttp://www.ondotnet.com/pub/a/dotnet/ex
cerpt/progvisbasic_ch08/index.html
References
![Page 32: ADO.Net and Stored Procedures](https://reader030.vdocuments.us/reader030/viewer/2022033021/56815ca2550346895dcaa8cf/html5/thumbnails/32.jpg)
Thank youThank you