How to Reseed a SQL Server Identity Column

In SQL Server you can specify a column as an Identity column (ColumnName int IDENTITY(1,1) NOT NULL) to have SQL Server automatically set the value of this column upon insert by incrementing a seed value.  This works great but I have run into situations where I wanted to change what identity value is assigned during the next insert. 

What had happened is that this table in question had the primary key column set as an Identity column.  There were only a couple thousand rows in the table but SQL Server was handing out identity values in the 10-million range.  In most cases this would not be a big deal, but for this table the identity value was actually used by customers so a eight digit number was not as easy to remember and repeat as a 4-digit number. 

What had happened is that a row had been inserted into the table with a high identity value (over 10 million) and then SQL Server took over from there and handed out new identity values that were incremented from that number – 23000001, 23000002, 23000003, etc...

To fix this, I was able to change the seed of the Identity field by using a DBCC CHECKIDENT statement.

You can use this DBCC CHECKIDENT statement to check the current identity value:

DBCC CHECKIDENT('table_name', NORESEED)

And then you can use this DBCC CHECKIDENT statement to change the identity value to another value:

DBCC CHECKIDENT('table_name', RESEED, 2300)

After making this change new records inserted into the table now were assigned 4-digit values and were much easier for customers to remember and use.

 

Read More

Build your .NET project with a right click in Windows Explorer

MSBuildShellExtension lets you build .NET projects without ever opening Visual Studio or the command prompt. MSBuild targets can be executed from your favourite file system tool like Windows Explorer or Total Commander. The possibility to extend MSBuildShellExtension with your own targets and editors, makes it a very flexible and useful tool for all .NET developers.

Read More

25 Visual Studio 2008 IDE Tips

0. Know your Keybindings! All these are in the General Development Settings.

Searching

1. How to behold the power of incremental search

http://blogs.msdn.com/saraford/archive/2007/07/23/did-you-know-behold-the-power-of-incremental-search.aspx

Command: Edit.IncrementalSearch

Shortcut: Ctrl+i

2. Ctrl+F3 to search for currently-selected word

http://blogs.msdn.com/saraford/archive/2007/10/26/did-you-know-ctrl-f3-searches-for-the-currently-selected-string-without-brining-up-the-find-window.aspx

Command: Edit.FindNextStatement

3. F3 to search for last thing you searched for

http://blogs.msdn.com/saraford/archive/2007/10/25/did-you-know-f3-searches-for-the-last-thing-you-searched-for.aspx

Command: Edit.FindNext

4. Customize what files to find in

http://blogs.msdn.com/saraford/archive/2007/11/07/did-you-know-how-to-customize-what-files-to-find-in.aspx

Find In Files – Look in – Choose Search Folders

5. You can use a reg hack for customizing search results

http://blogs.msdn.com/saraford/archive/2008/11/24/did-you-know-you-can-customize-how-search-results-are-displayed-in-the-find-results-window-363.aspx

HKCU\Software\Microsoft\VisualStudio\9.0\Find String Find=$f$e($l,$c):$t\r\n

Editing

6. How not to accidentally copy a blank line

http://blogs.msdn.com/saraford/archive/2007/09/28/did-you-know-how-not-to-accidentally-copy-a-blank-line.aspx

Tools – Options – Text Editor – All Languages – General, Uncheck Apply cut or copy to blank lines

7. How to cycle through the Clipboard ring

http://blogs.msdn.com/saraford/archive/2007/10/01/did-you-know-how-to-cycle-through-the-clipboard-ring-to-paste-different-things.aspx

Command: Edit.CycleClipboardRing

Shortcut: Ctrl+Shift+v

8. How to use box/column selection in the editor

http://blogs.msdn.com/saraford/archive/2007/07/27/did-you-know-how-to-do-box-selection-in-the-editor.aspx

Command: Edit.LineUpColumnExtend, Edit.LineDownColumnExtend, Edit.CharRightColumnExtend, Edit.CharLeftColumnExtend

Shortcut: Shift+Alt+Arrow

9. You can copy a file’s full path / open windows explorer from the file tab channel

http://blogs.msdn.com/saraford/archive/2008/01/09/did-you-know-you-can-copy-a-file-s-full-path-from-the-file-tab-channel.aspx

Command: File.CopyFullPath

10. Drag and drop code onto the toolbox’s general tab

http://blogs.msdn.com/saraford/archive/2008/04/10/did-you-know-you-can-drag-and-drop-code-onto-the-toolbox-general-tab-191.aspx

11. You can use Ctrl+. to show a smart tag

http://blogs.msdn.com/saraford/archive/2008/11/18/did-you-know-ctrl-shows-a-smart-tag-359.aspx

Command: View.ShowSmartTag

12. You can insert a snippet by pressing Tab Tab

http://blogs.msdn.com/saraford/archive/2008/06/10/did-you-know-you-can-insert-a-snippet-via-tab-tab-234.aspx

Type in snippet shortcut, then press Tab Tab

Customizing

13. You can create temp or throw away projects

http://blogs.msdn.com/saraford/archive/2008/02/25/did-you-know-you-can-create-temp-or-throw-away-projects-158.aspx

Tools - Options - Projects and Solutions - General, uncheck Save new projects when created

14. Change text editor font size via keyboard (Accessibility macros)

http://blogs.msdn.com/saraford/archive/2008/06/20/did-you-know-you-can-bind-macros-to-keyboard-shortcuts-or-how-to-quickly-increase-decrease-your-text-editor-font-size-242.aspx

Command: Macros.Samples.Accessibility.DecreaseTextEditorFontSize

Command: Macros.Samples.Accessibility.IncreaseTextEditorFontSize

15. How to open a file without any UI

http://blogs.msdn.com/saraford/archive/2007/11/26/did-you-know-how-to-have-fun-with-the-find-combo-box.aspx

Ctrl+/ (or whatever Tools.GoToCommandLine is bound to)

alias fo file.openfile

fo <filename>

16. Guidelines in the editor registry key hack

http://blogs.msdn.com/saraford/archive/2008/04/01/did-you-know-you-can-display-guidelines-in-the-editor-and-tip-of-the-day-ends-today-184.aspx

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\Text Editor  String RBG(128,0,0) 5, 20

17. You can create a macro for your import/export settings – see http://blog.jeffhandley.com/archive/2009/03/31/vs-profiles.aspx

http://blogs.msdn.com/saraford/archive/2008/12/05/did-you-know-you-can-create-toolbar-buttons-to-quickly-toggle-your-favorite-vs-settings-371.aspx

18. How to not show the start page (or have the last loaded solution open)

http://blogs.msdn.com/saraford/archive/2008/01/03/did-you-know-how-to-customize-what-visual-studio-opens-to-or-how-to-make-the-start-page-not-show-up-when-vs-opens.aspx

Tools - Options - Environment - Startup, At Startup

19. File tab channel registry hack

http://blogs.msdn.com/saraford/archive/2008/10/09/did-you-know-you-can-keep-recently-used-files-from-falling-off-the-file-tab-channel-331.aspx

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0 key, you can create a DWORD UseMRUDocOrdering = 1

20. How to show Misc Files Project to keep your files around

http://blogs.msdn.com/saraford/archive/2008/01/01/did-you-know-how-to-show-the-miscellaneous-files-project-in-the-solution-explorer.aspx

tools - options - environment – documents, show miscellaneous files in Solution Explorer

21. Edit project file from within IDE (unload project)
http://blogs.msdn.com/saraford/archive/2008/10/10/did-you-know-how-to-edit-a-csproj-or-vbproj-project-file-within-the-ide-332.aspx

Unload project, edit project, reload project

Debugging

22. You can use tracepoints to log stuff in your code

http://blogs.msdn.com/saraford/archive/2008/06/13/did-you-know-you-can-use-tracepoints-to-log-printf-or-console-writeline-info-without-editing-your-code-237.aspx

Right-click in indicator margin, select breakpoints, select Insert Tracepoint

23. How to get the find source dialog back

http://blogs.msdn.com/saraford/archive/2008/09/17/did-you-know-how-to-get-the-find-source-dialog-back-instead-of-the-there-is-no-source-code-available-message-315.aspx

Solution Properties, under Common Properties - Debug Source Files, Delete Do no look for these source files edit box contents

24. You can disable the exception assistant

http://blogs.msdn.com/saraford/archive/2008/08/05/did-you-know-you-can-disable-the-exception-assistant-274.aspx

Tools – Options – Debugging – General, uncheck Enable the Exception Assistant

25. You can use the XML Visualizer

http://blogs.msdn.com/saraford/archive/2008/09/25/did-you-know-you-can-use-the-xml-visualizer-to-view-xml-321.aspx

Drop down the little down arrow on the data tip or in the watch window.

More

Read More

How To Publish an ASP.NET Website from a Command Line

To test the tools which we develop on the team, at times I need to build a website and publish it. I use a simple way of publishing websites from the command line that saves me a LOT of time so thought I would share it.

Launch notepad and copy paste the code below and save it as Publish.cmd file. Run visual studio command prompt (as administrator) and run the publish.cmd.

 

   1: @ECHO OFF

   2: set WEB_ROOT=C:\inetpub\wwwroot\mytestsite

   3: set PROJECT_ROOT=D:\Source\website

   4: 

   5: echo Publishing site %PROJECT_ROOT% to %WEB_ROOT%

   6: 

   7: del /S /Q %WEB_ROOT%\*.* || goto Error

   8: rmdir /S /Q %WEB_ROOT%\ || goto Error

   9: aspnet_compiler -p "%PROJECT_ROOT%" /v /commercesite /d "%WEB_ROOT%" || goto Error

  10: 

  11: goto Success

  12: :Error

  13: echo Site was not published

  14: 

  15: goto End

  16: 

  17: :Success

  18: echo Site published successfully

  19: 

  20: :End

If there aren’t any errors the site will be published successfully as shown below

C:\Windows\system32>D:\Source\website\publish.cmd
Publishing site D:\Source\website to c:\inetpub\wwwroot\mytestsite Utility to precompile an ASP.NET application
Copyright (C) Microsoft Corporation. All rights reserved.

Site published successfully
C:\Windows\system32>

Modify the above script parameters appropriately to publish your site successfully!

 

Read More

Using The ConnectionStringBuilder class

Building a connection string from scratch can sometimes be a little daunting when you do not know the exact syntax. Of course, you can always visit www.connectionstrings.com and find some great help there. In lieu of this you can also use the ConnectionStringBuilder class. Each of the ADO.NET providers supplies a version of this class that will build a connection string for you. Below is an example of how to use this class.

VB.NET
Imports System.Data.SqlClient

Private Sub CreateConnectionString()
  Dim builder As New SqlConnectionStringBuilder

  builder.DataSource = "(local)"
  builder.InitialCatalog = "Northwind"
  builder.UserID = "user1"
  builder.Password = "P@ssw0rd"

  MessageBox.Show(builder.ConnectionString)
End Sub

C#
using System.Data.SqlClient;

private void CreateConnectionString()
{
  SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

  builder.DataSource = "(local)";
  builder.InitialCatalog = "Northwind";
  builder.UserID = "user1";
  builder.Password = "P@ssw0rd";

  MessageBox.Show(builder.ConnectionString);
}

This is a pretty easy class to use. You can just fill in the basic information such as the DataSource, InitialCatalog, the UserId and Password and it will create a connection string for you. The output from the above code will be: "Data Source=(local);Initial Catalog=Northwind;User ID=user1;Password=p@ssword".

To add on additional keywords for your connection string you may use the Add method. This method takes the keyword and the value and will add them in the appropriate format to your connection string.

As mentioned, each ADO.NET data provider supplies one of these classes. For example, if you are using Oracle, you would use the System.Data.OracleClient namespace, then use the OracleConnectionStringBuilder.

The ConnectionStringBuilder class allows you to parse the individual elements of a connection string and put them into the corresponding properties in the ConnectionStringBuilder. In the following example you take an existing connection string like the one shown in the code below and place it into the ConnectionString property of the ConnectionStringBuilder. The ConnectionStringBuilder will then break it into the appropriate properties.

VB.NET
Private Sub ParseConnectionString()
  Dim cnn As String
  Dim builder As New SqlConnectionStringBuilder

  cnn = "Server=Localhost;Initial Catalog=Northwind;User ID=user1;Password=P@ssw0rd"

  builder.ConnectionString = cnn

  MessageBox.Show(builder.DataSource)
  MessageBox.Show(builder.InitialCatalog)
  MessageBox.Show(builder.UserID)
End Sub

C#
private void ParseConnectionString()
{
  string cnn;
  SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

  cnn = "Server=Localhost;Initial Catalog=Northwind;User ID=user1;Password=P@ssw0rd";

  builder.ConnectionString = cnn;

  Debug.WriteLine(builder.DataSource);
  Debug.WriteLine(builder.InitialCatalog);
  Debug.WriteLine(builder.UserID);
}

So the next time you have a connection string that you wish to extract the elements from, or you need to build a connection string, consider using the ConnectionStringBuilder class. I would not recommend using this in a production application as I can't imagine that the performance would be that great, but for figuring out a connection string, this works great.

 

Read More

Application running in localhost and debugging mode

Most of the time (If not all) when working on the project we view the website with the help of localhost in debugging mode. Many a times when working on some modules, for solving small problem, we want to write some code that should execute only when running in the localhost or debugging environment.

Normally developers would write the code and then take it as their responsibility to remove the code before the code goes to the next environment. But some time we might make a mistake of not removing the code and letting it go to the next environment, which can cause a lots of trouble.

In these kinds of circumstances and Asp.Net developer can take help of the IsLocal method of the Request class. The method will return true in case the application is running from localhost.

If (Request.IsLocal)
{

}

To know if the application is running in the debugging mode, we can use the

If (HttpContext.Current.IsDebuggingEnabled)
{
// Do task that should execute only when Application is running in debugging mode.
}

 

Read More

Using Parameters with Dynamic SQL

Some programming situations require you to use Dynamic SQL. Of course the problem with using Dynamic SQL is that this can lead to SQL Injection attacks. However, you can avoid these problems, by just changing how you submit Dynamic SQL to your back end database.

To illustrate this, consider the sample table of users listed below.

CREATE TABLE User
(
Login char(16) not null primary key,
Password varchar(20) not null
)
go
INSERT INTO User values('PSheriff', 'password')
go
INSERT INTO User values('Keng', 'password')
go

You can copy and paste the above SQL code into your database management system and create this table. After you have created this table, you might create a login screen and use some code like the following to see if the users are in the User table.

Private Sub BADLoginCode()
 Dim sql As String
 Dim cmd As SqlClient.SqlCommand
 Dim rows As Integer

 sql = " SELECT Count(*) As TotalRows FROM User "
 sql &= " WHERE Login = '" & txtLogin.Text & "'"
 sql &= " AND Password = '" & txtPassword.Text & "'"

 cmd = New SqlClient.SqlCommand(sql)
 cmd.Connection = New SqlClient.SqlConnection(AppConfig.ConnectString)
 cmd.Connection.Open()

 rows = Convert.ToInt32(cmd.ExecuteScalar())

 If rows > 0 Then
   MessageBox.Show("Success")
 Else
   MessageBox.Show("Failure")
 End If
End Sub

The problem with the above code is if a hacker where to type in the following into the Login ID field;

' OR 1=1 DELETE FROM Users --

Then the resulting SQL that is submitted to the back end database is the following:

SELECT Count(*) As TotalRows
FROM User
WHERE Login = '' OR 1=1
DELETE FROM User --'
AND Password = ''

As you can see this will not only allow the user into the application, but will also delete all other users in the system! This is not a good thing.

However, with just a very minor change to this code, you can protect yourself against this type of attack. Just like when calling a stored procedure you use Parameters on the command object to submit the login id and password to the input parameters, you can do the same type of coding with dynamic SQL.

Private Sub LoginGood()
 Dim sql As String
 Dim cmd As SqlClient.SqlCommand
 Dim rows As Integer

 sql = " SELECT Count(*) As TotalRows FROM User "
 sql &= " WHERE Login = @sLogin "
 sql &= " AND Password = @sPassword"

 cmd = New SqlClient.SqlCommand(sql)
 cmd.Connection = New SqlClient.SqlConnection(AppConfig.ConnectString)
 cmd.Connection.Open()
 cmd.Parameters.Add(New _
   SqlClient.SqlParameter("@Login", SqlDbType.Char))
 cmd.Parameters.Add(New _
   SqlClient.SqlParameter("@Password", SqlDbType.Char))
 cmd.Parameters.Item("@Login").Value = txtLogin.Text
 cmd.Parameters.Item("@Password").Value = txtPassword.Text

 rows = Convert.ToInt32(cmd.ExecuteScalar())
 If rows > 0 Then
   MessageBox.Show("Success")
 Else
   MessageBox.Show("Failure")
 End If
End Sub

Notice the use of the Parameters in the dynamic SQL. When you now run this code, the command object and the parameters ensure that no SQL injection attacks will affect your code.

 

Read More