使用c#导入数据到数据库

本文关键字：数据库 数据 导入 使用 | 更新日期: 2023-09-27 18:03:53

我有一个gridview与1000行，我也有一个导出&导入功能。例如，当我导出100行并对它们进行更改并再次导入它们时，则仅反映更改的100行。其余更改的行保持不变。

我使用下面的代码将数据导入数据库。现在，我如何在导入此工作模型时添加新行呢?

非常感谢您的指导。

代码: 

protected void btnImportXL_Click(object sender, EventArgs e)
{
    string strSqlTable = "##TempupdatePm";
    string sexcelconnectionstring = "";
    string strFileType = Path.GetExtension(FileUploadExcel.FileName).ToLower();
    // string path = FileUploadExcel.PostedFile.FileName;
    string query = "";
    string FileName = string.Empty;
    //GridView2.Visible = false;
    FileName = Path.GetFileName(FileUploadExcel.PostedFile.FileName);
    string Extension = Path.GetExtension(FileUploadExcel.PostedFile.FileName);
    string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
    //  string path = FileName;
    //Get file name of selected file     
    string path = Path.GetFileName(Server.MapPath(FileUploadExcel.FileName));
    System.IO.File.Delete(Server.MapPath(FolderPath) + path);
    //Save selected file into server location         
    FileUploadExcel.SaveAs(Server.MapPath(FolderPath) + path);
    //Get file path     
    string filePath = Server.MapPath(FolderPath) + path;
    if (strFileType != String.Empty)
    {
        //Connection String to Excel Workbook
        if (strFileType.Trim() == ".xls")
        {
            sexcelconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='"Excel 8.0;HDR=Yes;IMEX=2'"";
        }
        else if (strFileType.Trim() == ".xlsx")
        {
            sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='"Excel 12.0;HDR=Yes;IMEX=2'"";
        }
        else
        {
            ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Please upload the correct file format')", true);
            return;
        }
        try
        {
            OleDbConnection conn = new OleDbConnection(sexcelconnectionstring);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetname = dt.Rows[0]["Table_Name"].ToString();
            query = "SELECT * FROM [" + sheetname + "]";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            oledbconn.Open();
            OleDbCommand oledbcmd = new OleDbCommand(query, oledbconn);
            OleDbDataReader dReader;
            dReader = oledbcmd.ExecuteReader();
            string create1 = "Create Table " + strSqlTable + "( [CODE] [varchar](10) NOT NULL,[Name] [varchar](150) NOT NULL,[Group] [varchar](10) NOT NULL,  [Team] [varchar](10) NOT NULL, [Size1] [varchar](10) NOT NULL, [Size2] [varchar](10) NOT NULL";
            SqlConnection sqlconn2 = new SqlConnection(strConnectionString);
            SqlCommand sqlcmd2 = new SqlCommand(create1, sqlconn2);
            sqlconn2.Open();
            sqlcmd2.ExecuteNonQuery();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlconn2);
            sqlBulk.DestinationTableName = strSqlTable;
            sqlBulk.WriteToServer(dReader);

            string update1 = "update o set o.Size1=t.Size1,O.Size2=t.Size2 from  Application as o inner join ##TempupdatePm as t on(o.CODE=t.CODE )and (o.Team=t.Team) and (o.Group=t.Group) where (o.CODE=t.CODE ) and (o.Group=t.Group)";
            SqlCommand sqlcmd1 = new SqlCommand(update1, sqlconn2);
            sqlcmd1.ExecuteNonQuery();
            string drop1 = "drop Table " + strSqlTable;
            SqlCommand sqlcmd3 = new SqlCommand(drop1, sqlconn2);
            sqlcmd3.ExecuteNonQuery();
            oledbconn.Close();
            conn.Close();
            conn.Dispose();
            sqlconn2.Close();
            ClientScript.RegisterStartupScript(Page.GetType(), "alert", "alert('Data saved successfully');window.location='ApplicationInfo.aspx';", true);
        }
        catch (Exception ex)
        {
        }
        //objDA.BindGrid(GridView1, "select * from " + strSqlTable);
    }
    else
    {
        ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Please select a file to import the data.')", true);
    }
}

使用c#导入数据到数据库

对sql查询执行类似的操作，以使用一个查询插入和更新。

INSERT INTO MyTable (field1, field2, fieldN)
VALUES ("value1","value2", "valueN")
ON DUPLICATE KEY UPDATE 
field1=VALUES(value1)
field2=VALUES(value2)
fieldN=VALUES(valueN)
//and so on.

你可以在更新之前写一个insert语句，比如

Insert in to table select CODE,Size1,Size2 from #temp_table where CODE not in (Select code from table )
相关文章：