public void CreateAd(Ad ad)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@title",100,ad.Title),
SqlUtilities.GenerateInputNVarcharParameter("@path",100,ad.Path),
SqlUtilities.GenerateInputNVarcharParameter("@url",100,ad.Url),
SqlUtilities.GenerateInputIntParameter("@sort_num",ad.SortNum),
SqlUtilities.GenerateInputNVarcharParameter("@create_time",200,ad.CreateTime)
};
string sql = "INSERT INTO ads(title, path, url, create_time, sort_Num)VALUES(@title, @path, @url, @create_time, @sort_num)";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public Ad GetAdbyId(int id)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@Id",id)
};
Ad ad = null;
string sql = "SELECT id, title, path, url, create_time, sort_num FROM ads WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ad = new Ad();
ad.Id = dr.GetInt32(0);
ad.Title = dr.GetString(1);
ad.Path = dr.GetString(2);
ad.Url = dr.GetString(3);
ad.CreateTime = dr.GetDateTime(4);
ad.SortNum = dr.GetInt32(5);
}
}
return ad;
}
public List<Ad> GetAdSortByNum()
{
List<Ad> result = new List<Ad>();
string sql = "SELECT id, title, path, url, create_time, sort_num FROM ads ORDER BY sort_num DESC,id ASC";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Ad ad = new Ad();
ad.Id = dr.GetInt32(0);
ad.Title = dr.GetString(1);
ad.Path = dr.GetString(2);
ad.Url = dr.GetString(3);
ad.CreateTime = dr.GetDateTime(4);
ad.SortNum = dr.GetInt32(5);
result.Add(ad);
}
}
return result;
}
public void UpdateAd(Ad ad)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", ad.Id),
SqlUtilities.GenerateInputNVarcharParameter("@title",100,ad.Title),
SqlUtilities.GenerateInputNVarcharParameter("@path",100,ad.Path),
SqlUtilities.GenerateInputNVarcharParameter("@url",100,ad.Url),
SqlUtilities.GenerateInputIntParameter("@sort_num",ad.SortNum),
SqlUtilities.GenerateInputNVarcharParameter("@create_time",200,ad.CreateTime)
};
string sql = "UPDATE ads SET title = @title, path = @path, url = @url, create_time = @create_time, sort_num = @sort_num WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public static void UpdateAd(Ad ad)
{
dal.UpdateAd(ad);
}
public static void CreateAd(Ad ad)
{
dal.CreateAd(ad);
}