Store/retrieve files to/from SQL Server database

Question

I want to allow users to attach files to a project. Files can be images, documents, texts, zip files etc.

I have created a table in my database which will store file name, extension, size, comments about files and the file data. Column type for file data is VarBinary (blob).

Now, I have taken the input from user and stored that in my local listview (not bound to db). I want to iterate on the list view rows and store them to database. How should I store the file data to BLOB column?

Answer 1

Try this

// **** Read File/Image into Byte Array from Filesystem
        public static byte[] GetPhoto(string filePath)
        {
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            BinaryReader br = new BinaryReader(fs);

            byte[] photo = br.ReadBytes((int)fs.Length);

            br.Close();
            fs.Close();

            return photo;
        }

Then call above function to add into database

// **** Read Image from Filesystem and add it to the Database.
        public void AddFileDataIntoDatabase(
            string a,string b,string c, string photoFilePath)
        {

            // Read Image into Byte Array from Filesystem
            byte[] photo = GetPhoto(photoFilePath);

            // Construct INSERT Command
            SqlCommand addEmp = new SqlCommand(
                "INSERT INTO tablename ("+
                "col1,col2,Col3,Photo) "+
                "VALUES(@col1,@col2,@col3,@Photo)",_conn);

            addEmp.Parameters.Add("@col1",  SqlDbType.NVarChar, 20).Value = plastName;
            addEmp.Parameters.Add("@col2", SqlDbType.NVarChar, 10).Value = pfirstName;
            addEmp.Parameters.Add("@col3",     SqlDbType.NVarChar, 30).Value = ptitle;                
            addEmp.Parameters.Add("@Photo",     SqlDbType.Image, photo.Length).Value = photo;

            // Open the Connection and INSERT the BLOB into the Database
            _conn.Open();
            addEmp.ExecuteNonQuery();
            _conn.Close();
        }

Refer here