一、在有帮助类DbHelperSQL的时候
1、下为其中返回SqlDataReader的方法
/// <summary>
/// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
} }
2、三层DAL层方法
/// <summary>
/// 把读出的数据转换成List
/// </summary>
public List<int> GetUserList(int hid)
{
string sql = " select V_UserID from UserVoters where V_HeadlineID=" + hid + "";
SqlDataReader dr = DbHelperSQL.ExecuteReader(sql);
List<UserVotersModel> plist = new List<UserVotersModel>();
while (dr.Read())
{
UserVotersModel uvm = new UserVotersModel();
uvm.V_UserID = int.Parse(dr["V_UserID"].ToString());
plist.Add(uvm);
}
List<int> list = new List<int>();
for (int i = ; i < plist.Count; i++)
{
list.Add(int.Parse(plist[i].V_UserID.ToString()));
}
return list;
}
3、把List当参数放到sql语句里的方法
/// <summary>
/// 获得未投票用户列表
/// </summary>
public DataSet GetNoPersonList(List<int> list)
{
StringBuilder strSql = new StringBuilder();
if (list != null && list.Count > )
{
string listMes = string.Empty;
foreach (int str in list)
{
listMes += "'" + str + "',";
}
strSql.Append("select U_Uid,U_Name,U_Sex,U_Phone,U_QQ from dbo.UsersInfo where U_ID not in(" + listMes.TrimEnd(',') + ") and U_Level=4 and U_Name='' ORDER BY U_ID DESC ");
return DbHelperSQL.Query(strSql.ToString());
}
else
{
strSql.Append(" select U_Uid,U_Name,U_Sex,U_Phone,U_QQ from dbo.UsersInfo where U_Level=4 and U_Name='' ORDER BY U_ID DESC ");
return DbHelperSQL.Query(strSql.ToString());
}
}
二、没有帮助类直接读取数据库
/// <summary>
/// 读取数据库的信息,并转换成List
/// </summary>
public List<MailData> GetExpiredLicense()
{
string connectionString = "Data Source=01;Initial Catalog=Music;Persist Security Info=True;User ID=Musicname;Password=123456";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
string sql = string.Format( @" select * from Tale" ); SqlDataAdapter da = new SqlDataAdapter(sql, connectionString);
DataSet ds = new DataSet();
da.Fill(ds);
List<MailData> list = new List<MailData>(); //MailData为要查询表的实体类
foreach (DataRow item in ds.Tables[].Rows)
{
list.Add(MailData.ConvertRow(item));
}
return list;
}