locked
Importing Data from Datagridview to SQL Server RRS feed

  • Question

  • Hi

    Importing Data from Datagridview to SQL Server, the data include data types such as Numeric,date, Money and varchar.

    so which C# code/class can i used to copy data from the datagridview to an sql server table. I used SQLBulkCopy but it messes with my data types.

    Wednesday, November 19, 2014 5:55 AM

Answers

  • Hi tmpmadula,

    >>so which C# code/class can i used to copy data from the datagridview to an sql server table.

    Please try the following code, I have tested this code, It works on my side.

     private void ImportToDataTable_Click(object sender, EventArgs e)
            {
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    str = @"INSERT INTO USERSTable (Id,UserName,Password,Type) VALUES ('" + dataGridView1.Rows[i].Cells["Id"].Value + "', '" + dataGridView1.Rows[i].Cells["UserName"].Value + "'," + dataGridView1.Rows[i].Cells["Password"].Value + ",'" + dataGridView1.Rows[i].Cells["Type"].Value + "');";
                    try
                    {
                        using (SqlConnection con = new SqlConnection(conString))
                        {
                            using (SqlCommand com = new SqlCommand(str, con))
                            {
                                con.Open();
                                com.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
    
                }
                label1.Text = "Records inserted successfully";
            }

    Have a nice day!

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Kristin Xie Friday, November 28, 2014 9:49 AM
    Thursday, November 20, 2014 3:15 AM

All replies

  • Hi tmpmadula,

    >>so which C# code/class can i used to copy data from the datagridview to an sql server table.

    Please try the following code, I have tested this code, It works on my side.

     private void ImportToDataTable_Click(object sender, EventArgs e)
            {
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    str = @"INSERT INTO USERSTable (Id,UserName,Password,Type) VALUES ('" + dataGridView1.Rows[i].Cells["Id"].Value + "', '" + dataGridView1.Rows[i].Cells["UserName"].Value + "'," + dataGridView1.Rows[i].Cells["Password"].Value + ",'" + dataGridView1.Rows[i].Cells["Type"].Value + "');";
                    try
                    {
                        using (SqlConnection con = new SqlConnection(conString))
                        {
                            using (SqlCommand com = new SqlCommand(str, con))
                            {
                                con.Open();
                                com.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
    
                }
                label1.Text = "Records inserted successfully";
            }

    Have a nice day!

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Kristin Xie Friday, November 28, 2014 9:49 AM
    Thursday, November 20, 2014 3:15 AM
  • Build your parameter with data type after get the data from your Datagridview.

    https://curah.microsoft.com/39543/crud-by-creating-sqldataadapter-when-you-cannot-use-entity-framework

    chanmm


    chanmm

    Thursday, November 20, 2014 6:05 AM
  • I think you mean, export from DataGridView to SQL Server, right.

    From DGV to SQL Server:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Configuration;
    using System.Data.SqlClient;
    
    namespace WindowsFormsApplication6
    {
        public partial class Form1 : Form
        {
    
    
            private DataTable table;
            private DAL dal;
            protected void Form_Load(object sender, EventArgs e)
    
            {
                dal = new DAL();
                table = dal.GetData();
                dataGridView1.DataSource = table;
            }
    
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button5_Click(object sender, EventArgs e)
            {
                dal.UpdateData(table);
            }
    
            class DAL //data access layer
            {
                string connString = @"Server=EXCEL-PC\EXCELDEVELOPER;Database=AdventureWorksLT2012;Trusted_Connection=True;";
                SqlDataAdapter da;
                SqlCommandBuilder builder;
                DataTable table;
                SqlConnection conn;
                public DataTable GetData()
                {
                    table = new DataTable("dataGridView1");
                    conn = new SqlConnection(connString);
                    da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand(@"SELECT * FROM [SalesLT].[Product]", conn);
                    builder = new SqlCommandBuilder(da);
                    da.Fill(table);
                    return table;
                }
                public void UpdateData(DataTable table)
                {
                    if (da != null && builder != null)
                    {
                        da.Update(table);
                    }
                }
            }
        }
    }

    Update . . . just in case you want it . . .

     

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    using System.Configuration;


    namespace WindowsFormsApplication2
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            public DataSet ds;
            public SqlConnection con;

    private void btnUpdate_Click(object sender, EventArgs e) { using (SqlConnection con = new SqlConnection("Server=EXCEL-PC\\EXCELDEVELOPER;Database=AdventureWorksLT2012;Trusted_Connection=True;")) { using (SqlCommand cmd = new SqlCommand("SELECT * FROM Courses", con)) { using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { { SqlCommandBuilder sqlcmd = new SqlCommandBuilder(da); DataSet ds = new System.Data.DataSet(); // remove this line da.Update(this.ds, "Courses"); } } } } }

    }

    }



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, December 12, 2014 7:54 PM
  • There are a couple more flavors of this, if you are interested.

            private void button6_Click(object sender, EventArgs e)
            
            {
                SqlConnection con = new System.Data.SqlClient.SqlConnection();
                con = new System.Data.SqlClient.SqlConnection();
                con.ConnectionString = "Server=EXCEL-PC\\EXCELDEVELOPER;Database=AdventureWorksLT2012;Trusted_Connection=True;";
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter();
    
                for (int i = 0; i <= dataGridView1.Rows.Count - 2; i++)
                {
    
                    String insertData = "INSERT INTO Import_List(Fname, Lname, Age) VALUES (@Fname, @Lname, @Age)";
                    SqlCommand cmd = new SqlCommand(insertData, con);
                    cmd.Parameters.AddWithValue("@Fname", dataGridView1.Rows[i].Cells[0].Value);
                    cmd.Parameters.AddWithValue("@Lname", dataGridView1.Rows[i].Cells[1].Value);
                    cmd.Parameters.AddWithValue("@Age", dataGridView1.Rows[i].Cells[2].Value);
                    da.InsertCommand = cmd;
                    cmd.ExecuteNonQuery();
                }
    
                con.Close();
            }
            private void button8_Click(object sender, EventArgs e)
            {
    
                //SqlConnection connection = new SqlConnection("Data Source=Excel-PC;Initial Catalog=Northwind.MDF;Trusted_Connection=True;");
                DataTable dt = (DataTable)dataGridView1.DataSource;
                string connection = "Data Source=Excel-PC;Initial Catalog=Northwind.MDF;Trusted_Connection=True;";
                using (var conn = new SqlConnection(connection))
                {
                    conn.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                    {
                        bulkCopy.ColumnMappings.Add(0, "Fname");
                        bulkCopy.ColumnMappings.Add(1, "Lname");
                        bulkCopy.ColumnMappings.Add(2, "Age");
    
                        bulkCopy.BatchSize = 10000;
                        bulkCopy.DestinationTableName = "Import_List";
                        bulkCopy.WriteToServer(dt.CreateDataReader());
                    }
                }
            
            }



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, December 12, 2014 7:55 PM