public static string GetEntityTable <T>()
{
string tName = BaseTO.GetTableName <T>();
Type type = typeof(T);
tName = tName.Replace("_" + type.Namespace + "_" + type.Name, "");
return(tName);
}
/// <summary>
/// 根据主键数组和给定实体的值更新
/// </summary>
/// <param name="to"></param>
/// <param name="ids"></param>
/// <param name="enablePKUpdate">是否允许更新主键，用于某些自定义主键需要更新的情况</param>
public int UpdateFromIds(BaseTO to, long[] ids, bool enablePKUpdate)
{
Dictionary <string, object> dict;
dict = to.GetSQLWriteValues();
//我把recordcount放在baseTo里面，用于列表分页时返回记录总数，这里新增修改时需要过滤掉 kevin
dict.Remove("recordcount");
StringBuilder sb = new StringBuilder();
sb.Append("update ").Append(_tableName).Append(" set ");
List <SqlParameter> parameters = new List <SqlParameter>();
int i = 0;
string[] primaryKeys = to.GetPrimaryKey <T>();
foreach (string name in dict.Keys)
{
//如果不允许更新主键，不是主键才添加到update语句的更新字段中
if (enablePKUpdate ? true : !primaryKeys.Contains <string>(name))
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append(name).Append("=@").Append(name);
parameters.Add(Query.BuilderParam(name, dict[name]));
i++;
}
}
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
string idList = string.Join(",", ids);
sb.Append(" where " + primaryKeys[0] + " in ( " + idList + ")");
SqlTransConn transConn = GetConnection(_connectionName);
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
public int DeleteByPKArray(long[] idArrar)
{
StringBuilder sb = new StringBuilder();
sb.Append("Delete from ").Append(_tableName);
string[] primaryKeys = BaseTO.GetSQLPrimaryKey(BaseTO.GetTableName <T>());
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
if (1 < primaryKeys.Length)
{
//此方法只用于实体类只有一个主键的情况
throw new ExceptionErrorPrimaryKeyNumber();
}
string idList = string.Join(",", idArrar);
sb.Append(" where ")
.Append(primaryKeys[0])
.Append(" in (")
.Append(idList)
.Append(")");
List <SqlParameter> parameters = new List <SqlParameter>();
SqlTransConn transConn = GetConnection(_connectionName);
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), null);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
public int DeleteByPK(long id)
{
StringBuilder sb = new StringBuilder();
sb.Append("Delete from ").Append(_tableName);
string[] primaryKeys = BaseTO.GetSQLPrimaryKey(BaseTO.GetTableName <T>());
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
if (1 < primaryKeys.Length)
{
//此方法只用于实体类只能有一个主键的情况
throw new ExceptionErrorPrimaryKeyNumber();
}
sb.Append(" where ").Append(primaryKeys[0]).Append("=@PKValue");
List <SqlParameter> parameters = new List <SqlParameter>();
SqlParameter parameter = new SqlParameter("@PKValue", SqlDbType.Int, 0);
parameter.Value = id;
parameters.Add(parameter);
SqlTransConn transConn = GetConnection(_connectionName);
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
public int UpdateFromTO(BaseTO to, object PKValue)
{
Dictionary <string, object> dict = to.GetSQLWriteValues();
StringBuilder sb = new StringBuilder();
sb.Append("update ").Append(TableName).Append(" set ");
List <SqlParameter> parameters = new List <SqlParameter>();
int i = 0;
foreach (string name in dict.Keys)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append(name).Append("=@").Append(name);
parameters.Add(Query.BuilderParam(name, dict[name]));
i++;
}
sb.Append(" where ").Append(PrimaryKey).Append("=@PKValue");
parameters.Add(new SqlParameter("@PKValue", PKValue));
SqlTransConn transConn = GetConnection();
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
transConn.Close();
}
return(result);
}
public T GetItemByPK(T t, string pkValue)
{
Query query = new Query();
string tableName = BaseTO.GetTableName <T>();
query.Selects = GetEntityExtendQuery();
string[] primaryKeys = t.GetPrimaryKey <T>();
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
if (1 < primaryKeys.Length)
{
//此方法只用于实体类只能有一个主键的情况
throw new ExceptionErrorPrimaryKeyNumber();
}
query.Wheres.AddEqualPair(primaryKeys[0], pkValue);
return(GetItem(query));
}
public int InsertFromTO(BaseTO to)
{
Dictionary <string, object> dict = to.GetSQLWriteValues();
StringBuilder sb = new StringBuilder();
sb.Append("Insert into ").Append(TableName).Append(" (");
sb.Append(String.Join(", ", dict.Keys.ToArray()));
sb.Append(") values (");
List <SqlParameter> parameters = new List <SqlParameter>();
int i = 0;
foreach (string name in dict.Keys)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append("@").Append(name);
parameters.Add(Query.BuilderParam(name, dict[name]));
i++;
}
sb.Append(")");
SqlTransConn transConn = GetConnection();
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
transConn.Close();
}
return(result);
}
//通过多个主键查询
public T GetItemByPK(BaseTO to)
{
Query query = new Query();
string tableName = BaseTO.GetTableName <T>();
query.Selects = GetEntityExtendQuery();
string[] primaryKeys = to.GetPrimaryKey <T>();
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
foreach (string pkName in primaryKeys)
{
query.Wheres.AddSql(string.Format("{0} = @{0}", pkName), new string[] { "@" + pkName });
query.Parameters.Add("@" + pkName, new Query.ParameterItem()
{
Type = SqlDbType.NVarChar,
Value = to.GetPropertyValue(pkName).ToString()
});
//query.Wheres.AddEqualPair(pkName, to.GetPropertyValue(pkName).ToString());
}
return(GetItem(query));
}
/// <summary>
/// 根据多个主键ID查询
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public List <T> GetItemByPK(long[] ids)
{
Query query = new Query();
string tableName = BaseTO.GetTableName <T>();
query.Selects = GetEntityExtendQuery();
string[] primaryKeys = BaseTO.GetSQLPrimaryKey(tableName);
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
if (1 < primaryKeys.Length)
{
//此方法只用于实体类只能有一个主键的情况
throw new ExceptionErrorPrimaryKeyNumber();
}
if (ids == null || ids.Length < 1)
{
ids = new long[] { 0 };
}
query.Wheres.AddSql(primaryKeys[0] + " in (" + string.Join(",", ids) + ")");
//query.Wheres.AddEqualPair(primaryKeys[0], id);
return(GetItems(query));
}
public BaseDAO()
{
_tableName = BaseTO.GetTableName <T>();
_connectionName = SqlServerSet.ConnectionName;
}
public static Query.SelectItemCollection GetEntityExtendQuery()
{
string tableName = BaseTO.GetTableName <T>();
return(entityExtendQuery.ThreadSafeRead <string, Query.SelectItemCollection>(tableName, "entityExtendQuery"));
}
public static void SetEntityExtendQuery(Query.SelectItemCollection extQuerys)
{
string tableName = BaseTO.GetTableName <T>();
entityExtendQuery.ThreadSafeWrite <string, Query.SelectItemCollection>(tableName, extQuerys, "entityExtendQuery");
}
/// <summary>
/// 根据主键数组和给定实体的值更新
/// </summary>
public int UpdateFromIds(BaseTO to, long[] ids)
{
return(UpdateFromIds(to, ids, false));
}
/// <summary>
/// 批量更新 kevin
/// </summary>
/// <param name="to"></param>
/// <param name="ids"></param>
/// <returns></returns>
public int UpdateFromIds(BaseTO to, int[] ids)
{
return(UpdateFromIds(to, ids.Select(t => Convert.ToInt64(t)).ToArray(), false));
}
public BaseDAO(string connectionName)
{
_tableName = BaseTO.GetTableName <T>();
_connectionName = connectionName;
}
/// <summary>
/// Insert a entity to database.
/// </summary>
/// <param name="to">Entity object</param>
/// <returns>If entity's primary key is identify field return the new id, else return the return value of SqlCommand.ExecuteNonQuery()</returns>
public object InsertFromTO(BaseTO to)
{
Dictionary <string, object> dict = to.GetSQLWriteValues();
//我把recordcount放在baseTo里面，用于列表分页时返回记录总数，这里新增修改时需要过滤掉 kevin
dict.Remove("recordcount");
bool hasIdentifyPK = false;
string[] primaryKay = to.GetPrimaryKey <T>();
foreach (string keyName in primaryKay)
{
SQLFieldAttribute[] attrs = to.GetFieldPropertyAttribute <SQLFieldAttribute>(keyName);
if (null != attrs)
{
foreach (SQLFieldAttribute a in attrs)
{
hasIdentifyPK = a.IsIdentify;
break;
}
}
if (hasIdentifyPK)
{
break;
}
}
StringBuilder sb = new StringBuilder();
sb.Append("Insert into ").Append(_tableName).Append(" (");
sb.Append(String.Join(", ", dict.Keys.ToArray()));
sb.Append(") values (");
List <SqlParameter> parameters = new List <SqlParameter>();
int i = 0;
foreach (string name in dict.Keys)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append("@").Append(name);
parameters.Add(Query.BuilderParam(name, dict[name]));
i++;
}
sb.Append(")");
if (0 == to.CustomPrimaryKeyIdentify ? hasIdentifyPK : (1 == to.CustomPrimaryKeyIdentify))
{
sb.Append(";select @@identity");
}
SqlTransConn transConn = GetConnection(_connectionName);
object result = 0;
try
{
if (0 == to.CustomPrimaryKeyIdentify ? hasIdentifyPK : (1 == to.CustomPrimaryKeyIdentify))
{
result = SqlHelper.ExecuteScalar(transConn, CommandType.Text, sb.ToString(), parameters);
}
else
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
//private static Dictionary<string, string> entityTable = new Dictionary<string, string>();
/// <summary>
/// 设置实体对应的表名
/// </summary>
/// <param name="tableName"></param>
public static void SetEntityTable <T>(string tableName)
{
BaseTO.SetTableName <T>(tableName);
}
