几个数据访问类
TableAttribute.cs
ColumnAttribute.cs
BaseDAL.cs
using System;
using System.Collections.Generic;
using System.Text;
namespace Common
{
[AttributeUsage(AttributeTargets.Class)]
public class TableAttribute : Attribute
{
private string name;
public TableAttribute(string name)
{
this.name = name;
}
public string Name
{
get { return name; }
set { name = value; }
}
}
}
using System.Collections.Generic;
using System.Text;
namespace Common
{
[AttributeUsage(AttributeTargets.Class)]
public class TableAttribute : Attribute
{
private string name;
public TableAttribute(string name)
{
this.name = name;
}
public string Name
{
get { return name; }
set { name = value; }
}
}
}
ColumnAttribute.cs
using System;
namespace Common
{
[AttributeUsage(AttributeTargets.Property)]
public class ColumnAttribute : Attribute
{
private string name;
public ColumnAttribute(string name)
{
this.name = name;
}
public string Name
{
get { return name; }
set { name = value; }
}
}
}
namespace Common
{
[AttributeUsage(AttributeTargets.Property)]
public class ColumnAttribute : Attribute
{
private string name;
public ColumnAttribute(string name)
{
this.name = name;
}
public string Name
{
get { return name; }
set { name = value; }
}
}
}
BaseDAL.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Text;
using System.Web;
using System.Reflection;
using Data;
using Common;
using Config;
using Cache;
namespace Data
{
public class BaseDAL : DataProvider
{
private const string providerType = "data";
private ProviderConfiguration providerConfiguration = ProviderConfiguration.GetProviderConfiguration(providerType);
private string connectionString = "";
private string connectionKey = "HUANG_SqlConnection";
private string transactionKey = "HUANG_SqlTransaction";
private static SqlConnection connection;
private static SqlTransaction transaction;
#region Constructor
public BaseDAL()
{
// Read the configuration specific information for this provider
Provider objProvider = (Provider)providerConfiguration.Providers[providerConfiguration.DefaultProvider];
// Read the attributes for this provider
connectionString = objProvider.Attributes["connectionString"];
}
public BaseDAL(string connectionString) : this()
{
this.connectionString = connectionString;
if (Connection == null)
{
Connection = new SqlConnection(connectionString);
}
}
public BaseDAL(IDbConnection connection) : this(connection.ConnectionString)
{
}
#endregion
#region Properties
private SqlConnection Connection
{
get
{
if (!ConfigHelper.IsWeb)
{
if (connection == null)
{
connection = new SqlConnection(connectionString);
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
return connection;
}
else
{
//System.Web.HttpContext.Current.Response.Write(HttpContext.Current.Session.SessionID + "<br/>");
SqlConnection connection = (SqlConnection)DataCache.GetSession(connectionKey);
if (connection == null)
{
DataCache.SetSession(connectionKey, new SqlConnection(connectionString));
}
connection = (SqlConnection)DataCache.GetSession(connectionKey);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
return connection;
}
}
set
{
if (!ConfigHelper.IsWeb)
{
connection = value;
}
else
{
DataCache.SetSession(connectionKey, value);
}
}
}
private SqlTransaction Transaction
{
get
{
if (!ConfigHelper.IsWeb)
{
return transaction;
}
else
{
return (SqlTransaction)DataCache.GetSession(transactionKey);
}
}
set
{
if (!ConfigHelper.IsWeb)
{
transaction = value;
}
else
{
DataCache.SetSession(transactionKey, value);
}
}
}
#endregion
#region ExecuteNonQuery
protected bool ExecuteNonQuery(SqlCommand command)
{
// 当没有开启事务时，执行之后关闭连接，否则在提交事务或者回滚事务中关闭连接。
bool closeConnection = true;
try
{
command.Connection = Connection;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
command.Transaction = transaction;
closeConnection = false;
}
command.ExecuteNonQuery();
if (command.Parameters[0].Direction == ParameterDirection.ReturnValue)
{
int result = int.Parse(command.Parameters[0].Value.ToString());
if (result != 0)
{
throw new CException(result, CMessage.Item(result));
}
else
{
return true;
}
}
else
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
//command.Parameters.Clear();
if (closeConnection && command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
}
}
#endregion
#region ExecuteReader
public SqlDataReader ExecuteReader(string commandText, CommandBehavior behavior)
{
SqlConnection conn = new SqlConnection(connectionString);
using (SqlCommand command = new SqlCommand(commandText, conn))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
try
{
return command.ExecuteReader(behavior);
}
catch
{
throw;
}
}
}
protected SqlDataReader ExecuteReader(SqlCommand command)
{
try
{
SqlConnection conn = new SqlConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command.Connection = conn;
command.CommandTimeout = 300;
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
command.Parameters.Clear();
return reader;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region ExecuteScalar
protected object ExecuteScalar(SqlCommand command)
{
try
{
SqlConnection conn = new SqlConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command.Connection = conn;
object obj = command.ExecuteScalar();
command.Parameters.Clear();
return obj;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region ExecuteDataSet
protected DataSet ExecuteDataSet(SqlCommand command)
{
command.Connection = Connection;
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
command.Parameters.Clear();
// 关闭连接
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
return ds;
}
#endregion ExecuteDataSet
#region CRUD使用函数
private string GetTableName(object obj)
{
Type objType = obj.GetType();
TableAttribute att = (TableAttribute)Attribute.GetCustomAttribute(objType, typeof(TableAttribute));
if (att != null)
return att.Name;
else
return "I_" + obj.GetType().Name;
}
private DataTable GeCacheSchema(object obj)
{
string key = GetTableName(obj);
DataTable dt = (DataTable)DataCache.GetCache(key + "_Schema");
if (dt == null)
{
// 得到实体表的结构
string sql = "select top 1 * from [" + key + "]";
SqlDataReader reader = ExecuteReader(sql, CommandBehavior.CloseConnection | CommandBehavior.KeyInfo);
dt = reader.GetSchemaTable();
reader.Close();
// 遍历所有的行,去掉实体中没有的行(对应为表的列)
IList<DataRow> listRemove = new List<DataRow>();
IList<PropertyInfo> objProperties = CBO.GetPropertyInfo(obj.GetType());
foreach (DataRow row in dt.Rows)
{
string columnName = Convert.ToString(row["BaseColumnName"]);
bool bexist = false;
for (int intProperty = 0; intProperty < objProperties.Count; intProperty++)
{
// 当实体的属性和数据表的列名不一致，只需在实体属性上加[Column("列名")]即可，否则列名和属性名一致
ColumnAttribute ca = (ColumnAttribute)Attribute.GetCustomAttribute(objProperties[intProperty], typeof(ColumnAttribute));
if ((ca != null && ca.Name == columnName) || (objProperties[intProperty].Name == columnName))
{
bexist = true;
break;
}
}
if (!bexist)
{
listRemove.Add(row);
}
}
foreach (DataRow row in listRemove)
{
dt.Rows.Remove(row);
row.Delete();
}
DataCache.SetCache(key + "_Schema", dt);
}
return dt;
}
private IDictionary<string, SqlParameter> GetCacheParams(object obj)
{
string key = GetTableName(obj);
// 缓存每个表对应的所有参数
IDictionary<string, SqlParameter> param = (IDictionary<string, SqlParameter>)DataCache.GetCache(key + "_Params");
if (param == null)
{
param = new Dictionary<string, SqlParameter>();
// 取得表的结构
DataTable dt = GeCacheSchema(obj);
string baseName;
string type;
int length;
bool allowNull;
SqlParameter p;
foreach (DataRow row in dt.Rows)
{
baseName = Convert.ToString(row["BaseColumnName"]);
type = row["DataTypeName"].ToString();
length = Convert.ToInt32(row["ColumnSize"]);
allowNull = Convert.ToBoolean(row["AllowDBNull"]);
p = new SqlParameter(baseName, GetSqlDbType(type), length, ParameterDirection.Input, allowNull, 0, 0, baseName, DataRowVersion.Default, null);
param.Add(baseName, p);
}
DataCache.SetCache(key + "_Params", param);
}
return param;
}
private SqlDbType GetSqlDbType(string type)
{
System.ComponentModel.EnumConverter enu = new System.ComponentModel.EnumConverter(typeof(SqlDbType));
return (SqlDbType)enu.ConvertFrom(type);
#region Convert To Enum
/*switch (type.ToLower())
{
case "bigint" :
return SqlDbType.BigInt;
case "binary" :
return SqlDbType.Binary;
case "bit" :
return SqlDbType.Bit;
case "char" :
return SqlDbType.Char;
case "datetime":
return SqlDbType.DateTime;
case "decimal" :
return SqlDbType.Decimal;
case "float" :
return SqlDbType.Float;
case "image" :
return SqlDbType.Image;
case "int" :
return SqlDbType.Int;
case "money" :
return SqlDbType.Money;
case "nchar" :
return SqlDbType.NChar;
case "ntext":
return SqlDbType.NText;
case "nvarchar" :
return SqlDbType.NVarChar;
case "real" :
return SqlDbType.Real;
case "smalldatetime" :
return SqlDbType.SmallDateTime;
case "smallint" :
return SqlDbType.SmallInt;
case "smallmoney" :
return SqlDbType.SmallMoney;
case "sql_variant":
return SqlDbType.Variant;
case "text" :
return SqlDbType.Text;
case "timestamp" :
return SqlDbType.Timestamp;
case "tinyint" :
return SqlDbType.TinyInt;
case "uniqueidentifier" :
return SqlDbType.UniqueIdentifier;
case "varbinary" :
return SqlDbType.VarBinary;
case "varchar" :
return SqlDbType.VarChar;
default:
return SqlDbType.VarChar;
}
*/
#endregion
}
private bool IsDefault(object value)
{
if (value is DateTime)
{
DateTime dt = (DateTime)value;
return (dt == DateTime.MinValue || dt == DateTime.MaxValue);
}
return false;
}
#endregion
#region Create
public bool Create(object obj)
{
Type objType = obj.GetType();
string tableName = GetTableName(obj);
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb1.Append("INSERT INTO [" + tableName + "](");
sb2.Append("VALUES(");
IList<SqlParameter> parameters = new List<SqlParameter>();
IDictionary<string, SqlParameter> plist = (IDictionary<string, SqlParameter>)GetCacheParams(obj);
DataTable dt = GeCacheSchema(obj);
bool isIdentity;
string baseName;
// 反射得到this属性，通过this这样得到各个属性的值
MethodInfo minfo = objType.GetMethod("GetValue");
object value;
foreach (DataRow row in dt.Rows)
{
isIdentity = Convert.ToBoolean(row["IsIdentity"]);
baseName = Convert.ToString(row["BaseColumnName"]);
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null && !isIdentity && !IsDefault(value))
{
if (sb1[sb1.Length - 1] == '(')
{
sb1.Append("[" + baseName + "]");
sb2.Append("@" + baseName);
}
else
{
sb1.Append(", [" + baseName + "]");
sb2.Append(", @" + baseName);
}
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
sb1.Append(")");
sb2.Append(")");
sb1.Append(" " + sb2.ToString());
try
{
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteNonQuery(command);
}
catch
{
throw;
}
}
#endregion
#region Retrieve
public IDataReader Retrieve(object obj, string where, string columns)
{
Type objType = obj.GetType();
string tableName = GetTableName(obj);
StringBuilder sb1 = new StringBuilder();
string head = string.Format("SELECT {0} FROM [{1}]", columns, tableName);
sb1.Append(head);
IList<SqlParameter> parameters = new List<SqlParameter>();
IDictionary<string, SqlParameter> plist = (IDictionary<string, SqlParameter>)GetCacheParams(obj);
IDictionary<string, PropertyInfo> prolist = new Dictionary<string, PropertyInfo>();
DataTable dt = GeCacheSchema(obj);
string baseName;
// 反射得到this属性，通过this这样得到各个属性的值
MethodInfo minfo = objType.GetMethod("GetValue");
object value;
if (string.IsNullOrEmpty(where))
{
foreach (DataRow row in dt.Rows)
{
baseName = Convert.ToString(row["BaseColumnName"]);
// 处理数据表中有但是在实体中没有对应的字段
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null)
{
if (sb1.ToString() == head)
{
sb1.Append(" WHERE [" + baseName + "] = @" + baseName);
}
else
{
sb1.Append(" AND [" + baseName + "] = @" + baseName);
}
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
}
else
{
foreach (DataRow row in dt.Rows)
{
baseName = Convert.ToString(row["BaseColumnName"]);
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null && where.IndexOf(baseName) != -1)
{
if (sb1.ToString() == head)
{
sb1.Append(" WHERE [" + baseName + "] = @" + baseName);
}
else
{
sb1.Append(" AND [" + baseName + "] = @" + baseName);
}
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
}
try
{
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteReader(command);
}
catch
{
throw;
}
}
#endregion
#region Update
public bool Update(object obj)
{
Type objType = obj.GetType();
string tableName = GetTableName(obj);
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
string head = "UPDATE [" + tableName + "] SET ";
sb1.Append(head);
object value;
IList<SqlParameter> parameters = new List<SqlParameter>();
IDictionary<string, SqlParameter> plist = (IDictionary<string, SqlParameter>)GetCacheParams(obj);
DataTable dt = GeCacheSchema(obj);
bool isKey;
bool isIdentity;
string baseName;
// 反射得到this属性，通过this这样得到各个属性的值
MethodInfo minfo = objType.GetMethod("GetValue");
foreach (DataRow row in dt.Rows)
{
// Console.WriteLine("[{0}]", row["IsKey"]);
isKey = Convert.ToBoolean(row["IsKey"]);
isIdentity = Convert.ToBoolean(row["IsIdentity"]);
baseName = Convert.ToString(row["BaseColumnName"]);
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null && !IsDefault(value))
{
if (isKey)
{
if (sb2.Length == 0)
{
sb2.Append(" WHERE [" + baseName + "] = @" + baseName);
}
else
{
sb2.Append(" AND [" + baseName + "] = @" + baseName);
}
}
else if (!isIdentity)
{
if (sb1.ToString() == head)
{
sb1.Append("[" + baseName + "] = " + "@" + baseName);
}
else
{
sb1.Append(", [" + baseName + "] = " + "@" + baseName);
}
}
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
sb1.Append(sb2.ToString());
try
{
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteNonQuery(command);
}
catch
{
throw;
}
}
#endregion
#region Delete
public bool Delete(object obj)
{
Type objType = obj.GetType();
string tableName = GetTableName(obj);
IDictionary<string, SqlParameter> plist = (IDictionary<string, SqlParameter>)GetCacheParams(obj);
StringBuilder sb1 = new StringBuilder();
string head = "DELETE FROM [" + tableName + "]";
sb1.Append(head);
IList<SqlParameter> parameters = new List<SqlParameter>();
DataTable dt = GeCacheSchema(obj);
bool isKey;
string baseName;
object value;
// 反射得到this属性，通过this这样得到各个属性的值
MethodInfo minfo = objType.GetMethod("GetValue");
foreach (DataRow row in dt.Rows)
{
// isKey = Convert.ToBoolean(row["IsKey"]);
baseName = Convert.ToString(row["BaseColumnName"]);
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null)
{
//if (isKey)
{
if (sb1.ToString() == head)
{
sb1.Append(" WHERE [" + baseName + "] = @" + baseName);
}
else
{
sb1.Append(" AND [" + baseName + "] = @" + baseName);
}
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
}
try
{
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteNonQuery(command);
}
catch
{
throw;
}
}
#endregion
#region Transaction
public void BeginTransaction()
{
SqlConnection connection = Connection;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlTransaction transaction = Transaction;
if (transaction == null)
{
transaction = connection.BeginTransaction();
}
Connection = connection;
Transaction = transaction;
}
public void Commit()
{
SqlConnection connection = Connection;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
transaction.Commit();
transaction.Dispose();
transaction = null;
Transaction = null;
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
public void Rollback()
{
SqlConnection connection = Connection;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
transaction.Rollback();
transaction.Dispose();
transaction = null;
Transaction = null;
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
#endregion
public void Dispose()
{
SqlConnection connection = Connection;
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
}
}
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Text;
using System.Web;
using System.Reflection;
using Data;
using Common;
using Config;
using Cache;
namespace Data
{
public class BaseDAL : DataProvider
{
private const string providerType = "data";
private ProviderConfiguration providerConfiguration = ProviderConfiguration.GetProviderConfiguration(providerType);
private string connectionString = "";
private string connectionKey = "HUANG_SqlConnection";
private string transactionKey = "HUANG_SqlTransaction";
private static SqlConnection connection;
private static SqlTransaction transaction;
#region Constructor
public BaseDAL()
{
// Read the configuration specific information for this provider
Provider objProvider = (Provider)providerConfiguration.Providers[providerConfiguration.DefaultProvider];
// Read the attributes for this provider
connectionString = objProvider.Attributes["connectionString"];
}
public BaseDAL(string connectionString) : this()
{
this.connectionString = connectionString;
if (Connection == null)
{
Connection = new SqlConnection(connectionString);
}
}
public BaseDAL(IDbConnection connection) : this(connection.ConnectionString)
{
}
#endregion
#region Properties
private SqlConnection Connection
{
get
{
if (!ConfigHelper.IsWeb)
{
if (connection == null)
{
connection = new SqlConnection(connectionString);
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
return connection;
}
else
{
//System.Web.HttpContext.Current.Response.Write(HttpContext.Current.Session.SessionID + "<br/>");
SqlConnection connection = (SqlConnection)DataCache.GetSession(connectionKey);
if (connection == null)
{
DataCache.SetSession(connectionKey, new SqlConnection(connectionString));
}
connection = (SqlConnection)DataCache.GetSession(connectionKey);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
return connection;
}
}
set
{
if (!ConfigHelper.IsWeb)
{
connection = value;
}
else
{
DataCache.SetSession(connectionKey, value);
}
}
}
private SqlTransaction Transaction
{
get
{
if (!ConfigHelper.IsWeb)
{
return transaction;
}
else
{
return (SqlTransaction)DataCache.GetSession(transactionKey);
}
}
set
{
if (!ConfigHelper.IsWeb)
{
transaction = value;
}
else
{
DataCache.SetSession(transactionKey, value);
}
}
}
#endregion
#region ExecuteNonQuery
protected bool ExecuteNonQuery(SqlCommand command)
{
// 当没有开启事务时，执行之后关闭连接，否则在提交事务或者回滚事务中关闭连接。
bool closeConnection = true;
try
{
command.Connection = Connection;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
command.Transaction = transaction;
closeConnection = false;
}
command.ExecuteNonQuery();
if (command.Parameters[0].Direction == ParameterDirection.ReturnValue)
{
int result = int.Parse(command.Parameters[0].Value.ToString());
if (result != 0)
{
throw new CException(result, CMessage.Item(result));
}
else
{
return true;
}
}
else
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
//command.Parameters.Clear();
if (closeConnection && command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
}
}
#endregion
#region ExecuteReader
public SqlDataReader ExecuteReader(string commandText, CommandBehavior behavior)
{
SqlConnection conn = new SqlConnection(connectionString);
using (SqlCommand command = new SqlCommand(commandText, conn))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
try
{
return command.ExecuteReader(behavior);
}
catch
{
throw;
}
}
}
protected SqlDataReader ExecuteReader(SqlCommand command)
{
try
{
SqlConnection conn = new SqlConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command.Connection = conn;
command.CommandTimeout = 300;
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
command.Parameters.Clear();
return reader;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region ExecuteScalar
protected object ExecuteScalar(SqlCommand command)
{
try
{
SqlConnection conn = new SqlConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command.Connection = conn;
object obj = command.ExecuteScalar();
command.Parameters.Clear();
return obj;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region ExecuteDataSet
protected DataSet ExecuteDataSet(SqlCommand command)
{
command.Connection = Connection;
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
command.Parameters.Clear();
// 关闭连接
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
return ds;
}
#endregion ExecuteDataSet
#region CRUD使用函数
private string GetTableName(object obj)
{
Type objType = obj.GetType();
TableAttribute att = (TableAttribute)Attribute.GetCustomAttribute(objType, typeof(TableAttribute));
if (att != null)
return att.Name;
else
return "I_" + obj.GetType().Name;
}
private DataTable GeCacheSchema(object obj)
{
string key = GetTableName(obj);
DataTable dt = (DataTable)DataCache.GetCache(key + "_Schema");
if (dt == null)
{
// 得到实体表的结构
string sql = "select top 1 * from [" + key + "]";
SqlDataReader reader = ExecuteReader(sql, CommandBehavior.CloseConnection | CommandBehavior.KeyInfo);
dt = reader.GetSchemaTable();
reader.Close();
// 遍历所有的行,去掉实体中没有的行(对应为表的列)
IList<DataRow> listRemove = new List<DataRow>();
IList<PropertyInfo> objProperties = CBO.GetPropertyInfo(obj.GetType());
foreach (DataRow row in dt.Rows)
{
string columnName = Convert.ToString(row["BaseColumnName"]);
bool bexist = false;
for (int intProperty = 0; intProperty < objProperties.Count; intProperty++)
{
// 当实体的属性和数据表的列名不一致，只需在实体属性上加[Column("列名")]即可，否则列名和属性名一致
ColumnAttribute ca = (ColumnAttribute)Attribute.GetCustomAttribute(objProperties[intProperty], typeof(ColumnAttribute));
if ((ca != null && ca.Name == columnName) || (objProperties[intProperty].Name == columnName))
{
bexist = true;
break;
}
}
if (!bexist)
{
listRemove.Add(row);
}
}
foreach (DataRow row in listRemove)
{
dt.Rows.Remove(row);
row.Delete();
}
DataCache.SetCache(key + "_Schema", dt);
}
return dt;
}
private IDictionary<string, SqlParameter> GetCacheParams(object obj)
{
string key = GetTableName(obj);
// 缓存每个表对应的所有参数
IDictionary<string, SqlParameter> param = (IDictionary<string, SqlParameter>)DataCache.GetCache(key + "_Params");
if (param == null)
{
param = new Dictionary<string, SqlParameter>();
// 取得表的结构
DataTable dt = GeCacheSchema(obj);
string baseName;
string type;
int length;
bool allowNull;
SqlParameter p;
foreach (DataRow row in dt.Rows)
{
baseName = Convert.ToString(row["BaseColumnName"]);
type = row["DataTypeName"].ToString();
length = Convert.ToInt32(row["ColumnSize"]);
allowNull = Convert.ToBoolean(row["AllowDBNull"]);
p = new SqlParameter(baseName, GetSqlDbType(type), length, ParameterDirection.Input, allowNull, 0, 0, baseName, DataRowVersion.Default, null);
param.Add(baseName, p);
}
DataCache.SetCache(key + "_Params", param);
}
return param;
}
private SqlDbType GetSqlDbType(string type)
{
System.ComponentModel.EnumConverter enu = new System.ComponentModel.EnumConverter(typeof(SqlDbType));
return (SqlDbType)enu.ConvertFrom(type);
#region Convert To Enum
/*switch (type.ToLower())
{
case "bigint" :
return SqlDbType.BigInt;
case "binary" :
return SqlDbType.Binary;
case "bit" :
return SqlDbType.Bit;
case "char" :
return SqlDbType.Char;
case "datetime":
return SqlDbType.DateTime;
case "decimal" :
return SqlDbType.Decimal;
case "float" :
return SqlDbType.Float;
case "image" :
return SqlDbType.Image;
case "int" :
return SqlDbType.Int;
case "money" :
return SqlDbType.Money;
case "nchar" :
return SqlDbType.NChar;
case "ntext":
return SqlDbType.NText;
case "nvarchar" :
return SqlDbType.NVarChar;
case "real" :
return SqlDbType.Real;
case "smalldatetime" :
return SqlDbType.SmallDateTime;
case "smallint" :
return SqlDbType.SmallInt;
case "smallmoney" :
return SqlDbType.SmallMoney;
case "sql_variant":
return SqlDbType.Variant;
case "text" :
return SqlDbType.Text;
case "timestamp" :
return SqlDbType.Timestamp;
case "tinyint" :
return SqlDbType.TinyInt;
case "uniqueidentifier" :
return SqlDbType.UniqueIdentifier;
case "varbinary" :
return SqlDbType.VarBinary;
case "varchar" :
return SqlDbType.VarChar;
default:
return SqlDbType.VarChar;
}
*/
#endregion
}
private bool IsDefault(object value)
{
if (value is DateTime)
{
DateTime dt = (DateTime)value;
return (dt == DateTime.MinValue || dt == DateTime.MaxValue);
}
return false;
}
#endregion
#region Create
public bool Create(object obj)
{
Type objType = obj.GetType();
string tableName = GetTableName(obj);
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb1.Append("INSERT INTO [" + tableName + "](");
sb2.Append("VALUES(");
IList<SqlParameter> parameters = new List<SqlParameter>();
IDictionary<string, SqlParameter> plist = (IDictionary<string, SqlParameter>)GetCacheParams(obj);
DataTable dt = GeCacheSchema(obj);
bool isIdentity;
string baseName;
// 反射得到this属性，通过this这样得到各个属性的值
MethodInfo minfo = objType.GetMethod("GetValue");
object value;
foreach (DataRow row in dt.Rows)
{
isIdentity = Convert.ToBoolean(row["IsIdentity"]);
baseName = Convert.ToString(row["BaseColumnName"]);
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null && !isIdentity && !IsDefault(value))
{
if (sb1[sb1.Length - 1] == '(')
{
sb1.Append("[" + baseName + "]");
sb2.Append("@" + baseName);
}
else
{
sb1.Append(", [" + baseName + "]");
sb2.Append(", @" + baseName);
}
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
sb1.Append(")");
sb2.Append(")");
sb1.Append(" " + sb2.ToString());
try
{
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteNonQuery(command);
}
catch
{
throw;
}
}
#endregion
#region Retrieve
public IDataReader Retrieve(object obj, string where, string columns)
{
Type objType = obj.GetType();
string tableName = GetTableName(obj);
StringBuilder sb1 = new StringBuilder();
string head = string.Format("SELECT {0} FROM [{1}]", columns, tableName);
sb1.Append(head);
IList<SqlParameter> parameters = new List<SqlParameter>();
IDictionary<string, SqlParameter> plist = (IDictionary<string, SqlParameter>)GetCacheParams(obj);
IDictionary<string, PropertyInfo> prolist = new Dictionary<string, PropertyInfo>();
DataTable dt = GeCacheSchema(obj);
string baseName;
// 反射得到this属性，通过this这样得到各个属性的值
MethodInfo minfo = objType.GetMethod("GetValue");
object value;
if (string.IsNullOrEmpty(where))
{
foreach (DataRow row in dt.Rows)
{
baseName = Convert.ToString(row["BaseColumnName"]);
// 处理数据表中有但是在实体中没有对应的字段
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null)
{
if (sb1.ToString() == head)
{
sb1.Append(" WHERE [" + baseName + "] = @" + baseName);
}
else
{
sb1.Append(" AND [" + baseName + "] = @" + baseName);
}
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
}
else
{
foreach (DataRow row in dt.Rows)
{
baseName = Convert.ToString(row["BaseColumnName"]);
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null && where.IndexOf(baseName) != -1)
{
if (sb1.ToString() == head)
{
sb1.Append(" WHERE [" + baseName + "] = @" + baseName);
}
else
{
sb1.Append(" AND [" + baseName + "] = @" + baseName);
}
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
}
try
{
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteReader(command);
}
catch
{
throw;
}
}
#endregion
#region Update
public bool Update(object obj)
{
Type objType = obj.GetType();
string tableName = GetTableName(obj);
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
string head = "UPDATE [" + tableName + "] SET ";
sb1.Append(head);
object value;
IList<SqlParameter> parameters = new List<SqlParameter>();
IDictionary<string, SqlParameter> plist = (IDictionary<string, SqlParameter>)GetCacheParams(obj);
DataTable dt = GeCacheSchema(obj);
bool isKey;
bool isIdentity;
string baseName;
// 反射得到this属性，通过this这样得到各个属性的值
MethodInfo minfo = objType.GetMethod("GetValue");
foreach (DataRow row in dt.Rows)
{
// Console.WriteLine("[{0}]", row["IsKey"]);
isKey = Convert.ToBoolean(row["IsKey"]);
isIdentity = Convert.ToBoolean(row["IsIdentity"]);
baseName = Convert.ToString(row["BaseColumnName"]);
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null && !IsDefault(value))
{
if (isKey)
{
if (sb2.Length == 0)
{
sb2.Append(" WHERE [" + baseName + "] = @" + baseName);
}
else
{
sb2.Append(" AND [" + baseName + "] = @" + baseName);
}
}
else if (!isIdentity)
{
if (sb1.ToString() == head)
{
sb1.Append("[" + baseName + "] = " + "@" + baseName);
}
else
{
sb1.Append(", [" + baseName + "] = " + "@" + baseName);
}
}
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
sb1.Append(sb2.ToString());
try
{
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteNonQuery(command);
}
catch
{
throw;
}
}
#endregion
#region Delete
public bool Delete(object obj)
{
Type objType = obj.GetType();
string tableName = GetTableName(obj);
IDictionary<string, SqlParameter> plist = (IDictionary<string, SqlParameter>)GetCacheParams(obj);
StringBuilder sb1 = new StringBuilder();
string head = "DELETE FROM [" + tableName + "]";
sb1.Append(head);
IList<SqlParameter> parameters = new List<SqlParameter>();
DataTable dt = GeCacheSchema(obj);
bool isKey;
string baseName;
object value;
// 反射得到this属性，通过this这样得到各个属性的值
MethodInfo minfo = objType.GetMethod("GetValue");
foreach (DataRow row in dt.Rows)
{
// isKey = Convert.ToBoolean(row["IsKey"]);
baseName = Convert.ToString(row["BaseColumnName"]);
// 通过反射得到方法的值
value = minfo.Invoke(obj, new object[] { baseName });
if (value != null)
{
//if (isKey)
{
if (sb1.ToString() == head)
{
sb1.Append(" WHERE [" + baseName + "] = @" + baseName);
}
else
{
sb1.Append(" AND [" + baseName + "] = @" + baseName);
}
SqlParameter param = (SqlParameter)((ICloneable)plist[baseName]).Clone();
param.Value = value;
parameters.Add(param);
}
}
}
try
{
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteNonQuery(command);
}
catch
{
throw;
}
}
#endregion
#region Transaction
public void BeginTransaction()
{
SqlConnection connection = Connection;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlTransaction transaction = Transaction;
if (transaction == null)
{
transaction = connection.BeginTransaction();
}
Connection = connection;
Transaction = transaction;
}
public void Commit()
{
SqlConnection connection = Connection;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
transaction.Commit();
transaction.Dispose();
transaction = null;
Transaction = null;
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
public void Rollback()
{
SqlConnection connection = Connection;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
transaction.Rollback();
transaction.Dispose();
transaction = null;
Transaction = null;
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
#endregion
public void Dispose()
{
SqlConnection connection = Connection;
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
}
}
