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:

  • Why the maximum size of log file is 2147483648 KB?
  • How to Select execute 'dynamic sql' from stored procedure?
  • Multpilcation Aggregate in Sql Server
  • SQL group and sum
  • How to insert the results of sp_executesql into a table which to be created during the insert?
  • ASP.NET MVC Caching vary by authentication
  • <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.

  • ASPNETDB.mdf for MySQL
  • Update Each Exisiting Row of SQL Server Table Using Loop
  • Anybody know why SQL Server 2005 throws “'SQLOLEDB' failed with no error message available, result code: E_FAIL(0x80004005). ”?
  • Retrieving the selected checkbox values from a ValidationGroup
  • sql server query returning null
  • Using Query on SQL Server database to count records of some type and grouped by some foreign key
  • 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.