c# 自定义字段对应数据库的基类与反射赋值

来源：互联网发布：淘宝收藏大师怎么设置编辑：程序博客网时间：2024/05/19 14:52

1、属性类

public enum DataType
{
Incrementing = 0,//自增
CommonField//普通字段
}

public class RSFDBFieldAttribute : Attribute
{
public RSFDBFieldAttribute(string FieldName)
{
this.DBFieldName = FieldName;
}

public RSFDBFieldAttribute(string FieldName, DataType dt)
{
this.DBFieldName = FieldName;
this.DBFieldType = dt;
}

public string DBFieldName
{
get { return _DBFieldName; }
set { _DBFieldName = value; }
}
private string _DBFieldName;

public DataType DBFieldType
{
get { return _DBFieldType; }
set { _DBFieldType = value; }
}
private DataType _DBFieldType = DataType.CommonField;
}

------------------------------

2、数据库对应的基类

public class RSFDBObject
{
public string TableName
{
get { return _TableName; }
set { _TableName = value; }
}
public string _TableName = "";

[RSFDBField("ID", DataType.Incrementing)]
public int ID
{
get { return _ID; }
set { _ID = value; }
}
private int _ID;

[RSFDBField("GUIDEX")]
public string GUID
{
get { return _GUID; }
set { _GUID = value; }
}
private string _GUID = Regex.Replace(Guid.NewGuid().ToString(), @"\{|-", "");

[RSFDBField("Version")]
public int Version
{
get { return _Version; }
set { _Version = value; }
}
private int _Version = 1;

[RSFDBField("VersionFlag")]
public int VersionFlag
{
get { return _VersionFlag; }
set { _VersionFlag = value; }
}
private int _VersionFlag = 0;

public bool Insert()
{
Type type = this.GetType();
string strSQLText = "INSERT INTO {0}({1}) VALUES({2})";
StringBuilder sbField = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
foreach (PropertyInfo pInfo in type.GetProperties())
{
foreach (RSFDBFieldAttribute attr in Attribute.GetCustomAttributes(pInfo))
{
if (attr.GetType() == typeof(RSFDBFieldAttribute) && attr.DBFieldType != DataType.Incrementing)
{
sbField.Append(attr.DBFieldName + ",");
sbValues.Append(DataTypeHandle(pInfo.GetValue(this, null)));
}
//else
//{
// sbField.Append(attr.DBFieldName + ",");
// sbValues.Append("NULL,");
//}
}
}
strSQLText = string.Format(strSQLText, TableName, sbField.Remove(sbField.Length - 1, 1), sbValues.Remove(sbValues.Length - 1, 1));
Debug.WriteLine(strSQLText);
return ConstEnvironment.Instance.DBAccess.ExecuteNonQuery(strSQLText);
}

public bool Create()
{
return this.Insert();
}

public string DataTypeHandle(Object obj)
{
string value = "";
if (obj == null)
return value;
string typeName = obj.GetType().Name;
switch (typeName)
{
case "String":
value = string.Format("'{0}',", obj.ToString());
break;
case "Int32":
case "Double":
value = string.Format("{0},", obj.ToString());
break;
case "DateTime":
value = string.Format("{0},", ((DateTime)obj).ToOADate());
break;
}
if (string.IsNullOrEmpty(value))
{
if (obj.GetType().IsEnum)//枚举类型
value = string.Format("{0},", ((int)obj).ToString());
}
return value;
}

public string DataTypeHelper(Object obj)
{
string value = "";
if (obj == null)
return value;
string typeName = obj.GetType().Name;
switch (typeName)
{
case "String":
value = string.Format("'{0}'", obj.ToString());
break;
case "Int32":
case "Double":
value = string.Format("{0}", obj.ToString());
break;
case "DateTime":
value = string.Format("{0}", ((DateTime)obj).ToOADate());
break;
}
if (string.IsNullOrEmpty(value))
{
if (obj.GetType().IsEnum)//枚举类型
value = string.Format("{0}", ((int)obj).ToString());
}
return value;
}

public void Delete()
{
string strSQLText = string.Format("DELETE {0} WHERE ID = {1}", TableName, this.ID);
ConstEnvironment.Instance.DBAccess.ExecuteNonQuery(strSQLText);
}

public bool Update(string FieldName)
{
//获得相应属性的值
string value = this.GetType().InvokeMember(FieldName, System.Reflection.BindingFlags.GetProperty, null, this, null).ToString();
string strSQLText = string.Format("UPDATE {0} SET {1} = '{2}'", TableName, FieldName, value);
return ConstEnvironment.Instance.DBAccess.ExecuteNonQuery(strSQLText);
}

public bool UpdateToDB(string FieldName)
{
return this.Update(FieldName);
}

public void Update()
{
Type type = this.GetType();
string strSQLText = "UPDATE {0} SET {1}";
StringBuilder sbFieldAndValue = new StringBuilder();
foreach (PropertyInfo pInfo in type.GetProperties())
{
foreach (RSFDBFieldAttribute attr in Attribute.GetCustomAttributes(pInfo))
{
if (attr.GetType() == typeof(RSFDBFieldAttribute) && pInfo.Name.ToUpper() != "ID")
{
sbFieldAndValue.AppendFormat("{0} = {1},", pInfo.Name, DataTypeHelper(pInfo.GetValue(this, null)));
}
}
}
strSQLText = string.Format(strSQLText, TableName, sbFieldAndValue);
Debug.WriteLine(strSQLText);
ConstEnvironment.Instance.DBAccess.ExecuteNonQuery(strSQLText);
}

public void Query()
{
string strSQLText = string.Format("SELECT * FROM {0} WHERE GUIDEX = '{1}'", _TableName, this.GUID);
string strError = "";
DataTable dt = ConstEnvironment.Instance.DBAccess.Query(strSQLText, ref strError);
Debug.Assert(dt.Rows.Count != 0);
DataRow dr = dt.Rows[0];
Type type = this.GetType();
foreach (PropertyInfo pInfo in type.GetProperties())
{
foreach (RSFDBFieldAttribute attr in Attribute.GetCustomAttributes(pInfo))
{
if (!dr.IsNull(attr.DBFieldName) && attr.GetType() == typeof(RSFDBFieldAttribute))
{
pInfo.SetValue(this, dr[attr.DBFieldName], null);//赋值
}
}
}
}
}

3、数据库操作类

public class ConstEnvironment
{
/// <summary>
/// 单件实例
/// </summary>
public static ConstEnvironment Instance
{
get
{
if (_Instance == null)
_Instance = new ConstEnvironment();
return ConstEnvironment._Instance;
}
//set { ConstantSystmeEnvironment._Instance = va
}
private static ConstEnvironment _Instance;

/// <summary>
/// 数据库访问
/// </summary>
public SQLServerHelper DBAccess
{
get
{
if (_DBAccess == null)
_DBAccess = new SQLServerHelper();
return _DBAccess;
}
//set { _DBH = value; }
}
private SQLServerHelper _DBAccess;
/// <summary>
/// 登录用户
/// </summary>
public UserInfo LoginUser
{
get { return _LoginUser; }
set { _LoginUser = value; }
}
private UserInfo _LoginUser;

public void CloseDataBase()
{
DBAccess.CloseConnection();
}
}

-----------------

public class SQLServerHelper
{
/// <summary>
/// 构造函数
/// </summary>
public SQLServerHelper()
{
}

/// <summary>
/// 数据库连接
/// </summary>
public SqlConnection Conn
{
get
{
if (_conn == null || _conn.State == ConnectionState.Closed)//暂时只考虑关闭的状态
{
_conn = this.GetConnection();
_conn.Open();
}
return _conn;
}
set { _conn = value; }
}
private SqlConnection _conn;

/// <summary>
/// 获取连接
/// </summary>
/// <returns></returns>
private SqlConnection GetConnection()
{
string conStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
return new SqlConnection(conStr);
}

private void WirteLog(string sql, string errorMsg)
{
File.WriteAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "log.txt"), sql + " " + errorMsg , Encoding.UTF8);
}

/// <summary>
/// 执行非查询方法
/// </summary>
/// <param name="strSQLText"></param>
/// <returns></returns>
public bool ExecuteNonQuery(string strSQLText)
{
SqlCommand cmd = new SqlCommand(strSQLText, Conn);
try
{
int rowCount = cmd.ExecuteNonQuery();
Debug.WriteLine(rowCount);
return true;
}
catch (SqlException e)
{
Debug.Assert(false, e.Message);
this.WirteLog(strSQLText, e.Message);
}
return false;
}

public bool Execute(string strSQLText, ref string strError)
{
SqlCommand cmd = new SqlCommand(strSQLText, Conn);
try
{
int rowCount = cmd.ExecuteNonQuery();
Debug.WriteLine(rowCount);
return true;
}
catch (SqlException e)
{
strError = e.Message;
Debug.Assert(false, e.Message);
WirteLog(strSQLText, e.Message);
}
return false;
}

public SqlDataReader Query(string strSQLText)
{
SqlCommand cmd = new SqlCommand(strSQLText, Conn);
return cmd.ExecuteReader();
}

public DataTable Query(string strSQLText, ref string strError)
{
DataSet ds = new DataSet();
try
{
SqlDataAdapter da = new SqlDataAdapter(strSQLText, Conn);
da.Fill(ds);
}
catch (SqlException e)
{
strError = e.Message;
return null;
}
return ds.Tables[0];
}

public DataTable GetDataTable(string strSQLText)
{
SqlDataAdapter da = new SqlDataAdapter(strSQLText, Conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}

public void CloseConnection()
{
Conn.Dispose();
}
}

0 0