Bulk insertion of data in to database.

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

You can create multiple quires separated by semicolon (;) and fire those in a single go. all the quires will get executed.

For example;

strSQL = "INSERT INTO [TableName] [Fields] VALUES (Values);INSERT INTO [TableName] [Fields] VALUES (Values);INSERT INTO [TableName] [Fields] VALUES (Values);...;"

-Amol

Hello Dear
this is so easy if you use LINQ to sql.
if you are not using that do these:
1: right click on you project in solution explorer and select 'Add New Item' in window 'Add New Item' which displayed choose 'Linq To Sql Class' and set its name DataClass.
2:in Dataclass Designer from Server Explore menu , choose your database and from tables of that database choose your table for example 'Person' . select table and drag and drop it on designer . Dataclass Designer Creates a class from that table.
3: Now , you must retrieve data from database and fill you DataGrid
for that you must do these :
* creates a new object from class which VS made for you (Dataclass )

DataClassDataContext DataClass = new DataClassDataContext("Connection String");

* fill you datagrid with data :

DataGridView1.DataSource = DataClass.Persons.Tolist();

* do each changes in your dataGrid and then do this to save changes in DataBase

DataClass.SubmitChanges();

that is a very easy way for your answer
you can do all these oprations with DataSet, too.
if you had any problem I can help you
Good Luck

Hi,
if you are looking for a method which will insert multiple records with one SQL statement, then you should go for UNION ALL option.

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

Personally I prefer using multiple inserts statements for each of the item, as it is a more natural way of inserting data into the database, but if single SQL statement is a requirement you can use the above example as a reference.
Regards

You can achieve the same by using SqlBulkCopy class. Create a data table with all the details and call WriteToServer method.

Please refer the msdn[^] for more details.