createtable.cpp

速达开源ERP系统

  sgField->Cells[3][FRow]=sgSelect->Cells[2][sgSelect->Row];
  sgField->Cells[4][FRow]=sgSelect->Cells[3][sgSelect->Row];
  sgField->Cells[5][FRow]=sgSelect->Cells[4][sgSelect->Row];
  sgField->Cells[6][FRow]=sgSelect->Cells[5][sgSelect->Row];
  sgField->Cells[7][FRow]=sgSelect->Cells[6][sgSelect->Row];
  sgField->Visible =true;
  FloatPanel2->Visible=false;
  FloatPanel1->Enabled=true;
}
//---------------------------------------------------------------------------

void __fastcall TfrmCreateTable::btnCreateClick(TObject *Sender)
{
  AnsiString mStr,SqlStr,InsSql;
  bool  HasCopy,HasIdentity;
  char AnsiChar;
  int ErrorNum;
  TComResultSet *m_TableRst;
  m_TableRst=NewResultSet();
  try
  {
    if(sgField->RowCount<2)
      throw Exception("没有数据结构资料!");
    if(MessageBox(this->Handle,"确认要修改表结构?","",MB_YESNO)==IDYES)
    {
      HasCopy=false;
      HasIdentity=false;
      mStr=AnsiString("select * from sysobjects where type='U' and name='")+cbxTable->Text+AnsiString("'");
      m_TableRst->Open(mStr,"");
      if(m_TableRst->RecordCount>0)
      {
        HasCopy=true;
        m_TableRst->Close();
        mStr=AnsiString("select * from sysobjects where type='U' and name='YW_")+cbxTable->Text.SubString(3,cbxTable->Text.Length()-2) +AnsiString("'");
        m_TableRst->Open(mStr,"");
        if(m_TableRst->RecordCount>0)
        {
          m_TableRst->Close();
          mStr=AnsiString("drop table YW_")+cbxTable->Text.SubString(3,cbxTable->Text.Length()-2);
          m_TableRst->Execute(mStr);
        }
        m_TableRst->StartTrans=0;
        m_TableRst->Execute("sp_dboption 'sderp','select into/bulkcopy','TRUE'");
        mStr=AnsiString("select * into YW_")+cbxTable->Text.SubString(3,cbxTable->Text.Length()-2)+AnsiString(" from ")+cbxTable->Text;
        m_TableRst->Execute(mStr);
        mStr=AnsiString("drop table ")+cbxTable->Text;
        m_TableRst->Execute(mStr);
      }
      else
        m_TableRst->Close();
      SqlStr=AnsiString("create table ")+cbxTable->Text+ AnsiString(" (");
      for(int i=1;i<sgField->RowCount;i+=2)
      {
        SqlStr+=sgField->Cells[2][i];
        mStr=sgField->Cells[3][i];
        AnsiChar=mStr[1];
        switch(AnsiChar)
        {
          case 'C':
          case 'D':
            SqlStr+=AnsiString("  varchar(")+sgField->Cells[4][i]+AnsiString(")");
            break;
          case 'F':
            SqlStr+=AnsiString("  decimal(")+sgField->Cells[4][i]+AnsiString(",")+sgField->Cells[5][i]+AnsiString(")");
            break;
          case 'L':
            SqlStr+=AnsiString("  int");
            break;
          case 'N':
            SqlStr+=AnsiString("  smallint");
            break;
          case 'S':
            SqlStr+=AnsiString("  tinyint");
            break;
          case 'B':
            SqlStr+=AnsiString("  bit");
            break;
          case 'M':
            SqlStr+=AnsiString("  text");
            break;
          case 'T':
            SqlStr+=AnsiString("  datetime");
            break;
        }
        SqlStr+=(sgField->Cells[7][i]=="1")?"  identity(1,1)":"  ";
        SqlStr+=(sgField->Cells[6][i]=="1")?"  null,":"  not null,";
        if(sgField->Cells[7][i]=="1")
          HasIdentity=true;
      }
      if(lbPk->Items->Count>0)
      {
        mStr="";
        for(int i=0;i<lbPk->Items->Count;i++)
          mStr+=lbPk->Items->Strings[i]+AnsiString(",");
        mStr=mStr.SubString(1,mStr.Length()-1);
        SqlStr+=AnsiString(" CONSTRAINT PK_")+cbxTable->Text+AnsiString(" PRIMARY KEY  NONCLUSTERED (")+
            mStr+AnsiString("))");
      }
      else
        SqlStr=SqlStr.SubString(1,SqlStr.Length()-1)+AnsiString(")");
      m_TableRst->Execute(SqlStr);
      if(HasCopy)
      {
        SqlStr=AnsiString("Insert into ")+cbxTable->Text+AnsiString(" (");
        InsSql=AnsiString("Select ");
        for(int i=1;i<sgField->RowCount;i+=2)
        {
          SqlStr+=sgField->Cells[2][i]+AnsiString(", ");
          mStr=sgField->Cells[3][i];
          AnsiChar=mStr[1];
          switch(AnsiChar)
          {
            case 'C':
            case 'D':
              if(sgField->Cells[2][i+1]!="")
                InsSql+=AnsiString("convert(varchar(")+sgField->Cells[4][i]+AnsiString("),")+sgField->Cells[2][i+1]+AnsiString("), ");
              else
                InsSql+=AnsiString("'', ");
              break;
            case 'F':
              if(sgField->Cells[2][i+1]!="")
                InsSql+=AnsiString("convert(decimal(")+sgField->Cells[4][i]+AnsiString(",")+sgField->Cells[5][i]+AnsiString("),")+sgField->Cells[2][i+1]+AnsiString("), ");
              else
                InsSql+=AnsiString("0, ");
              break;
            case 'L':
              if(sgField->Cells[2][i+1]!="")
                InsSql+=AnsiString("convert(int,")+sgField->Cells[2][i+1]+AnsiString("), ");
              else
                InsSql+=AnsiString("0, ");
              break;
            case 'N':
              if(sgField->Cells[2][i+1]!="")
                InsSql+=AnsiString("convert(smallint,")+sgField->Cells[2][i+1]+AnsiString("), ");
              else
                InsSql+=AnsiString("0, ");
              break;
            case 'S':
              if(sgField->Cells[2][i+1]!="")
                InsSql+=AnsiString("convert(tinyint,")+sgField->Cells[2][i+1]+AnsiString("), ");
              else
                InsSql+=AnsiString("0, ");
              break;
            case 'B':
              if(sgField->Cells[2][i+1]!="")
                InsSql+=AnsiString("convert(bit,")+sgField->Cells[2][i+1]+AnsiString("), ");
              else
                InsSql+=AnsiString("0, ");
              break;
            case 'M':
              if(sgField->Cells[2][i+1]!="")
                InsSql+=AnsiString("convert(text,")+sgField->Cells[2][i+1]+AnsiString("), ");
              else
                InsSql+=AnsiString("'', ");
              break;
            case 'T':
              if(sgField->Cells[2][i+1]!="")
                InsSql+=AnsiString("convert(datetime,")+sgField->Cells[2][i+1]+AnsiString("), ");
              else
                InsSql+=AnsiString("getdate(), ");
              break;
          }
        }
        SqlStr=SqlStr.SubString(1,SqlStr.Length()-2)+AnsiString(")");
        InsSql=InsSql.SubString(1,InsSql.Length()-2)+AnsiString(" from YW_")+cbxTable->Text.SubString(3,cbxTable->Text.Length()-2);
        if(HasIdentity)
        {
          mStr=AnsiString("SET IDENTITY_INSERT ")+cbxTable->Text+AnsiString(" ON");
          m_TableRst->StartTrans=0;
          m_TableRst->Execute(WideString(mStr));
        }
        SqlStr+=AnsiString("  ")+InsSql;
        m_TableRst->Execute(SqlStr);
        if(HasIdentity)
        {
          mStr=AnsiString("SET IDENTITY_INSERT ")+cbxTable->Text+AnsiString(" OFF");
          m_TableRst->StartTrans=0;
          m_TableRst->Execute(mStr);
        }
      }
      m_TableRst->StartTrans=0;
      m_TableRst->Execute("sp_dboption 'sderp','select into/bulkcopy','TRUE'");
      ShowMessage("数据表更新成功!");
      this->btnRefreshClick(this);
    }
  }
  __finally
  {
    delete m_TableRst;
  }
}
//---------------------------------------------------------------------------


void __fastcall TfrmCreateTable::cbxTableClick(TObject *Sender)
{
  edTableDesc->Text =cbxTable->ItemData[1];
  edClassid->Text =cbxTable->ItemData[2];
  edDate->Text =cbxTable->ItemData[3];
}
//---------------------------------------------------------------------------