datagrid 填充数据_使用Visual Studio 2008以最少的方式从数据库填充Datagrid

datagrid 填充数据

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows application.

.Net的对象模型有时可能不堪重负-如此不堪重负，以至于琐碎的任务通常需要数小时的研究。 在这种情况下，当前的任务是从Visual Studio 2008 Windows应用程序中SQL Server数据库填充数据网格。

Google search of course returns code samples; so does Visual Studio Help; however I did not find a single sample that wouldn’t create some objects in code. This got me curious: during the design, Visual Studio GUI creates several objects facilitating this data extraction, so how come we need to create even more in code? Let’s try to do it without any.

Google搜索当然会返回代码示例； Visual Studio帮助也是如此； 但是我没有找到一个不会在代码中创建某些对象的示例。 这让我感到好奇：在设计过程中，Visual Studio GUI创建了多个对象来促进数据提取，那么我们为什么需要在代码中创建更多对象呢？ 让我们尝试不做任何事情。

For this example, we create a Windows form, and place a textbox and DatagridView control on it. We will need to populate it from SQL Server database using stored procedure with one parameter, which value will be taken from the textbox - very typical task. For our sample (based on real life shipping application), we need to retrieve serial numbers of the units assigned to a given packlist. The user will enter packlist number, press the button, and receive the list of the serial numbers of the units he needs pull from the shelf and ship to the customer. The stored procedure in SQL Server is

对于此示例，我们创建一个Windows窗体，并在其上放置一个文本框和DatagridView控件。 我们将需要使用带有一个参数的存储过程从SQL Server数据库中填充它，该值将从文本框中获取-这是非常典型的任务。 对于我们的样本（基于现实生活中的运输应用程序），我们需要检索分配给给定装箱单的设备的序列号。 用户将输入装箱单编号，按下按钮，然后接收需要从货架上拉出并运送给客户的设备的序列号列表。 SQL Server中的存储过程为

Create procedure packlist_sn(@packlistid int) as 
select sn from serialno where packlistid=@packlistid
go

Of course you can simply use one of your own procedures in this example. Or, if you want to follow along, then to create the table for the Stored Procedure with some sample data you can use the following code :

当然，在此示例中，您可以简单地使用自己的过程之一。 或者，如果您想继续，那么要使用一些示例数据为存储过程创建表，可以使用以下代码：

create table serialno (packlistid int, sn varchar(20))
insert serialno values (1,'sn1')
insert serialno values (1,'sn3')
insert serialno values (1,'sn5')
insert serialno values (2,'sn2')
insert serialno values (2,'sn8')

Here’s the initial view of our form, with the datagrid occupying the lower part.

这是我们表单的初始视图，其中数据网格占据了下部。

The next step is to specify our stored procedure as the data source of the datagrid. We achieve it by specifying the property “Datasource” of the grid. We select “Add new data source” and follow the wizard to specify our SQL Server database, and within it, our stored procedure packlist_sn . Once we are finished, we find our Datagrid complete with the column sn that will be returned by the stored procedure, plus, there are now 3 new components added to the form – dataset, binding source, and table adapter.

下一步是将我们的存储过程指定为datagrid的数据源。 我们通过指定网格的属性“ Datasource”来实现。 我们选择“添加新数据源”，然后按照向导指定我们SQL Server数据库，并在其中指定存储过程packlist_sn。 完成后，我们发现Datagrid带有存储过程将返回的sn列，此外，现在在表单中添加了3个新组件–数据集，绑定源和表适配器。

After having completed the wizard, in the Datasource of the datagrid we find PacklissnBindingSource, which is one of the generated components. In turn, PacklistsnBindingSource has its own DataSource which is another generated component  - DbDataset. The 3rd generated component, Packlist_snTableAdapter, is in fact a public component of DbDataset. It serves as a bridge between this data model and the database, and in its properties we will find SQL Server connection string as well as SQL commands for data manipulation – select, update, delete, and insert. All they are available through the constructor:

完成向导后，在数据网格的数据源中找到PacklissnBindingSource，它是生成的组件之一。 反过来，PacklistsnBindingSource有其自己的DataSource，它是另一个生成的组件-DbDataset。 实际上，第三个生成的组件Packlist_snTableAdapter是DbDataset的公共组件。 它充当此数据模型与数据库之间的桥梁，并且在其属性中，我们将找到SQL Server连接字符串以及用于数据操作SQL命令-选择，更新，删除和插入。 它们都可以通过构造函数使用：

Note that at the above picture, only SelectCommand is specified, while InsertCommand, UpdateCommand, and DeleteCommand are not. This is correct and desired, since we indeed want only to retrieve the data, and we are not going to edit them in the database.

请注意，在上图中，仅指定了SelectCommand，而没有指定InsertCommand，UpdateCommand和DeleteCommand。 这是正确且理想的，因为我们确实只想检索数据，并且我们不会在数据库中对其进行编辑。

So, our datagrid is based on the dataset dbDataset, which Visual Studio has automatically craeated and added to the project (dbDataSet.xsd in the project tree above), and the dataset will be populated from the SQL Server database.

因此，我们的数据网格基于dbDataset数据集，Visual Studio已自动将其插入并添加到项目中（上述项目树中的dbDataSet.xsd），并且将从SQL Server数据库中填充该数据集。

At this point, it’s good idea to verify that everything is working correctly: click on the small right arrow button in the upper right corner of the datagrid, select “Preview Data”, and upon manually entering our parameter of the stored procedure, we can see what data is about to be returned to our application.

此时，最好验证一下一切是否正常：单击数据网格右上角的右小箭头按钮，选择“ Preview Data”，然后在手动输入存储过程的参数后，我们可以查看哪些数据将要返回到我们的应用程序。

In the application, the dataset, and consequently the grid, won’t be populated automatically by the adapter; we need to provide the code, which among other things will provide the value of the parameter. This is where the many ways to achieve that mentioned in the beginning of the article come into play. Arguably the shortest of them all, and not involving any new objects besides those already created, is the following code:

在应用程序中，适配器不会自动填充数据集，因此也不会自动填充网格。 我们需要提供代码，该代码除其他外将提供参数的值。 这是实现本文开头提到的许多方法的地方。 可以说，它们中最短的是以下代码，除了已经创建的对象之外，不涉及任何新对象：

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
   Packlist_snTableAdapter.Fill(DbDataSet.packlist_sn, TextBox1.Text)
End Sub

In fact, this one line of the code has been the essence of this article. And it works!

实际上，这一行代码已经成为本文的重点。 而且有效！

This solution also will work in compact application, without any changes (we tested it in Windows Mobile 6), so long as you are connected to your network. The only nuance will be the SQL Server connection string, that is, what needs to be specified as server\instance for the compact application to connect. Instead of NAME\INSTANCE, we will need to specify IP address (depending on the network connection of the mobile device); also, we need to explicitly specify SQL Server's listening port number, which is 1433 by default. This is not necessary in desktop Windows, but without the port number, Windows Mobile 6 will return "Invalid Connection".

只要您连接到网络，该解决方案也可以在紧凑的应用程序中运行，而无需进行任何更改（我们在Windows Mobile 6中对其进行了测试）。 唯一的区别是SQL Server连接字符串，也就是说，紧凑型应用程序要连接时需要指定为server \ instance。 而不是NAME \ INSTANCE，我们将需要指定IP地址（取决于移动设备的网络连接）； 另外，我们需要显式指定SQL Server的侦听端口号，默认情况下为1433。 在桌面Windows中这不是必需的，但是如果没有端口号，Windows Mobile 6将返回“无效的连接”。

For example, when testing the compact device, connected by USB connection, the connection string that worked was :

例如，在测试通过USB连接连接的紧凑型设备时，有效的连接字符串为：

Data Source=169.254.2.2,1433; (other parameters as usual)

数据源= 169.254.2.2,1433;

With this device in hand, our shipping department employee will have much less chance to forget the serial numbers on his way from the application screen to the shelf.

有了此设备，我们的运输部门员工将很少有机会忘记从应用程序屏幕到货架的过程中的序列号。

翻译自: https://www.experts-exchange.com/articles/4353/Populating-Datagrid-from-the-database-using-minimum-means-with-Visual-Studio-2008.html

datagrid 填充数据