Working with databases in C#

[sid78669]

Hey!

What i am looking for is help on how to work with databases in c#. I have basic knowledge of C# and a pretty good working knowledge of SQL. But I have never learned how to use them together. Can someone point me to a tutorial or something similar from which i may understand how both of them work?

The reason is that I want to write a small software which records data in a small database of its own, and then issues various select statements and uses the results for various calculations. Most of the links i came up with require a SQL server, but i want my software to run regardless of a server.

Thanks for reading this long ;)

[Ramy Mahrous]

Read about ADO.NET in http://msdn.com

[JerryShaw]

If you really don't need an SQL Server, then you can use XML.
Let me explain, On many projects I use a locally saved DataSet for all kinds of purposes, storing configuration, multi-media, and even storing serialized classes.

The DataSet class has an WriteXml method that will save the entire DataSet as an XML file. It has a ReadXml method that allows you to repopulate your applications dataset when you need it.

To get started, Create a dataset on your form, or special class, or DLL. Add the desired Tables, and columns with Types. You can even setup relationships similar to foreign keys between the tables.

Setup your form just like you would for any database centric application using binding sources, etc. (although this is optional).

Your application starts off with no data in the dataset, so let your user add data to the tables using your form. Once your user wants to save the data, then use

_myDataSet.WriteXml(_myFileName,XmlWriteMode.WriteSchema);

Now when the user starts the application, check to see if your filename exists, and if it does, use

_myDataSet.ReadXml(_myFileName);

Okay, now you have a local database contained in a DataSet. Now to perform SQL type queries, you can use the Table.Select method.
Lets assume you have a table named "Payroll" contained in your dataset. Lets say you want to get all the records for department "Engineering". (If you want all that start with Enginerring, you can use the LIKE 'Engineering%' qualifier, very much like SQL, read up on the Select method capabilities).

DataRow[] eng = Payroll.Select("Department='Engineering' ");

Now your eng array of datarows contains all of those records.

There are a ton of things you can do with the local dataset. Checkout "Expression" on MSDN to see how you can have automatic aggregation calculations. You can Filter the data using a number of options. You can even setup relationships and have a one to many display capability all done automatically.
Basically the DataSet is the abstract layer between you and the datasource. It is independent from Sql Server, DB2, MySQL, Oracle, or in this case XML.

Hope this helps, if you need more info, just let me know. I am between projects at this time, so I have a little slack time to help on this forum.

// Jerry