What is ADO.NET?

Question

I've written a few Access db's and used some light VBA, and had an OO class. Now I'm undertaking to write a C# db app. I've got VS and System.Data.SQLite installed and connected, and have entered my tables and columns, but that's where I'm stuck.

I'm trying to find what info and tutorials I need to look for, but there are a lot of terms I don't understand and I don't know if or exactly how they apply to my project.

I've read definitions for these terms (Wikipedia and elsewhere), but the definitions don't make sense to me because I don't know what they are or how they fit together or which ones are optional or not optional for my project.

Some of the terms on the System.Data.SQLite website (I wanted to use System.Data.SQLite for my db).

I figured my first step in my project would be to get the db and queries set up and tested. Please tell me if there are other pieces of this part of the puzzle I will need to know about, too. If I can figure out what's what, I can start looking for the tutorials I need. (btw, I know I don't want to use an ORM because my app is so simple, and because I want to keep from biting off too much too soon.)

Thank you very much.

SQLite.NET

Framework

ADO.NET

ADO.NET provider

ADO.NET 2.0 Provider for SQLite

Update: Removed "Entity Framework" terms because apparently they are ORM's, which I won't be using.

Also, please talk to me as if I know nothing except what my limited experience (above) covers (unfortunately that's the case since I've gotten so confused while trying to research this stuff, all the terms have overwhelmed me into overload-paralysis.) Thank you.

Answer 1

Loosely, in easy speak

ADO.NET is a library of commands and objects that allow your application to talk to a database. If you have used ADODB in VB6/C++ then ADO.NET is the .net (vb.net/C#) equivalent. ADO.NET provides you with objects such as a connection object, dataset and datareader objects.

ADO.NET provider Think of a provider like a graphics or device driver. Each time you put a different graphics card inside your computer you need to have a new graphics driver to get your graphics card to work at its best. The same is true for ADO.NET, for each different type of database you connect to (e.g Microsoft Access, Microsoft SQL Server, MySQL, Sybase, Oracle etc.) you will need a different ADODB.Net provider. A handful come as standard (for example, the provider for SQL Server)

SQLite.NET Is a database server or RDBMS - think of it as a lightweight competitor to SQL Server or MySQL.

ADO.NET 2.0 Provider for SQLite Combine the last two answers!

SQLite Entity Framework and SQLite Entity Framework provider This is a whole different subject completely. Look up Object Relational Mapping

Answer 2

There are large amounts of books written to cover this topic ... so this won't be an exhaustive answer, but this will give you the necessary information needed to begin. And it is very much simplified.

ADO.NET is a framework that allows you to manage, in memory, the data retrieved from the database (permanent storage) and connect it to display objects (textboxes, etc). Access databases have all the "layers" contained in it (the form, query, tables) and you don't have to mess too much with the activities to retrieve the information and display them. However, now that you have graduated to a Visual Studio project, you need to manage each of the layers.

1. Create your database
2. Populate it with data
3. Create stored procedures (in the database), or write SQL statements (in the application) to retrieve, insert, delete, update data by using Command objects
4. Install a data provider (for SQLite, MSSQL, MySQL, Oracle)
5. Build the User Interface
6. In the interface Events, create an instance of the a ADO.NET provider Connection, Adapter, and Command/Table objects.
7. Using the Command objects (and DataReaders), retrieve data using SELECT statements; and subsequently using Update, Insert, Delete statements to put data back.
8. Update the interface text boxes by referencing the Reader fields.

In parts 3-7 you will do most of your work (the stuff you are asking about); you will be using ADO.net to connect to the data source, using a data provider (SQLite) + connection string (with Catalog name, Username, Password, Connection type). Then, use the objects, like Connections (to connect), Adapters (to build holding areas), and DataTables (tables in memory) to do the data retrieval and actions that get and push the data to/from the database (static permanent data).