通过ASP.NET CSV到MySQL [英] CSV to MySQL via ASP.NET

问题描述

我发展，我想，以允许用户导入CSV文件，其中每行中的数据可能会插入或更新为1或2个表在MySQL数据库中一个ASP.Net网站。该CSV文件的范围可以从1到几千行和约为6或7列宽

I am developing an ASP.Net site where i want to allow the user to import a CSV file where the data in each row may insert or update to 1 or 2 tables in a MySQL database. The CSV files may range from 1 to a few thousand rows and are about 6 or 7 columns wide.

我想知道是否有可能做到这一点不用循环，虽然每一行和调用存储过程，同时还针对SQL注入保护因为这种方法似乎是它可能很慢？此外，我当发生这种情况这是怎么回事渲染我的网站不响应所有当前用户想知道？

I was wondering if it is possible to do this without looping though each row and calling a stored procedure while still protecting against SQL injection as this method seems like it maybe quite slow? Also i was wondering while this is happening is this going to render my site as unresponsive to all current users?

在此先感谢

推荐答案

是的，要做到这一点，最好的方法是导入与ASP.NET SqlBulkCopy的一个临时表。

Yes, the best way to do this is to import into a temp table with ASP.NET SqlBulkCopy.

然后使用TSQL复制到目标表。

Then use TSQL to copy to destination tables.

加载CSV到一个DataTable。

Load the csv into a DataTable.

下面是code你需要

使用连接作为SqlConnection的=新的SqlConnection（康涅狄格州）

Using connection As SqlConnection = New SqlConnection(conn)

        connection.Open()

        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, Nothing)

                bulkCopy.DestinationTableName = "TempTable"

            bulkCopy.BulkCopyTimeout = 60000

            bulkCopy.BatchSize = 0

            bulkCopy.WriteToServer(MyDataTable)

            bulkCopy.Close()

        End Using

        connection.Close()

        End Using

这篇关于通过ASP.NET CSV到MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！