C# SqlBulkCopy miss a row

c# sqlbulkcopy

Question

I'm using SqlBulkCopy to import data from excel and from another database into 2 different SQL tables.

Everything goes good until I managed that every time a row is missing in the target tables, either the source from the excel or from the other database.

Here is the code snippet for importing data from excel:

 public void ImportDataFromExcel(string excelFilePath)
    {
        string ssqltable = "szip_IncomingAssetData";
        string myexceldataquery = "SELECT * FROM ["+ GetExcelSheetNames(excelFilePath)+"]";
        try
        {

            string sexcelconnectionstring = GetExcelConnectionString(excelFilePath);
            Logger.Log("Excel Connection String: " + sexcelconnectionstring, false);


            OpenDatabaseConnection(1, "ImportDataFromExcel");

            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);

            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlBulkCopy bulkcopy = new SqlBulkCopy(hpamConnection)
            {
                DestinationTableName = ssqltable
            };

            SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping(System.Configuration.ConfigurationManager.AppSettings["szip_AssetID"], "szip_IncomingAssetID");
            SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetName"], "szip_IncomingAssetName");
            SqlBulkCopyColumnMapping mapSerial = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetSerial"], "szip_IncomingAssetSerial");
            SqlBulkCopyColumnMapping mapRI = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetRI"], "szip_IncomingAssetRI");
            SqlBulkCopyColumnMapping mapModel = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetModel"],"szip_IncomingAssetModel");
            SqlBulkCopyColumnMapping mapVendor = new SqlBulkCopyColumnMapping(System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetVendor"],"szip_IncomingAssetVendor");
            SqlBulkCopyColumnMapping mapFRU = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetFirstRU"], "szip_IncomingAssetFirstRU");
            SqlBulkCopyColumnMapping mapLRU = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetLastRU"], "szip_IncomingAssetLastRU");
            SqlBulkCopyColumnMapping mapLocation = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetLocation"], "szip_IncomingAssetLocation");
            SqlBulkCopyColumnMapping mapRack = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetRack"], "szip_IncomingAssetRack");
            SqlBulkCopyColumnMapping mapStatus = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetStatus"], "szip_IncomingAssetStatus");
            SqlBulkCopyColumnMapping mapConfig = new SqlBulkCopyColumnMapping(System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetConfig"], "szip_IncomingAssetConfig");
            SqlBulkCopyColumnMapping mapIPDNS = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetIP_DNSname"], "szip_IncomingAssetIP_DNSname");
            SqlBulkCopyColumnMapping mapArea = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetArea"], "szip_IncomingAssetArea");
            SqlBulkCopyColumnMapping mapContact = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetContact"], "szip_IncomingAssetContact");
            SqlBulkCopyColumnMapping mapExtension = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetExtension"], "szip_IncomingAssetExtension");
            SqlBulkCopyColumnMapping mapHWType = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetHardwareType"], "szip_IncomingAssetHardwareType");
            SqlBulkCopyColumnMapping mapConnections = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetCurrentConnections"], "szip_IncomingAssetCurrentConnections");
            SqlBulkCopyColumnMapping mapMaxConnections = new SqlBulkCopyColumnMapping( System.Configuration.ConfigurationManager.AppSettings["szip_IncomingAssetMaxConnections"], "szip_IncomingAssetMaxConnections");

            bulkcopy.ColumnMappings.Add(mapID);
            bulkcopy.ColumnMappings.Add(mapName);
            bulkcopy.ColumnMappings.Add(mapSerial);
            bulkcopy.ColumnMappings.Add(mapRI);
            bulkcopy.ColumnMappings.Add(mapModel);
            bulkcopy.ColumnMappings.Add(mapVendor);
            bulkcopy.ColumnMappings.Add(mapFRU);
            bulkcopy.ColumnMappings.Add(mapLRU);
            bulkcopy.ColumnMappings.Add(mapLocation);
            bulkcopy.ColumnMappings.Add(mapRack);
            bulkcopy.ColumnMappings.Add(mapStatus);
            bulkcopy.ColumnMappings.Add(mapConfig);
            bulkcopy.ColumnMappings.Add(mapIPDNS);
            bulkcopy.ColumnMappings.Add(mapArea);
            bulkcopy.ColumnMappings.Add(mapContact);
            bulkcopy.ColumnMappings.Add(mapExtension);
            bulkcopy.ColumnMappings.Add(mapHWType);
            bulkcopy.ColumnMappings.Add(mapConnections);
            bulkcopy.ColumnMappings.Add(mapMaxConnections);

            while (dr.Read())
            {
                  bulkcopy.WriteToServer(dr);
            }
            dr.Close();
            oledbconn.Close();
            CloseDatabaseConnection(1, "ImportDataFromExcel");
            Logger.Log("Data Imported from Excel to Database", false);
        }
        catch (Exception e)
        {
           Logger.Log("Cannot Read Excel File: " + e.Message.ToString(), true); 

        }

connection string is as follows:

private string GetExcelConnectionString(string excelfile)
    {
        Dictionary<string, string> props = new Dictionary<string, string>
        {
            ["Provider"] = "Microsoft.ACE.OLEDB.12.0",
            ["Extended Properties"] = "'Excel 12.0 XML;HDR=YES'",
            ["Data Source"] = excelfile
        };


        StringBuilder sb = new StringBuilder();

        foreach (KeyValuePair<string, string> prop in props)
        {
            sb.Append(prop.Key);
            sb.Append('=');
            sb.Append(prop.Value);
            sb.Append(';');
        }

        return sb.ToString();
    }

In the case of the import from other database into my application database:

public void ImportSmartZoneAssetData()
    {

        SqlConnection hpamConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["hpamConnectionString"]);
        SqlConnection smartZoneConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["szConnectionString"]);

        string sqlCommand = "select i.pa_deviceid as DeviceID, " +
                            "i.pa_displayname as DeviceName, " +
                            "j.PA_MATERIALIZEDSTRPATH as [Location], " +
                            "k.PA_CONTAINERTYPEID as ContainerTypeID, " +
                            "l.PA_CONTAINERTYPEDESCRIPTION as ContainerType, " +
                            "k.PA_DISPLAYNAME as ContainerName, " +
                            "i.PA_CONTAINERPOSITION as FirstRU, " +
                            "(select pa_assetvalue from PA_ASSETLIST where PA_PARENTID = i.PA_DEVICEID and PA_ASSETATTRIBUTEID = 24) as SerialNumber, " +
                            "(select pa_assetvalue from PA_ASSETLIST where PA_PARENTID = i.PA_DEVICEID and PA_ASSETATTRIBUTEID = 25) as BarCode " +
                            "from pa_device i " +
                            "left join PA_LOCATION j on i.pa_locationid = j.PA_LOCATIONID " +
                            "left join PA_CONTAINER k on i.PA_CONTAINERID = k.PA_CONTAINERID " +
                            "left join PA_CONTAINERTYPE l on k.PA_CONTAINERTYPEID = l.PA_CONTAINERTYPEID";

        string ssqltable = "szip_SmartZoneAssetData";

        SqlBulkCopy bulkcopy = new SqlBulkCopy(hpamConnection)
        {
            DestinationTableName = ssqltable
        };
        SqlDataReader myReader = null;
        SqlCommand myCommand = new SqlCommand(sqlCommand, smartZoneConnection);
        hpamConnection.Open();
        smartZoneConnection.Open();
        myReader = myCommand.ExecuteReader();
        while (myReader.Read())
        {
            bulkcopy.WriteToServer(myReader);
        }
        Logger.Log("Imported Records from SmartZone: " + GetRowsCopied(bulkcopy), false);
        myReader.Close();
        hpamConnection.Close();
        smartZoneConnection.Close();
        Logger.Log("Data Imported from SmartZone to Database", false);
    }
}

I want to know if there is something wrong in the code and the reason why I always loose one and only one record in both cases.

/**************************

Got rid of the "while (myReader.Read())" on both cases and now it works perfect. New code is:

myReader = myCommand.ExecuteReader();
        try
        {
            bulkcopy.WriteToServer(myReader);
        }
        catch (Exception e)
        {
            Logger.Log("Cannot Import SmartZone Device Data: " + e.Message, true);
        }

Thanks :-)

Accepted Answer

As suggested, the while (myReader.Read()) was advancing one register.

I got rid of the "while (myReader.Read())" on both cases and now it works perfect. New code is:

myReader = myCommand.ExecuteReader();
    try
    {
        bulkcopy.WriteToServer(myReader);
    }
    catch (Exception e)
    {
        Logger.Log("Cannot Import SmartZone Device Data: " + e.Message, true);
    }

Thanks :-)




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why