实例化 DataSet 对象是否会自动为 CRUD 操作创建到基于 SQL 服务的数据库的连接? [英] Does instantiating a DataSet object automatically create a connection to a SQL service-based database for CRUD operations?

问题描述

这是我所做的一切:

1. 在 Visual Studio 2013 C# 项目中，我创建了一个服务数据库(.mdf 文件).注意:我将名称从 Database1.mdf 更改为 fghLocalDB.mdf.

1. In a visual studio 2013 C# project, I created a service database (.mdf file). Note: I changed the name from Database1.mdf to fghLocalDB.mdf.

我在服务器浏览器中打开了这个数据库.

I opened this database in the server explorer.

我使用表设计器创建了 2 个名为 Country 和 CarbonDioxide 的表.

I created 2 tables called Country and CarbonDioxide using the table designer.

我在 Country 表中添加了一个条目，如 Country 表的数据表所示.

I added an entry to the Country table as shown by the Data Table of the Country table.

我执行了以下操作来创建我的应用程序可以使用的数据集.我通过单击顶部菜单栏上的项目"选项并单击下拉菜单中的添加新数据源..."选项来创建数据源.

I did the following to create a DataSet my application can use. I created a Data Source by clicking on the "Project" option on the top menu bar and clicking on the "Add New Data Source ..." option from the drop down.

这就是我的项目文件此时的样子.

This is what my project files looked like at this point.

我在 main 方法中编写了以下代码，认为这就是我需要写入数据库的全部内容.

I wrote the following code in the main method thinking that this would be all I need to write to the database.

// Create a connection to the DataSet and TableAdapters that will communicate with our 
// local database to handle CRUD operations.


fghLocalDBDataSet dataSet = new fghLocalDBDataSet();
fghLocalDBDataSetTableAdapters.CountryTableAdapter countryTableAdapter = 
new fghLocalDBDataSetTableAdapters.CountryTableAdapter();

try
{
   // Insert a row into Country table. EDIT 1 Will comment after first program run.
   Console.WriteLine(countryTableAdapter.Insert("United States"));

   // Actually writeback information to the database?      
   // dataSet.AcceptChanges(); EDIT 2 commented this as LeY suggested it was not needed.


   // EDIT 3 Validation code as suggested by Ley.
   var dt = new fghLocalDBDataSet.CountryDataTable();
   var adapter = new fghLocalDBDataSetTableAdapters.CountryTableAdapter();
   adapter.Fill(dt);

   foreach (var row in dt)
   {
            // This does not get executed after a second run of the program.        
            // Nothing is printed to the screen.
            Console.WriteLine("Id:" + row.Id + "----Name: " + row.Name);
   }
   Console.Read();
}
catch(SqlException exception){
   Console.WriteLine("ERROR: " + exception.ToString());
}
Console.ReadLine();

我运行了程序，一切看起来都很好.

I ran the program and everything seemed fine.

我认为这与连接字符串有关.我右键单击我的项目并打开属性.

I think it has to do with the connectionstring. I right clicked on my project and opened properties.

在这里，我通过查看数据库属性中的字符串来确保连接字符串与本地数据库的连接字符串匹配.他们是一样的.

Here I made sure the connection string matched that of the local database by looking at the string in the properties of the database. They are the same.

我复制并粘贴了每个连接字符串的实际文本:

I copied and pasted the actual text for each connection string:

项目的连接字符串:

数据源=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\fghLocalDB.mdf;Integrated Security=True

Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\fghLocalDB.mdf;Integrated Security=True

实际数据库的连接字符串(.mdf 文件):

Connection string of actual database (.mdf file):

Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\gabriel\Source\Workspaces\Capstone\Sandbox\aduclos\QueryDataMarketConsole\QueryDataMarketConsole\fghLocalDB.mdf;Integrated Security=True

Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\gabriel\Source\Workspaces\Capstone\Sandbox\aduclos\QueryDataMarketConsole\QueryDataMarketConsole\fghLocalDB.mdf;Integrated Security=True

我假设 |DataDirectory|等于 C:\Users\gabriel\Source\Workspaces\Capstone\Sandbox\aduclos\QueryDataMarketConsole\QueryDataMarketConsole\fghLocalDB.mdf；因为在上图中，当我单击按钮以展开连接字符串的值时，连接属性窗口会打开并显示数据库文件名的路径.

I am assuming |DataDirectory| is equal to C:\Users\gabriel\Source\Workspaces\Capstone\Sandbox\aduclos\QueryDataMarketConsole\QueryDataMarketConsole\fghLocalDB.mdf; since in the picture above when I clicked on the button to expand the Value of the connection string the connection properties window opened up and had this path for the database file name.

简而言之，我的问题是在代码中实例化 DataSet 对象是否会自动创建到基于 SQL 服务的数据库的连接以进行 CRUD 操作?

My question in a nutshell is does instantiating a DataSet object in the code automatically create a connection to a SQL service-based database for CRUD operations?

如果不是，我如何将我的 DataSet 对象连接到我的 sql 数据库，以便在使用 TableAdapter 时我可以实际写入数据库?

If not how do I connect my DataSet object to my sql database so that way I can actually write to the database when using the TableAdapters?

我阅读了以下链接:

TableAdapter 的插入方法不起作用?

TableAdapter 插入非持久化数据

在源代码文件中使用来自 web.config 的连接字符串

我需要一个实际的 SqlConnection 对象吗?以及如何将其连接到 DataSet &表适配器?

Do I need an actual SqlConnection object? and how to I connect this to the DataSet & TableAdapters?

推荐答案

我从未使用过 tableadpter.insert() 方法.但是我在我的本地机器上试过了，它有效.根据您提供的信息，我无法弄清楚您的问题，抱歉，但我可以为您指明方向.

I never used tableadpter.insert() method. But I tried it on my local machine, and it works. I can't figure out your problem based on the information you provided, sorry, but I can point you a direction.

如果您从向导中创建了所有内容，则无需担心连接，适配器表将为您处理连接.连接字符串(您圈出的)将自动添加到您的 app.config 文件以及您的设置类中.这就是您的应用程序(或您)使用它的方式.

If you created everything from wizard, you don't need to worry about the connection, the table Adapters will handle the connection for you. The connection string (you circled) will be added to your app.config file as well as your setting class automaticly. That is how your application (or you) uses it.

var countryTableAdapter = new CountryTableAdapter();countryTableAdapter.Insert("美国");

如果没有抛出异常，这2行代码足以将行插入数据库，我不知道为什么它对您不起作用.也许你验证它的方式出了问题，但你可以用另一种方式验证它.

This 2 lines of code are enough to insert the row into database if there is no exception thrown, I don't know why it doesn't work for you. Maybe the way you verify it somehow goes wrong, but you can verify it in another way.

countryTableAdapter.Insert 方法将返回受影响的行数，在您的情况下，应该是一.所以把下面的代码放进去，然后在后面设置一个断点.如果 rowAffected == 1，则插入有效.

The countryTableAdapter.Insert method will return the number of row get affected, in your case , should be one. So put the following code in , and set a breakpoint after it. if the rowAffected == 1, then the insertion works.

var rowAffected = countryTableAdapter.Insert("Test2")

如果您需要更多确认，请尝试此操作.

If you need more confirmation , try this.

var dt = new fghLocalDBDataSet.CountryDataTable();
var adapter = new CountryTableAdapter();
adapter.fill(dt);
foreach (var row in dt){
    Console.WriteLine("Id:" + row.Id + "----Name: " + row.Name);
}
Console.Read();

您将看到表中的所有记录.我希望这会有所帮助.

you will see all the records in your table. I hope this will help.

顺便说一下，从你的代码

By the way, from your code

dataSet.AcceptChanges();

上面的代码行根本不更新数据库.它只修改您的本地数据存储.它使用当前版本覆盖您的 dataRow 原始版本并将当前版本行状态更改为未更改.

The line of code above doesn't update the database at all. It only modify your local data storage. it overwrites your dataRow original version using current version and change the current version row state to unchanged.

只有 tableadapters 可以与数据库对话(我知道不是真的，但我只想说明 Dataset 不能直接与数据库对话).而且我通常只需要 tableadapte.Update 方法并使用正确的 RowState 传递 dataSet 或 dataTable.

Only the tableadapters can talk to database (not true I know, but I just want to make a point that Dataset can not talk to database directly). And I usually only need tableadapte.Update method and pass the dataSet or dataTable in with correct RowState.

tableAdapter.update 方法如果成功更新数据库，最终会在每一行上调用 AcceptChanges.

The tableAdapter.update method will call AcceptChanges on each row eventually if it successfully updated the database.

除非您只想更新内存中的数据集，否则您永远不需要显式调用 AcceptChanges.

You should never need to call AcceptChanges explicitly unless you only want update your dataset in memory.

我建议您阅读 ADO.NET 架构，以全面了解 DataSet 和 TableAdapter 的工作原理.

I recommend you to read ADO.NET Architecture to get the big picture how DataSet and TableAdapter worked.

这篇关于实例化 DataSet 对象是否会自动为 CRUD 操作创建到基于 SQL 服务的数据库的连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！