Adding and updating a record programmatically using .NETCF

Question

OK - I have worded this search 40 different ways and I seem to be lost here.

Every example I find seems so happy that you can easily drag and drop a datagrid and let the user fill it in -- then they stop! I know how to do everything I am asking through LINQ. That obviously won't translate here. I really should have learned ADO.NET first, then LINQ, but NOoooo...

I need to know how to do the following in .NETCF (Windows Mobile 5) using a SQL CE database on the device.

1. Add a new record and populate some or all of the fields with data I supply. I don't need to add a record to a datagrid - sometimes the user will not even see the record. How do I add a new record -- put data into it and save it? For example: Create a new delivery record, say, and have the program store the date in one field and a number in another field.

2. Search for a record, then update data in it. Again, using LINQ I can do this easily -- I cannot for the life of me find any examples of doing it without it. I can find lots of examples of populating a grid of databound fields, letting the user make changes then saving it out. I don't need to do that.

Say I need to search for the one record that meets a criteria (customerID=10 and orderID=1234), then when (if) that record is found, update a field in it.

Please let me know if you need any more info. I have done a lot of reading and just seem to be missing something!

Thanks in advance...

Joe

Answer 1

For #1, there are a few ways. Two easy, common ways are:

Use a SqlCeResultset, and create a row with it, then insert.

using(SqlCeConnection connection = new SqlCeConnection(myConnString))
using (SqlCeCommand cmd = new SqlCeCommand())
{
    connection.Open();
    cmd.CommandText = "MyTable";
    cmd.CommandType = CommandType.TableDirect;
    cmd.Connection = Connection;
    SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);

    SqlCeUpdatableRecord record = rs.CreateRecord();

    // do something to set your values in the record

    rs.Insert(record);
}

Use a SqlCeCommand, set the command SQL and call ExecuteNonQuery.

using (SqlCeConnection connection = new SqlCeConnection(myConnString))
using (SqlCeCommand cmd = new SqlCeCommand())
{
    connection.Open();
    cmd.CommandText = "INSERT INTO MyTable(Foo) VALUES('bar')";
    cmd.CommandType = CommandType.Text;
    cmd.Connection = Connection;
    cmd.ExecuteNonQuery();
}

For #2 it's not much different. Use an updateable resultset, seek to the record, modify and call Update or build your SQL and send it through the command using ExecuteScalar or ExecutNonQuery. The code is similar enough to those above that I'll leave it to you to work out.

A quick search on "SqlCeResultSet" and "example" or "update example" give loads of relevant results.

Answer 2

Thank you,

Between that and this code here, I think I am on my way.

Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click

    Dim con As SqlCeConnection

    con = New SqlCeConnection(("Data Source = " & Me.NorthwindDatabaseFullPath))
    con.Open()

    Dim sqlcdm As String = "INSERT INTO Orders VALUES(999, 'ALFKI', 1, 'RUDEDOG', '', '', '', '', '', 2, '11/15/1967', '11/16/1967', '11/17/1967', '23')"

    Dim SqlCeCommand As New SqlCeCommand(sqlcdm, con)

    SqlCeCommand.ExecuteNonQuery()

End Sub

Answer 3

Also for anybody else searching this book also has a really good chapter on this procudere: http://my.safaribooksonline.com/0321174046

I am still working my through this issue but making good progress,

Thanks for the help.

Joe