Save data from rows to database

Question

Refer the code below:

void loadInstallMentPattern(System.Collections.ArrayList pattern)
    {
        dataGridView1.Rows.Clear();

        for (int i = 0; i < pattern.Count; i++)
        {
            int c = dataGridView1.Rows.Add();
            dataGridView1.Rows[c].Cells["gvcSNo"].Value = (i + 1).ToString();
            dataGridView1.Rows[c].Cells["gvcDueDate"].Value = ((InstallmentPatternStruct)pattern[i]).DueDate;
            dataGridView1.Rows[c].Cells["gvcAmount"].Value = ((InstallmentPatternStruct)pattern[i]).PrincipalAmt;
            dataGridView1.Rows[c].Cells["gvcInterestAmt"].Value = ((InstallmentPatternStruct)pattern[i]).InterestAmt;

            dataGridView1.Rows[c].Cells["gvcDebitAmt"].Value = ((InstallmentPatternStruct)pattern[i]).DebitPrincipalAmt;
            dataGridView1.Rows[c].Cells["gvcEMI"].Value = ((InstallmentPatternStruct)pattern[i]).EMI;
        }
    }

I have pragmatically added a few rows to DataGridView which are required to be further send to database for persistence.

Currently I am sending the data by reading each row from grid and then sending it to db. This means if I've 500 rows in DataGridView, then I'll have to fire 500 Insert queries.

I was wondering is there any other way to send data to db(in bulk) in case where DataGRidView is not data bound.

I hope I am able to explain my problem clearly. Any help would be greatly appreciated.

Don't see a problem with 500 insert queries, should be done in less than a second anyway. Just make sure to use parameters. — Victor Zakharov, Mar 02 '13 at 14:16
500 is just an example, could be several 100 tuples too.. but anyways, thanks for the info. — Amit Mittal, Mar 02 '13 at 14:38

score 1 · Answer 1 · answered Mar 02 '13 at 05:19

1

There's probably a way to do it closer to how .NET objects let you do it, but there's always, as a fallback,

INSERT INTO Table ( ColumnA, ColumnB ) 
VALUES ( ValueA1, ValueB1 ), ( ValueB2, ValueB2 ), ... ( ValueAn, ValueBn)

answered Mar 02 '13 at 05:19

Andrew Stollak

71
1
6

Don't do this with data you don't trust!!! It's a *huge* security hole for SQL injection!!! – Andrew Stollak Mar 02 '13 at 05:46
It's always a trade-off in the real world, meaning you sometimes have to make a security sacrifice. I would use parameters, to make it harder for the attacker. – Victor Zakharov Mar 02 '13 at 14:15
I can't use this approach because I am using SQL Server 2005. And as far as I know, this methods works starting from SQL Server 2008. – Amit Mittal Mar 02 '13 at 14:36

score 0 · Accepted Answer · answered Mar 02 '13 at 11:29

Well, I've found the solution to the problem. I dont know why it did not came in my mind before, but sooner or later it came!!

I've manually created a DataTable from the List from which I was filling up the DataGridView. Then I used SQLBulkCopy to send the entire data in one shot to the server.

Here's the code of what I did:

  public static void saveAllotmentLeaseToDb(int allotmentId, System.Collections.Generic.List<LeasePatternStruct> arr)
    {

        DataTable dt = new DataTable();
        dt.Columns.Add("AllotmentID",Type.GetType("System.Int32"));
        dt.Columns.Add("LeaseNumber", Type.GetType( "System.Int32"));
        dt.Columns.Add("DueDate",Type.GetType("System.DateTime"));
        dt.Columns.Add("Amount",Type.GetType("System.Double"));
        dt.Columns.Add("Remarks",Type.GetType("System.String"));
        dt.Columns.Add("LeaseIncrementID",Type.GetType("System.Int32"));
        dt.Columns.Add("isPaid",Type.GetType("System.Boolean"));
        dt.Columns.Add("PaymentID", Type.GetType("System.Int32"));
        for (int i = 0; i < arr.Count; i++)
        {
            DataRow dr = dt.NewRow();
            dr["AllotmentID"] = allotmentId;
            dr["LeaseNumber"] = (i + 1).ToString();
            dr["DueDate"] = arr[i].DueDate;
            dr["Amount"] = arr[i].Amount;
            dr["Remarks"] = arr[i].Remarks;
            dr["LeaseIncrementID"] = DBNull.Value; ;
            dr["isPaid"] = false; ;
            dr["PaymentID"] = DBNull.Value; ;
            dt.Rows.Add(dr);

        }
        using (SqlConnection connection = dataHandler.getConnection())
        {
            connection.Open();

            //Open bulkcopy connection.
            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
            {
                //Set destination table name
                //to table previously created.
                bulkcopy.DestinationTableName = "LottaryAllotment_Lease_Details";


                    bulkcopy.WriteToServer(dt);


                connection.Close();
            }

        }

    }

A downside of this approach is error handling, which you will have trouble performing. With single Inserts, you have a rather granular control over what happens in your code. [Check this out](http://stackoverflow.com/a/1004629/897326). — Victor Zakharov, Mar 02 '13 at 14:18
Also consider the effort to create a DataTable. For large amounts of data (>100K records), it will be extremely slow. Otherwise, yeah, if you already have a DataTable, performance of SqlBulkCopy is unbeatable. — Victor Zakharov, Mar 02 '13 at 14:42

Save data from rows to database

2 Answers2