C# code returns row count from SQL stored procedure but all data is null

Question

I am trying to call a stored procedure from C# code and return a list of data. The stored procedure works well in SQL. When I run the code in C# I get the count of records but all the data is null. My current result is 192 rows of data when running stored procedure through SQL. When I debug in C# i see the 192 rows but they are all filled with null data. What do I need to do?

Stored Procedure

SELECT DISTINCT CA.Item
, CL.Chains
, S.Sub_Name
, I.Description  AS 'Description'
, I.SizeDesc AS 'Size'
, Left(I.UpcRetail, LEN(I.UpcRetail)-1) AS 'UPC'
, CL.AssignedToUserId
FROM ChainAuth_ChainAuth CA
INNER JOIN ChainAuth_ChainList CL
ON CA.ChainId = CL.ChainId
INNER JOIN HeidDWHSE.dbo.ItemV I
ON CA.[Item] = cast(I.Item as int)
INNER JOIN HeidDWHSE..Sub S
ON I.Sub = S.Sub
WHERE CA.ChainId = @ChainID
END

C# 1st Attempt

public List<ChainAuthorizations> GetAuthListByChain(string chainId)
{
var parameters = new[] {
new SqlParameter("@ChainId", chainId)
};
var auths = hc.Database.SqlQuery<ChainAuthorizations>("EXEC 
usp_GetAuthorizedItemDetails @ChainId", 
parameters).ToList<ChainAuthorizations>();
using (SqlCommand command = new SqlCommand("usp_GetAuthorizedItemDetails"))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@ChainId", chainId));
conn.Open();
command.Connection = conn;

using (SqlDataReader reader = 
command.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
int item = reader.GetInt32(reader.GetOrdinal("Item"));
string chains = reader.GetString(reader.GetOrdinal("Chains"));
string sub_Name = reader.GetString(reader.GetOrdinal("Sub_Name"));
string description = reader.GetString(reader.GetOrdinal("Description"));
string size = reader.GetString(reader.GetOrdinal("Size"));
string upc = reader.GetString(reader.GetOrdinal("UPC"));
int assignedToUserId = 
reader.GetInt32(reader.GetOrdinal("AssignedToUserId"));
}
}
}
}
return auths;
}

C# 2nd attempt

public List<ChainAuthorizations> GetAuthListByChain(string chainId)
{
List<ChainAuthorizations> cAuths = new List<ChainAuthorizations>();
SqlConnection con = new SqlConnection("connection string data here");
SqlDataAdapter sda = new SqlDataAdapter("usp_GetAuthorizedItemDetails", 
con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add(new SqlParameter("@chainId", 
SqlDbType.VarChar));
sda.SelectCommand.Parameters["@chainId"].Value = chainId;
DataTable dt = new DataTable();
sda.Fill(dt);
cAuths = dt.ToString();
return cAuths.ToList();
}

Remove this line cAuths = dt.ToString(); and iterate like this — VCody
– VCody, Commented Sep 24, 2018 at 12:34
Results from Postman [] ...Still an empty array after removing line — n8bizall
– n8bizall, Commented Sep 24, 2018 at 12:37
that is not the way to convert the data from datatable to a list — Ehsan Sajjad
– Ehsan Sajjad, Commented Sep 24, 2018 at 12:39
try to remove CommandBehavior.CloseConnection from first attempt and check is there reader.HasRows is true or false — er-sho
– er-sho, Commented Sep 24, 2018 at 12:40
Your first example doesn't make much sense - where are you declaring and populating the list? — Rui Jarimba
– Rui Jarimba, Commented Sep 24, 2018 at 12:40

VCody · Accepted Answer · 2018-09-24 13:05:52Z

3

The Problem is you are casting dt to dt.ToString() and returning empty list.You have to iterate through the dt(DataTable) and Convert to List. Please check the below location.

public List<ChainAuthorizations> GetAuthListByChain(string chainId)
{
    List<ChainAuthorizations> cAuths = new List<ChainAuthorizations>();
    SqlConnection con = new SqlConnection("connection string data here");
    SqlDataAdapter sda = new SqlDataAdapter("usp_GetAuthorizedItemDetails",
    con);
    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
    sda.SelectCommand.Parameters.Add(new SqlParameter("@chainId",
    SqlDbType.VarChar));
    sda.SelectCommand.Parameters["@chainId"].Value = chainId;
    DataTable dt = new DataTable();
    sda.Fill(dt);
    cAuths = (from DataRow dr in dt.Rows
                   select new ChainAuthorizations()
                   {
                       //Change with your model

                       StudentId = Convert.ToInt32(dr["StudentId"]),
                       StudentName = dr["StudentName"].ToString(),
                       Address = dr["Address"].ToString(),
                       MobileNo = dr["MobileNo"].ToString()
                   }).ToList();
    return cAuths;
}

edited Sep 24, 2018 at 13:05

answered Sep 24, 2018 at 12:40

VCody

5163 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Uwe Keim Over a year ago

You really should explain what your code does and why it is a solution/answer to the OP's question.

n8bizall Over a year ago

@VCody Thank you for your simple solution. It worked.

VCody Over a year ago

@UweKeim agree updated the same thank you ,please feel free to update the answer.

Red Over a year ago

@n8bizall, if VCodys answer solved your issue you should mark it as the answer (click the tick below the number at the top of the post)

n8bizall Over a year ago

@Red I am new to StackO, it says I can't vote yet. Is there something else I should do to mark the answer?

|

Collectives™ on Stack Overflow

C# code returns row count from SQL stored procedure but all data is null

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related