Database access, in the .NET Framework

Database access, in the .NET Framework Introduction to ADO.NET ADO.NET is an object model that lets you build sets of data in memory. You can create data sets from data you have in an spread-sheet, retrieve from a text file or an XML file, retrieve from an exchange server, or from a database system. ADO.NET does not care where the data comes from as long as you can fit it into rows and columns within its objects. ADO.NET has several classes that we need to master: Some of the most important classes are the Connection, Command, CommandBuilder, DataAdapter, DataSet, DataTable, Dataview and DataReader classes Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ 1 Class DataSet Description This class is like an in-memory database. It can contain one or more DataTable objects. This class can hold the original set of data, and any modifications made to this data. You can then use other ADO.NET objects to submit these changes back to the data source. DataTable DataView This class holds rows of data. Each row is made up of columns and each column can hold data. This class is a specific view of a DataTable. This object is frequently used to set sort orders or filters on the original DataTable object. This class is used to create a connection to a data source. There are two default types of this class, one is for OLE DB data sources and one is specific to SQL Server. This class is used to submit SQL statements to a back end data source. You can use SQL or calls to stored procedures for all data retrieval and modification. This class has methods to submit SQL that modifies data, and methods to retrieve data. This class is used to build Command objects specifically to SELECT, INSERT, UPDATE or DELETE data from a data source. This class is used to populate a DataSet or a DataTable object with data from a database. It will create a connection to a database, build a command object with the appropriate SQL statement, retrieve the data and build a DataSet or DataTable object, then disconnect from the database. The output from this object will be fully populated DataSet or DataTable. This class is a forward-only, read-only cursor (sometimes called a fire-hose cursor) that will quickly read data from a data source. This class is excellent for filling data grids, list boxes and combo boxes. Connection Command CommandBuilder DataAdapter DataReader Database access, in the .NET Framework, is handled by the classes in the System.Data namespaces. The main sets of classes: System.Data - This namespace contains database classes for working with inmemory databases. The classes in this namespace include the DataSet. Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ 2 OleDb and SqlClient There are two different flavors of the Connection, Command, CommandBuilder, DataAdapter and DataReader classes. One set of these classes, prefixed with “OleDb” and use the OLE DB providers to get at a data source. The other set are prefixed with “Sql” and use native providers to talk directly to SQL Server. These two sets of classes come from two separate namespaces.   System.Data.SqlClient - This namespace contains classes for working with Microsoft SQL Server (version 7.0 or higher). The classes in this namespace includes the SqlDataReader. System.Data.OleDb - This namespace contains classes for working with any database that has an OLE DB provider such as Microsoft Access or Oracle. The classes in this namespace include in the OleDbDataReader. SqlClient versus OleDb ADO.NET contains one set of classes designed specifically for accessing Microsoft SQL Server and another set of classes for working with every other database. If you plan to work exclusively with Microsoft SQL Server (version 7.0 or higher) then you'll want to use the classes in the System.Data.SqlClient namespace. Here is a list of the important classes in the System.Data.SqlClient namespace:    SqlConnection - Represents a connection to a Microsoft SQL Server database SqlCommand - Represents a SQL command or a SQL Server stored procedure SqlDataReader - Represents a set of database records The classes in the System.Data.SqlClient namespace have been optimized for Microsoft SQL Server. These classes do not depend on OLE DB, ODBC or any intermediate layer to communicate with SQL Server. Below are the complete names you would use to declare the Sql classes. Dim oConn As SqlClient.SqlConnection Dim oCmd As SqlClient.SqlCommand Dim oBuild As SqlClient.SqlCommandBuilder Dim oDA As SqlClient.SqlDataAdapter Dim oDR As SqlClient.SqlDataReader Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ 3 If you need to communicate with another type of database, such as Microsoft Access or Oracle, then you need to use the classes from the System.Data.OleDb namespace. These classes work with (almost) any existing OLE DB Provider. Here is a list of the important classes in the System.Data.OleDb namespace:    OleDbConnection - Represents a connection to an OLE DB data source OleDbCommand - Represents a SQL command or stored procedure OleDbDataReader - Represents a set of database records . Below are the complete names you would use to declare the OleDb classes. Dim oConn As OleDb.OleDbConnection Dim oCmd As OleDb.OleDbCommand Dim oBuild As OleDb.OleDbCommandBuilder Dim oDA As OleDb.OleDb.OleDbDataAdapter Dim oDR As OleDb.OleDb.OleDbDataReader Notice that the classes in the System.Data.OleDb namespace mirror the classes in the System.Data.SqlClient namespace. The two sets of classes have almost exactly the same properties and methods. The only difference is that one set of classes is optimized for SQL Server and the other set of classes is not. Understanding Connection Object: Connection Objects are responsible for handling the physical connection between a datastore and a .NET application. The two Data Providers supported by the .NET Framework implement the OleDBConnection in the System.Data.OleDB namespace and the SqlConnection in the System.Data.SqlClient namespace. Creating Connections: Connections can be created at design-time and at run-time. For design time, we can use a DataAdapter Configuration wizard which in-turn creates a Connection Object automatically. Run-time connection object can be created by first declaring the connection object of type SqlClient or OleDB and then using the Open method which can be invoked by either Data Adapter or the Data Command Objects. strConn = "Provider=sqloledb;Data Source=DESIMONE; Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ 4 Initial catalog=ITM;User ID=sa;Pasword=;" strSQL = "Select ConsultantId,ConsultantName From Consultants" oCmd = New OleDb.OleDbCommand() With oCmd .Connection = New OleDb.OleDbConnection(strConn) .Connection.Open() .CommandText = strSQL oDR = .ExecuteReader(CommandBehavior.SequentialAccess) End With Understanding Command Object: Command Object is simply a SQL command or a reference to a stored procedure that is executed against a Connection Object, it basically retrieves and updates data. When a Data Command returns a result set, a DataReader is used to retrieve the data. Data Commands can be a OleDbCommand or a SqlCommand. Creating Command Objects: Command Objects can be created at design-time and at run-time. cmdGetConsultants = New SqlClient.SqlCommand() Me. cmdGetConsultants.Connection = Me.cnITM Me. cmdGetConsultants.CommandText = "SELECT * FROM Consultants " & _ "WHERE (ConsultantCity = @City) AND (ConsultantSex = @Sex)" Me.cnITM.Open() oDR = Me. cmdGetConsultants.ExecuteReader Command Object supports Parameter collection for the SQL command or stored procedure specified in the CommandText property, however we must add each of the required parameters to the Parameters collection of the command object using the “Add method”. Depending upon the Data Provider we use we will be supplying the parameters accordingly. Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ 5 OleDbCommand uses a question mark and the other uses @ character. ' Build the connection string strConn = "Provider=sqloledb;Data Source=DESIMONE;Initial catalog=ITM;User ID=sa;Password=;" strSQL = "ConsultantByCity @ConsultantCity=?" ' Setup a Command object oCmd = New OleDb.OleDbCommand() With oCmd ' Create New Connection .Connection = _ New OleDb.OleDbConnection(strConn) ' Open Connection .Connection.Open() ' Set SQL string .CommandText = strSQL .CommandType = CommandType.StoredProcedure ' Add parameters, mention the datatype and length .Parameters.Add("ConsultantCity", Data.OleDb.OleDbType.VarChar, 15) .Parameters("ConsultantCity").Value = txtSearchCity.Text ' Execute stored procedure oDR = .ExecuteReader(CommandBehavior.SequentialAccess) End With Confusing Command Object Methods: Command Object offers four confusing Execute methods called ExecuteNonQuery, ExecuteReader, ExecuteScalar and ExecuteXMLReader ExecuteNonQuery is used when when the SQL command or stored procedure to be executed returns no rows. (Ex: Update Query) ExecuteScalar is used for SQL commands or stored procedures that return a single value. (Ex: Select Count(*) from Consultants) ExecuteReader used for commands returning multiple rows. ExecuteReader can take parameters for example CommandBehaviour with a value of SequentialAccess which will help in reading large binary values. Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ 6 Understanding DataReaders Object: If you need to quickly retrieve and display database records then you can use either the SqlDataReader or OleDbDataReader class. The DataReader class requires an open database connection. You can use the DataReader to fetch a single record into memory at a time. It represents the fastest method of retrieving database records. You cannot use the DataReader class, however, to cache database records. You also cannot use the DataReader to sort or filter records. Understanding DataAdapter Object: DataAdapter sits between the Connection object and the Dataset object and is responsible to pass the data between them. Creating DataAdapter : DataAdapter can be created at design-time and at runtime. For design time, we can use a DataAdapter Configuration wizard which is used from the Server Explorer. Dim oAdapter As OleDb.OleDbDataAdapter oAdapter = New OleDb.OleDbDataAdapter(strSQL, strConn) moDS = New DataSet() oAdapter.Fill(moDS,”Consultants”) The DataAdapter supports two important methods, Fill and Update. Fill: Loads data from the Data Source into a DataSet Update: Loads data from a DataSet into a Data Source To perform updates on the datasource a DataAdapter uses four Data Commands, one for each possible action namely, SelectCommand, UpdateCommand, InsertCommand and DeleteCommand. Understanding DataSet Object: If you want to create an in-memory representation of a set of records, then you need to use the DataSet class rather than the DataReader class. The DataSet class represents an in-memory database. ADO.NET supports two distinct types of DataSets called TYPED and UNTYPED Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ 7 A Typed DataSet exposes its tables and the columns within them as object properties, meaning we can reference tables and columns directly by their names. For Ex: A Typed DataSet called dsConsultants that contains a Table called dtConsultants and has a column called ConsultantSex, we can reference the value of the ConsultantSex column in the first row as Me.dsConsultants.dtConsultants(0).ConsultantSex Compared to Untyped DatSet where we would do the same thing as Me.dsConsultants.Tables(dtConsultants).Rows(0).Item(“ConsultantSex”) The Typed DataSets provides another important benefit, it allows compile-time checking of data values, which is called “strong typing” Ex: ConsultantSex is a numeric field (0 for Male and 1 for Female) Me.dsConsultants.dtConsultants(0).ConsultantSex=”Male” would generate an compilation error which will not happen in case of Untyped DataSet. Creating DataSet Objects: DataSet Objects can be created at design-time and at run-time. At design-time the DataSet gets generated as a part of running the DataAdapter wizard which outputs the connection object and the DataSet can be generated from the Data Menu item. Another way of generating a DataSet at design-time would be explicitly dragging it from the tool-box and configuring it using the wizard wherein we will have the option of either going for a Untyped or a Typed DataSet. We need a DataAdapter Object to generate a DataSet using its Fill method oAdapter = New OleDb.OleDbDataAdapter(strSQL, strConn) oAdapter.Fill(moDS, "Consultants") The DataSet class is part of the System.Data namespace. This namespace includes the following important classes:  DataSet - Represents an in-memory database 8 Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ   DataTable - Represents an in-memory database table DataView - Represents an in-memory database view (a filtered or sorted representation of a DataTable) The DataSet Object has Tables collection, each table being a DataTable object which in-turn has collections of Rows as DataRow object and collections of columns as DataColumns object. There is another collection called the DataRelation which represents the relationship between DataTable Objects A DataTable gets created as a result of using the Fill method of the DataAdapter Object, however it can also be created using the Add method of the Tables collection of the DataSet Object. Me.moDS.Table.Add(“Consultants”)) Or dtCons=new System.Data.DataTable(“Consultants”) Binding a DataSet/DataTable to a control Me.lstConsultants.DataSource= Me.moDS.Tables(“Consultants”) Me.lstConsultants.DisplayMember=”ConsultantName” For Each oRow In moDS.Tables("Consultants").Rows oConsult = New GenericIDName() With oConsult .ID = CInt(oRow.Item("ConsultantID")) .Name = oRow.Item("ConsultantName") End With lstConsultants.Items.Add(oConsult) Next Unlike a DataReader, there is not a distinct version of a DataSet optimized for Microsoft SQL Server and a distinct version for every other database. There is a single DataSet that works with all databases. Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ 9 ADO.NET Versus ADO ADO.NET is an enhanced version of ADO. While there are some basic differences between the two objects models, the end result is still the same. You need an object model that allows you to store data, retrieve data, and modify data. ADO let you do that with Recordsets, ADO.NET lets you do that with DataSets and a few other objects as discussed above. Prepared By : Hafeez Mohammed for ITM Consulting and Training Group Inc, 6 Kilmer Road, Edison, NJ 10