SQL connection in C#

Question

private static string SqlDBConnectionString = "Server=12831-QHOO\\SQLEXPRESS;Database=DBHome;Trusted_Connection=True;";

private static void SaveDataToDB(DataTable DT)
{
    using (var bulkCopy = new SqlBulkCopy(SqlDBConnectionString, SqlBulkCopyOptions.KeepIdentity))
    {
        foreach (DataColumn col in DT.Columns)
        {
            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);//(DT.ColumnName,SQLTableColumnName)
        }

        bulkCopy.BulkCopyTimeout = 600;
        bulkCopy.DestinationTableName = "DBHome";
        bulkCopy.WriteToServer(DT);
    }
}

I am attempting to connect to a local SQL Server database in C# using the above settings. In doing so, the code after what is shown above is not being executed, i.e. no data is being sent to the tables.

Is there a way to see if it's actually connecting to the database? I get no errors and an exit with code 0.

Looks like your connection string has an extra backslash. Should just be `Server=12831-QHOO\SQLEXPRESS;Database=DBHome;Trusted_Connection=True;` Not sure if that fully solves your problem but its a start. — zep426, Jul 05 '22 at 16:38
Use the debugger. Execute line by line. If an error is thrown, you will see it. Are you certain you are looking in the same table of the same database of the same server instance when you verify? — SMor, Jul 05 '22 at 16:56
Also it appears you are targeting a database named DBHome with a table named DBHome. Perhaps this is correct, but I don't normally see a table with the same name as the database, might be a copy paste issue. — zep426, Jul 05 '22 at 16:57

score 2 · Answer 1 · edited Jul 06 '22 at 00:32

Instead of using the constructor for SqlBulkCopy() where you pass a connection string and options (as you have), you can create a connection yourself, and then pass that connection to SqlBulkCopy(). This way, you can use a try-catch block to verify that you were connected (failed connections throw an error with SqlConnection objects.

Ex:

try {
    using (SqlConnection destinationConnection =
                     new SqlConnection(connectionString))
    {
        destinationConnection.Open();
        using (SqlBulkCopy bulkCopy =
                  new SqlBulkCopy(destinationConnection))
        {
            //do the operations you need to do in this block

        }
    }

}catch (Exception e)
{
    Console.WriteLine(e.Message);
}

This code doesn't actually change any of the functionality, but it's easier to debug. As @zep426 mentioned in the comments, I suspect the connection string might be your issue (you may need to escape certain characters), but using this approach it should be easier to nail down the problem. You can add a console line to print destinationConnection.State, for instance.

Edit:

Also Beware that the C# interface for SQL server uses a different connection string than JDBC connections (which your string seems to be). See this post or these docs for more info.

you should wrap the SqlConnection in a using block to make sure that the Connection is getting closed even if an exception is thrown between the open and close of the connection — Leppin, Jul 05 '22 at 18:11

SQL connection in C#

1 Answers1