1 / 35

Dr. Rathindra Sarathy

MSIS 5133 Advanced MIS - E-Commerce Spring 2003 Lecture 4: DotNet Technologies - Part 3 Working with Databases. Dr. Rathindra Sarathy. General Steps for Using Web Databases. Build your database tables and queries Create a connection to the database

erich-shepard
Télécharger la présentation

Dr. Rathindra Sarathy

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. MSIS 5133 Advanced MIS - E-CommerceSpring 2003Lecture 4: DotNet Technologies - Part 3Working with Databases Dr. Rathindra Sarathy

  2. General Steps for UsingWeb Databases • Build your database tables and queries • Create a connection to the database • The connection identifies the location of the database (the data source) and the connection method (an ODBC driver, OLE-DB provider, or an OLE-DB.NET data provider), along with any other settings such as username or password • Create an ASP.NET Web page • Add an ADO.NET connection object that connects to the database, executes commands, and returns data from the database • Create code that will interact with the data, display the data in an ASP.NET control, perform calculations on the data, or upload changes to the database

  3. Application Framework - ASP.NET, ADO.NET and Databases http://www.microsoft.com/singapore/downloads/devfest/Day2_HighPerf.DBApps.UsingADO.net_LeeWeiMeng.ppt

  4. Overview of the ADO.NET Framework • Universal Data Access Model (UDA) provides a method whereby data can be shared across different applications and platforms • The ODBC drivers and OLE-DB providers provide the low-level interface to the database • The ActiveX Data Object Model (ADO) provides objects that allow you to interface with the database • The ADO.NET model separates the process of connecting to the data source from the manipulation of the data • Allows us the ability to write code that can transfer between applications • Two new sets of data providers are known as Managed Providers or .NET providers • The ASP.NET application interacts with ADO.NET objects built into the ADO.NET Model • SQL Server .NET data provider is used to connect to a SQL Server database • OLE-DB .NET data provider is used to connect to any other data source that can be accessed via the OLE-DB interface

  5. .NET Managed Data Providers

  6. ADO.NET Object Model • Interact with the database using its own proprietary interface • Use a standard to translate commands between your application and the database to provide the low-level interface to the database • ODBC drivers are used to provide access to an ODBC compliant database • OLE-DBproviders are available for most common data stores, including Access, SQL Server, and Exchange Server • SQL Server .NET data provider uses a native communication method to communicate with SQL Server so there is a performance improvement • OLE-DB - ODBC providers - an OLE-DB provider that will interface with the ODBC driver in order to support legacy database applications

  7. Managed Data Providers for ADO.NET • What is a Managed Provider? • Connects, fills, and persists the DataSet to and from data stores • Has detailed and specific information about a data source • “Managed” means it is controlled by the .NET Common Language Runtime (CLR) • SQL Server.NET Data provider (System.Data.SQLClient) – for SQL Server 7 or later. • OLE DB.NET Data Provider (System.Data.OLEDB) – for other databases (including SQL Server 6.5 or earlier, Jet, Oracle etc.) http://www.able-consulting.com/dotnet/ado/adonet101_files/frame.htm http://www.microsoft.com/singapore/downloads/devfest/Day2_HighPerf.DBApps.UsingADO.net_LeeWeiMeng.ppt

  8. Data Related Namespaces • The ADO.NET data objects are stored within the Data base class • You can access these objects via the Data namespaces. These namespaces must be imported into your Web application

  9. Architecture of ADO.NET http://www.microsoft.com/singapore/downloads/devfest/Day2_HighPerf.DBApps.UsingADO.net_LeeWeiMeng.ppt

  10. A quick look at the various classes in ADO.NET • Connection – opens a connection to a database • Command – executes commands (stored procedures/T-SQL statements) against a data source • DataAdapter – provides a link between a data source and a dataset • DataReader – provides a quick access to data (connected) • DataSet – represents a disconnected set of data from a data source • SQL Server .NET data provider • SqlConnection, SqlCommand, SqlDataReader, and SqlDataAdaptor • OLE-DB data provider • OleDbConnection, OleDbCommand, OleDbDataReader, and OleDbDataAdaptor. • DataTable – represents a table in memory • DataRelation – establish a relationship between two DataTable objects http://www.microsoft.com/singapore/downloads/devfest/Day2_HighPerf.DBApps.UsingADO.net_LeeWeiMeng.ppt

  11. ADO.NET Connections and Commands • Connection used to talk to a Database • Commands travel over connections • Result sets are returned in the form of Streams • Streams are read by DataReader or pushed into DataSet object via DataAdapter • Creating a Connection –Connection Strings • Open method • Opened implicitly when using DataAdapter • Provides the connection to the database • Requires a connection string that can be manually configured, or created in the Data Connections window of Visual Studio .NET http://www.able-consulting.com/dotnet/ado/adonet101_files/frame.htm

  12. Data Connection Properties • ConnectString property contains all of the values of the data connection properties in a single string • Database property is the name of the database and the Server property is the name of the server • Type property is the name of the database application • Driver is the name of the ODBC driver, OLE-DB provider, or .NET managed data provider • State property indicates if the database is currently connected • User property identifies the name of the user account that created the database. The creator of the database is known as the database owner, or dbo

  13. Creating Connections • Connection string • Provider – name of the managed provider. The name of the SQL Server OLE-DB Provider is Provider=SQLOLEDB.1 • Data Source - name of the server • User ID and Password - identifies the authentication • Initial Catalog - name of the database • Open up a connection to the Northwind SQL Server database Dim oSqlConnection As SqlConnectionoSqlConnection = New SqlConnection( _ "Data Source=(local);” & _ "Initial Catalog=Northwind;“ & _ “Trusted_Connection=Yes;” ) oSqlConnection.Open() Console.WriteLine(oSqlConnection.ConnectionString) oSqlConnection.Close() http://www.able-consulting.com/dotnet/ado/adonet101_files/frame.htm

  14. Command Object Properties • Contains the information that is submitted to the DB • Call Stored Procedures, or run SQL Commands • Identify an SQL command or a stored procedure • Connection - the Connection used by the Command object • CommandType - to indicate the type of command being executed • Text - by default, indicates that the command is an SQL text string • SQL - the Structured Query Language - to identify what records to add, delete, or modify within the database • StoredProcedure - specifies the name of a stored procedure • Stored procedures - SQL commands that are stored within the database

  15. Command Object Properties and Methods • CommandText – identifies the command to execute • TableDirect - specifies the name of a table to return • The Command object also exposes a Parameters collection that can be used to apply parameters which are passed to stored procedures • Execute method - executes the command and passes the results to the DataReader object • ExecuteNonQuery method - does not return rows because it’s used to insert, modify, and delete data, but returns an integer of the number of rows affected by the command • Used to perform SQL commands such as CREATE TABLE, INSERT INTO, etc • ExecuteReader = Runs a result set Query (Select)

  16. Executing Non-Select Commands via Command Object • Perform an “action” query (no result set returned) Dim oSqlCommand As SqlCommand sCmdString = "UPDATE Customers " & _ "SET ContactName = 'Carl Prothman' “ & _ "WHERE CustomerID = 'VINET'“ oSqlCommand = New SqlCommand(sCmdString, oSqlConn) oSqlCommand.ExecuteNonQuery() http://www.able-consulting.com/dotnet/ado/adonet101_files/frame.htm

  17. The ADO.NET DataReader Object • To deliver a stream of data from the database • Provides a high-performance method of accessing read-only data • Read-only, forward-only stream of data from the database • Requires continual access to the database, while the DataAdapter uses a disconnected dataset to access the data • You must remain connected until you have finished reading the stream of data • Supports Relational and XML data • Methods & Properties • Read - returns a single row and caches each row in memory only once, then moves the current record pointer to the next record or row • CommandBehavior property - closes the connection as CloseConnection • Close method - closes the DataReader object and releases the references to the rowset • ExecuteReader method - retrieves rows of data as they are located in the database

  18. Using ADO.NET Objects to Access a DataSet Object

  19. Executing Select Commands via DataReader • Returning a read-only, forward only result set via SqlDataReader Dim oSqlCommand As SqlCommand Dim oSqlDataReader As SqlDataReader ' Create and run a new "select" command sCmdString = "SELECT * FROM Customers" oSqlCommand = New SqlCommand(sCmdString, oSqlConn) oSqlDataReader = oSqlCommand.ExecuteReader() Do While (oSqlDataReader.Read()) Console.Write(oSqlDataReader("ContactName")) Loop oSqlDataReader.Close() http://www.able-consulting.com/dotnet/ado/adonet101_files/frame.htm

  20. Executing Select Commands with Parameters Input Parameter for Stored Procedure oSqlCommand = New SqlCommand("spSelectCustomers", oSqlConn) oSqlCommand.CommandType = CommandType.StoredProcedure oSqlParameter = _oSqlCommand.Parameters.Add("@CustomerID", _ SqlDbType.NVarChar, 5) oSqlParameter.Value = "VINET“ oSqlDataReader = oSqlCommand.ExecuteReader() Do While (oSqlDataReader.Read()) Console.Write(oSqlDataReader("ContactName")) Loop oSqlDataReader.Close() http://www.able-consulting.com/dotnet/ado/adonet101_files/frame.htm

  21. Features of DataSet • DataSet is a “disconnected” data cache architecture • Can contain Tables, Columns, Relationships, and Constraints • Ability to cache data without an open connection • Data is cached in a DataSet • The DataSet is exposed, so it doesn’t matter where the data came from, or what application it will be used in I.e., DataSet is independent of data source(s) • Within the Web page, the ASP.NET data controls are bound to the DataReaders or DataSets. • For Read-Only, Forward-Only operations, use DataReader • Filling ComboBox Dropdowns • Read-only displays • For interactive operations, use Datasets • Data Binding • Navigation through the Dataset • Remoting the results of a database query –Insert/Updates • Data is persisted as XML Data structures are defined using XML Schema (xsd)

  22. Features of DataSet

  23. Using the DataSet Object toAccess the DataTableCollection

  24. The ADO.NET DataSet Object • Consists of the DataTableCollection and the DataRelationCollection • DataTableCollection is a collection of one or more DataTable Objects • Each DataTable object consists of a DataRowCollection, DataColumnCollection, and ConstraintCollection • The DataRowCollection and DataColumnCollection store information about the rows and columns of data • The ConstraintCollection includes information about the primary and foreign keys, and constraint rules

  25. The ADO.NET DataSet Object • A primary key is used to ensure that no duplicate records appear in this column. Therefore, if one customer has the customer number, no other customer can have that number • The Constraint rules are used to ensure that the field contains the correct datatype and values • The UniqueContraint and ForeignKeyConstraint are used to create the relationships • The DataRelationCollection contains the data required to maintain relationships between the DataTables • Relational data can be exposed via ADO.NET because relationships can be made between DataTables • The tables are joined using the primary and foreign keys that are defined in the DataTable object

  26. DataSet Designer

  27. Adding Constraints to a Dataset To populate a DataSet with existing constraint information from a data source, you can either call the FillSchema method of the DataAdapter, or set the MissingSchemaAction property of the Fill method to AddWithKey before calling Fill. Adding schema information to a DataSet before filling it with data ensures that primary key constraints will be included with the DataTable objects in the DataSet. As a result, when additional calls to Fill the DataSet are made, the primary key column information will be used to match new rows from the data source with current rows in each DataTable, and current data in the tables will be overwritten with data from the data source. Without the schema information, the new rows from the data source would be appended to the DataSet, resulting in duplicate rows. http://aspx.securedomains.com/devguide/adonet/addingexistingconstraintstodataset.aspx

  28. The ADO.NET DataAdapter Object • DataAdapter is used to get and maintain data from a data source - DataAdapter provides the bridge between the DataSet object and the data source • Because the DataSet is disconnected, there must be methods used to maintain the original set of data and the changes. To see latest changes made by other users, refresh the dataset using Fill method of the DataAdapter object • Is like a Command with a few specific differences • The Fill method populates a DataSet from the SelectCommand • The Update method takes changes from a DataSet and pushes them back into the database, using the InsertCommand, UpdateCommand, and DeleteCommand • SelectCommand is used to retrieve data • InsertCommand is used to add a new record • UpdateCommand is used to modify the data within an existing record • DeleteCommand is used to permanently remove a record from the database • DataAdapter commands are automatically generated via the CommandBuilder object • CommandBuilder object can generate these Commands at run-time based upon a Select statement ; However, the run-time generation requires an extra round-trip to the server in order to obtain the required metadata • Entering the insert, update, and delete commands at design time will result in better run-time performance!

  29. Filling a DataSet – Two Ways • via SQLDataAdapter Dim oDataSet As New CustomersDS SqlDataAdapter1.Fill(oDataSet) Return oDataSet • Filling a DataSet “by-hand” Dim oSqlDataAdapter As SqlDataAdapter Dim oSqlCommandBuilder As SqlCommandBuilder ‘ Load all Customers into DataSet table called Customers sCmdString = "SELECT * FROM Customers" oSqlDataAdapter = New SqlDataAdapter(sCmdString, oSqlConn) oDataSet = New DataSet() oSqlCommandBuilder = New SqlCommandBuilder(oSqlDataAdapter) oSqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey oSqlDataAdapter.Fill(oDataSet, "Customers") ‘ Display Contact Name for each record found in dataset For Each oDataRow In oDataSet.Tables("Customers").Rows Console.WriteLine(oDataRow("ContactName").ToString() ) Next http://www.able-consulting.com/dotnet/ado/adonet101_files/frame.htm

  30. Making Changes to a Database – Updating, Inserting and Deleting • There are 4 ways: • Work directly with SQLCommand sCmdString = "UPDATE Customers SET ContactName = 'Carl Prothman' “ & _ "WHERE CustomerID = 'VINET'“ oSqlCommand = New SqlCommand(sCmdString, oSqlConn) oSqlCommand.ExecuteNonQuery()) • Bind Dataset to DataGrid and use DataGrid’s Edit methods and properties in conjunction with SqlCommand • Use the DataAdapter’s UpdateCommand, InsertCommand and DeletCommand methods to store back changes made to a Dataset back to the Database (useful when Dataset is not bound to DataGrid, but to other controls) • Use SqlCommandBuilder when your Dataset consists of only a single table. The SqlCommandBuilder will create the DataAdapter commands behind the scenes. (useful when Dataset is not bound to DataGrid, but to other controls)

  31. Updating a Database from a DataSet 'Create a new DataRow based on Customers table oDataRow = oDataSet.Tables("Customers").NewRow() oDataRow("CustomerId") = "NewID" oDataRow("ContactName") = "New Name" oDataRow("CompanyName") = "New Company Name" ' Add DataRow to Customer Table’s Rows oDataSet.Tables("Customers").Rows.Add(oDataRow) ' Update the Database with the new record oSqlDataAdapter.Update(oDataSet, "Customers") oSqlDataReader.Close() http://www.able-consulting.com/dotnet/ado/adonet101_files/frame.htm

  32. The ADO.NET DataView Object • Contains the data from the DataSet for a single DataTable or subset of records from a table • The DataTable object has a DefaultView property that returns all the records in the DataTable. However, you can select a subset of records from a table • You an add columns to the DataColumnCollection as well as to filter and sort the data in a DataView • The RowFilter property of the DataView allows you to filter a subset of the DataView • The Sort property of the DataView also allows you to sort the data based upon a specific criteria in one or more of the columns

  33. XML and ADO.NET

  34. Internet Resources • There are lots of samples, documentation, and tutorials already on the Internet • Microsoft MSDN Library — http://msdn.microsoft.com/library/ • Microsoft UDA — http://www.microsoft.com/data/default.htm • MSDN Library — http://www.msdn.microsoft.com/library/ • SQL at Microsoft — http://www.microsoft.com/sql/

More Related