Download
1 / 32
320 likes | 444 Views
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
E N D
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 • It is used to connect to data source for retrieving, updating and manipulating data.
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)
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
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
.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=");
.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()
.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
.Net Components cont. • Data Adapter: SqlDataAdapter adapter = new SqlDataAdapter ("select * from users", "server=localhost;database=a1;uid=sa;pwd=");
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(); • }
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(); • }
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(); • }
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
Data Adapter Two versions: SqlDataAdapter and OleDbDataAdapter Main Methods of Data Adapter are Fill and Update Namespace – system.data.common.DbDataAdapter
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);
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
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
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);
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*'");
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;
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 ();
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);
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..
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
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 (); }
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 (); }
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 (); }
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 (); }
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
