This blog is moved to
http://amalhashim.wordpress.com

Sunday, April 4, 2010

Sql | Parameterized Queries

In msdn forums, a common question is regarding database retrieval and updation. To help such users I thought of bringing up this article. I want to give a concrete example, which will demonstrate the usage. To start with, I have a Employee database with a table named EmployeeDetails witht the following structure.

image

In the below code sample, I will show how to insert a record and get it back. To avoid the problems that can cause from SQL Injection, .Net framework has introduced Parameterized queries. Another way to get around this situation is using stored procedures.

using System;
using System.Data.SqlClient;

namespace ConsoleApplication2
{
class Program
{
public static void Main(string[] args)
{
string connectionString = "server=.;initial catalog=employee;user id=sa;password=sa123";

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("INSERT INTO" + "EmployeeDetails Values(@Id, @Name, @Address)", conn))
{
cmd.Parameters.Add("@Id", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@Address", System.Data.SqlDbType.VarChar);

cmd.Parameters["@Id"].Value = 4;
cmd.Parameters["@Name"].Value = "Rooney";
cmd.Parameters["@Address"].Value = "Manchester United";

int rowsInserted = cmd.ExecuteNonQuery();

Console.WriteLine("Rows inserted = " + rowsInserted);
}

using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeDetails", conn))
{
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine(reader["Id"].ToString() + " " + reader["Name"].ToString() + " " + reader["Address"].ToString());
}
}
else
{
Console.WriteLine("No Rows");
}
}
}
}
}
}

Hope this was helpful.

.Net Framework | Value Types

Value type holds data in the variable. They are stored in “Stack”. Because of this the performance is good and causes minimal overhead. Mainly there are three value types

1. In built

2. User defined(structs)

3. Enums

All the above are derived from System.Value

Type Bytes Occupied Range
System.SByte 1 -128 to 127
System.Byte 1 0 to 255
System.Int16 2 -32768 to 32767
System.Int32 4 -2147483648 to 2147483647
System.UInt32 4 0 to 4294967295
System.Int64 8 -9223372036854775808 to 9223372036854775807
System.Single 4 –3.402823E+38 to 3.402823E+38
System.Double 8 -1.79769319486232E+308 to 1.79769319486232E+308
System.Decimal 16 -79228162514264337593543950335 to 79228162514264337593543950335
System.Char 2  
System.Boolean 4  
System.DateTime 8 1/1/0001 12:00:00 AM to 12/31/9999 11:59:59 PM
System.Boolean 4 True/False
System.IntPtr Platform Dependent  

Nullable Types

In some scenarios we might need to store the value null in the basic types. For doing that we can make the type nullable as shown below

bool? isValid = null;
//or
Nullable<bool> isSelected = null;


Structures or User Defined Types


Struct as mentioned is also a value type and is stored on the stack. Struct resembles class, but have several differences. Structures are composite types. They form a meaningful  data. The most common example give for structures is the Point type available in System.Drawing namespace. Each point can be represented using x and y coordinate and hence a structure can be formed as


struct Point
{
public UInt32 X;
public UInt32 Y;
}



I have enhanced the Point structure as shown below



using System;
using System.Text;

namespace ConsoleApplication2
{
struct Point
{
public UInt32 X;
public UInt32 Y;

public Point(uint x, uint y)
{
this.X = x;
this.Y = y;
}

public static Point operator +(Point p1, Point p2)
{
Point newPoint = new Point(p1.X + p2.X, p1.Y + p2.Y);

return newPoint;
}

public static Point operator -(Point p1, Point p2)
{
Point newPoint = new Point(p1.X + p2.X, p1.Y + p2.Y);

return newPoint;
}

public override string ToString()
{
StringBuilder sb = new StringBuilder();
sb.Append("The current point has X = ").Append(this.X.ToString())
.Append(" and Y = ").Append(this.Y.ToString());
return sb.ToString();
}
}

class Program
{
static void Main(string[] args)
{
Point p1 = new Point(10, 20);
Point p2 = new Point(20, 30);

p1 += p2;

Console.WriteLine(p1);

}

}
}

As you can see the structure has a parameterized constructor and can have methods. It also has overloaded operators.


Note: We can’t use default constructors in Structure. Default constructor is used by the framework to initialize the fields in structure.


Note: Always make use of a structure only if the entire data in it constitute less than 16 bytes.


Enumerations or Enum


Enums are grouped constants. They are introduced mainly to make the code readable.


Lets extend the above application by adding an Enum Direction. Based on the direction the point will be moved.


using System;
using System.Text;

namespace ConsoleApplication2
{
struct Point
{
public UInt32 X;
public UInt32 Y;

public Point(uint x, uint y)
{
this.X = x;
this.Y = y;
}

public static Point operator +(Point p1, Point p2)
{
Point newPoint = new Point(p1.X + p2.X, p1.Y + p2.Y);

return newPoint;
}

public static Point operator -(Point p1, Point p2)
{
Point newPoint = new Point(p1.X + p2.X, p1.Y + p2.Y);

return newPoint;
}

public override string ToString()
{
StringBuilder sb = new StringBuilder();
sb.Append("The current point has X = ").Append(this.X.ToString())
.Append(" and Y = ").Append(this.Y.ToString());
return sb.ToString();
}

public void Move(Direction d)
{
switch (d)
{
case Direction.Up:
if (this.X > 0)
this.X--;
break;
case Direction.Down:
if (this.X < 1000)
this.X++;
break;
case Direction.Left:
if (this.Y > 0)
this.Y--;
break;
case Direction.Right:
if (this.Y < 1000)
this.Y++;
break;
default:
break;
}
}
}

enum Direction
{
Up,
Down,
Left,
Right
}

class Program
{
static void Main(string[] args)
{
Point p1 = new Point(0, 0);
Console.WriteLine(p1);
p1.Move(Direction.Left);
Console.WriteLine(p1);
p1.Move(Direction.Down);
Console.WriteLine(p1);
p1.Move(Direction.Left);
Console.WriteLine(p1);
p1.Move(Direction.Right);
Console.WriteLine(p1);
}

}
}

Friday, April 2, 2010

MVP – Visual C# – 2010



Dear Amal Hashim,


Congratulations! We are pleased to present you with the 2010 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in Visual C# technical communities during the past year.


The Microsoft MVP Award provides us the unique opportunity to celebrate and honor your significant contributions and say "Thank you for your technical leadership."


Toby Richards
General Manager
Community & Online Support

Monday, March 29, 2010

SQL Server | Resetting Identity Column

Identity column’s are used when you want to have a serial number for each row that get inserted into the table. In other word, it will create a numeric sequence. We can make a column identity while creating the table. Check this simple query which will create an Employee table.

CREATE TABLE EMPLOYEE(EmpID int identity(1, 1), EmpName varchar(20))

The above statement will create a table named EMPLOYEE with two columns, EmpID and EmpName. EmpID is an identity column, the 1st record will have the EmpID as 1 and all subsequent records will have a value incremented by 1. So if you give identity(1,2), each record will have the EmpID as an increment of 2

At some instant we might require the identity value of the insert statement.

SELECT @@IDENTITY

If you want to insert a value into an identity column you can use the SET IDENTITY_INSERT statement.
Once you delete all the data from the table, the identity column won’t get reset to the initial value. For doing this we must use the following statement.
DBCC CHECKIDENT('Employee', RESEED, 0)

Another approach is to Truncate. This will delete all data as well as reset the Identity column.

TRUNCATE TABLE Employee

MOSS Site Export Import

MOSS comes with a handy utility named “stsdam”. The utility is present in the following folder by default.

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN

For exporting a site use the following command

image

Either move to the BIN folder or SET path to BIN folder. The complete command is

stsadm –o export –url http://sites/site –filename filename.cab –overwrite

Replace http://sites/site with your site name and filename with the required filename you want. The switch overwrite will overwrite the file if the name you specified already exist. For getting the complete help of export, try the following command

stsadm –help export

If the exporting runs without error, you will get the following message

image

For importing you can use the import statement with stsadm.

stsadm –o import –url http://newsites/site –filename filename.cab

On success you will get a similar screen with the message “Operation completed successfully” at the end.

For more help on import use

stsadm –help import

Hope this helps!!!

Wednesday, March 10, 2010

Cookie manipulation through JavaScript

<script type="text/javascript">
currentuser = "amal"
window.onload=function() {
if (navigator.cookieEnabled) {
var username = readCookie("username");
if (username) {
alert("cookie name = " + username);
if (username != currentuser) {
eraseCookie(username);
} else {
setCookie("username",username);
}
} else {
alert("setting cookie");
setCookie("username", currentuser);
}
}
}

// set cookie expiration date in year 2010
function setCookie(key,value) {
var cookieDate = new Date(2010,11,10,19,30,30);
document.cookie=key + "=" + encodeURI(value) + "; expires=" +
cookieDate.toGMTString() + "; path=/";
}

// each cookie separated by semicolon;
function readCookie(key) {
var cookie = document.cookie;
var first = cookie.indexOf(key+"=");

// cookie exists
if (first >= 0) {
var str = cookie.substring(first,cookie.length);
var last = str.indexOf(";");

// if last cookie
if (last < 0) last = str.length;

// get cookie value
str = str.substring(0,last).split("=");
return decodeURI(str[1]);
} else {
return null;
}
}

// set cookie date to the past to erase
function eraseCookie (key) {
var cookieDate = new Date(2000,11,10,19,30,30);
document.cookie=key + "= ; expires="+cookieDate.toGMTString()+"; path=/";
}

</script>

Accessing an ASP.Net WebMethod From JavaScript

Lets start by creating a sample webservice.

  1. Create a new Web Service application project from Visual Studio

    image

  2. Click Ok
  3. For demonstration lets keep the webservice simple. The template automatically create a HelloWorld webmethod.
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Services;

    namespace TestWebService
    {
    /// <summary>
    ///
    Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {

    [WebMethod]
    public string HelloWorld()
    {
    return "Hello World";
    }
    }
    }

  4. For demonstration lets keep the webservice simple. The template automatically create a HelloWorld webmethod.

  5. Add a new website to the Project

    image


  6. Now we need to add the TestWebService reference to our TestWebsite. For this, right click the TestWebsite reference and select Add Service Reference. image

  7. This will bring up the following window, hit the discover button and press OK image

Now onload of the webpage I am going to call the WebService method using JavaScript.

<script language="javascript" type="text/javascript">
window.onload = CallWebService();
var xmlHttp;
function CallWebService() {
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
xmlHttp.open("post", "http://localhost:2700/Service1.asmx/HelloWorld", false);
xmlHttp.onreadystatechange=doUpdate;
xmlHttp.send();
return false;
}

function doUpdate(){
if (xmlHttp.readyState == 4) {
var startTag = "<string xmlns=\"http://tempuri.org/\">";
var endTag = "</string>";
var exch;
var valueStart = 0;
var valueEnd = 0;
valueStart = xmlHttp.responseXML.xml.indexOf(startTag, valueEnd) + startTag.length;
valueEnd = xmlHttp.responseXml.xml.indexOf(endTag, valueEnd + 1);
exch = xmlHttp.responseXML.xml.substring(valueStart, valueEnd);
alert(exch);
}
}
</script>
View the default.aspx in browser will bring the following alert windowimage

What we are doing is, registering a method CallWebService() to the window onload event. In this method we are creating an XMLHttp object. This is used for AJAX call. Once we have this object, we will use the Open method for posting the request to the webservice. The false argument makes the call synchronous. If you are looking for asynchronous, the simply pass true. We need to assign a callback method. This method will get executed once the request object got changed. XMLHttp object has a property called ReadyState which can hold the following value0- uninitialized
1- loading
2- loaded
3-interactive
4-complete

We will check if ready state is complete, means our request was successful. Now we need to parse the returned xml string and get the value.

Note: I will update this article and add some more details like passing parameters back and forth. Keep checking :-)