Wednesday, January 16, 2008

Database Script for US Phone No. Validation

Database Script for Validating Previously stored US Phone Numbers.

Example: You were not storing phone no. in format 1234567890 and to convert it to 123-456-7890 format following script would be useful.


Database Script for Formatting USA Phone Number

Note: Red Mark are the considerations which you need to taken care of for individual table.

DECLARE c1 CURSOR READ_ONLY

FOR

select [Phone-Number],[Last Name],[First Name] from Temp_Boniva_Speakers

declare @PhoneNum varchar(50)

declare @NewPhoneNum varchar(50)

declare @FirstDash varchar(1)

declare @SecondDash varchar(1)

declare @LeftNum varchar(3)

declare @MiddleNum varchar(3)

declare @LastNum varchar(4)

--Extra Fields for checking uniqueness of record

declare @FirstName varchar(50)

declare @LastName varchar(50)

declare @Email varchar(200)

open c1

FETCH NEXT FROM c1

Into @PhoneNum, @LastName, @FirstName

WHILE @@FETCH_STATUS = 0

BEGIN

set @PhoneNum = ltrim(@PhoneNum)

set @PhoneNum = rtrim(@PhoneNum)

--PRINT @PhoneNum

/********** Logic for conversion **************/

set @FirstDash = SUBSTRING(@PhoneNum,4,1)

set @SecondDash = SUBSTRING(@PhoneNum,8,1)

--When Proper Dash are their (Eg: 123-456-7890)

if @FirstDash = '-' and @SecondDash = '-'

begin

set @NewPhoneNum = @PhoneNum --Don't Change

end

--When Space is their instead of dash (Eg: 123 456 7890)

else if ascii(@FirstDash) = 32 and ascii(@SecondDash) = 32

begin

set @NewPhoneNum = REPLACE(@PhoneNum,@FirstDash,'-')

end

--When Given format is their (Eg: (123) 456-7890)

else if SUBSTRING(@PhoneNum,1,1) = '(' and SUBSTRING(@PhoneNum,4,1) = ')' and ascii(SUBSTRING(@PhoneNum,5,1)) = 32

begin

set @LeftNum = SUBSTRING(@PhoneNum,2,3)

set @MiddleNum = SUBSTRING (@PhoneNum,6,3)

set @LastNum = SUBSTRING (@PhoneNum,10,4)

set @NewPhoneNum = @LeftNum + '-' + @MiddleNum + '-' + @LastNum

end

--When No Dash is their (Eg: 1234567890)

else if IsNumeric(@PhoneNum) = 1

begin

set @LeftNum = SUBSTRING(@PhoneNum,1,3)

set @MiddleNum = SUBSTRING (@PhoneNum,4,3)

set @LastNum = SUBSTRING (@PhoneNum,7,4)

set @NewPhoneNum = @LeftNum + '-' + @MiddleNum + '-' + @LastNum

end

else --For Unknown Format Don't Loose the data

begin

set @NewPhoneNum = @PhoneNum

end

--Update

update Temp_Boniva_Speakers

set [Phone-Number]=@NewPhoneNum

where

--Uniqueness Checking

[Last Name] = @LastName and

[First Name] = @FirstName

--select @NewPhoneNum as NewPhone, @PhoneNum as OldPhone, @LastName, @FirstName

FETCH NEXT FROM c1

Into @PhoneNum, @LastName, @FirstName

END

CLOSE c1

DEALLOCATE c1

Tuesday, January 15, 2008

URL Mapping in asp.net 2.0

URL Mapping is a mechanism by which you can change the displayed url in address bar.

Example:

Your asp.net application is developed from years, with convention frm as prefix to webform.

Now to change already assigned name to legacy system which is maintained for past few years and to avoid risk we can make use of URL Mapping.


For URL Mapping
Step1: Add Mapping URL in web.config file.
<system.web>
<
urlMappings enabled="true">

<add url="~/Department.aspx" mappedUrl=" oldforms/frmDept.aspx"/>

<add url="~/Employee.aspx" mappedUrl=" oldforms/frmEmployee.aspx"/>

<add url="~/Product.aspx" mappedUrl=" oldforms/frmProduct.aspx"/>

</urlMappings>

</system.web>


Step2: Change the URL in .aspx file

<a href="Department.aspx">Department</a><br />

<a href="Product.aspx">Product</a><br />

<a href="Employee.aspx">Employee</a>


Step3: Now Check your ugly looking URL, it is replaced with New desired URL.

Before URL Mapping


After URL Mapping



Encrypt / Decrypt Section in Configuration File

Lets understand Encrypt and Decrypt particular section in web.config file with example of connectionstring encrypt in web.config file.

Add Namespace

using System.Web.Configuration;

How to Encrypt Connection String in Asp.net stored in Web.Config file

protected void Button1_Click(object sender, EventArgs e)

{

String webConfigPath = “~”;

Configuration config = WebConfigurationManager.OpenWebConfiguration(webConfigPath);

ConfigurationSection configSection =

config.GetSection("connectionStrings");

configSection.SectionInformation.ProtectSection

("DataProtectionConfigurationProvider");

config.Save();

}

Understanding code:

1. Defining Path for Web.Config.

2. Opening Web Configuration File.

3. Getting Connection String Section for Encryption.

4. Protect Connection String Section.

5. Make Changes Saved to Configuration File.

How to Decrypt Connection String in Asp.net stored in Web.Config file

protected void Button1_Click(object sender, EventArgs e)

{

String webConfigPath = “~”;

Configuration config = WebConfigurationManager.OpenWebConfiguration(webConfigPath);

ConfigurationSection configSection =

config.GetSection("connectionStrings");

configSection.SectionInformation.UnprotectSection();

config.Save();

}

Thursday, January 10, 2008

Free Database Compare Software for SQL Server

@ Colleague Thanks for providing useful information.

Free Database Compare Software for SQL Server , MY SQL and MS Access. It will also allow you to create snapshots of a database and compare a live database to a snapshot. A database structure viewer is included. You can filter the comparison results by type of object and you can also choose to exclude the comparison of constraints and fields.

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape