小功能实现（存储过程，类，映射）-优快云博客

本文介绍了使用C#进行数据库操作的方法，包括WebConfig中配置连接字符串、执行存储过程、获取SqlDataReader及前台数据绑定等步骤，并展示了如何通过存储过程实现高效的数据检索。

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

转载于:https://www.cnblogs.com/bfy-19/archive/2012/04/23/2466186.html