数据访问层，完整的增，删，改，查

 

添加信息

/// <summary>
/// 添加新UseType
/// </summary>
/// <param name="useType">需要添加的内容</param>
/// <returns>返回受影响多少行</returns>
public int InsertUseType(UseType useType)
{
string cmdText = string.Format("insert into dbo.UseType (userTypeTo) values(@userTypeTo)");
SqlCommand command = new SqlCommand(cmdText);
command.CommandType = CommandType.Text;
SqlParameter[] sqlParameters = new SqlParameter[]
{
new SqlParameter("@userTypeTo",SqlDbType.NVarChar,30)
};

if (useType.UserTypeTo == null)
sqlParameters[0].Value = DBNull.Value;
else
sqlParameters[0].Value = useType.UserTypeTo;

command.Parameters.AddRange(sqlParameters);

return SQLDBHelper.ExecuteNonQuery(SQLDBHelper.ConnectionString, command);
}

 

更新信息

/// <summary>
/// 更新UseType信息
/// </summary>
/// <param name="useType">需要更新的UseType对象</param>
/// <returns>返回更新的行数</returns>
public int UpdateUseTypeById(UseType useType)
{
string cmdText = string.Format("update dbo.UseType set [userTypeTo] = @userTypeTo where [userTypeId] = @userTypeId");
SqlCommand command = new SqlCommand(cmdText);
SqlParameter[] sqlParameters = new SqlParameter[]
{
new SqlParameter("@userTypeTo",SqlDbType.NVarChar,30),
new SqlParameter("@userTypeId",SqlDbType.Int,4)

};

if (useType.UserTypeTo == null)
sqlParameters[0].Value = DBNull.Value;
else
sqlParameters[0].Value = useType.UserTypeTo;

sqlParameters[1].Value = useType.UserTypeId;

command.Parameters.AddRange(sqlParameters);
return SQLDBHelper.ExecuteNonQuery(SQLDBHelper.ConnectionString, command);
}

 

查询全部信息

/// <summary>
/// 获取所有UseType信息
/// </summary>
/// <returns>返回所有UseType信息列表</returns>
public List<UseType> GetUseTypeAll()
{
List<UseType> useTypeList = new List<UseType>();

string cmdText = "select * from dbo.UseType";
SqlCommand command = new SqlCommand(cmdText);
using (SqlDataReader dr = SQLDBHelper.ExecuteReader(SQLDBHelper.ConnectionString, command))
{
while (dr.Read())
{
UseType useType = new UseType();
useType.UserTypeId = dr.GetInt32(0);
if (dr[1] is System.DBNull)
useType.UserTypeTo = String.Empty;
else
useType.UserTypeTo = dr.GetString(1);

useTypeList.Add(useType);
}
}

return useTypeList;
}

 

根据Id获取信息

/// <summary>
/// 根据useTypeId获取useTypeId信息
/// </summary>
/// <param name="useTypeId">编号</param>
/// <returns>useTypeId信息</returns>
public UseType GetUseTypeByID(int useTypeId)
{
UseType useType = new UseType();
string cmdText = string.Format("select * from dbo.UseType where userTypeId=@userTypeId");
SqlCommand command = new SqlCommand();
command.CommandText = cmdText;
SqlParameter[] sqlParameters = new SqlParameter[]
{
new SqlParameter("@userTypeId",SqlDbType.Int,4)
};
sqlParameters[0].Value = useTypeId;
command.Parameters.AddRange(sqlParameters);

using (SqlDataReader dr = SQLDBHelper.ExecuteReader(SQLDBHelper.ConnectionString, command))
{
if (dr.Read())
{
useType.UserTypeId = dr.GetInt32(0);
if (dr[1] is System.DBNull)
useType.UserTypeTo = String.Empty;
else
useType.UserTypeTo = dr.GetString(1);
}
}
return useType;
}

 

根据Id删除信息

/// <summary>
/// 根据useTypeId删除信息
/// </summary>
/// <param name="useTypeId">编号</param>
/// <returns>返回删除行数</returns>
public int DeleteUseType(int useTypeId)
{
string cmdText = "Delete dbo.UseType where userTypeId=@userTypeId";
SqlCommand command = new SqlCommand(cmdText);

SqlParameter[] sqlParameters = new SqlParameter[]
{
new SqlParameter("@userTypeId",SqlDbType.Int,4)
};

sqlParameters[0].Value = useTypeId;
command.Parameters.AddRange(sqlParameters);

return SQLDBHelper.ExecuteNonQuery(SQLDBHelper.ConnectionString, command);
}

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/sogo/archive/2010/04/17/DAL.html