• How to Store Image in SQL Table Database Table Column

    by  • January 19, 2016 • Microsoft ASP.NET MVC, Microsoft SQL Server • 0 Comments

    We are discussing here how we can insert an image file to a SQL Database. To read a text file, we use Bulk insert. Bulk insert is an efficient way to load multiple rows of data into the database table. Bulk insert task can transfer data only from a text file and the destination must be an SQL Server table.

    T-SQL provide a very useful Insert statement with Bulkcolumn and Openrowset functions to read a file and insert into database.SQL inserts image to table using Blob datatype. Lets look at SQL statement below;

    Openrowset is T-SQL function for reading data using the bulk import capabilities. One of the useful feature of bulk is the ability to read files and insert the file data into SQL System such as loading a data from text file into SQL Server. Openrowset can be referenced as target table of Insert, Update or Delete statement.

    Now we have created a new Table in our SQL database named as Product as shown below;

    productTable

     

    Product table has five columns as shown above. Here we use Insert SQL statement with BulkColumn and Openrowset function. Let look at the following given T-SQL insert statement to insert the data into Product Table.

    In the above SQL Statement, we use BulkColumn and openrowset funtion as discuss above. Now We also used Single_Blob with bulk statement which reads a file as varBinary(Max) and then we convert this file as Image data type. By using the above SQL statement, we insert three more record in the product table as shown below;

    ResultantProductTable

     

    In the above table, four records have been added in the Product table. Now we can Display these records in MVC View as shown below

    Output




    Namecheap.com

    Leave a Reply