How to change return of function from string to generic list ?

Question

1.00/5 (1 vote)

See more:

problem

How to change return of function from string to return generic list by using csharp ?

I have stored procedure name getcompanies return list of companies id as following

create proc getcompannies
as
select compnyid from companes where compnyid > 10

so that result will be as following
11
12
13
14
15
etc...
so that i need to change function below from string to return generic list to be dynamically using

What I have tried:

public static string ExecuteProcedureReturnString(string connString, string procName,
           params SqlParameter[] paramters)
       {
           string result = "";
           using (var sqlConnection = new SqlConnection(connString))
           {
               using (var command = sqlConnection.CreateCommand())
               {
                   command.CommandType = System.Data.CommandType.StoredProcedure;
                   command.CommandText = procName;
                   if (paramters != null)
                   {
                       command.Parameters.AddRange(paramters);
                   }
                   sqlConnection.Open();
                   var ret = command.ExecuteScalar();
                   if (ret != null)
                       result = Convert.ToString(ret);
               }
           }
           return result;
       }

Posted 22-Nov-19 11:08am

ahmed_sa

Updated 22-Nov-19 11:46am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-11-22T11:42:00

Start by changing the signature:

public static string ExecuteProcedureReturnString(string connString, string procName,
           params SqlParameter[] paramters)

Becomes

public static List<string> ExecuteProcedureReturnString(string connString, string procName,
           params SqlParameter[] paramters)

And then, the work starts.
Your method doesn't deal with multiple values: it calls a stored procedure that returns a single value, which may or may not be a string - we have no access to the SP or the code while it's running so we can't tell. It's possible that what it return's is a comma delimiter string of values or similar, but we can't tell.

So start by looking at exactly what is returned, and what the SP does.
Sorry, but we can't do any of that for you!

Afzaal Ahmad Zeeshan · Answer 2 · 2019-11-22T11:46:00

That would require an introduction to generics here and a change in the result that you return in the end. But here is something that can get you started:

C#

// Add the T for type annotation.
public static List<T> ExecuteProcedureReturnString<T>(string connString, string procName, params SqlParameter[] paramters)
{
   List<T> result = new List<T>(); // Change the variable.
   using (var sqlConnection = new SqlConnection(connString))
   {
       using (var command = sqlConnection.CreateCommand())
       {
           command.CommandType = System.Data.CommandType.StoredProcedure;
           command.CommandText = procName;
           if (paramters != null)
           {
               command.Parameters.AddRange(paramters);
           }
           sqlConnection.Open();

           // This would change from scalar to query; ExecuteQuery()
           var ret = command.ExecuteQuery();
           if (ret != null)
               // Fill in the results.
       }
   }
   return result;
}

This page would help you in querying the data and building a custom list for the input. I did not modify most of the code since that would only break the build and cause more confusion. You can take care of that.

Retrieving Data Using a DataReader | Microsoft Docs[^]

Read more about generics in C# here, Generics - C# Programming Guide | Microsoft Docs[^], it would be amazing if you can create a C# type and use that instead of generics — as that would clear a lot of confusion in code for starters.

This would work just fine since you are returning a list of integers,

C#

public static List<int> ExecuteProcedureReturnString(string connString, string procName, params SqlParameter[] paramters)
{

Then return a list of integers from your code.

How to change return of function from string to generic list ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0