调用方法
public bool Add(Model.CarAddress item){
return DataAccess.Add<Model.CarAddress>(item, "id") > 0 ? true : false;}public bool Update(Model.CarAddress item){
return DataAccess.Update<Model.CarAddress>(item, "id") > 0 ? true : false;}public Model.CarAddress GetItem(int Id){
return DataAccess.Get<Model.CarAddress>(Id, "id");}public bool Remove(int Id){
return DataAccess.Delete<Model.CarAddress>(Id, "id");}public List<Model.CarAddress> Get(){
return DataAccess.GetListOrderBy<Model.CarAddress>("sort desc,id desc");}public List<Model.CarAddress> GetPager(int index, int size, out int total){
total = DataAccess.GetCount<Model.CarAddress>("id");
return DataAccess.GetPage_List<Model.CarAddress>(index, size, "id", "sort desc,id desc");
通过反射实现Access实现数据库操作语句生成
public static class DataAccess{
//public static DataAccess() { GC.Collect(); }
#region 更新
/// <summary>
/// 插入一条记录
/// </summary>
/// <param name="t">要插入的对象</param>
/// <param name="primaryKey">主键</param>
/// <returns></returns>
public static int Add<T>(T t, string primaryKey) where T : new()
{
Type type = typeof(T);
PropertyInfo[] propertys = type.GetProperties();
OleDbParameter[] p = new OleDbParameter[propertys.Length - 1];
string fields = null;
string values = null;
int i = 0;
foreach (PropertyInfo pi in propertys)
{
if (pi.Name.ToLower() != primaryKey.ToLower())
{
fields += "[" + pi.Name + "],";
values += "@" + pi.Name + ",";
if (pi.GetValue(t, null) == null || pi.GetValue(t, null).ToString() == new DateTime().ToString())
{
p[i] = new OleDbParameter("@" + pi.Name, DBNull.Value);
}
else
{
if (pi.GetValue(t, null).GetType().Name == typeof(System.DateTime).Name)
{
p[i] = new OleDbParameter("@" + pi.Name, pi.GetValue(t, null).ToString());
}
else
{
p[i] = new OleDbParameter("@" + pi.Name, pi.GetValue(t, null));
}
}
i++;
}
}
fields = fields.TrimEnd(',');
values = values.TrimEnd(',');
string sql = "insert into [" + type.Name + "](" + fields + ")values(" + values + ");";
return (new DbHelp().Save(OleHelper.connString, CommandType.Text, sql, p));
}
/// <summary>
/// 删除一条记录
/// </summary>
/// <param name="primaryKey">主键</param>
/// <param name="id">主键的值</param>
/// <returns></returns>
public static bool Delete<T>(int id, string primaryKey) where T : new()
{
T t = new T();
Type type = typeof(T);
string sql = "delete from [" + type.Name + "] where [" + primaryKey + "]=@id";
OleDbParameter[] p = new OleDbParameter[1];
p[0] = new OleDbParameter("@id", id);
if (new DbHelp().Save(OleHelper.connString, CommandType.Text, sql, p) > 0)
return true;
else
return false;
}
/// <summary>
/// 删除一条记录
/// </summary>
/// <param name="primaryKey">主键</param>
/// <param name="id">主键的值</param>
/// <returns></returns>
public static int Drop<T>(string ids, string primaryKey) where T : new()
{
T t = new T();
string sql = "delete from [" + typeof(T).Name + "] where [" + primaryKey + "] in(" + ids + ")";
return new DbHelp().Save(OleHelper.connString, CommandType.Text, sql, null);
}
/// <summary>
/// 批量删除
/// </summary>
/// <param name="sql">参数化的SQL语句</param>
/// <param name="OleDbParameter">参数</param>
/// <returns></returns>
public static int Delete(string sql, params DbParameter[] OleDbParameter)
{
return new DbHelp().Save(OleHelper.connString, CommandType.Text, sql, OleDbParameter);
}
/// <summary>
/// 更新一条记录
/// </summary>
/// <param name="t">要更新的对象</param>
/// <param name="primaryKey">主键</param>
/// <returns></returns>
public static int Update<T>(T t, string primaryKey) where T : new()
{
Type type = typeof(T);
string sql = "update [" + type.Name + "] set ";
PropertyInfo[] propertys = type.GetProperties();
PropertyInfo primaryKeyProperty = null;
OleDbParameter[] p = new OleDbParameter[propertys.Length];
int i = 0;
foreach (PropertyInfo pi in propertys)
{
if (pi.Name.ToLower() != primaryKey.ToLower())
{
sql += "[" + pi.Name + "]=@" + pi.Name + ",";
if (pi.GetValue(t, null) == null || pi.GetValue(t, null).ToString() == new DateTime().ToString())
{
p[i] = new OleDbParameter("@" + pi.Name, DBNull.Value);
}
else
{
if (pi.GetValue(t, null).GetType().Name == typeof(System.DateTime).Name)
{
p[i] = new OleDbParameter("@" + pi.Name, pi.GetValue(t, null).ToString());
}
else
{
p[i] = new OleDbParameter("@" + pi.Name, pi.GetValue(t, null));
}
}
i++;
}
else
{
primaryKeyProperty = pi;
}
}
if (primaryKeyProperty.GetValue(t, null) == null || primaryKeyProperty.GetValue(t, null).ToString() == new DateTime().ToString())
{
p[propertys.Length - 1] = new OleDbParameter("@" + primaryKeyProperty.Name, DBNull.Value);
}
else
{
if (primaryKeyProperty.GetValue(t, null).GetType().Name == typeof(System.DateTime).Name)
{
p[propertys.Length - 1] = new OleDbParameter("@" + primaryKeyProperty.Name, primaryKeyProperty.GetValue(t, null).ToString());
}
else
{
p[propertys.Length - 1] = new OleDbParameter("@" + primaryKeyProperty.Name, primaryKeyProperty.GetValue(t, null));
}
}
sql = sql.TrimEnd(',');
sql += " where ";
sql += "[" + primaryKey + "]=@" + primaryKey;
return new DbHelp().Save(OleHelper.connString, CommandType.Text, sql, p);
}
/// <summary>
/// 批量更新
/// </summary>
/// <param name="sql">参数化的SQL语句</param>
/// <param name="OleDbParameter">参数</param>
/// <returns></returns>
public static int Update(string sql, params DbParameter[] OleDbParameter)
{
return new DbHelp().Save(OleHelper.connString, CommandType.Text, sql, OleDbParameter);
}
#endregion
#region 抽取单条记录
/// <summary>
/// 抽出一条记录
/// </summary>
/// <param name="primaryKey">主键</param>
/// <param name="id">主键的值</param>
/// <returns></returns>
public static T Get<T>(int id, string primaryKey) where T : new()
{
T t = new T();
Type type = typeof(T);
string sql = "select top 1 " + GetCollectionsColumnName<T>() + " from [" + type.Name + "] where [" + primaryKey + "]=@id";
OleDbParameter[] p = new OleDbParameter[1];
p[0] = new OleDbParameter("@id", id);
DataSet ds = new DbHelp().ReadDataSet(OleHelper.connString, CommandType.Text, sql, p);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return ConvertToModel<T>(ds.Tables[0]);
}
else
return default(T);
}
