/// <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); }