1 / 27

ADO.Net

ADO.Net. CS795. 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

lsholar
Download Presentation

ADO.Net

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related