在使用C#将Excel上载到SQL Server时自动添加上载人(用户名或名称) [英] Adding uploaded by (user name or name) Automatically while uploading the Excel into SQL Server in C#

问题描述

我想将Excel数据上传到SQL Server中，并使用(通过会话方法选择)进行上传

I want to upload Excel data into SQL Server with uploaded by (i.e which pick from session method)

我已经创建了表格名称，国家/地区，地址，上传者.

I have created table name, country, address, uploaded by.

通常情况下，上传工作正常，但我想在上传excel批量上传文件时按值添加上传的文件

Normally the upload is working fine but I want to add the uploaded by value while uploading the excel bulk upload

我不知道如何按值插入上传值，批量上传对我有帮助

I don't know how to insert the uploaded by value in bulk upload some one help me

这是我的代码:

try
{
    myExcelConn.Open();

    // GET DATA FROM EXCEL SHEET.
    OleDbCommand objOleDB = new OleDbCommand("SELECT * FROM [Sheet1$]", myExcelConn);

    // READ THE DATA EXTRACTED FROM THE EXCEL FILE.
    OleDbDataReader objBulkReader = null;

    objBulkReader = objOleDB.ExecuteReader();

    // SET THE CONNECTION STRING.
    string sCon = sqlconn;

    using (SqlConnection con = new SqlConnection(sCon))
    {
        con.Open();
        //SqlConnection p = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\jetback\source\repos\Projectstudent\webdown_up_load\App_Data\upload.mdf;Integrated Security=True");

        // FINALLY, LOAD DATA INTO THE DATABASE TABLE.
        oSqlBulk = new SqlBulkCopy(con);
        oSqlBulk.DestinationTableName = "Table3"; // TABLE NAME.

        oSqlBulk.ColumnMappings.Add("name", "name");
        oSqlBulk.ColumnMappings.Add("address", "address");
        oSqlBulk.ColumnMappings.Add("country", "country");
        oSqlBulk.WriteToServer(objBulkReader);
    }

    lblConfirm.Text = "DATA IMPORTED SUCCESSFULLY.";
    lblConfirm.Attributes.Add("style", "color:green");
}
catch (Exception ex)
{
    lblConfirm.Text = ex.Message;
    lblConfirm.Attributes.Add("style", "color:red");
}
finally
{
    // CLEAR.
    oSqlBulk.Close();
    oSqlBulk = null;
    myExcelConn.Close();
    myExcelConn = null;
}

推荐答案

如果您想在yr dataSet中已经有数据时添加更多col，最好使用DataTable:

It's better to use DataTable if u wanna add more col when there's already data in yr dataSet:

var results = new DataTable();
using(var myExcelConn = new OleDbConnection(excCnnStr))
{
    using (var cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", myExcelConn))
    {
        myExcelConn.Open();

        var adapter = new OleDbDataAdapter(cmd);
        adapter.Fill(results);
    }
}

//add new col
results.Columns.Add("uploadedBy", typeof(System.Int32));
foreach (DataRow row in results.Rows)
{
    row["uploadedBy"] = loggedInUserId; // set uploader
}

using (var con = new SqlConnection(sCon))
{
    con.Open();
    using (var oSqlBulk = new SqlBulkCopy(con))
    {
        oSqlBulk.DestinationTableName = "Table3";

        oSqlBulk.ColumnMappings.Add("name", "name");
        oSqlBulk.ColumnMappings.Add("address", "address");
        oSqlBulk.ColumnMappings.Add("country", "country");
        oSqlBulk.ColumnMappings.Add("uploadedBy", "uploadedBy");
        oSqlBulk.WriteToServer(results);
    }
} 

这篇关于在使用C#将Excel上载到SQL Server时自动添加上载人(用户名或名称)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！