Retrieving images using a file path stored in a sql database

Question

I have a table in my SQL database that stores an AccountID and an ImageID - the ImageID being a file path to an actual account image in .gif format.

What would I need to do is, to retrieve the image from the file path saved on my SQL database onto an image control? I am using asp.net in c#.

Potentially, when the page is accessed, depending on which account has logged on, the appropriate image specific for the account is retrieve (I would imagine I can set up an @AccountID parameter at a later date)

If anyone has example code for something they have done similarly, I would be most grateful for any pointers you may have.

I don't have the necessary expertise to propose a full solution to your problem but I would just suggest also storing the images in the database, in order to avoid access rights issues. — Sorin Comanescu
– Sorin Comanescu, Commented Dec 2, 2009 at 9:53

Darin Dimitrov · Accepted Answer · 2009-12-02 09:58:26Z

1

Here's a sample:

var connectionString = ConfigurationManager.ConnectionStrings["SomeCN"].ConnectionString;
using (var cn = new SqlConnection(connectionString))
using (var cmd = cn.CreateCommand())
{
    cn.Open();
    cmd.CommandText = "select imageid from accounts where accountid = @accountid";
    cmd.Parameters.AddWithValue("@accountid", 5);
    using (var reader = cmd.ExecuteReader())
    {
        if (reader.Read())
        {
            var filePath = reader.GetString(0);
            // For this to work images must be stored inside the web application.
            // filePath must be a relative location inside the virtual directory
            // hosting the application. Depending on your environment some
            // transformations might be necessary on filePath before assigning it
            // to the image url.
            imageControl.ImageUrl = filePath;
        }
    }
}

edited Dec 2, 2009 at 9:58

answered Dec 2, 2009 at 9:52

Darin Dimitrov

1.0m276 gold badges3.3k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MrDean Over a year ago

Hello Darin. Sorry for the simple question but should I just include this in 'protected void Page_load' or should I stick it under 'protected void Image1_Load'?

MrDean Over a year ago

Me again! - I am receiving "The type or namespace name 'SqlConnection' could not be found. I'm using system.data.sql. Any ideas where I am going wrong? Also, in your example What should be placed in ["SomeCN"]? Apologies for the additional questions

Darin Dimitrov Over a year ago

You need to reference the System.Data assembly in your project.

MrDean Over a year ago

thanks Darin - I managed to get that to work..ish...well it is firing but unfortunaterly the images do not pull through. Do I need to amend the asp.image control to pick up the filepath or should this just display the image depending upon the account ID? How would I amend the code above so that I could test this i.e. using Account 12345 ? Thank you for your assistance, as you can tell, I am out of my depth here.

ACP · Accepted Answer · 2009-12-02 09:59:09Z

Hai , Check this out... In your aspx:

<div>
    <asp:FileUpload ID="ImgUpload" runat="server" />
    <asp:Button ID="Upload" runat="server" Text="Upload" onclick="Upload_Click" />
    <asp:Image ID="samp" runat="server" ImageUrl="~/images/new//Testingimages1253899911515.gif " />
</div>

In your aspx.cs:

protected void Page_Load(object sender, EventArgs e)
{

}
private bool IsImage(HttpPostedFile file)
{
    if (file != null && Regex.IsMatch(file.ContentType, "image/\\S+") &&
      file.ContentLength > 0)
    {
        return true;
    }
    return false;
}
public string SaveImageFile(FileUpload fu, string directoryPath, int MaxWidth, int MaxHeight, string prefixName)
{
    string serverPath = "", returnString = "";
    if (fu.HasFile)
    {
        Byte[] bytes = fu.FileBytes;
        //Int64 len = 0;
        prefixName = "Testing" + prefixName;
        //directoryPath = "Testing/";
        System.IO.MemoryStream ms = new System.IO.MemoryStream(bytes);
        System.Drawing.Image img = System.Drawing.Image.FromStream(ms);
        string dipath = System.Web.HttpContext.Current.Server.MapPath("~/") + directoryPath;
        DirectoryInfo di = new DirectoryInfo(dipath);
        if (!(di.Exists))
        {
            di.Create();
        }
        HttpPostedFile file = fu.PostedFile;
        DateTime oldTime = new DateTime(1970, 01, 01, 00, 00, 00);
        DateTime currentTime = DateTime.Now;
        TimeSpan structTimespan = currentTime - oldTime;
        prefixName += ((long)structTimespan.TotalMilliseconds).ToString();
        if (IsImage(file))
        {
            using (Bitmap bitmap = new Bitmap(file.InputStream, false))
            {
                serverPath = dipath + "//" + prefixName + fu.FileName.Substring(fu.FileName.IndexOf("."));
                img.Save(serverPath);
                returnString = "~/" + directoryPath + "//" + prefixName + fu.FileName.Substring(fu.FileName.IndexOf("."));
            }
        }
    }
    return returnString;
}

protected void Upload_Click(object sender, EventArgs e)
{
    string imageUrl;
    imageUrl = SaveImageFile(ImgUpload, "images/new", 600, 600, "images");
    Response.Write(imageUrl);
}

add namespaces: using System.IO; using System.Drawing; using System.Drawing.Imaging; using System.Text; using System.Text.RegularExpressions;

pratik patel · Accepted Answer · 2014-11-29 05:39:40Z

0

Try these links it might help you..

you can also try by storing the image files on the server and store the paths on the Sql table.. by these links

http://pratikataspdotnet.blogspot.in/2014/11/retrieve-images-from-path-stored-in.html

answered Nov 29, 2014 at 5:39

pratik patel

641 gold badge2 silver badges18 bronze badges

Collectives™ on Stack Overflow

Retrieving images using a file path stored in a sql database

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related