Register Script into Page Header from ASP.NET Content Page

We used the ‘ScriptManager.RegisterStartupScript’ to register script from a Content Page. However this approach adds the JavaScript reference inside the <body> tag of the page and not the <head>. If we want to add script tag in <head> tag then we should add it in following way using System.Web.UI.HtmlControls.HtmlGenericControl class.

protected void Page_Load(object sender, EventArgs e)
{
HtmlGenericControl ctrl = new HtmlGenericControl("script");
ctrl.Attributes.Add("type", "text/javascript");
ctrl.Attributes.Add("src", @"Scripts\Alert.js");
this.Page.Header.Controls.Add(ctrl);
}

Run the application, right click the page and View Source.

image path problem with routing in website

When we are applying routing with parameters in asp.net 3.5 then images are not displayed due to wrong path of images.

So we have to change the code for assiging image path in following way.

In aspx page

<img src="images/icon_first.gif" alt="" /> is replaced by <img src='<%= ResolveUrl("~/images/icon_first.gif") %>'  alt="" />

 
Same way, in javascript

 $get(obj).src ="images/button_qittop.jpg"; is replaced by $get(obj).src = '<%= ResolveUrl("~/images/button_qittop.jpg") %>';

Common Table Expression

This is one of the most useful features found in SQL
A simple use of CTE (Common Table Expression) is shown as under.
Suppose that we have an ‘employee’ table as under

empid       name reportto
1        Employee 1    2
2        Employee 2    3
3        Employee 3    NULL
4        Employee 4    3
5        Employee 5    2
WITH empHierarchy (empid,name,reportto, HierarchyLevel) AS
(
   -- Base case
   SELECT
      empid,name,reportto,
      1 as HierarchyLevel
   FROM 
      employee
   WHERE 
      reportto is null
   UNION ALL
   -- Recursive step
   SELECT
      e.empid,e.name,e.reportto,
      eh.HierarchyLevel + 1 AS HierarchyLevel
   FROM 
      employee e
   JOIN empHierarchy eh ON
      e.reportto = eh.empid
)
SELECT * FROM empHierarchy

 
This query will give a very interesting output.
It will show you at which level an employee comes.
 
The above example shown is a very simple use of ‘recursive CTE’.
CTE can be used in different queries as ‘temporay views’ inside stored procedures. Thus it can solve various purposes.
 
Note: The Select statement is very important to run the CTE.

SQL Injection

One of the causes of SQL injection is Incorrectly filtered escape characters. i.e. the escape characters are not filtered but are directly passed into an SQL statement. This infant helps many malicious uses to hack a site.
Consider the following simple SQL statement.

statement = "SELECT * FROM users WHERE name = '" + userName + "';"

Now if in place of “userName” some data as a’ or ‘t’ = ‘t is entered
The above statement will be rendered as

SELECT * FROM users WHERE name = 'a' OR 't'='t';

which is a valid statement for SQL and hence it will be executed and provide needed information to the hackers.

Again something like
;a’ ;DROP TABLE users; can be entered into the “userName” field which when executed will successfully drop a table and stop the site.

Prevention of such type of SQL injection can be done by using parameterization statements
C# Eg.

using (SqlCommand myCommand = new SqlCommand("SELECT * FROM USERS WHERE USERNAME=@username AND PASSWORD=HASHBYTES('SHA1', @password)", myConnection))
{
        myCommand.Parameters.AddWithValue("@username", user);
        myCommand.Parameters.AddWithValue("@password", pass);
        myConnection.Open();
        SqlDataReader myReader = myCommand.ExecuteReader())
        ...................
}

Note: The best way to prevent SQL injections is to use Stored Procedures

Adding Color to a DropDownList in JavaScript

Suppose I have a DropDownList and I want to colour the text inside the list with red using javascript then the code for it will be as under :

ddlDummy = document.getElementById("ddlDummy");
var contents =  SomeArrayListValues;
for (var i = 0; i < contents.length; i++) 
{
        var opt = document.createElement("option");
        opt.text = contents[i].DummyName;
        opt.value = contents[i].DummyID;
        opt.style.color = ‘Red’;
        ddlDummy.options.add(opt); 
}

Verfication and Validation

Verification: The main requirement for verification are documents like business requirement speculations, system requirement specifications, high level requirements, use cases, reference web sites etc. . Meetings are conducted or the documents are reviewed to check whether the product is fulfilling the clients’ requirements. Verification always takes place before validation and it is also known as Quality Assurance.

Validation: The main requirement for validation is the product itself. The product is tested to check whether it has fulfilling all requirements, as specified in the documents. The testing conducted can be any of Black Box, White Box or Experience based testing. Validation takes place only after coding is done or after a product can be executed. Validation is also known as Quality Control.

Disable Session Expiration when using Directory.Delete()

Once I was encountering a serious problem in my application.

Session of my application got expired every time I ran a Directory.Delete() statement.

On google I found out that if you delete the folder that are existing in your application, sessions will be expired…

So Session Expiration can be prevented by using one of the following steps

1- Use out-of-process mode session
2- Use the below code in Global.asax file

void Application_Start(object sender, EventArgs e)
{
   Ajax.Utility.HandlerPath = "ajax";

   BusinessFacade.Utilities.Common.DBConnectionString =    ConfigurationManager.AppSettings["DBConnectionString"];

   PropertyInfo p = typeof(System.Web.HttpRuntime).GetProperty("FileChangesMonitor", BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static);

   object o = p.GetValue(null, null);

   FieldInfo f = o.GetType().GetField("_dirMonSubdirs", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.IgnoreCase);

   object monitor = f.GetValue(o);

   MethodInfo m = monitor.GetType().GetMethod("StopMonitoring", BindingFlags.Instance | BindingFlags.NonPublic);

   m.Invoke(monitor, new object[] { });
}

Pls import System.Reflection Namespace at the start of the page.

If not in Global.asax page, this code can also be placed in the same file just before any Directory.Delete() statement.

Formating the date as 1st,2nd,3rd….

Displaying dates in format

Day(st/nd/th) MonthName Year format with the help of SQL query.

declare @fdate as datetime ='2009-10-31'
declare @tdate as datetime ='2009-11-30'
declare @cdate as datetime
declare @daynm as nvarchar(4)
declare @monthnm as nvarchar(10)
declare @yearnm as nvarchar(4)
declare @datetable as table (nday nvarchar(4),nmonth nvarchar(10),nyear nvarchar(4))

while @fdate<@tdate
begin
  set @cdate = DATEADD(day,1,@fdate)
  set @fdate = @cdate
  set @daynm =
  case
    when right(cast(day(@cdate) as nvarchar), 1) = '1' and DAY(@cdate) <> 11
    then cast(day(@cdate) as nvarchar)+''+ 'st'
    when right(cast(day(@cdate) as nvarchar), 1) = '2' and DAY(@cdate) <> 12
    then cast(day(@cdate) as nvarchar)+''+ 'nd'
    when right(cast(day(@cdate) as nvarchar), 1) = '3' and DAY(@cdate) <> 13
    then cast(day(@cdate) as nvarchar)+''+ 'rd'
    else cast(day(@cdate) as nvarchar)+''+ 'th'
  end
  set @monthnm = datename(MONTH,@cdate)
  set @yearnm = YEAR(@cdate)
  insert into @datetable (nday,nmonth,nyear)
  select @daynm,@monthnm,@yearnm
end

select * from @datetable

This will display the dates as

1st November 2009,
2nd November 2009 ....
11th November 2009
12th November 2009...
21st November 2009...
22nd November 2009...

This sql function can be generalised for any months by parametarizing it.

MERGE SQL Statement in SQL Server 2008

Merge statement is like a boon to database developers.

Earlier the database developer or database administrator had to write lengthy Transact SQL code to achieve INSERT, UPDATE and DELETE by comparing primary key values of source and destination tables. Obviously the operational changes takes place in the destination table if it matches with the criteria i.e. WHERE clauses.

In ORACLE we used to write CURSORS to achieve the same.

The merge statement works as under:

They first need to check if a record is available, based on primary key value comparison, in the destination table, if they are then an UPDATE statement is fired to update the destination values with that of the source values. If the values are not available in destination table then INSERT statement is fired to add values in destination table from source table.

If values are not available in destination table then DELETE statement is fired to delete records from destination table.

Complex joins can also be used with MERGE statements.

There are three different matched clauses in MERGE:

WHEN MATCHED THEN
WHEN [TARGET] NOT MATCHED THEN
WHEN SOURCE NOT MATCHED THEN

A simple example of Merge statement using TBL_SOURCE and TBL_DESTINATION is as under:

MERGE INTO TBL_DESTINATION
USING TBL_SOURCE
ON TBL_DESTINATION.PKFIELD1 = TBL_SOUCE.PKFIELD1
WHEN MATCHED AND ( TBL_DESTINATION.FIELD2 != TBL_SOUCE.FIELD2 )THEN
UPDATE SET TBL_DESTINATION.FIELD2 = TBL_SOUCE.FIELD2
WHEN NOT MATCHED THEN
INSERT VALUES ( TBL_SOUCE.PKFIELD1, TBL_SOUCE.FIELD2 )
WHEN SOURCE NOT MATCHED THEN
DELETE

Thus we can implement the feature of update/insert/delete using a single statement without checking through the records.

CommandBuilder in ADO.NET

Hello all,

Today, I am writing this blog about SqlCommandBuilder. CommandBuilder automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database. SqlCommandBuilder works with the SqlDataAdapter. First we need to populate the DataSet with the data and CommandBuilder will generate Insert/Update/Delete command on its own. I have created the sample to demonstrate this.

For sample first we fill the DataSet as follows:

SqlCommand command = new SqlCommand("SELECT * FROM Profile", connection);

SqlDataAdapter adapter = new SqlDataAdapter(command);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

DataTable table = new DataTable();
adapter.Fill(table);

In above snippet of code, we have created the SqlCommandBuilder object from the SqlDataAdapter. Now, if we query the Insert/Update/Delete command of SqlDataAdapter we can view the commands generated by SqlCommandBehavior.

Response.Write(builder.GetInsertCommand().CommandText + @"<br /><br />");
Response.Write(builder.GetUpdateCommand().CommandText + @"<br /><br />");
Response.Write(builder.GetDeleteCommand().CommandText + @"<br /><br />");

The above statements will output the query for Insert/Update/Delete statements.

Now, perform any Insert/Update/Delete operations and call Update in SqlDataAdapter. The relevant changes will be persisted to the database.

//Use Insert
DataRow row = table.NewRow();
row["firstName"] = "Jignesh";
row["lastName"] = "Panchal";
row["email"] = "jignesh@isummation.com";
row["status"] = true;
table.Rows.Add(row);

//Use Update
DataRow[] results = table.Select("email = 'jignesh@isummation.com'");
results[0]["firstName"] = "Jignesh";

//Use Delete
DataRow[] deleteThese = table.Select("email = 'jignesh@isummation.com'");
foreach (DataRow r in deleteThese)
{
     r.Delete();
}

adapter.Update(table);
table.AcceptChanges();

Thus, SqlCommandBehaviour can be used to used to generate Insert/Update/Delete commands in simple scenario.