解析一个csv文件并将其上传到数据库中 [英] Parsing a csv file and uploading it in database

问题描述

我需要使用C#(4.5)解析一个csv文件(大约0.5 MB)，并将其上传到Sql Server数据库(2008)中.

请问实现此功能的最好，最有效的方法是什么?

1.使用C#(Entityframework ??)
2.使用SqlLoader吗?
3.还有其他方法吗?

谢谢

Hi,

I need to parse a csv file(about 0.5 MB) using C#(4.5) and upload it in Sql server database(2008).

What's the best and most efficient way of achieving this functionality please?

1.Using C#(Entityframework??)
2.Using SqlLoader?
3.Any other approach?

Thanks.

推荐答案

有很多方法(bcp， SqlBulkCopy 类:

There are a number of methods (bcp, BULK INSERT) you can use. For .NET you could use the SqlBulkCopy Class:

string SQLConnectionString = "Data Source=(local);" + "Initial Catalog=Northwind;" + "Integrated Security=SSPI";

using (SqlConnection SourceConnection = new SqlConnection(SQLConnectionString)) {
	SourceConnection.Open();

	// Perform an initial count on the destination table.
	SqlCommand CommandRowCount = new SqlCommand("SELECT COUNT(*) FROM dbo.Orders2;", SourceConnection);
	long CountStart = System.Convert.ToInt32(CommandRowCount.ExecuteScalar());
	Console.WriteLine("Starting row count = {0}", CountStart);

	// Get data from the source table as a TextDataReader.
	string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + "C:\\Documents and Settings\\...\\My Documents\\My Database\\Text" + ";" + "Extended Properties=\"Text;HDR=No\"";

	System.Data.OleDb.OleDbConnection TextConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);

	OleDbCommand TextCommand = new OleDbCommand("SELECT * FROM Orders#txt", TextConnection);
	TextConnection.Open();
	OleDbDataReader TextDataReader = TextCommand.ExecuteReader(CommandBehavior.SequentialAccess);

	using (SqlConnection DestinationConnection = new SqlConnection(SQLConnectionString)) {
		DestinationConnection.Open();

		// Set up the bulk copy object. 
		// The column positions in the source data reader 
		// match the column positions in the destination table, 
		// so there is no need to map columns.
		using (SqlBulkCopy BulkCopy = new SqlBulkCopy(DestinationConnection)) {
			BulkCopy.DestinationTableName = "dbo.Orders2";

			try {
				// Write from the source to the destination.
				BulkCopy.WriteToServer(TextDataReader);

			} catch (Exception ex) {
				Console.WriteLine(ex.Message);

			} finally {
				// Close the AccessDataReader. The SqlBulkCopy
				// object is automatically closed at the end
				// of the Using block.
				TextDataReader.Close();
			}
		}

		// Perform a final count on the destination table
		// to see how many rows were added.
		long CountEnd = System.Convert.ToInt32(CommandRowCount.ExecuteScalar());
		Console.WriteLine("Ending row count = {0}", CountEnd);
		Console.WriteLine("{0} rows were added.", CountEnd - CountStart);
	}
}

这篇关于解析一个csv文件并将其上传到数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！