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();
}
}
}
