ADO.Net

1. ADO.Net CS795

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

3. System.Data.SqlClient using System.Data.SqlClient … SqlConnection conn = new SqlConnection (“server=(local); Initial Catalog = database=pubs; uid=mukka; pwd=“); Or (“server=(local); Initial Catalog = database=pubs; Trusted_Connection=Yes;“); try { conn.Open(); SqlCommand cmd = new SqlCommand(“select * from titles”, conn); SqlDataReader reader = cmd.ExecuteReader (); while (reader.Read ()) Console.Writeln (reader[“title”]); } Catch (SqlException ex) {Console.WriteLine (ex.message); } finally {conn.Close();} Note: For OLEDB, simply replace Sql in the above code by OleDb

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

5. SqlConnection Class SqlConnection = conn SqlConnection(); Conn.ConnectionString = “server=localhost; database=pubs; uid=mukka; pwd=“; Or SqlConnection conn = “server=localhost; database=pubs; uid=mukka; pwd=“; Other parameters for ConnectionString: http://authors.aspalliance.com/aspxtreme/sys/data/sqlclient/SqlConnectionClassConnectionString.aspx It can also be defined on web.config: • The constructor of the SqlConnection 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="ConnectionString value="server=HAW2L1800\NetSDK;Trusted_Connection=true;database=StoreDOC“/>

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

7. Command Classes ExecuteReader (for read) ExecuteNonQuery (for updates) SqlConnection conn = new SqlConnection (“server=localhost; datbase=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();}

8. ExecuteNonQuery • 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 SqlCommand cmd = new SqlCommand (“insert into titles (title_id, title, type, pubdate)”+ “values (‘CS150’,’C++ Programming’,” + “ ‘computer science’, ‘May 2006’), conn); cmd.ExecuteNonQuery();

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

10. ExecuteScalar (cont.) • To retrieve BLOBs (Binary large objects) from databases • http://builder.com.com/5100-6371-5766889.html • http://support.microsoft.com/default.aspx?scid=kb;en-us;309158 • http://www.codeproject.com/cs/database/images2db.asp • http://www.codeproject.com/useritems/Blobfield.asp FileStream stream new FileStream (“Logo.jpg”, FileMode.Open); byte[] blob new byte [stream.Length]; stream.Read (blob, 0, (int) stream.Length); stream.Close(); SqlConnection con = new … try{ conn.Open(); SqlCommand cmd = new SqlCommand (“insert into pub_info (pub_id, logo) values (‘9937’, @logo)”, conn); cmd.Parameters.Add (“@logo”, blob); cmd.ExecuteNonQuery (); } catch … finally …

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

12. ExecuteReader Method • To perform database queries • Returns a DataReader object: SqlDataReader or OleDataReader 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();

13. Transactions • Ex: transfer funds from one account (say 1234) to another account (say 9876). SqlTransaction trans = null; SqlConnection conn = new SqlConnection (“server=localhost; database=mybank; uid=mukka; pwd=“); try{conn.Open(); trans = conn.BeginTransaction (IsolationLevel.Serializable); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.Transaction=trans; cmd.CommandText = “update accounts set balance = balance-1500 where account_id = ‘1234’ “; cmd.ExecuteNonQuery(); cmd.CommandText = “update accounts set balance = balance+1500 where account_id = ‘9876’ “; cmd.ExecuteNonQuery(); Trans.Commit(); } Alternate to Commit is Rollback.

14. Parameterized Commands • When commands are the same but the parameters are different Try{ conn.Open(); 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); cmd.Parameters[“@amount”].Value = -1500; cmd.Parameters[“@id”].Value = “1234”; cmd.ExecuteNonQuery (); cmd.Parameters[“@amount”].Value = 1500; cmd.Parameters[“@id”].Value = “9867”; cmd.ExecuteNonQuery (); }

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

16. How does an application call the stored procedure? SqlConnection conn = new SqlConnection (“server=localhost; database=mybank; uid=mukka; pwd=“); try{conn.Open(); SqlCommand cmd = new SqlCommand (“proc_TransferFunds”, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add (“@amount”, 1500); cmd.Parameters.Add (“@from”, ‘1234’); cmd.Parameters.Add (“@to”, ‘9876’); cmd.ExecuteNonQuery (); } Catch (SqlException ex) { ….} finally {conn.Close(); }

17. Example where it returns a value CREATE PROCEDURE proc_GetBalance @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 ***************** SqlConnection conn = new SqlConnection (“server=localhost; database=mybank; uid=mukka; pwd=“); try{conn.Open(); SqlCommand cmd = new SqlCommand (“proc_GetBalance”, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add (“@id”, ‘1234’); SqlParameter bal = cmd.Parameters.Add (“@balance”, SqlDbType.Money); bal.Direction = ParameterDirection.Output; SqlParameter ret = cmd.Parameters.Add (“@return”, SqlDbType.Int); ret.Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery (); int retval = (int) ret.value; decimal balance = (decimal) bal.Value; } catch (SqlException ex) { ….} finally {conn.Close(); }

18. DataSet Class

19. 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 DataReader) • Great for caching, especially in web applications.

20. DataSets vs. DataReaders • 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, DataReader 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, DataSet is a good alternate.

21. DataAdapter • DataSets don’t interact with databases directly; • Instead, they interact through DataAdapters • Purpose: To perform database queries and create DataTables containing the query results; also, to write the modified DataTables into databases • Fill and Update

22. DataAdapter.Fill SqlDataAdapter adapter = new SqldataAdapter (“select * from titles”, “server=localhost; database=pubs; uid=mukka; pwd=“); DataSet ds = new dataSet (); adapter.Fill (ds, “Titles”); What does Fill do? • Open a connection to the pubs database using adapter. • Performs a query on the pubs database using the query string passed to adapter. • Creates a DataTable named “Titles” in ds. • Initializes DataTable with a schema that matches that of the “Titles” table in the database. • Retrieves records produced by the query and writes them to the DataTable • Closes the connection to the database

23. DataTable foreach (DataTable table in ds.Tables) Console.WriteLine (table.TableName); DataTable table = ds.Tables[0]; foreach (DataRow row in table.Rows) Console.WriteLine(row[0]); DataTable table = ds.Tables[0]; foreach (DataRow row in table.Rows) Console.WriteLine(row[“account_id”]); DataTable table = ds.Tables[0]; foreach (DataColumn col in table.Columns) Console.WriteLine(“Name={0}, Type ={1}”, col.ColumnName, col.DataType);

24. Insert a record into DataTable SqlDataAdapter adapter = new SqldataAdapter (“select * from titles”, “server=localhost; database=pubs; uid=mukka; pwd=“); DataSet ds = new dataSet (); adapter.Fill (ds. “Titles”); DataTable table ds.Tables[“Titles”]; DataRow row = table.NewRow (); row[“title_id”] = “CS795”; row[“title”] = “.Net Security”; row[“price”]=“70.99”; Table.Rows.Add (row);

25. Propagating Changes back to Database SqlDataAdapter adapter = new SqldataAdapter (“select * from titles”, “server=localhost; database=pubs; uid=mukka; pwd=“); SqlCommandBuilder builder = new SqlCommandBuilder (adapter); DataSet ds = new dataSet (); adapter.Fill (ds. “Titles”); DataTable table ds.Tables[“Titles”]; DataRow row = table.NewRow (); row[“title_id”] = “CS795”; row[“title”] = “.Net Security”; row[“price”]=“70.99”; table.Rows.Add (row); adapter.Update (table); (only writes the ones that were changed)

26. Links • Video 1: https://www.youtube.com/watch?v=aoFDyt8oG0k • Video 2: https://www.youtube.com/watch?v=Mo0ECWKVVDU • Video 3: https://www.youtube.com/watch?annotation_id=annotation_294978&feature=iv&index=3&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&src_vid=Mo0ECWKVVDU&v=pBCPc44CE74 • Video 4: https://www.youtube.com/watch?annotation_id=annotation_953179&feature=iv&index=4&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&src_vid=pBCPc44CE74&v=vDvV3KrRuMA • Video 5: https://www.youtube.com/watch?annotation_id=annotation_907100&feature=iv&index=5&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&src_vid=vDvV3KrRuMA&v=Zv9i1S_dkTI • Video 6: https://www.youtube.com/watch?annotation_id=annotation_30659&feature=iv&index=6&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&src_vid=Zv9i1S_dkTI&v=QKhHkEmv3Kw • Video 7: https://www.youtube.com/watch?annotation_id=annotation_197748&feature=iv&index=7&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&src_vid=QKhHkEmv3Kw&v=stIWeAcO45Y • Video 8: https://www.youtube.com/watch?annotation_id=annotation_38739&feature=iv&index=8&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&src_vid=stIWeAcO45Y&v=EzwBuqILfds • Video 9: https://www.youtube.com/watch?annotation_id=annotation_789082&feature=iv&index=9&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&src_vid=EzwBuqILfds&v=rovj0xmM-0M • Video 10: https://www.youtube.com/watch?annotation_id=annotation_276753&feature=iv&index=10&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&src_vid=rovj0xmM-0M&v=vN-CZYP2ZuY • Video 11: https://www.youtube.com/watch?annotation_id=annotation_730981&feature=iv&index=11&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&src_vid=vN-CZYP2ZuY&v=TsKx5CLisSM

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