Saturday, December 22, 2007

How To: Compress ViewState in ASP.NET 2.0


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.

<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="/wEPDwUJLTMzMTY4NDI5ZGRrYY+UdQNeb33gRiGcw2LoiMHduA==" />

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));
    }
}

Friday, December 21, 2007

Show and Hide ModalPopupExtender from JavaScript


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.

<script language="javascript">

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


</script >

Monday, November 19, 2007

Message Box in ASP.NET

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);
}
}
}

Saturday, October 27, 2007

Sending Emails with Dynamic Content

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);

Thursday, October 25, 2007

How to use AJAX

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.

Tuesday, August 21, 2007

What is Business Intelligence?

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

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.

Saturday, August 18, 2007

How to Insert Values into an Identity Column in SQL Server

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

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.

Saturday, August 11, 2007

Database Mail

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

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>

<mailSettings>

<smtp from="test@foo.com">

<network host="smtpserver1" port="25" userName="username" password="secret" defaultCredentials="true" />

smtp>

mailSettings>

system.net>

Hope this helps,


Ganesh

ASP.Net 2.0: Export GridView to Excel

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)

{

}