如何与网站中的存储过程集成(ASP.NET)

Question

首先，我创建了与数据库集成的类(SQL Server(，然后我想与我在SQL Server中创建的存储过程连接(我创建存储过程的目的是使我们的网站更高效，而不是使用select语句(

public class TicketTypeDB
{
public static string conStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
public static List<TicketType> getTicketByCountry(string country)
{
SqlConnection con = new SqlConnection(conStr);
try
{
SqlCommand command = new SqlCommand("usp_getTicket");
con.Open();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "usp_getTicket";
command.Connection = new SqlConnection(conStr);
command.Parameters.Add(new SqlParameter("@country", System.Data.SqlDbType.VarChar, 50, "country"));
command.Connection = con;
int i = command.ExecuteNonQuery();
}
finally
{
con.Close();
}
}
}

这是我的存储过程代码，它在 SSMS 中工作正常

create procedure usp_getTicket
(@country varchar(50))
as
begin
select 
TicketType.type, TicketType.description, TicketType.price, 
Attraction.country 
from 
TicketType 
inner join 
Attraction on TicketType.orgEmail = Attraction.orgEmail
where 
country = @country
end
exec usp_getTicket 'singapore';

这是票证类型类

public class TicketType
{
public string TicketID { get; set; }
public string Type { get; set; }
public string Description { get; set; }
public double Price { get; set; }
public Attraction Attraction { get; set; }
public List<Attraction> attraction { get; set; }
public TicketType()
{
attraction = new List<Attraction>();
}
public override string ToString()
{
return TicketID + " " + Type + " " + Description + " " + Price;
}
}

Answer 1

我创建存储过程的目的是使我们的网站更高效，而不是使用 select 语句

这是一个谬论。SELECT 语句的效率不低于存储过程。很久以前都是如此，但是今天使用参数化查询，无论哪种方式，您都可以获得相同的性能。

无论如何，问题是该方法声称返回List<TicketType>，但没有兑现承诺。根本没有return声明。

我建议这种模式：

public class TicketTypeDB
{
//Make this PRIVATE, which will ensure ALL db access goes through this class
private static string conStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
public static IEnumerable<TicketType> getTicketByCountry(string country)
{
using (var con = new SqlConnection(conStr))
using (var command = new SqlCommand("usp_getTicket", con))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("@country", System.Data.SqlDbType.VarChar, 50).Value = country;
using (var reader = command.ExecuteDataReader())
{
while (reader.Read())
{
yield return new TicketType() {
//The `TicketType` class in the question isn't shared, so I have to guess here.
//Assign fields to that class based on reader columns. Example:
//Type = reader["type"],
//Description = reader["description"],
//Price = reader["Price"],
//Country = reader["country"]
};
}
reader.Close();
}
}
}
}

如果你真的需要一个列表(提示：你通常不需要(，你可以.ToList()附加到你调用此方法的任何位置。

Answer 2

您需要使用传入的国家/地区字符串的 VALUE，而不是文字值"country"。这样的事情应该有效。

command.Parameters.Add(new SqlParameter("@country", System.Data.SqlDbType.VarChar, 50, country))