110,534
社区成员
发帖
与我相关
我的任务
分享
有两处错误:
1、sql语句,inner join 表,怎么会是c.GradeId = 变量名?
2、SqlParamater para 变量体,声明变量@ClassName是多余的,完全没有用到。
实际上只需要将@GradeId变量定义一下就可以:
/* Frm窗体 */
int gradeId = cboGrades.SelectedValue;
string className = txtClassName.Text.Trim();
string sqlStr = @"
select ClassId, ClassName, GradeName, Remark"
sqlStr +=" from ClassInfo a
inner join GradeInfo b on a.GradeId = b.GradeInfo"
sqlStr +=" where 1=1"
SqlParameter[] paras =
{
new SqlParameter("@GradeId", SqlDbType.Int),
new SqlParameter("@ClassName",SqlDbType.NVarChar,20)
};
if(gradeId >0)
{
sqlStr +=" and a.GradeId = @GradeId";
paras[0].Value = gradeId;
}
if(!string.IsNullOrEmpty(className))
{
sqlStr += " and ClassName like '%' + @ClassName + '%'";
paras[1].Value = className;
}
//调用DAL层方法
DataTable dtClasses = SqlHelper.GetDataTable(sqlStr, CommandType.Text, paras);
/* SqlHelper DAL层方法类 */
public class SqlHelper
{
//定义连接对象
private static SqlConnection _conn = null;
/// <summary>
/// SqlDataAdapter执行查询方法,支持存储过程
/// </summary>
/// <param name="cmdText">StoredProcedure,512/TableDirect,4/Text,1(默认)</param>
/// <param name="cmdType">sql对象</param>
/// <param name="para">参数组</param>
/// <returns>DataTable数据集</returns>
public static DataTable GetDataTable(string cmdText, CommandType cmdType, params SqlParameter[] para)
{
_conn = new SqlConnection(ConnContext);
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(cmdText, _conn);
if (para != null)
sda.SelectCommand.Parameters.AddRange(para);
sda.SelectCommand.CommandType = cmdType;
sda.Fill(dt);
sda.SelectCommand.Parameters.Clear();
return dt;
}
}