什么是sql的图像插入查询 [英] What is image insert query for sql

问题描述

将图像插入到Sql Server 2008的表中的查询是什么

What will be the Query to insert the image into the table for Sql Server 2008

推荐答案

检查此代码示例

Check this code sample

protected void Button1_Click(object sender, EventArgs e)
    {
        //  'using System.IO' 
      
        FileStream fs = new FileStream("C:\\Files\\draft.jpg",FileMode.Open,FileAccess.Read);
        BinaryReader br = new BinaryReader(fs);
        byte[] photo = br.ReadBytes((int)fs.Length);

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

        //  'using System.Data.SqlClient'
                     
        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ContestConnection"].ConnectionString);

        SqlCommand addpic = new SqlCommand(  "INSERT INTO userTable ("+" img_data) "+" VALUES(@img)",myConnection);
            
        addpic.Parameters.Add("@img",SqlDbType.Image, photo.Length).Value = photo;
        
        myConnection.Open();
        
        addpic.ExecuteNonQuery();
        myConnection.Close();
         
        }

如果您使用FileUploadControl，那么代码将是这样的

If you use FileUploadControl , then the code will be something like this

protected void btnUpload_Click(object sender, EventArgs e)

{

//Create a new filestream object based on the file chosen in the FileUpload control

FileStream fs = new FileStream(ImageUploadToSQL.PostedFile.FileName, FileMode.Open, FileAccess.Read);

//Create a binary reader object to read the binary contents of the file to upload

BinaryReader br = new BinaryReader(fs);

//dump the bytes read into a new byte variable named image

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

//close the binary reader

br.Close();

//close the filestream

fs.Close();

//Now that we've read the chosen file into a byte variable we can work with,

//stick the contents of that variable into the SQL Server database

//Create a SQL Server connection variable

SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["BlobsConnectionString"].ConnectionString);

//Create a SQL Server command variable

SqlCommand addimage = new SqlCommand("INSERT INTO Images (" + " Image, ImageName)" + " VALUES(@Image, @ImageName)", myConnection);

//Assign our image variable and filename variable to the parameters of the SQL Command

addimage.Parameters.Add("@Image", SqlDbType.Image, image.Length).Value = image;

addimage.Parameters.Add("@ImageName", SqlDbType.NVarChar).Value = txtFileName.Text;

//open the SQL connection and run the command to insert image and image name to the database

myConnection.Open();

addimage.ExecuteNonQuery();

myConnection.Close();

}

您好，



请参考以下链接，



http://social.msdn.microsoft.com/Forums/zh/sqlgetstarted/thread/aa25aa9b-17d2-4724-806f-17ec4b871119 [ ^ ]

在Sql表中取varbinary（max）类型的列：

然后在插入时，首先使用此代码将图像转换为字节数组

In Sql table take column of varbinary(max) type:
and after that while inserting, first convert the image to byte array using this code

public byte[] ConvertImageToByteArray(FileUploadField fuImage)
{
    byte[] ImageByteArray;
    try
    {
        MemoryStream ms = new MemoryStream(fuImage.FileBytes);
        ImageByteArray = ms.ToArray();
        return ImageByteArray;
    }
    catch (Exception ex)
    {
        return null;
    }
}

将此字节数组作为参数传递给插入函数，如下所示

Pass this byte array as parameter to your insert function as follow

public static long insertImage(string ID, byte[] Image)
     {

         long Id = 0;
         SqlConnection conn = null;
         try
         {
             string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
             conn = new SqlConnection(strConn);
             SqlCommand cmd = new SqlCommand();
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Connection = conn;
             cmd.CommandText = "Your_Sp_Name_Here";
             cmd.Parameters.Add("@ID", SqlDbType.VarChar, 30).Value = ID;
             cmd.Parameters.Add("@Image", SqlDbType.Image, 0).Value = Image;
             conn.Open();

             Id = Convert.ToInt16(cmd.ExecuteScalar());
             conn.Close();



         }
         catch (Exception ex)
         {

         }

         finally
         {
             if (conn != null && conn.State != ConnectionState.Closed)
                 conn.Close();
         }


         return Id;
     }

这篇关于什么是sql的图像插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！