In our discussions so far, I’ve shown how to create SSCE databases and load them with data. By now I’m sure you are wondering how to pull that data back out. Today I will show you two methods, first a way to bind the data to a control, then how to read through a tables rows programmatically.
First, create a form and put a button on, call it btnLoadGrid. Next add a data grid viewer control, I named mine dgvCoolPeople after the table we’ll be reading.
In the click event for the button, here’s the code you’ll need:
private void cmdLoadDataGrid_Click(object sender, EventArgs e)
{
SqlCeConnection cn = new SqlCeConnection(ConnectString());
if (cn.State==ConnectionState.Closed)
{
cn.Open();
}
try
{
// Set the command to use the table, not a query
SqlCeCommand cmd = new SqlCeCommand(“CoolPeople”, cn);
cmd.CommandType = CommandType.TableDirect;
// Get the table
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
// load the result set into the datasource
dgvCoolPeople.DataSource = rs;
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, “Oh Crap.”,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Oh Crap.”,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// Note, do not close the connection,
// if you do the grid won’t be able to display.
// For production code you probably want to make
// your result set (rs) a class level variable
}
First we open the database, as you have seen before. Next we set the command to a new SqlCeCommand, and pass in the name of the table, CoolPeople. Then we tell the command it’s type is TableDirect. Using this method we can directly access the table, which is very fast if we are doing a quick read through all rows.
Next we execute the command and return a SqlCeResultSet, which is then loaded into the data source for the dgvCoolPeople grid viewer control.
The one important thing to note is NOT to close the connection, otherwise it will also close your grid. Normally I would keep my connection at the class level instead of in a method.
And that’s all there is to it, I should mention if you want your grid updateable make sure to use ResultSetOptions.Scrollable|ResultSetOptions.Updatable in the ExecuteResultSet method.
Now that you’ve seen how to bind your control, let’s look at what it takes to step through the rows programmatically. Go back to your form and add another button, call it btnReadRecords. Also add a textbox named txtName. Finally if you are using your code from before, you already have a label named lblResult, if not go ahead and add it as well.
private void btnReadRecords_Click(object sender, EventArgs e)
{
SqlCeConnection cn = new SqlCeConnection(ConnectString());
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
// Build the sql query. If this was real life,
// I’d use a parameter for the where bit
// to avoid SQL Injection attacks.
string sql = “select LastName, FirstName from CoolPeople “;
if (txtName.Text.Length > 0)
{
sql += “where LastName like ‘” + txtName.Text + “%’ “;
}
try
{
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
// if you don’t set the result set to
// scrollable HasRows does not work
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
// If you need to be able to update the result set, instead use:
// SqlCeResultSet rs = cmd.ExecuteResultSet(
// ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
if (rs.HasRows)
{
// Use the get ordinal function so you don’t
// have to worry about remembering what
// order your SQL put the field names in.
int ordLastName = rs.GetOrdinal(“LastName”);
int ordFirstname = rs.GetOrdinal(“FirstName”);
// Hold the output
StringBuilder output = new StringBuilder();
// Read the first record and get it’s data
rs.ReadFirst();
output.AppendLine(rs.GetString(ordFirstname)
+ ” “ + rs.GetString(ordLastName));
// Now read thru the rest of the records.
// When there’s no more data, .Read returns false.
while (rs.Read())
{
output.AppendLine(rs.GetString(ordFirstname)
+ ” “ + rs.GetString(ordLastName));
}
// Set the output in the label
lblResults.Text = output.ToString();
}
else
{
lblResults.Text = “No Rows Found.”;
}
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, “Oh Crap.”,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Oh Crap.”,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Don’t need it anymore so we’ll be good and close it.
// in a ‘real life’ situation
// cn would likely be class level
cn.Close();
}
}
Again we open the connection, then setup our SQL statement. This time I’ve constructed a simple select. If the user enters a letter for a name, I’ve added code for an optional where clause to limit the number of rows returned.
Like with the grid, we need to create a command that we can execute. This time we’ll pass in the SQL statement, and indicate that it is a SQL Statement by setting the command type to text.
Let me take a short side trip, if you typed in the code, when you hit the period after “CommandType”, you should have noticed 3 options.. The first two we have discussed, TableDirect and Text. You’ll also notice there’s a choice for “StoredProcedure”.
If you read the previous articles, you’ll probably be scratching your head as I’ve already said SSCE does not support stored procedures. So why is this option in the list?
Ya got me. My guess is they are sharing the intellisense with another library, and didn’t or couldn’t remove it. Either way, you should ignore it. If you try to use it all you’ll do is generate a run time error.
Back to the code, you see the next thing that is done is a check for HasRows. I need to emphasize something very important: HasRows only works when the Scrollable option is set in the ExecuteResultSet method! I can’t tell you why, I can just tell you to make sure to use a scrollable option or else you’ll have no end of headaches.
So if we have rows, we obviously want to process them. To retrieve column data from a SSCE row, the SqlCeResultSet object has a variety of GetType methods, where Type is such things as String or Int. In order to make it work, you pass the GetType methods what they call an ordinal value, which is nothing more than an integer that indicates the column number you want to retrieve.
I, for one don’t want to have to keep up with which column is which number, further I want the flexibility to change my column order or add new columns without worrying about a lot of code refactoring. That’s where the GetOrdinal method comes into play.
Simply call GetOrdinal and pass in a string with the name of the column, and SSCE will tell you what column number it’s in. Because I use these several times I took these and stored them in int variables. Now I’m free to go change my SQL all I want and don’t have to worry about breaking the rest of my code. It’s a technique I highly advise you to follow.
OK, so we know which column goes where, from here it’s pretty simple. Use the ReadFirst method to move to the first row, then let’s grab it’s data using the GetString methods.
Next we enter a while loop, the Read method will move us to the next record, and return false when there are no more records (thus exiting the loop).
And finally we copy the output we’ve been building into the label control. In this case I have no more need for the connection, so unlike in the grid example I can go ahead and close this connection variable.
These two techniques can be interchanged, for example I could have used a table direct to load my label, or a text type command to load the grid view. But these two basic techniques should give you all the functionality to complete your toolset for working with SSCE databases.
Arcane Code 
What’s wrong?
while (data.Read()) {
int city= data.GetOrdinal(“km_city”);
int road = data.GetOrdinal(“km_road”);
MessageBox.Show(“Ciudad:” + data.GetInt32(city));
MessageBox.Show(“Carretera:” + data.GetInt32(road));
}
Error: “when casting from a number, the value must be a number less than infinity”;
because massageBOxIcon.Error is not valued in mobile device.
just use this
MessageBox.Show(sqlexception.Message);