none
Adaptando banco de dados access via codigo, c# RRS feed

  • Pergunta

  • Bom dia, eu tenho um sistema com um banco de dados ACCESS e estou com um grande problema, sempre que crio um novo campo no banco, eu tenho que cria-lo manualmente no banco do meu cliente, existe alguma ferramenta que faria essa adaptacao neste banco access, tipo gerar um script sql deste banco e depois compará-lo, como eh feito com o software SQL COMPARE, para bancos sql server. Ou alguem tem alguma outra solucao, via codigo mesmo, em que eu posso adaptar esse banco manualmente, ou seja, atualizar o banco (criar o novo campo na tabela ja existente e com dados) no meu sistema, no cliente

    Agradeco a atencao de todos, desde ja.

    Obrigado.

    abs. Marcus

    Ps. eu havia postado a mesma pergunta, mas foi movida para outro forum, do office, mas ja expliquei que gostaria de criar um código para incluir esse novo campo no banco via código, ou algum programa que faca isso.

    quinta-feira, 29 de julho de 2010 21:23

Respostas

  • Ae pessoal, depois de muito procurar eu encontrei um código que faz +- o que eu precisava, segue o código adaptado:

    Classe:

    public class Class2
      {
        private string _DBLoc;
    
        public string DBLoc
        {
          get { return _DBLoc; }
          set { _DBLoc = value; }
        }
        private OleDbConnection _globCN;
    
        public OleDbConnection GlobCN
        {
          get { return _globCN; }
          set { _globCN = value; }
        }
        private string _SelStr;
    
        public string SelStr
        {
          get { return _SelStr; }
          set { _SelStr = value; }
        }
    
        public bool CreateNewConnection()
        {
          string cnStr = string.Empty;
          try
          {
            if (!File.Exists(DBLoc))
            {
              return false;
            }
    
            cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBLoc + ";";
    
            if (GlobCN == null || GlobCN.State != ConnectionState.Open)
            {
              GlobCN = new OleDbConnection(cnStr);
              GlobCN.Open();
            }
            return true;
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool TestConnection()
        {
          try
          {
            switch (GlobCN.State)
            {
              case ConnectionState.Broken:
                return false;
                break;
              case ConnectionState.Closed:
                return false;
                break;
              case ConnectionState.Connecting:
                return true;
                break;
              case ConnectionState.Executing:
                return true;
                break;
              case ConnectionState.Fetching:
                return true;
                break;
              case ConnectionState.Open:
                return true;
                break;
              default:
                return false;
                break;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void DisconnectFromDB()
        {
          try
          {
            SelStr = string.Empty;
            if (GlobCN != null)
            {
              GlobCN.Close();
              GlobCN.Dispose();
              GlobCN = null;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void AddColumnToDB(string TableName, string ColumnName, string ColumnType)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName });
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][3].ToString() == ColumnName)
                {
                  break;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "ALTER Table " + TableName + " ADD COLUMN " + ColumnName + " " + ColumnType;
    
                if (ColumnType == "TEXT" || ColumnType == "VARCHAR")
                {
                  SelStr += "(50)";
                }
                SelStr += ";";
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
              }
            }
            else
            {
              
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void AlterColumnInDB(string Table, string Column)
        {
          try
          {
            if (CreateNewConnection())
            {
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "ALTER Table " + Table + " ALTER COLUMN " + Column + " TEXT(25)";
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
              }
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public DataTable RetornaSchemaTabelas(string Tabela)
        {
          return GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, Tabela });
        }
    
        public void DeleteColumnFromDB(string TableName, string ColumnName)
        {
          DataTable dt_field = new DataTable();
          bool exists = false;
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName });
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][3].ToString() == ColumnName)
                {
                  exists = true;
                  break;
                }
              }
    
              if (exists)
              {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                  SelStr = "ALTER Table " + TableName + " DROP COLUMN" + ColumnName;
    
                  cmd.CommandType = CommandType.Text;
                  cmd.CommandText = SelStr;
                  cmd.Connection = GlobCN;
    
                  cmd.ExecuteNonQuery();
                }
              }
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public DataTable RetornaSchema()
        {
          return GlobCN.GetSchema("TABLES");
        }
    
        public bool CreateTable(string TableName, List<string> ColumnNames)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  return true;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "CREATE TABLE " + TableName;
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
    
                if (ColumnNames != null && ColumnNames.Count > 0)
                {
                  for (int j = 0; j < ColumnNames.Count - 1; j++)
                  {
                    AddColumnToDB(TableName, ColumnNames[j], "Text");
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool CreateTable(string TableName, DataTable ColumnNames)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  return true;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "CREATE TABLE " + TableName;
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
    
                if (ColumnNames != null && ColumnNames.Rows.Count > 0)
                {
                  for (int j = 0; j < ColumnNames.Rows.Count -1; j++)
                  {
                    AddColumnToDB(TableName, ColumnNames.Rows[j][0].ToString(), ColumnNames.Rows[j][1].ToString());
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool DropTable(string TableName)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  using (OleDbCommand cmd = new OleDbCommand())
                  {
                    SelStr = "DROP TABLE " + TableName;
    
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = SelStr;
                    cmd.Connection = GlobCN;
    
                    cmd.ExecuteNonQuery();
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
      }
    

    E um exemplo de uso:

    private void button1_Click(object sender, EventArgs e)
        {
          DataTable dt = new DataTable();
          Class2 c1 = new Class2();
          Class2 c2 = new Class2();
          bool existe = false;
    
          c1.DBLoc = textBox1.Text;
          c2.DBLoc = textBox2.Text;
    
          if (c1.CreateNewConnection() && c2.CreateNewConnection())
          {
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
    
            dt1 = c1.RetornaSchema();
            dt2 = c2.RetornaSchema();
    
            for (int i = 0; i < dt1.Rows.Count - 1; i++)
            {
              existe = false;
              for (int j = 0; j < dt2.Rows.Count - 1; j++)
              {
                if (dt1.Rows[i][2].ToString() == dt2.Rows[j][2].ToString())
                {
                  existe = true;
                }
              }
              if (dt1.Rows[i][2].ToString() == "PALMEIRAS")
              {
                string teste = string.Empty;
              }
              if (!existe)
              {
                c2.CreateTable(dt1.Rows[i][2].ToString(), c1.RetornaSchemaTabelas(dt1.Rows[i][2].ToString()));
                c2.AddColumnToDB(dt1.Rows[i][2].ToString(), "AGORAVAI", "TEXT");
              }
            }
          }
          //Class2.DBLoc = textBox1.Text;
    
          //if (Class2.CreateNewConnection())
          //{
          //  dt = Class2.RetornaSchema();
          //}
        }
    Abraços...
    sexta-feira, 6 de agosto de 2010 20:51

Todas as Respostas

  • Marcus,

     

        Existem algumas alteranativas para seu problema. Vou citar uma:

     

        1) Crie um aplicativo console e mande junto com seu aplicativo. O aplicativo deve executar um código como o que está abaixo:

     

    Dim Cmd As OleDbCommand
    Dim SQL As String
    Dim objCmd As New OleDbCommand

    Dim Con = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;data source=" & Database & "")

    SQL = "CREATE TABLE tbCatalogue ([Id] COUNTER, [Stock Code] TEXT(10), [Account Number] TEXT(6))"
    Cmd = New OleDbCommand(SQL, Con)

    Con.Open()
    objCmd = New OleDbCommand(SQL, Con)
    objCmd.ExecuteNonQuery()
    Con.Close()

     

         Sendo que o banco de dados estará na mesma pasta que o aplicativo e que o banco.

         O código acima executa um create table. Você pode fazer com que ele leia e execute o conteúdo do arquivo script.sql que você vai criar com o comando que deseja executar.  Depois de executar, ele renomeia o arquivo (ou apaga).

         No seu aplicativo, coloque uma "chamada" para o aplicativo console. E no início do aplicativo console coloque: if (File.Exists("script.sql")) { o código aqui }

     

         Desta maneira, o funcionamento será:

            Ao abrir seu aplicativo será verificado se o arquivo texto "script.sql" existe. Se o arquivo "script.sql" existe:

            1) Executa o aplicativo console;

            2) Se existe o arquivo"script.sql"

                    2.1) Abre o arquivo e lê o comando que você quer executar;

                    2.2) Executa o comando com o trecho de código que coloquei acima;

                    2.3) Renomeia o arquivo;

     

            Assim cada vez que o seu cliente abre o aplicativo, se houverem atualizações no banco de dados, elas serão executadas antes. Com o recurso de macro do access você poderia fazer de outra maneira. Mas faz muuuuuito tempo que nem tenho access instalado.

     

    sábado, 31 de julho de 2010 13:13
  • Ok Ricardo, muito obrigado pela ajuda, não tinha pensado nesta possibilidade ainda, mas ainda me deparo com um problema, eu tenho alguns clientes, e não necessáriamente eu faço as atualizações neles, faço apenas em um ou outro, então como eu farei para gerar um script do banco access e fazer os ifs da vida, para criar o campo ou tabela, se ainda não existir, não sei se vc me entendeu, mas ainda continuo com essa dúvida. Porque senão seria fácil, cada vez que eu alterasse alguma coisa na estrutura do banco eu geraria um script manualmente e faria esse app console como vc disse, mas é que eu não sei se aquele cliente recebeu a atualização com aquele campo entende, então precisaria gerar tipo um script, como o sql server faz, quando eu gero um script por ele, ele faz vários ifs, se existe não cria, entendeu?

    Mas agradeço a ajuda.

    segunda-feira, 2 de agosto de 2010 15:12
  • Ae pessoal, depois de muito procurar eu encontrei um código que faz +- o que eu precisava, segue o código adaptado:

    Classe:

    public class Class2
      {
        private string _DBLoc;
    
        public string DBLoc
        {
          get { return _DBLoc; }
          set { _DBLoc = value; }
        }
        private OleDbConnection _globCN;
    
        public OleDbConnection GlobCN
        {
          get { return _globCN; }
          set { _globCN = value; }
        }
        private string _SelStr;
    
        public string SelStr
        {
          get { return _SelStr; }
          set { _SelStr = value; }
        }
    
        public bool CreateNewConnection()
        {
          string cnStr = string.Empty;
          try
          {
            if (!File.Exists(DBLoc))
            {
              return false;
            }
    
            cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBLoc + ";";
    
            if (GlobCN == null || GlobCN.State != ConnectionState.Open)
            {
              GlobCN = new OleDbConnection(cnStr);
              GlobCN.Open();
            }
            return true;
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool TestConnection()
        {
          try
          {
            switch (GlobCN.State)
            {
              case ConnectionState.Broken:
                return false;
                break;
              case ConnectionState.Closed:
                return false;
                break;
              case ConnectionState.Connecting:
                return true;
                break;
              case ConnectionState.Executing:
                return true;
                break;
              case ConnectionState.Fetching:
                return true;
                break;
              case ConnectionState.Open:
                return true;
                break;
              default:
                return false;
                break;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void DisconnectFromDB()
        {
          try
          {
            SelStr = string.Empty;
            if (GlobCN != null)
            {
              GlobCN.Close();
              GlobCN.Dispose();
              GlobCN = null;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void AddColumnToDB(string TableName, string ColumnName, string ColumnType)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName });
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][3].ToString() == ColumnName)
                {
                  break;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "ALTER Table " + TableName + " ADD COLUMN " + ColumnName + " " + ColumnType;
    
                if (ColumnType == "TEXT" || ColumnType == "VARCHAR")
                {
                  SelStr += "(50)";
                }
                SelStr += ";";
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
              }
            }
            else
            {
              
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void AlterColumnInDB(string Table, string Column)
        {
          try
          {
            if (CreateNewConnection())
            {
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "ALTER Table " + Table + " ALTER COLUMN " + Column + " TEXT(25)";
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
              }
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public DataTable RetornaSchemaTabelas(string Tabela)
        {
          return GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, Tabela });
        }
    
        public void DeleteColumnFromDB(string TableName, string ColumnName)
        {
          DataTable dt_field = new DataTable();
          bool exists = false;
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName });
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][3].ToString() == ColumnName)
                {
                  exists = true;
                  break;
                }
              }
    
              if (exists)
              {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                  SelStr = "ALTER Table " + TableName + " DROP COLUMN" + ColumnName;
    
                  cmd.CommandType = CommandType.Text;
                  cmd.CommandText = SelStr;
                  cmd.Connection = GlobCN;
    
                  cmd.ExecuteNonQuery();
                }
              }
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public DataTable RetornaSchema()
        {
          return GlobCN.GetSchema("TABLES");
        }
    
        public bool CreateTable(string TableName, List<string> ColumnNames)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  return true;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "CREATE TABLE " + TableName;
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
    
                if (ColumnNames != null && ColumnNames.Count > 0)
                {
                  for (int j = 0; j < ColumnNames.Count - 1; j++)
                  {
                    AddColumnToDB(TableName, ColumnNames[j], "Text");
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool CreateTable(string TableName, DataTable ColumnNames)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  return true;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "CREATE TABLE " + TableName;
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
    
                if (ColumnNames != null && ColumnNames.Rows.Count > 0)
                {
                  for (int j = 0; j < ColumnNames.Rows.Count -1; j++)
                  {
                    AddColumnToDB(TableName, ColumnNames.Rows[j][0].ToString(), ColumnNames.Rows[j][1].ToString());
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool DropTable(string TableName)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  using (OleDbCommand cmd = new OleDbCommand())
                  {
                    SelStr = "DROP TABLE " + TableName;
    
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = SelStr;
                    cmd.Connection = GlobCN;
    
                    cmd.ExecuteNonQuery();
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
      }
    

    E um exemplo de uso:

    private void button1_Click(object sender, EventArgs e)
        {
          DataTable dt = new DataTable();
          Class2 c1 = new Class2();
          Class2 c2 = new Class2();
          bool existe = false;
    
          c1.DBLoc = textBox1.Text;
          c2.DBLoc = textBox2.Text;
    
          if (c1.CreateNewConnection() && c2.CreateNewConnection())
          {
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
    
            dt1 = c1.RetornaSchema();
            dt2 = c2.RetornaSchema();
    
            for (int i = 0; i < dt1.Rows.Count - 1; i++)
            {
              existe = false;
              for (int j = 0; j < dt2.Rows.Count - 1; j++)
              {
                if (dt1.Rows[i][2].ToString() == dt2.Rows[j][2].ToString())
                {
                  existe = true;
                }
              }
              if (dt1.Rows[i][2].ToString() == "PALMEIRAS")
              {
                string teste = string.Empty;
              }
              if (!existe)
              {
                c2.CreateTable(dt1.Rows[i][2].ToString(), c1.RetornaSchemaTabelas(dt1.Rows[i][2].ToString()));
                c2.AddColumnToDB(dt1.Rows[i][2].ToString(), "AGORAVAI", "TEXT");
              }
            }
          }
          //Class2.DBLoc = textBox1.Text;
    
          //if (Class2.CreateNewConnection())
          //{
          //  dt = Class2.RetornaSchema();
          //}
        }
    Abraços...
    sexta-feira, 6 de agosto de 2010 20:51
  • Nova parte

    namespace WindowsFormsApplication6
    {
      public partial class Form1 : Form
      {
        public Form1()
        {
          InitializeComponent();
        }
    
        private void button1_Click(object sender, EventArgs e)
        {
          DataTable dt = new DataTable();
          Class2 c1 = new Class2();
          Class2 c2 = new Class2();
          bool existe = false;
          bool existeCol = false;
    
          c1.DBLoc = textBox1.Text;
          c2.DBLoc = textBox2.Text;
    
          if (c1.CreateNewConnection() && c2.CreateNewConnection())
          {
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            DataTable dtCol1 = new DataTable();
            DataTable dtCol2 = new DataTable();
    
            dt1 = c1.RetornaSchema();
            dt2 = c2.RetornaSchema();
    
            for (int i = 0; i < dt1.Rows.Count - 1; i++)
            {
              existe = false;
              for (int j = 0; j < dt2.Rows.Count - 1; j++)
              {
                if (dt1.Rows[i][2].ToString() == dt2.Rows[j][2].ToString())
                {
                  existe = true;
                  dtCol1 = c1.GetColumns(dt1.Rows[i][2].ToString());
                  dtCol2 = c2.GetColumns(dt2.Rows[j][2].ToString());
                  for (int a = 0; a < dtCol1.Columns.Count; a++)
                  {
                    existeCol = false;
                    for (int b = 0; b < dtCol2.Columns.Count; b++)
                    {
                      if (dtCol1.Columns[a].ToString() == dtCol2.Columns[b].ToString())
                      {
                        existeCol = true;
                        break;
                      }
                    }
                    if (!existeCol)
                    {
                      c2.AddColumnToDB(dt2.Rows[j][2].ToString(), dtCol1.Columns[a].ToString(), dtCol1.Columns[a].DataType.Name);
                    }
                  }
                  break;
                }
              }
              
              if (!existe)
              {
                c2.CreateTable(dt1.Rows[i][2].ToString(), c1.RetornaSchemaTabelas(dt1.Rows[i][2].ToString()));
                c2.AddColumnToDB(dt1.Rows[i][2].ToString(), "AGORAVAI", "TEXT");
              }
            }
          }
          //Class2.DBLoc = textBox1.Text;
    
          //if (Class2.CreateNewConnection())
          //{
          //  dt = Class2.RetornaSchema();
          //}
        }
      }
    }
    

    outra parte

    namespace WindowsFormsApplication6
    {
      public class Class2
      {
        private string _DBLoc;
    
        public string DBLoc
        {
          get { return _DBLoc; }
          set { _DBLoc = value; }
        }
        private OleDbConnection _globCN;
    
        public OleDbConnection GlobCN
        {
          get { return _globCN; }
          set { _globCN = value; }
        }
        private string _SelStr;
    
        public string SelStr
        {
          get { return _SelStr; }
          set { _SelStr = value; }
        }
    
        public bool CreateNewConnection()
        {
          string cnStr = string.Empty;
          try
          {
            if (!File.Exists(DBLoc))
            {
              return false;
            }
    
            cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBLoc + ";";
    
            if (GlobCN == null || GlobCN.State != ConnectionState.Open)
            {
              GlobCN = new OleDbConnection(cnStr);
              GlobCN.Open();
            }
            return true;
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool TestConnection()
        {
          try
          {
            switch (GlobCN.State)
            {
              case ConnectionState.Broken:
                return false;
                break;
              case ConnectionState.Closed:
                return false;
                break;
              case ConnectionState.Connecting:
                return true;
                break;
              case ConnectionState.Executing:
                return true;
                break;
              case ConnectionState.Fetching:
                return true;
                break;
              case ConnectionState.Open:
                return true;
                break;
              default:
                return false;
                break;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void DisconnectFromDB()
        {
          try
          {
            SelStr = string.Empty;
            if (GlobCN != null)
            {
              GlobCN.Close();
              GlobCN.Dispose();
              GlobCN = null;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void AddColumnToDB(string TableName, string ColumnName, string ColumnType)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName });
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][3].ToString() == ColumnName)
                {
                  break;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "ALTER Table " + TableName + " ADD COLUMN " + ColumnName + " " + ColumnType;
    
                if (ColumnType == "TEXT" || ColumnType == "VARCHAR")
                {
                  SelStr += "(50)";
                }
                SelStr += ";";
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
              }
            }
            else
            {
              
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void AlterColumnInDB(string Table, string Column)
        {
          try
          {
            if (CreateNewConnection())
            {
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "ALTER Table " + Table + " ALTER COLUMN " + Column + " TEXT(25)";
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
              }
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public DataTable RetornaSchemaTabelas(string Tabela)
        {
          return GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, Tabela });
        }
    
        public void DeleteColumnFromDB(string TableName, string ColumnName)
        {
          DataTable dt_field = new DataTable();
          bool exists = false;
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName });
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][3].ToString() == ColumnName)
                {
                  exists = true;
                  break;
                }
              }
    
              if (exists)
              {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                  SelStr = "ALTER Table " + TableName + " DROP COLUMN" + ColumnName;
    
                  cmd.CommandType = CommandType.Text;
                  cmd.CommandText = SelStr;
                  cmd.Connection = GlobCN;
    
                  cmd.ExecuteNonQuery();
                }
              }
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public DataTable RetornaSchema()
        {
          return GlobCN.GetSchema("TABLES");
        }
    
        public DataTable GetColumns(string tableName)
        {
          DataTable dt = new DataTable();
          if (CreateNewConnection())
          {
            //dt = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName });
    
            OleDbCommand cmdSelect;
            OleDbDataReader dr;
            //cmdSelect = new OleDbCommand("SELECT * FROM " + tableName, GlobCN);
            cmdSelect = GlobCN.CreateCommand();
            cmdSelect.CommandText = @"SELECT * FROM " + tableName;
            dr = cmdSelect.ExecuteReader(CommandBehavior.SchemaOnly);
            for (int i = 0; i <= dr.FieldCount - 1; i++)
            {
              dt.Columns.Add(dr.GetName(i));
              dt.Columns[i].DataType = dr.GetFieldType(i);
            }
            dr.Close();
          }
          return dt;
        }
    
        public bool CreateTable(string TableName, List<string> ColumnNames)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  return true;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "CREATE TABLE " + TableName;
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
    
                if (ColumnNames != null && ColumnNames.Count > 0)
                {
                  for (int j = 0; j < ColumnNames.Count - 1; j++)
                  {
                    AddColumnToDB(TableName, ColumnNames[j], "Text");
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool CreateTable(string TableName, DataTable ColumnNames)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  return true;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "CREATE TABLE " + TableName;
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
    
                if (ColumnNames != null && ColumnNames.Rows.Count > 0)
                {
                  for (int j = 0; j < ColumnNames.Rows.Count -1; j++)
                  {
                    AddColumnToDB(TableName, ColumnNames.Rows[j][0].ToString(), ColumnNames.Rows[j][1].ToString());
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool DropTable(string TableName)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  using (OleDbCommand cmd = new OleDbCommand())
                  {
                    SelStr = "DROP TABLE " + TableName;
    
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = SelStr;
                    cmd.Connection = GlobCN;
    
                    cmd.ExecuteNonQuery();
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
      }
    }
    
    
    sexta-feira, 10 de setembro de 2010 21:01
  • Nova Parte, atualizado....

    public partial class Form1 : Form
      {
        public Form1()
        {
          InitializeComponent();
        }
    
        private void button1_Click(object sender, EventArgs e)
        {
          DataTable dt = new DataTable();
          Class2 c1 = new Class2();
          Class2 c2 = new Class2();
          bool existe = false;
          bool existeCol = false;
    
          c1.DBLoc = textBox1.Text;
          c2.DBLoc = textBox2.Text;
    
          if (c1.CreateNewConnection() && c2.CreateNewConnection())
          {
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            DataTable dtCol1 = new DataTable();
            DataTable dtCol2 = new DataTable();
    
            dt1 = c1.RetornaSchema();
            dt2 = c2.RetornaSchema();
    
            for (int i = 0; i < dt1.Rows.Count - 1; i++)
            {
              existe = false;
              for (int j = 0; j < dt2.Rows.Count - 1; j++)
              {
                if (dt1.Rows[i][3].ToString() != "TABLE")
                {
                  continue;
                }
                if (dt1.Rows[i][2].ToString() == dt2.Rows[j][2].ToString())
                {
                  existe = true;
                  dtCol1 = c1.GetColumns(dt1.Rows[i][2].ToString());
                  dtCol2 = c2.GetColumns(dt2.Rows[j][2].ToString());
                  for (int a = 0; a < dtCol1.Rows.Count; a++)
                  {
                    existeCol = false;
                    for (int b = 0; b < dtCol2.Rows.Count; b++)
                    {
                      if (dtCol1.Rows[a]["ColumnName"].ToString() == dtCol2.Rows[b]["ColumnName"].ToString())
                      {
                        existeCol = true;
                        break;
                      }
    
                    }
                    if (!existeCol)
                    {
                      c2.AddColumnToDB(dt2.Rows[j][2].ToString(), dtCol1.Rows[a]["ColumnName"].ToString(), dtCol1.Rows[a]["DataType"].ToString(), dtCol1.Rows[a]["ColumnSize"].ToString(), dtCol1.Rows[a]["NumericPrecision"].ToString(), dtCol1.Rows[a]["NumericScale"].ToString());
                    }
                  }
                  break;
                  //for (int a = 0; a < dtCol1.Columns.Count; a++)
                  //{
                  //  existeCol = false;
                  //  for (int b = 0; b < dtCol2.Columns.Count; b++)
                  //  {
                  //    if (dtCol1.Columns[a].ToString() == dtCol2.Columns[b].ToString())
                  //    {
                  //      existeCol = true;
                  //      break;
                  //    }
                  //  }
                  //  if (!existeCol)
                  //  {
                  //    c2.AddColumnToDB(dt2.Rows[j][2].ToString(), dtCol1.Columns[a].ToString(), dtCol1.Columns[a].DataType.Name);
                  //  }
                  //}
                  //break;
                }
              }
              
              if (!existe)
              {
                c2.CreateTable(dt1.Rows[i][2].ToString(), c1.RetornaSchemaTabelas(dt1.Rows[i][2].ToString()));
                //c2.AddColumnToDB(dt1.Rows[i][2].ToString(), "AGORAVAI", "TEXT");
              }
            }
          }
          //Class2.DBLoc = textBox1.Text;
    
          //if (Class2.CreateNewConnection())
          //{
          //  dt = Class2.RetornaSchema();
          //}
        }
      }
    
    public class Class2
      {
        private string _DBLoc;
    
        public string DBLoc
        {
          get { return _DBLoc; }
          set { _DBLoc = value; }
        }
        private OleDbConnection _globCN;
    
        public OleDbConnection GlobCN
        {
          get { return _globCN; }
          set { _globCN = value; }
        }
        private string _SelStr;
    
        public string SelStr
        {
          get { return _SelStr; }
          set { _SelStr = value; }
        }
    
        public bool CreateNewConnection()
        {
          string cnStr = string.Empty;
          try
          {
            if (!File.Exists(DBLoc))
            {
              return false;
            }
    
            cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBLoc + ";";
    
            if (GlobCN == null || GlobCN.State != ConnectionState.Open)
            {
              GlobCN = new OleDbConnection(cnStr);
              GlobCN.Open();
            }
            return true;
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool TestConnection()
        {
          try
          {
            switch (GlobCN.State)
            {
              case ConnectionState.Broken:
                return false;
                break;
              case ConnectionState.Closed:
                return false;
                break;
              case ConnectionState.Connecting:
                return true;
                break;
              case ConnectionState.Executing:
                return true;
                break;
              case ConnectionState.Fetching:
                return true;
                break;
              case ConnectionState.Open:
                return true;
                break;
              default:
                return false;
                break;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void DisconnectFromDB()
        {
          try
          {
            SelStr = string.Empty;
            if (GlobCN != null)
            {
              GlobCN.Close();
              GlobCN.Dispose();
              GlobCN = null;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public void AddColumnToDB(string TableName, string ColumnName, string ColumnType, string ColumnSize, string NumericPrecision, string NumericScale)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName });
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][3].ToString() == ColumnName)
                {
                  break;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "ALTER Table " + TableName + " ADD COLUMN " + ColumnName + " " + RetornaTipo(ColumnType, ColumnSize, NumericPrecision, NumericScale) + ";";
    
                //if (ColumnType == "VARCHAR" )
                //{
                //  SelStr += "(50)";
                //}
                //SelStr += ";";
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
              }
            }
            else
            {
              
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        private string RetornaTipo(string ColumnType, string ColumnSize, string NumericPrecision, string NumericScale)
        {
          switch (ColumnType)
          {
            case "System.String":
              if (Convert.ToInt32(ColumnSize) > 255)
              {
                return "LONGTEXT";
              }
              else
              {
                return "VARCHAR(" + ColumnSize + ")";
              }
            case "System.Double":
              return "DOUBLE";
            case "System.Numeric":
              return "NUMERIC(" + NumericPrecision + "," + NumericScale + ")";
            case "System.Int32":
              return "INTEGER";
            case "System.DateTime":
              return "DATETIME";
            case "System.Boolean":
              return "BIT";
            case "System.Byte[]":
              return "LONGBINARY";
            case "System.Byte":
              return "BYTE";
            case "System.Decimal":
              return "DECIMAL(" + NumericPrecision + "," + NumericScale + ")";
            default:
              return "VARCHAR(50)";
              break;
          }
        }
    
        private string RetornaTipo(string ColumnType)
        {
          switch (ColumnType)
          {
            case "System.string":
              return "VARCHAR";
              break;
            case "System.double":
              return "DOUBLE";
              break;
            case "System.Numeric":
              return "NUMERIC";
              break;
            case "System.int":
              return "INTEGER";
              break;
            default:
              return "VARCHAR";
              break;
          }
        }
    
        public void AlterColumnInDB(string Table, string Column)
        {
          try
          {
            if (CreateNewConnection())
            {
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "ALTER Table " + Table + " ALTER COLUMN " + Column + " TEXT(25)";
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
              }
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public DataTable RetornaSchemaTabelas(string Tabela)
        {
          return GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, Tabela });
        }
    
        public void DeleteColumnFromDB(string TableName, string ColumnName)
        {
          DataTable dt_field = new DataTable();
          bool exists = false;
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName });
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][3].ToString() == ColumnName)
                {
                  exists = true;
                  break;
                }
              }
    
              if (exists)
              {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                  SelStr = "ALTER Table " + TableName + " DROP COLUMN" + ColumnName;
    
                  cmd.CommandType = CommandType.Text;
                  cmd.CommandText = SelStr;
                  cmd.Connection = GlobCN;
    
                  cmd.ExecuteNonQuery();
                }
              }
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public DataTable RetornaSchema()
        {
          return GlobCN.GetSchema("TABLES");
        }
    
        public DataTable GetColumns(string tableName)
        {
          DataTable dt = new DataTable();
          if (CreateNewConnection())
          {
            //dt = GlobCN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName });
    
            OleDbCommand cmdSelect;
            OleDbDataReader dr;
            //cmdSelect = new OleDbCommand("SELECT * FROM " + tableName, GlobCN);
            cmdSelect = GlobCN.CreateCommand();
            cmdSelect.CommandText = @"SELECT * FROM [" + tableName + "]";
            //dr = cmdSelect.ExecuteReader(CommandBehavior.SchemaOnly);
            dr = cmdSelect.ExecuteReader();
            dt = dr.GetSchemaTable();
            //for (int i = 0; i <= dr.FieldCount - 1; i++)
            //{
            //  dt.Columns.Add(dr.GetName(i));
            //  dt.Columns[i].DataType = dr.GetFieldType(i);
    
    
            //  var a = dr.GetDataTypeName(i);
            //}
            dr.Close();
          }
          return dt;
        }
    
        public bool CreateTable(string TableName, List<string> ColumnNames)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  return true;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "CREATE TABLE " + TableName;
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
    
                if (ColumnNames != null && ColumnNames.Count > 0)
                {
                  for (int j = 0; j < ColumnNames.Count - 1; j++)
                  {
                    //AddColumnToDB(TableName, ColumnNames[j], "Text");
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool CreateTable(string TableName, DataTable ColumnNames)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  return true;
                }
              }
    
              using (OleDbCommand cmd = new OleDbCommand())
              {
                SelStr = "CREATE TABLE " + TableName;
    
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SelStr;
                cmd.Connection = GlobCN;
    
                cmd.ExecuteNonQuery();
    
                if (ColumnNames != null && ColumnNames.Rows.Count > 0)
                {
                  for (int j = 0; j < ColumnNames.Rows.Count -1; j++)
                  {
                    //AddColumnToDB(TableName, ColumnNames.Rows[j][0].ToString(), ColumnNames.Rows[j][1].ToString());
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
        public bool DropTable(string TableName)
        {
          DataTable dt_field = new DataTable();
    
          try
          {
            if (CreateNewConnection())
            {
              dt_field = GlobCN.GetSchema("TABLES");
    
              for (int i = 0; i < dt_field.Rows.Count - 1; i++)
              {
                if (dt_field.Rows[i][2].ToString() == TableName)
                {
                  using (OleDbCommand cmd = new OleDbCommand())
                  {
                    SelStr = "DROP TABLE " + TableName;
    
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = SelStr;
                    cmd.Connection = GlobCN;
    
                    cmd.ExecuteNonQuery();
                  }
                }
              }
    
              return true;
            }
            else
            {
              return false;
            }
          }
          catch (Exception)
          {
    
            throw;
          }
        }
    
      }
    
    sexta-feira, 17 de setembro de 2010 20:51