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;";
string dbSQL = "SELECT SERVICE_LEVEL_ID, DEFINITION FROM property.SERVICE_LEVEL"; 

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

//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
    MessageBox.Show(dr["DEFINITION"].ToString());
}

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

Advertisements
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 )

Google+ photo

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

Connecting to %s