将数据批量插入数据库. [英] Bulk insertion of data in to database.

问题描述

我有一个称为"studentdetails"的表格.我不想在单击按钮时一一插入记录，而是想使用循环语句插入几百条记录.任何想法?
例如:
我有表Colums(Name，Degree，Fees)sql server(2008).
我在前端有一个gridview控件，当我在gridview中填充十行并单击保存"按钮时，必须一次将总共10行保存在表中.

请求任何代码，建议或链接
请紧迫...

谢谢... .....

i have a table called ''studentdetails''. i dont want to insert the record one by one during button click,instead i want to insert some hundred records using looping statement.Any idea??
FOR EXAMPLLE:
i have table Colums(Name,Degree,Fees) sql server(2008).
i have a gridview control in front end , when i fill ten rows in gridview and click button save, then total 10 rows must be saved in the table at a time.

Requesting any code,suggestion or link
pls its urgent...

THANK''S .....

推荐答案

在循环内调用DB绝不是一个好主意，因为它既费时又容易出错.我建议您创建数据的XML(10条记录或100条记录)并将其发送到SP. 此处是可以从XML读取的SP的示例.

Calling DB inside a loop is never a good idea as it is time-consuming and error-prone. I would suggest that you create XML of your data (10 records or 100 records) and send that to your SP. Here is an example of SP which can read from XML.

你好，亲爱的;)
好的，现在我来说明如何使用DataSet
使用DataSet的最简单方法是使用"DataSet Designer".因为VS的这个工具会生成很多类和代码，从而导致非常容易地将数据编辑，删除和插入到数据库中.
首先在数据库(SQL或ACCESS)中创建所需的表.如果需要，在它们之间创建关系.在数据库中完成工作之后，现在您必须通过VS在代码中开始.做这些
1.右键单击您的项目，然后选择添加新项目"
2.从显示的添加新项"窗口中选择数据集"
3.在VS将新数据集添加到您的项目后，显示数据集设计器"窗口
3.如果在您的Vs的左侧显示"Server Explorer"，那就很好；否则，从VS的MenuBar中选择视图，然后选择服务器资源管理器"
4.在服务器资源管理器"中，您添加的所有服务器和数据库都显示在树状视图中
5.右键单击服务器资源管理器"中的数据连接"，然后选择添加连接".显示``添加连接''窗口
6.选择您的数据库(SQL或ACCESS)，然后单击确定"按钮.该数据库已添加到服务器资源管理器"的数据连接"中
7.现在扩展新添加的连接，然后从"Tables"文件夹中选择要在项目中处理它们的所有表，然后将它们拖放到"DataSet Designer"的表面上.

现在可以使用您的DataSet了，现在该编码了.

1.在您的表单中添加一个"BindingSource"，然后将其Datasource设置为您生成的Dataset，然后将其DataMember设置为所需的DataSet表.
2. VS自动将一个DataSet和一个TableAdapter添加到您的表单中，并在表单的事件加载中添加一行
3.如果要使用DataGridView来显示和编辑数据，则将DataGridView添加到表单中，并将其DataSource设置为BindingSource，然后您就可以添加几秒钟了
3.如果运行项目，则所有数据都将在DataGridView中检索并显示，但是如果添加，编辑或删除数据，则对数据库Data无效.
4.要将数据保存回数据库，您必须执行此操作
每次编辑，添加或删除数据后想要将数据保存回数据库时，只需执行此操作
调用VS自动向您的表单添加的TableAdapter的方法"Update()"，例如this.TableAdapter1.Update(this.dataSet1);

试试看，如果您有任何问题，如果我在线，我可以为您提供帮助
祝您好运

Hello Dear Again ;)
OK , Now i explain how to do it using DataSet
the easiest way to work with DataSet is using ''DataSet Designer'' . because this tool of VS generate A lot of class and Code which cause very Easy Editing,Deleting,Insering of data To DataBase.
First Create Tables which you need in Database(SQL or ACCESS).and create relationship between them if needed. After Your work is finished in Database now you must begin in Code via VS. Do these
1. Rigth Click on your project and Select ''Add New item''
2. from window ''Add New Item'' which is displayed select ''DataSet''
3. After VS Add New DataSet to your project , window ''DataSet Designer'' displayes
3. If ''Server Explorer'' is displays in left side of your Vs , it is good ; otherwise from MenuBar of VS select view then select ''Server Explorer''
4. in ''Server Explorer'' All servers and databases which you have added is shown in a treeview
5. right click on ''Data Connection'' in ''Server Explorer'' and select ''Add Connection'' . window ''Add Connection'' displayes
6. select your database (SQL or ACCESS) and click ''OK'' Button . that database is added to ''Data Connection'' of ''Server Explorer''
7. now expand new added connection, and from ''Tables'' folder select all of your tables which you want to work on them in your project , and Drag and Drop them on surface of ''DataSet Designer'' .

your DataSet is ready to use , now , it is time of coding

1. in your form add a ''BindingSource'' and first sets its Datasource to Dataset which you generate,then set its DataMember to table of DataSet which you want.
2. VS automaticely adds a DataSet and a TableAdapter to your form and adds a line of in event load of your form
3. if you want to work DataGridView to show and edit data adds a DataGridView to your forms and sets its DataSource to BindingSource which you added some seconds a go
3.if you run your project all data are retrieves and displays in DataGridView , but if you add , edit or delete data , it has no effect in database Data
4. to save data back to database you must do this
each time after editing , adding or deleting data when you want to save data back to database just do this
call method ''Update()'' of TableAdapter which VS automaticely added yo your form , for examle this.TableAdapter1.Update(this.dataSet1);

try it,if you have any problem , I can help you if I am online
Good Luck

您最好的选择是使用交易"，就像这样

Your best bet would be to use a Transaction, something like this

using (SqlConnection con = new SqlConnection("yourConnectionString"))
            {
                //Declare new transaction
                SqlTransaction trans = new SqlTransaction();
                con.Open();
                //Set Transaction to current connection object
                trans = con.BeginTransaction();
                try
                {
                    foreach (object obj in InsertList)
                    {
                        SqlCommand cmd = new SqlCommand("your Insert Query", con);
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
                catch (SqlException sEx)
                {
                    //Handle Error
                    if (trans != null)
                    {
                        trans.Rollback();
                    }
                }
            }

希望对您有帮助

Hope this helps

这篇关于将数据批量插入数据库.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！