Ado.net C#: Say What Exactly You Want to Data Sources!

ado.net c#Year after year, an alphabet soup of technology for database access has been introduced by Microsoft. These have acronyms like RDO, DAO, ODBC, ADO, DKNY (just kidding, not that last one)! The goal overall is to provide a set of consistent interfaces for programming that be utilized by various clients for speaking to a lot of different data sources including non-relational and relational data. To provide a user friendly interface for Microsoft Visual Basic, they created ADO, which stands for ActivX Data Objects. The way ADO looks and feels is not unlike the popular DAO or Data Access Objects which provides Jet access object model that pretty easy to use. By the way, here is a course  you might be interested in, entitled Visual Studio C# ado.net and taught by Tolga Karadayi.

There are a couple of advantages with the ADO model. One is that it is based on the OLD DB and thus gives a broad reach to programmers in terms of data sources. Also, it is a bit flatter, making it easy to use since you won’t have to do much traveling down a hierarchy of objects. Speaking of which, you might want to check out this course entitled Programming for Complete Beginners in C# that helps you learn C# coding with a hundred exercises to learn from.

Ado.net c# is a set of libraries that is object-oriented, allowing you to interact with sources of data. Usually, the sources of data are a database, but these could also be an XML file, Excel spread sheet or text file. Here is a course entitled C# Introduction that will help you learn programming fundamentals in this language of Microsoft.

As you probably already know, there are many different database types you can avail of. For instance, there is IBM DB2, Borland Interbase, Oracle, Microsoft Access and Microsoft SQL Server, to name a few. For the sake of just being on the same page, all the examples I will show you here will be using the SQL Server. If you want to know more about different frameworks such as the basics of LINQ here is an article entitled Understanding the Basics of LINQ Entity Framework you might want to check out.

When interacting with data sources, ado.net c# is the technology of .NET to use. You have many different providers of data allowing communication with various sources of data, depending on the used protocols or what the database happens to be. No matter what Data Provider you use, you will use a similar object set to interact with the source of data.

Providers of Data

Okay, so now you know that ado.net C# lets you interact with various types of databases and data sources. On the other hand, there is not a single class set that lets you universally get this done. Since various sources of data expose different protocols, you need a method of communication with the right sources of data using the correct protocols. Some older sources of data use the protocol ODBC. Many newer sources of data use the protocol OleDb and this does not even mention that fact that every day there are more sources of data allowing you direct communication with them through class libraries of ado.net C#.

There are different library sets for each way you can talk to a source of data and ado.net c# gives you a somewhat common method of interaction with these. By the way the name for these libraries is ‘data providers’ and usually these are named for the data source or protocol type that you are allowed to interact with. Some well-known providers of data are listed in Table 1, the type of data source they let you interact with and the API prefix used:

API PrefixProvider NameData Source Description
BdpBorland Data ProviderDatabase generic access such as Oracle, IBM DB2, SQL Server and Interbase.
SqlSQL Data ProviderFor Microsoft SQL Server interactions
OracleOracle Data ProviderFor Databases of Oracle
OleDbOleDb Data ProviderExposed OleDb Interface data source such as Excel or Access
OdbcODBC Data ProviderNormally an older database, source of data with an interface of ODBC.

To help you get what an API prefix actually means, here is an example. The connection object is one of the first an ado.net c# objects you will hear of, as gets you a connection to a source of data established. If you were using the data provider OleDb to connect to the source of data, this will expose an interface of OleDb. You will then need to use OleDbConnection, which is a connection object. In the same way, the name of the connection name will be pre-fixed with Sql or Odbc for a SqlConnection object on an SQL Server database or an OdbcConnection object on a data source Odbc respectively.

Objects of Ado.net C#

When working with data, there are many objects included in ado.net C# that you can work with. Here is a list of a lot of the main objects you can use. Over the course of learning, you will be exposed with many more objects of ado.net C# not included in this list. The ones listed here are the most important ones; since knowing them will give you an inkling of what types of data actions you can do using ado.net C#.

DataSet

These are representations of data that are in-memory. These contain a more than one object on the Datatable, which, just like normal tables of database, contains rows and columns. Relations can be defined between each table to create relationships that involve parents and children. Specifically, the DataSet is designed for helping you manage memory data and to support operations on the data that are disconnected, when this applies. An object that is used by all the Data Providers, a DataSet does not for this reason have a Data Provider prefix specifically.

SqlDataReader

Many operations of data require that you only get a data stream for reading. The object of data reader allows you to get a SELECT statement result from an object of command. For reasons of performance, the data that a data reader returns is a stream of data that is ‘fast forward only.’ What exactly does this mean? This means that from the stream, you can only pull data in a manner that is sequential. When you want more speed, this is great. However, for data manipulations, then a better object for working with is a DataSet.

SqlaCommand

When you interact with a database this means that you need to specify what actions you want to happen. A command object helps you do this. To send SQL statements to a database, you use a command object. Command objects use connection objects for figuring out which database it is possible to go through communication with. You can execute a direct command by using a command object alone, or hold a set of commands that work on a data group by assigning a reference to object commands to SqlDataAdapter.

SqlConnection

When interacting with databases, you need to connect to it. Doing this will help get the database server identified including the password, user name and database name as well as other required parameters for getting connected to the data base. Connection objects are used by command objects so that these know which databases to execute commands on. Hope this helps! Here is a course you might be interested in entitled Explore the Fundamentals of C# Programming. With this course, you can immerse yourself in .net and C# knowledge.