Databases


What is the difference between ADO and ADO.NET ?

ADO.NET is a powerful successor to ADO which provides you with the same disconnected philosophy as the Web.
ADO.NET only supports forward-only, read-only resultsets and dsiconnected resultsest.
The key difference between ADO and ADO.NET is that ADO.NET can have a disconnected architecture.
Data is retrieved from the database and cached on your local machine. You only connect to the database when you need to change or acquire new data.
ADO.Net uses XML for representing data carries forward the .NET philosophy and ensures that data can be communicated with a wide variety of data sources, objects and applications.


Connected vs Disconnected data

You should use a Connected Data Access when:

  • Transactions are required

  • No user interaction with the data is necessary

  • processes large amounts of data

When you maintain a direct connection to a data source then you should use the classes SqlConnection, SqlCommand and SqlReader


You should use a Disconnected Data Access when:

  • User interaction is required

  • If memory data is required

  • Distributed applications

When you maintain a disconnected connection you should use a DataSet


Typed vs Untyped Datasets

A typed dataset is binded with the database table(s) at design time.
You have direct access to all the schema information and can use/reference the actual table and column names.
The schema file is added as an (.xsd) file


An untyped dataset is binded at runtime. You are not aware of the schema information and have to access the tables and columns using index numbers.



Data Providers

ADO.NET uses the concept of a data provider to provide access to different types of databases.
The .NET framework includes the following two data providers
Different data providers use different namespaces. The two common namespaces are:
System.Data.SqlClient - contains classes that give optimised access to Microsoft SQL Server 7.0 and later
System.Data.OleDB - contains classes that give access to Microsoft SQL Server 6.5 and earlier. Also provides access to Oracle, sybase, Access etc


The DataAdapter is the bridge between the Connection and the DataSet.


 


ADO.NET Namespaces

There are six important namespaces
System::Data - contains all the objects that are generic to ADO.NET and that do not belong to a specific data provider.
(DataSet, DataTable, DataRow, DataColumn, DataRelation)


System::Data::Common - contains the DataAdapter object which both OleDbDataAdapter and SqlDataAdapter inherit from.
System::Xml -


Important Namespaces
System.Data.Common - used for permissions, table column mappings
System.Data.SqlTypes - define native types in SQL Server.


Storing connection strings in the web.config


Server Explorer

This is a graphical tool that lets you establish connections to local, client server or Internet based data sources. Using server explorer you can view the structure of the database tables and learn more about the attributes of the tables, fields and records in a database.


You can also log on to network servers and explore the databases and system services that they offer.
You can drag database components, or nodes from server explorer and drop them onto theVisual Studio.NET designers, such as Windows Forms Designer.


Before you can manipulate the information in a database, you need to establish a connection to it.


The DataAdapter objects Fill method opens up the connection, runs the query through a DataReader, constructs the DataTable objects and closes the connection
The Visual Studio IDE lets you drag and drop a number of Wizards to construct much of the code.


To register a database (View > Server Explorer) right click Data Connections, then Add Connection
Drag the datanase node onto the Windows Form.
This creates an OLEDBConnection to the source



ADO.NET Objects

CommandSqlCommand OleDbCommandOdbcCommand
ConnectionSqlConnectionOleDbConnectionOdbcConnection
DataAdapterSqlDataAdapterOleDbDataAdapterOdbcDataAdapter
DataReaderSqlDataReaderOleDbDataReaderOdbcDataReader


To easily view the layout of the System.Data namespace (View > Other Windows) use the Object Browser


Binding Source

??



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Next