Unable to retrieve or store image in database SQL Server 2005

I am trying to retrieve image from back end in ASP.Net. Using SQL SERVER 2005 as back end. I have tried n number of codes including the one available online. Can any one guide me solve this issue.

My code is below

Table Design:-

create table Image
 (
ImageId Int identity (1,1),ImageName Varchar(50), Image image   
)

Code:-

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridData();
        }
    }
    string strcon = "Data Source=SUJITHA\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            //getting length of uploaded file
            int length = FileUpload1.PostedFile.ContentLength;
            //create a byte array to store the binary image data
            byte[] imgbyte = new byte[length];
            //store the currently selected file in memeory
            HttpPostedFile img = FileUpload1.PostedFile;
            //set the binary data
            img.InputStream.Read(imgbyte, 0, length);
            string imagename =TextBox1.Text;
            //use the web.config to store the connection string
            SqlConnection connection = new SqlConnection(strcon);
            connection.Open();
            SqlCommand cmd = new SqlCommand("INSERT INTO Image (ImageName,Image) VALUES (@imagename,@imagedata)", connection);
            cmd.Parameters.Add("@imagename", SqlDbType.VarChar, 50).Value = imagename;
            cmd.Parameters.Add("@imagedata", SqlDbType.Image).Value = imgbyte;
            int count = cmd.ExecuteNonQuery();
            connection.Close();
            if (count == 1)
            {
                BindGridData();
                TextBox1.Text = string.Empty;
                ScriptManager.RegisterStartupScript(this, this.GetType(), "alertmessage", "javascript:alert('" + imagename + " image inserted successfully')", true);
            }
        }
    }

    private void BindGridData()
    {
        SqlConnection connection = new SqlConnection(strcon);
        SqlCommand command = new SqlCommand("SELECT ImageName,Image  from [Image]", connection);
        SqlDataAdapter daimages = new SqlDataAdapter(command);
        DataTable dt = new DataTable();
        daimages.Fill(dt);
       GridView1.DataSource = dt;
       GridView1.DataBind();
       GridView1.Attributes.Add("bordercolor", "black");
    }

Answers


SqlConnection con = new SqlConnection(@"Data Source=AMAR-PC\SQLEXPRESS;Initial Catalog=a;User ID=sa;Password=amar");
string path = Server.MapPath("Images/");

if (FileUpload1.HasFile)
{
    byte[] img = new byte[FileUpload1.PostedFile.ContentLength];
    HttpPostedFile myimage = FileUpload1.PostedFile;
    myimage.InputStream.Read(img, 0, FileUpload1.PostedFile.ContentLength);
    SqlCommand cmd = new SqlCommand("insert into images values ('" + TextBox1.Text + "','" + FileUpload1.PostedFile + "')", con);

    con.Open();

This is what i did...and I also know that there are many other ways to upload file.... Now I want that a the uploaded pic should be dispayed in Image Control....could you help me now


    */Your code Like this*         

    **//Insert the file into database**

            string strQuery = "insert into tblFiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)";

            SqlCommand cmd = new SqlCommand(strQuery);

            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;

            cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = "application/vnd.ms-word";

            cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;

            InsertUpdateData(cmd);

    **//Select the file from database**
        string strQuery = "select Name, ContentType, Data from tblFiles where id=@id";

        SqlCommand cmd = new SqlCommand(strQuery);

        cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;

        DataTable dt = GetData(cmd);

        if (dt != null)

        {

            download(dt);

        }

        private void download (DataTable dt)

        {

            Byte[] bytes = (Byte[])dt.Rows[0]["Data"];

            Response.Buffer = true;

            Response.Charset = "";

            Response.Cache.SetCacheability(HttpCacheability.NoCache);

            Response.ContentType = dt.Rows[0]["ContentType"].ToString();

            Response.AddHeader("content-disposition", "attachment;filename="

            + dt.Rows[0]["Name"].ToString());

            Response.BinaryWrite(bytes);

            Response.Flush();

            Response.End();

        }
private DataTable GetData(SqlCommand cmd)
    {
        DataTable dt = new DataTable();
        String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        SqlConnection con = new SqlConnection(strConnString);
        SqlDataAdapter sda = new SqlDataAdapter();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        try
        {
            con.Open();
            sda.SelectCommand = cmd;
            sda.Fill(dt);
            return dt;
        }
        catch 
        {
            return null;
        }
        finally
        {
            con.Close();
            sda.Dispose();
            con.Dispose();
        }
    }

Couple of things worry me

  • you use the master database

  • your table name is image which is a reserved word in SQL Server

If you need to name tables with reserved names, you need to quote them with square brackets: INSERT INTO [Image] ...


Need Your Help

OS X shell scripting: How to pause execution till after Finder restarts?

macos bash shell finder

This is about shell scripting on Mac OS X. I want to write a bash script that relaunches the Finder and does something afterwards. How can I add a delay after the killall command so that the next c...

Passing selected option in URl to Jquery UI tabs

jquery user-interface jquery-ui-tabs

Is there a way that i can link to a page containing a jquery ui tab set and pass the selected variable in the url link to automatically show a particular tab depending in which link was clicked to ...