SQL与Excel数据一起附上“User.Identity.Name”导入到SQL Server数据库
问题描述:
我使用VS2005 C#和SQL Server 2005SQL与Excel数据一起附上“User.Identity.Name”导入到SQL Server数据库
我有进口的数据在电子表格中插入一个Excel文件导入数据库。
但是,为了改进它,我想对每个导入实施某种“日志记录”。
对于用户导入的每一行数据,都会有一列存储登录用户的名称,即User.Identity.Name
。
下面是我的Excel导入代码的代码片段:
// Create Connection to Excel Workbook
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties=Excel 8.0;";
using (OleDbConnection connection = new OleDbConnection(connStr))
{
OleDbCommand command = new OleDbCommand("Select [User ID], [Username], [Age], [Address], [Date of Reg], [Membership Type], [Membership Expiry] FROM [sheet1$]", connection);
//would like to insert 'User.Identity.Name' into column 'userlog'
connection.Open();
Console.WriteLine("Connection Opened");
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=<DS>";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "UserData";
bulkCopy.WriteToServer(dr);
}
}
}
我不知道,因为SELECT
声明将我从中导入数据的sheet1$
唯一的变量我如何能做到这一点,所以我我不知道如何将另一个变量从另一个源包含到sql查询中。我需要有经验的建议。
谢谢你们提前提供任何帮助。
编辑:
我有.csv
类型另一个导入方法。由于SELECT
声明与我用于.xls
的声明不同,我应该如何更改它?下面的代码片段:
string dir = @"C:\...\UploadFiles";
string mycsv = DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;
// Save the Excel spreadsheet on server.
UserImport.SaveAs(dir + mycsv);
// Create Connection to Excel Workbook
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir +
";Extended Properties=Text;";
using (OleDbConnection ExcelConnection = new OleDbConnection(connStr))
{
OleDbCommand ExcelCommand = new OleDbCommand(
"SELECT [User ID], [Username], [Age], [Address],
[Date of Reg], [Membership Type], [Membership Expiry]
FROM " + mycsv, ExcelConnection);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
ExcelConnection.Open();
using (DbDataReader dr = ExcelCommand.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=<DS>";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "UserData";
bulkCopy.WriteToServer(dr);
//Response.Write("<script>alert('User Data imported');</script>");
}
}
}
谢谢
答
你可以尝试扩展你的select子句。
string selectStmt = string.Format("Select [User ID], [Username], [Age], [Address],
[Date of Reg], [Membership Type], [Membership Expiry],
'{0}' as [userlog] FROM [sheet1$]", User.Identity.Name);
OleDbCommand command = new OleDbCommand(selectStmt, connection);
根据您编辑的问题
string selectStmt = string.Format("SELECT [User ID], [Username], [Age], [Address],
[Date of Reg], [Membership Type], [Membership Expiry],'{0}' as [userlog]
FROM {1}", User.Identity.Name, mycsv);
Pilgerstorfer嗨更新,对不起,我在SQL查询中犯了一个错误。我想为它做一个'INSERT'语句。我应该如何改变查询?谢谢 – gymcode
我想出了更新查询。非常感谢,您的解决方案就是我正在寻找的。 – gymcode
很高兴我能帮忙! –