首先,我创建了与数据库集成的类(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';
这是TicketType类
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;
}
}
我创建存储过程的目的是使我们的网站效率更高,而不是使用select语句
这是一个谬论。SELECT语句的效率不比存储过程低。以前很久以前就是这样,但是今天使用参数化查询,无论哪种方式,您都可以获得相同的性能。
无论如何,问题在于方法要求返回a 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()
到调用此方法的任何位置。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句