How to implement image access using varbinary (MAX) in SQL SERVER 2008

In the development of database application projects, some binary image data is often used. The path link method and memory streaming method are mainly used to store and read the display image data. The path link method is to store the image file in a fixed path, and only the path and name of the image file are stored in the database. This method has a small database capacity, fast access, but poor security; the memory flow method is to directly binary data Stored in the database, this method is very convenient for data sharing, and the security is relatively high. It is often used when the image capacity is not very large. This article mainly discusses how to realize the storage of binary image data by using the memory streaming method through SQL Server 2008. 1 VARBINARY (MAX) data type introduction In SQL Server 2000 and earlier versions, if the data volume of each record far exceeds a single record of 8K, we commonly use the IMAGE data type to store the data in binary, using IMAGE For data types, data is not stored with ordinary data. A small binary value called a pointer is stored together with ordinary data. This binary value points to the actual storage location of the data in the data file. READTEXT [1] and WRITETEXT commands are needed to read and insert data from IMAGE data. These two commands need to use the TEXTPTR function to obtain the correct binary pointer. This binary pointer is used to locate the data in the physical file. trouble. Although the IMAGE data type is still provided in SQL Server 2008, Microsoft plans to delete the IMAGE data type in a future version of SQL Server and use the VARBINARY (MAX) data type instead. The IMAGE type should not be used in this version. VARBINARY (MAX) is variable-length binary data, not limited to the maximum length. It is commonly used when the data exceeds 8,000 bytes. You can directly use the insert command to add data, which is relatively simple to use. 2 Using T-SQL statement to read the image file directly into the VARBINARY (MAX) field First create the test database ImageDB in the query window of SQL Server 2008, and establish the ImageTable table to store the image data:
   CREATE DATABASE ImageTest
GO
USE ImageTest;
GO
CREATE TABLE ImageTable
(
ImageData varBinary (MAX)
);
Then use the Insert command to insert it into the ImageTest table (here the image file is C: \ aa.jpg), the OPENROWSET function contains all the connection information required to access the remote data in the OLE DB data source, and the built-in BULK access interface supports For large-capacity operations, the codes implemented are:
INSERT INTO ImageTable (ImageData)
   SELECT *
   FROM OPENROWSET (BULK N‘C: \ aa.jpg ’, SINGLE_BLOB) AS Photo
GO
Run the Select * from ImageTable statement to see the binary encoding of the image, but SQL Server 2008 does not provide a way to view the picture directly. In order to view the picture, we need to create an application. Part 4 of this article introduces its implementation method. 3 Copy binary data to image file in SQL Server. Copy binary data to image file in SQL Server 2008. To operate on Windows files, we need to use xp_cmdshell extended stored procedure. Before using it, we need to obtain and execute the Command authority:
     EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
EXEC sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE
GO
Copy the binary data to the image file by executing xp_cmdshell:
EXEC master..xp_cmdshell ‘bcp" SELECT ImageData FROM ImageTest.dbo.ImageTable "queryout" c: \ bb.jpg "-T -c’
Go
You can see an image file bb.jpg is added under the C drive. 4 Use C # .NET to access the image data in SQL Server 2008 Add an ImagePath field to the ImageTable table created in 2 to record the path and file name of the image. Add a button to the form to read the image file into SQL Server. The basic process is to select the image file to be read through the openFileDialog control, convert the image file into a binary stream, and use the insert command after connecting to the database. Store the binary stream data to SQL Server, the implementation code is as follows:
public static bool StoreImages (string [] fileNames, string [] filePaths)
        {
            try
            {
                for (int i = 0; i <fileNames.Length; i ++)
                {
                    string fileName = fileNames [i];
                    string filePath = filePaths [i];

                    using (SqlConnection connection = new SqlConnection (PubVariant.ConnectionString))
                    {
                        connection.Open ();
                        FileStream byteStream = new FileStream (fileName, FileMode.Open, FileAccess.Read);
                        byte [] byteImage = new byte [byteStream.Length];
                        byteStream.Read (byteImage, 0, (int) byteStream.Length);
                        string strSql = "insert into Data (Name, Data, DataPath) values (@ Name, @ Data, @ DataPath)";
                        using (SqlCommand cmd = new SqlCommand (strSql, connection))
                        {
                            cmd.Parameters.Add ("@ Name", SqlDbType.Text);
                            cmd.Parameters.Add ("@ Data", SqlDbType.Binary);
                            cmd.Parameters.Add ("@ DataPath", SqlDbType.Text);
                            cmd.Parameters ["@ Name"]. Value = fileName;
                            cmd.Parameters ["@ Data"]. Value = byteImage;
                            cmd.Parameters ["@ DataPath"]. Value = filePath;
                            cmd.ExecuteNonQuery ();
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.Message);
                return false;
            }
        }
Add another button to display the binary data in SQL Server as an image, and bind the records of the ImageTable table to the dataGridView control after connecting to the database. Although the dataGridView control can display the binary data as a picture, the row and column spacing is too small, not Good looking, so at the same time also display the image in the pictureBox control, the implementation code is as follows:
public static Image GetImageFromName (string fileName)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection (PubVariant.ConnectionString))
                {
                    connection.Open ();
                    string strSql = "select Data from Data where Name =‘ "+ fileName +" ‘";
                    SqlCommand cmd = new SqlCommand (strSql, connection);
                    SqlDataReader dr = cmd.ExecuteReader ();
                    dr.Read ();
                    MemoryStream ms = new MemoryStream ((byte []) dr [0]);
                    Image img = Image.FromStream (ms);
                    return img;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.Message);
                return null;
            }
        }