Filtering DataSet Based On Column Values In C#

In this blog, we will learn, how to filter a DataSet, based on the value of a particular column. Suppose, we have a DataSet ‘ds’ with the records of some users, given below:

Now, we want to filter this DataSet, based on States.

For example, we have to get the records with State as “Maharashtra” only

For that we can write the code, given below:

1. ds.Tables[0].DefaultView.RowFilter = "State = 'Maharashtra'";  
2. DataTable dt = (ds.Tables[0].DefaultView).ToTable();  

Now, in DataTable dt, we will get the records from DataSet ds with State=Maharashtra.

We can also set this filtered DataSet directly as the DataSource of Gridview.

1. ds.Tables[0].DefaultView.RowFilter = "State = 'Karnataka'";  
2. GridView1.DataSource = ds.Tables[0].DefaultView;  
3. GridView1.DataBind();  

We can also give multiple conditions in filtering with “and” or “or” operators. 

1. ds.Tables[0].DefaultView.RowFilter = "State = 'Maharashtra' and City='Nagpur'";  
2. DataTable dt = (ds.Tables[0].DefaultView).ToTable();   

Hope that this will be helpful for someone out there!

Changing The Default Values Of Select/Edit In SQL Server Management Studio

In this blog, we will learn, how to change the default values of select/edit command in SQL Server Management Studio.

Many of us have used the methods, given above, to select, insert and update the records in a table. It is much easier, compared to writing a query for the same. With these default numbers, it is not possible to edit the big tables. Thus, I searched for the options to change these numbers and found out this.

I know that this is not a big deal for most people, but for someone like me (who rarely looks at what all those menus are for!), it matters a lot and I knew many people like me complained about the same. Thus, I thought of sharing this.

Follow these steps,

Click “Tools” Menu -> “Options”.

Expand “SQL Server Object Explorer”.

Click “Commands”.

Change the values for edit & select.

Press “OK”

That’s it. Now, if you right click your table you can see that your values are changed.

Hope that this will be helpful for someone out there!

Exporting HTML Table To Excel Using jQuery

In this blog, we will see how to export an HTML table to an Excel file, using the simple table2excel jQuery plugin. First we, will create the HTML table, which shows employee details and an "Export to Excel" button, as shown below.

1. <div>  
2.     <table id="mytable" cellpadding="5" border="1" cellspacing="0">  
3.         <thead>  
4.             <tr>  
5.                 <th>  
6.                     Employee Name  
7.                 </th>  
8.                 <th>  
9.                     Age  
10.                 </th>  
11.                 <th>  
12.                     Designation  
13.                 </th>  
14.                 <th>  
15.                     Experience  
16.                 </th>  
17.             </tr>  
18.         </thead>  
19.         <tbody>  
20.             <tr>  
21.                 <td>  
22.                     Rajeev  
23.                 </td>  
24.                 <td>  
25.                     31  
26.                 </td>  
27.                 <td>  
28.                     Developer  
29.                 </td>  
30.                 <td>  
31.                     6  
32.                 </td>  
33.             </tr>  
34.             <tr>  
35.                 <td>  
36.                     Sandhya  
37.                 </td>  
38.                 <td>  
39.                     27  
40.                 </td>  
41.                 <td>  
42.                     Tester  
43.                 </td>  
44.                 <td>  
45.                     2  
46.                 </td>  
47.             </tr>  
48.             <tr>  
49.                 <td>  
50.                     Ramesh  
51.                 </td>  
52.                 <td>  
53.                     25  
54.                 </td>  
55.                 <td>  
56.                     Designer  
57.                 </td>  
58.                 <td>  
59.                     1  
60.                 </td>  
61.             </tr>  
62.             <tr>  
63.                 <td>  
64.                     Sanjay  
65.                 </td>  
66.                 <td>  
67.                     32  
68.                 </td>  
69.                 <td>  
70.                     Developer  
71.                 </td>  
72.                 <td>  
73.                     5  
74.                 </td>  
75.             </tr>  
76.             <tr>  
77.                 <td>  
78.                     Ramya  
79.                 </td>  
80.                 <td>  
81.                     23  
82.                 </td>  
83.                 <td>  
84.                     Developer  
85.                 </td>  
86.                 <td>  
87.                     1  
88.                 </td>  
89.             </tr>  
90.         </tbody>  
91.     </table>  
92.     <br />  
93.     <button onclick="exportexcel()">  
94.         Export to Excel</button>  
95. </div>  

Running the page will look as shown below.

Now, we reference the jQuery file and table2excel file in our head section.

1. <head runat="server">  
2.     <title>Table 2 Excel</title>  
3.     <script src="jquery.min.1.11.1.js" type="text/javascript"></script>  
4.     <script src="jquery.table2excel.min.js" type="text/javascript"></script>  
5. </head>  

Now, we write our exportexcel() function, as shown below.

1. <script type="text/javascript">  
2.         function exportexcel() {  
3.             $("#mytable").table2excel({  
4.                 name: "Table2Excel",  
5.                 filename: "myFileName",  
6.                 fileext: ".xls"  
7.             });  
8.         }  
9. </script>  

In the script given above, the filename is the name of the file downloaded and fileext is the extension of the file to be downloaded, which is XLS.

Now, we will run the page and click on Export to Excel button.

Our Excel file will be downloaded. On opening the file, we can see the data of our table, as shown below.



Our Excel exporting is complete.

Reference

jquery-table2excel

Summary

In this blog, we have learned how to export HTML table to an Excel file, using the table2excel jquery plugin. I hope this will be helpful.