Getting a DataSet from an SQL Express Server C#

Question

How can I get a DataSet with all the data from a SQL Express server using C#?

Thanks

edit: To clarify, I do want all the data from every table. The reason for this, is that it is a relatively small database. Previously I'd been storing all three tables in an XML file using DataSet's abilities. However, I want to migrate it to a database.

Answer 1

I think you need to narrow down the question somewhat... All the data? You mean, all the data in every table in every database? Well, the only answer to that is, a lot of code.

To connect to and talk to a SQL Server Express database engine, use the classes in the System.Data.SqlClient namespace, namely:

• SqlConnection: Connect to the database
• SqlCommand: Talk to the database
• SqlDataReader: Iterate over data retrieved from the database

You can check the MSDN pages for all of these classes by clicking on the links above.

Here are some overview-links with more information:

• CodeProject: Beginners guide to accessing SQL Server through C#
• DevHood: Accessing SQL Server Data in C# with ADO.NET

Note that by and large, you use a SQL Server Express database engine the same way as the full SQL Server product, the difference is more in the tools you get with it, and some limitations in the express engine. Other than that you can just use the classes and language that you would use for a normal SQL Server database engine installation.

If this post didn't answer your question, please elaborate, and you have a higher chance of getting the answer you seek.

Answer 2

This can be done by using dataAdapter class.

Answer 3

You can use the GetSchema method to get all the tables in the database and then use a data adapter to fill a dataset. Something like this (I don't know if it compiles, I just paste some code and change it a bit):

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");

DataTable tables = null;
DataSet database = new DataSet();

using (DbConnection connection = factory.CreateConnection())
{

    connection.ConnectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";

    string[] restrictions = new string[4];

    // Catalog
    restrictions[0] = "Northwind";

    // Owner
    restrictions[1] = "dbo";

    // Table - We want all, so null
    restrictions[2] = null;

    // Table Type - Only tables and not views
    restrictions[3] = "BASE TABLE";

    connection.Open();

    // Here is my list of tables
    tables = connection.GetSchema("Tables", restrictions);

    // fill the dataset with the table data
    foreach (DataRow table in tables.Rows)
    {

        string tableName = table["TABLE_NAME"].ToString();

        DbDataAdapter adapter = factory.CreateDataAdapter();
        DbCommand command = factory.CreateCommand();
        command.Connection = connection;
        command.CommandType = CommandType.Text;
        command.CommandText = "select * from [" + tableName + "]";
        adapter.SelectCommand = command;
        adapter.Fill(database, tableName);

    }

}

EDIT:

Now I refactored it a bit and now it's working as it should. The use of DbConnection and DbProviderFactories is for database engine abstraction, I recommend using it so you can change the database engine changing this line and the connection string:

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OracleClient");

The GetSchema method will retrive all tables from your database to a DataTable and then we get all the data from each table to the DataSet using the DataAdapter.