Clever Geek Handbook

Getting DataSet from SQL Express Server C #

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

thank

edit: To clarify, I need all the data from each table. The reason for this is that it is a relatively small database. I used to store all three tables in an XML file using the DataSet's capabilities. However, I want to transfer it to the database.

0


source to share


3 answers


You can use the GetSchema method to get all tables in a database, and then use a data adapter to populate the dataset. Something like this (I don't know if it compiles, I just paste in the 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:



I have now reworked it a bit and now it works as it should. Using DbConnection and DbProviderFactories to abstract the database engine, I recommend using it so you can change the database engine by changing this string and the connection string:

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

The GetSchema method will dump all tables from your database into a DataTable, and then we will get all data from each table in the DataSet using the DataAdapter.

+2


source


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

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

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



Here are some overview links with more information:

Note that in general you are using the SQL Server Express database engine in the same way as the full SQL Server product, the difference is greater in the tools you get with it, and some limitations in the express engine. Other than that, you can simply use the classes and language that will be used to properly install the SQL Server Database Engine.

If this post did not answer your question, please clarify and you have a higher chance of getting the answer you are looking for.

+2


source


This can be done using the dataAdapter class.

0


source






More articles:


All Articles