i need to import some bulk data in an asp.net application from txt/csv files
until now i used SqlBulkCopy, but now on iis7 with 64bit it doesn't work. there are many posts about jet not working with 64bit.
i read that iis can be configured to work in 32 bit mode, but id rather keep performance as good as possible via 64bit.
additionally, i never quite managed to make sqlbulkcopy use the extended properties (as you might see here https://stackoverflow.com/questions/4305502/net-sqlbulkcopy-with-unicode-chars or here http://forums.asp.net/p/1627034/4184689.aspx#4184689?SqlBulkCopy+DATAFILETYPE+widechar)
so the question is, is there another/better method to import bulk records to mssql via .net? something that works fine with 64bit and allows for customization as to unicode & filetypes?
thank you all for helping out!
thanks for your answers, fellows.
though it seems that the easiest way to go, is manually. so i started writing some code myself. and ill post it here if anyone might find some interest in it
it may not cover everything, but it is pretty flexible.
it does use some of my helper functions and such, not listed here, so if you're reading this and cannot figure out something, ill be glad to elaborate :-)
Sub ImportFile(ByVal FilePath As String, ByVal RecordType As ObjectType, Optional ByVal HasHeaders As Boolean = True, Optional ByVal RowDelimiter As String = ControlChars.Tab, Optional ByVal CellDelimiter As String = ControlChars.NewLine) Dim objReader = New StreamReader(FilePath) Dim strContents = objReader.ReadToEnd objReader.Close() Dim Columns = New List(Of String) Dim axName = ObjectType.Account.GetType.Assembly.FullName Dim objRecord = Activator.CreateInstance(axName, RecordType.ToString).Unwrap Dim dcx = DBContext() dcx.EditLogging = False Dim tbl = dcx.GetTable(objRecord.GetType) Dim TableColumns = New List(Of String) For Each p In objRecord.GetType.GetProperties If (p.PropertyType.BaseType IsNot Nothing AndAlso p.PropertyType.BaseType.Name = "ValueType") Or p.PropertyType Is "".GetType Then TableColumns.Add(p.Name) Next Dim Rows = Split(strContents, RowDelimiter).ToList If HasHeaders Then Columns = Rows(0).Split(CellDelimiter).ToList Rows.RemoveAt(0) 'check validity For Each clm In Columns If Not TableColumns.Contains(clm) Then Throw New ApplicationException(clm & " is not a valid column name.") Next Else Columns = TableColumns End If For Each row In Rows objRecord = Activator.CreateInstance(axName, RecordType.ToString).Unwrap Dim Cells = row.Split(CellDelimiter) For i = 0 To Cells.Count - 1 SetObjProperty(objRecord, Columns(i), Cells(i)) Next tbl.InsertOnSubmit(objRecord) Next dcx.SubmitChanges() End Sub
You can get x64 Jet, but not sure if SqlBulkCopy supports it,