/// <summary>
/// 删除记录
/// </summary>
/// <example>
/// DELETE Menu WHERE ID=@ID
/// DELETE Menu WHERE ID=:ID
/// </example>
public void Delete <T>(T entity)
{
MappingInfo <T> mappingInfo = new MappingInfo <T>();
IDbCommand command = this.driver.CreateDbCommand();
StringBuilder sql = new StringBuilder();
string fullTableName = this.driver.ToFullTableName(mappingInfo.DbName, mappingInfo.TableName);
sql.AppendFormat("DELETE FROM {0} WHERE 1=1", fullTableName);
foreach (string propertyName in mappingInfo.PrimaryKeyColumns.Keys)
{
ColumnAttribute columnAttribute = mappingInfo.Columns[propertyName];
IDataParameter parameter = this.driver.CreateDbParameter(propertyName);
command.Parameters.Add(parameter);
string sqlParameterName = this.driver.ToSQLParameterName(propertyName);
sql.AppendFormat(" AND {0}={1}", columnAttribute.Name, sqlParameterName);
}
command.CommandText = sql.ToString();
command.CommandType = CommandType.Text;
SqlUtil.AssignParameters(command.Parameters, entity);
this.ExecuteNonQuery(command);
}
/// <summary>
/// 判读记录是否已经存在
/// </summary>
/// <returns>是/否</returns>
/// <example>
/// SELECT COUNT(*) FROM Table WHERE ID=@ID
/// SELECT COUNT(*) FROM Table WHERE ID=:ID
/// </example>
public virtual bool IsExist <T>(T entity)
{
MappingInfo <T> mappingInfo = new MappingInfo <T>();
IDbCommand command = this.driver.CreateDbCommand();
StringBuilder sql = new StringBuilder();
string fullTableName = this.driver.ToFullTableName(mappingInfo.DbName, mappingInfo.TableName);
sql.AppendFormat("SELECT COUNT(*) FROM {0} WHERE 1=1", fullTableName);
foreach (string propertyName in mappingInfo.PrimaryKeyColumns.Keys)
{
ColumnAttribute columnAttribute = mappingInfo.Columns[propertyName];
IDataParameter parameter = this.driver.CreateDbParameter(propertyName);
command.Parameters.Add(parameter);
string sqlParameterName = this.driver.ToSQLParameterName(propertyName);
sql.AppendFormat(" AND {0}={1}", columnAttribute.Name, sqlParameterName);
}
command.CommandText = sql.ToString();
command.CommandType = CommandType.Text;
SqlUtil.AssignParameters(command.Parameters, entity);
object returnValue = this.ExecuteScalar(command);
return(Convert.ToInt32(returnValue) > 0);
}
/// <summary>
/// 根据存储过程名称与实体对象执行数据库操作命令
/// </summary>
/// <param name="storedProcedureName">存储过程名称</param>
/// <param name="entity">实体对象</param>
public void ExecuteProcedure(string procedureName, object entity)
{
IDbCommand command = this.driver.CreateProcedureCommand(procedureName);
// 从实体对象中取参数值赋给数据库操作命令参数
SqlUtil.AssignParameters(command.Parameters, entity);
this.ExecuteNonQuery(command);
// 从数据库操作命令参数取返回值赋给实体对象
SqlUtil.AssignProperties(entity, command.Parameters);
}
/// <summary>
/// 修改记录
/// </summary>
/// <example>
/// UPDATE Menu SET MenuItem=@MenuItem,ParentID=@ParentID WHERE ID=@ID
/// UPDATE Menu SET MenuItem=:MenuItem,ParentID=:ParentID WHERE ID=:ID
/// </example>
public void Update <T>(T entity)
{
MappingInfo <T> mappingInfo = new MappingInfo <T>();
IDbCommand command = this.driver.CreateDbCommand();
StringBuilder sql = new StringBuilder();
string fullTableName = this.driver.ToFullTableName(mappingInfo.DbName, mappingInfo.TableName);
sql.AppendFormat("UPDATE {0} ", fullTableName);
StringBuilder where = new StringBuilder();
where.Append(" WHERE 1=1");
bool isFirst = true;
foreach (string propertyName in mappingInfo.Columns.Keys)
{
ColumnAttribute columnAttribute = mappingInfo.Columns[propertyName];
if (columnAttribute == null || (columnAttribute.IsIncrement && !columnAttribute.IsPrimaryKey))
{
continue;
}
string columnName = columnAttribute.Name;
string sqlParameterName = this.driver.ToSQLParameterName(propertyName);
IDataParameter parameter = this.driver.CreateDbParameter(propertyName);
command.Parameters.Add(parameter);
// 不能对自增字段赋值
if (!columnAttribute.IsIncrement)
{
sql.Append(isFirst ? "SET " : ", ")
.AppendFormat("{0}={1}", columnName, sqlParameterName);
if (isFirst)
{
isFirst = false;
}
}
//主键作为条件
if (columnAttribute.IsPrimaryKey)
{
where.AppendFormat(" AND {0}={1}", columnName, sqlParameterName);
}
}
command.CommandText = sql.Append(where).ToString();
command.CommandType = CommandType.Text;
SqlUtil.AssignParameters(command.Parameters, entity);
this.ExecuteNonQuery(command);
}
/// <summary>
/// 添加记录
/// </summary>
/// <example>
/// INSERT INTO Configure(UserName,Password) VALUES(@UserName,@Password)
/// INSERT INTO Configure(UserName,Password) VALUES(:UserName,:Password)
///
/// MSSQL: SELECT @ID = IDENT_CURRENT('Table')
/// ORACLE: SELECT Sequence.Nextval FROM Sequence
/// </example>
public virtual void Insert <T>(T entity)
{
MappingInfo <T> mappingInfo = new MappingInfo <T>();
IDbCommand command = this.driver.CreateDbCommand();
StringBuilder sql = new StringBuilder();
string fullTableName = this.driver.ToFullTableName(mappingInfo.DbName, mappingInfo.TableName);
sql.AppendFormat("INSERT INTO {0} ", fullTableName);
StringBuilder columnsExpression = new StringBuilder();
StringBuilder valuesExpression = new StringBuilder();
bool isFirst = true;
foreach (string propertyName in mappingInfo.Columns.Keys)
{
ColumnAttribute columnAttribute = mappingInfo.Columns[propertyName];
// 不处理自增字段
if (columnAttribute == null || columnAttribute.IsIncrement)
{
continue;
}
IDataParameter parameter = this.driver.CreateDbParameter(propertyName);
command.Parameters.Add(parameter);
columnsExpression.Append(isFirst ? string.Empty : ",")
.Append(columnAttribute.Name);
string sqlParameterName = this.driver.ToSQLParameterName(propertyName);
valuesExpression.Append(isFirst ? string.Empty : ",")
.Append(sqlParameterName);
if (isFirst)
{
isFirst = false;
}
}
sql.AppendFormat("({0})", columnsExpression.ToString())
.AppendFormat(" VALUES({0})", valuesExpression.ToString());
command.CommandText = sql.ToString();
command.CommandType = CommandType.Text;
SqlUtil.AssignParameters(command.Parameters, entity);
this.ExecuteNonQuery(command);
}
