I Learnt Today…

2012-01-16

The RowFilter property of the DataView allows to use flexible string similar to SQL or LINQ to easily filter rows wihtout having to iterate through them.

I had to implement a fully flexible search module for a project I’m working on. The user had to be able to perform a string search on any field displayed on the gridview. The above mentioned RowFilter method is really handy to provide such a functionality due to increased efficiency.

Assume following example:

The data table (in the database)

Field Name         Data Type  

Name                String

DateOfBirth        DateTime

Data

Name                     DOB

John                      1976-10-12

Sophie                   1990-12-30

If you want to use the RowFilter to enable flexible searching (i.e. if the user type “j” in the search textbox the search grid view would only show the record for John) you can use following code:

string SearchFor = SearchTextBox.Text;
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("Name LIKE '%", SearchFor, "%'");

This will allow the above explained behaviour so if the user now enter “h” in the textbox it will show both records because both John and Sophie have the letter “h” in their names.

So what if we repeat the same and use the following code for the date of birth field?

// WRONG CODE
string SearchFor = SearchTextBox.Text;
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("DateOfBirth LIKE '%", SearchFor, "%'");

We would expect the program to filter records similarly. However, if you enter “1990″ in the search textbox hoping it would filter Sophie, it would give you an error instead!

This is because the LIKE operator cannot work with non-charactor types.

The Solution!

We have to use a converter to convert the datetime field into a string just before the RowFilter is applied.

Here’s the code:

string SearchFor = SearchTextBox.Text;
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("CONVERT(DateOfBirth, System.String) LIKE '%", SearchFor, "%'");

The Convert function will cast the datetime value into string just before the LIKE operation takes place. And since the datetime value is only temperory converted, the original data are not affected as well.

So if you add this code to the previously worked code for the Name column as described below:

string SearchFor = SearchTextBox.Text;
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("Name LIKE '%", SearchFor, "%'");
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("CONVERT(DateOfBirth, System.String) LIKE '%", SearchFor, "%'");

The Result

Now if you type “John” the grid will only show records that match that value and if you type a digit (e.g. 30) it will show the record that has 30 in the dob field (i.e. the record for Sophie)

Note
You can use the same method for any other field type which doesn’t support use of LIKE directly.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2011-10-07

Timestamp is a value that is incremented by the database whenever an insert or update operation is performed.

Even though the name Timestamp could be a bit misleading, this value has no relevance to a clock related time. This only shows a linear progression of time.

For an example , it is something like your database saying it has been two update or insert commands since your last visit.

You can see this value by referring to @@DBTS  [ select @@DBTS ]

How can this be helpful at all?

Well, in simple terms, this helps to keep track of versions of records.

For an example, assume a scenario where you have to fetch a record from a table, manipulate the data and write it back.

What if the record gets changed (from another database call) after you fetched data? You will manipulate the old data and update the record without knowing somebody has updated the record in between your fetching and updating commands.

How can timestamp help?

You can add a column to your table (you can call it “Version”) and set its data type to TimeStamp. Then whenever you update or insert a record to this table, this column will record the database timestamp after that transaction.

So before writing your manipulated data, you can check if the timestamp value remains the same as what you read at the beginning of the transaction.

Following example demonstrates how timestamp can be used to monitor versions:

The Person table used for this example has a column called “Version” which is of type Timestamp.

First simply query the Version column of the table for the person called “Robert”

After an update to the same record, we will check the version (timestamp) of the same record:

As you can see, the timestamp value for the record has been automatically updated.

Note: Timestamp columns are automatically updated by the database engine so you do not have to specify value when either inserting or updating a row of a table which has a timestamp (version) column.

So to insert a record to a table with a timestamp column simply omit the timestamp column from your insert statement.

i.e. – Person table has following columns [Id, Given_Name, Family_Name, Age, Version]

Insert statement would be:

Insert into Person(Id, Given_Name, Family_Name, Age)
Values(001, "Robert", "Nox", 78);

The database will take care of the timestamp (version) column.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2010-04-01

We frequently get to fetch data from the database, update them and then write them back to the database.

Most of the time we only have to write them back as individual records.

How about updating a whole database table in the memory and having to synchronize all changes to the actual data table?

My initial thought was this would be full of complex coding. However, thanks to Microsoft, there’s nothing much to be done at all.

So how are we gonna do this is…

We will use a data adaptor to fill our data table as usual.

The only new thing is the use of a Command Binder.

Command Binder: A command binder is capable to detect changes that have occurred to a table (in the memory) and then automatically generate appropriate SQL statements to save those changes into the actual data table (in the database).

Following is a simple example: Scenario: In my application I had to take a database table name and present data in a data grid and then save all changes made by the user.

What we need: a data table an adaptor a connection a command builder I have defined them at the form level so I can use them across the form from different events.

private DataTable _tbl;

private SqlDataAdapter _adptr;

private SqlConnection _conn;

private SqlCommandBuilder _cbldr;

Step1: Initialize connection and retrieve data from the database

_tbl = new DataTable();
_conn = FetchData.GetOpenConnection();
_adptr = new SqlDataAdapter("Select * from " + DatabaseTableName, _conn);
_cbldr = new SqlCommandBuilder(_adptr);
_adptr.Fill(_tbl);

Step2: Let the user to change data (in here simply bind the table to a grid)

dgMainGrid.DataSource = _tbl;

Step3: Save (synchronize) changes to the actual database table Even though this is a complex process and undoubtedly would take a lot of effort to do manually, Thanks to .net framework all we need is a line of code.

_adptr.Update(_tbl);

Once you call the adaptor to update the table, it will use the command builder attached to it to generate all necessary SQL command building. The database table is now up-to-date!

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2009-05-19

English is not my first language so I use online dictionaries frequently as a part of my daily routine to clarify and learn unfamiliar words as I read through articles in the Internet.

I have been using www.ditionary.com for a long time and I had the plan to make this work easy by creating a small application to retrieve the definition with support of a web service. This morning suddenly a new idea hit me to use JavaScript to get the word in the first place and load the appropriate URL.

How Does This Work?

When a user enters a word in the www.dictionary.com interface it redirects the user to the following dynamic URL.

http://dictionary.reference.com/browse/Target-Word

Target-Word is The word we are seeking the definition for

What this JavaScript does is it retrieves the desired word from the user via a prompt, generate the appropriate URL according to the above format and open the new URL in a new window or tab so the time taken (under normal circumstance) to, load the web site, type-in the word, wait till the site redirects you, is saved. Now time is only taken for a single server request/response.

How To Set This Up?

It’s simple, just add a new favorite (for IE) or bookmark (for FireFox) in your browser and paste following code as the URL (for IE) or Location (for FireFox):

javascript:(function(){var%20word=prompt(%22Enter Word:%22);if(word!=null){window.open(%22http://dictionary.reference.com/browse/%22+word);}})();

 

 

 

 

 

 

 

Now, when you open the favorite/bookmark, it will ask you for the word you want to look for and open the definition in a new window/tab.

 

 

 

 

 

It’s easier to use if you add the bookmark/favorite as a button into your bookmark/links toolbar

This small script helps me a lot daily so I hope this would help you too!

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2009-05-11

I have being working on a software application made on .Net and recently my client asked me to use D LINQ instead of SQL.

D LINQ has great benefits loaded! As I started working with DLINQ I started to know that preventing SQL injection is not a headache anymore and misspelled SQL queries will not trouble agian at run time because DLINQ generates all necessary SQL inside the framework!

I chose to use annotations inside the class instead of using separate xml file. Following is a part of the first class I ported to D LINQ.

</p>
_
Public Class Process
_
Public ProcessID As Integer
_
Public BusinessProcessID As String
_
Public ProcessText As String
...
...

ProcessID column is the primary key of my database table tbl_Process.

Important Point: I use database to auto-generate values for the primary key column (integer value incremented by one).

So when I run the application, It gave me this unexpected error:

Cannot insert explicit value for identity column in table ‘tbl_Process’ when IDENTITY_INSERT is set to OFF.

Basically, the IDENTITY_INSERT when using the database to auto generate value for a field but when I ran a SQL insert statement at the database end it worked fine!

After some tough time I found out the solution for this problem!

When we use an auto-generate field in a data table we have to specifically mention it in the matching field in the appropriate class.

The code to state this is:

Syntax:

IsDbGenerated:=True

This has to be added to the annotation added for the specific column like:

</p>
_
Public ProcessID As Integer

Now D LINQ can understand that the field value is auto-generated by the database!

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2008-12-26

Today I was experimenting on a grid view where I was trying to select multiple rows of the grid view using a check box column.

I wrote some code in the CheckedChanged of checkbox but then I found that the code was not executed when the state of the checkbox is changed.

So I did a little googling and found out about this AutoPostBack property. This property defines whether the control should post back to the server each time when the user interacts with the control. Or, according to this scenario, a post back will fire when the user clicks on the check box or when the Checked property is changed.

AutoPostBack :

This value holds a boolean value (true/false)

If the property is set to true, a post back is sent immediately to the server and no post back is occurred when set to false.

The Use of AutoPostBack:

According to MSDN, for most WebControls, when AutoPostBack is false, only the events from actions that cause a net change in the state of the control are submitted to the server.

In other words some events are not queued to the server. For example no event is fired when a user selects a value from a drop down list or when user presses Enter or Tab key after entering a value to a textbox.

If you want such events to be fired then you have to enable autopostback by setting autopostback property to true.

How AutoPostBack Works :

When AutoPostBack is enabled, the .Net framework automatically injects following additional items into the generated HTML code.

1. Two Hidden variables with name __EVENTTARGET and __EVENTARGUMENT
2. A Java script method with name __doPostBack (eventtarget, eventargument)
3. OnChange JavaScript event to the control

What is __EVENTTARGET :

__EVENTTARGET tells the server which control wants to fire the event so that the framework can fire the event on that control.

What is __EVENTARGUMENT :

__EVENTARGUMENT can be used to provide additional information to the server about the event.

What is __doPostBack (eventtarget, eventargument) :

Parameters sent to this method holds relevant target and event argument values and this method sets those values into __EVENTTARGET and __EVENTARGUMENT hidden variables so that the server can read those.

Then this method submits the form to the server where the appropriate event will be fired.

What is OnChange JavaScript event to the control :

Every control has a client side event called OnChange. When AutoPostBack is enabled for a control the framework sets the handler for this client side event as the __doPostBack method and will pass the name of the control as the first parameter, eventtarget.

Ex/

Following shows how the framework binds the __doPostBack method to the OnChange event.

<input type=”checkbox” onclick=“javascript:setTimeout(‘__doPostBack(‘CheckBox1′,”)‘, 0)” />

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

Recommended Books:

• Pro ASP.NET 3.5 in C# 2008

• Professional ASP.NET 2.0 Special Edition
• Beginning ASP.NET 3.5: In C# and VB Languages

2008-12-22

Sometimes things we consider as simple take much time to be accomplished than we expect just because we forget!

Today I wanted to save a copy of a web based visual studio 2005 solution but it took life 10 minutes for me to accomplish that because I had completely forgotten the method to do it. Since keeping track of what I learn is one objective of this blog I decided to include this small detail today.

How to save a copy a solution in visual studio 2005 (Save As)

Select the solution (click on it) in the solution explorer

Now go to File menu and there will be a command to Save the solution to any place you want.

Ex/

If your solution name is “My Solution.sln”

When you select your solution in the solution explorer and go to the file menu, you will see command like:

Save My Solution.sln As

Simply click on that and you will get the usual Save As dialog box.

Some Details

Even though you can select the place to save project files in web based solutions such as web sites, the solution is saved in a different location which is located in your My Documents folder.

The solution file contains information about your projects and files including paths so if you want to move the entire solution to a different computer to continue work on a different workstation then this tip will become handy unless you don’t forget things as I do

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

Recommended Books:

• Microsoft – Visual Studio 2005 Unleashed
• Microsoft – Visual Studio 2008 Unleashed

2008-12-04

Today I had to take care of a problem of a certain company (CIS) that I consult. They had a problem of keeping the values modified in a template field of a gridview when the user moves from one page of the grid to another.

Template fields allow us to add custom columns into a datagrid or gridview. For an instance, we can add a grid column which has a textbox in each cell.

When the gridview has several pages, the gridview holds only the rows that are displayed in its Rows (collection) property. So we cannot access the values in other pages using the gridview.Rows property.

Then I thought that I could access the whole collection of rows by referring to the DataSource property of the gridview. This is a good idea because even though the gridview only shows the rows on the current page for display purposes, the data source property of the gridview holds the whole collection of rows in it.

ASP.Net clears the data source of any control at post backs. This is done to optimize performance of communications. In addition, the state of each control is stored in ViewState so it is not necessary to keep the datasource between postbacks.

The problem is worse now because I could not use both datasource and the direct row collection from the gridview.

Of course we can use either cache or session to keep the datasouce.

Session["DataSource"] = dt;  // store our table in the session

Then to synchronize (update) the datasource which was stored in the session we have to:

• Go to each row in the gridview
• Get the matching data row from the data table
• Update the fields of the data table row

Since we have enabled paging for the gridview, we have to do this when ever user changes the page he/she is viewing to preserve his/her modifications.

So we need to write our code in the PageIndexChanging event handler of the GridView.

In my example I have two columns of the grid view called “ID” and “Name” and the name is the only template column I have used so I am only updating that column in the stored data table.

Code:

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
// get the datatable from the
DataTable dTable = (DataTable)Session["dataSource"];

// now we will iterate through all rows of the grid
// then get the matching row from the data table (datasource of our grid)
// and append the updated data (by the user) to the selected data row
foreach (GridViewRow grv in this.GridView1.Rows)
{
// get the matching row from the data table
DataRow dRow = dTable.Rows.Find(this.GridView1.DataKeys[grv.RowIndex].Value);

// set values of updated columns : here I have only let the user to edit "Name" column
dRow["Name"] = ((TextBox)grv.FindControl("txtName")).Text;
}

// go to the next page of the grid
this.GridView1.PageIndex = e.NewPageIndex;
this.GridView1.DataSource = (DataTable)Session["DataSource"];
this.GridView1.DataBind();

// show the whole collection of data in the second grid (used only to display)
this.GridView2.DataSource = (DataTable)Session["DataSource"];
this.GridView2.DataBind();
}

I have used a different gridview called GridView2 to show the whole data source without paging.

So don’t misunderstand the use of GridView2 in the last section of the code. It’s used only do display the whole set of data (without paging).

I cannot upload my code into this blog because it seems like zip files are not supported. If you like to take a glance of my code just add a comment so I’ll send the code to you via email.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek