user2152373 user2152373 - 2 months ago 11
ASP.NET (C#) Question

How to get a value from a stored procedure

I want to know how I can get the parameter of my stored procedure. I'm new to ASP.Net C#.. this is my stored procedure.

ALTER procedure [dbo].[obtnCtUsuarios]
((@nombre nvarchar (20))
AS
SELECT
id, idEmpresa
FROM
ctUsuarios
WHERE
nombre = @nombre
ORDER BY
idNombre DESC


And I get an error in here because I don't know how to put the parameters.

public List<ctUsuarios> select_ALL_ctUsuarios()
{
List<ctUsuarios> LstctUsuarios = new List<ctUsuarios>();

string storedProcedure = "obtnCtUsuarios";

using (DbConnection con = Conexion.dpf.CreateConnection())
{
con.ConnectionString = Conexion.constr;

using (DbCommand cmd = Conexion.dpf.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = storedProcedure;
cmd.CommandType = CommandType.StoredProcedure;

con.Open();

using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
LstctUsuarios.Add(
new ctUsuarios((int)dr["idUsuario"],
(string)dr["id"],
(string)dr["idEmpresa"]));
}
}
}
}

return LstctUsuarios;
}


This is my connection and my
ExecuteNonQuery
.

public class Conexion
{
public Conexion()
{ }

public static string constr
{
get { return ConfigurationManager.ConnectionStrings["Conn"].ConnectionString; }
}

//Cliente
public static string Provider
{
get { return ConfigurationManager.ConnectionStrings["Conn"].ProviderName; }
}

public static DbProviderFactory dpf
{
get
{
return DbProviderFactories.GetFactory(Provider);
}
}

private static int ejecutaNonQuery(string storedProcedure, List<DbParameter> parametros)
{
int Id = 0;

try
{
using (DbConnection con = Conexion.dpf.CreateConnection())
{
con.ConnectionString = Conexion.constr;

using (DbCommand cmd = Conexion.dpf.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = storedProcedure;
cmd.CommandType = CommandType.StoredProcedure;

foreach (DbParameter param in parametros)
cmd.Parameters.Add(param);

con.Open();
Id = cmd.ExecuteNonQuery();
}
}
}
catch (Exception)
{
throw;
}
finally
{
//conection.close();
}

return Id;
}


Can someone help me get the parameters? tHx

Answer

cmd.Parameters.Add(literalFromSproc, SqlDbType.VarChar).Value = will do what you want. If say you have a param in your sproc called @Name you would do - cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "evan";

There are also similar variants like AddWithValue that would work. Check out the docs here