ADO.NET and Stored Procedures

1. ADO.NET and Stored Procedures VENKATA CS 795 vpatloll@cs.odu.edu 00787704

2. Evolution of ADO.NET • The first model used to access database, DAO (data access model) was created for local databases. • Next RDO (Remote Data Object) & ADO (Active Data Object) for Client Server architectures. • ADO was a good architecture but as the language changes so is the technology. • ADO was a connected data access. Which raises concerns about Database Security and network traffic.

3. What is an ADO.NET • ADO stands for “ActiveX Data Objects”. • ADO.NET is used to connect application system and database server. • ADO.NET consists of a set of classes used to handle data access and data services. • ADO.NET uses XML to store and transfer data among applications, provide fast access of data for desktop and distributed applications. • ADO.NET is scalable and interoperable.

4. ADO.NET Architecture ADO.net Objects are the means to access and modify the data in the Database.

5. Connection object • Connects to databases. • Provider-specific classes • SqlConnection • OracleConnection • OleDbConnection. • Connections can be opened in two ways: • Explicitly by calling the “Open” method on the connection • Implicitly when using a DataAdapter. • Connections handle transactions

6. Example for connection SqlConnection conn = new SqlConnection (“server=localhost; database=pubs; uid=mukka; pwd=“); try{ conn.Open(); ….} catch (SqlConnection ex){ …..} finally { conn.Close(); }

7. Command Object • Command objects are used to execute commands to a database across a data connection. • These provide 3 methods to execute commands on database: • ExecuteNonQuery: Executes commands that have no return values such as INSERT, UPDATE or DELETE • ExecuteScalar: Returns a single value from a database query • ExecuteReader: Returns a result set by way of a DataReader object • Provider-specific classes • SqlCommand • OleDbCommand • Results are returned in the form of streams. Accessed by: • DataReader object • DataSet object via a DataAdapter.

8. Example for Command Object ExecuteReader (for read) ExecuteNonQuery (for updates) SqlConnection conn = new SqlConnection (“server=localhost; database=pubs; uid=mukka; pwd=“); try { conn.Open(); SqlCommand cmd = new SqlCommand (); cmd.CommandText= “delete from title where title_id = “xxxx”; cmd.Connection = conn; cmd.ExecuteNonQuery (); } catch (SqlException ex) { ….} finally { conn.Close();}

9. DataReader Object • Provides methods and properties that deliver a forward-only stream of data rows from a data source. • When a DataReader is used, parts of the ADO.NET model are cut out, providing faster and more efficient data access.

10. EXAMPLE for DataReader try{ conn.Open(); SqlCommand cmd = new SqlCommand(“select * from titles”, conn); SqlDataReader reader = cmd.ExecuteReader(); While (reader.Read()) Console.WriteLine (reader[“title”]); } ****************** try {conn.Open(); SqlCommand cmd = new SqlCommand(“select * from titles”, conn); SqlDataReader reader = cmd.ExecuteReader(); for (int i=0; I <reader.FieldCount; i++) Console.WriteLine (reader.GetName[i])); } reader.Close();

11. DataAdapter Object • Provides a set of methods and properties to retrieve and save data between a DataSet and its source data store. • Allows the use of stored procedures. • Connects to the database to fill the DataSet and also update the database. • Types of DataAdapters --SQLDataAdapter. --ORACLEDataAdapter --OleDbDataAdapter.

12. Example for DataAdapter using System.Data; using System.Data.SqlClient; using System.Windows.Forms; namespace WindowsFormsApplication9 { public partial class Form1 : Form { public Form1() { InitializeComponent(); FillData(); } void FillData() { using (SqlConnection c = new SqlConnection( Properties.Settings.Default.DataConnectionString)) { c.Open(); using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM EmployeeIDs", c)) { DataTable t = new DataTable(); a.Fill(t); } } } } }

13. DataSet Object • Replaces the ADO Recordset. • Represents a cache of data that contains tables, columns, relationships, and constraints, just like a database. • Regardless of where the source data comes from, data can all be placed into DataSet objects. • Tracks changes that are made to the data it holds before updating the source data. • DataSet are also fully XML-featured. • Works with all current models of data storage: flat, relational, and hierarchical

14. Example for DataSet SqlDataAdapter ad = new SqlDataAdapter(“select * from Categories",myConnection); DataSetds = new DataSet(); ad.Fill(ds,"Categories"); DataGrid1.DataSource = ds; DataGrid1.DataBind();

15. DataView Object • Provides methods and properties that enable UI objects such as a DataGrid to bind to a DataSet. • A view of the data contained in the DataSet. • Only used in conjunction with a DataSet

16. Example for DataAdapter string connectionString = "..Nortwind Connection String.."; DataTable customers = new DataTable("Customers"); using (SqlConnection connection =new SqlConnection(connectionString)) { SqlCommand selectAllCustomers = connection.CreateCommand(); selectAllCustomers.CommandText = "SELECT * FROM [Customers]"; connection.Open(); customers.Load(selectAllCustomers.ExecuteReader(CommandBehavior.CloseConnection)); } DataViewdv = new DataView(customers,"Region = 'SP' andCountry = 'Brazil'", "ContactName",DataViewRowState.CurrentRows); dataGridView1.DataSource = dv;

17. DataTable Object • It holds a table data from a data source; Data tables contains two important properties: • COLUMNS • ROWS -- DataRow Objects: Datarow objects correspond to a particular row in a table. We use “Item” property to get or set a value in a particular field in row. -- DataColumn Objects: DataColumn objects represent the columns in the table. Each DataColumn has a data type which specify which type of data column contains.

18. Example for DataTable DataSetdset; DataTabledtbl; DataRowdrow; drow=dtbl.NewRow(); drow["LastName"]="Altindag"; drow[1]="Altindag"; dtbl.Rows.Add(drow); dtbl.Rows.Add(new object[] {1, "Altindag"});

19. Constraint Object • Dataset support constraint objects to check data integrity Constraints are of two types: Unique Constraint and foreign constraint. -- Unique Constraint check that new record entered is unique throughout table -- Foreign constraint specify that if one table is updated, how the related records in another table would be affected. --Not NULL constraint: it is used to check whether the column with this constraint is filled or not.

20. Example for Adding Constraint to DataTable using System;using System.Data;class Program    {static void Main(string[] args)        {DataTable dt1 = new DataTable("Table-1");DataColumnpkCol = dt1.Columns.Add("Id", typeof(int));            dt1.Columns.Add("Field1", typeof(string)).MaxLength = 50;            dt1.PrimaryKey = new DataColumn[] { pkCol };DataTable dt2 = new DataTable("Table-2");            dt2.Columns.Add("Id", typeof(int));            dt2.Columns.Add("Field1", typeof(string)).MaxLength = 50;UniqueConstraintuc = new UniqueConstraint("PrimaryKey-2", dt2.Columns["Id"], true);dt2.Constraints.Add(uc);DataTable dt3 = new DataTable("Table-3");            dt3.Columns.Add("Id1", typeof(int));            dt3.Columns.Add("Id2", typeof(int));            dt3.Columns.Add("Field1", typeof(string)).MaxLength = 50;dt3.Constraints.Add("PrimaryKey-3",new DataColumn[] { dt3.Columns["Id1"], dt3.Columns["Id2"] },true);        }    }

21. DataRelation Object • Data Relation objects specify a relationship between parent and child tables, based on a key that both tables share. • You can then use the DataRelation object to get related records.

22. Example for DataRelation private void Page_Load(object sender, System.EventArgs e){            DataSetds = GetDataSet();ds.EnforceConstraints = false;DataRelation dl = new DataRelation("CHILDREN",ds.Tables[0].Columns["id"], ds.Tables[1].Columns["parent"], false);dl.Nested = true;ds.Relations.Add(dl);ComputeHierarchy(ds.Tables[0].Select("[parent] is null"), 0); } ……........ ……......... ……………

23. ADO.NET using Stored Procedures

24. Stored Procedure • A stored procedure is a pre-defined, reusable routine that is stored in a database.  • SQL Server compiles stored procedures, which makes them more efficient to use.  • Therefore, rather than dynamically building queries in the code, we can take advantage of the reuse and performance benefits of stored procedures. 

25. Executing a Stored Procedure • In addition to commands built with strings, the SqlCommand type can be used to execute stored procedures.  • There are two tasks require to make this happen: 1. let the SqlCommand object know which stored procedure to execute. // create a command object identifying the stored procedure SqlCommand cmd = new SqlCommand( “xxxxx”, conn); While declaring the SqlCommand object above, the first parameter “xxxxx” is the name of a stored procedure in the database.  The second parameter is the connection object, which is used for executing query strings.

26. Cont… 2. tell the SqlCommand object that it is executing a stored procedure. set the command object so that it knows to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; The second command tells the SqlCommand object what type of command it will execute by setting its Command Type property to the Stored Procedure.

27. Sending Parameters to Stored Procedures Using parameters for stored procedures is the same as using parameters for query string commands.  • create a command object identifying the stored procedure SqlCommand cmd = new SqlCommand( "CustOrderHist", conn); • set the command object so it knows to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; • add parameter to command, which will be passed to the stored procedure cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

28. Cont…. • The SqlCommand constructor above specifies the name of a stored procedure.  • This particular stored procedure takes a single parameter, named “@CustomerID”.  Therefore, we must populate this parameter using a SqlParameter object.  • The name of the parameter passed as the first parameter to the SqlParameter constructor must be spelled exactly the same as the stored procedure parameter.  Then execute the command the same as you would with any other SqlCommand object.

29. Calling Stored procedures in ADO.NET with an example • Initially create a object of SqlConnection class which is available in System.Data.SqlClient namespace. • We have to provide the connection string as a parameter which includes the Data Source name, the database name and the authentication credentials. • Open the connection using the Open() method. • SqlConnection con = new SqlConnection("Data Source= ; initial catalog= Northwind ; User Id= ; Password= '"); con.open();

30. Cont… • Create the following stored procedure on the Region table in the Northwind database which accepts two parameters and does not have any output parameters. CREATE PROCEDURE RegionUpdate (@RegionID INTEGER, @RegionDescription NCHAR(50)) AS SET NOCOUNT OFF UPDATE Region SET RegionDescription = @RegionDescription

31. Cont… • Create a SqlCommand object with the parameters as the name of the stored procedure that is to be executed and the connection object “con” to which the command is to be sent for execution. SqlCommand command = new SqlCommand("RegionUpdate",con); • Change the command objects CommandType property to stored procedure. command.CommandType = CommandType.StoredProcedure; • Add the parameters to the command object using the Parameters collection and the SqlParameter class. command.Parameters.Add(new SqlParameter ("@RegionID",SqlDbType.Int,0,"RegionID"));

32. Cont… command.Parameters.Add(new SqlParameter ("@RegionDescription",SqlDbType.NChar,50,"RegionDescription")); • Specify the values of the parameters using the Value property of the parameters. command.Parameters[0].Value=4; command.Parameters[1].Value="SouthEast"; • Excecute the stored procedure using the ExecuteNonQuery method which returns the number of rows effected by the stored procedure. inti=command.ExecuteNonQuery();

33. Cont… • Call the stored procedure and access the RegionDescription for the RegionID 4 using the value property of the parameter. string newRegionDescription =(string) command.Parameters["@RegionDescription"].Value; • Close the sql connection. con.Close();

34. Refereces • http://www.startvbdotnet.com/ado/default.aspx • http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx. • http://www.codeproject.com/KB/cs/simplecodeasp.aspx. • http://www.developer.com/db/article.php/3438221/Calling-Stored-Procedures-with-ADONET.htm

35. Thank you

36. QUESTIONS ???