02/24/2004 Entry: "Reading database columns in .NET"
Posted by Maynard @ 05:20 PM MST

Reading database columns in .NET

There are some very frustrating things about .NET. Namely, its inability to directly reference values in a data grid. Let's say you wanted to sum the values in the 4th column of a grid that you are populating. You wouldn't say
for(int x=0; x<dataGrid.table[0].RowCount(); i++);
     x += dataGrid.table[0].row[x].column[y]
What would you do? Why, you'd read the latest from MM.net, of course!

Then you'd get back to work you lazy bums.

The following example reads data from a SQL database and loads the data into a grid. It then counts the values in the 2nd column (zero indexed) and stores it for later use (in my code, its a return variable used to set the value of a label).

The variables used are:
connectionString - used to identify the server and database to connect to.
selectCommandText - used to fill the data grid with data from the SQL database.
sqlConnection1 - an SqlConnection that is a member of the dialog.
sqlDataAdapter1 - an SqlDataAdapter that is a member of the dialog.
TotalHours - used to sum the hours in the data grid.
DS - a DataSet used to translate the data from the SQL data adapter to the data grid.
dataGrid - the name of the grid control on the dialog.
sqlDR - The key to the puzzle. An SqlDataReader used to read the data from the data grid.

---

string connectionString = "Data Source=executor; Initial Catalog=Timesheet; Integrated security=SSPI;";
string selectCommandText = "SELECT LastName as 'Last', BillableHours as 'Time' FROM Timesheet WHERE DayOfEntry >= '" + From.ToString("MM/dd/yy") + "' and DayOfEntry <= '" + To.ToString("MM/dd/yy") + "' and LastName = '" + lastName + "' and FirstName = '" + firstName + "'";
sqlConnection1 = new SqlConnection(connectionString);
sqlDataAdapter1 = new SqlDataAdapter(selectCommandText, sqlConnection1);
int TotalHours = 0;
try
{
sqlConnection1.Open();
DataSet DS = new DataSet();
sqlDataAdapter1.Fill(DS);
dataGrid.DataSource = DS.Tables[0].DefaultView;
DS.Dispose();

SqlDataReader sqlDR = sqlDataAdapter1.SelectCommand.ExecuteReader();
while ( sqlDR.Read() )
TotalHours += Convert.ToInt32(sqlDR.GetValue(1).ToString());

sqlConnection1.Close();
}
catch(SqlException sqlExcp)
{
MessageBox.Show(sqlExcp.Message, "SQL Error");
}
finally
{
sqlConnection1.Dispose();
sqlDataAdapter1.Dispose();
}

---

Yeah, you'd think you would just refer to the row and column. But C# and .NET are from Microsoft, so the easy way is the wrong way.

Replies: (1)

I have seen the light! From now on I will implement MM .NET over MS .NET for all my database needs! :op

(sorry. Couldn't resist.)

Posted by Cracker Jack @ 02/27/2004 09:23 PM MST

Add A New Comment

Name

E-Mail (optional)

Homepage (optional)

Comments