|
Inserting Image into Database
To start with, let me explain the SQL Server database table structure we are going to use to insert the image. The table you are going to create to store the image must contain a column of data type IMAGE. This image data type is a Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes. To store the image into this column we are going to convert it into binary string with the help of some IO classes and then insert into the table. For demonstration, we are going to create a table named ImageGallery with four columns in the following structure
After we create table in the database, we can start the coding part. 1. Open your web application in Visual Studio 2005, drag and drop File Upload control and a Button control into the web page. 2. In the code-behind, add the namespace System.IO.
3. In the Button’s Button1_Click event, write the following code |
|||||||||||||||||||||||||||||||||||||
To upload the image from any location (your local drive) to the server, we have to use HttpPostedFile object. Point the uploaded file to HttpPostedFile object. Then the InputStream.Read method will read the content of the image by a sequence of bytes from the current stream and advances the position within the stream by the number of bytes it read. So myimage contains the image as binary data. Now we have to pass this data into the SqlCommand object, which will insert it into the database table. |
|||||||||||||||||||||||||||||||||||||
|
Display the Image in a GridView with Handler.ashx So far, the article explains the way to insert images into the database. The Image is in the database in binary data format. Retrieving this data in an ASP.NET web page is fairly easy, but displaying it is not as simple. The basic problem is that in order to show an image in an apsx page, you need to add an image tag that links to a separate image file through the src attribute or you need to put an Image control in your page and specify the ImageUrl. For example: |
|||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||
|
What is Handler?
An ASP.NET HTTP Handler is a simple class that allows you to process a request and return a response to the browser. Simply we can say that a Handler is responsible for fulfilling requests from the browser. It can handle only one request at a time, which in turn gives high performance. A handler class implements the IHttpHandler interface. For this article demonstration, we are going to display the image in the GridView control along with the data we stored in the table. Here are the steps required to accomplish this: 1. Create a Handler.ashx file to perform image retrieval. This Handler.ashx page will contain only one method called ProcessRequest. This method will return binary data to the incoming request. In this method, we do normal data retrieval process and return only the Image_Content field as bytes of array. The sample code follows |
|||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||
|
Before you bind the GridView, you should write the FetchAllImagesInfo method to return all the records with their image data from the table and then you have to load the images into the GridView control. The code for FetchAllImagesInfo is
That's it. When you run your project, you can see the images got loaded into the GridView control. This is a very simple explanation to store images into the data source and to retrieve it back to display in the webpage. You can make the logic even simpler and even elaborate it upto your requirements. |
Asp.Net
GridView Control Custom Sorting and Paging with Image Indicator
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Introduction
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
For demonstration purpose, we are going to display
Customer Information in a GridView control and we are going to perform
sorting in the GridView control, which also got custom paging in it. So place
a GridView control in the Aspx page, specify its
AutoGenerateColumns="False". Then add TemplateFields columns to the
GridView as many as columns you want to display. In our case, we are going to
display Customer Name [Cus_Name], Gender [Cus_Gender], Age [Cus_Age], EmailId
[Cus_Email] and State [Cus_State]. Every TemplateField column will contains a
ItemTemplate section, AlternatingItemTemplate section, EditItemTemplate
section, HeaderTemplate section and FooterTemplate section. For the purpose
of this article, we are going to work on Only ItemTemplate and
HeaderTemplate sections. And we will explain you, simply for only one
TemplateField column, so that you can do the same for all columns in the
GridView.
Point you MUST NOT DO
Concentrate in First TemplateField Column : Customer Name
[Cus_Name]
In HeaderTemplate section, add a LinkButton, and change some of its properties as stated in the table below. The below table shows the property of the LinkButton, the value of the property you have to change and a brief note to explain the field's requirement. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Next you have add a PlaceHolder control near the LinkButton control and specify its ID as ‘placeholderName’. You must assign unique ID for every PlaceHolders on each and every TemplateField columns. Abstract Reminder: You have to add a LinkButton and a PlaceHolder in each and every column of the TemplateField’s Header section that you want to do custom sorting and do the above-specified settings for every LinkButton and the PlaceHolder controls. In the ItemTemplate section, add a Label control and bind its Text property to Customer Name [Cus_Name]. So the Label control in the ItemTemplate section of the GridView will look like,
Just repeat the above steps for all the columns you want to display in the GridView control as custom sorting column. As per our requirement, the GridView’s Html code will be as follows |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Last, we bind the GridView control with PagedDataSource
object to display the records and call another private method ‘doPaging’ for
building custom Paging page numbers. The code for doPaging method is as
follows
So the doPaing method has a DataTable with two columns such as PageIndex and PageText. The PageIndex column is the selected index value and PageText column is the display value in the dlPaging. So this DataTable has to be binded with dlPaging DataList control. Source Code for Custom Sorting In the GridView’s RowCommand event, we are going to do sort operation for the GridView control as follows |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
First we have to check the CommandName is equals “Sort”
command, if so, we can initialize the ViewState variable “SortExp” with the
CommandArgument (which contains the field name to sort) and “SortOrder” with
“ASC” for ascending or “DESC” for descending order sorting. The above code
got two conditions of assignment.
1. First time, the ViewState of SortExp will be nothing, so initialize it with the CommandArgument and set its SortOrder as “ASC”. 2. Next time, the ViewState of SortExp will have any field name, so we have to compare both the CommandArgument and the ViewState SortExp value.
After all the checking and assignment, call the BindGrid
event to re-arrange the GridView rows according to the above specified Sort
Expression and Sort Order.
Source Code for Image Indicator in Header Row The specialty of this article is an Image Indicator in the header row, indicates which column has sorted and in which order it has been sorted. We are going to do the coding in the GridView control RowDataBound event as follows. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Source Code for Custom Paging
We have already completed the designing part of the Custom Paging for the GridView control. The DataList dlPaging will display page numbers. When it is clicked, the corresponding page will be appeared in the GridView control without disturbing the Sorting. For this we have to write code at the dlPaging ItemCommand event as follows
Here we just set the CurrentPage value from the CommandArgument, which is actually the PageIndex, then call the BindGrid event. Thus we have integrated the DataList and the GridView control to perform Custom Paging. To highlight the selected Page Number (PageIndex) in different style, do the following code in ItemDataBound event of the dlPaging datalist. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Source Code for Next and Previous LinkButton controls When Next LinkButton is clicked, we have to increment the CurrentPage value and when Previous LinkButton is clicked, we have to decrease the value of the CurrentPage variable, then call the BindGrid method to display the records in the corresponding page.
Since we are maintaining the Sort Expression, Sort Order and Page Index in the ViewState of the Page, Custom Sorting and Custom Paging is easily achievable at the same time, without one disturbing the other. Now, save the changes you have done and run this page in the browser. For sure, the GridView control will be populated with data, if the records are more than 10, paging numbers will appear. When you click the any LinkButton in the GridView Header Row, GridView will re-arrange the records in Ascending Order, and an image with down arrow will appear in the header row of that column. Click the same LinkButton again, now the GridView will re-arrange the records Descending Order and the image will show an up arrow denotes it is descending order. Click on the page number, you can see the GridView navigates to the page number you clicked without the Sorting being disturbed. This article is to give you an abstract idea of doing custom paging and sorting in GridView control. There will be many ways to do this. It is up to you, to choose the right one, that suite your requirement.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Asp.Net AdRotator control
with Database Integration
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlDataAdapter da;
DataSet ds = new DataSet();
SqlConnection con;
SqlCommand cmd = new SqlCommand();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
Binddata();
}
}
// Define the Edit Command
public void editgrid_click(object sender, DataGridCommandEventArgs e)
{
gridedit.EditItemIndex = e.Item.ItemIndex;
Binddata();
}
// Define the Cancel Command
public void gridcancel_click(object sender, DataGridCommandEventArgs e)
{
gridedit.EditItemIndex = -1;
Binddata();
}
//Here we Bind the data
public void Binddata()
{
con = new SqlConnection(ConfigurationSettings.AppSettings["connect"]);
cmd.CommandText="select * from record order by id Asc";
cmd.Connection = con;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Open();
cmd.ExecuteNonQuery();
gridedit.DataSource = ds;
gridedit.DataBind();
con.Close();
}
//Update Command Defination
protected void updategrid_UpdateCommand(object source, DataGridCommandEventArgs e)
{
try
{
con = new SqlConnection(ConfigurationSettings.AppSettings["connect"]);
cmd.CommandText = "Update record set name=@name ,F_name=@F_Name, l_name=@l_name,City=@City,State=@State where id=@id";
cmd.Parameters.Add("@name", SqlDbType.Char).Value = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
cmd.Parameters.Add("@F_name", SqlDbType.Char).Value = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
cmd.Parameters.Add("@l_name", SqlDbType.Char).Value = ((TextBox)e.Item.Cells[3].Controls[0]).Text;
cmd.Parameters.Add("@City", SqlDbType.Char).Value = ((TextBox)e.Item.Cells[4].Controls[0]).Text;
cmd.Parameters.Add("@State", SqlDbType.Char).Value = ((TextBox)e.Item.Cells[5].Controls[0]).Text;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = gridedit.DataKeys[e.Item.ItemIndex];
cmd.Connection = con;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
gridedit.EditItemIndex = -1;
Binddata();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
// Delete Command Defination
public void gridedit_DeleteCommand(object sender, DataGridCommandEventArgs e)
{
con = new SqlConnection(ConfigurationSettings.AppSettings["connect"]);
int U_ID = (int)gridedit.DataKeys[(int)e.Item.ItemIndex];
cmd.CommandText = " Delete from record where id=" + U_ID;
cmd.Connection = con;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
gridedit.EditItemIndex = -1;
Binddata();
}
// For Paging
public void gridedit_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
gridedit.CurrentPageIndex = e.NewPageIndex;
Binddata();
}
//Link for Insert a new Record in a table
public void InsertNewRecord_click(object source, System.EventArgs e)
{
lblnewname.Visible = true;
txtnewname.Visible = true;
lblF_name.Visible = true;
txtF_name.Visible = true;
lblLast_name.Visible = true;
txtLast_Nmae.Visible = true;
lblcity.Visible = true;
txtcity.Visible = true;
lblState.Visible = true;
txtState.Visible = true;
btnnewRecordSubmit.Visible = true;
}
// Command for insert a new Record
public void Submitnew(object source, System.EventArgs e)
{
con = new SqlConnection(ConfigurationSettings.AppSettings["connect"]);
cmd.CommandText = "insert into record(name,F_name,l_name,City,State) values(@name,@F_Name,@l_name,@City,@State)";
cmd.Parameters.Add("@name", SqlDbType.Char).Value = txtnewname.Text;
cmd.Parameters.Add("@F_Name", SqlDbType.Char).Value = txtF_name.Text;
cmd.Parameters.Add("@l_name", SqlDbType.Char).Value = txtLast_Nmae.Text;
cmd.Parameters.Add("@City", SqlDbType.Char).Value = txtcity.Text;
cmd.Parameters.Add("@State", SqlDbType.Char).Value = txtState.Text;
cmd.Connection = con;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
gridedit.EditItemIndex = -1;
Binddata();
txtnewname.Text = "";
txtF_name.Text = "";
txtLast_Nmae.Text = "";
txtcity.Text = "";
txtState.Text = "";
lblnewname.Visible = false;
txtnewname.Visible = false;
lblF_name.Visible = false;
txtF_name.Visible = false;
lblLast_name.Visible = false;
txtLast_Nmae.Visible = false;
lblcity.Visible = false;
txtcity.Visible = false;
lblState.Visible = false;
txtState.Visible = false;
btnnewRecordSubmit.Visible = false;
}
}
to display images in gridview.you
have to do the following
in button click just paste the following code string strSql = "Select ImageId, ImageName, Images, ImageType, ImageSize from Pictures"; SqlDataAdapter daPics = new SqlDataAdapter(strSql, con); daPics.Fill(dsPics); dsPics.Tables[0].Columns.Add("imgFile"); foreach (DataRow drPics in dsPics.Tables[0].Rows) { drPics["imgFile"] = ("grabpicture.aspx?ImageId=" + drPics[0]); } gvWithImages.DataSource = dsPics; gvWithImages.DataBind(); in the above code pictures table is used and its design script is attached.you can find it in the attachment. and i have used one more aspx page to load the image that is "grabpicture.aspx" in grabpicture.aspx page global declaration area declare these variables DataSet dsPics; SqlDataAdapter daPics; byte[] ImgContent; DataRow drPics; string strSql, strImgContentType; SqlConnection con = new SqlConnection("user id=<user>;pwd=<password>;data source=<sysname/ipaddress>;database=<dbname>"); in grabpicture.aspx page load event write the following code strSql = "Select * from Pictures where ImageId = " + Request.QueryString["ImageId"]; daPics = new SqlDataAdapter(strSql, con); dsPics = new DataSet(); daPics.Fill(dsPics); drPics = dsPics.Tables[0].Rows[0]; ImgContent = (byte[])drPics["Images"]; strImgContentType = drPics[3].ToString(); Response.ContentType = strImgContentType; Response.OutputStream.Write(ImgContent, 0, (int)drPics[4]); Response.End();
private void grid_view()
{ string s=ConfigurationManager.ConnectionStrings["constr"].ConnectionString; string sel = "select * from TableName"; SqlConnection con = new SqlConnection(s); SqlCommand cmd = new SqlCommand(sel, con); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); sda.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); } protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; grid_view(); } Saving the image in SQL Server SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]); con.Open(); byte[] ImgBytes = new byte[flurl.PostedFile.InputStream.Length]; flurl.PostedFile.InputStream.Read(ImgBytes, 0, ImgBytes.Length); string qry = "Insert into Table1(ImageData) values(@ImageData)"; SqlCommand cmd = new SqlCommand(qry, con); cmd.Parameters.AddWithValue("@ImageData", ImgBytes); cmd.ExecuteNonQuery(); con.Close(); page_load() { // give sql connections(connection string) } protected void Button1_Click(object sender, EventArgs e) { int len = FileUpload1.PostedFile.ContentLength; byte[] pic = new byte[len]; FileUpload1.PostedFile.InputStream.Read(pic, 0, len); string fup = FileUpload1.PostedFile.FileName; if (k1 == 0) { cmd = new SqlCommand("insert into login_inf(first_name,logo) values (@name,@logo)", conn); cmd.Parameters.Add("@name", TextBox1.Text.Replace("'", "''")); cmd.Parameters.Add("@logo", pic); cmd.ExecuteNonQuery(); } }
byte[] file =
SqlConvert.ToByteArray(dr["FileImage"]);
string fileName = SetupFileName(SqlConvert.ToString(dr["FileName"])); string filePath = System.Environment.CurrentDirectory + “\\myFiles\\” + fileName; FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write); BinaryWriter bw = new BinaryWriter(fs); bw.Write(file); bw.Flush(); bw.Close(); fs.Close(); |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
No comments:
Post a Comment