• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2820
  • Last Modified:

ASP.net 2.0 and SQL server - store and retrive image

Hi

I've to insert and retrieve a graphic to/from SQL Server 2005 dbase for use on ASP.net 2.0 page.  
I've tried some links but never quite got there.....so code would be better this time
Here's a simplified table, not actual table or context, that could help with demo
tblCar
CarID - integer, autonumber
CarName - string
CarGraphic - type Image

So I need:
1) page to insert new car with a graphic,  
2) page to retrieve all the cars and display with their graphics on GridView

Note I don't want to store graphic files, rather I need to put them in database.

Thanks in advance for help
0
rwallacej
Asked:
rwallacej
  • 4
  • 2
1 Solution
 
strickddCommented:
create the page with the textbox for the car name and an <asp:FileUpload> constrol. The SQL will look like this:

CREATE PROCEDURE [dbo].[InsertNewCar]
      (@CarName varchar(50)  --Chagne 50 to whatever size it is in your DB
        ,@CarGraphic image)

AS

INSERT INTO [UCIT].[dbo].[Serv_Req]
           (CarName
           ,CarGraphic)
     VALUES
                  (@CarName
                  ,@CarGraphic)


The code will be something like this:

SqlConnection Connection = new SqlConnection(Utilities.CommonFunctions.ConvertToString(ConfigurationManager.ConnectionStrings["connection"]));
      SqlCommand Command = new SqlCommand("InsertNewCar", Connection);
            Command.CommandType = CommandType.StoredProcedure;

      Command.Parameters.AddWithValue("@CarName", CarNameTextBox.Text);

            if (CarGraphicFileUpload.HasFile)
            {
                  Command.Parameters.AddWithValue("@CarGraphic", CarGraphicFileUpload.FileBytes);
                  Command.Parameters.AddWithValue("@File1Name", File1.FileName);
            }
            else
            {
                  Command.Parameters.AddWithValue("@CarGraphic", empty);
            }

            Connection.Open();
                Command.ExecuteNonQuery();
                Connection.Close();
0
 
rwallacejAuthor Commented:
thank-you, this seems to be much progress on where I was at!.
when I click "Add image" with above code,  entries are added to database with the
CarGraphic field containing data <Binary data>

now how do I display the graphic or say a GridView or FormView ?  I tried the following but it doesn't display images (I didn't think this would be work)

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
                <asp:BoundField DataField="CarName" HeaderText="CarName" SortExpression="CarName" />
                <asp:ImageField DataImageUrlField="CarGraphic">
                </asp:ImageField>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:Image ID="Image1" runat="server" Height="192px" ImageUrl='<%# Eval("CarGraphic") %>'
                            Width="240px" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>

Thanks!
0
 
strickddCommented:
You will need to create and intermediary page that will do a Response.BinaryWrite() of the image data.  This page explains the concept very well. http://authors.aspalliance.com/stevesmith/articles/imagequery.asp

basically you need to set the URL of the image to be a page.aspx?ID=DBIDofImage. Then that page will make a call to the DB using that ID as a reference and do the following:

Byte[] myFile = (Byte[])reader["CarGraphic"];

Response.ContentType = "image/gif"; //or image/jpeg
Response.BinaryWrite(myFile);
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rwallacejAuthor Commented:
thanks.......almost there

one thing left - how do I set the ID for Image URL here?  Its currently hard coded ID=4 and graphics display fine, but need to put in right ID for each row on GridView

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
                    <asp:BoundField DataField="CarName" HeaderText="CarName" SortExpression="CarName" />
                    <asp:HyperLinkField Text="Show Detail" DataNavigateUrlFormatString="~/cardetails.aspx?EmployeeID={0}"
                        DataNavigateUrlFields="ID" />
                    <asp:TemplateField>
                        <ItemTemplate>
                            Picture<br />
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("ID") %>' Width="152px"></asp:Label><br />
                            &nbsp;
                            <asp:Image ID="Image1" runat="server" Height="96px" ImageUrl="~/viewer2.aspx?ID=4"  Width="120px" />
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
0
 
rwallacejAuthor Commented:
its OK, I've got answer now

<img alt="Picture" src='<%# Eval("ID", "viewer2.aspx?ID={0}") %>' /><br />
0
 
rwallacejAuthor Commented:
thank-you  strickdd for your help
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now