Little quirky but worked fine.
below call will return email address.
dim email=getEmailAddress('Aslam M Iqbal , new read watson st, aslam.iqbal-test@certainweb.com 02004 425577')
Imports System.Text.RegularExpressions
Shared Function getEmailAddress(ByVal mystring As String) As String
Dim pattern As String
pattern = "[_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+"
Dim RegularExpressionObject As Match = Regex.Match(mystring, pattern)
getEmailAddress = RegularExpressionObject.ToString()
End Function
Thursday 23 October 2008
simple search for column name all tables in db
select *
from information_schema.columns
where column_name like '%column_name%'
from information_schema.columns
where column_name like '%column_name%'
SQL Server Find and Replace Values in All Tables and All Text Columns
ref: http://www.mssqltips.com/tip.asp?tip=1555
SET NOCOUNT ON
DECLARE @stringToFind VARCHAR(100)
DECLARE @stringToReplace VARCHAR(100)
DECLARE @schema sysname
DECLARE @table sysname
DECLARE @count INT
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @object_id INT
SET @stringToFind = 'Smith'
SET @stringToReplace = 'Jones'
DECLARE TAB_CURSOR CURSOR FOR
SELECT B.NAME AS SCHEMANAME,
A.NAME AS TABLENAME,
A.OBJECT_ID
FROM sys.objects A
INNER JOIN sys.schemas B
ON A.SCHEMA_ID = B.SCHEMA_ID
WHERE TYPE = 'U'
ORDER BY 1
OPEN TAB_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE COL_CURSOR CURSOR FOR
SELECT A.NAME
FROM sys.columns A
INNER JOIN sys.types B
ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
WHERE OBJECT_ID = @object_id
AND IS_COMPUTED = 0
AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')
OPEN COL_CURSOR
FETCH NEXT FROM COL_CURSOR
INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'
SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''
EXEC( @sqlCommand + @where)
SET @count = @@ROWCOUNT
IF @count > 0
BEGIN
PRINT @sqlCommand + @where
PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)
PRINT '----------------------------------------------------'
END
FETCH NEXT FROM COL_CURSOR
INTO @columnName
END
CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
END
CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR
SET NOCOUNT ON
DECLARE @stringToFind VARCHAR(100)
DECLARE @stringToReplace VARCHAR(100)
DECLARE @schema sysname
DECLARE @table sysname
DECLARE @count INT
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @object_id INT
SET @stringToFind = 'Smith'
SET @stringToReplace = 'Jones'
DECLARE TAB_CURSOR CURSOR FOR
SELECT B.NAME AS SCHEMANAME,
A.NAME AS TABLENAME,
A.OBJECT_ID
FROM sys.objects A
INNER JOIN sys.schemas B
ON A.SCHEMA_ID = B.SCHEMA_ID
WHERE TYPE = 'U'
ORDER BY 1
OPEN TAB_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE COL_CURSOR CURSOR FOR
SELECT A.NAME
FROM sys.columns A
INNER JOIN sys.types B
ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
WHERE OBJECT_ID = @object_id
AND IS_COMPUTED = 0
AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')
OPEN COL_CURSOR
FETCH NEXT FROM COL_CURSOR
INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'
SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''
EXEC( @sqlCommand + @where)
SET @count = @@ROWCOUNT
IF @count > 0
BEGIN
PRINT @sqlCommand + @where
PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)
PRINT '----------------------------------------------------'
END
FETCH NEXT FROM COL_CURSOR
INTO @columnName
END
CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
END
CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR
To search all columns of all tables in a database for a keyword
Makes life easy by search for a column
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
ref: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
ref: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Monday 6 October 2008
Understanding View State in ASP.NET
good article:
http://aspalliance.com/909_Understanding_View_State_in_ASPNET.all
http://aspalliance.com/909_Understanding_View_State_in_ASPNET.all
Dynamic SQL use sp_executesql instead of EXECUTE Command
sp_executesql is more efficient, faster in execution and also supports parameter substitution
refer
http://www.codeproject.com/KB/database/Building_Dynamic_SQL.aspx
refer
http://www.codeproject.com/KB/database/Building_Dynamic_SQL.aspx
Sunday 18 May 2008
Javascript Postcode formatting
function FormatPostcode()
{
var sPostcode;var objTempField;objTempField = document.getElementById('txtPostcode');
sPostcode = objTempField.value;sPostcode = sPostcode.toUpperCase().replace(' ','');
if (sPostcode.length >= 5){sPostcode=sPostcode.substr(0,sPostcode.length-3) + ' ' + sPostcode.substr(sPostcode.length-3,3);
}
objTempField.value=sPostcode;
}
{
var sPostcode;var objTempField;objTempField = document.getElementById('txtPostcode');
sPostcode = objTempField.value;sPostcode = sPostcode.toUpperCase().replace(' ','');
if (sPostcode.length >= 5){sPostcode=sPostcode.substr(0,sPostcode.length-3) + ' ' + sPostcode.substr(sPostcode.length-3,3);
}
objTempField.value=sPostcode;
}
Friday 16 May 2008
Monday 5 May 2008
PHP first letter of each word is capitalized
from HERE
function formatArray(s:String):String { // rraWrd is array to hold split values from passed string parameter. var rraWrd:Array = s.split(" "); // stgNew is a string into which to concatenate the new full sentence. var stgNew:String = ""; var nbrLen = rraWrd.length; for (var i:Number = 0; i < nbrLen; i++) { rraWrd[i] = rraWrd[i].substring(0, 1).toUpperCase() + rraWrd[i].substring(1); stgNew += (i < nbrLen - 1) ? rraWrd[i] + " " : rraWrd[i]; } return stgNew;}//var stgSentence:String = "This is not a love song";trace("Before: " + stgSentence);stgSentence = formatArray(stgSentence);trace("After: " + stgSentence);
function formatArray(s:String):String { // rraWrd is array to hold split values from passed string parameter. var rraWrd:Array = s.split(" "); // stgNew is a string into which to concatenate the new full sentence. var stgNew:String = ""; var nbrLen = rraWrd.length; for (var i:Number = 0; i < nbrLen; i++) { rraWrd[i] = rraWrd[i].substring(0, 1).toUpperCase() + rraWrd[i].substring(1); stgNew += (i < nbrLen - 1) ? rraWrd[i] + " " : rraWrd[i]; } return stgNew;}//var stgSentence:String = "This is not a love song";trace("Before: " + stgSentence);stgSentence = formatArray(stgSentence);trace("After: " + stgSentence);
Monday 31 March 2008
How to select the first/least/max row per group in SQL
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
select type, variety, price
from fruits
where (
select count(*) from fruits as f
where f.type = fruits.type and f.price < fruits.price
) <= 2;
select type, variety, price
from fruits
where (
select count(*) from fruits as f
where f.type = fruits.type and f.price < fruits.price
) <= 2;
Sunday 16 March 2008
trouble shooting slow web site
What are the common mistakes causing your asp.net (1.1) site to run slowly? (pages take ages to load) Is there some kind of check list (urls) I can look at, are there any tools to diagnose this?
(http://discuss.joelonsoftware.com/default.asp?dotnet.12.386147.6)
There isn't really a magic checklist - somewhere along the way something is running too slowly: reading from database, doing business logic, generating pages, sending over the network, rendering in the browser...
The poor man's tool (which always works well for me): strategically place code throughout the application that logs timestamps, look at where the time is being used and drill down into that area with more fine-grained logging. You can usually zoom in on the problem area quickly and then apply performance tuning techniques appropriately (e.g. examine database query plans, etc).
The first time it runs any page on the site, it has to load all the dlls. So if you have a low-usage site, you're going to have that happen a lot.
Check the properties of the pages. If you have a large viewstate, then every page will take forever on slow connections (I have seen 300kb in viewstate on a page). If viewstate is part of the problem, then you should look into storing the state serverside. There should be a bunch of articles on the web about how to do that. Viewstate will be stored as a hidden input field on the page:
To solve a bad query (which I was not permitted to fix), on one slow loading page, we set up the server to cache that page specially, so it really ran only like once/day, and was fed from the cache the rest of the time.
One thing that got a site that I worked on to go faster was that someone had the include for the CSS in the code of the page by accident. Moving that to the header speed up teh pages dramatically. It was actually acceptable using dialup and before it wasn't.
I'd look for ViewState bloat first. View the source of the page from your web browser. If ViewState is a problem, it'll be very obvious from pages and pages of seemingly random garbage.
Then I'd look for excess use of DataBinder.Eval (or the newer Eval and Bind methods in ASP.NET 2.0). DataBinder.Eval late binds using reflection which means it's sloooooooow. You can get tremendous performance gains by dumping DataBinder.Eval in favor of direct casting of the data item to the appropriate type.
Be sure to eliminate the obvious stuff first. Like dozens of connections to download objects, and large file size and image size.
You can use a tool like www.SiteReportCard.com to check some of this.
Once you know it's not that, then start in on the advice given above.
Log4Net is a good tool to do logging of timestamps. You can turn it on/off with the config file, so you can use the same code and never mess with multiple compiles. …
If it is "slow" it should be easy to "shoot". ;)
Sorry, I couldn't resist making fun of the title of this post. I think he meant "troubleshooting" huh?
(http://discuss.joelonsoftware.com/default.asp?dotnet.12.386147.6)
There isn't really a magic checklist - somewhere along the way something is running too slowly: reading from database, doing business logic, generating pages, sending over the network, rendering in the browser...
The poor man's tool (which always works well for me): strategically place code throughout the application that logs timestamps, look at where the time is being used and drill down into that area with more fine-grained logging. You can usually zoom in on the problem area quickly and then apply performance tuning techniques appropriately (e.g. examine database query plans, etc).
The first time it runs any page on the site, it has to load all the dlls. So if you have a low-usage site, you're going to have that happen a lot.
Check the properties of the pages. If you have a large viewstate, then every page will take forever on slow connections (I have seen 300kb in viewstate on a page). If viewstate is part of the problem, then you should look into storing the state serverside. There should be a bunch of articles on the web about how to do that. Viewstate will be stored as a hidden input field on the page:
To solve a bad query (which I was not permitted to fix), on one slow loading page, we set up the server to cache that page specially, so it really ran only like once/day, and was fed from the cache the rest of the time.
One thing that got a site that I worked on to go faster was that someone had the include for the CSS in the code of the page by accident. Moving that to the header speed up teh pages dramatically. It was actually acceptable using dialup and before it wasn't.
I'd look for ViewState bloat first. View the source of the page from your web browser. If ViewState is a problem, it'll be very obvious from pages and pages of seemingly random garbage.
Then I'd look for excess use of DataBinder.Eval (or the newer Eval and Bind methods in ASP.NET 2.0). DataBinder.Eval late binds using reflection which means it's sloooooooow. You can get tremendous performance gains by dumping DataBinder.Eval in favor of direct casting of the data item to the appropriate type.
Be sure to eliminate the obvious stuff first. Like dozens of connections to download objects, and large file size and image size.
You can use a tool like www.SiteReportCard.com to check some of this.
Once you know it's not that, then start in on the advice given above.
Log4Net is a good tool to do logging of timestamps. You can turn it on/off with the config file, so you can use the same code and never mess with multiple compiles. …
If it is "slow" it should be easy to "shoot". ;)
Sorry, I couldn't resist making fun of the title of this post. I think he meant "troubleshooting" huh?
What make a website slow and How to fix it !
http://rochakchauhan.com/blog/2007/09/25/what-make-a-website-slow-and-how-to-fix-it/
Slow Internet connection:
This used to the major factor in the age of Dial-up internet connection. If you still use a modem and a telephone to connect to the Internet, then chances are it the reason for slow loading of the website. Practically this is not a common reason now a days.
Too much content on the Website:
There are few websites, which needs to display a lot of graphics, sounds, videos or flash file on the home page. All this leads to more time in loading of the homepage. Again, in the age of broadband and DSL, it is not the common factor to slow down a website.
Slow Computer:
Well if your system happens to have less memory (RAM) or a Spyware /Virus then it could also lead to painfully slow web experience. Having said that, if you have a Anti-Virus or Anti-Spyware installed then the chances are even this is not the reason.
Invalid HTML Code:
Invalid HTML ! what on earth does it have to do with the slow website ? As a matter of fact, HTML is the only medium, by which server tells your browser to display a web page. Lets take a classic example, you open any website on your browser:
Client takes 2 seconds to send request to the server.
Server responds back in 5 seconds.
Your browser takes 20 seconds to render the page.
What ? 20 seconds taken by the browser ??? But Why ? The reason is simple. HTML or xHTML is a collection of tags which tells your browser where to place and image, what color should be the text and so forth. But is there is some invalid HTML tags (eg unclosed tags ) then the browser is capable of debugging and fixings those invalid tags on its own. But, debugging takes a lot of time.
Slow Internet connection:
This used to the major factor in the age of Dial-up internet connection. If you still use a modem and a telephone to connect to the Internet, then chances are it the reason for slow loading of the website. Practically this is not a common reason now a days.
Too much content on the Website:
There are few websites, which needs to display a lot of graphics, sounds, videos or flash file on the home page. All this leads to more time in loading of the homepage. Again, in the age of broadband and DSL, it is not the common factor to slow down a website.
Slow Computer:
Well if your system happens to have less memory (RAM) or a Spyware /Virus then it could also lead to painfully slow web experience. Having said that, if you have a Anti-Virus or Anti-Spyware installed then the chances are even this is not the reason.
Invalid HTML Code:
Invalid HTML ! what on earth does it have to do with the slow website ? As a matter of fact, HTML is the only medium, by which server tells your browser to display a web page. Lets take a classic example, you open any website on your browser:
Client takes 2 seconds to send request to the server.
Server responds back in 5 seconds.
Your browser takes 20 seconds to render the page.
What ? 20 seconds taken by the browser ??? But Why ? The reason is simple. HTML or xHTML is a collection of tags which tells your browser where to place and image, what color should be the text and so forth. But is there is some invalid HTML tags (eg unclosed tags ) then the browser is capable of debugging and fixings those invalid tags on its own. But, debugging takes a lot of time.
Best Practices to Improve ASP.Net Web Application Performance.
From: http://www.dotnetfunda.com/articles/article45.aspx
Introduction
This document lists out some tips for optimizing ASP.Net Web applications and many traps and pitfalls are discussed as follows :
Tips For Web Application
1) Turn off Tracing unless until required
Tracing is one of the wonderful features which enable us to track the application's trace and the sequences. However, again it is useful only for developers and you can set this to "false" unless you require to monitor the trace logging.
How it affects performance:
Enabling tracing adds performance overhead and might expose private information, so it should be enabled only while an application is being actively analyzed.
Solution:
When not needed, tracing can be turned off using
2) Turn off Session State, if not required
One extremely powerful feature of ASP.NET is its ability to store session state for users, such as a shopping cart on an e-commerce site or a browser history.
How it affects performance:
Since ASP.NET Manages session state by default, you pay the cost in memory even if you don't use it. I.e. whether you store your data in in-process or on state server or in a Sql Database, session state requires memory and it's also time consuming when you store or retrieve data from it.
Solution:
You may not require session state when your pages are static or when you do not need to store information captured in the page.
In such cases where you need not use session state, disable it on your web form using the directive,
<@%Page EnableSessionState="false"%>
In case you use the session state only to retrieve data from it and not to update it, make the session state read only by using the directive,
<@%Page EnableSessionState ="ReadOnly"%>
3) Disable View State of a Page if possible
View state is a fancy name for ASP.NET storing some state data in a hidden input field inside the generated page. When the page is posted back to the server, the server can parse, validate, and apply this view state data back to the page's tree of controls.
View state is a very powerful capability since it allows state to be persisted with the client and it requires no cookies or server memory to save this state. Many ASP.NET server controls use view state to persist settings made during interactions with elements on the page, for example, saving the current page that is being displayed when paging through data.
How it affects performance:
There are a number of drawbacks to the use of view state, however.
It increases the total payload of the page both when served and when requested. There is also an additional overhead incurred when serializing or deserializing view state data that is posted back to the server.
View state increases the memory allocations on the server. Several server controls, the most well known of which is the DataGrid, tend to make excessive use of view state, even in cases where it is not needed.
Solution:
Pages that do not have any server postback events can have the view state turned off.
The default behavior of the ViewState property is enabled, but if you don't need it, you can turn it off at the control or page level. Within a control, simply set the EnableViewState property to false, or set it globally within the page using this setting:
<%@ Page EnableViewState="false" %>
If you turn view state off for a page or control, make sure you thoroughly test your pages to verify that they continue to function correctly.
4) Set debug=false in web.config
When you create the application, by default this attribute is set to "true" which is very useful while developing. However, when you are deploying your application, always set it to "false".
How it affects performance:
Setting it to "true" requires the pdb information to be inserted into the file and this results in a comparatively larger file and hence processing will be slow.
Solution:
Therefore, always set debug="false" before deployment.
5) Avoid Response.Redirect
Response.Redirect () method simply tells the browser to visit another page.
How it affects performance:
Redirects are also very chatty. They should only be used when you are transferring people to another physical web server.
Solution:
For any transfers within your server, use .transfer! You will save a lot of needless HTTP requests. Instead of telling the browser to redirect, it simply changes the "focus" on the Web server and transfers the request. This means you don't get quite as many HTTP requests coming through, which therefore eases the pressure on your Web server and makes your applications run faster.
Tradeoffs:
".transfer" process can work on only those sites running on the server. Only Response.Redirect can do that.
Server.Transfer maintains the original URL in the browser. This can really help streamline data entry techniques, although it may make for confusion when debugging
5. A) To reduce CLR Exceptions count, Use Response.Redirect (".aspx", false) instead of response.redirect (".aspx").
6) Use the String builder to concatenate string
How it affects performance:
String is Evil when you want to append and concatenate text to your string. All the activities you do to the string are stored in the memory as separate references and it must be avoided as much as possible.
i.e. When a string is modified, the run time will create a new string and return it, leaving the original to be garbage collected. Most of the time this is a fast and simple way to do it, but when a string is being modified repeatedly it begins to be a burden on performance: all of those allocations eventually get expensive.
Solution:
Use String Builder when ever string concatenation is needed so that it only stores the value in the original string and no additional reference is created.
7) Avoid throwing exceptions
How it affects performance:
Exceptions are probably one of the heaviest resource hogs and causes of slowdowns you will ever see in web applications, as well as windows applications.
Solution:
You can use as many try/catch blocks as you want. Using exceptions gratuitously is where you lose performance. For example, you should stay away from things like using exceptions for control flow.
8) Use Finally Method to kill resources
The finally method gets executed independent of the outcome of the Block.
Always use the finally block to kill resources like closing database connection, closing files and other resources such that they get executed independent of whether the code worked in Try or went to Catch.
9) Use Client Side Scripts for validations
User Input is Evil and it must be thoroughly validated before processing to avoid overhead and possible injections to your applications.
How It improves performance:
Client site validation can help reduce round trips that are required to process user's request. In ASP.NET you can also use client side controls to validate user input. However, do a check at the Server side too to avoid the infamous Javascript disabled scenarios.
10) Avoid unnecessary round trips to the server
How it affects performance:
Round trips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic and processing load on the database server can adversely affect performance.
Solution:
Keep round trips to an absolute minimum
Implement Ajax UI whenever possible. The idea is to avoid full page refresh and only update the portion of the page that needs to be changed
11) Use Page.ISPostBack
Make sure you don't execute code needlessly. Use Page.ISPostBack property to ensure that you only perform page initialization logic when a page is first time loaded and not in response to client postbacks.
12) Include Return Statements with in the Function/Method
How it improves performance
Explicitly using return allows the JIT to perform slightly more optimizations. Without a return statement, each function/method is given several local variables on stack to transparently support returning values without the keyword. Keeping these around makes it harder for the JIT to optimize, and can impact the performance of your code. Look through your functions/methods and insert return as needed. It doesn't change the semantics of the code at all, and it can help you get more speed from your application.
13) Use Foreach loop instead of For loop for String Iteration
Foreach is far more readable, and in the future it will become as fast as a For loop for special cases like strings. Unless string manipulation is a real performance hog for you, the slightly messier code may not be worth it.
14) Avoid Unnecessary Indirection
How it affects performance:
When you use byRef, you pass pointers instead of the actual object.
Many times this makes sense (side-effecting functions, for example), but you don't always need it. Passing pointers results in more indirection, which is slower than accessing a value that is on the stack.
Solution:
When you don't need to go through the heap, it is best to avoid it there by avoiding indirection.
15) Use "ArrayLists" in place of arrays
How it improves performance
An ArrayList as everything that is good about an array PLUS automatic sizing, Add, Insert, Remove, Sort, Binary Search. All these great helper methods are added when implementing the IList interface.
Tradeoffs:
The downside of an ArrayList is the need to cast objects upon retrieval.
16) Always check Page.IsValid when using Validator Controls
Always make sure you check Page.IsValid before processing your forms when using Validator Controls.
17) Use Paging
Take advantage of paging's simplicity in .net. Only show small subsets of data at a time, allowing the page to load faster.
Tradeoffs:
Just be careful when you mix in caching. Don't cache all the data in the grid.
18) Store your content by using caching
How it improves performance:
ASP.NET allows you to cache entire pages, fragment of pages or controls. You can cache also variable data by specifying the parameters that the data depends. By using caching you help ASP.NET engine to return data for repeated request for the same page much faster.
When and Why Use Caching:
A Proper use and fine tune of caching approach of caching will result on better performance and scalability of your site. However improper use of caching will actually slow down and consume lots of your server performance and memory usage.
Good candidate to use caching is if you have infrequent chance of data or static content of web page.
19) Use low cost authentication
Authentication can also have an impact over the performance of your application. For example passport authentication is slower than form-base authentication which in here turn is slower than Windows authentication.
20) Minimize the number of web server controls
How it affects performance:
The use of web server controls increases the response time of your application because they need time to be processed on the server side before they are rendered on the client side.
Solution:
One way to minimize the number of web server controls is to taking into consideration, the usage of HTML elements where they are suited, for example if you want to display static text.
21) Avoid using unmanaged code
How it affects performance:
Calls to unmanaged code are a costly marshaling operation.
Solution:
Try to reduce the number calls between the managed and unmanaged code. Consider to do more work in each call rather than making frequent calls to do small tasks.
22) Avoid making frequent calls across processes
If you are working with distributed applications, this involves additional overhead negotiating network and application level protocols. In this case network speed can also be a bottleneck. Try to do as much work as possible in fewer calls over the network.
23) Cleaning Up Style Sheets and Script Files
A quick and easy way to improve your web application's performance is by going back and cleaning up your CSS Style Sheets and Script Files of unnecessary code or old styles and functions. It is common for old styles and functions to still exist in your style sheets and script files during development cycles and when improvements are made to a website.
Many websites use a single CSS Style Sheet or Script File for the entire website. Sometimes, just going through these files and cleaning them up can improve the performance of your site by reducing the page size. If you are referencing images in your style sheet that are no longer used on your website, it's a waste of performance to leave them in there and have them loaded each time the style sheet is loaded.
Run a web page analyzer against pages in your website so that you can see exactly what is being loaded and what takes the most time to load.
24) Design with ValueTypes
Use simple structs when you can, and when you don't do a lot of boxing
and unboxing.
Tradeoffs:
ValueTypes are far less flexible than Objects, and end up hurting performance if used incorrectly. You need to be very careful about when you treat them like objects. This adds extra boxing and unboxing overhead to your program, and can end up costing you more than it would if you had stuck with objects.
25) Minimize assemblies
Minimize the number of assemblies you use to keep your working set small. If you load an entire assembly just to use one method, you're paying a tremendous cost for very little benefit. See if you can duplicate that method's functionality using code that you already have loaded.
26) Encode Using ASCII When You Don't Need UTF
By default, ASP.NET comes configured to encode requests and responses as UTF-8.
If ASCII is all your application needs, eliminated the UTF overhead can give you back a few cycles. Note that this can only be done on a per-application basis.
27) Avoid Recursive Functions / Nested Loops
These are general things to adopt in any programming language, which consume lot of memory. Always avoid Nested Loops, Recursive functions, to improve performance.
28) Minimize the Use of Format ()
When you can, use toString () instead of format (). In most cases, it will provide you with the functionality you need, with much less overhead.
29) Place StyleSheets into the Header
Web developers who care about performance want browser to load whatever content it has as soon as possible. This fact is especially important for pages with a lot of content and for users with slow Internet connections. When the browser loads the page progressively the header, the logo, the navigation components serve as visual feedback for the user.
When we place style sheets near the bottom part of the html, most browsers stop rendering to avoid redrawing elements of the page if their styles change thus decreasing the performance of the page. So, always place StyleSheets into the Header
30) Put Scripts to the end of Document
Unlike StyleSheets, it is better to place scripts to the end of the document. Progressive rendering is blocked until all StyleSheets have been downloaded. Scripts cause progressive rendering to stop for all content below the script until it is fully loaded. Moreover, while downloading a script, browser does not start any other component downloads, even on different hostnames.
So,always have scripts at the end of the document.
31) Make JavaScript and CSS External
Using external files generally produces faster pages because the JavaScript and CSS files are cached by the browser. Inline JavaScript and CSS increases the HTML document size but reduces the number of HTTP requests. With cached external files, the size of the HTML is kept small without increasing the number of HTTP requests thus improving the performance.
Tips For Database Operations
1) Return Multiple Resultsets
The database code if has request paths that go to the database more than once then, these round-trips decreases the number of requests per second your application can serve.
Solution:
Return multiple resultsets in a single database request, so that you can cut the total time spent communicating with the database. You'll be making your system more scalable, too, as you'll cut down on the work the database server is doing managing requests.
2) Connection Pooling and Object Pooling
Connection pooling is a useful way to reuse connections for multiple requests, rather than paying the overhead of opening and closing a connection for each request. It's done implicitly, but you get one pool per unique connection string. Make sure you call Close or Dispose on a connection as soon as possible. When pooling is enabled, calling Close or Dispose returns the connection to the pool instead of closing the underlying database connection.
Account for the following issues when pooling is a part of your design:
Share connections
Avoid per-user logons to the database
Do not vary connection strings
Do not cache connections
3) Use SqlDataReader Instead of Dataset wherever it is possible
If you are reading a table sequentially you should use the DataReader rather than DataSet. DataReader object creates a read only stream of data that will increase your application performance because only one row is in memory at a time.
4) Keep Your Datasets Lean
Remember that the dataset stores all of its data in memory, and that the more data you request, the longer it will take to transmit across the wire.
Therefore Only put the records you need into the dataset.
5) Avoid Inefficient queries
How it affects performance:
Queries that process and then return more columns or rows than necessary, waste processing cycles that could best be used for servicing other requests.
Cause of Inefficient queries:
Too much data in your results is usually the result of inefficient queries.
The SELECT * query often causes this problem. You do not usually need to return all the columns in a row. Also, analyze the WHERE clause in your queries to ensure that you are not returning too many rows. Try to make the WHERE clause as specific as possible to ensure that the least number of rows are returned.
Queries that do not take advantage of indexes may also cause poor performance.
6) Unnecessary round trips
How it affects performance:
Round trips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic and processing load on the database server can adversely affect performance.
Solution:
Keep round trips to an absolute minimum.
7) Too many open connections
Connections are an expensive and scarce resource, which should be shared between callers by using connection pooling. Opening a connection for each caller limits scalability.
Solution:
To ensure the efficient use of connection pooling, avoid keeping connections open and avoid varying connection strings.
8) Avoid Transaction misuse
How it affects performance:
If you select the wrong type of transaction management, you may add latency to each operation. Additionally, if you keep transactions active for long periods of time, the active transactions may cause resource pressure.
Solution:
Transactions are necessary to ensure the integrity of your data, but you need to ensure that you use the appropriate type of transaction for the shortest duration possible and only where necessary.
9) Avoid Over Normalized tables
Over Normalized tables may require excessive joins for simple operations. These additional steps may significantly affect the performance and scalability of your application, especially as the number of users and requests increases.
10) Reduce Serialization
Dataset serialization is more efficiently implemented in .NET Framework version 1.1 than in version 1.0. However, Dataset serialization often introduces performance bottlenecks.
You can reduce the performance impact in a number of ways:
Use column name aliasing
Avoid serializing multiple versions of the same data
Reduce the number of DataTable objects that are serialized
11) Do Not Use CommandBuilder at Run Time
How it affects performance:
CommandBuilder objects such as as SqlCommandBuilder and OleDbCommandBuilder are useful when you are designing and prototyping your application. However, you should not use them in production applications. The processing required to generate the commands affects performance.
Solution:
Manually create stored procedures for your commands, or use the Visual Studio® .NET design-time wizard and customize them later if necessary.
12) Use Stored Procedures Whenever Possible
Stored procedures are highly optimized tools that result in excellent performance when used effectively.
Set up stored procedures to handle inserts, updates, and deletes with the data adapter
Stored procedures do not have to be interpreted, compiled or even transmitted from the client, and cut down on both network traffic and server overhead.
Be sure to use CommandType.StoredProcedure instead of CommandType.Text
13) Avoid Auto-Generated Commands
When using a data adapter, avoid auto-generated commands. These require additional trips to the server to retrieve meta data, and give you a lower level of interaction control. While using auto-generated commands is convenient, it's worth the effort to do it yourself in performance-critical applications.
14) Use Sequential Access as Often as Possible
With a data reader, use CommandBehavior.SequentialAccess. This is essential for dealing with blob data types since it allows data to be read off of the wire in small chunks. While you can only work with one piece of the data at a time, the latency for loading a large data type disappears. If you don't need to work the whole object at once, using
Sequential Access will give you much better performance.
Tips for Asp.Net applications developed using VB
1) Enable Option Strict and Option Explicit for your pages
With Option Strict on, you protect yourself from inadvertent late binding and enforce a higher level of coding discipline.
2) Use early binding in Visual Basic or JScript code
Visual Basic 6 does a lot of work under the hood to support casting of objects, and many programmers aren't even aware of it. In Visual Basic 7, this is an area that out of which you can squeeze a lot of performance.
Solution:
When you compile, use early binding. This tells the compiler to insert a Type Coercion is only done when explicitly mentioned.
This has two major effects:
Strange errors become easier to track down.
Unneeded coercions are eliminated, leading to substantial performance improvements.
When you use an object as if it were of a different type, Visual Basic will coerce the object for you if you don't specify. This is handy, since the programmer has to worry about less code.
3) Put Concatenations in One Expression
If you have multiple concatenations on multiple lines, try to stick them all on one expression. The compiler can optimize by modifying the string in place, providing a speed and memory boost. If the statements are split into multiple lines, the Visual Basic compiler will not generate the Microsoft Intermediate Language (MSIL) to allow in-place concatenation.
Summary
When we talk about ASP.Net performance, there are lots of factors in place.
Above discussed are the most critical of the speed improvements you can make in ASP.net that will have a dramatic impact on the user experience of your web application.
Introduction
This document lists out some tips for optimizing ASP.Net Web applications and many traps and pitfalls are discussed as follows :
Tips For Web Application
1) Turn off Tracing unless until required
Tracing is one of the wonderful features which enable us to track the application's trace and the sequences. However, again it is useful only for developers and you can set this to "false" unless you require to monitor the trace logging.
How it affects performance:
Enabling tracing adds performance overhead and might expose private information, so it should be enabled only while an application is being actively analyzed.
Solution:
When not needed, tracing can be turned off using
2) Turn off Session State, if not required
One extremely powerful feature of ASP.NET is its ability to store session state for users, such as a shopping cart on an e-commerce site or a browser history.
How it affects performance:
Since ASP.NET Manages session state by default, you pay the cost in memory even if you don't use it. I.e. whether you store your data in in-process or on state server or in a Sql Database, session state requires memory and it's also time consuming when you store or retrieve data from it.
Solution:
You may not require session state when your pages are static or when you do not need to store information captured in the page.
In such cases where you need not use session state, disable it on your web form using the directive,
<@%Page EnableSessionState="false"%>
In case you use the session state only to retrieve data from it and not to update it, make the session state read only by using the directive,
<@%Page EnableSessionState ="ReadOnly"%>
3) Disable View State of a Page if possible
View state is a fancy name for ASP.NET storing some state data in a hidden input field inside the generated page. When the page is posted back to the server, the server can parse, validate, and apply this view state data back to the page's tree of controls.
View state is a very powerful capability since it allows state to be persisted with the client and it requires no cookies or server memory to save this state. Many ASP.NET server controls use view state to persist settings made during interactions with elements on the page, for example, saving the current page that is being displayed when paging through data.
How it affects performance:
There are a number of drawbacks to the use of view state, however.
It increases the total payload of the page both when served and when requested. There is also an additional overhead incurred when serializing or deserializing view state data that is posted back to the server.
View state increases the memory allocations on the server. Several server controls, the most well known of which is the DataGrid, tend to make excessive use of view state, even in cases where it is not needed.
Solution:
Pages that do not have any server postback events can have the view state turned off.
The default behavior of the ViewState property is enabled, but if you don't need it, you can turn it off at the control or page level. Within a control, simply set the EnableViewState property to false, or set it globally within the page using this setting:
<%@ Page EnableViewState="false" %>
If you turn view state off for a page or control, make sure you thoroughly test your pages to verify that they continue to function correctly.
4) Set debug=false in web.config
When you create the application, by default this attribute is set to "true" which is very useful while developing. However, when you are deploying your application, always set it to "false".
How it affects performance:
Setting it to "true" requires the pdb information to be inserted into the file and this results in a comparatively larger file and hence processing will be slow.
Solution:
Therefore, always set debug="false" before deployment.
5) Avoid Response.Redirect
Response.Redirect () method simply tells the browser to visit another page.
How it affects performance:
Redirects are also very chatty. They should only be used when you are transferring people to another physical web server.
Solution:
For any transfers within your server, use .transfer! You will save a lot of needless HTTP requests. Instead of telling the browser to redirect, it simply changes the "focus" on the Web server and transfers the request. This means you don't get quite as many HTTP requests coming through, which therefore eases the pressure on your Web server and makes your applications run faster.
Tradeoffs:
".transfer" process can work on only those sites running on the server. Only Response.Redirect can do that.
Server.Transfer maintains the original URL in the browser. This can really help streamline data entry techniques, although it may make for confusion when debugging
5. A) To reduce CLR Exceptions count, Use Response.Redirect (".aspx", false) instead of response.redirect (".aspx").
6) Use the String builder to concatenate string
How it affects performance:
String is Evil when you want to append and concatenate text to your string. All the activities you do to the string are stored in the memory as separate references and it must be avoided as much as possible.
i.e. When a string is modified, the run time will create a new string and return it, leaving the original to be garbage collected. Most of the time this is a fast and simple way to do it, but when a string is being modified repeatedly it begins to be a burden on performance: all of those allocations eventually get expensive.
Solution:
Use String Builder when ever string concatenation is needed so that it only stores the value in the original string and no additional reference is created.
7) Avoid throwing exceptions
How it affects performance:
Exceptions are probably one of the heaviest resource hogs and causes of slowdowns you will ever see in web applications, as well as windows applications.
Solution:
You can use as many try/catch blocks as you want. Using exceptions gratuitously is where you lose performance. For example, you should stay away from things like using exceptions for control flow.
8) Use Finally Method to kill resources
The finally method gets executed independent of the outcome of the Block.
Always use the finally block to kill resources like closing database connection, closing files and other resources such that they get executed independent of whether the code worked in Try or went to Catch.
9) Use Client Side Scripts for validations
User Input is Evil and it must be thoroughly validated before processing to avoid overhead and possible injections to your applications.
How It improves performance:
Client site validation can help reduce round trips that are required to process user's request. In ASP.NET you can also use client side controls to validate user input. However, do a check at the Server side too to avoid the infamous Javascript disabled scenarios.
10) Avoid unnecessary round trips to the server
How it affects performance:
Round trips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic and processing load on the database server can adversely affect performance.
Solution:
Keep round trips to an absolute minimum
Implement Ajax UI whenever possible. The idea is to avoid full page refresh and only update the portion of the page that needs to be changed
11) Use Page.ISPostBack
Make sure you don't execute code needlessly. Use Page.ISPostBack property to ensure that you only perform page initialization logic when a page is first time loaded and not in response to client postbacks.
12) Include Return Statements with in the Function/Method
How it improves performance
Explicitly using return allows the JIT to perform slightly more optimizations. Without a return statement, each function/method is given several local variables on stack to transparently support returning values without the keyword. Keeping these around makes it harder for the JIT to optimize, and can impact the performance of your code. Look through your functions/methods and insert return as needed. It doesn't change the semantics of the code at all, and it can help you get more speed from your application.
13) Use Foreach loop instead of For loop for String Iteration
Foreach is far more readable, and in the future it will become as fast as a For loop for special cases like strings. Unless string manipulation is a real performance hog for you, the slightly messier code may not be worth it.
14) Avoid Unnecessary Indirection
How it affects performance:
When you use byRef, you pass pointers instead of the actual object.
Many times this makes sense (side-effecting functions, for example), but you don't always need it. Passing pointers results in more indirection, which is slower than accessing a value that is on the stack.
Solution:
When you don't need to go through the heap, it is best to avoid it there by avoiding indirection.
15) Use "ArrayLists" in place of arrays
How it improves performance
An ArrayList as everything that is good about an array PLUS automatic sizing, Add, Insert, Remove, Sort, Binary Search. All these great helper methods are added when implementing the IList interface.
Tradeoffs:
The downside of an ArrayList is the need to cast objects upon retrieval.
16) Always check Page.IsValid when using Validator Controls
Always make sure you check Page.IsValid before processing your forms when using Validator Controls.
17) Use Paging
Take advantage of paging's simplicity in .net. Only show small subsets of data at a time, allowing the page to load faster.
Tradeoffs:
Just be careful when you mix in caching. Don't cache all the data in the grid.
18) Store your content by using caching
How it improves performance:
ASP.NET allows you to cache entire pages, fragment of pages or controls. You can cache also variable data by specifying the parameters that the data depends. By using caching you help ASP.NET engine to return data for repeated request for the same page much faster.
When and Why Use Caching:
A Proper use and fine tune of caching approach of caching will result on better performance and scalability of your site. However improper use of caching will actually slow down and consume lots of your server performance and memory usage.
Good candidate to use caching is if you have infrequent chance of data or static content of web page.
19) Use low cost authentication
Authentication can also have an impact over the performance of your application. For example passport authentication is slower than form-base authentication which in here turn is slower than Windows authentication.
20) Minimize the number of web server controls
How it affects performance:
The use of web server controls increases the response time of your application because they need time to be processed on the server side before they are rendered on the client side.
Solution:
One way to minimize the number of web server controls is to taking into consideration, the usage of HTML elements where they are suited, for example if you want to display static text.
21) Avoid using unmanaged code
How it affects performance:
Calls to unmanaged code are a costly marshaling operation.
Solution:
Try to reduce the number calls between the managed and unmanaged code. Consider to do more work in each call rather than making frequent calls to do small tasks.
22) Avoid making frequent calls across processes
If you are working with distributed applications, this involves additional overhead negotiating network and application level protocols. In this case network speed can also be a bottleneck. Try to do as much work as possible in fewer calls over the network.
23) Cleaning Up Style Sheets and Script Files
A quick and easy way to improve your web application's performance is by going back and cleaning up your CSS Style Sheets and Script Files of unnecessary code or old styles and functions. It is common for old styles and functions to still exist in your style sheets and script files during development cycles and when improvements are made to a website.
Many websites use a single CSS Style Sheet or Script File for the entire website. Sometimes, just going through these files and cleaning them up can improve the performance of your site by reducing the page size. If you are referencing images in your style sheet that are no longer used on your website, it's a waste of performance to leave them in there and have them loaded each time the style sheet is loaded.
Run a web page analyzer against pages in your website so that you can see exactly what is being loaded and what takes the most time to load.
24) Design with ValueTypes
Use simple structs when you can, and when you don't do a lot of boxing
and unboxing.
Tradeoffs:
ValueTypes are far less flexible than Objects, and end up hurting performance if used incorrectly. You need to be very careful about when you treat them like objects. This adds extra boxing and unboxing overhead to your program, and can end up costing you more than it would if you had stuck with objects.
25) Minimize assemblies
Minimize the number of assemblies you use to keep your working set small. If you load an entire assembly just to use one method, you're paying a tremendous cost for very little benefit. See if you can duplicate that method's functionality using code that you already have loaded.
26) Encode Using ASCII When You Don't Need UTF
By default, ASP.NET comes configured to encode requests and responses as UTF-8.
If ASCII is all your application needs, eliminated the UTF overhead can give you back a few cycles. Note that this can only be done on a per-application basis.
27) Avoid Recursive Functions / Nested Loops
These are general things to adopt in any programming language, which consume lot of memory. Always avoid Nested Loops, Recursive functions, to improve performance.
28) Minimize the Use of Format ()
When you can, use toString () instead of format (). In most cases, it will provide you with the functionality you need, with much less overhead.
29) Place StyleSheets into the Header
Web developers who care about performance want browser to load whatever content it has as soon as possible. This fact is especially important for pages with a lot of content and for users with slow Internet connections. When the browser loads the page progressively the header, the logo, the navigation components serve as visual feedback for the user.
When we place style sheets near the bottom part of the html, most browsers stop rendering to avoid redrawing elements of the page if their styles change thus decreasing the performance of the page. So, always place StyleSheets into the Header
30) Put Scripts to the end of Document
Unlike StyleSheets, it is better to place scripts to the end of the document. Progressive rendering is blocked until all StyleSheets have been downloaded. Scripts cause progressive rendering to stop for all content below the script until it is fully loaded. Moreover, while downloading a script, browser does not start any other component downloads, even on different hostnames.
So,always have scripts at the end of the document.
31) Make JavaScript and CSS External
Using external files generally produces faster pages because the JavaScript and CSS files are cached by the browser. Inline JavaScript and CSS increases the HTML document size but reduces the number of HTTP requests. With cached external files, the size of the HTML is kept small without increasing the number of HTTP requests thus improving the performance.
Tips For Database Operations
1) Return Multiple Resultsets
The database code if has request paths that go to the database more than once then, these round-trips decreases the number of requests per second your application can serve.
Solution:
Return multiple resultsets in a single database request, so that you can cut the total time spent communicating with the database. You'll be making your system more scalable, too, as you'll cut down on the work the database server is doing managing requests.
2) Connection Pooling and Object Pooling
Connection pooling is a useful way to reuse connections for multiple requests, rather than paying the overhead of opening and closing a connection for each request. It's done implicitly, but you get one pool per unique connection string. Make sure you call Close or Dispose on a connection as soon as possible. When pooling is enabled, calling Close or Dispose returns the connection to the pool instead of closing the underlying database connection.
Account for the following issues when pooling is a part of your design:
Share connections
Avoid per-user logons to the database
Do not vary connection strings
Do not cache connections
3) Use SqlDataReader Instead of Dataset wherever it is possible
If you are reading a table sequentially you should use the DataReader rather than DataSet. DataReader object creates a read only stream of data that will increase your application performance because only one row is in memory at a time.
4) Keep Your Datasets Lean
Remember that the dataset stores all of its data in memory, and that the more data you request, the longer it will take to transmit across the wire.
Therefore Only put the records you need into the dataset.
5) Avoid Inefficient queries
How it affects performance:
Queries that process and then return more columns or rows than necessary, waste processing cycles that could best be used for servicing other requests.
Cause of Inefficient queries:
Too much data in your results is usually the result of inefficient queries.
The SELECT * query often causes this problem. You do not usually need to return all the columns in a row. Also, analyze the WHERE clause in your queries to ensure that you are not returning too many rows. Try to make the WHERE clause as specific as possible to ensure that the least number of rows are returned.
Queries that do not take advantage of indexes may also cause poor performance.
6) Unnecessary round trips
How it affects performance:
Round trips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic and processing load on the database server can adversely affect performance.
Solution:
Keep round trips to an absolute minimum.
7) Too many open connections
Connections are an expensive and scarce resource, which should be shared between callers by using connection pooling. Opening a connection for each caller limits scalability.
Solution:
To ensure the efficient use of connection pooling, avoid keeping connections open and avoid varying connection strings.
8) Avoid Transaction misuse
How it affects performance:
If you select the wrong type of transaction management, you may add latency to each operation. Additionally, if you keep transactions active for long periods of time, the active transactions may cause resource pressure.
Solution:
Transactions are necessary to ensure the integrity of your data, but you need to ensure that you use the appropriate type of transaction for the shortest duration possible and only where necessary.
9) Avoid Over Normalized tables
Over Normalized tables may require excessive joins for simple operations. These additional steps may significantly affect the performance and scalability of your application, especially as the number of users and requests increases.
10) Reduce Serialization
Dataset serialization is more efficiently implemented in .NET Framework version 1.1 than in version 1.0. However, Dataset serialization often introduces performance bottlenecks.
You can reduce the performance impact in a number of ways:
Use column name aliasing
Avoid serializing multiple versions of the same data
Reduce the number of DataTable objects that are serialized
11) Do Not Use CommandBuilder at Run Time
How it affects performance:
CommandBuilder objects such as as SqlCommandBuilder and OleDbCommandBuilder are useful when you are designing and prototyping your application. However, you should not use them in production applications. The processing required to generate the commands affects performance.
Solution:
Manually create stored procedures for your commands, or use the Visual Studio® .NET design-time wizard and customize them later if necessary.
12) Use Stored Procedures Whenever Possible
Stored procedures are highly optimized tools that result in excellent performance when used effectively.
Set up stored procedures to handle inserts, updates, and deletes with the data adapter
Stored procedures do not have to be interpreted, compiled or even transmitted from the client, and cut down on both network traffic and server overhead.
Be sure to use CommandType.StoredProcedure instead of CommandType.Text
13) Avoid Auto-Generated Commands
When using a data adapter, avoid auto-generated commands. These require additional trips to the server to retrieve meta data, and give you a lower level of interaction control. While using auto-generated commands is convenient, it's worth the effort to do it yourself in performance-critical applications.
14) Use Sequential Access as Often as Possible
With a data reader, use CommandBehavior.SequentialAccess. This is essential for dealing with blob data types since it allows data to be read off of the wire in small chunks. While you can only work with one piece of the data at a time, the latency for loading a large data type disappears. If you don't need to work the whole object at once, using
Sequential Access will give you much better performance.
Tips for Asp.Net applications developed using VB
1) Enable Option Strict and Option Explicit for your pages
With Option Strict on, you protect yourself from inadvertent late binding and enforce a higher level of coding discipline.
2) Use early binding in Visual Basic or JScript code
Visual Basic 6 does a lot of work under the hood to support casting of objects, and many programmers aren't even aware of it. In Visual Basic 7, this is an area that out of which you can squeeze a lot of performance.
Solution:
When you compile, use early binding. This tells the compiler to insert a Type Coercion is only done when explicitly mentioned.
This has two major effects:
Strange errors become easier to track down.
Unneeded coercions are eliminated, leading to substantial performance improvements.
When you use an object as if it were of a different type, Visual Basic will coerce the object for you if you don't specify. This is handy, since the programmer has to worry about less code.
3) Put Concatenations in One Expression
If you have multiple concatenations on multiple lines, try to stick them all on one expression. The compiler can optimize by modifying the string in place, providing a speed and memory boost. If the statements are split into multiple lines, the Visual Basic compiler will not generate the Microsoft Intermediate Language (MSIL) to allow in-place concatenation.
Summary
When we talk about ASP.Net performance, there are lots of factors in place.
Above discussed are the most critical of the speed improvements you can make in ASP.net that will have a dramatic impact on the user experience of your web application.
Monday 7 January 2008
convert datareader to datatable
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
Dim dt As New DataTable
dt.Load(objSQLdatareader)
now datatable contains all data from datareader
we can assign datatable to datagrid, gridview datalist etc. as below
gridview.DataSource = dt
gridview1.DataBind()
Dim dt As New DataTable
dt.Load(objSQLdatareader)
now datatable contains all data from datareader
we can assign datatable to datagrid, gridview datalist etc. as below
gridview.DataSource = dt
gridview1.DataBind()
Datepicker control asp.net
1. create new page:DatePicker.aspx
2. code behild : DatePicker.aspx.vb
code to place the all this page
1. Under head section of sample.aspx:
2. code sample.aspx page
3. code DatePicker.aspx
see the picture for -html code
4. Code DatePicker.aspx.vb
Imports System.Text
Public Class DatePicker
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
End Sub
'Protected WithEvents Calendar1 As System.Web.UI.WebControls.Calendar
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Nothing to do, here
End Sub
Private Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender
'// Clear the link from this day
e.Cell.Controls.Clear()
'// Add the custom link
Dim Link As System.Web.UI.HtmlControls.HtmlGenericControl
Link = New System.Web.UI.HtmlControls.HtmlGenericControl
Link.TagName = "a"
Link.InnerText = e.Day.DayNumberText
Link.Attributes.Add("href", String.Format("JavaScript:window.opener.document.{0}.value = '{1:d}'; window.close();", Request.QueryString("field"), e.Day.Date))
'// By default, this will highlight today's date.
If e.Day.IsSelected Then
Link.Attributes.Add("style", Me.Calendar1.SelectedDayStyle.ToString())
End If
'// Now add our custom link to the page
e.Cell.Controls.Add(Link)
End Sub
End Class
Messagebox in ASP.Net using div
1. Place a 'place holder' control in asp.net page and
2. call the class below with setting values for the properties
sample:
Dim newBox As MessageBox = New MessageBox()
newBox.MessageBoxButton = 2
newBox.MessageBoxTop = 150
newBox.MessageBoxLeft = 50
newBox.MessageBoxWidth = 500
newBox.MessageBoxHeight = 150
newBox.MessageBoxButtonWidth = 50
newBox.MessageBoxIDYes = "yesno"
newBox.MessageBoxIDNo = "yesno"
newBox.MessageBoxButtonYesText = "Continue"
newBox.MessageBoxButtonNoText = "Cancel"
NewBox.MessageBoxTitle = "Postcode has been covered."
newBox.MessageBoxMessage = aMessage '"Do you want to continue?"
newBox.MessageBoxImage = "images/information.gif"
PlaceHolder1.Controls.Add(newBox)
'Main Class here
Imports Microsoft.VisualBasic
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.Drawing.Drawing2D
Public Class js
Public Function js()
End Function
End Class
Public Class MessageBox
Inherits WebControl
Private strLeft As String
Private strTop As String
Private intButton As Integer
Private strMessage As String
Private strTitle As String
Private strImage As String
Private strCss As String
Private strCssTitle As String
Private strCssMessage As String
Private strButtonYes As String
Private strButtonNo As String
Private strButtonCancel As String
Private strButtonWidth As Integer
Private strMessageBoxIDYes As String
Private strMessageBoxIDNo As String
Private strMessageBoxIDCancel As String
Private intMessageBoxWidth As Integer
Private intMessageBoxHeight As Integer
Private intMessageBoxImageWidth As Integer
Private intMessageBoxImageHeight As Integer
Dim homedirectory As String
Private strMessageBoxWidth As String
' Message box left position
#Region "properties"
Public Property MessageBoxLeft() As String
Get
Return strLeft
End Get
Set(ByVal Value As String)
strLeft = Value
End Set
End Property
' Message box top position
Public Property MessageBoxTop() As String
Get
Return strTop
End Get
Set(ByVal Value As String)
strTop = Value
End Set
End Property
' Number of buttons you want to display in the message box
Public Property MessageBoxButton() As Integer
Get
Return intButton
End Get
Set(ByVal Value As Integer)
intButton = Value
End Set
End Property
' Customize message you want to display in the message box
Public Property MessageBoxMessage() As String
Get
Return strMessage
End Get
Set(ByVal Value As String)
strMessage = Value
End Set
End Property
' Title you want to display in the message box
Public Property MessageBoxTitle() As String
Get
Return strTitle
End Get
Set(ByVal Value As String)
strTitle = Value
End Set
End Property
' Image you want to display in the message box (like information / warning)
Public Property MessageBoxImage() As String
Get
Return strImage
End Get
Set(ByVal Value As String)
strImage = Value
End Set
End Property
' Message box ID for Yes button
Public Property MessageBoxIDYes() As String
Get
Return strMessageBoxIDYes
End Get
Set(ByVal Value As String)
strMessageBoxIDYes = Value
End Set
End Property
' Message box ID for No button
Public Property MessageBoxIDNo() As String
Get
Return strMessageBoxIDNo
End Get
Set(ByVal Value As String)
strMessageBoxIDNo = Value
End Set
End Property
' Message box ID for Cancel button
Public Property MessageBoxIDCancel() As String
Get
Return strMessageBoxIDCancel
End Get
Set(ByVal Value As String)
strMessageBoxIDCancel = Value
End Set
End Property
' Style you want to incorporate for message box
Public Property MessageBoxCss() As String
Get
Return strCss
End Get
Set(ByVal Value As String)
strCss = Value
End Set
End Property
Public Property MessageBoxCssTitle() As String
Get
Return strCssTitle
End Get
Set(ByVal Value As String)
strCssTitle = Value
End Set
End Property
Public Property MessageBoxCssMessage() As String
Get
Return strCssMessage
End Get
Set(ByVal Value As String)
strCssMessage = Value
End Set
End Property
' Message box Text for Yes button
Public Property MessageBoxButtonYesText() As String
Get
Return strButtonYes
End Get
Set(ByVal Value As String)
strButtonYes = Value
End Set
End Property
' Message box Text for No button
Public Property MessageBoxButtonNoText() As String
Get
Return strButtonNo
End Get
Set(ByVal Value As String)
strButtonNo = Value
End Set
End Property
' Message box Text for Cancel button
Public Property MessageBoxButtonCancelText() As String
Get
Return strButtonCancel
End Get
Set(ByVal Value As String)
strButtonCancel = Value
End Set
End Property
' Message box buttons width
Public Property MessageBoxButtonWidth() As Integer
Get
Return strButtonWidth
End Get
Set(ByVal Value As Integer)
strButtonWidth = Value
End Set
End Property
'' Message box width
'Public Property MessageBoxWidth() As Integer
' Get
' Return intMessageBoxWidth
' End Get
' Set(ByVal Value As Integer)
' intMessageBoxWidth = Value
' End Set
'End Property
' Message box width
Public Property MessageBoxWidth() As String
Get
Return strMessageBoxWidth
End Get
Set(ByVal Value As String)
strMessageBoxWidth = Value
End Set
End Property
' Message box height
Public Property MessageBoxHeight() As Integer
Get
Return intMessageBoxHeight
End Get
Set(ByVal Value As Integer)
intMessageBoxHeight = Value
End Set
End Property
' Message box image width
Public Property MessageBoxImageWidth() As Integer
Get
Return intMessageBoxImageWidth
End Get
Set(ByVal Value As Integer)
intMessageBoxImageWidth = Value
End Set
End Property
' Message box image height
Public Property MessageBoxImageHeight() As Integer
Get
Return intMessageBoxImageHeight
End Get
Set(ByVal Value As Integer)
intMessageBoxImageHeight = Value
End Set
End Property
#End Region
Protected Friend layer As HtmlGenericControl
Protected Friend ilayer As HtmlGenericControl
Protected Friend img As HtmlGenericControl
Protected Friend div As HtmlGenericControl
Protected Friend ButtonOK As Button
Protected Friend ButtonYes As Button
Protected Friend ButtonNo As Button
Protected Friend ButtonCancel As Button
Protected Alertimage As System.Web.UI.WebControls.Image
Public Sub New()
MyBase.New("div")
End Sub
Protected Overrides Sub OnInit(ByVal e As EventArgs)
MyBase.OnInit(e)
' Default properties settings for message box control
If strLeft Is Nothing Then
strLeft = "250"
End If
If strTop Is Nothing Then
strTop = "250"
End If
If strTitle Is Nothing Then
strTitle = "MessageBox"
End If
If intButton < 0 Then
intButton = 1
End If
If strMessageBoxIDYes Is Nothing Then
strMessageBoxIDYes = "MessageBoxIDYes"
End If
If strMessageBoxIDNo Is Nothing Then
strMessageBoxIDNo = "MessageBoxIDNo"
End If
If strMessageBoxIDCancel Is Nothing Then
strMessageBoxIDCancel = "MessageBoxIDCancel"
End If
If strCss Is Nothing Then
strCss = ""
End If
If strCssMessage Is Nothing Then
strCssMessage = ""
End If
If strCssTitle Is Nothing Then
strCssTitle = ""
End If
If strMessage Is Nothing Then
strMessage = "No message to display here."
End If
If intButton = 1 Or intButton > 3 Or intButton < 1 Then
If strButtonYes Is Nothing Then
strButtonYes = "OK"
End If
ElseIf intButton > 1 And intButton < 4 Then
If strButtonYes Is Nothing Then
strButtonYes = "Approve"
End If
If strButtonNo Is Nothing Then
strButtonNo = "Cancel"
End If
If strButtonCancel Is Nothing Then
strButtonCancel = "Ignore"
End If
End If
If strButtonWidth < 5 Then
strButtonWidth = 70
End If
If intMessageBoxWidth < 10 Then
intMessageBoxWidth = 250
End If
If intMessageBoxHeight < 1 Then
intMessageBoxHeight = 8
End If
If intMessageBoxImageWidth < 5 Then
intMessageBoxImageWidth = 36
End If
If intMessageBoxImageHeight < 5 Then
intMessageBoxImageHeight = 36
End If
If homedirectory Is Nothing Then
homedirectory = Me.Page.Request.PhysicalApplicationPath
End If
End Sub
#Region "createChildControls"
Protected Overrides Sub createChildControls()
' Creating message box
Dim myRow As TableRow
Dim myCell As TableCell
Dim myTable As Table = New Table()
myTable.BorderWidth = New Unit(0)
myTable.CellSpacing = 0
myTable.Width = New Unit(intMessageBoxWidth)
myTable.Height = New Unit(intMessageBoxHeight)
Controls.Add(myTable)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
myCell = New TableCell()
Dim NewLabel As Label = New Label()
NewLabel.Text = strTitle
NewLabel.CssClass = strCssTitle
myCell.Controls.Add(NewLabel)
myCell.ID = "dragbar"
myCell.ColumnSpan = 5
myCell.CssClass = strCssTitle
If strCssTitle = "" Then
myCell.ForeColor = System.Drawing.Color.White
myCell.BackColor = System.Drawing.Color.DarkBlue
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
myCell.Style.Add("CURSOR", "hand")
End If
myRow.Cells.Add(myCell)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
Dim myRow1 As TableRow
Dim myCell1 As TableCell
Dim myTable1 As Table = New Table()
myTable1.BorderWidth = New Unit(0)
myTable1.CellSpacing = 0
myCell.Controls.Add(myTable1)
myRow1 = New TableRow()
myRow1.BorderWidth = New Unit(0)
myTable1.Rows.Add(myRow1)
myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)
myCell1.Width = New Unit(36)
Dim Alertimage As System.Web.UI.WebControls.Image = New System.Web.UI.WebControls.Image()
Alertimage.Height = New Unit(intMessageBoxImageHeight)
Alertimage.Width = New Unit(intMessageBoxImageWidth)
Alertimage.BorderWidth = New Unit(0)
Alertimage.ImageUrl = strImage
myCell1.Controls.Add(Alertimage)
myRow1.Cells.Add(myCell1)
myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)
myCell1.CssClass = strCssMessage
If strCssMessage = "" Then
myCell1.HorizontalAlign = HorizontalAlign.Center
myCell1.ForeColor = System.Drawing.Color.Black
myCell1.BackColor = System.Drawing.Color.LightGray
myCell1.BorderColor = System.Drawing.Color.LightGray
myCell1.Font.Name = "Verdana"
myCell1.Font.Bold = True
myCell1.Font.Size = New FontUnit(8)
End If
Dim NewLabel1 As Label = New Label()
NewLabel1.Text = strMessage
myCell1.Controls.Add(NewLabel1)
myRow1.Cells.Add(myCell1)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
If intButton = 1 Or intButton > 3 Or intButton < 1 Then
myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
myCell.HorizontalAlign = HorizontalAlign.Center
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonOK = New Button()
ButtonOK.ID = strMessageBoxIDYes
ButtonOK.Text = strButtonYes
ButtonOK.Width = New Unit(strButtonWidth)
ButtonOK.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonOK)
myRow.Cells.Add(myCell)
End If
If intButton > 1 And intButton < 4 Then
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Right
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonYes = New Button()
ButtonYes.ID = strMessageBoxIDYes
ButtonYes.Text = strButtonYes
ButtonYes.Width = New Unit(strButtonWidth)
ButtonYes.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonYes)
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.Width = New Unit(20)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
If intButton = 2 Then
myCell.HorizontalAlign = HorizontalAlign.Left
ElseIf intButton = 3 Then
myCell.HorizontalAlign = HorizontalAlign.Center
End If
ButtonNo = New Button()
ButtonNo.ID = strMessageBoxIDNo
ButtonNo.Text = strButtonNo
ButtonNo.Width = New Unit(strButtonWidth)
ButtonNo.Attributes("WIDTH") = strButtonWidth.ToString()
ButtonNo.Attributes("HEIGHT") = strButtonWidth.ToString()
ButtonNo.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonNo)
myRow.Cells.Add(myCell)
If intButton = 3 Then
myCell = New TableCell()
myCell.Width = New Unit(10)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Left
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonCancel = New Button()
ButtonCancel.ID = strMessageBoxIDCancel
ButtonCancel.Text = strButtonCancel
ButtonCancel.Width = New Unit(strButtonWidth)
ButtonCancel.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonCancel)
myRow.Cells.Add(myCell)
End If
End If
End Sub
#End Region
Protected Overrides Sub AddAttributesToRender(ByVal writer As HtmlTextWriter)
' Rendering message box control to the browser
MyBase.AddAttributesToRender(writer)
writer.AddAttribute(HtmlTextWriterAttribute.Id, "showimage")
'writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strLeft + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction="135," strength="3;"")
writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strMessageBoxWidth + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction=135), strength=3;")
End Sub
End Class
2. call the class below with setting values for the properties
sample:
Dim newBox As MessageBox = New MessageBox()
newBox.MessageBoxButton = 2
newBox.MessageBoxTop = 150
newBox.MessageBoxLeft = 50
newBox.MessageBoxWidth = 500
newBox.MessageBoxHeight = 150
newBox.MessageBoxButtonWidth = 50
newBox.MessageBoxIDYes = "yesno"
newBox.MessageBoxIDNo = "yesno"
newBox.MessageBoxButtonYesText = "Continue"
newBox.MessageBoxButtonNoText = "Cancel"
NewBox.MessageBoxTitle = "Postcode has been covered."
newBox.MessageBoxMessage = aMessage '"Do you want to continue?"
newBox.MessageBoxImage = "images/information.gif"
PlaceHolder1.Controls.Add(newBox)
'Main Class here
Imports Microsoft.VisualBasic
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.Drawing.Drawing2D
Public Class js
Public Function js()
End Function
End Class
Public Class MessageBox
Inherits WebControl
Private strLeft As String
Private strTop As String
Private intButton As Integer
Private strMessage As String
Private strTitle As String
Private strImage As String
Private strCss As String
Private strCssTitle As String
Private strCssMessage As String
Private strButtonYes As String
Private strButtonNo As String
Private strButtonCancel As String
Private strButtonWidth As Integer
Private strMessageBoxIDYes As String
Private strMessageBoxIDNo As String
Private strMessageBoxIDCancel As String
Private intMessageBoxWidth As Integer
Private intMessageBoxHeight As Integer
Private intMessageBoxImageWidth As Integer
Private intMessageBoxImageHeight As Integer
Dim homedirectory As String
Private strMessageBoxWidth As String
' Message box left position
#Region "properties"
Public Property MessageBoxLeft() As String
Get
Return strLeft
End Get
Set(ByVal Value As String)
strLeft = Value
End Set
End Property
' Message box top position
Public Property MessageBoxTop() As String
Get
Return strTop
End Get
Set(ByVal Value As String)
strTop = Value
End Set
End Property
' Number of buttons you want to display in the message box
Public Property MessageBoxButton() As Integer
Get
Return intButton
End Get
Set(ByVal Value As Integer)
intButton = Value
End Set
End Property
' Customize message you want to display in the message box
Public Property MessageBoxMessage() As String
Get
Return strMessage
End Get
Set(ByVal Value As String)
strMessage = Value
End Set
End Property
' Title you want to display in the message box
Public Property MessageBoxTitle() As String
Get
Return strTitle
End Get
Set(ByVal Value As String)
strTitle = Value
End Set
End Property
' Image you want to display in the message box (like information / warning)
Public Property MessageBoxImage() As String
Get
Return strImage
End Get
Set(ByVal Value As String)
strImage = Value
End Set
End Property
' Message box ID for Yes button
Public Property MessageBoxIDYes() As String
Get
Return strMessageBoxIDYes
End Get
Set(ByVal Value As String)
strMessageBoxIDYes = Value
End Set
End Property
' Message box ID for No button
Public Property MessageBoxIDNo() As String
Get
Return strMessageBoxIDNo
End Get
Set(ByVal Value As String)
strMessageBoxIDNo = Value
End Set
End Property
' Message box ID for Cancel button
Public Property MessageBoxIDCancel() As String
Get
Return strMessageBoxIDCancel
End Get
Set(ByVal Value As String)
strMessageBoxIDCancel = Value
End Set
End Property
' Style you want to incorporate for message box
Public Property MessageBoxCss() As String
Get
Return strCss
End Get
Set(ByVal Value As String)
strCss = Value
End Set
End Property
Public Property MessageBoxCssTitle() As String
Get
Return strCssTitle
End Get
Set(ByVal Value As String)
strCssTitle = Value
End Set
End Property
Public Property MessageBoxCssMessage() As String
Get
Return strCssMessage
End Get
Set(ByVal Value As String)
strCssMessage = Value
End Set
End Property
' Message box Text for Yes button
Public Property MessageBoxButtonYesText() As String
Get
Return strButtonYes
End Get
Set(ByVal Value As String)
strButtonYes = Value
End Set
End Property
' Message box Text for No button
Public Property MessageBoxButtonNoText() As String
Get
Return strButtonNo
End Get
Set(ByVal Value As String)
strButtonNo = Value
End Set
End Property
' Message box Text for Cancel button
Public Property MessageBoxButtonCancelText() As String
Get
Return strButtonCancel
End Get
Set(ByVal Value As String)
strButtonCancel = Value
End Set
End Property
' Message box buttons width
Public Property MessageBoxButtonWidth() As Integer
Get
Return strButtonWidth
End Get
Set(ByVal Value As Integer)
strButtonWidth = Value
End Set
End Property
'' Message box width
'Public Property MessageBoxWidth() As Integer
' Get
' Return intMessageBoxWidth
' End Get
' Set(ByVal Value As Integer)
' intMessageBoxWidth = Value
' End Set
'End Property
' Message box width
Public Property MessageBoxWidth() As String
Get
Return strMessageBoxWidth
End Get
Set(ByVal Value As String)
strMessageBoxWidth = Value
End Set
End Property
' Message box height
Public Property MessageBoxHeight() As Integer
Get
Return intMessageBoxHeight
End Get
Set(ByVal Value As Integer)
intMessageBoxHeight = Value
End Set
End Property
' Message box image width
Public Property MessageBoxImageWidth() As Integer
Get
Return intMessageBoxImageWidth
End Get
Set(ByVal Value As Integer)
intMessageBoxImageWidth = Value
End Set
End Property
' Message box image height
Public Property MessageBoxImageHeight() As Integer
Get
Return intMessageBoxImageHeight
End Get
Set(ByVal Value As Integer)
intMessageBoxImageHeight = Value
End Set
End Property
#End Region
Protected Friend layer As HtmlGenericControl
Protected Friend ilayer As HtmlGenericControl
Protected Friend img As HtmlGenericControl
Protected Friend div As HtmlGenericControl
Protected Friend ButtonOK As Button
Protected Friend ButtonYes As Button
Protected Friend ButtonNo As Button
Protected Friend ButtonCancel As Button
Protected Alertimage As System.Web.UI.WebControls.Image
Public Sub New()
MyBase.New("div")
End Sub
Protected Overrides Sub OnInit(ByVal e As EventArgs)
MyBase.OnInit(e)
' Default properties settings for message box control
If strLeft Is Nothing Then
strLeft = "250"
End If
If strTop Is Nothing Then
strTop = "250"
End If
If strTitle Is Nothing Then
strTitle = "MessageBox"
End If
If intButton < 0 Then
intButton = 1
End If
If strMessageBoxIDYes Is Nothing Then
strMessageBoxIDYes = "MessageBoxIDYes"
End If
If strMessageBoxIDNo Is Nothing Then
strMessageBoxIDNo = "MessageBoxIDNo"
End If
If strMessageBoxIDCancel Is Nothing Then
strMessageBoxIDCancel = "MessageBoxIDCancel"
End If
If strCss Is Nothing Then
strCss = ""
End If
If strCssMessage Is Nothing Then
strCssMessage = ""
End If
If strCssTitle Is Nothing Then
strCssTitle = ""
End If
If strMessage Is Nothing Then
strMessage = "No message to display here."
End If
If intButton = 1 Or intButton > 3 Or intButton < 1 Then
If strButtonYes Is Nothing Then
strButtonYes = "OK"
End If
ElseIf intButton > 1 And intButton < 4 Then
If strButtonYes Is Nothing Then
strButtonYes = "Approve"
End If
If strButtonNo Is Nothing Then
strButtonNo = "Cancel"
End If
If strButtonCancel Is Nothing Then
strButtonCancel = "Ignore"
End If
End If
If strButtonWidth < 5 Then
strButtonWidth = 70
End If
If intMessageBoxWidth < 10 Then
intMessageBoxWidth = 250
End If
If intMessageBoxHeight < 1 Then
intMessageBoxHeight = 8
End If
If intMessageBoxImageWidth < 5 Then
intMessageBoxImageWidth = 36
End If
If intMessageBoxImageHeight < 5 Then
intMessageBoxImageHeight = 36
End If
If homedirectory Is Nothing Then
homedirectory = Me.Page.Request.PhysicalApplicationPath
End If
End Sub
#Region "createChildControls"
Protected Overrides Sub createChildControls()
' Creating message box
Dim myRow As TableRow
Dim myCell As TableCell
Dim myTable As Table = New Table()
myTable.BorderWidth = New Unit(0)
myTable.CellSpacing = 0
myTable.Width = New Unit(intMessageBoxWidth)
myTable.Height = New Unit(intMessageBoxHeight)
Controls.Add(myTable)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
myCell = New TableCell()
Dim NewLabel As Label = New Label()
NewLabel.Text = strTitle
NewLabel.CssClass = strCssTitle
myCell.Controls.Add(NewLabel)
myCell.ID = "dragbar"
myCell.ColumnSpan = 5
myCell.CssClass = strCssTitle
If strCssTitle = "" Then
myCell.ForeColor = System.Drawing.Color.White
myCell.BackColor = System.Drawing.Color.DarkBlue
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
myCell.Style.Add("CURSOR", "hand")
End If
myRow.Cells.Add(myCell)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
Dim myRow1 As TableRow
Dim myCell1 As TableCell
Dim myTable1 As Table = New Table()
myTable1.BorderWidth = New Unit(0)
myTable1.CellSpacing = 0
myCell.Controls.Add(myTable1)
myRow1 = New TableRow()
myRow1.BorderWidth = New Unit(0)
myTable1.Rows.Add(myRow1)
myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)
myCell1.Width = New Unit(36)
Dim Alertimage As System.Web.UI.WebControls.Image = New System.Web.UI.WebControls.Image()
Alertimage.Height = New Unit(intMessageBoxImageHeight)
Alertimage.Width = New Unit(intMessageBoxImageWidth)
Alertimage.BorderWidth = New Unit(0)
Alertimage.ImageUrl = strImage
myCell1.Controls.Add(Alertimage)
myRow1.Cells.Add(myCell1)
myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)
myCell1.CssClass = strCssMessage
If strCssMessage = "" Then
myCell1.HorizontalAlign = HorizontalAlign.Center
myCell1.ForeColor = System.Drawing.Color.Black
myCell1.BackColor = System.Drawing.Color.LightGray
myCell1.BorderColor = System.Drawing.Color.LightGray
myCell1.Font.Name = "Verdana"
myCell1.Font.Bold = True
myCell1.Font.Size = New FontUnit(8)
End If
Dim NewLabel1 As Label = New Label()
NewLabel1.Text = strMessage
myCell1.Controls.Add(NewLabel1)
myRow1.Cells.Add(myCell1)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
If intButton = 1 Or intButton > 3 Or intButton < 1 Then
myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
myCell.HorizontalAlign = HorizontalAlign.Center
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonOK = New Button()
ButtonOK.ID = strMessageBoxIDYes
ButtonOK.Text = strButtonYes
ButtonOK.Width = New Unit(strButtonWidth)
ButtonOK.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonOK)
myRow.Cells.Add(myCell)
End If
If intButton > 1 And intButton < 4 Then
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Right
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonYes = New Button()
ButtonYes.ID = strMessageBoxIDYes
ButtonYes.Text = strButtonYes
ButtonYes.Width = New Unit(strButtonWidth)
ButtonYes.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonYes)
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.Width = New Unit(20)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
If intButton = 2 Then
myCell.HorizontalAlign = HorizontalAlign.Left
ElseIf intButton = 3 Then
myCell.HorizontalAlign = HorizontalAlign.Center
End If
ButtonNo = New Button()
ButtonNo.ID = strMessageBoxIDNo
ButtonNo.Text = strButtonNo
ButtonNo.Width = New Unit(strButtonWidth)
ButtonNo.Attributes("WIDTH") = strButtonWidth.ToString()
ButtonNo.Attributes("HEIGHT") = strButtonWidth.ToString()
ButtonNo.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonNo)
myRow.Cells.Add(myCell)
If intButton = 3 Then
myCell = New TableCell()
myCell.Width = New Unit(10)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Left
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonCancel = New Button()
ButtonCancel.ID = strMessageBoxIDCancel
ButtonCancel.Text = strButtonCancel
ButtonCancel.Width = New Unit(strButtonWidth)
ButtonCancel.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonCancel)
myRow.Cells.Add(myCell)
End If
End If
End Sub
#End Region
Protected Overrides Sub AddAttributesToRender(ByVal writer As HtmlTextWriter)
' Rendering message box control to the browser
MyBase.AddAttributesToRender(writer)
writer.AddAttribute(HtmlTextWriterAttribute.Id, "showimage")
'writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strLeft + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction="135," strength="3;"")
writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strMessageBoxWidth + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction=135), strength=3;")
End Sub
End Class
One database reusable class for SQL Server communication
create two classes
1. 'SQLConnection' (main class to communicate with db)
2. 'SQLStoredProcedureParameterInfo' to be called from main class to get parameter info.
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Collections.Generic
Imports System.Data.SqlClient
'class 1
Public Class SQLConnection
Inherits MarshalByRefObject
Implements IDisposable
'Private variables
Private m_sConnectionString As String
Private m_objConnection As SqlClient.SqlConnection = Nothing
Private m_objTransaction As SqlClient.SqlTransaction = Nothing
Private disposedValue As Boolean = False ' To detect redundant calls
Private Shared m_objCommandParameterInfo As New Hashtable
Public Sub New(ByVal ConnectionString As String)
m_sConnectionString = ConnectionString
End Sub
Public Function TestConnection() As Boolean
Dim bRetVal As Boolean = False
Const iTestTimeout As Integer = 5 'seconds
Try
With OpenConnection(iTestTimeout)
bRetVal = (.State = ConnectionState.Open)
End With
Catch ex As Exception
bRetVal = False
End Try
Return bRetVal
End Function
Public Function OpenConnection(Optional ByVal Timeout As Integer = 15) As SqlClient.SqlConnection
If (m_objConnection Is Nothing) Then
Dim sConnString As New SqlClient.SqlConnectionStringBuilder(m_sConnectionString)
sConnString.ConnectTimeout = Timeout
m_objConnection = New SqlClient.SqlConnection(sConnString.ToString)
End If
If m_objConnection.State = ConnectionState.Closed Then
With m_objConnection
.Open()
End With
End If
Return m_objConnection
End Function
Public Sub CloseConnection()
If (m_objConnection IsNot Nothing) Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If
m_objConnection = Nothing
End If
End Sub
Public Function ConnectSP(ByVal Name As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand
'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection, m_objTransaction)
End If
With objRetVal
.CommandType = CommandType.StoredProcedure
RetrieveCommandParameters(objRetVal)
End With
Return objRetVal
End Function
Public Function ConnectSQL(ByVal SQL As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand
'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection, m_objTransaction)
End If
With objRetVal
.CommandType = CommandType.Text
End With
Return objRetVal
End Function
Public Function BeginTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
With OpenConnection()
m_objTransaction = .BeginTransaction()
End With
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Public Function CommitTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
m_objTransaction.Commit()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Public Function RollbackTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
m_objTransaction.Rollback()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Private Sub RetrieveCommandParameters(ByVal SQLCommand As SqlClient.SqlCommand)
Dim lstParameterInfo As List(Of SQLStoredProcedureParameterInfo)
With SQLCommand
If m_objCommandParameterInfo.Contains(.CommandText) Then
'Get cached parameter info
lstParameterInfo = m_objCommandParameterInfo(.CommandText)
'Loop through and add a new parameter
For Each objParameterInfo As SQLStoredProcedureParameterInfo In lstParameterInfo
.Parameters.Add(objParameterInfo.CreateParameter())
Next
Else
'Retrieve parameters from database server
SqlClient.SqlCommandBuilder.DeriveParameters(SQLCommand)
'Build parameter info list
lstParameterInfo = New List(Of SQLStoredProcedureParameterInfo)
For Each objParameter As SqlClient.SqlParameter In .Parameters
lstParameterInfo.Add(New SQLStoredProcedureParameterInfo(objParameter))
Next
'...and add into cache for next time
m_objCommandParameterInfo.Add(.CommandText, lstParameterInfo)
End If
End With
End Sub
' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
m_objTransaction = Nothing
If m_objConnection IsNot Nothing Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If
m_objConnection = Nothing
End If
End If
' TODO: free shared unmanaged resources
End If
Me.disposedValue = True
End Sub
#Region " IDisposable Support "
' This code added by Visual Basic to correctly implement the disposable pattern.
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class
'Class 2
Public Class SQLStoredProcedureParameterInfo
Public Name As String
Public Type As System.Data.SqlDbType
Public Direction As System.Data.ParameterDirection
Public DefaultValue As Object
Public Size As Integer
Public Sub New(ByVal Parameter As SqlClient.SqlParameter)
With Parameter
Name = .ParameterName
Type = .SqlDbType
Direction = .Direction
DefaultValue = .Value
Size = .Size
End With
End Sub
Public Function CreateParameter() As SqlClient.SqlParameter
Dim objRetVal As New SqlClient.SqlParameter
With objRetVal
.ParameterName = Name
.SqlDbType = Type
.Direction = Direction
.Value = DefaultValue
.Size = Size
End With
Return objRetVal
End Function
End Class
''''method to use this class as below:
Using objSqlConnection As SQLConnection = New SQLConnection(ConfigurationManager.AppSettings.Item("SQLConnString").ToString)
Dim strSQL As String
'for SQL
Dim BusinessRegionIntroducerGroupId As Integer
strSQL = "select * from table1 where employid=" & Session("empId")
With objSqlConnection.ConnectSQL(strSQL)
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
If objSQLdatareader.Read Then
empname= objSQLdatareader.Item("empname")
End If
objSQLdatareader.Close()
End With
'for SP
strSQL = "spSaveEmployee"
With objSqlConnection.ConnectSP(strSQL)
.Parameters("@DeptId").Value = DeptId
.Parameters("@empid").Value = Session("empid").ToString
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
objSQLdatareader.Close()
End With
End Using
1. 'SQLConnection' (main class to communicate with db)
2. 'SQLStoredProcedureParameterInfo' to be called from main class to get parameter info.
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Collections.Generic
Imports System.Data.SqlClient
'class 1
Public Class SQLConnection
Inherits MarshalByRefObject
Implements IDisposable
'Private variables
Private m_sConnectionString As String
Private m_objConnection As SqlClient.SqlConnection = Nothing
Private m_objTransaction As SqlClient.SqlTransaction = Nothing
Private disposedValue As Boolean = False ' To detect redundant calls
Private Shared m_objCommandParameterInfo As New Hashtable
Public Sub New(ByVal ConnectionString As String)
m_sConnectionString = ConnectionString
End Sub
Public Function TestConnection() As Boolean
Dim bRetVal As Boolean = False
Const iTestTimeout As Integer = 5 'seconds
Try
With OpenConnection(iTestTimeout)
bRetVal = (.State = ConnectionState.Open)
End With
Catch ex As Exception
bRetVal = False
End Try
Return bRetVal
End Function
Public Function OpenConnection(Optional ByVal Timeout As Integer = 15) As SqlClient.SqlConnection
If (m_objConnection Is Nothing) Then
Dim sConnString As New SqlClient.SqlConnectionStringBuilder(m_sConnectionString)
sConnString.ConnectTimeout = Timeout
m_objConnection = New SqlClient.SqlConnection(sConnString.ToString)
End If
If m_objConnection.State = ConnectionState.Closed Then
With m_objConnection
.Open()
End With
End If
Return m_objConnection
End Function
Public Sub CloseConnection()
If (m_objConnection IsNot Nothing) Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If
m_objConnection = Nothing
End If
End Sub
Public Function ConnectSP(ByVal Name As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand
'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection, m_objTransaction)
End If
With objRetVal
.CommandType = CommandType.StoredProcedure
RetrieveCommandParameters(objRetVal)
End With
Return objRetVal
End Function
Public Function ConnectSQL(ByVal SQL As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand
'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection, m_objTransaction)
End If
With objRetVal
.CommandType = CommandType.Text
End With
Return objRetVal
End Function
Public Function BeginTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
With OpenConnection()
m_objTransaction = .BeginTransaction()
End With
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Public Function CommitTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
m_objTransaction.Commit()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Public Function RollbackTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
m_objTransaction.Rollback()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Private Sub RetrieveCommandParameters(ByVal SQLCommand As SqlClient.SqlCommand)
Dim lstParameterInfo As List(Of SQLStoredProcedureParameterInfo)
With SQLCommand
If m_objCommandParameterInfo.Contains(.CommandText) Then
'Get cached parameter info
lstParameterInfo = m_objCommandParameterInfo(.CommandText)
'Loop through and add a new parameter
For Each objParameterInfo As SQLStoredProcedureParameterInfo In lstParameterInfo
.Parameters.Add(objParameterInfo.CreateParameter())
Next
Else
'Retrieve parameters from database server
SqlClient.SqlCommandBuilder.DeriveParameters(SQLCommand)
'Build parameter info list
lstParameterInfo = New List(Of SQLStoredProcedureParameterInfo)
For Each objParameter As SqlClient.SqlParameter In .Parameters
lstParameterInfo.Add(New SQLStoredProcedureParameterInfo(objParameter))
Next
'...and add into cache for next time
m_objCommandParameterInfo.Add(.CommandText, lstParameterInfo)
End If
End With
End Sub
' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
m_objTransaction = Nothing
If m_objConnection IsNot Nothing Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If
m_objConnection = Nothing
End If
End If
' TODO: free shared unmanaged resources
End If
Me.disposedValue = True
End Sub
#Region " IDisposable Support "
' This code added by Visual Basic to correctly implement the disposable pattern.
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class
'Class 2
Public Class SQLStoredProcedureParameterInfo
Public Name As String
Public Type As System.Data.SqlDbType
Public Direction As System.Data.ParameterDirection
Public DefaultValue As Object
Public Size As Integer
Public Sub New(ByVal Parameter As SqlClient.SqlParameter)
With Parameter
Name = .ParameterName
Type = .SqlDbType
Direction = .Direction
DefaultValue = .Value
Size = .Size
End With
End Sub
Public Function CreateParameter() As SqlClient.SqlParameter
Dim objRetVal As New SqlClient.SqlParameter
With objRetVal
.ParameterName = Name
.SqlDbType = Type
.Direction = Direction
.Value = DefaultValue
.Size = Size
End With
Return objRetVal
End Function
End Class
''''method to use this class as below:
Using objSqlConnection As SQLConnection = New SQLConnection(ConfigurationManager.AppSettings.Item("SQLConnString").ToString)
Dim strSQL As String
'for SQL
Dim BusinessRegionIntroducerGroupId As Integer
strSQL = "select * from table1 where employid=" & Session("empId")
With objSqlConnection.ConnectSQL(strSQL)
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
If objSQLdatareader.Read Then
empname= objSQLdatareader.Item("empname")
End If
objSQLdatareader.Close()
End With
'for SP
strSQL = "spSaveEmployee"
With objSqlConnection.ConnectSP(strSQL)
.Parameters("@DeptId").Value = DeptId
.Parameters("@empid").Value = Session("empid").ToString
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
objSQLdatareader.Close()
End With
End Using
Subscribe to:
Posts (Atom)