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.

Answer 1

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.