Using a database in .NET

Question

UPDATED QUESTION:

Ok, I am going to simplify my question since I don't really know how to answer your questions.

Say I create a new Windows Forms Application, then Project->Add New Item->Local Database.

Then in Database Explorer I create a table ("testtable") and give it an "ID" column and "VALUE" column.

Can you provide me with the steps to simply add a row to the database from the code?

OLD QUESTION:

I have been trying to do something that I think would be really easy but have never used C# before and am having trouble with the details. I simple want to use a sql database with Visual C# Express 2008.

For testing purposes I have a datagrid on my form that can reflect changes to the db.

If i use this:

codesTableAdapter.Fill(dataSet1.codes);

The datagrid(dataset) will fill with the info from the sql database.

If i then do something like this:

codesTableAdapter.InsertQuery(txtCode.Text,txtName.Text);
codesTableAdapter.Fill(dataSet1.codes);
codesTableAdapter.Update(dataSet1);
dataSet1.AcceptChanges();

The datagrid reflects the changes but if close the program and go to the database the changes are not there. When I open the program again the changes are not there.

I have a feeling this isn't too clear as my understanding here is very low so please let me know what other info is needed.

Answer 1

are your DataRow objects within your DataSet in edit-mode?

Answer 2

To advice better it would be helpfull if you can publish your sql statement. Maybe your rows are not in updateable mode ?

Also there are tons of materials about that subj in Internet - eg http://authors.aspalliance.com/aspxtreme/sys/data/common/DataAdapterClassUpdate.aspx

Answer 3

One potential problem is that you are looking at a different copy of the database than you are inserting into. Are you using SqlCE? The database could be copied to the output directory and therefore may be a different DB than the one that you are looking at...

Answer 4

Perhaps you can try something like this.

DataTable dt = dataSet1.Tables["codes"];
codesTableAdapter.InsertQuery(txtCode.Text,txtName.Text); 
codesTableAdapter.Fill(dt); 
// Add a new row to your datatable;
DataRow dr = dt.NewRow();
dr["ID"] = 100;
dr["Value"] = "This is my value";  // this is assumeing the 'Value' columns is of type string.
dt.Rows.Add(dr);

codesTableAdapter.Update(dataSet1); 
dataSet1.AcceptChanges(); 

Answer 5

Really hard to understand what is going on without seeing your code, but I will make some guesses:

Say I create a new Windows Forms application then Project->Add New Item->Local Database

This will create an empty SQL Server Compact Edition .SDF file in your project. You can edit this file from Visual Studio or SQL CE Query Analyzer.

Then in Database Explorer I create a table ("testtable") and give it an "ID" column and "VALUE" column.

This will create an empty table in your .SDF file. Those are pretty bad names, but for test purpose we can use them.

Can you provide me with the steps to simply add a row to the database from the code?

System.Data.SqlServerCe provides methods to manage .SDF files. To add a row to your test table:

using (SqlCeConnection myConnection = new SqlCeConnection(@"Data Source=Path\To\Your\SDF\file.sdf;"))
using (SqlCeCommand myCmd = myConnection.CreateCommand())
{
    myCmd.CommandType = CommandType.Text;
    myCmd.CommandText = "INSERT INTO testtable (ID, [VALUE]) VALUES (1, whatever)";
    myConnection.Open();
    myCmd.ExecuteNonQuery();
}

Be aware that Path\To\Your\SDF\file.sdf may not be what your think; Visual Studio by default copies project files to the Project Build Output Path (e.g. Path\to\your\project\bin\Debug\file.sdf). If you change the built file then rebuild your project, the original .SDF file gets recopied to your output path, wiping out any changes you have made to your project file. If that's not what you want you will have to explain what you are trying to accomplish.

For example, to make changes apply only to the built project file, change your data file "Copy to Output Directory" property in VS to "Copy if Newer".