如何使用C＃中的import spec自动化csv文件来访问db [英] How to automate the csv file to access db using import spec in C#
本文介绍了如何使用C＃中的import spec自动化csv文件来访问db的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我是.Net技术的初学者。
i得到了一项任务即获取来自oracle的数据作为数据表，检查空值，将单引号替换为datatable列中的双引号并插入到访问数据库中。
i已编写代码但它是需要3小时才能加载。
以及最近插入几条记录后我得到错误操作必须使用可更新的查询但我使用插入查询，我没有写任何更新查询。
现在我的经理让我自动化csv文件，使用c＃中的import spec访问数据库。
因为import spec会自动处理null和单引号值。
怎么做，给我一个主意。
提前致谢。
我的尝试：
我之前的代码是：但是需要很长时间才能插入访问数据库。
for （ int i = 0 ;我< dtAEDetails.Rows.Count; i ++）
{
for （ int j = 0 ; j < dtAEDetails.Columns.Count; j ++）
{
sOffice = ' + dtAEDetails.Rows [i] [ 0 ] .ToString（）+ ';
sInsertValues = sOffice + ，;
sAe = ' + dtAEDetails.Rows [i] [ 1 ]。ToString（）+ ';
sInsertValues = sInsertValues + sAe + ，;
sDesc1 = ' + dtAEDetails.Rows [i] [ 7 ]。ToString（）。替换（ '， ''）+ < span class =code-string>'; // 将单引号替换为双引号
sInsertValues = sInsertValues + sDesc1 + ，;
if （！string.IsNullOrEmpty（dtAEDetails.Rows [i] [ 9 ]。ToString （）））
{
sPrinc = ' + dtAEDetails.Rows [i] [ 9 ]。ToString（）。替换（ '， ''）+ ';
sInsertValues = sInsertValues + sPrinc + ，;
}
else
{
sInsertValues = sInsertValues + null + ， ; // 检查空值
}
insertAEdetails.ParmValue = sInsertValues;
insertAEdetails.InsertIntoAccess（）; // 在访问数据库中插入此连接字符串。
}
}
public void InsertIntoAccess（）
{
string sQueryToRun = string .Empty;
// 设置数据库连接
OleDbConnection oleDbConn = new OleDbConnection（strAccessConn）;
尝试
{
// prep sqlManager
SqlManager sqlManager = SqlManagerUtil.GetSqlManager（sSqlXmlFile）;
// 从sql manager获取查询
SqlExecution执行= sqlManager.GetSqlExecution（sQueryName.ToString（））;
sQueryToRun = execution.SqlText;
sQueryToRun = sQueryToRun.Replace（ ：ValueString，sParmValue.ToString（））;
// 准备命令
OleDbCommand InsCmd = new OleDbCommand（sQueryToRun.ToString（），oleDbConn）;
oleDbConn.Open（）; // 打开连接
InsCmd.ExecuteNonQuery（）; // 执行查询
}
catch （Exception ex）
{
Logger.WriteMessage（ 错误在InsertIntoAccess）;
Logger.WriteMessage（ + ex.Message）;
throw ex;
}
最后
{
// < span class =code-comment>如果连接打开关闭它
if （oleDbConn.State == ConnectionState.Open）
{
oleDbConn.Close（）;
}
}
}
解决方案
我在MSDN上找到了这段代码： C＃选择.CSV文件，读入MS Access数据库 [ ^ ]
System.Data.OleDb.OleDbConnection AccessConnection = new System.Data.OleDb.OleDbConnection（Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C：\\Test Files \\\ \\ db1 XP.mdb）;
AccessConnection.Open（）;
System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand（SELECT * INTO [ImportTable] FROM [Text; FMT = Delimited; DATABASE = C：\\Documents和设置\\ ... \\我的文件\\我的数据库\\Text; HDR =否]。[x123456.csv]，AccessConnection）;
AccessCommand.ExecuteNonQuery（）;
AccessConnection.Close（）;
嗨瑞克，
感谢您的支持回应。 executiontenonquery（）函数抛出异常。
条件表达式中的数据类型不匹配。
我的csv文件包含单引号文本和空值。你能不能给我任何想法，早些时候我用来检查代码，这就是为什么花了很多时间，任何优化技术。
私有字符串strAccessConn1 =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ WebConfig.GetSetting（AccessDBFilePath.devdesktop）; //在部署之前需要更改
public DataTable InserttblDetailOPSData（string sInputPath，string sInFile）
{
string query = string.Empty;
string header =是;
DataTable dataTable = null;
string filePath = string.Empty;
string fileName = string.Empty;
sInputPath = System.IO.Path.GetDirectoryName（System.Reflection.Assembly.GetExecutingAssembly（）。Location）+ @\;
sInFile = WebConfig.GetSetting（InFileName）;
try
{
// csv文件目录
filePath = sInputPath;
// csv文件名
fileName = WebConfig.GetSetting（InFileName）;
query = @SELECT * FROM [+ fileName +];
using（OleDbConnection connection = new OleDbConnection（（@Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ filePath +;扩展属性= \Text; HDR =+ header +\）））
{
using（OleDbCommand command = new OleDbCommand（query，connection））
{
using（OleDbDataAdapter adapter = new OleDbDataAdapter（command） ））
{
dataTable = new DataTable（）;
dataTable.Locale = CultureInfo.CurrentCulture;
adapter.Fill（dataTable）;
try
{
//创建与Access DB的连接
OleDbConnection DBconn = new OleDbConnection（Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + WebConfig.GetSetting（AccessDBFilePath.devdesktop））;
OleDbCommand cmd = new OleDbCommand（）;
//设置cmd设置
cmd.Connection = DBconn;
cmd.CommandType = CommandType.Text;
//打开数据库连接
DBconn.Open（）;
//读取数据表中的每一行并将该记录插入DB
中（int i = 0; i< dataTable.Rows.Count; i ++）
{
cmd.CommandText =INSERT INTO tblDetail（[OFFICE]，[AE]，[SETDATE]，[ACCT]，[SHORT]，[BS]，[SHARES]，[DESC]，[PRICE]，[PRINC]，[严重]，[STAND]，[NET]，[新网]，[AECHG]，[BONUS]，[BT]，[取消]，[转码]，[预约]，[REALGR]，[ANNUITYNET]，[的recordId] ，[USERCODE1]，[USERCODE7]）+
VALUES（'+ dataTable.Rows [i] .ItemArray.GetValue（0）+'，'+ dataTable.Rows [i] .ItemArray。 GetValue（1）+'，'+ dataTable.Rows [i] .ItemArray.GetValue（2）+
'，'+ dataTable.Rows [i] .ItemArray.GetValue（3）+ '，'+ dataTable.Rows [i] .ItemArray.GetValue（4）+'，'+ dataTable.Rows [i] .ItemArray.GetValue（5）+
'，'+ dataTable.Rows [i] .ItemArray.GetValue（6）+'，'+ dataTable.Rows [i] .ItemArray.GetValue（7）+'，'+ dataTable.Rows [ i] .ItemArray.GetValue（8）+
'，'+ dataTable.Rows [i] .ItemArray.GetValue（9）+'，'+ dataTable.Rows [i] .ItemArray.GetValue （10）+'，'+ dataTable.Rows [i] .ItemArray.GetValue（11）+
'，'+ dataTable.Rows [i] .ItemArray.GetValue（12）+' ，'+ dataTable.Rows [i] .ItemArray.GetValue（13）+'，'+ dataTable.Rows [i] .ItemArray.GetValue（14）+
'，'+ dataTable。行[i] .ItemArray.GetValue（15）+'，'+ dataTable.Rows [i] .ItemArray.GetValue（16）+'，'+ dataTable.Rows [i] .ItemArray.GetValue（17 ）+
'，'+ dataTable.Rows [i] .ItemArray.GetValue（18）+'，'+ dataTable.Rows [i] .ItemArray.GetV alue（19）+'，'+ dataTable.Rows [i] .ItemArray.GetValue（20）+
'，'+ dataTable.Rows [i] .ItemArray.GetValue（21）+ '，'+ dataTable.Rows [i] .ItemArray.GetValue（23）+'，'+ dataTable.Rows [i] .ItemArray.GetValue（24）+
'，'+ dataTable .Rows [i] .ItemArray.GetValue（25）+'）;
cmd.ExecuteNonQuery（）;
}
//关闭DB.connection
DBconn.Close（）;
}
catch（Exception ex）
{
//sendEmail(ConfigurationManager.AppSettings[\"QuantumEmailFrom]，ConfigurationManager.AppSettings [QuantumEmailTo]，Quantum CSV Import To SMS数据库FAILED，ex.Message）;
}
}
}
}
//检查文件夹是否存在
if（Directory.Exists（sInputPath））
{
//删除所有文件夹内容并重新创建一个空文件夹
Directory.Delete（sInputPath，true）;
Directory.CreateDirectory（sInputPath）;
}
}
catch（Exception ex）
{
throw ex;
//sendEmail(ConfigurationManager.AppSettings[\"QuantumEmailFrom]，ConfigurationManager.AppSettings [QuantumEmailTo]，Quantum CSV导入到SMS数据库FAILED，ex.Message）;
}
返回dataTable;
}
检查我的旧答案，从csv导入具有特定分隔符等的数据：读取文本文件特定列 [ ^ ]
这可能有助于在MSDN上查找相关文章：如何读取文本文件数据并使用VB.NET在datagridview中显示 [ ^ ]
Hi,
I am beginner to .Net technologies.
i got a task i.e get the data from oracle as a datatable, check the null values,replace single quote into double quote in datatable columns and inserting into access database.
i have written code but it is taking 3 hrs time to load.
and also recently after inserting few records i got error "the operation must use an updatable query" but i am using insert query , i haven't written any update query.
Now my manager asked me to automate the csv file to access db using import spec in c#.
bec import spec automatically handles the null and single quote values.
How to do it , give me an idea.
Thanks in advance.
What I have tried:
My previous code is :but taking long time to insert into access db.
for (int i = 0; i < dtAEDetails.Rows.Count; i++)
{
for (int j = 0; j < dtAEDetails.Columns.Count; j++)
{
sOffice = "'" + dtAEDetails.Rows[i][0].ToString() + "'";
sInsertValues = sOffice + ",";
sAe = "'" + dtAEDetails.Rows[i][1].ToString() + "'";
sInsertValues = sInsertValues + sAe + ",";
sDesc1 = "'" + dtAEDetails.Rows[i][7].ToString().Replace ("'", "''") + "'";//replacing single quote into double quote
sInsertValues = sInsertValues + sDesc1 + ",";
if (!string.IsNullOrEmpty(dtAEDetails.Rows[i][9].ToString()))
{
sPrinc = "'" + dtAEDetails.Rows[i][9].ToString().Replace("'", "''") + "'";
sInsertValues = sInsertValues + sPrinc + ",";
}
else
{
sInsertValues = sInsertValues + "null" + ",";//checking null value
}
insertAEdetails.ParmValue = sInsertValues;
insertAEdetails.InsertIntoAccess();//inserting into access db this concatenated string.
}
}
public void InsertIntoAccess()
{
string sQueryToRun = string.Empty;
//set the database connection
OleDbConnection oleDbConn = new OleDbConnection(strAccessConn);
try
{
//prep sqlManager
SqlManager sqlManager = SqlManagerUtil.GetSqlManager(sSqlXmlFile);
//Get the query from sql manager
SqlExecution execution = sqlManager.GetSqlExecution(sQueryName.ToString());
sQueryToRun = execution.SqlText;
sQueryToRun = sQueryToRun.Replace(":ValueString", sParmValue.ToString());
//prep the command
OleDbCommand InsCmd = new OleDbCommand(sQueryToRun.ToString(), oleDbConn);
oleDbConn.Open(); //open the connection
InsCmd.ExecuteNonQuery(); //execute the query
}
catch (Exception ex)
{
Logger.WriteMessage(" Error in InsertIntoAccess");
Logger.WriteMessage(" " + ex.Message);
throw ex;
}
finally
{
// if connection is open close it
if (oleDbConn.State == ConnectionState.Open)
{
oleDbConn.Close();
}
}
} 解决方案
I found this piece of code on MSDN: C# Select .CSV File, Read Into MS Access Database[^]
System.Data.OleDb.OleDbConnection AccessConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test Files\\db1 XP.mdb"); AccessConnection.Open(); System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO [ImportTable] FROM [Text;FMT=Delimited;DATABASE=C:\\Documents and Settings\\...\\My Documents\\My Database\\Text;HDR=No].[x123456.csv]", AccessConnection); AccessCommand.ExecuteNonQuery(); AccessConnection.Close();
Hi Rick,
Thanks for your response . executenonquery() function throwing exception .
Datatype mismatch in criteria expression.
Bec my csv file contains single quote text and null values. Can you please give me any idea , earlier I use to check in code that's why it took lot of time, any optimization technique.,
private string strAccessConn1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + WebConfig.GetSetting("AccessDBFilePath.devdesktop");//NEED TO CHANGE BEFORE DEPLOYMENT public DataTable InserttblDetailOPSData(string sInputPath, string sInFile) { string query = string.Empty; string header = "Yes"; DataTable dataTable = null; string filePath = string.Empty; string fileName = string.Empty; sInputPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\"; sInFile = WebConfig.GetSetting("InFileName"); try { //csv file directory filePath = sInputPath; //csv file name fileName = WebConfig.GetSetting("InFileName"); query = @"SELECT * FROM [" + fileName + "]"; using (OleDbConnection connection = new OleDbConnection((@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Text;HDR=" + header + "\""))) { using (OleDbCommand command = new OleDbCommand(query, connection)) { using (OleDbDataAdapter adapter = new OleDbDataAdapter(command)) { dataTable = new DataTable(); dataTable.Locale = CultureInfo.CurrentCulture; adapter.Fill(dataTable); try { //create connection to Access DB OleDbConnection DBconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + WebConfig.GetSetting("AccessDBFilePath.devdesktop")); OleDbCommand cmd = new OleDbCommand(); //set cmd settings cmd.Connection = DBconn; cmd.CommandType = CommandType.Text; //open DB connection DBconn.Open(); //read each row in the Datatable and insert that record into the DB for (int i = 0; i < dataTable.Rows.Count; i++) { cmd.CommandText = "INSERT INTO tblDetail ([OFFICE],[AE],[SETDATE],[ACCT],[SHORT],[BS],[SHARES],[DESC],[PRICE],[PRINC],[GROSS],[STAND],[NET],[NEWNET],[AECHG],[BONUS],[BT],[CANCEL],[TRANSCODE],[ORDER],[REALGR],[ANNUITYNET],[RECORDID],[USERCODE1],[USERCODE7])" + " VALUES ('" + dataTable.Rows[i].ItemArray.GetValue(0) + "','" + dataTable.Rows[i].ItemArray.GetValue(1) + "','" + dataTable.Rows[i].ItemArray.GetValue(2) + "','" + dataTable.Rows[i].ItemArray.GetValue(3) + "','" + dataTable.Rows[i].ItemArray.GetValue(4) + "','" + dataTable.Rows[i].ItemArray.GetValue(5) + "','" + dataTable.Rows[i].ItemArray.GetValue(6) + "','" + dataTable.Rows[i].ItemArray.GetValue(7) + "','" + dataTable.Rows[i].ItemArray.GetValue(8) + "','" + dataTable.Rows[i].ItemArray.GetValue(9) + "','" + dataTable.Rows[i].ItemArray.GetValue(10) + "','" + dataTable.Rows[i].ItemArray.GetValue(11) + "','" + dataTable.Rows[i].ItemArray.GetValue(12) + "','" + dataTable.Rows[i].ItemArray.GetValue(13) + "','" + dataTable.Rows[i].ItemArray.GetValue(14) + "','" + dataTable.Rows[i].ItemArray.GetValue(15) + "','" + dataTable.Rows[i].ItemArray.GetValue(16) + "','" + dataTable.Rows[i].ItemArray.GetValue(17) + "','" + dataTable.Rows[i].ItemArray.GetValue(18) + "','" + dataTable.Rows[i].ItemArray.GetValue(19) + "','" + dataTable.Rows[i].ItemArray.GetValue(20) + "','" + dataTable.Rows[i].ItemArray.GetValue(21) + "','" + dataTable.Rows[i].ItemArray.GetValue(23) + "','" + dataTable.Rows[i].ItemArray.GetValue(24) + "','" + dataTable.Rows[i].ItemArray.GetValue(25) + "')"; cmd.ExecuteNonQuery(); } //close DB.connection DBconn.Close(); } catch (Exception ex) { //sendEmail(ConfigurationManager.AppSettings["QuantumEmailFrom"], ConfigurationManager.AppSettings["QuantumEmailTo"], "Quantum CSV Import To SMS Database FAILED", ex.Message); } } } } //checks folder exists if (Directory.Exists(sInputPath)) { //deletes all folder contents and recreates an empty folder Directory.Delete(sInputPath, true); Directory.CreateDirectory(sInputPath); } } catch (Exception ex) { throw ex; //sendEmail(ConfigurationManager.AppSettings["QuantumEmailFrom"], ConfigurationManager.AppSettings["QuantumEmailTo"], "Quantum CSV Import To SMS Database FAILED", ex.Message); } return dataTable; }
Check my old answer about importing data from csv with specific delimiter, etc.: Read Text File Specific Columns[^]
This might be helpful in finding related articles on MSDN: How to read text file data and display in datagridview with VB.NET[^]
这篇关于如何使用C＃中的import spec自动化csv文件来访问db的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
