C # SQL Stored Procedure Call Values

Question

Trying to contact the database, I got a little confused about how to pass the value as a parameter (for example, itemID) and return records that have this identifier.

Here is my stored procedure:

ALTER PROCEDURE [dbo].[sp_lightItem]
(
    @itemID INT
)
AS
BEGIN

SELECT [itemID],
       [itemName],
       [itemLocation],
       [itemChBy]
FROM   [dbo].[Item] 
WHERE itemSystemType='E'  and itemID=@itemID ORDER BY itemID DESC;
END

And this is my C # code.

 public string LoadItemNew(int ItemID)
 {
     var acf = new AcFunctions();
     var newstorevalue = SqlHelper.ExecuteDataset(acf.AcConn(), "sp_lightItem", ItemID);
 }

As you can see in the stored procedure, I want to return these 4 elements:

[itemID],[itemName],[itemLocation],[itemChBy]

Unfortunately, I don't know how to return them / how to call them in a C # function.
Any help is appreciated.

Answer 1

, , , DataReader .

, DTO, LightItemDTO

public class LightItemDTO
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public string Location { get; set; }
            public string ChangedBy { get; set; }
        }

. , : SQL Server

ADO.NET

public IEnumerable<LightItemDTO> GetLightItem(string itemText, string sqlConnectionString)
        {
            var results = new List<LightItemDTO>();

            using (var con = new SqlConnection(sqlConnectionString))
            {
                using (var cmd = new SqlCommand("sp_lightItem", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ItemID", SqlDbType.VarChar).Value = itemText;
                    con.Open();
                    using (var reader = cmd.ExecuteReader())
                    {
                        results.Add(new LightItemDTO
                        {
                            Id = Convert.ToInt32(reader["itemID"]),
                            Name = reader["itemName"].ToString(),
                            Location = reader["itemLocation"].ToString(),
                            ChangedBy = reader["itemChBy"].ToString()
                        });
                    }
                }
            }

            return results;
        }

DataReader - . ADO.NET - , ORM , : Entity Framework, Dapper.NET...

Answer 2

,

AcFunctions();

, ConnectionString

 public string LoadItemNew(int ItemID)
            {
    var acf = new AcFunctions();
    var newstorevalue = SqlHelper.ExecuteDataset(acf.AcConn(), "sp_lightItem", new SqlParameter ("@itemID",ItemID));
}

1

  public string LoadItemNew(int ItemID)
    {
            List<string> testList = new List<string>();            

        var acf = new AcFunctions();


        var newstorevalue = SqlHelper.ExecuteReader(acf.AcConn(), "sp_lightItem", new SqlParameter ("@itemID",ItemID));

     if(newstorevalue.HasRows)
    {





           while(newstorevalue.Read())
           {
               testList.Add(newstorevalue["itemID"].ToString());
               testList.Add(newstorevalue["itemName"].ToString());
               testList.Add(newstorevalue["itemLocation"].ToString());
               testList.Add(newstorevalue["itemChBy"].ToString());
          }

    }

 }

Answer 3

:

using (SqlConnection con = new SqlConnection(dc.Con)) {
  using (SqlCommand cmd = new SqlCommand("sp_lightItem", con)) {
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@ItemID", SqlDbType.VarChar).Value = itemId.Text;

    con.Open();
    cmd.ExecuteNonQuery();
  }
}

Answer 4

first set Commandtypehow stored procedure, and this procedure will return some data that you save to dataset, and then return the data set to where you want to fill in the data

public DataSet LoadItemNew(int ItemID)
 {
     var acf = new AcFunctions();
     return DataSet ds = SqlHelper.ExecuteDataset(acf.AcConn(),CommandType.StoredProcedure, "sp_lightItem",new SqlParameter("@itemID" ItemID);
 }

Answer 5

You can try like this.

public string LoadItemNew(int ItemID)
{
     var acf = new AcFunctions();
     List<SqlParameter> parameters = new List<SqlParameter>();
     parameters.Add(new SqlParameter("@itemID", ItemID));
     DataSet Ds = SqlHelper.ExecuteDataset(acf.AcConn(),CommandType.StoredProcedure, "sp_lightItem" , parameters.ToArray());
     return "ok";
}