ASP.NET 4.0 c# code exporting gridview data to mysql database

I am developing a browser based application based on ASP.NET 4.0 c# code. I populated a gridview from CSV file using the code
described at
http://forums.asp.net/t/1705264.aspx?Import+data+from+CSV+to+GridView
Now I need to export the all the rows to MySQL database table assuming that the database table has all the fields defined as per the column names of the grid
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
Hi,

Once you get the data into your DataTable, you can use SqlBulkCopy class and insert multiple rows to SQL database.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy%28v=vs.110%29.aspx
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
Does it work even for MySQL database as opposed to SQL server database?
Lokesh B RDeveloperCommented:
Hi,

It will work only with MS SQL database.

You can use FOR loop with datatable and insert each record to the MYSQL database with INSERT statement.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
Usually I do the way you suggested with standard gridview in which columns defined as template fields. For example:
protected void btn_exportMPDB_Click(object sender, EventArgs e)
    {
        foreach (GridViewRow gvr in gridgrncsv.Rows)
        {
            // capture each field value and for the required fields and then insert into MySQL database
        }
    }
please see the attached cs file.
In the present case I do not know how to do it with datatable. Can you give an example. Should I identify the field names by index or by their actual names defined in the source CSV file. In my case, I need to export only few fields (6 or 7) from about 20 fields in the source.
grnimportCSV.aspx.cs
Lokesh B RDeveloperCommented:
Hi,

try this code for inserting.

protected void btn_exportMPDB_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable dt = (DataTable)ReadToEnd(fu_ImportCSV.PostedFile.FileName);
                if (dt != null && dt.Rows.Count > 0)
                {
                    string cs = @"server=localhost;userid=user12;password=34klq*;database=mydb";
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        using (MySqlConnection con = new MySqlConnection(cs))
                        {
                            string query = "INSERT INTO TableName(Id,Name,Age,Salary) VALUES(@Id,@Name,@Age,@Salary);";
                            MySqlCommand cmd = new MySqlCommand(query, con);

                            cmd.Parameters.Add("@Id", MySqlDbType.Int16).Value = dt.Rows[i]["Id"].ToString();
                            cmd.Parameters.Add("@Name", MySqlDbType.VarChar).Value = dt.Rows[i]["Name"].ToString();
                            cmd.Parameters.Add("@Age", MySqlDbType.Int16).Value = dt.Rows[i]["Age"].ToString();
                            cmd.Parameters.Add("@Salary", MySqlDbType.Decimal).Value = dt.Rows[i]["Salary"].ToString();
                            con.Open();
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
Brilliant solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.