linking application to data

so … we have an application, written in c#. we have a rockin’ sql database. how do we get it together? how do we get sql and c# to talk to each other? that’s what this post will discuss. we need an API – an application programming interface – that will facilitate our program’s access to the data to carry out specified functions. a number of alternatives exist.

1. ODBC ~ open database connectivity ~ a procedural API (common set of functions/methods) for using SQL queries to access data. limitations: unsuitable for accessing non-relational data such as spreadsheets, text files and xml files. An implementation of ODBC will contain one or more applications, a core ODBC library, and one or more “database drivers”. The core library, independent of the applications and DBMS, acts as an “interpreter” between the applications and the database drivers, whereas the database drivers contain the DBMS-specific details. Thus a programmer can write applications that use standard types and features without concern for the specifics of each DBMS that the applications may encounter. Likewise, database driver implementors need only know how to attach to the core library. This makes ODBC modular.

2. OLE DB ~ object linking and embedding, database ~ a procedural API developed by MS as a successor to ODBC, to extend its feature set to support a wide variety of non-relational databases, such as object databases, and spreadsheets. OLE DB separates the data store from the application that needs access to it through a set of abstractions that include the datasource, session, command and rowsets. This was done because different applications need access to different types and sources of data and do not necessarily want to know how to access functionality with technology-specific methods. OLE DB is conceptually divided into consumers and providers. The consumers are the applications that need access to the data, and the provider is the software component that implements the interface and therefore provides the data to the consumer.

3. ADO.NET ~ activeX data object .NET ~ an API that’s a base class in the MS .NET framework. ADO.NET consists of a set of objects in the Systems.Data namespace that communicate with the database via .NET providers. these objects allow connectivity to the database to retrieve, edit, delete and insert data in the database and manipulate the data within the program. ADO.NET consists of two parts – (i). the DataSet and (ii). the .NET provider.

(i). DataSet ~ does not provide or maintain connectivity to the database. provides a copy of the data source – in memory – with which to work. consists of a nested collection of objects that allow for manipulation of data within the dataset. see figure below.

(ii).NET data provider objects ~ consists of objects that faciliate connectivity to individual data sources. each provider resides in its own namespace within the System.Data namespace. @ present, 4 main components. see figure below.

to access an SQL Server database requires an OLE DB connection provider.