从SQL Express Server C＃获取DataSet

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

如何使用C#从SQL Express服务器获取包含所有数据的DataSet?

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.

编辑:为了澄清,我确实想要每个表中的所有数据。原因是它是一个相对较小的数据库。以前我使用DataSet的功能将所有三个表存储在XML文件中。但是,我想将其迁移到数据库。

3 个解决方案

#1

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):

您可以使用GetSchema方法获取数据库中的所有表,然后使用数据适配器填充数据集。像这样的东西(我不知道它是否编译,我只是粘贴一些代码并稍微改变一下):

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:

现在我重构了一下,现在它正在按原样运行。使用DbConnection和DbProviderFactories用于数据库引擎抽象,我建议使用它,以便您可以更改数据库引擎更改此行和连接字符串:

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.

GetSchema方法将从数据库中检索所有表到DataTable,然后使用DataAdapter将每个表中的所有数据都获取到DataSet。

#2

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:

要连接到SQL Server Express数据库引擎并与之通信,请使用System.Data.SqlClient命名空间中的类,即:

• SqlConnection: Connect to the database

SqlConnection:连接到数据库

• SqlCommand: Talk to the database

SqlCommand:与数据库对话

• SqlDataReader: Iterate over data retrieved from the database

SqlDataReader:迭代从数据库中检索的数据

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

您可以通过单击上面的链接来检查所有这些类的MSDN页面。

Here are some overview-links with more information:

以下是一些概述 - 包含更多信息的链接:

• CodeProject: Beginners guide to accessing SQL Server through C#

CodeProject:初学者指南通过C#访问SQL Server

• DevHood: Accessing SQL Server Data in C# with ADO.NET

DevHood:使用ADO.NET在C#中访问SQL Server数据

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.

请注意,大体上,您使用SQL Server Express数据库引擎的方式与完整的SQL Server产品相同,区别在于您使用它的工具,以及快速引擎中的一些限制。除此之外,您可以使用将用于正常SQL Server数据库引擎安装的类和语言。

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

如果这篇文章没有回答你的问题,请详细说明,你有更高的机会得到你想要的答案。

#3

This can be done by using dataAdapter class.

这可以通过使用dataAdapter类来完成。