CODEWITHSUNDEEP
c#sql-serverstored-procedureslinq-to-entities
Papi
Papi

Reputation: 555

How to get the result returned back from a SQL Server stored procedure in C#

I have built a component that gets data from an Excel sheet, copies it to a SQL Server table. I also have a stored procedure that performs validation on that data and returns a message if the v=data does not pass the validation.

I managed to import the data into SQL Server, get the list of records and execute them in a stored procedure, all in C#. Now my challenge or confusion is actually getting the result of each record validated by the procedure so that I can exclude records that did not pass the validation. How can I do this in C#?

Here I Import the records

public static void ImportDataFromExcel(string excelFilePath)
{
    var sqlTable = "[MSCRM_Intergration].[dbo].[CRMSupplierClaimsUpload]";

    try
    {
        var excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";EXTENDED PROPERTIES=" + ";Extended Properties=Excel 12.0;";

        var sqlConnectionString = "data source=WM-PMARAKA-NB;initial catalog=MSCRM_Intergration;integrated security=True;";

        //this code removes any data in the SQL Server table before importing/ optional step, need to check with Steve and Kyle
        var sqlDeleteQuery = "DELETE FROM [dbo].[CRMSupplierClaimsUpload]";

        SqlConnection conn = new SqlConnection(sqlConnectionString);
        SqlCommand comm = new SqlCommand(sqlDeleteQuery, conn);

        conn.Open();
        comm.ExecuteNonQuery();
        conn.Close();

        //commands to bulk upload data to SQL Server table
        OleDbConnection oleDBConnection = new OleDbConnection(excelConnectionString);
        OleDbCommand oldeDBCommand = new OleDbCommand("SELECT * From [Claims$]", oleDBConnection);

        oleDBConnection.Open();
        OleDbDataReader oleDBDataReader = oldeDBCommand.ExecuteReader();

        SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnectionString);
        sqlBulkCopy.DestinationTableName = sqlTable;

        while (oleDBDataReader.Read())
        {
            sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Action", "Action"));
            sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Line No", "Line No."));
            sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Total Claim", "Total Claim"));
            sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Currency", "Currency"));
            sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Claim Reference", "Claim Reference"));
            //sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(5, 6));
            //sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(6, 7));
            sqlBulkCopy.WriteToServer(oleDBDataReader);
        }

        oleDBConnection.Close();
    }
    catch (Exception ex)
    {
        //throw new ArgumentException(ex.Message);
    }
}

Here I get the list from the table

public static IList<CRMSupplierClaimsData> GetClaimsUpdateRecordsFromStaging()
{
    using (MSCRM_IntergrationEntities1 db = new MSCRM_IntergrationEntities1())
    {
        var query = from claims in db.CRMSupplierClaimsUploads
                    select new CRMSupplierClaimsData()
                         {
                            Action = claims.Action,
                            LineNunber = claims.Line_No_,
                            TotalClaim = (Decimal)(claims.Total_Claim),
                            Currency = claims.Currency,
                            ClaimReference = claims.Claim_Reference
                         };

        return query.ToList();
    }
}

Here I attempt to run the procedure, here is where my question lies, how do I get the response from the procedure and then exclude the record, as the validated records need to be exported to Excel again.

public static IList<CRMSupplierClaimsData> ExcecuteClaimsValidationProc()
{
    using (EmbraceEntities context = new EmbraceEntities())
    {
        IList<CRMSupplierClaimsData> claimsData = GetClaimsUpdateRecordsFromStaging();

        foreach (var claim in claimsData)
        {
            context.Supplier_Claim_Upload(claim.LineNunber, claim.TotalClaim);
        }

        return claimsData;
    }
}

Here is an example of the procedure being run and the result it shows, ran from SQL Server itself

USE [Embrace]
GO

DECLARE @return_value int

EXEC    @return_value = [CRM].[Supplier_Claim_Upload]
        @Invoice = N'TA40151295*01-1',
        @Amount = 3404.2

SELECT  'Return Value' = @return_value
GO

and here is the returned error from the validation

TA40151295*01-1 Warning: You Are going to Overwrite a Claim Value: 3404.000

The procedure was written by an external person who I need to run validation with the data from the Excel sheet. Any ideas how I can achieve this?

Basically I need to do some logic in C# saying that if the invoice number passed and amount result in the warning "You Are going to Overwrite a Claim Value: 3404.000" I want to exclude this record.

Upvotes: 0

Views: 245

Answers (2)

Papi
Papi

Reputation: 555

So I got the final solution to my issue, the Stored Procedure returned a dynamic result based on what you passed to it, so I needed logic to cater for the different responses. Here is the code that finally worked for me

public static Supplier_Claim_Upload_Result ExcludeFailedValidationRecords()
    {
        IList<CRMSupplierClaimsData> claimsData = GetClaimsUpdateRecordsFromStaging();
        Supplier_Claim_Upload_Result supplierClaimUplaod = new Supplier_Claim_Upload_Result();
        //Supplier_Claim_Uplaod_Result_Error supplierClaimUploadError = new Supplier_Claim_Uplaod_Result_Error();
        var sqlConnection = "data source=WMVSQL02;initial catalog=Embrace;integrated security=True;";
        using (SqlConnection conn = new SqlConnection(sqlConnection))
        {
            try
            {
                foreach (var claim in claimsData)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandTimeout = 60;
                    SqlDataReader reader;

                    cmd.CommandText = "CRM.Supplier_Claim_Upload";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Invoice", SqlDbType.NVarChar).Value = claim.LineNunber;
                    cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = claim.TotalClaim;
                    cmd.Connection = conn;

                    conn.Open();
                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        supplierClaimUplaod.ST_Key = reader["ST_Key"].ToString();
                        if (supplierClaimUplaod.SupplierClaim != null)
                        {
                            supplierClaimUplaod.SupplierClaim = reader["Supplier_Claim"].ToString();
                        }
                        else if (supplierClaimUplaod.SupplierClaim == null)
                        {
                            if (supplierClaimUplaod.Error != null)
                            {
                                supplierClaimUplaod.Error = reader["Error"].ToString();
                            }
                            else if (supplierClaimUplaod.Error == null)
                            {
                                supplierClaimUplaod.SupplierClaim = "No value";
                            }
                        }
                        if (supplierClaimUplaod.OrigInv != null)
                        {
                            supplierClaimUplaod.OrigInv = reader["Orig_Inv"].ToString();
                        }
                        else if (supplierClaimUplaod.OrigInv == null)
                        {
                            if (supplierClaimUplaod.Error != null)
                            {
                                supplierClaimUplaod.Error = reader["Error"].ToString();
                            }
                            else if (supplierClaimUplaod.Error == null)
                            {
                                supplierClaimUplaod.OrigInv = reader["Orig_Inv"].ToString();
                            }
                        }
                        if (supplierClaimUplaod.SystemCost != null)
                        {
                            supplierClaimUplaod.SystemCost = reader["System_Cost"].ToString();
                        }
                        else if (supplierClaimUplaod.SystemCost == null)
                        {
                            if (supplierClaimUplaod.Error != null)
                            {
                                supplierClaimUplaod.Error = reader["Error"].ToString();
                            }
                            else if (supplierClaimUplaod.Error == null)
                            {
                                supplierClaimUplaod.SystemCost = reader["System_Cost"].ToString();
                            }
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.InnerException);
            }
            return supplierClaimUplaod;
        }
    }

Upvotes: 1

Alex
Alex

Reputation: 987

If the stored procedure returns a resultset then instead of ExecuteNonQuery() in your code in ImportDataFromExcel() method, call ExecuteReader() and read the dataset in code like you did with "SELECT * From [Claims$]" command further in code.

Upvotes: 0

Related Questions