CODEWITHSUNDEEP
c#.netwinformsdatagridview
Amit Mittal
Amit Mittal

Reputation: 1127

Save data from rows to database

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.

Upvotes: 3

Views: 291

Answers (2)

Amit Mittal
Amit Mittal

Reputation: 1127

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();
            }

        }

    }

Upvotes: 0

Andrew Stollak
Andrew Stollak

Reputation: 71

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)

Upvotes: 1

Related Questions