How to add key value from web.config file to database by uploading excel sheet?

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.

Here is my web.config:

  • Surprising performance differences: List.Constains,SortedList.ContainsKey,DataRowCollection.Contains,DataTable.Select, DataTable.FindBy
  • Sql query grouped by contigious foreign key values
  • SQL - Find duplicate values and remove in a field
  • Import image from Excel sheet into SQL Server table C#
  • How do I escape a single quote in SQL Server?
  • SELECT + SUM + LEFT JOIN
  • <appSettings>
    <add key="keyFinancialYr" value="2018-01-01" />
    
    <add key="keyFinancialQtr" value="1" />
    </appSettings>
    

    Code behind:

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

    Please help me resolve this issue.

  • How to round and format a decimal correctly?
  • SQL server and .NET memory constraints, allocations, and garbage collection
  • SELECT related articles based on keywords from single table
  • How to retrieve the value of a ListView item in the same row as a LinkButton
  • ASP.NET Entity Framework 6 HashSet or List for a collection?
  • Database Error: There is no row at position 0
  • One Solution collect form web for “How to add key value from web.config file to database by uploading excel sheet?”

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

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

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.