C#怎么把数据库里的东西放在集合里遍历输出?
DataSet ds = new DataSet();
public void BindCbo()
{
//数据库连接字符串
string connStr = "server=.;database=MySchool;UId=sa;Pwd=123456";
//创建连接
SqlConnection conn = new SqlConnection(connStr);
//sql语句
string sql = @"select Student.StudentNo as 学号,StudentName as 姓名,GradeName as 年级名,SubjectName as 科目名,StudentResult as 成绩 from Student
inner join Result on Student.StudentNo=Result.StudentNo
inner join Grade on Student.GradeId=Grade.GradeId
inner join [Subject] on Result.SubjectId=[Subject].SubjectId";
//实例化数据适配器
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
//填充数据
da.Fill(ds, "Student");
//实例对象
List<Student> StuList = new List<Student>();
}
我就不知道怎么写了 展开
给你个辅助类吧
model类 你自己改成你自己的 查询的列名你自己修改下即可
日志 ibLog 可以直接注释 你也可以改成你自己的日志 或者注释掉 不显示
有问题直接留言
public class IBDataBase
{
public virtual int ExecuteCommand(string safeSql)
{
return 0;
}
public virtual DataTable ExecuteSqlGetDataTable(string safeSql)
{
return null;
}
public virtual int ExecuteDropTable(string safeSql)
{
return 0;
}
}
//DBHelper.cs
public class IBMySQLDataBase : IBDataBase
{
//数据库连接属性,从config配置文件中获取连接字符串connectionString
public string ConnectionString { get; set; }
/// <summary>
/// 执行无参SQL语句,并返回执行记录数
/// </summary>
/// <param name="safeSql">sql字符串</param>
/// <returns>受影响的行数</returns>
public override int ExecuteCommand(string safeSql)
{
int res = 0;
if (string.IsNullOrEmpty(ConnectionString) == true)
{
return res;
}
MySqlConnection sqlConn = new MySqlConnection(ConnectionString);
try
{
sqlConn.Open();
}
catch (Exception es)
{
IBLogManager.Instance.LogException(es);
sqlConn.Close();
sqlConn.Dispose();
return res;
}
MySqlCommand cmd = new MySqlCommand(safeSql, sqlConn);
cmd.CommandTimeout = 600;
try
{
res = cmd.ExecuteNonQuery();
}
catch (Exception es)
{
IBLogManager.Instance.Log("SQL" + safeSql + es.StackTrace);
}
finally
{
cmd.Dispose();
sqlConn.Close();
sqlConn.Dispose();
}
return res;
}
/// <summary>
/// 执行SQL语句,并返回DataTable对象
/// </summary>
/// <param name="safeSql">SQL语句</param>
/// <returns>返回DataTable</returns>
public override DataTable ExecuteSqlGetDataTable(string safeSql)
{
DataTable dataTable = new DataTable();
if (string.IsNullOrEmpty(ConnectionString) == true)
{
return dataTable;
}
DataSet ds = new DataSet();
MySqlConnection sqlConn = new MySqlConnection(ConnectionString);
try
{
sqlConn.Open();
}
catch (Exception es)
{
IBLogManager.Instance.Log("SQL" + safeSql + es.StackTrace);
sqlConn.Close();
sqlConn.Dispose();
return dataTable;
}
MySqlCommand cmd = new MySqlCommand(safeSql, sqlConn);
cmd.CommandType = CommandType.Text;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(ds);
if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
{
dataTable = ds.Tables[0];
}
da.Dispose();
cmd.Dispose();
sqlConn.Close();
sqlConn.Dispose();
return dataTable;
}
}
public class DAL
{
private IBDataBase m_mySQLDataBaseMain = new IBMySQLDataBase() { ConnectionString = "Data Source=10.0.0.2;Initial Catalog=ib_sn_data;Persist Security Info=True;User ID=root;Password=root;Port=3306;Allow User Variables=True;" };
public IBDataBase GetDataBase()
{
return m_mySQLDataBaseMain;
}
public int ExecuteCommand(string strSQL)
{
int res = 0;
IBDataBase dataBase = GetDataBase();
if (dataBase == null)
{
return res;
}
try
{
res = dataBase.ExecuteCommand(strSQL);
}
catch (Exception es)
{
//IBLogManager.Instance.Log(IBError_Tag.ERROR_IBDBC.ToString() + "SQL" + strSQL + es.StackTrace, IBLogManager.LogLevel.IBS_Error);
}
return res;
}
public List<SNNoModel> GetSNNoModelList(string strSQL)
{
List<SNNoModel> currentTableList = new List<SNNoModel>();
IBDataBase dataBase = GetDataBase();
if (dataBase == null)
{
return currentTableList;
}
DataTable dataTable = dataBase.ExecuteSqlGetDataTable(strSQL);
if (dataTable != null)
{
foreach (DataRow item in dataTable.Rows)
{
SNNoModel tableNameItem = new SNNoModel();
try
{
tableNameItem.Id = int.Parse(item["id"].ToString());
tableNameItem.FullSNNo = item["FullSNNo"].ToString();
tableNameItem.SNWipe0 = item["SNWipe0"].ToString();
tableNameItem.Brand = item["Brand"].ToString();
}
catch (ArgumentException e)
{
IBLogManager.Instance.LogException(e);
}
catch (Exception e)
{
IBLogManager.Instance.LogException(e);
}
if (tableNameItem != null)
{
currentTableList.Add(tableNameItem);
}
}
}
return currentTableList;
}
public List<string> GetTableNamesFromDB(string strSQL, string colName)
{
List<string> currentTableList = new List<string>();
IBDataBase dataBase = GetDataBase();
if (dataBase == null)
{
return currentTableList;
}
DataTable dataTable = dataBase.ExecuteSqlGetDataTable(strSQL);
if (dataTable != null)
{
foreach (DataRow item in dataTable.Rows)
{
object tableNameItem = null;
try
{
tableNameItem = item[colName];
}
catch (ArgumentException e)
{
IBLogManager.Instance.LogException(e);
}
catch (Exception e)
{
IBLogManager.Instance.LogException(e);
}
if (tableNameItem != null)
{
currentTableList.Add(tableNameItem.ToString());
}
}
}
return currentTableList;
}
}
// 直接用datatable进行循环输出就可以
for (int i = 0; i < dt.Rows.count; i++) // 进行循环
{
string vlaue = dt.Rows[i]["字段名"].toString();
// value这个值可以输出
}