找到你要的答案

Q:Stored Procedure does not fire in Windows Service

Q:存储过程不在windows服务中引发

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();
    }
}

我用下面的代码创建了一个Windows服务。不知道为什么它会火不上SQL Server存储过程。如果我在代码中没有任何东西,但存储过程比它。没有错误。

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();
    }
}
answer1: 回答1:

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()

我在一些SQL命令中额外的代码”也有类似的问题。虽然我不能马上看到它,确实有一个错误被抛出,因为一些代码包含SQL命令之间。在尝试捕捉包装这可能有助于显示这。

要解决这个(和作为一个好的做法),你应该创建为每个SQL命令新的连接和不重复使用相同的连接对象和保持开放,所以长命令之间。如果你需要确保它们作为处理单元处理,将调用此方法的一种新的transactionscope()

c#  sql  sql-server  stored-procedures