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 :)
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 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.
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))
As
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.
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.
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);
}
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.
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.
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 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/