ADO.Net - Why isn't the insert happening?

Question

I went through MSDN pages to learn ADO.Net using Commands. I am able to read using the sample code posted there.

But when I tried to use the modification code below, the insert is not happening. I am not ale to figure out why. Can someone please tell me what is wrong with this code?

string connectionString = "A_VALID_CONNECTION_STRING";
string commandText = 
"INSERT INTO Contacts (FullName, Mobile) VALUES ('Pierce Brosnan', '1800-007')";

SqlConnection connection = new SqlConnection(connectionString);          

try
{
  connection.Open();
  SqlCommand command = new SqlCommand(commandText, connection);
  Console.WriteLine(command.ExecuteNonQuery());
  connection.Close();
}
catch (Exception ex)
{
  Console.WriteLine(ex.Message);
}

Edit

• No exception is thrown.

• The ExecuteNonQuery() which is supposed to return the no. of rows affected is returning 1.

• Environment: Visual C# 2010 Express | SQL Server 2008 Express | Windows 7 Ultimate 32-bit.

Update

1. Previously I was using a MDF file present in the project. It was, I guess, automatically attached to the SQL server instance each time the project ran. This is when I had the problem. The connection string had some info about attaching a database file.

2. I removed the SQL Server 2008 Express that I installed along with Visual C# 2010 Express. Also removed the MDF file from the project.

3. I Separately downloaded and installed SQL Server 2008 Express along with Management Studio Express.

4. Created a new database in management studio.

5. Used a different type of connection string to use the database in the server.

Now INSERT is working!

P.S. I guess I should have mentioned that I had an attach database file scenario. Really sorry for that.

Answer 1

My suspicion is that you had the following scenario:

• Database.mdf file was present in the project with the table structure created in it
• Your connection string looked something like this Server=.\SQLExpress;AttachDbFilename=database.mdf;Database=dbname; Trusted_Connection=Yes;, i.e. loading the database in the connection string.

What was happening was, when you built/ran your project, your application was compiled and the database.mdf file was copied along with it to ApplicationProjectFolder\bin\Debug, so that when the application was run, the file database.mdf was present. This means that everytime you ran your project, the "empty" database.mdf file was copied from ApplicationProjectFolder\database.mdf to ApplicationProjectFolder\bin\Debug\database.mdf, hence the data "disappearing". Also, the file database.mdf probably had "Copy Always" set on its properties in the project.

So, the "INSERT" was working, it was just being "reset" everytime you ran your application.