11.17三层架构DAL层创建增删改查方法

创建查询方法

var sql = "select * from ClassInfo where name like @name";

SqlParameter sqlParameters = new SqlParameter("@name",$"%{name}%");

 DataTable table = SqlHelper.Query(sql,sqlParameters);

 List<ClassInfo> list = new List<ClassInfo>();

 foreach (DataRow row in table.Rows)
 {
var cls = new ClassInfo()
{
Id = Convert.ToInt32(row["Id"]),
 Name=Convert.ToString(row["Name"]),
Comment=Convert.ToString(row["Comment"])
 };
 list.Add(cls);
}
 return list;
}

根据ID查询方法：

var sql = "select * from classinfo where id=@id";
 SqlParameter[] sqlParameters = new SqlParameter[]
{
 new SqlParameter("@id",id)
};
SqlDataReader reader = SqlHelper.Reader(sql, sqlParameters);
 ClassInfo info = null;
if (reader.Read())
 {
info = new ClassInfo()
{
 Id = Convert.ToInt32(reader["Id"]),
 Name = Convert.ToString(reader["Name"]),
 Comment = Convert.ToString(reader["Comment"])
 };
 }
reader.Close();
 return info;

添加方法：

var sql = "insert into ClassInfo values(@Name,@Comment)";
SqlParameter[] sqlParameter = new SqlParameter[]
{
new SqlParameter("@Name",info.Name),
 new SqlParameter("@Comment",info.Comment)
 };
 return SqlHelper.NonQuery(sql,sqlParameters: sqlParameter);

删除方法

//调用存储过程名字和参数列表
var sql = "del_class";//存储过程
SqlParameter[] sqlParameters = new SqlParameter[]
{
 new SqlParameter("@id",id)
 };//参数列表
return SqlHelper.NonQuery(sql,CommandType.StoredProcedure, sqlParameters);

 

修改方法：

var sql = "Update classinfo set name=@name ,comment=@comment where id=@id";
 SqlParameter[] sqlParameter = new SqlParameter[]
{
 new SqlParameter("@Id",info.Id),
 new SqlParameter("@Name",info.Name),
 new SqlParameter("@Comment",info.Comment)
};
return SqlHelper.NonQuery(sql, sqlParameters:sqlParameter);