CODEWITHSUNDEEP
c#asp.netsql-serverblobvarbinary
WAQ
WAQ

Reputation: 2616

Store/retrieve files to/from SQL Server database

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?

Upvotes: 0

Views: 5377

Answers (2)

WAQ
WAQ

Reputation: 2616

SELECT * FROM OPENROWSET(BULK N'<FILEPATH>', SINGLE_BLOB) AS Contents
It'll pull in the contents of the file as varbinary.

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

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

Upvotes: 2

Related Questions