Sample sqlceresultset code

[netcfadmirer]

Hi, anyone out there can help to create a sample database application
which can show one to many relationship and basic add, edit, delete
function using sqlceresultset for vs 2005 ?



Real need it, thanks in advanced.

 

[Ginny Caughey [MVP]]

netcfadmirer,

I don't know of a whole app to point you to that does what you want using
SqlCeResultSet, but here are the basics.

Create a database like this:

// Delete old file
File.Delete("Test.sdf");
// Create and open new file
SqlCeEngine engine = new SqlCeEngine("Data Source = Test.sdf");
engine.CreateDatabase();

Connect to it like this:

conn = new SqlCeConnection("Data Source = Test.sdf");
conn.Open();

Create a table like this:

// Create table with 3 fields
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE myTable (col1 INT, col2 MONEY, col3
NVARCHAR(200))";
cmd.ExecuteNonQuery();

Add a row to the table like this:

cmd.CommandText = "SELECT * FROM myTable";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable |
ResultSetOptions.Scrollable);
// Add a record
SqlCeUpdatableRecord rec = rs.CreateRecord();
// Insert values into the 3 fields
rec.SetInt32(0, 34);
rec.SetDecimal(1, (decimal)44.66);
rec.SetString(2, "Sample text");
rs.Insert(rec);

You'd need to do this for each table in your database. Changing data in the
current row in a SqlCeResultSet uses one of the Set... methods, depending on
the data type. To delete the current row use Delete. You can manage the
parent-child relations with the particular SELECT statement you use for each
result set. I assume you have to manage cascading deletes yourself
programmatically.

Using traditional SQL DML might be easier, but SqlCeResultSet is going to be
about 3 times as fast as the SqlCeDataAdapter and 6 times as fast as the
SqlCe 2.0 data adapter.

HTH,

Ginny Caughey
..NET Compact Framework MVP

 

[netcfadmirer]

Ginny,

Thanks for your help. One more question, when i set the value to table
fields, can i use column name instead of index such as 1, 2, 3..

 

[Ginny Caughey [MVP]]

You can use rs.GetOrdinal("YourColumnName") to convert from the name to the
ordinal. You should do this outside your read loop and then use the ordinal
inside the loop if you want maximum performance.

Ginny