public static T Find <T>(int id) where T : class, new() { // validate id if (id <= 0) { return(null); } // init cache key var cacheKey = "{0}find:id:{1}".FormatWith(GeneratePreCacheKey(typeof(T).Name), id); // get value from cache if (ContextCache.Get <T>(cacheKey) != null) { return(ContextCache.Get <T>(cacheKey)); } // get data form database SqlDataReader dr = null; // init connection var conn = new SqlConnection(ConnectionString); try { // init sql command var command = new SqlCommand { Connection = conn, CommandText = CreateSelectByIdQuery <T>(), CommandType = CommandType.Text }; // add params command.Parameters.AddWithValue("@Id", id); // open connection conn.Open(); // excute dr = command.ExecuteReader(); // check reader if (dr.Read()) { // fill data var obj = FillObject <T>(dr); // insert cache ContextCache.Set(cacheKey, obj); // return object return(obj); } // return null return(default(T)); } finally { // close reader dr?.Close(); // close connection if (conn.State != ConnectionState.Closed) { conn.Close(); } } }
public static int Add <T>(T obj) { var type = obj.GetType(); var conn = new SqlConnection(ConnectionString); try { var command = new SqlCommand { Connection = conn, CommandText = CreateInsertQuery <T>(), CommandType = CommandType.Text }; var arrPropertyInfo = type.GetProperties(); foreach (var p in arrPropertyInfo) { // ignore id parameter if (p.Name.ToLower() != "id") { if (p.CanWrite & !p.PropertyType.IsEnum) { var objValue = p.GetValue(obj, null); command.Parameters.AddWithValue(p.Name, objValue ?? DBNull.Value); } if (p.CanWrite & p.PropertyType.IsEnum) { var objValue = p.GetValue(obj, null); command.Parameters.AddWithValue(p.Name, (int)objValue); } } } conn.Open(); var id = command.ExecuteScalar(); // check id if (id == null) { return(0); } // remove cache ContextCache.RemoveRegex("{0}find:id:".FormatWith(GeneratePreCacheKey(typeof(T).Name))); // return inserted id return(Convert.ToInt32(id)); } finally { // close connection if (conn.State != ConnectionState.Closed) { conn.Close(); } } }
public static int Update <T>(T obj) { if (obj == null) { return(0); } var type = obj.GetType(); var conn = new SqlConnection(ConnectionString); try { var command = new SqlCommand { Connection = conn, CommandText = CreateUpdateQuery <T>(), CommandType = CommandType.Text }; var arrPropertyInfo = type.GetProperties(); foreach (var p in arrPropertyInfo) { if (p.CanWrite & !p.PropertyType.IsEnum) { var objValue = p.GetValue(obj, null); command.Parameters.AddWithValue(p.Name, objValue ?? DBNull.Value); } if (p.CanWrite & p.PropertyType.IsEnum) { var objValue = p.GetValue(obj, null); command.Parameters.AddWithValue(p.Name, (int)objValue); } } conn.Open(); // excute var rowEffected = command.ExecuteNonQuery(); // check excute result if (rowEffected > 0) { // remove cache ContextCache.RemoveRegex("{0}find:id:".FormatWith(GeneratePreCacheKey(typeof(T).Name))); } // return row effected return(rowEffected); } finally { // close connection if (conn.State != ConnectionState.Closed) { conn.Close(); } } }
public static bool Remove <T>(string condition) { if (string.IsNullOrEmpty(condition)) { return(true); } var conn = new SqlConnection(ConnectionString); try { var command = new SqlCommand { Connection = conn, CommandText = CreateDeleteQuery <T>(condition), CommandType = CommandType.Text }; conn.Open(); // excute var rowEffected = command.ExecuteNonQuery(); // check excute result if (rowEffected > 0) { // remove cache ContextCache.RemoveRegex("{0}find:id:".FormatWith(GeneratePreCacheKey(typeof(T).Name))); // return success return(true); } // return fail return(false); } finally { // close connection if (conn.State != ConnectionState.Closed) { conn.Close(); } } }