Saturday, November 12, 2011

Using AJAX AutoCompleteExtender with Web Services

Hi All,


Today I will demonstrate you on how to populate a text box with suggested values when user types a character in text box using a AJAX Auto Complete Extender. This demonstrate uses web service to get data from the database.
Suppose I have a database called "Demo" and have a table called "tblFlightInformation" to store Flight Schedule details.

Following is the database table script;

USE [Demo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblFlightInformation](
[FlightId] [int] NOT NULL,
[FlightDestination] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FlightLeaveTime] [datetime] NULL,
[FlightReturnTime] [datetime] NULL,
 CONSTRAINT [PK_tblFlightInformation] PRIMARY KEY CLUSTERED
(
[FlightId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



Lets populate the table with sample data;

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (1, 'India', '2011-11-12 19:30:00', '2011-11-12 23:00:00')

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (2, 'Italy', '2011-11-13 06:00:00', '2011-11-13 12:45:00')

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (3, 'Singapore', '2011-11-13 04:10:00', '2011-11-14 01:15:00')

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (4, 'America', '2011-12-01 09:20:00', '2011-12-06 08:50:00')

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (5, 'Saudi Arabia', '2011-12-10 23:10:00', '2011-12-13 19:10:00')



Lets create an asp.net web site and add a web service (WebServiceFlightInformation.asmx) and a web method called "LoadDestinationInformation".

[WebMethod]
public List<string> LoadDestinationInformation(string prefixText)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightInformation"].ConnectionString;

using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select FlightDestination from tblFlightInformation where " +
"FlightDestination like @FlightDestination + '%'";
cmd.Parameters.AddWithValue("@FlightDestination", prefixText);
cmd.Connection = conn;
conn.Open();

List<string> FlighDestination = new List<string>();

using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
FlighDestination.Add(sdr["FlightDestination"].ToString());
}
}

conn.Close();
return FlighDestination;
}
}


After that lets create the user interface by adding a aspx page to our web site. Following is the mark up for the aspx page.

<%@ Page Title="Home Page" Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Ajax AutoCompleteExtender without Webservice</title>
</head>
<body>
<form id="form1" runat="server">
<ajax:ToolkitScriptManager ID="ScriptManager1" runat="server" EnablePageMethods = "true"/>
<div>
<asp:TextBox ID="txtCountry" runat="server" AutoPostBack="True"></asp:TextBox>
<ajax:AutoCompleteExtender ID="AutoCompleteExtender1"
runat="server"
TargetControlID="txtCountry"
MinimumPrefixLength="1"
EnableCaching="true"
CompletionSetCount="1"
CompletionInterval="1000"
ServiceMethod="LoadDestinationInformation"
ServicePath="WebServiceFlightInformation.asmx">
</ajax:AutoCompleteExtender>
</div>
</form>
</body>
</html>


That's all and have a look into the below image, that's how it works.....



Happy Coding.

Thursday, November 10, 2011

Lets work around with XPath

XPath is nothing more than a language which gives you the capability of finding information in an XML document.
So what is XPath is all about : 
  • XPath is a syntax for defining parts of an XML document
  • XPath uses path expressions to navigate in XML documents
  • XPath contains a library of standard functions
  • XPath is a major element in XSLT
  • XPath is a W3C recommendation
Lets start with a sample demo;
I have created an xml file which contains information about flights schedule and I call it as "flightSchedule.xml" for the moment. 


Following is my xml file:
<!--?xml version="1.0" encoding="utf-8" ?-->
<flightschedule>
  <flight>
    <flightid>flight-001</flightid>
    <flightdestination>India</flightdestination>
    <flighttime>10.45.00AM</flighttime>
    <flightreturntime>15.20.00PM</flightreturntime>
</flight>
  <flight>
    <flightid>flight-002</flightid>
    <flightdestination>Singapore</flightdestination>
    <flighttime>06.00.00AM</flighttime>
    <flightreturntime>12.45.00PM</flightreturntime>
 </flight>
  <flight>
    <flightid>flight-546</flightid>
    <flightdestination>West Indies</flightdestination>
    <flighttime>01.00.00AM</flighttime>
    <flightreturntime>23.00.00PM</flightreturntime>
  </flight>
  <flight>
    <flightid>flight-879</flightid>
    <flightdestination>Australia</flightdestination>
    <flighttime>08.10.00AM</flighttime>
    <flightreturntime>13.30.00PM</flightreturntime>
  </flight>
  <flight>
    <flightid>flight-324</flightid>
    <flightdestination>Japan</flightdestination>
    <flighttime>04.40.00AM</flighttime>
    <flightreturntime>16.00.00PM</flightreturntime>
     </flight>
</flightschedule>


Now lets see how can we access or load this xml file in my .aspx page.
  • We can use "XMLHttpRequest" to load the xml file like the following                                                                                  var xhttp = new XMLHttpRequest();
So, in my example i will show you how to select values from the xml file using XPath. Imaging I want to select all the flight ids. So I can set is as "/flightSchedule/flight/flightId", and I can select the values using var nodes = xml.selectNodes(path);


And here is the output
flight-001
flight-002
flight-546
flight-879
flight-324


Following is the code for the sample demo;
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="xPath.aspx.cs" Inherits="xPath.xPath" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml">
<body>
<script type="text/javascript">
function loadXMLDoc(dname)
{
if (window.XMLHttpRequest)
{
xhttp = new XMLHttpRequest();
}
else
{
xhttp = new ActiveXObject("Microsoft.XMLHTTP");
}
xhttp.open("GET", dname, false);
xhttp.send("");
return xhttp.responseXML;
}
xml = loadXMLDoc("flightSchedule.xml");
path = "/flightSchedule/flight/flightId";
// code for IE
if (window.ActiveXObject)
{
var nodes = xml.selectNodes(path);


for (i = 0; i < nodes.length; i++)
{
document.write(nodes[i].childNodes[0].nodeValue);
document.write("<br />");
}
}
// code for Mozilla, Firefox, Opera, etc.
else if (document.implementation && document.implementation.createDocument)
{
var nodes = xml.evaluate(path, xml, null, XPathResult.ANY_TYPE, null);
var result = nodes.iterateNext();


while (result)
{
document.write(result.childNodes[0].nodeValue);
document.write("<br />");
result = nodes.iterateNext();
}
}
</script>
</body>
</html>


Happy Coding 




Monday, November 7, 2011

File Upload Control inside update panel - ASP/AJAX

Today when I was developing my asp.net application at my office, I came across with some difficulties when dealing with the File Upload Control that I had in asp.net. I have placed a button and a file upload control with some other controls inside an update panel. The issue was whenever I clicked on the upload button, the property "HasFile" of the file upload control gave me "false". hmm, what went wrong?? was it because of the file upload does not post back data when it reside inside a update panel...???  

This issue made me read some articles and do some research on this and it was due to missing some attributes on POST method. huh???

So what can we do to avoid this.

Add the following code in your page_Load() method:


VB
Page.Form.Attributes.Add("enctype", "multipart/form-data")

C#

Page.Form.Attributes.Add("enctype", "multipart/form-data");



That's all and my file upload control started working fine !!!

Happy Coding.

Monday, September 12, 2011

Using SQLCMD in SQL Server Management Studio & Pass parameters dynamically to the SQL Script

SQLCMD is a command line application that allows SQL queries to be written and executed from the command prompt. Sounds interesting?

Suppose you want to write a SQL script to create a database and you want it to be done something like parameterized fashion where you can simply pass your database name, data path (mdf and ldf). For this kind of scenarios SQLCMD is handy.

Follow the given SQL Query below.

CREATE DATABASE [$(DatabaseName)]
ON
(
NAME = [$(DatabaseName)],
FILENAME = '$(FilePathData)\$(DatabaseName)_Data.mdf',
SIZE = 51200 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10240 KB
)
LOG ON
(
NAME = [$(DatabaseName)_log],
FILENAME = '$(FilePathData)\$(DatabaseName)_Log.ldf',
SIZE = 102400 KB,
MAXSIZE = 2097152 MB,
FILEGROWTH = 1024 KB
)


Note : Save the SQL script as “DatabaseCreateScript.sql”.

If you can’t see the SQLCMD,
Go to Customizeà Query then select SQLCMD Mode and drag it into the Query.

Follow the figure below.








Now suppose you want to execute your sql script (“DatabaseCreateScript.sql”) and you want to pass above parameters manually using command prompt, following is the simple and easy way to do that;

sqlcmd -i "DatabaseCreateScript.sql" -v DatabaseName=Vzette -S DatabaseServername -v FilePathData="C:\Vzette" -U SQLServerUsername -P SQLServerPassword

Finally refresh the databases under SQL Server Management Studio and you will see the newly created database called “Vzette” and check the path (C:\Vzette) for data files. Easy hah?




Wednesday, May 4, 2011

Find Client PC Operating System and It's Service Pack

These days I am working on a project and today (04.05.2011) I came up with some sort of a small modification to the application by letting the user to see the current OS and It's Service Pack in their application. So I wrote the following piece of code and thought of sharing it with my techi friends :)


Note : Please add reference to System.Management


using System;
using System.Management;

namespace RetrieveSystemInfo
{


class Program
{


static void Main(string[] args)
{

 ManagementObjectSearcher searcher2 = new
 ManagementObjectSearcher("select * from Win32_OperatingSystem");


 foreach (ManagementObject objMgmt in searcher2.Get())
 {
  string sss = objMgmt["name"].ToString();
  string [] aa = sss.Split(new char [] {'|'});
  string bb = aa[0].ToString();

 OperatingSystem OS = Environment.OSVersion;
 string ServicePackDetails = OS.ServicePack;

 Console.WriteLine("OS Version is : " + bb);
 Console.WriteLine("Service Pack is : " + ServicePackDetails);

}

}
}
}

Tuesday, April 12, 2011

GUID or INT for my Primary key field


When my Tech Lead came to me couple of months ago and asked me to do a particular SQL Database task and the task was to create a sample data table and create a .net application to to the CURD operations on it. 

At that time I was a bit nervous, whether to use INT data type as the primary key or to use GUID type as the primary key; so I started working on that and took a chance to speak to my other team members and do some kind of a research on it. after having a proper understanding on that I used INT as my primary key based on the details I found on technical articles.

Below are the main advantages and disadvantages about both INT and GUID data type.

Advantages

INT 
  1. Small amount of storage size (and integer is 4 bytes)
  2. Increased readability, practical use in testing, and easy to understand
  3. Support for functions that return the last primary key generated (@@IDENTITY, SCOPE_IDENTITY())

GUID
  1. The primary key is uniquely identified in the entire system (Unique across the server.)
  2. Easy to merge tables
  3. Don't have problems when inserting a large number of operations

Disadvantages

INT 
  1. Hard to work with distributed tables
  2. Primary key in the form of INT/BIGINT is LUID, local unique identifier, which is only used locally in a table
  3. After a large number of operations (insert, delete), the counter for primary key can be reset, bringing the problem of chronology to 1

GUID

  1. Bigger storage size (16 bytes - four times the size of an integer)
  2. Hard to remember and understand

Tuesday, April 5, 2011

Sync Services for ADO.NET


Introduction to Sync Services for ADO.NET and the Visual Studio Designer

Hi techies,
Just thought of sharing some important stuff that can be used with Visual Studio and its non other than Sync services. In this session this will introduce Sync Services for ADO.NET which is a part of the Microsoft Sync Framework (MSF). You can watch the video using the following link. Hope you will enjoy it. :)

Sync Service