C# - ExecuteNonQuery() isn't working with SQL Server CE

Question

Hello.

I got some data inputed by the user that should be added to a Database File (.sdf). I've choose Sql Server CE because this application is quite small, and i didn't saw need to work with a service based database.

Well any way.

Here goes the code:

public class SqlActions
{
    string conStr = String.Format("Data Source = " + new System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\\basedados.sdf");

    public SqlCeConnection SQLCEConnect()
    {
        SqlCeConnection Connection = new SqlCeConnection(conStr);
        Connection.Open();
        return Connection;
    }

    public Boolean AdicionarAuditorio(string Nome, int Capacidade)
    {
        string Query = "INSERT INTO auditorios (nome, capacidade) VALUES (@Nome, @Capacidade)";
        using (var SQLCmd = new SqlCeCommand(Query, SQLCEConnect()))
        {
            SQLCmd.Parameters.AddWithValue("@Nome", Nome);
            SQLCmd.Parameters.AddWithValue("@Capacidade", Capacidade);
            if (SQLCmd.ExecuteNonQuery() == 1)
            {
                return true;
            } else {
                return false;
            }

        }
    }
}

I use the AdicionarAuditorio(string Nome, int Capacidade) function to Insert the data. running ExecuteNonQuery() which is supposed to return the number of affected rows after he as run the query. So it should return 1 if the query as successful, right?

In the end he returns 1, but if I browser the table data, the data that the query should add isn't there.

So whats wrong here?

NOTE. If your thinking that the problem is the connection: I can't see why is the problem once i got some Select statements that use that connection function SQLCEConnect() and they all work pretty well.

Thanks in advance.

Answer 1

Are you sure you are looking at the right file? When you build your app in VS, it copies the SDF file as content to the target folder, so the database in your project will not reflect any updates. Your code is picking up the the file location there.

This is btw not a good practice, because once deployed, the program folders are not writable to your app (could this be the problem - did you already deploy?). Instead, the database file should reside in your appdata folder.

Answer 2

Is it possible that you make the call to AdicionarAuditorio in a TransactionScope without calling transactionScope.Complete()?