如何更新从Excel工程导入的SQL服务器中的数据，并避免数据库中的重复数据和行？

问题描述

我上传了一张excel表，并在数据库中插入了数据。我在那张表中做了一些改动。我想再次使用这些新数据上传它。我想在数据库中更新该表。现在通过在现有行下面添加新行来插入新值。

I uploaded an excel sheet and that inserted data in database. I have done some changes in that sheet . I want to upload that again with those new datas. I want to update that table in database. Now new values are inserted by adding new rows below the existing rows.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


    public partial class LBMIS1New : System.Web.UI.Page
    {
    protected void Upload(object sender, EventArgs e)
    {
        //Upload and save the file
        string excelPath = Server.MapPath("~/Doc/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(excelPath);

        string conString = string.Empty;
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07 or higher
                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                break;

        }
        conString = string.Format(conString, excelPath);
        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:F]", excel_con))
            {
                oda.Fill(dtExcelData);
            }
            excel_con.Close();

            string consString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                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");
                    con.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    con.Close();
                }
            }

        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridview();
        }
    }
    protected void BindGridview()
    {
        string conString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        DataSet ds = new DataSet();
        using (SqlConnection con = new SqlConnection(conString))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from dbo.TestLDM", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            con.Close();
            gvDetails.DataSource = ds;
            gvDetails.DataBind();
        }
    }
    protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvDetails.PageIndex = e.NewPageIndex;
        BindGridview();
    }
}

我的尝试：



我应该使用商店程序吗？或者是否有任何其他进程来更新数据。

What I have tried:

Should I use store procedure for that? or Is there any other process to update data there.

推荐答案

string consString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
    con.Open();

    using (SqlCommand trunc = new SqlCommand("Truncate table dbo.TestLDM", con);
    {
        trunc.ExecuteNonQuery();
    }

    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");
        //con.Open();
        sqlBulkCopy.WriteToServer(dtExcelData);
        //con.Close();
    }
    con.Close();
}

这篇关于如何更新从Excel工程导入的SQL服务器中的数据，并避免数据库中的重复数据和行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！