How To Insert Data Into An Identity Column



TODO:

Have you ever wanted to insert data into a table, and specify an identity column.

 

SOULTION:

 

SET IDENTITY_INSERT MyTable ON

--do my inserts

SET IDENTITY_INSERT MyTable OFF

 

 

NOTES:

There are no notes on this topic.

How To Find Foreign Key Dependancies



TODO:

Have you ever wanted to find Foreign Key dependancies using T-SQL?

 

SOLUTION:

SELECT fk.name, OBJECT_NAME(fk.parent_object_id) as DepTable
FROM sys.foreign_keys fk
inner join sys.tables st on st.[object_id] = fk.referenced_object_id
WHERE st.name = 'MyTable'

NOTES:

No notes apply to this topic.

How To Restore A Database SQL Server



TODO:

Have you ever wanted to restore a database using SQL commands

 

SOLUTION:

restore database MyDB from disk = N'g:\MyDB.bak' 
	WITH MOVE 'MyDB_data' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.X\MSSQL\DATA\MyDB.mdf',
	MOVE 'MyDB_log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.X\MSSQL\DATA\MyDB.ldf'

NOTES:

No notes apply to this topic.

Cisco VPN interfering with ability to ping other computers on LAN



TODO:

Have you had issues with computers not being able to see or ping each other on your LAN? 

Example:  Computer 1 cannot ping Computer 2, but Computer 2 can ping Computer 1.

 

SOLUTION:

Open Cisco VPN Client.  Got to Options.  Uncheck the Stateful Firewall check box.

You should now be able to ping in both directions.

 

NOTES:

This post only applies if you are running Cisco VPN Client.

How To Find An Object In A Strongly Typed List ( List<T> )



TODO

Have you ever wanted to find an object in a list of objects?  Did you know you do not need to loop through the list to do so?

 

SOLUTION:

C#

SalesOrder foundSalesOrder = SalesOrderList.SalesOrders.Find(delegate(SalesOrder so) { return so.SalesOrderId == 123; });

 

NOTES:

SalesOrderList is a List<SalesOrder>

SalesOrder is a class that has contains sales order info (ex SalesOrderId, Amount, SaleDate etc.)

How To Find Or List All Column Names and Information For A Table Using T-SQL



TODO:

Have you ever wanted to get a list of all of the columns in a database table, and their details, ordered by name?

 

SOLUTION:

 

SELECT 
   ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,ISNULL(CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH),'')
  ,IS_NULLABLE
  ,ISNULL(COLUMN_DEFAULT,'')
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  TABLE_NAME = 'tbl_name_here' 
ORDER BY 
  COLUMN_NAME ASC;

 

NOTES:

No notes on this topic.

Re-Identity a Table in SQL Server



TODO:  Have you ever wanted to re-indentity a column in SQL Server?

 

SOLUTION

T-SQL

 

DECLARE @Count INT
SET @Count = 0

UPDATE [dbname].[dbo].[table]
SET  @Count = [id] = @Count+1

 

 

NOTES:

Identity Insert must be enabled.

"Id" is the name of the identity column.

Delete Duplicate Records From A Table



TODO:

Have you ever wanted to remove duplicate entries from a table using T-SQL?

 

SOLUTION:

 

BEGIN TRANSACTION

Delete from MyTable where row_id <
(Select Max(row_id) from MyTable mt where MyTable.Keywords = mt.Keywords)

--Verify data, then un-comment the commit
--COMMIT

 

 

NOTES:

This example deletes rows with duplicate 'Keywords' data.  'Keywords' is my column name.

Handy C# Regular Expressions



TODO:

Have you ever been looking for handy RegExp's.  I have grabbed a few I use from time to time and have put them below.

 

SOLUTION:

 

//Find first non-digit
Regex firstNonDigitIndexRegexp = new Regex(@"\D",RegexOptions.RightToLeft);

//Test if a string ends with a digit
Regex endsWithDigitRegexp = new Regex(@"\d$");      

//Test if a string starts with a digit
Regex startsWithDigitRegexp = new Regex(@"^\d");

//Find the last digit in a string
Regex lastDigitIndexRegexp = new Regex(@"\d", RegexOptions.RightToLeft);

//Test if a string contains only digits
Regex numbersOnlyRegExp = new Regex(@"\D");

//Validate an email address
public bool ValidateEmail(string  EmailAddress)
{
     string patternToMatch = @"^([a-zA-Z0-9_\+\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
     System.Text.RegularExpressions.Match match = Regex.Match(EmailAddress, patternToMatch, RegexOptions.IgnoreCase);
     return match.Success;
}

// Validate a URL
public static bool IsUrlValid(string url)
{
     //ensure we have a valid prefix
     if(!Regex.IsMatch(url, @"^(http|https)\:(\\\\|//)", RegexOptions.IgnoreCase))            
          url = String.Concat("http://", url);   //default to http

     string pattern = @"^(http|https)\://[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}(:[a-zA-Z0-9]*)?/?([a-zA-Z0-9\-\._\?\,\'/\\\+&%\$#\=~])*[^\.\,\)\(\s]$";
     Regex reg = new Regex(pattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);
     return reg.IsMatch(url);
}

 

 

NOTES:

No notes apply on this topic.

How To Call A .Net Web Service from cURL / PHP



TODO:

Have you ever wanted to post an image to a .Net Web Service using cURL and PHP?

 

SOLUTION:

 

<?php
	$ch = curl_init();
	curl_setopt($ch, CURLOPT_HEADER, 0);
	curl_setopt($ch, CURLOPT_VERBOSE, 0);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/4.0 (compatible;)");
	curl_setopt($ch, CURLOPT_POST, true);
	curl_setopt($ch, CURLOPT_URL, 'http://my.service.com/MyService.asmx/MyMethod' );

	$post_array = array(
				"FileName"=>'test.jpg',
				"ImageData"=>'@C:/test.jpg',
				"Param1"=> 'data1',
				"Param2"=> 'data2'
	);

	curl_setopt($ch, CURLOPT_POSTFIELDS, $post_array); 
	$response = curl_exec($ch);
	print_r($response);
?>

NOTES:

.Net Web Service Method should be parameterless, and use the HttpContext and HttpFileCollection objects.