小功能实现（存储过程，类，映射）
1.WebConfig配置
public static string connectionstring = ConfigurationSettings.AppSettings["MAIN"].ToString();//连接数据库
2.得到SqlDataReader
public static SqlDataReader GetReader(string sq ,params SqlParameter[] cmdParms)
{
SqlCommand command = new SqlCommand(sq, Article.SqlHelper.sqlexc.con);
con.Open(); //不能关闭，在前台关闭
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in cmdParms)
{
command.Parameters.Add(param);
}
SqlDataReader reader = command.ExecuteReader();
return reader;
}
3.执行带参数的存储过程
/// <param name="cmdText">存储过程名</param>
/// <param name="cmdParms">参数数组</param>
public static bool produce(string cmdText, params SqlParameter[] cmdParms)
{
System.Data.SqlClient.SqlConnection conn = Article.SqlHelper.sqlexc.con;
System.Data.SqlClient.SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in cmdParms)
{
cmd.Parameters.Add(param);
}
try
{
SqlHelper.sqlexc.con.Open();
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlHelper.sqlexc.con.Close();
}
}
4.前台数据绑定泛型
SqlParameter[] para = new SqlParameter[3];
para[0] = new SqlParameter("@month",int.Parse(DropDownList2.SelectedValue));
para[1] = new SqlParameter("@year", int.Parse(DropDownList3.SelectedItem.Text.ToString().Trim()));
para[2] = new SqlParameter("@CityId", int.Parse(DropDownList1.SelectedValue));
List<hl> list_art = new List<hl>();
using (SqlDataReader reader = Article.SqlHelper.sqlexc.GetReader("hlsj", para))
{
while (reader.Read())
{
hl art = new hl();
art.input = reader["ProcessedInput"].ToString();
art.count = Convert.ToInt32(reader["sl"].ToString());
list_art.Add(art);
}
Article.SqlHelper.sqlexc.con.Close();
}
5.存储过程：得到搜索次数排名前一百的搜索内容[利用索引和分区可提高搜索效率]
CREATE PROCEDURE [souke].[hlsj]
(@month int,
@year int,
@CityId int)
AS
BEGIN
SET NOCOUNT ON;
select top 100 count(ProcessedInput) as sl,ProcessedInput from 表名where [month]=@month and [year]=@year and CityId=@CityId group by ProcessedInput order by sl desc
END
