Inserting record in table with generating new id for primary key field using single query
November 12, 2007 at 10:21 am | In SQL Server | Leave a CommentQuery to Insert new record in table with generating new id in key column
If no records exist then new id is 1 else new id is greater then 1 of maximum id.
Example :
Inserting new product in product_master : -
Note : Assuming that productid is of type int.
Insert into product_master (productid,product_name,created_date)
select (select isnull(max(productid)+1,1) from product_master),’CPU’,getdate()
Basic of Ajax
November 7, 2007 at 8:06 am | In Ajax | Leave a CommentArticle Contents:
|
|
|
| Introduction |
|
Ajax is the shorthand notation for Asynchronous JavaScript and XML. It is a web development technique that is used for developing interactive web applications. Ajax develops interactive web application by sending a small amount of data to the server behind the scenes. The advantage of this is that the entire page is not reloaded every time when the user makes some changes in the input. It may seem that Ajax is some new technology. But that is not true. It is a new way of combining old technologies and representing them in a new way. · XHTML and CSS – for presentation· Document Object Model (DOM) – for dynamic display and interaction· XML and XSLT – for data interchange and manipulation· XMLHttpRequest – for asynchronous data retrieval· Java Script – for binding everything together Before going deep into Ajax, let us discuss some basics of web applications. When a person sits down to develop an application, he has two choices before him. · Desktop application · Web application Desktop applications normally come on a CD or are sometimes downloaded from some web site and are installed completely on the computer. Desktop application may use Internet sometimes to download some updates, but the code which is responsible for running those applications reside on the desktop only. On the other hand, Web applications run on a Web server which is present somewhere else and a person accesses those applications with the help of his browser. Desktop applications are pretty fast since they do not require any Internet connection to run and they have great user interfaces. They are also incredibly dynamic. A person can pull up menus and sub-menus and can do many more things without waiting for anything. And this feature is almost opposite to what happens in a web application. No doubt that the services provided by Web applications can never be provided by any desktop application, but it is also true that the user has to wait for everything in a Web application. Since Web applications use an Internet connection they are normally slower that desktop applications. A user has to wait to get a response back from the server, for the web page to get refreshed, etc. Now coming down to our topic, normally what happens in most of the classical web application is that any user request sends a HTTP request to a web server. The server does some processing to give back the response and then returns an HTML page to the user. But every time the user submits the page to the server, the server responds with a totally new page which makes the application run slower and is less user-friendly. An Ajax application eliminates this disadvantage by incorporating a middle layer between the user and the server. That intermediate layer is the Ajax engine. Actually, what happens here is that instead of loading a web page, at the start of the session the browser loads the Ajax engine. The Ajax engine is written in JavaScript and is usually placed in a hidden frame. This engine is responsible for communicating with the server and the user; i.e. it acts as the link between the server and user machine. The Ajax engine allows asynchronous interaction of the user with the application, independent of communication with the server. So here, the user does not have to keep waiting for the response from the server. In Ajax every user action that would create any HTTP request takes the form of a JavaScript call to the Ajax engine. Any user request that can be fulfilled by the engine is done by the engine itself without bothering the server. But any user request which requires the server interference, the engine makes those requests asynchronously, usually using XML, without stopping the user interaction with the application. |
| How to make an HTTP request and handle the response? |
|
In order to make an HTTP request to the server using JavaScript, an instance of a class that provides this functionality is required. Such a class was originally introduced in Internet Explorer as an ActiveX object and is called XMLHTTP. Then Mozilla, Safari and other browsers also followed and started implementing an XMLHttpRequest class that supports the methods and properties supported by Microsoft’s original ActiveX object. Create a new XMLHttpRequestListing 1<script language=”javascript” type=”text/javascript”> var XMLHttp=new xmlHttpRequest();</script> This is the object which handles all the server communication. In normal Web applications the users fill out form fields and then click a Submit button. Then, the entire form is sent to the server which passes it on to some script. When the script has finished its work, it sends back a completely new page as the response. But during the time the script or program on the server is processing and returning a new form, users have to wait the entire time. But in the case of Ajax, it puts the Javascript technology and the XMLHttpRequest object between the user’s Web form and the server. When users fill out forms, the data, rather than being directly sent to the server, is sent to a Javascript code. Thus, here Javascript code catches the form data and sends a request to the server. In order to create a cross-browser instance (object) of the required class we need to write the code. Listing 2if (window.xmlHttpRequest){ // for Mozilla, Safari, etc. XMLHttp = new xmlHttpRequest();}elseif(window.ActiveXObject){ // for Internet Explorer XMLHttp = new ActiveXObject(“Microsoft.XMLHTTP”);} Microsoft’s browser (Internet Explorer) uses the MSXML parser for handling XML. MSXML has two different versions, so we need to write code that handles both cases. Thus the code goes as shown below. Listing 3var XMLHttp = false;try{ XMLHttp = new ActiveXObject(“Msxml2.XMLHTTP”);}catch (e){ try { XMLHttp = new ActiveXObject(“Microsoft.XMLHTTP”); }}if (XMLHttp == false){ XMLHttp = new XMLHttpRequest();} The above code tries to create the object in one version of MSXML. If XMLHttp still has its value as false, it tries to create the object in the standard way. Making a Request First of all, a JavaScript method is required, which the web page can call. Then we need to do the following. · Get the required data from the Web form· Build the URL for connecting· Open a connection to the server· Write a function that the server can run when it is done with its work· Send the request For this the required coding. Listing 4function getData() {// Get the data in the name field from the web formvar name = document.getElementById(“name”).value; // Proceed only if there is value for the fieldif ((name == null) || (name == “”)) return; // Build the URL to connect tovar url = “/scripts/getAnswer.php?name=” + escape(name); // Setup a function for the server to run when it’s doneXMLHttp.onreadystatechange = showSuggestion; // Open a connection to the serverXMLHttp.open(“GET”, url, true); // Send the requestXMLHttp.send(null);} The first part of the code is simple JavaScript used to catch the form data. Then the code sets a PHP script as the destination for connecting to. First, the URL is specified and then the value of the name field is appended to it. Then the onreadystatechange property has been used to inform the HTTP request object about the JavaScript function to run after getting the response from the server. This function handles further processing of the response sent by the server. To make the actual request, a person needs to call the open() and send() methods of the HTTP request class. The first parameter of the open() function is the request method which can be GET,POST,HEAD or any other method supported by the person’s browser. The second parameter is the URL of the page the person requests. The third parameter sets whether the request is asynchronous or not. If TRUE, the execution of the JavaScript function will continue while the response of the server has not yet arrived and if set to false, it will wait for the server response which destroys the very meaning of Ajax i.e. asynchronous data retrieval. Finally, send() is called with a value of null. Since the data to be sent to the server (the value of the name field) has already been added in the request URL, it is not required to send anything in the request. So this triggers the request and the server can do what it has been asked to do. Handling the Response First, the function needs to check for the state of the request. If the state has the value of 4, it means that the full server response has already been received and it is OK to continue with the processing. Listing 5if (XMLHttp.readyState == 4){// the response has been fully received}else{// still not ready with the response} The full list of the “readystate” values is shown below: · 0: The request is uninitialized (before open() is called).· 1: The request is set up, but has not been sent yet (before send() is called).· 2: The request was sent and is being processed at present.· 3: The request is being processed; often some partial data is available, but the server has not finished with the response.· 4: The response is complete and can be used. The next thing to check is the status code of the HTTP server response. Here it is required to check only the “200″ i.e. “OK” response. Listing 6if (XMLHttp .status == 200){// you can move on}else{// there was a problem with the request,// (example-the response may be a 404 (i.e. Not Found)} Now, after checking the state of the request and the HTTP status code of the response, a person needs to decide what to do with the server response. There are two options to access that data: · XMLHttp.responseText – will return the server response as a string of text · XMLHttp.responseXML – will return the response as an XMLDocument object The code is as shown: Listing 7function showSuggestion(){ if (XMLHttp.readyState == 4) { if (XMLHttp.status == 200) { var response = XMLHttp.responseText; document.getElementById(“Suggestion”).value = response; } else { alert(‘There was a problem with the request.’); } }} It waits for the server to call it with the proper ready state and status. Then it uses the data returned by the server (in this case, the suggestions for the data entered by the user) to set the value of another form field. The result is that the Suggestion field suddenly appears with the suggestions for the name field but without requiring the user to click a submit button. Finally, here goes the HTML form. In this form there is a text input for the user to enter data and another placeholder to show the suggestions. Listing 8<form><p>Name: <input type=”text” name=”name” id=”name” size=”25″ onChange=”getData();” /></p><p>Suggestion: <span id=”Suggestion”></span></p></form> Here, span is used as a placeholder for data retrieved from the web server. When a user writes in a new value for name field, the getData() method is called up and the user is able to view the suggestions without having to click a button. This is what makes Ajax grab the attention of the developers. |
| Advantages & Disadvantages of Using Ajax |
| Advantages
Everything has its own merits and demerits, Ajax included. · The application seems to become more responsive and interactive as the user gets the response without clicking any buttons.· In classic web application, when the web server responds to the web browser with a new page, it may make use of multiple connection threads in order to speed up the process, but this happens for the content only (which is between <body> tags). The CSS as well as the script files present in the head section are transferred using only one connection thread which results in performance degradation. With Ajax, it is required to load only the basic script and CSS files. Rests are requested as content using multiple connections.· A big advantage is that the user is not required to keep on waiting and waiting.· One more important merit is traffic to and from the server is reduced a considerable amount.· If a section of a page encounters any error, other sections do not get affected and the data entered by the user is also not lost.Disadvantages· Ajax application development may lead to increase in development time and cost.· The biggest concern is accessibility because all browsers do not completely support Javascript and xmlHttpRequest object. · Using Ajax to asynchronously load bits of data into the existing page conflicts the way the users are used to viewing, navigating and creating bookmarks in a modern browser.· Another disadvantage lies in the xmlHttpRequest object itself because one can use it to access information from the host that served the “initial page” (due to security reasons). |
| Where to Use & Where Not to Use Ajax? |
|
Here is a list of a few areas where one should and where one should not use Ajax. To be used when:· The application involves heavy server requests, with multiple web forms that submit data to the server.· It is required to display large amount of data in sequence without refreshing the page.· Application response time and loading time is a matter of concern.Not to be used when:· Using plain, static HTML pages· Users do not interact with the application often.· Loading time and bandwidth usage is not a matter of concern.· The application is needed to be viewed in older browsers or over various versions of the browser.· Time and cost constitute important factors for a project development. |
| Conclusion |
|
Finally, I would like to conclude that by using Ajax, Internet applications can be made more responsive, more interactive and more user friendly, but at the same time Ajax has its own demerits. So before using it, sit back and think over whether by using Ajax in your applications you are getting rid of your problems or you are running into some other deeper problems. By Debjani Mallick |
Sql Invalid Cache
November 6, 2007 at 4:44 am | In SQL Server | Leave a CommentIntroducing Cache Dependencies:
As time passes, the data source may change in response to other actions. However, if your code uses caching, you may remain unaware of the changes and continue using out-of-date information from the cache. To help mitigate this problem, ASP.NET supports cache dependencies. Cache dependencies allow you to make a cached item dependent on another resource so that when that resource changes the cached item is removed automatically. ASP.NET includes three types of dependencies:
- Dependencies on other cache items.
- Dependencies on files or folders.
- Dependencies on a database query.
Introducing SQL Cache Notifications:
SQL cache dependencies are one of the most wonderful new features in ASP.NET 2.0, the ability to automatically invalidate a cached data object (such as a DataSet or Custom Data Type) when the related data is modified in the database. This feature is supported in both SQL Server 2005 and in SQL Server 2000, although the underlying plumbing is quite different.
Cache Notifications in SQL Server 2000:
Before you can use SQL Server cache invalidation, you need to enable notifications for the database. This task is performed with the aspnet_regsql.exe command-line utility, which is located in the c:\[WinDir]\Microsoft.NET\Framework\[Version] directory. To enable notifications, you need to use the -ed command-line switch. You also need to identify the server (use -E for a trusted connection and -S to choose a server other than the current computer) and the database (use -d). Here’s an example that enables notifications for the Northwind database on the current server:
aspnet_regsql -ed -E -d Northwind
After executing this command, a new table named SqlCacheTablesForChangeNotification is added to the database Northwind. The SqlCacheTablesForChangeNotification table has three columns: tableName, notificationCreated, and changeId. This table is used to track changes. Essentially, when a change takes place, a record is written into this table. The SQL Server polling queries this table. Also a set of stored procedures is added to the database as well. See the following table.
| Procedure Name | Description |
| AspNet_SqlCacheRegisterTableStoredProcedure | Sets a table up to support notifications. This process works by adding a notification trigger to the table, which will fire when any row is inserted, deleted, or updated. |
| AspNet_SqlCacheUnRegisterTableStoredProcedure | Takes a registered table and removes the notification trigger so that notifications won’t be generated. |
| AspNet_SqlCacheUpdateChangeIdStoredProcedure | The notification trigger calls this stored procedure to update the AspNet_SqlCacheTablesForChangeNotification table, thereby indicating that the table has changed. |
| AspNet_SqlCacheQueryRegisteredTablesStoredProcedure | Extracts just the table names fromthe AspNet_SqlCacheTablesForChangeNotification table. Used to get a quick look at all the registered tables. |
| AspNet_SqlCachePollingStoredProcedure | Gets the list of changes from the AspNet_SqlCacheTablesForChangeNotification table. Used to perform the polling. |
After this, need to enable notification support for each individual table. You can do this manually using the AspNet_SqlCacheRegisterTableStoredProcedure, to that, open your query analyzer and select your Database you’ve enabled for SQL Cache Notification for example Northwind database and write the following command:
exec AspNet_SqlCacheRegisterTableStoredProcedure ‘TableName’
Or you can use aspnet_regsql, using the -et parameter to enable a able for sql cache dependency notifications and the -t parameter to name the table. Here’s an example that enables notifications for the Employees table:
aspnet_regsql -et -E -d Northwind -t Products
Both options generates the notification trigger for the Products table as the following:
CREATE TRIGGER dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON [Products]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N’Products’
ENDSo any record is inserted, deleted or updated in Products table will update ChangeId field in AspNet_SqlCacheTablesForChangeNotification table.
How Notificaions Works:
The AspNet_SqlCacheTablesForChangeNotification contains a single record for every table you’re monitoring. When you make a change in the table (such as inserting ,deleting or updating a record), the changeId column is incremented by 1 -see AspNet_SqlCacheUpdateChangeIdStoredProcedure procedure-. ASP.NET queries this table repeatedly and keeps track of the most recent changeId values for every table. When this value changes in a subsequent read, ASP.NET knows that the table has changed.
Displaying time in format hh:mm:ss
November 5, 2007 at 12:44 pm | In SQL Server | Leave a CommentDisplaying time in format hh:mm:ss by using Convert() function
Convert(varchar(8),InDateTime,108) as [In Time]
Addding tow numbers using Web Services
November 2, 2007 at 10:24 am | In Web services | Leave a Comment
Addding tow numbers using Web Services
Environment: .NET 1.0.3705, IIS 5.0
Introduction
Visual Studio .NET makes .NET programming simple and accelerates the development process. It hides a lot of repetitive and configuration details from the user and improves productivity. However, sometimes you would like to program for .NET without using VS .NET; for example, you want to learn .NET framework programming and do not have access to VS .NET or you want to know what is actually going on under the hood.Writing a Web Service in .NET using VS .NET very is easy. However, it is possible to write Web Services using the plain .NET SDK. I struggled a lot to write Web Services without VS .NET and tried to find help on the Net. There are a lot of examples available for writing Web Services using VS.NET, but you will rarely find any examples of writing Web Services using only the .NET SDK. This article is exactly for this purpose. It looks at Web Services development using the .NET SDK alone.We will write and publish a simple web Service. We will also write two Web Service consumers: one Web-based consumer (ASP.NET application) and another Windows application-based consumer. Let’s start writing our first Web Service.
Writing a Web Service
Following is our first Web Service; it exposes two methods (Add and SayHello) as Web Services to be used by applications. This is a standard template for a Web Service. .NET Web Services use the .asmx extension. Note that a method exposed as a Web Service has the WebMethod attribute. Save this file as FirstService.asmx in the IIS virtual directory (as explained in configuring IIS; for example, c:\MyWebSerces).
FirstService.asmx
<%@ WebService language="C" class="FirstService" %>
using System;
using System.Web.Services;
using System.Xml.Serialization;
[WebService(Namespace="http://localhost/MyWebServices/")]
public class FirstService : WebService
{
[WebMethod]
public int Add(int a, int b)
{
return a + b;
}
[WebMethod]
public String SayHello()
{
return "Hello World";
}
}
To test a Web Service, it must be published. A Web Service can be published either on an intranet or the Internet. We will publish this Web Service on IIS running on a local machine. Let’s start with configuring the IIS.
- Open Start->Settings->Control Panel->Administrative tools->Internet Services Manager.
- Expand and right-click on [Default Web Site]; select New ->Virtual Directory.
- The Virtual Directory Creation Wizard opens. Click Next.
- The “Virtual Directory Alias” screen opens. Type the virtual directory name—for example, MyWebServices—and click Next.
- The “Web Site Content Directory” screen opens. Here, enter the directory path name for the virtual directory—for example, c:\MyWebServices—and click Next.
- The “Access Permission” screen opens. Change the settings as per your requirements. Let’s keep the default settings for this exercise. Click the Next button. It completes the IIS configuration. Click Finish to complete the configuration.
To test that IIS has been configured properly, copy an HTML file (for example, x.html) in the virtual directory (C:\MyWebServices) created above. Now, open Internet Explorer and type http://localhost/MyWebServices/x.html. It should open the x.html file. If it does not work, try replacing localhost with the IP address of your machine. If it still does not work, check whether IIS is running; you may need to reconfigure IIS and Virtual Directory.To test our Web Service, copy FirstService.asmx in the IIS virtual directory created above (C:\MyWebServices). Open the Web Service in Internet Explorer (http://localhost/MyWebServices/FirstService.asmx). It should open your Web Service page. The page should have links to two methods exposed as Web Services by our application. Congratulations; you have written your first Web Service!!!
Testing the Web Service
As we have just seen, writing Web Services is easy in the .NET Framework. Writing Web Service consumers is also easy in the .NET framework; however, it is a bit more involved. As said earlier, we will write two types of service consumers, one Web- and another Windows application-based consumer. Let’s write our first Web Service consumer.
Web-Based Service Consumer
Write a Web-based consumer as given below. Call it WebApp.aspx. Note that it is an ASP.NET application. Save this in the virtual directory of the Web Service (c:\MyWebServices\WebApp.axpx).This application has two text fields that are used to get numbers from the user to be added. It has one button, Execute, that, when clicked, gets the Add and SayHello Web Services.
WebApp.axpx
<%@ Page Language="C#" %>
<script runat="server">
void runSrvice_Click(Object sender, EventArgs e)
{
FirstService mySvc = new FirstService();
Label1.Text = mySvc.SayHello();
Label2.Text = mySvc.Add(Int32.Parse(txtNum1.Text),
Int32.Parse(txtNum2.Text)).ToString();
}
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<p>
<em>First Number to Add </em>:
<asp:TextBox id="txtNum1" runat="server"
Width="43px">4</asp:TextBox>
</p>
<p>
<em>Second Number To Add </em>:
<asp:TextBox id="txtNum2" runat="server"
Width="44px">5</asp:TextBox>
</p>
<p>
<strong><u>Web Service Result -</u></strong>
</p>
<p>
<em>Hello world Service</em> :
<asp:Label id="Label1" runat="server"
Font-Underline="True">Label</asp:Label>
</p>
<p>
<em>Add Service</em> :
& <asp:Label id="Label2" runat="server"
Font-Underline="True">Label</asp:Label>
</p>
<p align="left">
<asp:Button id="runSrvice" onclick="runSrvice_Click"
runat="server" Text="Execute"></asp:Button>
</p>
</form>
</body>
</html>
After the consumer is created, we need to create a proxy for the Web Service to be consumed. This work is done automatically by Visual Studio .NET for us when referencing a Web Service that has been added. Here are the steps to be followed:
- Create a proxy for the Web Service to be consumed. The proxy is created using the wsdl utility supplied with the .NET SDK. This utility extracts information from the Web Service and creates a proxy. Thus, the proxy created is valid only for a particular Web Service. If you need to consume other Web Services, you need to create a proxy for this service as well. VS .NET creates a proxy automatically for you when the reference for the Web Service is added. Create a proxy for the Web Service using the wsdl utility supplied with the .NET SDK. It will create FirstSevice.cs in the current directory. We need to compile it to create FirstService.dll (proxy) for the Web Service.
· c:> WSDL http://localhost/MyWebServices/
· FirstService.asmx?WSDL
· c:> csc /t:library FirstService.cs
- Put the compiled proxy in the bin directory of the virtual directory of the Web Service (c:\MyWebServices\bin). IIS looks for the proxy in this directory.
- Create the service consumer, which we have already done. Note that I have instantiated an object of the Web Service proxy in the consumer. This proxy takes care of interacting with the service.
- Type the URL of the consumer in IE to test it (for example, http://localhost/MyWebServices/WebApp.aspx).
Now, the question arises: How can I be sure that my application is actually calling the Web Service? It is simple to test. Stop your Web server so that the Web Service cannot be contacted. Now, run the WinApp application. It will fire a run-time exception. Now, start the Web server again. It should work.
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.