DB連線 時代的眼淚
protected SqlConnection GetConnection()
{
return new SqlConnection(_WebSiteConnectionString);
}
//避免因DB修改後對不上欄位錯誤
protected bool HasColumn(SqlDataReader reader, string columnName)
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i).Equals(columnName, StringComparison.OrdinalIgnoreCase))
return true;
}
return false;
}
protected List<T> ExecSqlReader<T>(string sqlString) where T : new()
{
var result = new List<T>();
using (var con = GetConnection())
{
using (var cmd = new SqlCommand(sqlString, con))
{
con.Open();
using (var reader = cmd.ExecuteReader())
{
var props = typeof(T).GetProperties();
while (reader.Read())
{
T obj = new T();
foreach (var prop in props)
{
if (!HasColumn(reader, prop.Name) || reader[prop.Name] is DBNull)
continue;
prop.SetValue(obj, Convert.ChangeType(reader[prop.Name], prop.PropertyType));
}
result.Add(obj);
}
}
con.Close();
con.Dispose();
}
}
return result;
}
protected int ExecSqlNonQuery(string sqlString, Dictionary<string, object> parameters = null)
{
using (var con = GetConnection())
using (var cmd = new SqlCommand(sqlString, con))
{
if (parameters != null)
{
foreach (var param in parameters)
{
cmd.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
}
}
con.Open();
return cmd.ExecuteNonQuery(); // 回傳受影響的筆數
}
}
protected int ExecSqlNonQueryWithRollBack(string sqlString, SqlConnection con, Dictionary<string, object> parameters = null)
{
using (var cmd = new SqlCommand(sqlString, con))
{
if (parameters != null)
{
foreach (var param in parameters)
{
cmd.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
}
}
return cmd.ExecuteNonQuery();
}
}
留言
張貼留言