ADO NET and Stored Procedures VENKATA CS 795

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

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.

What is an ADO. NET � ADO stands for “Active. X 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.

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

Connection object � Connects to databases. � Provider-specific classes o Sql. Connection o Oracle. Connection o Ole. Db. Connection. � Connections can be opened in two ways: o Explicitly by calling the “Open” method on the connection o Implicitly when using a Data. Adapter. � Connections handle transactions

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

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: o Execute. Non. Query: Executes commands that have no return values such as INSERT, UPDATE or DELETE o Execute. Scalar: Returns a single value from a database query o Execute. Reader: Returns a result set by way of a Data. Reader object � Provider-specific classes o Sql. Command o Ole. Db. Command � Results are returned in the form of streams. Accessed by: o Data. Reader object o Data. Set object via a Data. Adapter.

Example for Command Object Execute. Reader (for read) Execute. Non. Query (for updates) Sql. Connection conn = new Sql. Connection (“server=localhost; database=pubs; uid=mukka; pwd=“); try { conn. Open(); Sql. Command cmd = new Sql. Command (); cmd. Command. Text= “delete from title where title_id = “xxxx”; cmd. Connection = conn; cmd. Execute. Non. Query (); } catch (Sql. Exception ex) { …. } finally { conn. Close(); }

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

EXAMPLE for Data. Reader try{ conn. Open(); Sql. Command cmd = new Sql. Command(“select * from titles”, conn); Sql. Data. Reader reader = cmd. Execute. Reader(); While (reader. Read()) Console. Write. Line (reader[“title”]); } ********* try {conn. Open(); Sql. Command cmd = new Sql. Command(“select * from titles”, conn); Sql. Data. Reader reader = cmd. Execute. Reader(); for (int i=0; I <reader. Field. Count; i++) Console. Write. Line (reader. Get. Name[i])); } reader. Close();

Data. Adapter Object � Provides a set of methods and properties to retrieve and save data between a Data. Set and its source data store. � Allows the use of stored procedures. � Connects to the database to fill the Data. Set and also update the database. � Types of Data. Adapters --SQLData. Adapter. --ORACLEData. Adapter --Ole. Db. Data. Adapter.

Example for Data. Adapter using System. Data; using System. Data. Sql. Client; using System. Windows. Forms; namespace Windows. Forms. Application 9 { public partial class Form 1 : Form { public Form 1() { Initialize. Component(); Fill. Data(); } void Fill. Data() { using (Sql. Connection c = new Sql. Connection( Properties. Settings. Default. Data. Connection. String)) { c. Open(); using (Sql. Data. Adapter a = new Sql. Data. Adapter("SELECT * FROM Employee. IDs", c)) { Data. Table t = new Data. Table(); a. Fill(t); } } }

Data. Set 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 Data. Set objects. � Tracks changes that are made to the data it holds before updating the source data. � Data. Set are also fully XML-featured. � Works with all current models of data storage: flat, relational, and hierarchical

Example for Data. Set Sql. Data. Adapter ad = new Sql. Data. Adapter(“select * from Categories", my. Connection); Data. Set ds = new Data. Set(); ad. Fill(ds, "Categories"); Data. Grid 1. Data. Source = ds; Data. Grid 1. Data. Bind();

Data. View Object � Provides methods and properties that enable UI objects such as a Data. Grid to bind to a Data. Set. � A view of the data contained in the Data. Set. � Only used in conjunction with a Data. Set

Example for Data. Adapter string connection. String = ". . Nortwind Connection String. . "; Data. Table customers = new Data. Table("Customers"); using (Sql. Connection connection = new Sql. Connection(connection. String)) { Sql. Command select. All. Customers = connection. Create. Command(); select. All. Customers. Command. Text = "SELECT * FROM [Customers]"; connection. Open(); customers. Load(select. All. Customers. Execute. Reader (Command. Behavior. Close. Connection)); } Data. View dv = new Data. View(customers, "Region = 'SP' and Country = 'Brazil'", "Contact. Name", Data. View. Row. State. Current. Rows); data. Grid. View 1. Data. Source = dv;

Data. Table Object � It holds a table data from a data source; Data tables contains two important properties: 1. COLUMNS 2. ROWS -- Data. Row 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. -- Data. Column Objects: Data. Column objects represent the columns in the table. Each Data. Column has a data type which specify which type of data column contains.

Example for Data. Table Data. Set dset; Data. Table dtbl; Data. Row drow; drow=dtbl. New. Row(); drow["Last. Name"]="Altindag"; drow[1]="Altindag"; dtbl. Rows. Add(drow); dtbl. Rows. Add(new object[] {1, "Altindag"});

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.

Example for Adding Constraint to Data. Table using System; using System. Data; class Program { static void Main(string[] args) { Data. Table dt 1 = new Data. Table("Table-1"); Data. Column pk. Col = dt 1. Columns. Add("Id", typeof(int)); dt 1. Columns. Add("Field 1", typeof(string)). Max. Length = 50; dt 1. Primary. Key = new Data. Column[] { pk. Col }; Data. Table dt 2 = new Data. Table("Table-2"); dt 2. Columns. Add("Id", typeof(int)); dt 2. Columns. Add("Field 1", typeof(string)). Max. Length = 50; Unique. Constraint uc = new Unique. Constraint("Primary. Key-2", dt 2. Columns["Id"], true); dt 2. Constraints. Add(uc); Data. Table dt 3 = new Data. Table("Table-3"); dt 3. Columns. Add("Id 1", typeof(int)); dt 3. Columns. Add("Id 2", typeof(int)); dt 3. Columns. Add("Field 1", typeof(string)). Max. Length = 50; dt 3. Constraints. Add("Primary. Key 3", new Data. Column[] { dt 3. Columns["Id 1"], dt 3. Columns["Id 2"] }, true); } }

Data. Relation 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 Data. Relation object to get related records.

Example for Data. Relation private void Page_Load(object sender, System. Event. Args e) { Data. Set ds = Get. Data. Set(); ds. Enforce. Constraints = false; Data. Relation dl = new Data. Relation("CHILDREN", ds. Tables[0]. Columns["id"], ds. Tables[1]. Columns["parent"], false); dl. Nested = true; ds. Relations. Add(dl); Compute. Hierarchy(ds. Tables[0]. Select("[parent] is null"), 0); } ……. . . . ……………

ADO. NET using Stored Procedures

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.

Executing a Stored Procedure � In addition to commands built with strings, the Sql. Command type can be used to execute stored procedures. � There are two tasks require to make this happen: 1. let the Sql. Command object know which stored procedure to execute. // create a command object identifying the stored procedure Sql. Command cmd = new Sql. Command( “xxxxx”, conn); While declaring the Sql. Command 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.

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

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 Sql. Command cmd = new Sql. Command( "Cust. Order. Hist", conn); 2. set the command object so it knows to execute a stored procedure cmd. Command. Type = Command. Type. Stored. Procedure; 3. add parameter to command, which will be passed to the stored procedure cmd. Parameters. Add(new Sql. Parameter("@Customer. ID", cust. Id)); 1.

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

Calling Stored procedures in ADO. NET with an example � Initially create a object of Sql. Connection class which is available in System. Data. Sql. Client 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. � Sql. Connection con = new Sql. Connection("Data Source= ; initial catalog= Northwind ; User Id= ; Password= '"); con. open();

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 Region. Update (@Region. ID INTEGER, @Region. Description NCHAR(50)) AS SET NOCOUNT OFF UPDATE Region SET Region. Description = @Region. Description

Cont… � Create a Sql. Command 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. Sql. Command command = new Sql. Command("Region. Update", con); � Change the command objects Command. Type property to stored procedure. command. Command. Type = Command. Type. Stored. Procedure; � Add the parameters to the command object using the Parameters collection and the Sql. Parameter class. command. Parameters. Add(new Sql. Parameter

Cont… command. Parameters. Add(new Sql. Parameter ("@Region. Description", Sql. Db. Type. NChar, 50, "Region. Descriptio n")); � Specify the values of the parameters using the Value property of the parameters. command. Parameters[0]. Value=4; command. Parameters[1]. Value="South. East"; � Excecute the stored procedure using the Execute. Non. Query method which returns the number of rows effected by the stored procedure. int i=command. Execute. Non. Query();

Cont… � Call the stored procedure and access the Region. Description for the Region. ID 4 using the value property of the parameter. string new. Region. Description =(string) command. Parameters["@Region. Description"]. Value; � Close the sql connection. con. Close();

Refereces � http: //www. startvbdotnet. com/ado/default. aspx � http: //www. csharpstation. com/Tutorials/Ado. Dot. Net/Lesson 07. aspx. � http: //www. codeproject. com/KB/cs/simplecodeasp. aspx. � http: //www. developer. com/db/article. php/3438221/Calling. Stored-Procedures-with-ADONET. htm

Thank you

QUESTIONS ? ? ?