Issues connecting to a SQL Server database in C#

Question

I found this article for the majority of the code here How to set SQL Server connection string?.

Here's what I have though:

SqlConnection conn = new SqlConnection();

conn.ConnectionString = "Data Source=(localdb)\\Instance;" +
                        "Initial Catalog=Database;" +
                        "User id=root;Password=pass;";

try
{
     conn.Open();
}
catch (Exception ex)
{
     Console.WriteLine(ex.ToString());
}

SqlCommand myCommand = new SqlCommand("INSERT INTO Database.dbo.Table" +
                                      "VALUES ('stg', 'stg1', 'stg2', 'stg3');", conn);

try
{
     conn.Close();
}
catch (Exception ex)
{
     Console.WriteLine(ex.ToString());
}

A lot of the previous code was giving me errors. At one point, I was getting an error that I couldn't connect to the instance. This was an issue with the '\'. It was recognizing it as an escape character, so I did double '\' to get rid of that, so I'm not getting that error anymore, so that shouldn't be the problem.

I was getting an error about the wrong username and password. I'm using SQL Server Authentication and the code I was previously using was specifying Windows authentication. I'm not getting that error anymore.

As of right now, the code executes all the way through with no errors. When I check on the database though (I'm using SQL Server Management Studio), nothing gets inserted into the table. I have ran the Insert statement directly and it works fine, so I'm not sure what the issue is here. Also, I'm using VS 2013.

Don't want to be too harsh, but did you read any tutorial on ADO.NET before starting this kind of programming? You have a very powerful tool in your hands and you should try to understand a little of how it works otherwise these kind of problems will be a recurring scenario. — Steve, Oct 19 '14 at 21:31

score 2 · Answer 1 · answered Oct 19 '14 at 21:07

You are not using SqlCommand.ExecuteNonQuery to use the insert-command at all. Therefore nothing is inserted.

SqlCommand myCommand = new SqlCommand("INSERT INTO Database.dbo.Table" +
                                      "VALUES ('stg', 'stg1', 'stg2', 'stg3');", conn);
int inserted = myCommand.ExecuteNonQuery();

You should also use the using-statement to ensure that all unmanaged resources are disposed and the connection gets closed even on error. Then you don't need to use conn.Close explicitly.

Guffa · Accepted Answer · 2014-10-19T21:13:15.690

1

You are not executing the query. Add this after creating the command object:

myCommand.ExecuteNonQuery();

Side note: You should rather have a try...catch around all the code, and use using blocks for the connection and command. That will ensure that the objects are closed and disposed properly whatever happens:

try {

  using (SqlConnection conn = new SqlConnection()) {

    conn.ConnectionString =
      "Data Source=(localdb)\\Instance;" +
      "Initial Catalog=Database;" +
      "User id=root;" +
      "Password=pass;";

    conn.Open();

    using (SqlCommand myCommand = new SqlCommand("INSERT INTO Database.dbo.Table" +
                                      "VALUES ('stg', 'stg1', 'stg2', 'stg3');", conn)) {
      myCommand.ExecuteNonQuery();
    }

  }

} catch (Exception ex) {
  Console.WriteLine(ex.ToString());
}

edited Oct 19 '14 at 21:13

answered Oct 19 '14 at 21:07

Guffa

687,336
108
737
1,005

Writing `catch (Exception)` is a bad habit you shouldn't start new programmers on. At the very least, catch the specific exception you're expecting (in this case, `SqlException`). Better yet, if your program has no strategy for handling the exception, don't and let it crash. – Jeroen Mostert Oct 20 '14 at 08:16
@JeroenMostert: That is a good point, and I was thinking of mentioning that, but kept it closer to the original code. Writing the error message to the console seems to be a temporary measure for handling the error anyway. – Guffa Oct 20 '14 at 09:13

Issues connecting to a SQL Server database in C#

2 Answers2