dbgw.pas

数据库封装原代码 1. 该代码可以免费使用, 该代码的名字暂时定为"哲别" 2. 如果你需要使用该代码, 请注明该代码的原来作者: Jacky Zhou 3. 如果你发现该代码有bug,可以自己修改

  connection : TQuery;
begin
  connection := GetConnection;

  try
    //get table name
    strTableName := GetTableName(pety.GetEntityName);

    //get table field
    listEntityMapField := GetEntityMapField(pety.GetEntityName);

    //set the insert sql
    strSQLFields := '';
    strSQLValues := '';

    strFieldName := '';
    strFieldType := '';
    for i := 0 to listEntityMapField.GetEntityCount - 1 do
    begin
      etyEntityMapField := listEntityMapField.GetEntity(i);

      strFieldName := etyEntityMapField.GetAttributeValue(ID_TABLE_FIELD_NAME);
      bIsFieldChanged := pety.IsAttributeChanged(strFieldName);

      //在Insert时候，有些字段的值有值，有些没有，以下的代码是把有值的字段的Name
      //写到SQL语句里，没有值的就不需要写. 但是PK必须要有值
      if bIsFieldChanged then
      begin
        strFieldType := etyEntityMapField.GetAttributeValue(ID_TABLE_FIELD_TYPE);
        varFieldValue := pety.GetAttributeValue(strFieldName);

        if not VarIsNull(varFieldValue) then
        begin
          strSQLFields := strSQLFields + '[' + strFieldName + '], ';
          strFieldValue := FormatValueAppToDB(varFieldValue, strFieldType);

          if strFieldType = ID_FIELD_TYPE_STRING then
            strSQLValues := strSQLValues + '''' + strFieldValue + '''' + ', '
          else
            strSQLValues := strSQLValues + strFieldValue + ', ';
        end;
      end;
    end;

    if (strSQLValues = '') then
    begin
      InsertEntity := false;
      exit;
    end;

    //get rid of the last ',' in strSQLFields and strSQLValues
    strSQLFields := Copy(strSQLFields, 0, Length(strSQLFields) - 2);
    strSQLValues := Copy(strSQLValues, 0, Length(strSQLValues) - 2);
    strSQL := 'INSERT INTO ' + strTableName + '(' + strSQLFields + ') ' + 'VALUES( ' + strSQLValues + ')';

    connection.Close;
    connection.SQL.Clear;
    connection.SQL.Text := strSQL;
    connection.ExecSQL;

    Result := true;
  finally
    ReleaseConnection(connection);
  end
end;

//-----------------------------------------------------------------------------
//                              UpdateEntity
//-----------------------------------------------------------------------------
function TdmDBGW.UpdateEntity(const pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
var
  strTableName : String;
  etyEntityMapField : IEntity;
  listEntityMapField : TEtyList;
  strSQL, strSQLSet, strSQLWhere : String;

  strFieldName, strFieldType, strFieldValue : String;
  varFieldValue : Variant;
  bIsFieldChanged, bIsPK : Boolean;
  i : Integer;
  connection : TQuery;
begin
  connection := GetConnection;

  try
    //get table name
    strTableName := GetTableName(pety.GetEntityName);

    //get table field
    listEntityMapField := GetEntityMapField(pety.GetEntityName);

    //set the insert sql
    strSQLSet := '';
    strSQLWhere := '';

    strFieldName := '';
    strFieldType := '';
    for i := 0 to listEntityMapField.GetEntityCount - 1 do
    begin
      etyEntityMapField := listEntityMapField.GetEntity(i);

      strFieldName := etyEntityMapField.GetAttributeValue(ID_TABLE_FIELD_NAME);
      bIsFieldChanged := pety.IsAttributeChanged(strFieldName);

      //在Update时候，有些字段的值改变了，有些没有，以下的代码是把改变的字段的Name
      //写到SQL语句里，没有改变的就不需要写
      if bIsFieldChanged then
      begin
        strFieldType := etyEntityMapField.GetAttributeValue(ID_TABLE_FIELD_TYPE);
        bIsPK := etyEntityMapField.GetAttributeValue(ID_ISPK);
        varFieldValue := pety.GetAttributeValue(strFieldName);

        if (not bIsPK) then
        begin
          strFieldValue := FormatValueAppToDB(varFieldValue, strFieldType);

          if strFieldType = ID_FIELD_TYPE_STRING then
            strSQLSet := strSQLSet + '[' + strFieldName + '] = ' + ''''+ strFieldValue + '''' + ', '
          else
            strSQLSet := strSQLSet + '[' + strFieldName + '] = ' + strFieldValue + ', ';
        end;
      end;
    end;

    //get rid of the last ',' in strSQLSet
    if (strSQLSet = '') then
    begin
      UpdateEntity := false;
      exit;
    end;
    strSQLSet := Copy(strSQLSet, 0, Length(strSQLSet) - 2);

    //get strSQLWhere
    if listCondition <> nil then
      strSQLWhere := GetCondition(pety.GetEntityName, listCondition)
    else    //if listCondition = nil then only update this pety
      strSQLWhere := GetPKCondition(pety);

    //set strSQL
    if strSQLWhere = '' then
      strSQL := 'UPDATE ' + strTableName + ' SET ' + strSQLSet
    else
      strSQL := 'UPDATE ' + strTableName + ' SET ' + strSQLSet + ' WHERE ' + strSQLWhere;

    connection.Close;
    connection.SQL.Clear;
    connection.SQL.Text := strSQL;
    connection.ExecSQL;

    Result := true;
  finally
    ReleaseConnection(connection);
  end
end;

//-----------------------------------------------------------------------------
//                              DeleteEntity
//-----------------------------------------------------------------------------
function TdmDBGW.DeleteEntity(const pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
var
  strTableName : String;
  strSQL, strSQLWhere: String;
  connection : TQuery;
begin
  connection := GetConnection;

  try
    //get table name
    strTableName := GetTableName(pety.GetEntityName);

    if listCondition <> nil then
      strSQLWhere := GetCondition(pety.GetEntityName, listCondition)
    else    //if listCondition = nil then only delete this pety
      strSQLWhere := GetPKCondition(pety);

    //set strSQL
    if strSQLWhere = '' then
      strSQL := 'DELETE FROM ' + strTableName
    else
      strSQL := 'DELETE FROM ' + strTableName + ' WHERE ' + strSQLWhere;

    connection.Close;
    connection.SQL.Clear;
    connection.SQL.Text := strSQL;
    connection.ExecSQL;

    Result := true;
  finally
    ReleaseConnection(connection);
  end
end;

//-----------------------------------------------------------------------------
//                              GetCondition
//-----------------------------------------------------------------------------
function TdmDBGW.GetCondition(const strEntityName : String; const listCondition : TEtyList) : String;
var
  i : Integer;

  strTableName : String;
  strSQLWhere : String;
  etyCondition : IEntity;
  strEntityFieldName, strOperation, strValue : String;
  strTableFieldName, strTableFieldType : String;
begin
  try
    if listCondition = nil then
    begin
      Result := '';
      exit;
    end;

    strTableName := GetTableName(strEntityName);

    strSQLWhere := '';
    for i := 0 to listCondition.GetEntityCount - 1 do
    begin
      etyCondition := listCondition.GetEntity(i);

      strEntityFieldName := etyCondition.GetAttributeValue(FD_ENTITY_FIELD_NAME);
      strTableFieldName := GetTableFieldName(strTableName, strEntityFieldName);
      strTableFieldType := GetTableFieldType(strTableName, strTableFieldName);
      strOperation := etyCondition.GetAttributeValue(FD_OPERATION);
      strValue := etyCondition.GetAttributeValue(FD_VALUE);

      if strTableFieldType = ID_FIELD_TYPE_STRING then
        strSQLWhere := strSQLWhere + '[' + strTableName + '].[' + strTableFieldName +']' + strOperation + '''' + strValue + ''''+ ID_AND
      else
        strSQLWhere := strSQLWhere + '[' + strTableName + '].[' + strTableFieldName +']' + strOperation + strValue + ID_AND;
    end;

    //get rid of the last ID_AND in strSQLWhere
    strSQLWhere := Copy(strSQLWhere, 0, Length(strSQLWhere) - AND_LENGTH);

    Result := strSQLWhere;
  except
    Result := '';
  end
end;

//-----------------------------------------------------------------------------
//                              GetRule
//-----------------------------------------------------------------------------
function TdmDBGW.GetRule(const strEntityName : String; const listRule : TEtyList) : String;
var
  i : Integer;

  strTableName : String;
  strSQLRule : String;
  etyRule : IEntity;
  strEntityFieldName, strOperation : String;
  strTableFieldName : String;
begin
  try
    if listRule = nil then
    begin
      Result := '';
      exit;
    end;

    strTableName := GetTableName(strEntityName);

    strSQLRule := '';
    for i := 0 to listRule.GetEntityCount - 1 do
    begin
      etyRule := listRule.GetEntity(i);

      strEntityFieldName := etyRule.GetAttributeValue(FD_ENTITY_FIELD_NAME);
      strTableFieldName := GetTableFieldName(strTableName, strEntityFieldName);
      strOperation := etyRule.GetAttributeValue(FD_OPERATION);

      strSQLRule := strSQLRule + strOperation + ' [' + strTableName + '].[' + strTableFieldName +']' + ID_AND;
    end;

    //get rid of the last ID_AND in strSQLRule
    strSQLRule := Copy(strSQLRule, 0, Length(strSQLRule) - AND_LENGTH);

    Result := strSQLRule;
  except
    Result := '';
  end
end;

//-----------------------------------------------------------------------------
//                              GetPKCondition
//-----------------------------------------------------------------------------
function TdmDBGW.GetPKCondition(const pety : PIEntity) : String;
var
  strCondition : String;
  listEntityMapField : TEtyList;
  etyEntityMapField : IEntity;

  i, nFieldCount : Integer;
  strFieldName, strFieldType, strFieldValue : String;
  bIsPK : Boolean;
begin
  try
    //get Table Field
    listEntityMapField := GetEntityMapField(pety.EntityName);
    nFieldCount := listEntityMapField.GetEntityCount;
    if (nFieldCount = 0) then
    begin
      Result := '';
      exit;
    end;

    strCondition := '';
    strFieldName := '';
    strFieldType := '';
    strFieldValue := '';
    for i := 0 to nFieldCount - 1 do
    begin
      etyEntityMapField := listEntityMapField.GetEntity(i);

      strFieldName := etyEntityMapField.GetAttributeValue(ID_TABLE_FIELD_NAME);
      strFieldType := etyEntityMapField.GetAttributeValue(ID_TABLE_FIELD_TYPE);
      bIsPK := etyEntityMapField.GetAttributeValue(ID_ISPK);

      if bIsPK then
      begin
        strFieldValue := pety.GetAttributeValue(strFieldName);
        //if pk is '', then we can not get pk
        if (strFieldValue = '') then
        begin
          result := '';
          exit;
        end;

        strFieldValue := FormatValueDBToApp(strFieldValue, strFieldType);

        if strFieldType = ID_FIELD_TYPE_STRING then
          strCondition := strCondition + strFieldName + '=' + '''' + strFieldValue + '''' + ID_AND
        else
          strCondition := strCondition + strFieldName + '=' + strFieldValue + ID_AND;
      end;
    end;

    //get rid of the last ID_AND in strCondition
    strCondition := Copy(strCondition, 0, Length(strCondition) - AND_LENGTH);