CODEWITHSUNDEEP
c#sql-serverasp.net-mvcstored-proceduressqlcommand
MinhNguyen
MinhNguyen

Reputation: 846

How to use stored procedure in C# to return a list of results?

Here is my stored procedure:

    CREATE  Proc UpdateChecklist
(
    @TemplateId As INT
) as
begin
    select MF.CheckListDataId from TemplateModuleMap TM
    inner join ModuleField MF 
    on TM.ModuleId = MF.ModuleId
    where TM.TemplateId = @TemplateId and MF.CheckListDataId not in
    (select cktm.CheckListDataId from ChecklistTemplateMap cktm
    inner join ChecklistData ckd
    on cktm.CheckListDataId = ckd.Id
    where cktm.TemplateId = @TemplateId)
end

So I expect to have a returned list of CheckListDataId here. I'm trying to use Database.ExecuteSqlCommand() but not succeed yet. How can I return a list of CheckListDataId here? Do I need to modify my stored proc? I'm pretty new to sql.

Any suggestion? This is an ASP.NET MVC 5 project

Upvotes: 4

Views: 7197

Answers (1)

CodePhobia
CodePhobia

Reputation: 1315

Your Stored Procedure will return you a resultset and you can process that however you want in your C#.

I would call the procedure from inside my model class in this way:

DataTable loadLogFilterData = SQLHelper.ExecuteProc(STORED_PROCEDURE_NAME, new object[] { 
    //Parameters to Stored Proc If Any
                });

Then I have a SQLHelper Class inside which I create the SQL Connection and have the delegate methods to call the stored procedures. 

public static DataTable ExecuteProc(string procedureName, Object[] parameterList, string SQLConnectionString) // throws SystemException
        {
            DataTable outputDataTable;

            using (SqlConnection sqlConnection = OpenSQLConnection(SQLConnectionString))
            {
                using (SqlCommand sqlCommand = new SqlCommand(procedureName, sqlConnection))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    if (parameterList != null)
                    {
                        for (int i = 0; i < parameterList.Length; i = i + 2)
                        {
                            string parameterName = parameterList[i].ToString();
                            object parameterValue = parameterList[i + 1];

                            sqlCommand.Parameters.Add(new SqlParameter(parameterName, parameterValue));
                        }
                    }

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                    DataSet outputDataSet = new DataSet();
                    try
                    {
                        sqlDataAdapter.Fill(outputDataSet, "resultset");
                    }
                    catch (SystemException systemException)
                    {
                        // The source table is invalid.
                        throw systemException; // to be handled as appropriate by calling function
                    }

                    outputDataTable = outputDataSet.Tables["resultset"];
                }
            }

            return outputDataTable;
        }

You have treat every output from a stored procedure as a resultset no matter what it contains. Then you need to manipulate that result set in your Model to populate the desired data structure and data type.

Upvotes: 5

Related Questions