General ADO.NET Usage Question

Question

I’m just a little unsure about how ADO.NET can and should be used.  It is my understanding that t he typical deal is that you add a data source to a project, and Visual Studio creates a bunch of classes that are used to interact with a database. I understand the utility in that, but it is a bit rigid (which might be the point). Unfortunately, I am required to work with Access databases. These databases can change. For instance, some may have additional tables and/or columns than the “base” database. If I create a data source in Visual Studio, I basically have to pick one of these schemas and stick with it. That, unfortunately, is not an option. My code has to determine which tables/columns exist and adjust – in other words, it has to support multiple database versions. I suppose I’d just like to hear your thoughts on the subject. Is what I’m doing completely dumb? Can I manually create classes to give me some of the benefits of ADO.NET (strong typing, ease of access, schema validation, etc.)? Is there some way to create multiple data sources and just use the one that matches the database being used? Any nudge in the right direction would be appreciated. Thanks!

Answer 1

Hi Michael,

 

ADO.NET provides consistent access to data sources such as Microsoft SQL Server, as well as data sources exposed through OLE DB and XML. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update data.

 

You can get more about ADO.NET from this MSDN document.

 

About your question, I think it is depends on your requirements. If you are using strong typed dataset, you have to update the model when the table schema changes.

If you are using untyped dataset or datatable, you can manipulate it in code.

For example,

 

1.  OleDbDataAdapter myDA;

2.  DataSet myDataSet;

3.  private void // ERROR: Handles clauses are not supported in C# Form1_Load(System.Object sender, System.EventArgs e)

4.  {

5.      OleDbConnection con = new OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\\myDB.mdb");

6.      OleDbCommand cmd = new OleDbCommand("SELECT * FROM Table1", con);

7.      con.Open();

8.      myDA = new OleDbDataAdapter(cmd);

9.      //Here one CommandBuilder object is required.

10.     //It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object

11.     OleDbCommandBuilder builder = new OleDbCommandBuilder(myDA);

12.     myDataSet = new DataSet();

13.     myDA.Fill(myDataSet, "MyTable");

14.     DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView;

15.     con.Close();

16. }

 

The yellow part is untyped dataset.

 

For changing database, the usual way is to store the connection string in configuration file. You can modify it as you want.

Here is an article for your reference,

http://www.dreamincode.net/forums/showtopic45321.htm

 

You can store multiple connection string in configuration file, and try to open each connection string. But it is not recommended. You can update the configuration file with the latest one.

 

 

If you have any questions or concerns, please update the thread and we will have a further discussion.

 

 

Best Regards

Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.