Image from SQL Server Table to asp.net image contr

问题:

SqlCommand cmd = new SqlCommand("select top 1(CMTID),COMMENT,HEADING from FTAB ORDER BY CMTID DESC", conn);
conn.Open();

SqlDataReader sdr = cmd.ExecuteReader();

if (sdr.Read() == true)
{
    lblHead.Text = sdr["HEADING"].ToString();
    lblData.Text = sdr["COMMENT"].ToString();

}

conn.Close();

That is the code for normal data value retrieve from table now I want to get picture from SQL Server that is saved as Binary data that code mention in bottom. So I want to retrieve picture into an Image control of asp.net; please guide me.

if (FileUpload1.HasFile)
{
        conn.Close();

        String SqlQery;
        SqlQery = "select max(CMTID) from FTAB";

        SqlCommand cmdid = new SqlCommand(SqlQery, conn);
        conn.Open();

        MaxID = (int)(cmdid.ExecuteScalar()) + 1;

        conn.Close();

        byte[] img = new byte[FileUpload1.PostedFile.ContentLength];
        HttpPostedFile myimg = FileUpload1.PostedFile;
        myimg.InputStream.Read(img, 0, FileUpload1.PostedFile.ContentLength);
        SqlCommand cmd = new SqlCommand("insert into FTAB (CMTID, IMAGEDT, COMMENT, DATETM, HEADING) values (@imgid, @image, @comment, @datetm, @heading)", conn);

        SqlParameter imgid = new SqlParameter("@imgid",SqlDbType.Int);
        imgid.Value = MaxID;
        cmd.Parameters.Add(imgid);

        SqlParameter uploading = new SqlParameter("@image", SqlDbType.Image);
        uploading.Value = img;
        cmd.Parameters.Add(uploading);

        SqlParameter cmtt = new SqlParameter("@comment", SqlDbType.NVarChar);
        cmtt.Value = RadTextBox3.Text;
        cmd.Parameters.Add(cmtt);

        SqlParameter dttm = new SqlParameter("@datetm", SqlDbType.DateTime);
        dttm.Value = DateTime.Now;
        cmd.Parameters.Add(dttm);

        SqlParameter hhding = new SqlParameter("@heading", SqlDbType.NVarChar);
        hhding.Value = RadTextBox8.Text;
        cmd.Parameters.Add(hhding);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        lblHead.Text = "Image uploaded";
    }
    else
    {
        lblHead.Text = "No file selected";
    }

回答1:

Try this

SqlCommand cmd = new SqlCommand("select IMAGEDT from FTAB", new SqlConnection("your connection string"));
object data = cmd.ExecuteScalar();
byte[] imgBytes = (byte[])data;

System.Web.UI.WebControls.Image img = new System.Web.UI.WebControls.Image();

string filePath = Server.MapPath("temp") + "//" + "img"+DateTime.Now.Ticks.ToString()+".png";
FileStream fs = File.Create(filePath);
fs.Write(imgBytes, 0, imgBytes.Length);
fs.Flush();
fs.Close();

img.ImageUrl = filePath;

But I would say this isn't the best way to do it, you should save your uploaded images as files as in your website and save the path of that file in your database.

回答2:

yogi's answer is correct in principle, but requires a web server directory "temp" and write access on an IIS directory.

You can also solve the problem using an image handler as explained in another SO question. Database retrieval is done in the same way, but the rendering happens from a stream, rather than a temp file.