GridView Customization in ASP.Net With OLEDB Database

In ASP.NET, GridView is one of the most useful tools for developers. Sometimes we need to customize a GridView for various purposes. It's not about visual customization. Assume you need to edit and delete a row from a GridView. What will you do then? Can you find these from the properties or can you get a built-in method from the Library? Or maybe you need to show some image in a column. How will you configure that? The fields generally support strings as far I know. So, you need to configure them to support image extensions so that an image can be shown.

So, here we go. Open Visual Studio and create a new empty web application project in ASP.Net. First of all, we need to make a class that can edit, delete and fetch data from a database. We will use Microsoft Access 2002 database here, so that we can overcome server problem issues. Before that, the database should be designed. If you don't have Microsoft Access 2002 then any version of Access beyond it wil work. Open Microsoft Access and create a blank database. Design it as in the following:



The database has been created. So now we need to create a webform to insert information into the table. Flip to Visual Studio and create a new empty webform. Make a GUI like this:


design


There are the four properties Category, Name, Image and Description. All are in string format, but what about images? Here is the trick. We will save the image in a folder within our project. Then we will send only the image location string to the database along with the other value. Then we will fetch the location from the database and insert them into the GridView. For that, make a folder in your project and name it "uploads". Also insert an App_Data folder into your project and copy the created dataset into the App_Data folder. Go to the saveButton code and start writing some C# code.
  1. protected void Button1_Click(object sender, EventArgs e)  
  2.   
  3. {  
  4.   
  5.     try  
  6.   
  7.     {  
  8.   
  9.         connection.Open();  
  10.   
  11.         if (FileUpload1.HasFile)  
  12.   
  13.         {  
  14.   
  15.             string str = FileUpload1.FileName;  
  16.   
  17.             FileUpload1.PostedFile.SaveAs(HttpContext.Current.Server.MapPath("~/uploads/") + str);  
  18.   
  19.             path = "~//uploads//" + str.ToString();  
  20.   
  21.         }  
  22.   
  23.         string qry = "insert into AddNotice values ('" + TextBox1.Text + "','" + TextBox2.Text + "','" + path.ToString() + "','" + TextBox3.Text + "')";  
  24.   
  25.         OleDbCommand cmd = new OleDbCommand(qry, connection);  
  26.   
  27.         cmd.ExecuteNonQuery();  
  28.   
  29.         Label1.Text = "Data has been stored";  
  30.   
  31.     } catch (Exception exp)  
  32.   
  33.     {  
  34.   
  35.         Response.Write(exp.ToString());  
  36.   
  37.     } finally  
  38.   
  39.     {  
  40.   
  41.         connection.Close();  
  42.   
  43.     }  
  44.   

We have inserted data into the database.

save detail

Now, make a class and name it "EmployeeDataAccessLayercs". In this class we will make three methods. One for edit, one for delete and another for showing all the information.

  1. public class Employee  
  2.   
  3. {  
  4.   
  5.     public string name  
  6.   
  7.     {  
  8.         get;  
  9.         set;  
  10.     }  
  11.   
  12.     public string image  
  13.   
  14.     {  
  15.         get;  
  16.         set;  
  17.     }  
  18.   
  19.     public string description  
  20.   
  21.     {  
  22.         get;  
  23.         set;  
  24.     }  
  25.   
  26. }  
  27.   
  28. public class EmployeeDataAccessLayercs  
  29.   
  30. {  
  31.   
  32.     public static void UpdateEmployee(string name, string image, string description)  
  33.   
  34.     {  
  35.   
  36.         OleDbConnection connection = new OleDbConnection();  
  37.   
  38.         connection.ConnectionString = @  
  39.         "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Category.mdb;";  
  40.   
  41.         connection.Open();  
  42.   
  43.         string qry = "update AddNotice SET sName = '" + name + "', Description = '" + description + "' where Image = '" + image + "'";  
  44.   
  45.         OleDbCommand cmd = new OleDbCommand(qry, connection);  
  46.   
  47.         cmd.ExecuteNonQuery();  
  48.   
  49.     }  
  50.   
  51.     public static void DeleteEmployee(string image)  
  52.   
  53.     {  
  54.   
  55.         OleDbConnection connection = new OleDbConnection();  
  56.   
  57.         connection.ConnectionString = @  
  58.         "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Category.mdb;";  
  59.   
  60.         connection.Open();  
  61.   
  62.         string qry = "delete from AddNotice where Image = '" + image + "'";  
  63.   
  64.         OleDbCommand cmd = new OleDbCommand(qry, connection);  
  65.   
  66.         cmd.ExecuteNonQuery();  
  67.   
  68.     }  
  69.   
  70.     public static List < Employee > GetAllEmployees()  
  71.   
  72.     {  
  73.   
  74.         List < Employee > listEmployee = new List < Employee > ();  
  75.   
  76.         OleDbConnection connection = new OleDbConnection();  
  77.   
  78.         connection.ConnectionString = @  
  79.         "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Category.mdb;";  
  80.   
  81.         connection.Open();  
  82.   
  83.         string qry1 = "select sName,Image,Description from AddNotice";  
  84.   
  85.         OleDbCommand cmd = new OleDbCommand(qry1, connection);  
  86.   
  87.         OleDbDataReader rdr = cmd.ExecuteReader();  
  88.   
  89.         while (rdr.Read())  
  90.   
  91.         {  
  92.   
  93.             Employee employee = new Employee();  
  94.   
  95.             employee.name = rdr[0].ToString();  
  96.   
  97.             employee.image = rdr[1].ToString();  
  98.   
  99.             employee.description = rdr[2].ToString();  
  100.   
  101.             listEmployee.Add(employee);  
  102.   
  103.         }  
  104.   
  105.         connection.Close();  
  106.   
  107.         return listEmployee;  
  108.   
  109.     }  
  110.   

Create another webform. Drag and drop a GridView and a ObjectdataSource. Click on configure data source in object data source. A new window will open.



Select “EmployeeDataAccessLayercs” from the drop down and click next. This window will open.



Select the method carefully from the drop down list for SELECT, UPDATE and DELETE that we have created in the “EmployeeDataAccessLayercs” class. Then click Finish. Now click on the GridView and change the data source from none to ObjectDataSource1. Then check the enable editing and enable deleting options.

Now, click on the GridView and flip to source mode. To enable the editing and deleting options, we need to define a DataKeyNames. Write DataKeyNames=”Image” and AutoGenerateColumns=”False”. Since we need to show an image in the GridView, we need to override the template fields of Name, Image and Description. Delete all the code from the Column field and write some new code. Have a look at here:

  1. <Columns>  
  2.     <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />  
  3.     <asp:TemplateField HeaderText="name" SortExpression="name">  
  4.         <EditItemTemplate>  
  5.             <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("name") %>'>  
  6.             </asp:TextBox>  
  7.         </EditItemTemplate>  
  8.         <ItemTemplate>  
  9.             <asp:Label ID="Label1" runat="server" Text='<%# Bind("name") %>'>  
  10.             </asp:Label>  
  11.         </ItemTemplate>  
  12.     </asp:TemplateField>  
  13.     <asp:TemplateField HeaderText="image" SortExpression="image">  
  14.         <EditItemTemplate>  
  15.             <asp:Image ID="TextBox2" runat="server" ImageUrl='  
  16.                 <%# Eval ("Image") %>' Height="50" Width="50" />  
  17.             </EditItemTemplate>  
  18.             <ItemTemplate>  
  19.                 <asp:Image ID="TextBox2" runat="server" ImageUrl='  
  20.                     <%# Eval ("Image") %>' Height="100" Width="100" />  
  21.                 </ItemTemplate>  
  22.             </asp:TemplateField>  
  23.             <asp:TemplateField HeaderText="description" SortExpression="description">  
  24.                 <EditItemTemplate>  
  25.                     <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("description") %>'>  
  26.                     </asp:TextBox>  
  27.                 </EditItemTemplate>  
  28.                 <ItemTemplate>  
  29.                     <asp:Label ID="Label3" runat="server" Text='<%# Bind("description") %>'>  
  30.                     </asp:Label>  
  31.                 </ItemTemplate>  
  32.             </asp:TemplateField>  
  33.         </Columns> 

We must redefine the template mode for all the three properties Name, Image and description.

image

This is how to configure a GridView depending on our own requirements. The code has been given to you. I hope this will help you much.

Thank you.