/// <summary>
/// 執行 store procedure (5m timeout)，並回傳結果
/// </summary>
/// <param name="procedureName"></param>
/// <returns>List of T</returns>
public List <T> execProcedureReturnData(string procedureName)
{
if (procedureName == null || procedureName.Length == 0)
{
return(null);
}
using (SqlConnection con = new SqlConnection(_connStr))
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procedureName;
cmd.CommandTimeout = 300;
if (_parameters != null)
{
foreach (KeyValuePair <string, object> item in _parameters)
{
cmd.Parameters.AddWithValue(item.Key, item.Value);
}
}
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
_list = DataTableExten.ToList <T>(dt).ToList();
}
}
return(_list);
}
}
/// <summary>
/// 依sql進行查詢 (30m timeout)
/// </summary>
/// <param name="sql">SQL Statement</param>
/// <returns>List of T</returns>
public List <T> queryWithParameters(string sql)
{
if (sql == null || sql.Length == 0)
{
return(null);
}
if (_parameters == null)
{
throw new ArgumentNullException("parameters required !");
}
using (SqlConnection con = new SqlConnection(_connStr))
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
cmd.CommandTimeout = 1800;
if (_parameters != null && _parameters.Count > 0)
{
foreach (KeyValuePair <string, object> item in _parameters)
{
cmd.Parameters.AddWithValue(item.Key, item.Value);
}
}
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
if (dt != null && dt.Rows.Count > 0)
{
_list = DataTableExten.ToList <T>(dt).ToList();
}
}
return(_list);
}