C# SQL Server Stored Procedure Parameter Return List

C# SQL Server stored procedure parameter return list

Console.WriteLine(output); will simply display the result of ToString on the parameter, which for this type reverts to the default implementation, which just shows the name of the type of the output object. You need to iterate through the items in the list using foreach:

foreach (var item in output)
{
Console.WriteLine(item);
}

Which would cause you to get a row of lines just giving the type of that object. So either you can write an implementation of ToString for your object, or simply display the bits you want to see...

foreach (var item in output)
{
Console.WriteLine($"{item.ItemName} {item.Quantity}");
}

How to make a stored procedure in which the parameters are a list of strings [ SQL Server, ASP.NET Core 3.1 ]

You can use json or xml. Try this

/* test

DECLARE @Brands nvarchar(4000) SET @Brands = '{"Brands": [{"Brand": "Samsung"}, {"Brand": "iPhone"}, {"Brand": "LG"}, {"Brand": "Xiaomi"}]}';;

DECLARE @Storages nvarchar(4000) SET @Storages = '{"Storages": [{"Storage": "16"}, {"Storage": "32"}, {"Storage": "64"}, {"Storage": "128"}]}';;

SELECT * FROM OPENJSON(@Brands, '$.Brands') WITH ( Brand varchar(20) '$.Brand' );
SELECT * FROM OPENJSON(@Storages, '$.Storages') WITH ( Storage varchar(20) '$.Storage' );

*/


create procedure FilterByBrandStoragePrice
@Brands nvarchar(4000),
@Storages nvarchar(4000),
@min decimal,
@max decimal
as
SELECT *
FROM Item i
WHERE i.Brand in (SELECT * FROM OPENJSON(@Brands, '$.Brands') WITH ( Brand varchar(20) '$.Brand' ))
AND i.Storage in (SELECT * FROM OPENJSON(@Storages, '$.Storages') WITH ( Storage varchar(20) '$.Storage' ))
AND i.PRICE between @min and @max

Passing a List of objects to SQL Server stored procedure and retrieving an output value

Here you are adding the parameter as a value for a parameter:

var contactsParam =
new SqlParameter("@contacts", SqlDbType.Structured)
{
TypeName = "dbo.Contact",
Value = GetSqlDataRecordsContactsList(contacts)
};

command.Parameters.Add(new SqlParameter("@contacts", contactsParam)); //Problem is here

It should just be:

 command.Parameters.Add(contactsParam);

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

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.

How can I retrieve a list of parameters from a stored procedure in SQL Server

You can use SqlCommandBuilder.DeriveParameters() (see SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials) or there's this way which isn't as elegant.

How to read stored procedure output and return it as list

Change your code to this

public List<yourClass> GetData()
{
using (SqlConnection con = new SqlConnection(Global.Config.ConnStr))
{

DataTable dt = new DataTable();
List<yourClass> details = new List<yourClass>();

SqlCommand cmd = new SqlCommand("spp_adm_user_user_group_sel", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);

foreach(DataRow dr in dt.Rows)
{
yourClass obj = new yourClass();

obj.fullname= dr["fullname"].ToString();
obj.email= dr["email"].ToString();

details.Add(obj);
}


return details;
}

}

C# SQL Server - Passing a list to a stored procedure

If you're using SQL Server 2008, there's a new featured called a User Defined Table Type. Here is an example of how to use it:

Create your User Defined Table Type:

CREATE TYPE [dbo].[StringList] AS TABLE(
[Item] [NVARCHAR](MAX) NULL
);

Next you need to use it properly in your stored procedure:

CREATE PROCEDURE [dbo].[sp_UseStringList]
@list StringList READONLY
AS
BEGIN
-- Just return the items we passed in
SELECT l.Item FROM @list l;
END

Finally here's some sql to use it in c#:

using (var con = new SqlConnection(connstring))
{
con.Open();

using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
{
using (var table = new DataTable()) {
table.Columns.Add("Item", typeof(string));

for (int i = 0; i < 10; i++)
table.Rows.Add("Item " + i.ToString());

var pList = new SqlParameter("@list", SqlDbType.Structured);
pList.TypeName = "dbo.StringList";
pList.Value = table;

cmd.Parameters.Add(pList);

using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
Console.WriteLine(dr["Item"].ToString());
}
}
}
}

To execute this from SSMS

DECLARE @list AS StringList

INSERT INTO @list VALUES ('Apple')
INSERT INTO @list VALUES ('Banana')
INSERT INTO @list VALUES ('Orange')

-- Alternatively, you can populate @list with an INSERT-SELECT
INSERT INTO @list
SELECT Name FROM Fruits

EXEC sp_UseStringList @list

How to execute a stored procedure in Web API and return a list

try this

 var response = new  List<SalesAndReturns_RPT>();

using (var reader = cmd.ExecuteReader())
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var sales= new SalesAndReturns_RPT();
sales.BusinessArea = Convert.ToDecimal(reader.GetString(0));
sales.NetValue = Convert.ToDecimal(reader.GetDecimal(1));

response.Add(sales);

}

}
}
return response;


Related Topics



Leave a reply



Submit