Now that we have some tables, you naturally want to put some data into them. As you might have guessed from my last post, you perform data manipulation (insert, update, delete) just like you do when creating the table and use the SqlCeCommand object. Only this time there’s a twist.
First, because I wanted to load several rows I created a method to load a single row and pass in the parameters to my “CoolPeople” table. Here’s the small bit of code that handles it:
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, 7th 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!
Arcane Code 
I linked a combobox to a mobile database in a visual studio app.. bt i cant enter any data into that table. how to do it??
hi i m facing the problem to insert data into database in mobile appliction i have witten the code to insert data in c# and its working fine excutenonquery returns one row but when i select data directly from the database in dont have data.and when i insert data directy intodatabase then data is inserted and show in appliction.
plz help me i can not understand that how to resolve it
Hey If you have got answer to your problem can you email me on chintan_shah8@yahoo.com cos I am facing the same prob.
Regards,
Chintan
Thanks a lot!!!
I had problems inserting dates and your code solved my problem.
@Ashu :
Even i hv the same problem if u get ne solution for it then plz do mail me too on my id NayalShruti@ymail.com plz as soon as you get do reply me plz plz !
@Ashu :
Even i hv the same problem if u get ne solution for it then plz do mail me too on my id NayakShruti@ymail.com plz as soon as you get do reply me plz plz !
Hi,
I am facing problem in the connection string. Please give me the syntax of connection string.
Thanks,
Umesh
See my article, https://arcanecode.wordpress.com/2007/01/25/create-a-sql-server-compact-edition-database-with-c/ for details on the connection string. Also be sure to click on the Arcane Lessons link at the top of this page and scroll down to review all the lessons around SSCE.
hi,
Thanks a lot
this code is very useful 2 me
The records are not added even though the query returns 1 may be due to you forget to close the connection at the end.
This solved my problem.
Dont forget the finally block. You may close the connection outside the finally block but the important point is to close the connection for insertion to be reflected in the database.
Please notify me through mail if this solution works for you.
I am facing same problem the executenonquery is running fine but no data is inserting.Please help me???
Hi.. I have figured out what happened with the “No Insert in the table case”. 🙂
This must be what happen to you guys:
At first we have the database (let’s called it database1.sdf from now on) in the same folder as the Program.cs (the folder where the project is saved).
When we Start Debugging (F5), the application run and insert a row. We did sure that the row is inserted.
But then when we get back to the visual studio environment, and we select the table from the Server Explorer, we notice that the row that we inserted when the application running is not there (We get confuse and asked inside our heart: WHAT HAPPEN?!)
This is what actually happen:
When we Start Debugging (F5 – run the application through the Visual Studio), the database1.sdf from our source folder (the folder of the project) is copied to: Source_Folder_Path\bin\debug.
(Note: everytime we run/debug the project it is copied to the folder bin\debug. You change the property of database – Copy to Output Directory, so that the source database will be copy only if it is newer)
Now you try to insert a record and it is REALLY is inserted, but it is inserted to the database at folder bin\debug (Not the source folder). So when you select the table to see the record that you have inserted, from Server Explorer – Database1.sdf, its shown you the database1.sdf that still empty.
When you debug again, the database1.sdf with no records replace the database1.sdf at the bin\debug. So the application show no record also.
This is get me 3 days to notice what really is happened.
So i want to share to all of you.
I hope my explanation is understandable to you all. 😀
Regards,
Hendra
The same annoying behavior/problem also occurs with SQL Express. See http://blogs.msdn.com/b/smartclientdata/archive/2005/08/26/456886.aspx