Sunday, July 12, 2009

Dynamic searching/filtering of DataGridView, ListBox

Have you seen those nifty tricks where a data grid view or listbox is filtered as you type in a text box? Believe it or not its really really easy using both Visual Basic and C#. I thought it was hard too… that i have to run through nested for loops… but Visual Studio it self has done the hardest part and made it easy for us!

Difficulty : Easy

Time required : Less than 5 minutes

Prerequisites : Connecting to a database, Filling in a DataSet/DataTable

 

Ok lets get it started, lets first do it in Visual Basic

image

All you need is a data grid view and a text box! Its simple as that! Now lets load the data! Make sure you have created an SQL Server database, and filled it with data!

OK lets fill the data grid view with the data!

 

Imports System.Data.SqlClient

Public Class Form1


Dim ds As New DataSet


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim con As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=true; Initial Catalog=Bodgett")
        con.Open() 

         
        Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Employee", con)

        da.Fill(ds, "Emp")


        DataGridView1.DataSource = ds.Tables("Emp")

    End Sub


End Class

Ok…now run the project and you will be (you should! ) see the data grid view filled with the data! Like this…

image

Assuming that everyone went as planned, lets do the actual filtering!

Before actually coding, here is a heads up on the syntax used for the filtering.

Lets say we want to find the employee with the first name ‘Lilak’

Then the row filter would be Emp_FName = 'Lilak'

Or lets say that we want to find some one with the email address equals to ‘emiley@bodgett.com’

then the row filter would be Emp_Email = 'emiley@bodgett.com'

But how do we search for both at the same time, simple combine them with a “and”

eg: Emp_FName = ‘Lilak’ AND Emp_Email = 'lilak@bodgett.com'

But as you can see, there is no use of filtering without the use of WILD CARDS! (I love ‘em!)

The wild cards that can be used in a Data Grid View are % and *, which does the same job!

Now lets say we want to filter records based on the email provider.

Its going to be like Emp_Email LIKE '%yahoo%'

the % sign represents 0 or more characters.

So now hoping that you guys know how to write the filtering syntax, lets get wet with the actual coding… well, there is no CODING that you to do, as it is something that could be done with…yes my friends, 1 LINE OF CODE :)

ds.Tables("Emp").DefaultView.RowFilter



ds is our DataSet, but we are actually applying the coding to a DataTable’s (which is “Emp”) DataView. We are changing the RowFilter property of the DataView.



ds.Tables("Emp").DefaultView.RowFilter = "Emp_FName LIKE '*" & TextBox1.Text & "*'"


 



This changes the RowFilter while text is being typed on to the textbox, which dynamically filters the records according to the text. Notice that i have used * but remember % can be used too. For an example if the user enters the word Jason to the textbox, the rowFilter would look like



Emp_FName LIKE '*Jason*'



including the quotes ' '



Now run the program and see how it is…



So how do you filter based on multiple attributes. Easy! Just separate the multiple attributes with a OR



So if we want to filter using both First name and Last name we would use something like



ds.Tables("Emp").DefaultView.RowFilter = "Emp_FName LIKE '*" & TextBox1.Text & "*' OR Emp_Lname LIKE '*" & TextBox1.Text & "*'"



For the input “Jason” for the textbox this would output something like



Emp_FName LIKE '*Jason*' OR Emp_LName LIKE '*Jason*'



So this is it folks, the coding is over and so is the tutorial :)



For more information on rowFilter and rowFilter expression read these articles on MSDN



http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx



http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx



Here is the complete coding for whoever wants it :D



Imports System.Data.SqlClient

Public Class Form1


Dim ds As New DataSet


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim con As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=true; Initial Catalog=yourDBName")
con.Open()


Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Employee", con)

da.Fill(ds, "Emp")


DataGridView1.DataSource = ds.Tables("Emp")

End Sub


Private Sub
TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
ds.Tables("Emp").DefaultView.RowFilter = "Emp_FName LIKE '*" & TextBox1.Text & "*' OR Emp_Lname LIKE '*" & TextBox1.Text & "*'"

End Sub
End Class



Blogger Labels: Dynamic,DataGridView,ListBox,tricks,data,grid,text,Visual,Basic,Studio,self,Easy,Time,Prerequisites,database,DataSet,DataTable,Server,System,SqlClient,Public,Class,Private,sender,Object,EventArgs,MyBase,Load,SqlConnection,Source,Initial,Catalog,Bodgett,Open,SqlDataAdapter,SELECT,FROM,Employee,Fill,DataSource,syntax,WILD,CARDS,View,records,provider,characters,DefaultView,RowFilter,DataView

Sunday, July 5, 2009

OMG! Duplicate Files!! Not a problem anymore!

There was a time where it took me zillion hours to go through my playlist, my software folders and all my other load of images and videos….only to find they contain duplicates! Well though it took me zillion hours, i doubt whether i have done the job correctly!

But now it takes me only 10 seconds to scan my music folder, 15 seconds or less to scan my software list and less than 5 seconds to scan my list of images and get my hands on a neatly arranged, flexible and comprehensive list of duplicate files…all ready to be displayed in any customized way i want…

Ladies and Gentlemen, introducing Duplicate Cleaner by Digital Volcano. This too good to be free utility makes searching across directories and finding duplicates as easy as 1,2,3!

image

Digital Volcano, Duplicate Cleaner 1.3 (The latest version at the time of writing) starts with a very easy to understand interface!

First you need to decide where am i going to search for duplicates! You can choose any directory or a drive! Simply select it and click Add Path.

Now choose what you want to search :)

There are 2 handy sections, File Search and Music  Search…

image

The File search includes the basic search options. You can specify a File Filter (Wildcards) to limit which files to search for. The other amazing feature is check for the Same Content(CRC) which guarantees you almost 100% accurate search results :) Apart from that, you can search for same File Name, Size or Date.

The second option (Music Search, which saved me gigs out of my huge music collection) is the option to search for music files. It searches for songs with the same Artist, Title or Album.

Example result from my music folder may show that a particular song is there in 2 folders. Once i get the results, i can just check whatever file i need and press “Remove Selected”

image

Removing, does not necessarily mean getting rid of the file, that is why you can either delete to recycle bin, permanently delete or move all checked files to another folder.

 

Now lets say, your search returned 1000 duplicate entries, are you going to go through everything and check all by your self? Nope! That is when the handy Selection Assistant comes in to play!

image

It can make your life easier by specifying which files you want to delete, whether the file with the lowest bit rate, lowest sample rate or shortest length. (The option is to actually keep the opposite )

Or keep the newest/oldest or smallest/largest or by search pattern. When you click close after selecting the options to your heart’s content, just click remove selected files to get rid of them!

I anyway like the CRC option in the file search and it is the most used search method for me.

Duplicate Cleaner is a truly amazing piece of freeware, which perfectly does the job and saves your hard disk space and the money in your wallet!

Brilliant job, Digital Volcano