ADO Net CS 795 What is ADO Net

ADO. Net CS 795

What is ADO. Net? • Database language spoken by managed applications • ADO. net database accesses go through modules: data providers – SQL Server. Net provider---to interface with MS SQL databases without any help from unmanaged providers – OLE DB. Net provider---to interface with other databases through unmanaged OLE DB providers • OLE DB providers provide a uniform API over a variety of databases

System. Data. Sql. Client using System. Data. Sql. Client … Sql. Connection conn = new Sql. Connection (“server=(local); Initial Catalog = database=pubs; uid=mukka; pwd=“); Or (“server=(local); Initial Catalog = database=pubs; Trusted_Connection=Yes; “); try { conn. Open(); Sql. Command cmd = new Sql. Command(“select * from titles”, conn); Sql. Data. Reader reader = cmd. Execute. Reader (); while (reader. Read ()) Console. Writeln (reader[“title”]); } Catch (Sql. Exception ex) {Console. Write. Line (ex. message); } finally {conn. Close(); } Note: For OLEDB, simply replace Sql in the above code by Ole. Db

Connections, Commands, Data. Readers The canonical usage pattern for executing database commands in ADO. Net: 1. Create a connection object encapsulating a connection string 2. Open the connection by calling Open on the connection object 3. Create a command object encapsulating both an SQL command the connection that the command will use 4. Call a method on the command object to execute the command 5. Close the connection by calling Close on the connection object

Sql. Connection Class Sql. Connection = conn Sql. Connection(); Connection. String = “server=localhost; database=pubs; uid=mukka; pwd=“; Or Sql. Connection conn = “server=localhost; database=pubs; uid=mukka; pwd=“; Other parameters for Connection. String: http: //authors. aspalliance. com/aspxtreme/sys/data/sqlclient/Sql. Connection. Class. Connection. String. asp x It can also be defined on web. config: • The constructor of the Sql. Connection object expects one parameter: the connection string. The connection string identifies the SQL server name, the SQL database name, and satisfies the authorization requirement by providing a user name and a password, or by specifying Trusted_Connection=true. You can specify the connection string in line within the constructor line, or you can specify it in Web. config: <add key="Connection. String value="server=HAW 2 L 1800Net. SDK; Trusted_Connection=true; database=Store. DOC“ />

Open and Closing Connections • You must first open a connection and close it at the end. Sql. Connection conn = new Sql. Connection (“server=localhost; database=pubs; uid=mukka; pwd=“); try{ conn. Open(); …. } catch (Sql. Connection ex){ …. . } finally { conn. Close(); }

Command Classes Execute. Reader (for read) Execute. Non. Query (for updates) Sql. Connection conn = new Sql. Connection (“server=localhost; datbase=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(); }

Execute. Non. Query • To execute operations where database is changed • Example: insert, update, delete, create database, create table, etc. • Insert, update, delete: Returns number of rows affected by the operation • Returns -1 for others Sql. Command cmd = new Sql. Command (“insert into titles (title_id, title, type, pubdate)”+ “values (‘CS 150’, ’C++ Programming’, ” + “ ‘computer science’, ‘May 2006’), conn); cmd. Execute. Non. Query();

Execute. Scalar • Returns the 1 st row of the 1 st column in the result • Used for commands such as: count, avg, min, max, sum try{ conn. Open(); Sql. Command cmd = new Sql. Command (“select max (advance) from title”, conn); decimal amount = (decimal) cmd. Execute. Scalar (); Console. Write. Line (“Execute. Scalar returned (0: c)”, amount); } Catch (Sql. Exception ex} {Console. Writeln (ex. Message); } finally {conn. Close(); }

Execute. Scalar (cont. ) • To retrieve BLOBs (Binary large objects) from databases • http: //builder. com/5100 -6371 -5766889. html • http: //support. microsoft. com/default. aspx? scid=kb; en-us; 309158 • http: //www. codeproject. com/cs/database/images 2 db. asp • http: //www. codeproject. com/useritems/Blobfield. asp File. Stream stream new File. Stream (“Logo. jpg”, File. Mode. Open); byte[] blob new byte [stream. Length]; stream. Read (blob, 0, (int) stream. Length); stream. Close(); Sql. Connection con = new … try{ conn. Open(); Sql. Command cmd = new Sql. Command (“insert into pub_info (pub_id, logo) values (‘ 9937’, @logo)”, conn); cmd. Parameters. Add (“@logo”, blob); cmd. Execute. Non. Query (); } catch … finally …

Execute. Scalar (Cont. ) • To validate a user name and password (page 506 -507, Jeff Prosie book) Try { conn. Open(); String. Builder builder = new String. Builder (); builder. Append (“select count (*) from users where username = …. int count = (int) command. Execute. Scalar (); return (count > 0); }

Execute. Reader Method • To perform database queries • Returns a Data. Reader object: Sql. Data. Reader or Ole. 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();

Transactions • Ex: transfer funds from one account (say 1234) to another account (say 9876). Sql. Transaction trans = null; Sql. Connection conn = new Sql. Connection (“server=localhost; database=mybank; uid=mukka; pwd=“); try{conn. Open(); trans = conn. Begin. Transaction (Isolation. Level. Serializable); Sql. Command cmd = new Sql. Command(); cmd. Connection = conn; cmd. Transaction=trans; cmd. Command. Text = “update accounts set balance = balance-1500 where account_id = ‘ 1234’ “; cmd. Execute. Non. Query(); cmd. Command. Text = “update accounts set balance = balance+1500 where account_id = ‘ 9876’ “; cmd. Execute. Non. Query(); Trans. Commit(); } Alternate to Commit is Rollback.

Parameterized Commands • When commands are the same but the parameters are different Try{ conn. Open(); Sql. Command cmd = new Sql. Command(“update accounts set balance = balance”+ “+@amount where account_id = @id”, conn); cmd. Parameters. Add (“@amount”, Sql. Db. Type. Money); cmd. Parameters. Add (“@id”, Sql. Db. Type. Char); cmd. Parameters[“@amount”]. Value = -1500; cmd. Parameters[“@id”]. Value = “ 1234”; cmd. Execute. Non. Query (); cmd. Parameters[“@amount”]. Value = 1500; cmd. Parameters[“@id”]. Value = “ 9867”; cmd. Execute. Non. Query (); }

Stored Procedures • User defined command added to a database • Execute faster because they are already in compiled form. CREATE PROCEDURE proc_Transfer. Funds @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

How does an application call the stored procedure? Sql. Connection conn = new Sql. Connection (“server=localhost; database=mybank; uid=mukka; pwd=“); try{conn. Open(); Sql. Command cmd = new Sql. Command (“proc_Transfer. Funds”, conn); cmd. Command. Type = Command. Type. Stored. Procedure; cmd. Parameters. Add (“@amount”, 1500); cmd. Parameters. Add (“@from”, ‘ 1234’); cmd. Parameters. Add (“@to”, ‘ 9876’); cmd. Execute. Non. Query (); } Catch (Sql. Exception ex) { …. } finally {conn. Close(); }

Example where it returns a value CREATE PROCEDURE proc_Get. Balance @ID char(10), @Balance money OUTPUT AS SELECT @Balance =Balance From Accounts WHERE Account_ID = @ID IF @@ROWCOUNT = 1 RETURN 0 ELSE BEGIN SET @Balance = 0 RETURN -1 END GO ********* Sql. Connection conn = new Sql. Connection (“server=localhost; database=mybank; uid=mukka; pwd=“); try{conn. Open(); Sql. Command cmd = new Sql. Command (“proc_Get. Balance”, conn); cmd. Command. Type = Command. Type. Stored. Procedure; cmd. Parameters. Add (“@id”, ‘ 1234’); Sql. Parameter bal = cmd. Parameters. Add (“@balance”, Sql. Db. Type. Money); bal. Direction = Parameter. Direction. Output; Sql. Parameter ret = cmd. Parameters. Add (“@return”, Sql. Db. Type. Int); ret. Direction = Parameter. Direction. Return. Value; cmd. Execute. Non. Query (); int retval = (int) ret. value; decimal balance = (decimal) bal. Value; } catch (Sql. Exception ex) { …. } finally {conn. Close(); }

Data. Set Class Dataset Database Data. Table Data. Row Records in a Data. Table Data. Column Fields in a Data. Table Data. Set. Tables Data. Tabe. Rows Data. Table. Columns Uniuqe. Constraint Add a contsraint to a column Data. Relationship between two tables

Dataset (cont. ) • Datasets are ideal for retrieving results from database queries and storing them in memory. • In addition, this data may be modified and propagated back to the database. • It can also support, random access to the data (unlike Data. Reader) • Great for caching, especially in web applications.

Data. Sets vs. Data. Readers • If the application simply queries a database and reads through the records one at a time until it finds the record it is looking for, Data. Reader is the right tool • If the application requires all results from a query, say to display in a table, and have ability to iterate back and forth through the result set, Data. Set is a good alternate.

Data. Adapter • Data. Sets don’t interact with databases directly; • Instead, they interact through Data. Adapters • Purpose: To perform database queries and create Data. Tables containing the query results; also, to write the modified Data. Tables into databases • Fill and Update

Data. Adapter. Fill Sql. Data. Adapter adapter = new Sqldata. Adapter (“select * from titles”, “server=localhost; database=pubs; uid=mukka; pwd=“); Data. Set ds = new data. Set (); adapter. Fill (ds, “Titles”); What does Fill do? (1) Open a connection to the pubs database using adapter. (2) Performs a query on the pubs database using the query string passed to adapter. (3) Creates a Data. Table named “Titles” in ds. (4) Initializes Data. Table with a schema that matches that of the “Titles” table in the database. (5) Retrieves records produced by the query and writes them to the Data. Table (6) Closes the connection to the database

Data. Table foreach (Data. Table table in ds. Tables) Console. Write. Line (table. Table. Name); Data. Table table = ds. Tables[0]; foreach (Data. Row row in table. Rows) Console. Write. Line(row[0]); Data. Table table = ds. Tables[0]; foreach (Data. Row row in table. Rows) Console. Write. Line(row[“account_id”]); Data. Table table = ds. Tables[0]; foreach (Data. Column col in table. Columns) Console. Write. Line(“Name={0}, Type ={1}”, col. Column. Name, col. Data. Type);

Insert a record into Data. Table Sql. Data. Adapter adapter = new Sqldata. Adapter (“select * from titles”, “server=localhost; database=pubs; uid=mukka; pwd=“); Data. Set ds = new data. Set (); adapter. Fill (ds. “Titles”); Data. Table table ds. Tables[“Titles”]; Data. Row row = table. New. Row (); row[“title_id”] = “CS 795”; row[“title”] = “. Net Security”; row[“price”]=“ 70. 99”; Table. Rows. Add (row);

Propagating Changes back to Database Sql. Data. Adapter adapter = new Sqldata. Adapter (“select * from titles”, “server=localhost; database=pubs; uid=mukka; pwd=“); Sql. Command. Builder builder = new Sql. Command. Builder (adapter); Data. Set ds = new data. Set (); adapter. Fill (ds. “Titles”); Data. Table table ds. Tables[“Titles”]; Data. Row row = table. New. Row (); row[“title_id”] = “CS 795”; row[“title”] = “. Net Security”; row[“price”]=“ 70. 99”; table. Rows. Add (row); adapter. Update (table); (only writes the ones that were changed)

Links • The C# Station ADO. NET Tutorial • Using ADO. NET for beginners • In Depth ASP. NET using ADO. NET

Links • The C# Station ADO. NET Tutorial • Using ADO. NET for beginners • In Depth ASP. NET using ADO. NET