1 / 22

ADO.NET

ADO.NET. Road Map. The Problems. HTTP is disconnected So many database vendors Create a simple consistent versatile interface on the data Look at ADO.NET classes OleDb SQL server optimised. ADO.NET Classes. System.Data.OleDb.OleDbConnection

langer
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

  2. Road Map

  3. The Problems • HTTP is disconnected • So many database vendors • Create a simple consistent versatile interface on the data • Look at ADO.NET classes • OleDb • SQL server optimised

  4. ADO.NET Classes System.Data.OleDb.OleDbConnection The connection object is used to create a connection between our code and the database. System.Data.OleDb.OleDbCommand Used to store a command to be applied to the database. May be either raw SQL or a stored procedure. System.Data.OleDb.OleDbParameter Used to model parameters passed to the stored procedures / queries. System.Data.OleDb.OleDbDataAdapter Used to fill a data table with the results of a command.

  5. ADO.NET Classes Continued System.Data.DataTable Used to model data obtained from the database. Data Readers We are also going to take a look at data readers even though they are not used on the module.

  6. Connected v Disconnected Data • The need for locking… • User A opens a record • User B opens the same record and makes changes • User B saves the changes on the record • User A makes their changes to the record • User A now saves their changes and overwrites the changes made by User B • Relatively easy to lock records on a local area network

  7. Disconnected Data • When a client application access a record it communicates with the server via the HTTP request. • The server locates and processes the data returning it to the client at which point the connection is lost. • User A opens a record on the browser, the server locks the record to stop User B accessing it • User B tries to access the record and is presented with a message stating that the record is locked • User A is in the middle of editing the record and his browser crashes thus not telling the server he is done with the record • User B sits there waiting and waiting for the record to come free!

  8. Add a Time – Out? • User A opens the record and goes and makes a cup of tea • The lock times out • User B opens the record • What does User A do with their data when the save the changes? • I am not planning on exploring solutions to these problems, simply to bring your attention to them.

  9. OleDb v SQL Optimised Classes • OleDB is a technology devised by Microsoft for connecting to a wide range of database management systems (DBMS), e.g. Access, Oracle MySQL. • SQL optimised classes perform in exactly the same way as the OleDB classes but fine tuned to work with SQL server.

  10. Usage OleDb OleDbConnectionconnectionToDB = new OleDbConnection (); SQL SqlConnectionconnectionToDB = new SqlConnection(); The classes also have different namespace... using System.Data.SqlClient; and using System.Data.OleDb;

  11. The Execute Function

  12. The Connection Object • connectionToDB = new OleDbConnection(connectionString);

  13. DSN / DSN(less) Connections • DSN = Data Source Name • The database may be file on the disk (the way that we have been connecting to the database in this module!) (DSN(less)) • Or the database may be running on a server with an IP address on a specific port number (remember TCP/IP allows a program on one computer to talk to another!) (DSN)

  14. Data Providers • DBMS specific driver

  15. Connection Strings • Contains DBMS specific configuration data • Specifies the data provider //open the database connectionToDB.Open();

  16. The Command Object • Applies a “Command” to the data • Initialise with stored procedure name and connection //initialise the command builder for this connection OleDbCommanddataCommand = new OleDbCommand(SProcName, connectionToDB); • Add parameters //loop through each parameter for (int Counter = 0; Counter < SQLParams.Count; Counter += 1) { //add it to the command object dataCommand.Parameters.Add(SQLParams[Counter]); } • Set the Command Type //set the command type as stored procedure dataCommand.CommandType = CommandType.StoredProcedure;

  17. So far…

  18. The Mincing Machine

  19. Data Adapter • Initialisation //set the select command property for the data adapter dataChannel.SelectCommand = dataCommand; • Filling the Data Table //fill the data adapter dataChannel.Fill(queryResults);

  20. Data Tables Select * from tblAddress • To reference “Nottingham” in our code we would do the following... AnAddress.Town = queryResults.Rows[3]["Town"].ToString();

  21. Data Readers • Read-only, forward-only stream of data • Faster and more light weight than data tables • Lock the data so may have problems with multiple connections • One way flow of data

  22. Example

More Related