XML - Gerando tabelas no SQL Server

XML - Criando tabelas no SQL Server (SqlBulkCopy)

Neste artigo eu vou recordar como podemos criar tabelas no SQL Server a partir de arquivos XML usando o SqlBulkCopy e a linguagem C#.

Neste artigo veremos como podemos gerar tabelas no SQL Server a partir de arquivos XML e para isso vamos realizar tarefas básicas como:

Criar um DataTable a partir de um arquivo XML
Criar uma tabela no SQL Server usando um DataTable
Importando dados de um DataTable
Inserindo registros em uma tabela SQL Server

Dentre as diversas opções que temos para importar dados de um DataTable para uma Tabela podemos :

Inserir dados individualmente linha por linha executando uma consulta para cada linha
Usar o recurso BulkCopy do SQL que permite copiar todas as linhas de um Datatable e adicioná-las na tabela

Usaremos o SqlBulkCopy para importar dados a partir de um arquivo XML para uma tabela SQL.

A classe SqlBulkCopy fornece funcionalidade similar ao utilitário de linha de comando do Microsoft SQL Server conhecido como bcp. O bcp basicamente era usado para efetuar a cópia de grande volume de dados entre duas base de dados SQL Server.

O SqlBulkcopy vai além, pois permite ser usado também entre DataSets, DataTables , Arrays de objetos DataRow , DataReader, etc. e não somente entre base de dados SQL Server.

Como exemplo vou usar o arquivo funcionarios.xml que possui a seguinte estrutura:

<?xml version="1.0"?>
<funcionarios>
  <funcionario>
    <codigo>7369</codigo>
    <nome>Saulo Barbosa</nome>
    <cargo>Administrativo</cargo>
    <admissao>17-12-1980</admissao>
  </funcionario>
    ...
  <funcionario>
    <codigo>7788</codigo>
    <nome>Sandra Rocha</nome>
    <cargo>Analista</cargo>
    <admissao>19-04-1987</admissao>
  </funcionario>
  .....
    <codigo>7934</codigo>
    <nome>Miriam Rocha</nome>
    <cargo>Supervisora</cargo>
    <admissao>23-01-1982</admissao>
  </funcionario>
</funcionarios>

Com base neste arquivo vamos gerar a tabela no SQL Server com o mesmo nome onde a estrutura da tabela vai copiar os nomes das colunas com base nos elementos : codigo, nome, cargo e adminissao.

Recursos usados

VS 2017 Community
SQL Server Express 2012

Criando o projeto Windows Forms

Abra o VS 2017 Community e crie um novo projeto do tipo Windows Forms com o nome Xml_Database:

Inclua no projeto uma referência a System.Configuration :

A seguir no formulário Form1.cs inclua os seguintes controles a partir da ToolBox:

1 Panel
1 TextBox - txtArquivo
2 Buttons -btnLocalizar e btnImportar
1 ProgressBar - pgbar1
1 OpenFileDialog - ofdlg1

Inclua no arquivo App.Config a string de conexão com o banco de dados SQL Server:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.2" />
    </startup>
  <connectionStrings>
    <add name="conexaoSQL" connectionString="Data Source=MACORATTI;Initial Catalog=Estudo;
Integrated Security=True" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Disponha so controles conforme o leiaute da figura abaixo:

Implementando o código no formulário

No início do formulário inclua o código abaixo para obter a string de conexão a partir do arquivo App.Config:

string StrCon = ConfigurationManager.ConnectionStrings["conexaoSql"].ToString();

No Evento Click do botão Localizar inclua seguinte código:

private void btnLocalizar_Click(object sender, EventArgs e)
{
            // Configurações iniciais do OpenFileDialog
            this.ofdlg1.Multiselect = false;
            this.ofdlg1.Title = "Selecionar arquivo";
            ofdlg1.InitialDirectory = @"C:\dados\xml";
            //filtra para exibir somente arquivos xml
            ofdlg1.Filter = "XML (*.XML;*.xml)|*.XML;*.xml|" + "Todos (*.*)|*.*";
            ofdlg1.CheckFileExists = true;
            ofdlg1.CheckPathExists = true;
            ofdlg1.FilterIndex = 1;
            ofdlg1.RestoreDirectory = true;
            ofdlg1.ReadOnlyChecked = true;
            ofdlg1.ShowReadOnly = true;

            if (ofdlg1.ShowDialog() == DialogResult.OK)
           {
                try
                {
                    txtArquivo.Text = ofdlg1.FileName;
                }
                catch (Exception ex)
                {
                    // Não pode carregar o arquivo (problemas de permissão)
                    MessageBox.Show("Não é possível acessar o arquivo : " + ofdlg1.FileName
                                    + ". Você pode não ter permissão para ler o arquivo , ou " +
                                    " ele pode estar corrompido.\n\nErro reportado : " + ex.Message);
                }
          }
 }

O código acima abre uma caixa de diálogo para selecionar o arquivo XML e atribui o nome à caixa de texto : txtArquivo no formulário.

Agora no evento Click do botão Importar inclua o código abaixo:

 private void btnImportar_Click(object sender, EventArgs e)
        {
            string arquivoXML = txtArquivo.Text;

            if (File.Exists(arquivoXML))
            {
                // Converte o arquivo Xml para um DataTable  
                DataTable dt = CriaDataTableXML(arquivoXML);
                if (dt.Columns.Count == 0)
                    dt.ReadXml(arquivoXML);

                // Cria uma Consulta para criar a tabela no SQL Server
                string consultaSQL = CriaConsultaTabela(dt);
                SqlConnection con = new SqlConnection(StrCon);
                con.Open();

                // Deleta a tabela se ela já existir
                SqlCommand cmd = new SqlCommand("IF OBJECT_ID('dbo." + dt.TableName + "', 'U') IS NOT NULL 
DROP TABLE dbo."  + dt.TableName + ";", con);
                cmd.ExecuteNonQuery();

                // Cria a tabela
                cmd = new SqlCommand(consultaSQL , con);
                int verifica = cmd.ExecuteNonQuery();

                if (verifica != 0)
                {
                    // Copia os dados a partir do DataTable para a tabela SQL
                    using (var bulkCopy = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
                    {
                        // os nomes da coluna do DataTable usado correspondem aos nomes das colunas da tabela SQL,
                        // assim usei um laço foreach simples. No entanto, se os nomes das colunas
                        // não corresponderem, apenas passe qual nome do DataTabel corresponde ao nome da coluna SQL 
                        // em ColumnMappings
                        foreach (DataColumn col in dt.Columns)
                        {
                            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                        }

                        bulkCopy.BulkCopyTimeout = 600;
                        bulkCopy.DestinationTableName = dt.TableName;
                        bulkCopy.WriteToServer(dt);
                    }

                    MessageBox.Show("tabela Criada com Sucesso...");
                }
                con.Close();
            }
     }

Após a criação da tabela, adicionaremos os dados XML à tabela SQL uasndo o SqlBulkCopy.

A tabela abaixo descreve os membros da enumeração do SqlBulkCopyOptions:

Membro	Descrição
ChecConstraints	Aplica a verificação de restrições durante o processo de cópia
Default	Não usa opções para a operação de cópia em massa
FireTriggers	Permite triggers INSERT serem disparados durante a processo de cópia
KeepIdentity	Usa valores Identity da tabela fonte ao invés de gerar novos valores de identity baseados na semente da tabela de destino.
KeepNulls	Retêm valores fontes null
TableLock	Aplica um lock na tabela para a duração do processo de cópia. O padrão é o lock de linhas.
UseInternalTransaction	Faz com que cada lote de sql bulkcopy seja executado dentro de uma transação

O código acima cria a tabela a partir do arquivo XML selecionado e usa dois métodos para realizar essa tarefa:

CriaDataTableXML(arquivoXML)
CriaConsultaTabela(dt)

A seguir temos o código usado em cada um destes métodos:

1- CriaDataTableXML(arquivoXML)

        // Converte o arquivo Xml para um DataTable  
        public DataTable CriaDataTableXML(string arquivoXML)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(arquivoXML);
            DataTable Dt = new DataTable();
            try
            {
                Dt.TableName = GetNomeTabela(arquivoXML);
                XmlNode NodoEstructura = doc.DocumentElement.ChildNodes.Cast<XmlNode>().ToList()[0];
                pgbar1.Maximum = NodoEstructura.ChildNodes.Count;
                pgbar1.Value = 0;
                foreach (XmlNode columna in NodoEstructura.ChildNodes)
                {
                    Dt.Columns.Add(columna.Name, typeof(String));
                    Progresso();
                }

                XmlNode Filas = doc.DocumentElement;
                pgbar1.Maximum = Filas.ChildNodes.Count;
                pgbar1.Value = 0;
                foreach (XmlNode Fila in Filas.ChildNodes)
                {
                    List<string> Valores = Fila.ChildNodes.Cast<XmlNode>().ToList().Select(x => x.InnerText).ToList();
                    Dt.Rows.Add(Valores.ToArray());
                    Progresso();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return Dt;
        }

Neste código carregamos o arquivo XML e criamos um DataTable com o mesmo nome do arquivo XML obtido pelo método GetNomeTabela().

A seguir criamos a estrutura da tabela com base nos nós XML obtidos, e exibimos o progresso na ProgressBar usando o método Progresso().

Abaixo temos o código destes dois métodos:

        // Obtem o nome da tabela a partir do nome do arquivo XML
        public string GetNomeTabela(string arquivo)
        {
            FileInfo fi = new FileInfo(arquivo);
            string nomeTabela = fi.Name.Replace(fi.Extension, "");

            return nomeTabela;
        }

        // Exibe a ação da  ProgressBar
        public void Progresso()
        {
            if (pgbar1.Value < pgbar1.Maximum)
            {
                pgbar1.Value++;
                int percent = (int)(((double)pgbar1.Value / (double)pgbar1.Maximum) * 100);
                pgbar1.CreateGraphics().DrawString(percent.ToString() +
                    "%", new Font("Arial", (float)8.25, FontStyle.Regular), 
                    Brushes.Black, new PointF(pgbar1.Width / 2 - 10, pgbar1.Height / 2 - 7));

                Application.DoEvents();
            }
        }

2- CriaConsultaTabela(dt)

     public string CriaConsultaTabela(DataTable table)
     {
            string sqlsc = "CREATE TABLE " + table.TableName + "(";
            pgbar1.Maximum = table.Columns.Count;
            pgbar1.Value = 0;
            for (int i = 0; i < table.Columns.Count; i++)
            {
                sqlsc += "[" + table.Columns[i].ColumnName + "]";
                string columnType = table.Columns[i].DataType.ToString();
                switch (columnType)
                {
                    case "System.Int32":
                        sqlsc += " int ";
                        break;
                    case "System.Int64":
                        sqlsc += " bigint ";
                        break;
                    case "System.Int16":
                        sqlsc += " smallint";
                        break;
                    case "System.Byte":
                        sqlsc += " tinyint";
                        break;
                    case "System.Decimal":
                        sqlsc += " decimal ";
                        break;
                    case "System.DateTime":
                        sqlsc += " datetime ";
                        break;
                    case "System.String":
                    default:
                        sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : 
table.Columns[i].MaxLength.ToString());
                        break;
                }
                if (table.Columns[i].AutoIncrement)
                    sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + 
table.Columns[i].AutoIncrementStep.ToString() + ") ";
                if (!table.Columns[i].AllowDBNull)
                    sqlsc += " NOT NULL ";
                sqlsc += ",";

                Progresso();
            }
            return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
   }

Neste código montamos o comando SQL para criar a estrutura da tabela com base nas informações obtidas do arquivo XML.

Executando o projeto e clicando no botão Localizar temos o seguinte resultado:

Selecionando um arquivo XML e clicando no botão Importar temos o seguinte resultado:

Verificando no SQL Server o banco de dados Estudo, vemos a tabela funcionarios gerada com os dados obtidos do arquivo XML, conforme figura abaixo: