Click here to Skip to main content
14,690,515 members
Please Sign up or sign in to vote.
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
Updated 22-Nov-19 12:46pm

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!
   
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:

// 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,
public static List<int> ExecuteProcedureReturnString(string connString, string procName, params SqlParameter[] paramters)
{
Then return a list of integers from your code.
   
v2
Comments
ahmed_sa 22-Nov-19 18:20pm
   
thank you very much but remaining some points not understand about second answer or post
actually i need function generic as second thread but something not clear
1- what you mean executequery
command not have excute query
2- how to fill data or result please
can you complete second function if possible
Afzaal Ahmad Zeeshan 22-Nov-19 18:27pm
   
There is a difference in ExecuteScalar and ExecuteQuery, please read their documentation to know about the difference — a quick answer is that ExecuteQuery would return the records and ExecuteScalar would return a count of the rows that were processed.

To fill the data to rows, you use the DataReader, then you can simply use indexers ([]) to access the column value. Please check the link I provided for DataReader it has the answer to your question. :-)
phil.o 23-Nov-19 5:27am
   
More exactly, ExecuteScalar returns the first column of the first row. It is often used to return the result of a count function, but that does not mean it will always return the count of rows.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900