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

Question

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

specifically public virtual DataSet ExecuteDataSet( CommandType commandType, string commandText ) should suit you. — Amit Kumar Ghosh
– Amit Kumar Ghosh, Commented Aug 11, 2015 at 4:58
Thats not a stored procedure, thats a SELECT. What specific issue are you having? No data returned? incorrect data returned? error message? — Nick.Mc
– Nick.Mc, Commented Aug 11, 2015 at 5:05
Assuming the select is part of your procedure , you could pass CommandType.StoredProcedure and the Name of the procedure as the two parameters. — Amit Kumar Ghosh
– Amit Kumar Ghosh, Commented Aug 11, 2015 at 5:07

CodePhobia · Accepted Answer · 2015-08-11 05:10:59Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related