使用动态列名从datagridview插入SQL [英] Insert from datagridview to SQL with dynamic column names

问题描述

已经将数据从excel电子表格导入到datagridview中，我想创建一个表(由Windows窗体中的用户确定)，并将其与列名一起插入到datagridview的新表中.

Having imported data from an excel spreadsheet into datagridview, I would like to create a table (determined by the user in Windows forms) and insert it with the column names into the new table from the datagridview.

用户也可以使用程序"将其他Excel工作表插入其他数据库中.

The user can use the "program" to insert other excel sheets into other databases as well.

如何动态添加未预定义且由excel电子表格中的列数定义的列名?

How do I add column names dynamically that is not predefined and is defined by how many columns is in the excel spreadsheet?

推荐答案

我的目标是创建一个导入应用程序，以将excel电子表格导入到SQL Server数据库中.我面临的问题是获取需要插入到数据库中的数字列并根据excel电子表格动态命名它们.

My goal here was to create an import application to import excel spreadsheets into a SQL server database. The problem that I faced is to get the number columns that I needed to insert into the database and to dynamically name them according to the excel spreadsheet.

该应用程序将在各种电子表格上使用，并且列名不得硬编码.这是我用来管理应用程序最后阶段的代码，在该阶段中，将电子表格中的所有数据插入到SQL中的数据库中.

The application will be used on various spreadsheets and the column names must not be hardcoded. This is the code I used that managed the last phase of the application where all the data from the spreadsheet is inserted into to the database in SQL.

    //Import Button
    private void button5_Click(object sender, EventArgs e)
    {
        string createColumns = "";
        string columns = "";
        string rows = "";
        var grid = (DataTable)dataGridView3.DataSource;
        for (int i = 0; i < grid.Columns.Count; i++)
        {
            if (i == grid.Columns.Count - 1)
            {
                createColumns += "[" + grid.Columns[i].ToString() + "] varchar(200) NULL";
                columns += "[" + grid.Columns[i].ToString() + "]";
            }
            else
            {
                columns += "[" + grid.Columns[i].ToString() + "],";
                createColumns += "[" + grid.Columns[i].ToString() + "] varchar(200) NULL,";
            }

        }
        string createTable = string.Format("Create table [{0}] ({1})", textBox1.Text, createColumns);
        rows = string.Format("Insert Into[{0}]({1})", textBox1.Text, columns);
        for (int i = 0; i < grid.Rows.Count; i++)
        {
            string row = "";

            for (int c = 0; c < grid.Columns.Count; c++)
            {

                if (c == grid.Columns.Count - 1)
                    row += "'" + grid.Rows[i][c].ToString() + "'";
                else
                    row += "'" + grid.Rows[i][c].ToString() + "', ";
            }

            if (i == grid.Rows.Count - 1)
                rows += string.Format(" ({0});", row);
            else
            {
                if (i == 0)
                {
                    rows += " Values";
                }
                rows += string.Format(" ({0}),", row);
            }
        }
        string s = "Integrated Security = SSPI;Persist Security Info = False;Data Source = " +
            ServerName.Text + "; Initial Catalog = " +
            Databases.Text;

        SqlConnection conn = new SqlConnection(s);

        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = createTable;
        cmd.Connection = conn;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        SqlCommand cmd2 = new SqlCommand(rows, conn);
        cmd2.CommandType = CommandType.Text;
        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();

        Application.Exit();
    }

这篇关于使用动态列名从datagridview插入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！