Loading Data into a DataReader and Looping Through Records OLEDB / C#

Just a quick example of loading data into a data reader using C#. My data source is MS SQL Server in this case, although you would just need the correct OLEDB connection string to use any other data source.

//database connection string & SQL Statement to return SERVICE_LEVEL_ID and DEFINITION from a table called property.SERVICE_LEVEL
string dbConnectionString = @"Provider=SQLNCLI10;Server=(local)\SQLEXPRESS;Database=XXXXX;Uid=XXXXXX;Pwd=XXXXXXXXX;";

//Create and open connection string
OleDbConnection dbConnection = new OleDbConnection(dbConnectionString);

//create a command object using the sql and the connection object
OleDbCommand dbCommand = new OleDbCommand(dbSQL, dbConnection);
dbCommand.CommandType = CommandType.Text;

//excute the command object and write the output to the datareader dr
OleDbDataReader dr = dbCommand.ExecuteReader();

//loop through all the records in the datareader
while (dr.Read())
    //write the DEFINITION field out to a message box

Couple of things to notice in this code are the While(dr.Read()) line. Not only does dr.Read() get the next record in the data reader, it also returns true if a record is returned or false if one isn’t. So it effectively checks for EOF (end of file) at the same time. Also the dr[“DEFINITION”].ToString() could be replaced with an index based call of dr.GetString(1). NB column indexes start at 0

This entry was posted in Programming and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s