.Net Help

SQL Server 2005 Locking Hints

2009-01-20T15:57:00.001+05:30

ROWLOCK
Use row-level locks when reading or modifying data.

PAGLOCK
Use page-level locks when reading or modifying data.

TABLOCK
Use a table lock when reading or modifying data.

DBLOCK
Use a database lock when reading or modifying data.

UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.

XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.

HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.

NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.

ASP.NET Client Side State Management

2008-11-06T12:29:00.001+05:30

http://dotnet.dzone.com/news/aspnet-server-side-state-manag

How to launch Window application from C# console application

2008-11-06T12:28:00.001+05:30

This article is targeted for mid-level programmers who have already worked on C# language. This article uses batch files for demonstration and the reader should have preliminary knowledge of batch files.

Sample application describes how to call any Windows application or batch file using a C# console application. This application launches a new process and executes Windows applications in that process.

API Used

System.Console.WriteLine()
System.Diagnostics.Process
Process.Start()

Step by Step process to create a sample application

1. Create a Console project called as InvokeBatchFile.

2. Include the following namespce

Using System.Diagnostics;

3. Add following code for the Main function

Process p = null;

try

{

string targetDir;

targetDir = string.Format(@"C:\ CallBatchFile ");

p = new Process();

p.StartInfo.WorkingDirectory = targetDir;

p.StartInfo.FileName = "MyBatch.bat";

p.StartInfo.Arguments = string.Format("C-Sharp Console application");

p.StartInfo.CreateNoWindow = false;

p.Start();

p.WaitForExit();

}

catch (Exception ex)

{

Console.WriteLine("Exception Occurred :{0},{1}",

ex.Message, ex.StackTrace.ToString());

}

C# Class Generator

2008-11-06T12:27:00.001+05:30

http://www.csharpfriends.com/demos/csharp_class_generator.aspx

Undocumented SQL Server 2000 functions

2008-08-29T12:30:00.001+05:30

Encryption in SQL server [pwdencrypt,pwdcompare]

There are some hidden functions in SQL server through which we can encrypt any string and store the same in the table. This will be very helpful in encrypting the user password and other sensitive user data. Encryption supported by SQL server is one way hash. One way hash is nothing but the string encrypted cannot be decrypted. The only way is to compare values with encrypted string.

DECLARE @ClearPIN varchar (255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'test'
SELECT @EncryptedPIN = CONVERT (varbinary (255), pwdencrypt (@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

In the above example @EncryptedPIN will store the cipher Text. The data in this string is not the encrypted string instead it will return the hash code of the supplied plain string.

2008-06-02T19:04:00.000+05:30

Now let's get into details. First of all say in our aspx page we need to save the image to the server at the runtime. For this we need to use the stream class to read the file as a byte and then save it to the required path at a particular event. The event can vary from situation to situation. e.g in my project I did this in the page_load event. Some would definitely like to avail this just before producing the report.

Code for storing your Image in to the server

Listing 1

System.Drawing.Image drawingImage;

drawingImage = System.Drawing.Image.FromStream(new System.IO.MemoryStream (byteArr));

string strPath;

strPath = System.Web.HttpContext.Current.Request.MapPath("path");

drawingImage.Save(strPath, System.Drawing.Imaging.ImageFormat.Bmp);

After saving the image now its turn for designing the report. For this we need a dataset(xsd) which will store the Image.

For this add a dataset to your project. Create a datatable inside your dataset and add a column to the datatable for storing the image.One thing to note here is the datatype for the column. It should be System.Byte[].

Figure 1

After Creating the DataSet we can start on designing the report. Open the Crystal report, add the DataTable to the database fields of your report and then drag and drop the image field to the report.

Now for populating the datatable with the data at the runtime we will have a function inside a class that takes one paramater i.e the Image that needs to be displayed and using the data row we will add it to the datatable and finally return the datatable.

Now for displaying it actually in the report, set the datasource of the crystal report with the data table. The below code demonstrates this.

Code for setting the datasource of the Crystal Report

Listing 2

ReportDocument rptTest1 = new ReportDocument();

rptTest1.Load(System.Web.HttpContext.Current.Request.MapPath("App_Reports/rptpic1.rpt"));

rptTest1.Database.Tables["Images"].SetDataSource(rptTest.ImageTable(System.Web.HttpContext.

Current.Request.MapPath("App_Data/test1.bmp")));

Code for filling the data table with the Image

Listing 3

public class rptTest

{

public static DataTable ImageTable(string ImageFile)

{

DataTable data = newDataTable();

DataRow row;

data.TableName = "Images";

data.Columns.Add("img", System.Type.GetType("System.Byte[]"));

FileStream fs = new FileStream(ImageFile, FileMode.Open);

BinaryReader br = new BinaryReader(fs);

row = data.NewRow();

row[0] = br.ReadBytes(br.BaseStream.Length);

data.Rows.Add(row);

br = null;

fs.Close();

fs = null;

return data;

}

When the program runs, the image gets added up to the data table and then is pulled up by the crystal report without any database overhead.

Remove Spaces From String

2008-02-20T10:05:00.001+05:30

using System.Text.RegularExpressions;

private string RemoveSpaces(string str)
{
string result = "";
Regex regulEx = new Regex(@"[\s]+");
result = regulEx.Replace(str," ");
return result;
}

Understanding XSD Schema

2008-02-07T17:15:00.002+05:30

Introduction

Those who deal with data transfer or document exchange within or across organizations with heterogeneous platforms will certainly accept and appreciate the need and power of XML. I am not going to delve into the merits of XML. I will, however, address a simple but powerful schema concept called XSD or XML Schema Definition.

What is XSD Schema?
What are the advantages of XSD Schema?
What is important in XSD Schema?

What Is a Schema?

A schema is a "Structure", and the actual document or data that is represented through the schema is called "Document Instance". Those who are familiar with relational databases can map a schema to a Table Structure and a Document Instance to a record in a Table. And those who are familiar with object-oriented technology can map a schema to a Class Definition and map a Document Instance to an Object Instance.

A structure of an XML document can be defined as follows:

Document Type Definition (DTDs)
XML Schema Definition (XSD)
XML Data Reduced (XDR) -proprietary to Microsoft Technology

We are specifically going to work with XML Schema Definitions (XSD).

What Is XSD?

XSD provides the syntax and defines a way in which elements and attributes can be represented in a XML document. It also advocates that the given XML document should be of a specific format and specific data type.

XSD is fully recommended by W3C consortium as a standard for defining an XML Document. To know more about latest information on XSD, please refer the W3C site(http://www.w3.org/).

Advantages of XSD

So what is the benefit of this XSD Schema?

XSD Schema is an XML document so there is no real need to learn any new syntax, unlike DTDs.
XSD Schema supports Inheritance, where one schema can inherit from another schema. This is a great feature because it provides the opportunity for re-usability.
XSD schema provides the ability to define own data type from the existing data type.
XSD schema provides the ability to specify data types for both elements and attributes.

For more information Click Here

Different Options for Importing Data into SQL Server

2008-01-25T11:06:00.001+05:30

In addition to using the Import / Export wizards and/or DTS or SSIS to move data into SQL Server there are also a few other options for doing this that are built into SQL Server. Some these other options include bcp, BULK INSERT, OPENROWSET as well as others. The following examples show you some of these different options for importing data and how you can use some of these inline with your T-SQL code as well as others that can be run from the command line.

BCP
This is one of the options that is mostly widely used. One reason for this is that it has been around for awhile, so DBAs have come quite familiar with this command. This command allows you to both import and export data, but is primarily used for text data formats. In addition, this command is generally run from a Windows command prompt, but could also be called from a stored procedure by using xp_cmdshell or called from a DTS or SSIS package.

Here is a simple command for importing data from file C:\ImportData.txt into table dbo.ImportTest.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

BULK INSERT
This command is a T-SQL command that allows you to import data directly from within SQL Server by using T-SQL. This command imports data from file C:\ImportData.txt into table dbo.ImportTest.

BULK INSERT dbo.ImportTest
FROM 'C:\ImportData.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

OPENROWSET
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. By using this command along with an INSERT INTO command we can load data from the specified data source into a SQL Server table.

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTest.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Here is another example where data is pulled from worksheet [Sheet1$] by using a SELECT * FROM command. Again, by using the INSERT INTO command you can insert the query results into table dbo.ImportTest. The query can be any valid SQL query, so you can filter the columns and rows by using this option.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

OPENDATASOURCE
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. This is similar to the OPENROWSET command.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

OPENQUERY
Another option is OPENQUERY. This is another command that allows you to issue a T-SQL command to select data and again with the INSERT INTO option we can load data into our table. There are two steps with this process, first a linked server is setup and then second the query is issued using the OPENQUERY command. This option allow you to filter the columns and rows by the query that is issued against your linked data source.

EXEC sp_addlinkedserver 'ImportData',
   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
   'C:\ImportData.xls',
   NULL,
   'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

Linked Server
Here is yet another option with setting up a linked server and then issuing a straight SQL statement against the linked server. This again has two steps, first the linked server is setup and secondly a SQL command is issued against the linked data source.

EXEC sp_addlinkedserver 'ImportData',
   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
   'C:\ImportData.xls',
   NULL,
   'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT * FROM ImportData...Sheet1$

Export data from SQL Server to Excel

2008-01-25T10:47:00.001+05:30

     Exporting data from SQL Server to Excel can be achieved in a variety of ways. Some of these options include Data Transformation Services (DTS), SQL Server Integration Services (SSIS) and Bulk Copy (BCP). Data Transformation Services (SQL Server 2000) and SQL Server Integration Services (SQL Server 2005) offers a GUI where widgets can be dragged and dropped Each option has advantages and disadvantages, but all can do the job. It is just a matter of your comfort level with the tools and the best solution to meet the need.
     Another option that is available directly via the T-SQL language is the OPENROWSET command (SQL Server 2000 and SQL Server 2005). This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL..
     Below is a simple example of writing out the Job name and date to Sheet1 of an Excel spreadsheet in either SQL Server 2005 or 2000.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO

Dynamic SQL

2008-01-24T19:00:00.001+05:30

SQL Server offers a few ways of running a dynamically built SQL statement. These ways are:

Writing a query with parameters

Using EXEC

Using sp_executesql

For more information see: http://www.mssqltips.com/tip.asp?tip=1160

Factory Class for SQL and OLEDB Data Providers

2008-01-24T16:11:00.001+05:30

Introduction

While developing a real life software project you need to take in to account many things. One of these things is the choice of database. Most of the times your client will have predefined database (e.g. SQL Server or Oracle). However, some times situation arises such that your database is not fixed at development time. For example if you are developing a product then you will not be knowing the database of your prospective clients in advance. Similarly, if your client demands for database independence (may be because he is migrating his database) then also the database is not fixed. The common solution can be to develop two different sets of data access components - one for SQL server and another for Oracle. However, this will increate your maintenance and version tracking. .NET provides an easy way to tackle this problem. All the data access classes i.e. classes from System.Data.SQLClient and System.Data.OleDb namespaces actually implement certain interfaces from System.Data namespace. In order to make your data access layer components independent of any database you can code against these interfaces rather than actual classes. In this article we will develop a factory class that returns objects from appropriate namespace based on the data provider type you supply. We will also see how to code your data access layer against these interfaces.

System.Data Interfaces

All the .NET providers ( SQL and OleDB) implement certain set of interfaces found in System.Data namespace. Following is the list of some of these interfaces:

IDbConnection
IDbCommand
IDbDataAdapter
IDataParameter

You will easily be able to identify the mapping between the interfaces and actual classes.

Developing a factory class

We will develop a factory class that will simply return you objects of specified data provider. For example if you specify that your data provider is SQL server, you will be returned a SQLConnection object where as if you specify that your data provider is any OLEDB compatible database then a OleDbConnection will be returned. Following is the complete code for the class.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace DALFactory
{
public class DALObjects
{

public static IDbConnection
GetConnection(DataProviderType type) 
{
if(type==DataProviderType.SQL)
{
		return new SqlConnection();
	}
	else
	{
		return new OleDbConnection();
	}
}

public static IDbCommand
GetCommand(DataProviderType type)
{
 	if(type==DataProviderType.SQL)
	{
		return new SqlCommand();
	}
	else
	{
		return new OleDbCommand();
	}
}

public static IDbDataAdapter
GetDataAdapter(DataProviderType type)
{
	if(type== DataProviderType.SQL)
	{
		return new SqlDataAdapter();
	}
	else
	{
		return new OleDbDataAdapter();
	}
}


public static IDataParameter
GetParameter(DataProviderType type)
{
	if(type== DataProviderType.SQL)
	{
		return new SqlParameter();
	}
	else
	{
		return new OleDbParameter();
	}
}
}

public enum DataProviderType
{
	SQL=0,OLEDB=1
}
}

As you will notice we are returning objects that can be instantiated on their own. That is why we have not included DataReader here. Note that we have created all the methods as static. This way we can easily make use of our methods without creating class instance. Also, note that we will be using DataProviderType enum in our code that indicates which namespaces to use. Now that your have your factory class ready let us see how to use it.

Coding against System.Data interfaces

As mentioned earlier in this section we will see how to use the factory class in our code. Typically when you develop a data access class your code looks like this:

OleDbConnection cnn;
OleDbDataAdapter cmd;
OleDbDataReader dr;

cnn = New OleDbConnection(connectionstring);
cmd = New OleDbDataAdapter(sqlquery,cnn);

dr=cmd.ExecuteReader();

Now, since we need to make our class database independent we will not explicitly declare objects from OleDb or SqlClient namespaces. Instead we will code as follows:

IDbConnection cnn=
DALObjects.GetConnection(DataProviderType.OLEDB);
cnn.Open();
IDbCommand cmd=
DALObjects.GetCommand(DataProviderType.OLEDB);
cmd.ConnectionString="some_sql";
cmd.Connection=cnn ;
IDataReader dr=cmd.ExecuteReader();

Here, we have avoided using explicit classes of specific data provider. Note that since you are using interfaces here you will not be able to invoke methods and properties specific to a particular provider. For example, if you want to use IDbTransaction interface then you can not call Save() method which is specific to SQL Server. However, what we achieve is database independence. Now, in above example the data provider type parameter can be stored in some external XML file that can be easily changed after installation at client's place.

What is CRM?

2008-01-10T21:27:00.000+05:30

CRM stands for Customer Relationship Management. It is a process or methodology used to learn more about customers' needs and behaviors in order to develop stronger relationships with them. There are many technological components to CRM, but thinking about CRM in primarily technological terms is a mistake. The more useful way to think about CRM is as a process that will help bring together lots of pieces of information about customers, sales, marketing effectiveness, responsiveness and market trends.

CRM helps businesses use technology and human resources to gain insight into the behavior of customers and the value of those customers.

Using CRM, a business can:

Provide better customer service
Increase customer revenues
Discover new customers
Cross sell/Up Sell products more effectively
Help sales staff close deals faster
Make call centers more efficient
Simplify marketing and sales processes

How To: Compress ViewState in ASP.NET 2.0

2007-12-22T10:45:00.001+05:30

ViewState Compression with System.IO.Compression

ViewState is the built-in structure for automatically retaining values between multiple requests for the same page in ASP.NET. In other words, ViewState technology saves/restores page state between postbacks. On the other hand, this technology comes with an overhead that affects performance especially during page load since the state data is maintained in a hidden field.

Reducing ViewState Size

We can completely disable viewstate by setting EnableViewState to false in the page directive but you need extra programming effort for you to take care of the page state. It is a good idea to disable ViewState for the controls that do not actually need it such as Literals and Labels by setting EnableViewState to false. But this do not entirely solve the problem.

Compressing ViewState

ASP.NET 2.0 comes with the System.IO.Compression namespace, which contains classes with functionality to compress/decompress streams. In ASP.NET 1.1, developers must use third party compression tools such as ICSharpCode.SharpZipLib to compress viewstate.

Compressing/Decompressing using GZipStream

The following class contains two methods for compressing and decompressing a stream.

using System;
using System.Data;
using System.Configuration;
using System.IO;
using System.IO.Compression;

public static class CompressViewState
{
    public static byte[] Compress(byte[] data)
    {
        MemoryStream output = new MemoryStream();
        GZipStream gzip = new GZipStream(output,
                          CompressionMode.Compress, true);
        gzip.Write(data, 0, data.Length);
        gzip.Close();
        return output.ToArray();
    }

    public static byte[] Decompress(byte[] data)
    {
        MemoryStream input = new MemoryStream();
        input.Write(data, 0, data.Length);
        input.Position = 0;
        GZipStream gzip = new GZipStream(input,
                          CompressionMode.Decompress, true);
        MemoryStream output = new MemoryStream();
        byte[] buff = new byte[64];
        int read = -1;
        read = gzip.Read(buff, 0, buff.Length);
        while (read > 0)
        {
            output.Write(buff, 0, read);
            read = gzip.Read(buff, 0, buff.Length);
        }
        gzip.Close();
        return output.ToArray();
    }
}

You need to save this class in a .cs file in the App_Code directory.

Utilizing the CompressViewState Class

In order to compress the ViewState of a web page, you have to override the two methods LoadPageStateFromPersistenceMedium and SavePageStateToPersistenceMedium.

The folowing code creates a BasePage class which inherits from System.Web.UI.Page, and web pages using the following Base Page class as the base class utilizes ViewState compression. The BasePage class adds an additional hidden field __COMPRESSEDVIEWSTATE, to store the compressed ViewState.

using System;
using System.IO;
using System.IO.Compression;
using System.Collections;
using System.ComponentModel;
using System.Web.UI;
using System.Configuration;
using System.Threading;
using System.Globalization;
using System.Text;

public abstract class BasePage : System.Web.UI.Page
{
    private ObjectStateFormatter _formatter =
        new ObjectStateFormatter();

    protected override void
        SavePageStateToPersistenceMedium(object viewState)
    {
        MemoryStream ms = new MemoryStream();
        _formatter.Serialize(ms, viewState);
        byte[] viewStateArray = ms.ToArray();
        ClientScript.RegisterHiddenField("__COMPRESSEDVIEWSTATE",
            Convert.ToBase64String(
            CompressViewState.Compress(viewStateArray)));
    }
    protected override object
        LoadPageStateFromPersistenceMedium()
    {
        string vsString = Request.Form["__COMPRESSEDVIEWSTATE"];
        byte[] bytes = Convert.FromBase64String(vsString);
        bytes = CompressViewState.Decompress (bytes);
        return _formatter.Deserialize(
            Convert.ToBase64String(bytes));
    }
}

Show and Hide ModalPopupExtender from JavaScript

2007-12-21T20:18:00.001+05:30

Assign a BehaviourID to the ModalPopupExtender using the BehaviourID attribute.

BehaviorID ="ModalBehaviour1"

Use the $find method to get a handle to the Modal Popup Behaviour .

$find ("ModalBehaviour1").

Call your hide and show methods on the acquired handle.

The Javascript would look like this.

    function ShowModalPopup()
 {
     $find("ModalBehaviour1").show();
 }
 function HideModalPopup()
 {
     $find("ModalBehaviour1").hide();
 }

</script >

Message Box in ASP.NET

2007-11-19T10:23:00.000+05:30

public void ShowMessageBox(string message)
{
Page currentPage = (Page)HttpContext.Current.Handler;
if (currentPage != null)
{
ScriptManager manager = ScriptManager.GetCurrent(currentPage);

string _msg = "alert('" + message + "');";
if (manager != null && manager.IsInAsyncPostBack)
{
ScriptManager.RegisterClientScriptBlock(currentPage, currentPage.GetType(), "msg", _msg, true);
}
else
{
currentPage.ClientScript.RegisterStartupScript(currentPage.GetType(), "msg", _msg, true);
}
}
}

Sending Emails with Dynamic Content

2007-10-27T17:03:00.001+05:30

I want the message of the email to like

Hi <yourfriendname>, Thank you for sending this email. See you soon. Good Bye, <yourname> <dateyousendthisemail>

Code :

try {
StreamReader sr=new StreamReader("MyContent.htm");
sr = File.OpenText("MyContent.htm");
string result = sr.ReadToEnd(); sr.Close();
}
catch(Exception ex) {
Response.Write(ex.Message); return;
}
string MessageBody=result.Replace("<YourFriendName>", txtFriendName.Text); MessageBody=MessageBody.Replace("<YourName>", txtMyName.Text); MessageBody= MessageBody.Replace("<DateYouSendThisEmail>",
DateTime.Today.ToString());
MailMessage mail = new MailMessage();
mail.Body = MessageBody;
mail.BodyFormat = MailFormat.Html;
mail.From = "YOUR EMAIL ID";
mail.To = "YOUR FRIEND'S EMAIL ID";
mail.Subject = "Dynamic Content Email From "+ txtMyName.Text; SmtpMail.SmtpServer = "your email server"; SmtpMail.Send(mail);

How to use AJAX

2007-10-25T16:22:00.001+05:30

AJAX

AJAX stands for Asynchronous JavaScript And XML.

AJAX is a type of programming made popular in 2005 by Google (with Google Suggest).

· AJAX is not a new programming language, but a new way to use existing standards.

· With AJAX you can create better, faster, and more user-friendly web applications.

· AJAX is based on JavaScript and HTTP requests.

· With AJAX, your JavaScript communicates directly with the server, through the JavaScript XMLHttpRequest object

e.g

<script type="text/javascript">

function ajaxFunction() {

var xmlHttp;

try

// Firefox, Opera 8.0+, Safari

xmlHttp=new XMLHttpRequest();

}

catch (e) { // Internet Explorer

try {

xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");

} catch (e) {

try {

xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");

} catch (e) {

alert("Your browser does not support AJAX!");

return false;

}

</script>

Note:

· nternet Explorer uses an ActiveXObject, while other browsers uses the built-in JavaScript object called XMLHttpRequest.

· create the object with XMLHttp=new XMLHttpRequest(). This is for the Firefox, Opera, and Safari browsers.

If that fails, try xmlHttp=new ActiveXObject("Msxml2.XMLHTTP") which is for Internet Explorer 6.0+, if that also fails, try xmlHttp=new ActiveXObject("Microsoft.XMLHTTP") which is for Internet Explorer 5.5+

The onreadystatechange Property

After a request to the server, we need a function that can receive the data that is returned by the server.

The onreadystatechange property stores the function that will process the response from a server. The following code defines an empty function and sets the onreadystatechange property at the same time:

xmlHttp.onreadystatechange=function() {

// We are going to write some code here

}

The readyState Property

The readyState property holds the status of the server's response. Each time the readyState changes, the onreadystatechange function will be executed.

Here are the possible values for the readyState property:

State Description

0 The request is not initialized

1 The request has been set up

2 The request has been sent

3 The request is in process

4 The request is complete

We are going to add an If statement to the onreadystatechange function to test if our response is complete (this means that we can get our data):

xmlHttp.onreadystatechange=function() {

if(xmlHttp.readyState==4) {

// Get the data from the server's response

}

The responseText Property

The data sent back from the server can be retrieved with the responseText property.

In our code, we will set the value of our "time" input field equal to responseText:

xmlHttp.onreadystatechange=function() {

if(xmlHttp.readyState==4) {

document.myForm.time.value=xmlHttp.responseText;

}

AJAX - Sending a Request to the Server

To send off a request to the server, we use the open() method and the send() method.

The open() method takes three arguments. The first argument defines which method to use when sending the request (GET or POST). The second argument specifies the URL of the server-side script. The third argument specifies that the request should be handled asynchronously. The send() method sends the request off to the server. If we assume that the HTML and ASP file are in the same directory, the code would be:

xmlHttp.open("GET","time.asp",true);

xmlHttp.send(null);

Now we must decide when the AJAX function should be executed. We will let the function run "behind the scenes" when the user types something in the username text field:

<form name="myForm">

Name: <input type="text"

onkeyup="ajaxFunction();" name="username" />

Time: <input type="text" name="time" />

</form>

Our updated AJAX-ready "testAjax.htm" file now looks like this:

<html>

<body>

<script type="text/javascript">

function ajaxFunction() {

var xmlHttp;

try {

// Firefox, Opera 8.0+, Safari

xmlHttp=new XMLHttpRequest();

} catch (e) { // Internet Explorer

try {

xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");

} catch (e) {

try {

xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");

} catch (e) {

alert("Your browser does not support AJAX!");

return false;

}

xmlHttp.onreadystatechange=function() {

if(xmlHttp.readyState==4) {

document.myForm.time.value=xmlHttp.responseText;

}

xmlHttp.open("GET","time.asp",true);

xmlHttp.send(null);

}

</script>

<form name="myForm">

Name: <input type="text"

onkeyup="ajaxFunction();" name="username" />

Time: <input type="text" name="time" />

</form>

</body>

</html>

AJAX - The Server-Side ASP Script

Now we are going to create the script that displays the current server time.

The responseText property (explained in the previous chapter) will store the data returned from the server. Here we want to send back the current time. The code in "time.asp" looks like this:

response.expires=-1

response.write(time)

Note: The Expires property sets how long (in minutes) a page will be cached on a browser before it expires. If a user returns to the same page before it expires, the cached version is displayed. Response.Expires=-1 indicates that the page will never be cached.

Example Explained - The HTML Form

The form above has the following HTML code:

<form>

First Name:

<input type="text" id="txt1"

onkeyup="showHint(this.value)">

</form>

<p>Suggestions: <span id="txtHint"></span></p>

As you can see it is just a simple HTML form with an input field called "txt1".

An event attribute for the input field defines a function to be triggered by the onkeyup event.

The paragraph below the form contains a span called "txtHint". The span is used as a placeholder for data retrieved from the web server.

When the user inputs data, a function called "showHint()" is executed. The execution of the function is triggered by the "onkeyup" event. In other words: Each time the user moves his finger away from a keyboard key inside the input field, the function showHint is called.

Example Explained - The showHint() Function

The showHint() function is a very simple JavaScript function placed in the <head> section of the HTML page.

The function contains the following code:

function showHint(str) {

if (str.length==0) {

document.getElementById("txtHint").innerHTML="";

return;

}

xmlHttp=GetXmlHttpObject()

if (xmlHttp==null) {

alert ("Your browser does not support AJAX!");

return;

}

var url="gethint.asp";

url=url+"?q="+str;

url=url+"&sid="+Math.random();

xmlHttp.onreadystatechange=stateChanged;

xmlHttp.open("GET",url,true);

xmlHttp.send(null);

}

The function executes every time a character is entered in the input field.

If there is some input in the text field (str.length > 0) the function executes the following:

* Defines the url (filename) to send to the server

* Adds a parameter (q) to the url with the content of the input field

* Adds a random number to prevent the server from using a cached file

* Creates an XMLHTTP object, and tells the object to execute a function called stateChanged when a change is triggered

* Opens the XMLHTTP object with the given url.

* Sends an HTTP request to the server

If the input field is empty, the function simply clears the content of the txtHint placeholder.

Example Explained - The GetXmlHttpObject() Function

The example above calls a function called GetXmlHttpObject().

The purpose of the function is to solve the problem of creating different XMLHTTP objects for different browsers.

The function is listed below:

function GetXmlHttpObject(){

var xmlHttp=null;

try {

// Firefox, Opera 8.0+, Safari

xmlHttp=new XMLHttpRequest();

} catch (e) { // Internet Explorer

try {

xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");

} catch (e) {

xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");

}

return xmlHttp;

}

Example Explained - The stateChanged() Function

The stateChanged() function contains the following code:

function stateChanged(){

if (xmlHttp.readyState==4) {

document.getElementById("txtHint").innerHTML=xmlHttp.responseText;

}

The stateChanged() function executes every time the state of the XMLHTTP object changes.

AJAX ResponseXML Example

While responseText returns the HTTP response as a string, responseXML returns the response as XML.

The ResponseXML property returns an XML document object, which can be examined and parsed using W3C DOM node tree methods and properties.

AJAX Example Explained

The example above contains an HTML form, several <span> elements to hold the returned data, and a link to a JavaScript:

<html>

<head>

<script src="selectcustomer_xml.js"></script>

</head>

<body>

<form action="">

Select a Customer:

<select name="customers" onchange="showCustomer(this.value)">

<option value="ALFKI">Alfreds Futterkiste</option>

<option value="NORTS ">North/South</option>

<option value="WOLZA">Wolski Zajazd</option>

</select>

</form>

<b><span id="companyname"></span></b><br />

<span id="contactname"></span><br />

<span id="address"></span>

<span id="city"></span><br/>

<span id="country"></span>

</body></html>

The example above contains an HTML form with a drop down box called "customers".

When the user selects a customer in the dropdown box, a function called "showCustomer()" is executed. The execution of the function is triggered by the "onchange" event. In other words: Each time the user change the value in the drop down box, the function showCustomer() is called.

The AJAX JavaScript

This is the JavaScript code stored in the file "selectcustomer_xml.js":

var xmlHttp

function showCustomer(str) {

xmlHttp=GetXmlHttpObject();

if (xmlHttp==null) {

alert ("Your browser does not support AJAX!");

return;

}

var url="getcustomer_xml.asp";

url=url+"?q="+str;

url=url+"&sid="+Math.random();

xmlHttp.onreadystatechange=stateChanged;

xmlHttp.open("GET",url,true);

xmlHttp.send(null);

}

function stateChanged(){

if (xmlHttp.readyState==4) {

var xmlDoc=xmlHttp.responseXML.documentElement;

document.getElementById("companyname").innerHTML=

xmlDoc.getElementsByTagName("compname")[0].childNodes[0].nodeValue;

document.getElementById("contactname").innerHTML=

xmlDoc.getElementsByTagName("contname")[0].childNodes[0].nodeValue;

document.getElementById("address").innerHTML=

xmlDoc.getElementsByTagName("address")[0].childNodes[0].nodeValue;

document.getElementById("city").innerHTML=

xmlDoc.getElementsByTagName("city")[0].childNodes[0].nodeValue;

document.getElementById("country").innerHTML=

xmlDoc.getElementsByTagName("country")[0].childNodes[0].nodeValue;

}

function GetXmlHttpObject(){

var xmlHttp=null;

try { // Firefox, Opera 8.0+, Safari

xmlHttp=new XMLHttpRequest();

}

catch (e) { // Internet Explorer

try {

xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");

} catch (e) {

xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");

}

return xmlHttp;

}

The showCustomer() and GetXmlHttpObject() functions above are the same as in previous chapters. The stateChanged() function is also used earlier in this tutorial, however; this time we return the result as an XML document (with responseXML) and uses the DOM to extract the values we want to be displayed.

The AJAX Server Page

The server page called by the JavaScript, is a simple ASP file called "getcustomer_xml.asp".

The page is written in VBScript for an Internet Information Server (IIS). It could easily be rewritten in PHP, or some other server language. Look at a corresponding example in PHP.

The code runs an SQL query against a database and returns the result as an XML document:

response.expires=-1

response.contenttype="text/xml"

sql="SELECT * FROM CUSTOMERS "

sql=sql & " WHERE CUSTOMERID='" & request.querystring("q") & "'"

on error resume next

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open(Server.Mappath("/db/northwind.mdb"))

set rs=Server.CreateObject("ADODB.recordset")

rs.Open sql, conn

if err <> 0 then

response.write(err.description)

set rs=nothing

set conn=nothing

else

response.write("<?xml version='1.0' encoding='ISO-8859-1'?>")

response.write("<company>")

response.write("<compname>" &rs.fields("companyname")& "</compname>")

response.write("<contname>" &rs.fields("contactname")& "</contname>")

response.write("<address>" &rs.fields("address")& "</address>")

response.write("<city>" &rs.fields("city")& "</city>")

response.write("<country>" &rs.fields("country")& "</country>")

response.write("</company>")

end if

on error goto 0

Notice the second line in the ASP code above: response.contenttype="text/xml". The ContentType property sets the HTTP content type for the response object. The default value for this property is "text/html". This time we want the content type to be XML.

What is Business Intelligence?

2007-08-21T18:02:00.000+05:30

Business intelligence (BI) is a business management term, which refers to applications and technologies that are used to gather, provide access to, and analyze data and information about company operations. Business intelligence systems can help companies have a more comprehensive knowledge of the factors affecting their business, such as metrics on sales, production, internal operations, and they can help companies to make better business decisions

Sql Sever 2005 Overview

2007-08-21T17:55:00.000+05:30

Database Engine

The Database Engine is the core service for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise.

Use the Database Engine to create relational databases for online transaction processing or online analytical processing data. This includes creating tables for storing data, and database objects such as indexes, views, and stored procedures for viewing, managing, and securing data. You can use SQL Server Management Studio to manage the database objects, and SQL Server Profiler for capturing server events.

Analysis Services

Analysis Services is the core service for supporting rapid analysis of business data, delivering online analytical processing (OLAP) and data mining functionality in business intelligence applications.

OLAP
Analysis Services allows you to design, create, and manage multidimensional structures that contain detail and aggregated data from multiple data sources, such as relational databases, in a single unified logical model supported by built-in calculations. Analysis Services provides fast, intuitive, top-down analysis of large quantities of data built on this unified data model, which can be delivered to users in multiple languages and currencies. Analysis Services works with data warehouses, data marts, production databases and operational data stores, supporting analysis of both historical and real time data.

Data Mining
Analysis Services contains the features and tools you need to create complex data mining solutions.

A set of industry-standard data mining algorithms.

The Data Mining Designer, which you can use to create, manage, explore, and create predictions from mining models.

The DMX language, which you can use to manage mining models and to create complex prediction queries.

You can use a combination of these features and tools to discover trends and patterns that exist in your data, and then use the trends and patterns to make intelligent decisions about difficult business problems.

Integration Services

SQL Server 2005 Integration Services (SSIS) is the extract, transform, and load (ETL) component of SQL Server 2005. It replaces the earlier SQL Server ETL component, Data Transformation Services (DTS).

Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

Replication

Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet. SQL Server provides three types of replication, each with different capabilities: transactional replication, merge replication, and snapshot replication.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.

Reporting Services

SQL Server 2005 Reporting Services (SSRS) is a server-based reporting platform that provides comprehensive data reporting from relational and multidimensional data sources. Reporting Services includes processing components, a complete set of tools that you can use to create and manage reports, and an application programming interface (API) that allows developers to integrate or extend data and report processing in custom applications. The reports that you build can be based on relational or multidimensional data from SQL Server, Analysis Services, Oracle, or any Microsoft .NET Framework data provider, such as ODBC or OLE DB.

With Reporting Services, you can create interactive, tabular, or free-form reports that retrieve data at scheduled intervals or on-demand when the user opens a report. Reporting Services also enables users to create ad hoc reports based on predefined models, and to interactively explore data within the model. All reports can be rendered in both desktop and Web-oriented formats. You can choose from a variety of viewing formats to render reports on demand in preferred formats for data manipulation or printing.

Reporting Services is a server-based solution, and thus provides a way to centralize report storage and management, provide secure access to reports, models, and folders, control how reports are processed and distributed, and standardize how reports are used in your business.

Notification Services

SQL Server 2005 Notification Services is a platform for developing applications that generate and send notifications, and it is also an engine that runs those applications. You can use Notification Services to generate and send timely, personalized messages to thousands or even millions of subscribers, and deliver the messages to a wide variety of applications and devices.

The Notification Services platform enables the development of rich notification applications. Subscriptions, which express subscribers' interest in specific information (called events), can be evaluated based on the arrival of events or based on a schedule. The event data itself can originate from within the database, from other databases, or from external sources. Notifications, which result from the matching of events and subscriptions, can be richly formatted before being sent to the subscriber.

The Notification Services engine works in concert with the SQL Server Database Engine. The Database Engine stores the application data and performs the matching between events and subscriptions. The Notification Services engine controls the flow and processing of data, and can be scaled-out across multiple computers. This can improve the performance of very large and demanding applications.

Full-Text Search

SQL Server contains the functionality you need to issue full-text queries against plain character-based data in SQL Server tables. Full-text queries could include words and phrases or multiple forms of a word or phrase. Full-Text Search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In SQL Server 2005, Full-Text Search delivers enterprise-level search functionality.

Use Full-Text Search to search for plain, character-based data, in multiple fields in multiple tables at the same time. The performance benefit of using Full-Text Search can be best realized when querying against a large amount of unstructured text data. For example, a Transact-SQL LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query may take only seconds or less against the same data, depending on the number of rows that are returned. You can build full-text indexes on data stored in a char, varchar or nvarchar column or formatted binary data, such as Microsoft Word documents, stored in a varbinary(max) or image column.

Service Broker

SQL Server 2005 Service Broker provides the SQL Server Database Engine native support for messaging and queuing applications. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.

Application developers who use Service Broker can distribute data workloads across several databases without programming complicated communication and messaging internals. This reduces development and test work because Service Broker handles the communication paths within the context of a conversation. It also improves performance. For example, front-end databases supporting Web sites can record information and send process intensive tasks to queue in back-end databases. Service Broker ensures that all tasks are managed in the context of transactions to ensure reliability and technical consistency.

How to Insert Values into an Identity Column in SQL Server

2007-08-18T10:56:00.000+05:30

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (10, 'Row Ten')

SET IDENTITY_INSERT IdentityTable OFF

INSERT IdentityTable(TheValue)
VALUES ('Should be 11')

SELECT * FROM IdentityTable
GO

(1 row(s) affected)

(1 row(s) affected)
TheIdentity TheValue
----------- --------------------
10 Row Ten
11 Should be 11

(2 row(s) affected)

SQL Server: How to run a stored procedure at SQL Server start-up

2007-08-18T10:36:00.000+05:30

This is acctually quite simple. There is 'startup' option that you can set to the procedure.

There are a few limitations though:

- your stored pricedure must reside in the [master] database

- it's owner must be dbo

- it musn't have any input or output parameters

Here is some sample code:

USE master;
GO
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
-- then set the scan for startup procs to 1
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE

IF OBJECT_ID('spTest') IS NOT NULL
DROP PROC spTest
GO
-- crate a test stored procedure
CREATE PROC spTest
AS
-- just create a sample database
EXEC('CREATE database db1')

GO
-- set it to run at sql server start-up
exec sp_procoption N'spTest', 'startup', 'on'

Note that each stored procedure run at start up takes up one worker thread until finished. So if you want to run multiple sprocs at runtime

and parallelism doesn't matter create one sproc that executes all others.

Database Mail

2007-08-11T11:21:00.000+05:30

The SQL Mail problems, that we faced in SQL Server 7.0 and 2000, are no more. SQL Server 2005 supports and uses SMTP email now and there is no longer a need to MAPI client to send email. In SQL Server 2005, the mail feature is called Database Mail. In this article, I am going to demonstrate step-by-step, with illustrations, how to configure Database Mail and send email from SQL Server.

Database Mail has four components.

1. Configuration Component

Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.

The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members of DatabaseMailUserRole in MSDB database can send email. For private profile, a set of users should be defined.

2. Messaging Component

Messaging component is basically all of the objects related to sending email stored in the MSDB database.

3. Database Mail Executable

Database Mail uses the DatabaseMail90.exe executable to send email.

4. Logging and Auditing component

Database Mail stores the log information on MSDB database and it can be queried using sysmail_event_log.

Step 1

Before setting up the Database Mail profile and accounts, we have to enable the Database Mail feature on the server. This can be done in two ways. The first method is to use Transact SQL to enable Database Mail. The second method is to use a GUI.

In the SQL Server Management Studio, execute the following statement.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go

Step 2

The Configuration Component Database account can be enabled by using the sysmail_add_account procedure. In this article, we are going create the account, "MyMailAccount," using mail.optonline.net as the mail server and

makclaire@optimumonline.net as the e-mail account.

Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'makclaire@optonline.net',
@display_name = 'MyAccount',
@username='makclaire@optonline.net',
@password='abc123',
@mailserver_name = 'mail.optonline.net'

Step 3

The second sub component of the configuration requires us to create a Mail profile.

In this article, we are going to create "MyMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.

Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyMailProfile',
@description = 'Profile used for database mail'

Step 4

Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3.

Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMailAccount',
@sequence_number = 1

Step 5

Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.

Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1 ;

Step 6

Now let us send a test email from SQL Server.

Please execute the statement below.

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo.com',
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;

You will get the message
Mail queued

Sending Mail in ASP.NET 2.0

2007-08-11T10:57:00.000+05:30

MailMessage message = new MailMessage();

message.From = new MailAddress("sender@foo.bar.com");

message.To.Add(new MailAddress("recipient1@foo.bar.com"));

message.To.Add(new MailAddress("recipient2@foo.bar.com"));

message.To.Add(new MailAddress("recipient3@foo.bar.com"));

message.CC.Add(new MailAddress("carboncopy@foo.bar.com"));

message.Subject = "This is my subject";

message.Body = "This is the content";

SmtpClient client = new SmtpClient();

client.Send(message);

System.Net.Mail reads SMTP configuration data out of the standard .NET configuration system (so for ASP.NET applications you’d configure this in your application’s web.config file). Here is an example of how to configure it:

<system.net>

smtp>

mailSettings>

system.net>

Hope this helps,

Ganesh

ASP.Net 2.0: Export GridView to Excel

2007-08-11T10:49:00.000+05:30

The code to do the Excel Export is very straightforward. You can also export to different application type by changing the content-disposition and ContentType.

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

If you run the code as above, it will result in an HttpException as follows:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

To avoid this error, add the following code:

public override void VerifyRenderingInServerForm(Control control)

{

}