How to prevent duplicate insertion in sqlbulkcopy operation

i need to import multiple excel file sheet into Sql Server Database. i implement an ASP.Net Application to allow user upload those excel file and read all the data into the database. However i had found an issue that the sqlbulkcopy can quickly copy and import all the data into sql server. However the sqlbulkcopy is a straight copy. there is no option to detect the duplicate record or duplicate import during the process.

though we can enforce the duplciation detection in the sqlbulkcopy action. we still are able to prevent the duplicate records insertion in the table level.. we only create a unique table index for the target table then the index will filter all those duplciate record out during the quick imported by sqlbulkcopy.



here is a sample sinppet of sql code.

CREATE UNIQUE NONCLUSTERED INDEX [MyIndexName] ON [dbo].[MyTable]
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC,
    [Column4] ASC,
   )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


the criteria to create a UNIQUE NONCLUSTERED INDEX is to select all the columns that can unqiuely identify the record in the target imported table

Connection String for Excel and CSV using OLEDB connection

Connection string to read Excel File

Data source is the physical path of Excel file.

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\YourFile.xlsx; 

Extended Properties=""Excel 12.0;HDR=NO;"";

HDR is  option to indicate that excle file contain column header or not.

Command Text : Select * from [YourSheetName$]

Connection string to read CSV File 

data source is the folder that csv file stored.

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Temp
; Extended Properties=""text;HDR=NO;FMT=Delimited;IMEX=1;"

Command Text : Select * from [YourCSVFIleName]

Data type mismatch in criteria expression in Excel File Search in OLEDB conection

when i try to use a where cause to filter empty rows in Excel file query

if the column data type is string, then i can use [ColumnName] <>' ' to filter all empty rows.

however the column fill with numeric value, then you will receive this error message "Data type mismatch in criteria expression"

the trick to solve this issue is that you can use Is Not NULL to filter all empty rows when the excel file is read.

Here is the sample Query

Select [Column1],[Column2],[Column3],[Column4],[Column5],[Column6] from [Table$] where [Column1] is Not NULL

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

when you import excel sheet data into sql server. you might encounter this error "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine"

the main reason of this cause is that the your application is running in 64 bit machine.


there are two approach to solve this issue.

1. change your visual studio build platform target to X86 and deploy your applicaiton again.




























2. change Enable 32 bit applicaiton property to be true in the application pool advance setting section.

Alter Column Default value in SQL Server 2008

since i use the bulkcopy to import data from excel sheet to sql server 2008. there is no such date added column

in the excel sheet to match the dateadded column in sql server, i decide to update the column dateadded to default value GetDate() to have the current date.

in google there are lots of suggestion... even the MSDN sample is not working

finally i found a working example http://sqlserverplanet.com/ddl/add-column-default-value/

here is the sql syntax to update existing column with default value.

ALTER TABLE [dbo].MyTable ADD  DEFAULT (getdate()) FOR DateAdded

Reset Visual Studio 2008 Settings

after i upgraded my VS2010 to SP1, i found that it caused the conflict to my VS2008 Setting.

when i try to add a Web Service Reference to the Project. a warming windows poping up and prevent me

to add a new web service.

"the Web Service Enumeration Component is Missing, please re-install VS2008 Etc.;..........


after I search on the MSDN, here is the simple solution to fix the this issue.


go to VS2008 Folder in the program menu, select VS2008 CMD, right click on it and select run as an administrator (this is very important, only the administrator can modify the setting in Windows 7 OS)

in CMD execute this line devenv.exe ResetSkipPkgs ( to load all skip tags)  or  devenv.exe ResetSettings (reset the VS2008 back to Default setting).

i hope this can save you a day....

Upload File to AS400 Server with C#

if you implement a ftp upload feature to send file to AS400 via FTP.

here are couple of things necessary to make the program working

the format of address much be separated with double Quote between host address, Library and destination file.

 Dim fi As New FileInfo(localFileName)
 Dim ub As UriBuilder = New UriBuilder
 ub.Host ="HostAddress/"
 ub.Scheme = URI.UriSchemeFtp
 ub.Path = String.Format("{0}/%2F{1}.file", "/QSYS.LIB/%2FFTPTEMP.LIB",

 fileName.Replace(System.IO.Path.GetExtension(ftpUpload.PostedFile.FileName), "")) 

QSYS.LIB is the default system library.

FTPTEMP.LIB is the destination upload folder.