Arcane Code

        LoadARow(“Carl”, “Franklin”, @”http:\\www.dnrtv.com”);

        LoadARow(“Richard”, “Campbell”, @”http:\\www.dotnetrocks.com”);

        LoadARow(“Leo”, “Laporte”, @”http:\\www.twit.tv”);

        LoadARow(“Steve”, “Gibson”, @”http:\\www.grc.com”);

        LoadARow(“Arcane”, “Code”, @”http:\\arcanecode.wordpress.com”);

I then wrote a routine that would take the passed in data and insert it into the database. As with my create table example yesterday, I’m using the command object. This time though, I am adding parameters to the command.

If you look in the SQL, you see I have three parameters, noted by the @ sign. @first, @last, and @url. When SSCE creates the insert statement for the database, it will then look for three parameters and replace these three @ placeholders with the values you put into the parameters.

Sure, you could concatenate it all together in a string, but then you have to worry about things like the “O’Malley Issue” and SQL Injection attacks. (See Bill Vaughn’s book “Hitchhiker’s Guide to Visual Studio and SQL Server, 7^th edition for a complete discussion on these topics, or browse the web. There’s lots of info so I won’t take up further space now.)

Here’s the entire LoadARow routine. Note that my choosing to name the method parameters the same as the SSCE command parameters is entirely a coincidence, it simply makes it self documenting and is not a requirement.

    private void LoadARow(string first, string last, string url)

    {

      SqlCeConnection cn = new SqlCeConnection(ConnectString());

      if (cn.State == ConnectionState.Closed)

      {

        cn.Open();

      }

      SqlCeCommand cmd;

      string sql = “insert into CoolPeople “

        + “(LastName, FirstName, URL) “

        + “values (@lastname, @firstname, @url)”;

      try

      {

        cmd = new SqlCeCommand(sql, cn);

        cmd.Parameters.AddWithValue(“@lastname”, first);

        cmd.Parameters.AddWithValue(“@firstname”, last);

        cmd.Parameters.AddWithValue(“@url”, url);

        cmd.ExecuteNonQuery();

        lblResults.Text = “Row Added.”;

      }

      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

      {

        cn.Close();

      }

    }

After creating the SqlCeCommand object by passing in the sql string and the connection object, I can then add the parameters with a single line for each. By using the Parameters object of the command object, I can call the AddWithValue method, and simply pass in the parameter name as a string and the value for that parameter. Once you add all the parameters, simply call the ExecuteNonQuery method and the data is inserted!

This method can be a basis for all your future work with SSCE. Everything you need to do revolves around the command object and sending SQL commands to the database. Need to delete a record? Just change the SQL from an insert to a delete, pass the correct parameters and you are in business. Update? Same thing.

Using the code I’ve shown in this series you can create your own SSCE applications, and store data locally. Lest you think this is the wrap up, I have one more cool method to show you, but that’ll be for tomorrow!