如何通过上传Excel工作表从Web.config文件向数据库添加键值?

问题描述

我上传了一个Excel工作表，并将数据插入到数据库中.有两列名称，分别是财务年度和财务季度.我想从web.config中为这两列插入数据.

I uploaded an excel sheet and inserted data in database. There are two columns names as financial year and financial quarter. I want to insert data from web.config for these two columns.

这是我的web.config:

Here is my web.config:

<appSettings>
<add key="keyFinancialYr" value="2018-01-01" />

<add key="keyFinancialQtr" value="1" />
</appSettings>

后面的代码:

 using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
        DataTable dtExcelData = new DataTable();

        //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
        dtExcelData.Columns.AddRange(new DataColumn[7] { new DataColumn("Id", typeof(int)),
              new DataColumn("Banks", typeof(string)),
               new DataColumn("Crop Loan", typeof(int)),
                new DataColumn("Water Resources", typeof(decimal)),
                 new DataColumn("Farm Mechanisation", typeof(int)),
                  new DataColumn("Plantation & Horticulture", typeof(decimal)),
            new DataColumn("Forestry & Wasteland Dev.", typeof(int))
             });

        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "A2:G]", excel_con))
        {
            oda.Fill(dtExcelData);
        }
        excel_con.Close();

        string consString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            SqlCommand com = new SqlCommand("Truncate Table dbo.TestLDM ", con);
            con.Open();
            bool Deleted = com.ExecuteNonQuery() > 0;

            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.TestLDM";

                //[OPTIONAL]: Map the Excel columns with that of the database table
                sqlBulkCopy.ColumnMappings.Add("Id", "LDM_LBSMI1ID");
                sqlBulkCopy.ColumnMappings.Add("Banks", "BankName");
                sqlBulkCopy.ColumnMappings.Add("Crop Loan", "PCropLoanNo");
                sqlBulkCopy.ColumnMappings.Add("Water Resources", "PCropLoanAmt");
                sqlBulkCopy.ColumnMappings.Add("Farm Mechanisation", "PTermLoanWaterRNo");
                sqlBulkCopy.ColumnMappings.Add("Plantation & Horticulture", "PTermLoanWaterRAmt");
                sqlBulkCopy.ColumnMappings.Add("Forestry & Wasteland Dev.", "PTermLoanFarmMechanisationNo");

                sqlBulkCopy.WriteToServer(dtExcelData);
                con.Close();
            }
        }

请帮助我解决此问题.

推荐答案

这将插入您的AppsetingValue的记录:

This will insert the record of your AppsetingValue:

 string consString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
       static  String AppSetting1=ConfigurationManager.AppSettings["keyFinancialYr"].ToString();
       static  String AppSetting2=ConfigurationManager.AppSettings["keyFinancialQtr"].ToString();
        String QueryStr = "insert into yourTable(Col1,Col2)values('" + AppSetting1 + "','" + AppSetting2 + "')";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            ExecuteQuery(consString,QueryStr);
        }

        public int ExecuteQuery(String connectionString,string query)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = query;
                    cmd.CommandType = CommandType.Text;
                    int result = cmd.ExecuteNonQuery();
                    return result;
                }
            }
        }

如果表列的keyFinancialQtr是整数，则可以将AppSetting2解析为int.

If your table column keyFinancialQtr is integer you can parse the AppSetting2 to int.

只需将col1，col2更改为您的真实列名.

Just change the col1,col2 as your real column name.

这篇关于如何通过上传Excel工作表从Web.config文件向数据库添加键值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！