C# – Getting a DataSet from an SQL Express Server C#

cdatabasedatasetsql server

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.

Best Answer

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.