阿輝的零碎筆記

 把執行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;
}