时间戳

首页 > 解决方案 > C# 代码从 SQL 存储过程返回行数，但所有数据为空

c# - C# 代码从 SQL 存储过程返回行数，但所有数据为空

问题描述

我正在尝试从 C# 代码调用存储过程并返回数据列表。存储过程在 SQL 中运行良好。当我在 C# 中运行代码时，我得到了记录数，但所有数据都是空的。通过 SQL 运行存储过程时，我当前的结果是 192 行数据。当我在 C# 中调试时，我看到了 192 行，但它们都填充了空数据。我需要做什么？

存储过程

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# 第一次尝试

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#第二次尝试

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();
}

标签： c#sqlstored-procedures

解决方案

问题是您将 dt 转换为 dt.ToString() 并返回空列表。您必须遍历 dt(DataTable) 并转换为列表。请检查以下位置。

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;
}

推荐阅读