Working with Filestream and BLOB

Question

Friends, by company order, I need to take all the files that a system stores in a Windows directory and store in the Database.

In the database there is a column that stores the directory where the files are, I thought to go through each row of the column that stores the path of the files using Datareader, convert the file to binary and store in the database.

That’s what I got so far, but I’m stuck:

static void FileStreamMethod()
    {
        /*Conecta ao SQL*/
        SqlConnection cnnSQL = new SqlConnection(@"Data Source=xxxx; " +
                                                 "Initial Catalog=SCF2_HOMOLOG;" +
                                                 "User ID=xxxxx;" +
                                                 "Password=xxxxx");

        SqlCommand sqlCommand = new SqlCommand("SELECT id_DocumentoProcessoCompra, Path_Documento " +
                                               "FROM SCF_DocumentoProcessoCompra", cnnSQL);

        try { cnnSQL.Open(); }
        catch (SqlException ex) { Console.WriteLine(ex.Message); }

        SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();


        if (sqlDataReader.HasRows)
        {
            while (sqlDataReader.Read())
            {
                try
                {
                    string path = @"C:\Users\Lucas Garcia\Desktop\AnexoProcesso\" + sqlDataReader.GetString(1);
                    /* Objeto origem do arquivo */
                    FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read);

                    /*Le o binario do arquivo*/
                    BinaryReader binaryReader = new BinaryReader(fileStream);
                    byte[] files = binaryReader.ReadBytes(Convert.ToInt32(fileStream.Length));

                    SqlCommand sqlUpdateCommand = new SqlCommand("UPDATE SCF_DocumentoProcessoCompra " +
                                                                 "SET Documento = " + files +
                                                                 "WHERE id_ModeloDocumento = " + sqlDataReader.GetInt32(0), cnnSQL);

                    sqlUpdateCommand.ExecuteNonQuery();

                    binaryReader.Close();
                    fileStream.Close();

                    Console.WriteLine("ID {1}\nDocumento {0} armazenado no DB", sqlDataReader.GetString(1), sqlDataReader.GetInt32(0));
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                }

            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }

        sqlDataReader.Close();
    }

Answer 1

Follow an example approach to your problem, if sql has access to the repository where the files are, you can do it yourself.

static void FileStreamMethod()
    {
        /*Conecta ao SQL*/
        SqlConnection cnnSQL = new SqlConnection("Data Source=x.x.x.x;" +
                                                 "Initial Catalog=db_base_clientes;" +
                                                 "User ID=base_clientes;" +
                                                 "Password=xxxxx");
        cnnSQL.Open();

        SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

        if (sqlDataReader.HasRows)
        {
            while (sqlDataReader.Read())
            {
                /* Objeto para origem do arquivo */
                string path = @"C:\"; // EXISTE UMA COLUNA NO SQL COM OS PATHS, FAZER foreach PARA CADA PATH
                using(fileStream = new FileStream(path, FileMode.Open, FileAccess.Read)){

                    byte[] documento;
                    /*Le o binario*/
                    using( binaryReader = new BinaryReader(fileStream))
                    {
                        documento = binaryReader.ReadBytes(Convert.ToInt32(fileStream.Length));
                    }

                    try
                    {
                        //Substitua [ID] pela sua coluna de índice
                        SqlCommand sqlUpdateCommand = new SqlCommand("UPDATE SCF_DocumentoProcessoCompra" +
                                                                        "SET Documento = @documento" +
                                                                    "WHERE [ID] = @ID ", cnnSQL);

                        //Você precisa saber o tipo da coluna para escolher o SqlDbType adequado.
                        //Deixei como Image só para exemplo
                        sqlUpdateCommand.Add("@documento", SqlDbType.Image, documento.Length).Value = file;
                        sqlUpdateCommand.Add("@id", SqlDbType.Int).Value = 0 // <- Coloque aqui o seu atributo de id da linha que está lendo;

                        sqlUpdateCommand.ExecuteNonQuery();
                        sqlUpdateCommand.Dispose();

                    }
                    catch(Exeption e){
                        sqlDataReader.Close();
                        cnnSQL.Close();                            
                        throw e;
                    }
                }               

            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }

    sqlDataReader.Close();
    //Não esqueça de fechar a conexão do banco
    cnnSQL.Close();
}

Answer 2

Datareader does not accept 2 SQL commands on the same connection. So I did a gambiarra (since I will only run it once and never again) and opened 2 connections with SQL. I solved the problem like this:

static void FileStreamMethod()
    {
        /*Conecta ao SQL*/
        SqlConnection cnnSQL = new SqlConnection(@"Data Source=x.x.x.x; " +
                                                 "Initial Catalog=SCF2_HOMOLOG;" +
                                                 "User ID=xxxxxxx;" +
                                                 "Password=xxxxxxx");
        SqlConnection cnnSQL2 = new SqlConnection(@"Data Source=x.x.x.x; " +
                                                 "Initial Catalog=SCF2_HOMOLOG;" +
                                                 "User ID=xxxxxxx;" +
                                                 "Password=xxxxxxx");
        try { cnnSQL.Open(); cnnSQL2.Open(); }
        catch (SqlException ex) { Console.WriteLine(ex.Message); }

        SqlCommand sqlCommand = new SqlCommand("SELECT id_DocumentoProcessoCompra, Path_Documento " +
                                               "FROM SCF_DocumentoProcessoCompra", cnnSQL);


        SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();


        if (sqlDataReader.HasRows)
        {
            while (sqlDataReader.Read())
            {
                try
                {
                    string path = @"C:\Users\Lucas Garcia\Desktop\AnexoProcesso\" + sqlDataReader.GetString(1);
                    /* Objeto origem do arquivo */
                    FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read);

                    /*Le o binario do arquivo*/
                    BinaryReader binaryReader = new BinaryReader(fileStream);
                    byte[] file = binaryReader.ReadBytes(Convert.ToInt32(fileStream.Length));

                    SqlCommand sqlUpdateCommand = new SqlCommand("UPDATE SCF_DocumentoProcessoCompra " +
                                                                 "SET Documento = @documento " +
                                                                 "WHERE id_DocumentoProcessoCompra = @ID", cnnSQL2);

                    sqlUpdateCommand.Parameters.Add("@ID", SqlDbType.Int);
                    sqlUpdateCommand.Parameters["@ID"].Value = sqlDataReader.GetInt32(0);
                    sqlUpdateCommand.Parameters.Add("@documento", SqlDbType.Image);
                    sqlUpdateCommand.Parameters["@documento"].Value = file;

                    sqlUpdateCommand.ExecuteNonQuery();


                    binaryReader.Close();
                    fileStream.Close();

                    Console.WriteLine("ID {1}\nBinario: {2}\nDocumento {0} armazenado no DB", sqlDataReader.GetString(1), sqlDataReader.GetInt32(0), file);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }

            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }

        sqlDataReader.Close();
        cnnSQL.Close();
        cnnSQL2.Close();
    }