Mihai Bejenariu Blog

Design Patterns

Amazing how graceful Martin Fowlers can talk about patterns. I rediscovered with pleasure one of its books:

http://www.martinfowler.com/books.html.

Also, I would like remember here my favorite site about patterns:

http://www.dofactory.com/Patterns/Patterns.aspx

Happy design patterns :)

How to: Killing SQL Server Processes

Inactive processes can quickly eat up connections to your SQL Server databases, dramatically limiting the number of connections available for other work.

Gettting information about locks can be done using sp_lock system procedure.

Kill a process from from Enterprise Manager:
1. Expand a server group, and then expand a server.
2. Expand Management, and then expand Current Activity.
3. Click Process Info. The current server activity is displayed in the details pane.
4. In the details pane, right-click a Process ID, and then click Kill Process.
5. Confirm that the process has terminated.

Also, you can dynamically terminate a particular process by executing a kill statement, such as
kill n

Current process
Use @@SPID for getting the server process identifier (ID) of the current user process.

Sample:
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

NUnitForms - automate your testing

NUnitForms is an NUnit extension for unit and acceptance testing of Windows Forms applications.

Your NUnit tests can open a window and interact with the controls. Your tests will automatically manipulate and verify the properties of the GUI. NUnitForms takes care of cleaning up your forms between tests, detecting and handling modal dialog boxes, and verifying that your expectations for the test are fulfilled.

See below the first NUnitForms test. It shows a form, write a test in textbox control and then it clicks a button.

[TestFixture]
public class BatchSecurityTester: NUnitFormTest
{
            [Test]
            public void AddNewGroup()
            {
                        //Show the form
                        MyForm form = new MyForm();
                        form.Show();

                        //Enter text 'NewGroup' in txtGroupName control
                        TextBoxTester txtGroupName = new TextBoxTester("txtGroupName");
                        txtGroupName.Enter("NewGroup");

                        //Press btnOK control
                        ButtonTester btnOK = new ButtonTester("btnOK");
                        btnOK.Click();
            }
}

Automate testing is a powerful tool complementary to unit tests.
Happy (extreme programming) coding!

P.S. NUnitAsp is the correspondent for ASP.NET application.

SQL User Defined Function to Parse a Delimited String

Below function takes as parameter a text, split the text in tokens, and returns a table containing all this tokens.

CREATE FUNCTION ParseText2File(@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS

BEGIN

declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))

while len(@p_text) > 0 and charindex(@p_delimiter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_delimiter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_delimiter,@p_text))
end
insert @tempResults select @p_text

insert @results
select result
from @tempResults

return
END

Let’s see a practical case when this function can be used. You have a list of products. Each product belongs to a specific category. You need to return all products belonging to a list of categories.

An approach could be to create a stored procedure GetProducts(@categories varchar(4000)) which has a parameter containing all categories ids separated by comma like this: '1,3,6,11'. Using the above function, the solution of our problem is simple:

CREATE PROCEDURE GetProducts(@categories varchar(4000))

SELECT p.productId, p.productName

FROM products p

WHERE p.categoryId IN (SELECT id FROM dbo.ParseText2Table(@categories))

A different approach can be found here: 
http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp

P.S. Of course, you can take in consideration sp_executesql function.

Database backup programatically (using .NET)

Today my boss asked me to add a new feature to the application I work on: to make a database backup every time the application is closed.

I was very surprised to do this in less then 1 hour using SQLDMO library which is shipped with SQL Server 2000. The dll itself is a COM object and you must reference it from your .net project as such. The IDE will create the necessary COM wrappers needed to use the library.

using SQLDMO;

                      //Necessary declarations
                     SQLServer2Class server = new SQLServer2Class();
                     Database2 database;
                     Backup2Class backup = new Backup2Class();

                     //Connect to server
                     server.LoginSecure = true;
                     server.Connect(“server”, “user”, “pass”);

                     //Select database you want to backup
                     database = (Database2) databases.Item(“database_name”, null);
                     backup.Database = database.Name;

//Specify the place where backup to be saved.
backup.Files = “C:\mydb.bak”;

//Effective backup of the database
backup.SQLBackup(server);

//Disconnect, don’t forget it
server.DisConnect();

As you can see, this is a much easier alternative when SQL information or control is needed.

Get list of SQL Server databases

In this post I’ll present you 2 ways to get existing databases on a SQL Server.

First solution is to use sp_databases system procedure. Calling this procedure is made like calling any other stored procedure. Please find below the code snippet.

//Create and open the connection
SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();

//Create and execute the command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_databases";
SqlDataReader myReader = cmd.ExecuteReader();

//Shows all databases
while(reader.Read())
{
Console.WriteLine(reader.GetString(0));
}

The second solution is to use SQL-DMO library. As you may have been maybe expecting, the code is much simpler. You just need to connection to SQL Server, and use SQLServerClass.Databases property. Be aware to add reference to SQLDMO.dll. It might be found at the following path:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SQLDMO.dll

SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
server.Connect(".","sa","sa");
foreach(SQLDMO.Database db in server.Databases)
{
Console.WriteLine(db.Name);
}

Index Tunning Wizard

The Query Analyzer can be used to recommend indexes for specific tables. By entering a query into the Query Analyzer, and running the "Index Tuning Wizard" option, the query will be reviewed, and if appropriate, one or more indexes will be recommended by the Index Wizard. If an index is recommended, Query Analyzer can automatically create the index for you, if you like. This tool will not point out indexes that are not used, nor will it affect any existing indexes.

One way I take advantage of this tool when tuning a query is to run this option as a first step, before I really begin any analysis and work on the query. This way, if there are any obvious indexes needed, they will be found quickly, saving me a little time. Once this step is out of the way, then I run an execution plan of the query, and then look for other ways to tune the query.

Executing TRUCATE TABLE on a remote server

Did you tried to execute TRUNCATE statement against a table on a remote server? TRUNCATE TABLE server01.database01.dbo.Tablename01

Executing the above statement, you’ll get the error message:

Server: Msg 117, Level 15, State 1, Line 2
The object name 'server01.database01.dbo.' contains more
than the maximum number of prefixes. The maximum is 2.

TRUNCATE TABLE isn't a command that you can run directly by using the four-part name associated with a linked server. However, you can issue the TRUNCATE TABLE command against the linked server by using the sp_executesql stored procedure. Sp_executesql is designed primarily to help you parameterize a SQL query so that SQL Server can reuse the plan more easily. However, sp_executesql is also valuable when you're running commands against linked servers.

EXECUTE  sql3.master.dbo.sp_executesql 
	 N'TRUNCATE table tempdb..NewAuthors'

The above statement hows an example of how to use sp_executesql to execute SQL commands on a remote server even if the native commands don't directly support linked servers. Although this example demonstrates the execution of TRUNCATE TABLE, you can use this stored procedure to execute almost any type of SQL command.

Comparing files

One way to compare files is to use hashing. A hash value (also called a message digest) is a number generated from a text. The hash is substantially smaller than the text itself, and is generated by a formula in such a way that it is extremely unlikely that some other text will produce the same hash value.

Please have a look to the following code snippet to see implementation of files comparing with hash mechanism.

using System.IO;
using System.Security.Cryptography;
...
private bool AreFilesIdentical(string source, string target)
{
            //create the hashing object.
            HashAlgorithm alg = HashAlgorithm.Create();

            //Calculate the hash for the first file
            FileStream fsA = new FileStream(source, FileMode.Open);
            byte[] hashA = alg.ComputeHash(fsA);
            fsA.Close();

            //calculate the hash for the second file
            FileStream fsB = new FileStream(target, FileMode.Open);
            byte[] hashB = alg.ComputeHash(fsB);
            fsB.Close();

            //compare the hashes
            return BitConverter.ToString(hashA) == BitConverter.ToString(hashB);
}

Another solution would be to use file metadata information. But you can’t count on it every time. E.g. if you based on file time modification stamp, you might have surprises in case of file originate on system with different time zones.

I’m waiting your suggestions.

Refactoring

Refactoring is a disciplined technique for restructuring an existing body of code, altering its internal structure without changing its external behavior. Its heart is a series of small behavior preserving transformations. Each transformation (called a 'refactoring') does little, but a sequence of transformations can produce a significant restructuring. Since each refactoring is small, it's less likely to go wrong. The system is also kept fully working after each small refactoring, reducing the chances that a system can get seriously broken during the restructuring.

Here it is a list of possible refactorings in Alphabetical Order. Read this carefully and don’t hesitate to refactor your code when you have the opportunity. http://www.refactoring.com/catalog/

ReSharper is a refactoring plugin for Visual Studio to support C# development. It's developed by the same people who built IntelliJ Idea for Java and brings much of its development style to C#. http://www.jetbrains.com/resharper/