1 / 32

ADO.Net and Stored Procedures

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

melora
Download Presentation

ADO.Net and Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADO.Net and Stored Procedures By Radhika Malladi

  2. 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 • It is used to connect to data source for retrieving, updating and manipulating data.

  3. Introduction cont. • 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)

  4. Design Goals • ADO.Net is designed to meet the following goals: • Leverage current ADO knowledge • 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 • Integrating XML

  5. ADO.Net Components • Two main components: • Data Set – disconnected architecture of ADO.Net • .Net data provider components – for data manipulation and read-only access to data Connection – to make connections to database Command – access to database commands Data Reader – provides stream of data from data source Data Adapter – bridge between Data Set object and data source

  6. Architecture

  7. .Net provider Components • 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=");

  8. .Net Components cont. • 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 values Ex: count, min, max, sum, avg etc and to retrive BLOBs ExecuteReader()

  9. .Net Components cont. • Data Reader: SqlDataReader reader = cmd.ExecuteReader (); ExecuteReader() – obtains query results quickly for read-only. Ex: Select reader.close(); Methods: GetName – retrieve field names GetValue – retrieve field values(returns obj) GetOrdinal – converts field name into numeric index

  10. .Net Components cont. • Data Adapter: SqlDataAdapter adapter = new SqlDataAdapter ("select * from users",     "server=localhost;database=a1;uid=sa;pwd=");

  11. Example (ExecuteReader) • 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(); • }

  12. Example (ExecuteScalar) • 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(); • }

  13. Example (ExecuteNonQuery) • 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(); • }

  14. Data Set and Data Adapters • Set based access – captures query into memory and supports traversal through result set • This has 2 classes Data Set : in-memory database Data Adapter : bridge between Data Set and data source Perform database queries create DataTables containing query results Capable of writing changes made to data tables back to database

  15. Data Adapter Two versions: SqlDataAdapter and OleDbDataAdapter Main Methods of Data Adapter are Fill and Update Namespace – system.data.common.DbDataAdapter

  16. Data Adapter cont. • 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);

  17. Data Set • 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

  18. Data Set cont.

  19. Data Set cont. • DataTable Collection : contains collection of tables • DataRelationCollection: contains relationships of tables • ExtendingProperties: Property Collection where customized information can be placed Ex: Date/Time when data is generated

  20. Example to insert records 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);

  21. Examples to select records • 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*'");

  22. Example to update records 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;

  23. Example to delete records 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 ();

  24. Example for Updating the database 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);

  25. Stored Procedures • 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..

  26. 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

  27. 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 (); }

  28. Transacted Commands Example 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 (); }

  29. Transacted Commands Example • 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 (); }

  30. Parameterized Commands Example 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 = balance + @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 (); }

  31. References • ADO.Net by Alex Homer • Programming Microsoft .Net by Jeff Prosise • http://msdn2.microsoft.com/en-us/library/e80y5yhx(VS.71).aspx • http://www.ondotnet.com/pub/a/dotnet/excerpt/progvisbasic_ch08/index.html

  32. Thank you

More Related