Stored Procedure does not fire in Windows Service

Question

I created a Windows service with this code below. Not sure why it will not fire the last SQL Server stored procedure. If I have nothing in the code but the stored procedure than it fires ok. There are no errors.

using (SqlConnection SqlConnection = new SqlConnection(connectionString))
{
    SqlConnection.Open();

    using (SqlCommand cmdCreateITableSP = new SqlCommand("CreateSP", SqlConnection))
    {
        cmdCreateITableSP.CommandType = CommandType.StoredProcedure;
        cmdCreateTableSP.ExecuteNonQuery();
    }

    string INTable = "IN";
    string XMLPackagesDir = "D:\\Cle\\";

    // Create a datatable with two columns:
    DataTable INHReponseDT = new DataTable("INHReponseDT");

    // Create Columns: 
    INHReponseDT.Columns.Add("XM");
    INHReponseDT.Columns.Add("Cl");
    INHReponseDT.Columns.Add("I");
    INHReponseDT.Columns.Add("INH");
    INHReponseDT.Columns.Add("IN");

    DirectoryInfo DirInfo = new DirectoryInfo(XMLPackagesDir);

    DataRow INHReponseRow = INHReponseDT.NewRow();

    foreach (FileInfo fi in DirInfo.GetFiles("*.*", SearchOption.AllDirectories))
    {
        XmlSerializer serializer = new XmlSerializer(typeof(Response));
        Response i;
        FileStream fs = null;
        fs = new FileStream(Path.Combine(XMLPackagesDir, fi.Name), FileMode.Open);

        using (TextReader tr = new StreamReader(fs))
        {
            i = (Response)serializer.Deserialize(tr);
            INHReponseRow = INHReponseDT.NewRow();
            INHReponseRow["XM"] = fi.Name;
            INHReponseRow["Cl"] = i.ClientCorrelationID;
            INHReponseRow["I"] = i.StatusInformation.StatusItem.MessageText;
            INHReponseRow["INH"] = i.ResponseStatus;
            INHReponseRow["IN"] = i.RequestProcessedTime.ToString();

            INHReponseDT.Rows.Add(INHReponseRow);
        }

        //Insert into SQL Table
        using (SqlBulkCopy s = new SqlBulkCopy(SqlConnection))
        {

            s.DestinationTableName = INTable;
            s.BatchSize = INHReponseDT.Rows.Count;
            s.WriteToServer(INHReponseDT);
            s.Close();
        }
    }

    using (SqlCommand cmdUpdateCaseInformationINHResponseSP = new SqlCommand("UpdateCaseSP", SqlConnection))
    {
        cmdUpdateCaseInformationINHResponseSP.CommandType = CommandType.StoredProcedure;
        cmdUpdateCaseInformationINHResponseSP.ExecuteNonQuery();
    }
}

Naming you connecion `SqlConnection ` is not a great idea. Have you got this code in a `try / catch`? — Fred, Oct 07 '15 at 21:08
In your first call you name the `SqlCommand` `cmdCreateINHResponseTableSP ` yet inside the using block you use `cmdCreateITableSP` — Fred, Oct 07 '15 at 21:10
So there is an error in all your "other code". You should find it. At a glance since you are importing files I guess there is some data quality issue. Is your windows service crashing? You should capture errors and write them to the windows event log. — Nick.Mc, Oct 07 '15 at 23:10

score 0 · Answer 1 · edited May 23 '17 at 11:44

I had a similar issue with "extra code" in the middle of some SQL commands. While I couldn't immediately see it, there was indeed an error being thrown because of some of the code included between the SQL commands. Wrapping this in a try catch may help show this.

To fix this (and as a matter of good practices), you should create new connections for each SQL command and not reuse the same connection object and keep it open for so long between commands. If you need to ensure that they are handled as a transacted unit, wrap the call to this method in a new TransactionScope()

Stored Procedure does not fire in Windows Service

1 Answers1