Friday, October 24, 2008

SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored procedure

The below query will return the object name and as well as the object type.

SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%code/SQL command/any text to search for%'
ORDER BY Location, ObjectName

SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored procedure

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

   ----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Monday, September 1, 2008

JavaScript Password Validation

Here are the validation types supported out of the box. All are optional, which means that all passwords are allowed by default.

  • Minimum and maximum length.
  • Minimum n lowercase characters (a–z).
  • Minimum n uppercase characters (A–Z).
  • Minimum n combined a–z and A–Z characters.
  • Minimum n numeric characters (0–9).
  • Minimum n special characters (characters other than a–z, A–Z, and 0–9).
  • Ban particular words (tested case-insensitively).
  • Ban n-length character sequences (e.g. "abc", "XYZ", or "789", with a sequence length of 3; does not apply to special characters).
  • Ban n-length qwerty character sequences (e.g. "qwerty" or "asdf", with a sequence length of 4; does not apply to special characters).
  • Ban sequential, identical characters (e.g. "aa" or "!!").
  • Use custom regular expressions (tested using RegExp.prototype.test) and functions (the password is provided as the first argument, and a Boolean value is expected in return).

Here's an example of how it can be used:


var password = "password";
var passed = validatePassword(password, {
length: [8, Infinity],
lower: 1,
upper: 1,
numeric: 1,
special: 1,
badWords: ["password", "steven", "levithan"],
badSequenceLength: 4
});
// passed: false

The above requires that password is at least eight characters long; has at least one lowercase, uppercase, numeric, and special character; doesn't include the words "password", "steven", or "levithan"; and doesn't include an alphanumeric sequence four or more characters in length (e.g. "1234").

Here's the code (there are no external library dependencies):


function validatePassword (pw, options) {
// default options (allows any password)
var o = {
lower: 0,
upper: 0,
alpha: 0, /* lower + upper */
numeric: 0,
special: 0,
length: [0, Infinity],
custom: [ /* regexes and/or functions */ ],
badWords: [],
badSequenceLength: 0,
noQwertySequences: false,
noSequential: false
};

for (var property in options)
o[property] = options[property];

var re = {
lower: /[a-z]/g,
upper: /[A-Z]/g,
alpha: /[A-Z]/gi,
numeric: /[0-9]/g,
special: /[\W_]/g
},
rule, i;

// enforce min/max length
if (pw.length <> o.length[1])
return false;

// enforce lower/upper/alpha/numeric/special rules
for (rule in re) {
if ((pw.match(re[rule]) || []).length < o[rule])
return false;
}

// enforce word ban (case insensitive)
for (i = 0; i < o.badWords.length; i++) {
if (pw.toLowerCase().indexOf(o.badWords[i].toLowerCase()) > -1)
return false;
}

// enforce the no sequential, identical characters rule
if (o.noSequential && /([\S\s])\1/.test(pw))
return false;

// enforce alphanumeric/qwerty sequence ban rules
if (o.badSequenceLength) {
var lower = "abcdefghijklmnopqrstuvwxyz",
upper = lower.toUpperCase(),
numbers = "0123456789",
qwerty = "qwertyuiopasdfghjklzxcvbnm",
start = o.badSequenceLength - 1,
seq = "_" + pw.slice(0, start);
for (i = start; i < pw.length; i++) {
seq = seq.slice(1) + pw.charAt(i);
if (
lower.indexOf(seq) > -1 ||
upper.indexOf(seq) > -1 ||
numbers.indexOf(seq) > -1 ||
(o.noQwertySequences && qwerty.indexOf(seq) > -1)
) {
return false;
}
}
}

// enforce custom regex/function rules
for (i = 0; i < o.custom.length; i++) {
rule = o.custom[i];
if (rule instanceof RegExp) {
if (!rule.test(pw))
return false;
} else if (rule instanceof Function) {
if (!rule(pw))
return false;
}
}

// great success!
return true;

}

Tuesday, August 26, 2008

Convert string to Title Case/Sentence Case in C#

The ToTitleCase method resides in the TextInfo class which is a member of the System.Globalization namespace. The ToTitleCase method is not static and needs an instance of the class.
To gain access to the TextInfo class you first have to specify cultural information which you can get from the CurrentCulture property of the thread the code is running on.

So:

using System.Globalization;
using System.Threading;

//Create CultureInfo and TextInfo classes to use ToTitleCase method
CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
TextInfo textInfo = cultureInfo.TextInfo;

oClient.ClientName = textInfo.ToTitleCase(txtClientName.Text);

Ps: Title Case converts every first letter of a word in a sentence to upper case, and the rest to lower case.

Adding contains(t) function to JavaScript String.prototype

The contains(t) function returns true if the string contains the value of the parameter "t" and false if it doesn’t. To extend the String.prototype with the contains(t) function I use this line of code:

String.prototype.contains = function(t) { return this.indexOf(t) >= 0 ? true : false }

This is sample code how to use the contains(t) function:

var sMyVar = new String (" testing text ");
alert(sMyVar.contains(’testing’));

//use it with the value of a text box
document.getElementById("txtMyTextBox").value.contains(’some text’);

Use of Row_Number function for custom paging

The Row_Number() function in SQL is used to assign sequence number for each record retrieved for a collection of records.

Hence, we can use this function to retrieve a portion of records (Custom) from the collection of records.
Syntax:

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Field Used for Sorting])AS Row, * FROM ([Select Query])AS UserQuery) AS SelectionWithRowNumbers WHERE Row >[First Record]AND Row<=[Last Record]

Creating Property Class from XML : C#

If the XML structure is simple its easier to create a class file for serialisation.
This process consists of 3 steps as follows:

Go to Visual Studio 2005 -> Visual Studio Tools -> Visual Studio Command Prompt .

Let's consider our XML file name is like Menu.xml and is placed the D:/ drive,
Step 1: In the command prompt first point to the drive location where the XML file
is located for ex., in our case use the command d:
ex., C:\Program Files\Microsoft Visual Studio 8\VC>d:

Step 2: xsd Menu.xml
After this step, a file named Menu.xsd was created
Step 3: xsd Menu.xsd /classes
After this step, a file named Menu.cs was created

This Menu.cs file is the final class file contains the property classes for the XML.

Convert Int to Enum value : C#

This example will helps you to convert a integer value into corresponding enum value

(yourEnumType)Enum.ToObject(typeof(yourEnumType), integerValue)

DomainName & Default Gateway : C#

To get the LAN domain name and default gateway address try the below code
Note: don't forget to include the namespace

using System.Net.NetworkInformation;

IPGlobalProperties ipProperties = IPGlobalProperties.GetIPGlobalProperties();
string intranetDomainName = ipProperties.DomainName;
foreach (NetworkInterface networkCard in NetworkInterface.GetAllNetworkInterfaces())
{
foreach (GatewayIPAddressInformation gatewayAddress in networkCard.GetIPProperties().GatewayAddresses)
{
string defaultGatewayAddress = gatewayAddress.Address.ToString();
}
}

Retrieve DomainName using javascript

There are two ways to retrieve domain name using javascript

1. document.domain
2. window.location.hostname

The entry 'KEY' has already been added : .Net

This problem happen because .NET has several configuration files and they are all merged together. Your KEY is already defined in some other config file.

To resolve this issue
Add

in your web.config like below code.




Converting Class Array to DataTable : C#

Methods to Convert Class Array to DataTable

private static void FillData(PropertyInfo[] propertyInfo, DataTable dataTable, Object objectSource)

{

DataRow dataRow = dataTable.NewRow();

foreach (PropertyInfo pi in propertyInfo)

dataRow[pi.Name] = pi.GetValue(objectSource, null);

dataTable.Rows.Add(dataRow);

}

private static DataTable CreateDataTable(PropertyInfo[] propertyInfo)

{

DataTable dataTable = new DataTable();

DataColumn dataColumn = null;

foreach (PropertyInfo pi in propertyInfo)

{

dataColumn = new DataColumn();

dataColumn.ColumnName = pi.Name;

dataColumn.DataType = pi.PropertyType;

dataTable.Columns.Add(dataColumn);

}

return dataTable;

}

public static DataTable ConvertToDataTable(Object[] array)

{

PropertyInfo[] propertyInfo = array.GetType().GetElementType().GetProperties();

DataTable dataTable = CreateDataTable(propertyInfo);

if (array.Length != 0)

{

foreach (object o in array)

FillData(propertyInfo, dataTable, o);

}

return dataTable;

}

Ramdom Password Generation : Using C#

The following C# code will generate a random number without repeating character

public static string GetRandomPassword(int passwordLength)

{

string allowedChars = "$%#@!*abcdefghijklmnopqrstuvwxyz1234567890?~ABCDEFGHIJKLMNOPQRSTUVWXYZ^&";

string newPassword = "";

Random randomNumber = new Random();

for (int i = 0; i <>

{

int charIndex = randomNumber.Next(allowedChars.Length);

//Don't Allow Repetation of Characters

if (!newPassword.Contains(allowedChars[charIndex] + ""))

newPassword += allowedChars[charIndex];

else

i--;

}

return newPassword;

}

Print a number in Currency Format

To print a number in currency (dollar - $) format, use the following code


Create an object for NumberFormatInfo Class in the class level

protected System.Globalization.NumberFormatInfo nfi = null;


Then create an instance for the object and assign the CurrencySymbol and CurrencyDecimalDigits as below in the Page_Load Event

nfi = new System.Globalization.NumberFormatInfo();

nfi.CurrencyDecimalDigits = 0;

nfi.CurrencySymbol = "$";


In the ASPX page to print the number in currency format use the following code

<%#String.Format(nfi,"{0:C}", Convert.ToDecimal (DataBinder.Eval(Container.DataItem,"Price"))) %>

Dynamically Changing the WebService

In Web.Config file you need to add the alternate Webservice URL in appSettings

<appSettings>

<add key="AlternateWebServiceURL" value="http://alternatewebservices.com/webservice.asmx"/>

appSettings>

C# Code to dynamically change the WebService

MyWebServiceClass myWebServiceInstance = new MyWebServiceClass ();

myWebServiceInstance.Url = System.Configuration.ConfigurationManager.AppSettings ["AlternateWebServiceURL"];

Selecting Top 'N' number of records from SQL

The following SQL script will help you to retrieve TOP ‘N’ records from SQL table using the stored procedure

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SelectTopN]

(

@TopN int

)

AS

SET NOCOUNT ON;

SELECT ColumnName1, ColumnName2

FROM (

SELECT ROW_NUMBER() OVER (ORDER BY [ColumnName])AS Row, * FROM (

SELECT ColumnName1, ColumnName2

FROM [TableName]

)

AS UserQuery)

AS SelectionWithRowNumbers

WHERE Row >0 AND Row<=@TopN

Reading datagrid values using Javascript

JavaScript function:

function readDataGrid()
{
var gridContent = document.getElementById('MyDataGrid');
for(var i=0;i< gridContent.rows.length;i++)
{
for(var j=0;j< gridContent.rows[i].cells.length;j++)
{
This iterates through row\cells
//gridContent.rows[i].cells[j].innerHTML;
}
}
}