1 / 21

ADO .NET

ADO .NET. What shall we cover?. Understand the difference between ADO and ADO .NET How to integrate ADO .NET with .NET applications using Visual Studio .NET How to use ADO .NET’s capabilities Single table select/update Stored procedures - select/update tables Multiple table select/update

hildal
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. What shall we cover? • Understand the difference between ADO and ADO .NET • How to integrate ADO .NET with .NET applications using Visual Studio .NET • How to use ADO .NET’s capabilities • Single table select/update • Stored procedures - select/update tables • Multiple table select/update • How to take advantage of XML support with ADO .NET

  3. What happened to RecordSet? • In ADO • Recordset • In ADO .NET • DataReader • Connected, non-cached, Forward Only / Read Only records • DataSet • Disconnected, cached, scrollable data • DataAdapter • Logic for populating the DataSet and propagating changes back to the data source

  4. Comparison

  5. Presentation Tier Business Tier ADO Recordset ADO Recordset Data Tier OLE DB rowset Today’s Distributed Systems • Presentation Tier • Business Tier • Data Tier

  6. ADO .NET Architecture

  7. ADO .NET DataSet Managed Provider DataAdapter DataReader Connection Command Parameters

  8. Managed Providers • Namespaces -Which to use SqlClient Managed-Provider Namespace OleDb Managed-Provider Namespace

  9. Mappings Mappings Mappings DataAdapter • Loads a table from a data store and writes changes back. • Exposes two methods: • Update () (DataSet, DataTable) • Fill () (DataSet, DataTable) • Allows single DataSet to be populated from multiple different datasources DataSet Fill() Update() SelectCommand InsertCommand UpdateCommand DeleteCommand DataAdapter Data store

  10. DataSet DataSet Tables DataRow DataColumn DataTable Table Columns Column Constraints Constraint Rows Row Relations Relations Relation XML Schema DataSet DataTable Constraints

  11. Connection class • Represents a Connection to the Data Source • On a Connection, you can… • Begin, commit and abort transactions • Equivalent to the ADODB.Connection class in ADO

  12. Connection Example // Create and open an SqlConnection SqlConnection myConnection = new SqlConnection ("server=localhost;uid=sa;pwd=;database=northwind"); myConnection.Open (); // Optionally Start a Transaction SqlTransaction myTransaction = myConnection.BeginTransaction (); // Do some work if (this.TransferFunds (fromAccountNo, toAccountNo, amount)) myTransaction.Commit (); // Succeeded else myTransaction.Rollback (); // Failure myConnection.Close (); // Always Close Connection

  13. Command class • Represents a Command to be executed • With a Command you can: • Define an SQL statement to be executed on the database server • Set parameter information for stored procedure • Retrieve return values after command execution • Corresponds to ADODB.Command class in ADO

  14. Executing Commands • Methods • ExecuteNonQuery () • ExecuteReader () • ExecuteScalar () • ExecuteXmlReader ()

  15. Command Example // Command using stored procedure string connectionString = "server=(local);uid=user;pwd=password;database=Northwind"; SqlConnection myConnection = new SqlConnection (connectionString); // Create instance of Connection and Command classes SqlCommand myCommand = new SqlCommand ("usp_getOrdersList", myConnection); // Mark the Command object as for a Stored Procedure (SP) myCommand.CommandType = CommandType.StoredProcedure; // Add parameters to the Stored Procedure Command object SqlParameter custIDparameter = new SqlParameter ("@CustomerID", SqlDbType.Int, 4); custIDparameter.Value = Int32.Parse (customerID); myCommand.Parameters.Add (custIDparameter); // Execute the command and get results as a Data Reader myConnection.Open () SqlDataReader resultDataReader = myCommand.ExecuteReader (CommandBehavior.CloseConnection);

  16. Data Reader • Alternative to DataAdapter / DataSet • Connected • Read only • Forward only

  17. DataReader Example string connectionString = "server=(local);uid=sa;pwd=;database=Northwind"; string sqlStatement = "select name,companyname from cust"; SqlConnection myConnection = new SqlConnection (connectionString); myConnection.Open() SqlCommand sqlCommandObject = new SqlCommand (sqlStatement, myConnection); SqlDataReader resultsDataReader = objComman.ExecuteReader (); While (objReader.Read) { // Do the necessary operations here. } resultsDataReader.Close ();

  18. DataSet • In Memory Store For Client Data • Relational View of Data • Tables, Columns, Rows, Constraints, Relations • Directly create metadata and insert data • directly inserting data using DataTable • Explicit Disconnected Model • Disconnected, remotable object • No knowledge of data source or properties • Array-like indexing • Strong Typing

  19. Fill DataSet with DataAdapter // Fill dataset with data adapter string connectionString = "server=(local);uid=user;pwd=; database=Northwind"; SqlConnectoin myConnection = new SqlConnection (connectionString); string sqlStatement = "select Name, CompanyName from cust"; SqlDataAdapter resultsDataAdapter = new SqldataAdapter (sqlStatement, myConnection); DataSet resultsDataSet = new DataSet (); resultsDataAdapter.Fill (resultsDataSet, "Customers");

More Related