How can I attach a PDF file to a DB record in SQL with C #?


The need I am having is to know if there is any possibility of attaching PDF files or any type of file to a DB record in SQL Server 2008 R2 from a C # application made with VS 2012.

asked by Juan Manuel Palacios 09.02.2016 в 23:41

4 answers


It does not really matter what version of the IDE you use, with C # you can do many things, and among them you can choose to serialize the file (PDF or any other) to a Base64 string and save it as if it were a string:

using System;
using System.IO;

public class Serializador
    public static bool GuardarArchivo(string archivo)
        // Leemos todos los bytes del archivo y luego lo guardamos como Base64 en un string.
        string resultado = Convert.ToBase64String(File.ReadAllBytes(archivo));

        using (var cn = 
            new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString))
            using(var cmd = cn.CreateCommand())
                // Se invoca a un StoreProcedure para insertar el registro.
                cmd.CommandText = "usp_InsertArchivo";
                cmd.CommandType = CommandType.StoreProcedure;
                // Con la ayuda de FileInfo devolvemos unicamente el nombre del archivo.
                cmd.Parameters.AddWithValue("@nombreArchivo", new FileInfo(archivo).Name);
                // El resultado serializado del archivo pasa como un varchar cualquiera.
                cmd.Parameters.AddWithValue("@contenido", resultado);


                int cantidad = cmd.ExecuteNonQuery();

                // Si es mayor a 0 entonces se guardo correctamente.
                return (cantidad > 0);

answered by 10.02.2016 / 17:13

Yes, it is possible. You have three alternatives:

  • Store the file directly in database as type Blob and store the binary directly.

  • Store the file directly in database with type FileStream .

  • Store the file on disk and save the file path in a field of your table in the database.

  • Each alternative has its pros and cons after several discussions about it. The most recommended I've seen for SQL Server (at least on images, but applies to any type of file) is the second . In case you do not use SQL Server, the second option is discarded and I would recommend the third because it facilitates the maintenance of the files, the database is not so heavy, there is separation of issues, facilitates testing, etc.

    Here is more information: link

    answered by 09.02.2016 в 23:46

    If possible, although it is not recommended

    You can declare your column as VARBINARY in Sql

    And the code to upload the file would be more or less would be something like this:

    byte[] byteArray = cargarArchivo(); //Regresa tu archivo como un arreglo de bytes
    var command = new SqlCommand("Insert Into Tu_Tabla columna_archivo Values (@archivo)" , sqlConection); // Creamos un SqlCommand que tenga un insert comun, pero apuntando a la columna
    command.Parameters.Add("@archivo", SqlDbType.VarBinary, byteArray.Length).Value = byteArray; //Pasamos la referencia del arreglo de bytes
    command.ExecuteNonQuery();// Y ejecutamos el query sin retorno

    Working with files within the database is not a very good practice, since inserts and requests are more expensive than simple text or some other numerical type. But if possible.

    Here you touch that topic in English, take a look;)

    answered by 09.02.2016 в 23:54


    [ASP.NET] Burn file on base of data

    I explain how you could insert a file into a field in the table

    As you will see, a field of type varbinary is defined in the table and to insert you must pass a parameter array to INSERT .

    If your application is winforms it's very simple just use the File.ReadAllBytes ()

    In the link example, the useful code is in the persistence layer

        public static void Guardar(string nombrearchivo, int length, byte[] archivo)
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
                string query = @"INSERT INTO Archivos (nombre, length, archivo)
                                 VALUES (@name, @length, @archivo)";
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@name", nombrearchivo);
                cmd.Parameters.AddWithValue("@length", length);
                SqlParameter archParam = cmd.Parameters.Add("@archivo", SqlDbType.VarBinary);
                archParam.Value = archivo;

    generating a parameter in the INSERT that allows assigning the byte [] of the file

    I would also recommend values if you find it useful to define the field as FileStream , for the version of Sql Server that you mention you can use it and it is more optimal when registering files in the db.

    An Introduction to SQL Server FileStream

    How Do I: Use SQL File Stream

    answered by 09.02.2016 в 23:56