如果想把 DataReader 的資料丟出function之外,
似乎是個難題.
一般來說都是用 DataTable封存資料後當作物件return出去.
但如果想使用 DataReader 存取呢?
把讀取資料的部分封裝成function.
做法如下.
把執行SQL的部分封裝成 ExecuteReader.
protected void ExecuteReader(string cmdStr, CommandType cmdType, List<SqlParameter> sqlParams, string connectionString, Action<SqlDataReader> action, int cmdTimeout = 30)
{
try
{
using (SqlConnection conn = new SqlConnection())
using (SqlCommand cmd = new SqlCommand())
{
if (String.IsNullOrEmpty(connectionString))
{
connectionString = _muchnewdb;
}
conn.ConnectionString = connectionString;
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
cmd.CommandTimeout = cmdTimeout;
cmd.Connection = conn;
if (sqlParams != null && sqlParams.Count > 0)
cmd.Parameters.AddRange(sqlParams.ToArray());
conn.Open();
using (var rd = cmd.ExecuteReader())
{
while (rd.Read())
{
action(rd);
}
}
}
}
catch (Exception ex)
{
var sb = new StringBuilder();
sb.AppendLine("[ExecuteReader Error]");
sb.AppendLine(connectionString);
sb.AppendLine(cmdStr);
foreach (var sqlParam in sqlParams)
{
sb.AppendLine(string.Format("{0}, {1}", sqlParam.ParameterName, sqlParam.Value));
}
throw ex;
}
}
以下為叫用.
public List<RtnObj> GetData(int para1, DateTime para2)
{
var rtnObj = new List<RtnObj>();
var sqlParams = new List<SqlParameter>();
sqlParams.Add(new SqlParameter("@Para1", para1));
sqlParams.Add(new SqlParameter("@Para2", para2));
this.ExecuteReader("select Para1, Para2 from table1", CommandType.StoredProcedure, sqlParams, "connectString", sdr =>
{
rtnObj.Add(new RtnObj
{
Para1 = (int)sdr["Para1"],
Para2 = DateTime.Parse(sdr["Para2"].ToString()),
});
});
return rtnObj;
}