Not sure what is going on as I've used the OleDbDataReader before to pull data off an Excel spreadsheet, but suddenly my C# batch job is not stopping at blank rows via the while(_reader.Reader()) loop.
Here is the C# code I'm using. I'm simply trying to pull all the record off an Excel spreadsheet and loop through the data right now. For some reason, though, the _reader.Read() function comes back true no matter what.
private static OleDbDataReader _reader;
static void Main(string[] args)
{
string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= \\prdhilfs03\l&i-sales&mkt\WORKAREA\Agencyservices\Shared\AIC\Analysts_and_Reporting\Realignments\2014\MassUpdateTesting\Validate\ZipCodeTest.xlsx;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
string queryString = "SELECT * FROM [Query1$]";
Validation validation;
ZipCodeTerritory record;
bool flag = true;
try
{
Stopwatch watch = new Stopwatch();
watch.Start();
using (OleDbConnection connection = new OleDbConnection(connString))
{
//Set connection objects to pull from spreadsheet
OleDbCommand command = new OleDbCommand(queryString, connection);
connection.Open();
_reader = command.ExecuteReader();
//Instantiate validation object with zip and channel values
_allRecords = GetRecords();
validation = new Validation();
validation.SetLists(_allRecords);
while (_reader.Read())
{
record = new ZipCodeTerritory();
record.ChannelCode = _reader[0].ToString();
record.DrmTerrDesc = _reader[1].ToString();
record.IndDistrnId = _reader[2].ToString();
record.StateCode = _reader[3].ToString().Trim();
record.ZipCode = _reader[4].ToString().Trim();
record.EndDate = Convert.ToDateTime(_reader[5].ToString());
record.EffectiveDate = Convert.ToDateTime(_reader[6].ToString());
record.LastUpdateId = _reader[7].ToString();
record.ErrorCodes = _reader[8].ToString();
record.Status = _reader[9].ToString();
record.LastUpdateDate = DateTime.Now;
if (string.IsNullOrEmpty(record.LastUpdateId))
{
//Add to error list
_issues++;
_errorList.Add(record, "Missing last update Id");
continue;
}
if (_reader[10] != DBNull.Value)
{
record.Id = Convert.ToInt32(_reader[10]);
}
_errorMsg = validation.ValidateZipCode(record);
if (!string.IsNullOrWhiteSpace(_errorMsg))
{
_errorList.Add(record, _errorMsg);
continue;
}
else if (flag)
{
//Separate updates to appropriate list
SendToUpdates(record);
}
//Console.WriteLine(record.Id.ToString());
}
I found this question on stack overflow and tried to follow the selected answer by changing the queryString to the following. However, this simply throws an OleDbException when the OleDbCommand runs its .ExecuteReader() method. (the Message property of the exception reads "No value given for one or more required parameters")
SELECT * FROM [Query1$] WHERE NOT ([ChannelCode] = '' and [DrmTerrDec] = '' and [StateCode] = '' and [ZipCode] = '' and [EndDate] = '' and [EffectiveDate] = '')
EDIT
Just added all the code inside the while loop.
