Iroshan's Blog

App.Config

2010-07-15T17:21:00.000-07:00

sAttr = ConfigurationManager.AppSettings.Get("Key0");

DB Class

2010-07-15T17:20:00.000-07:00

using System;
using System.Data;
using System.Data.SqlClient;

public class DataManager
{
public DataManager()
{
}

public static DataTable ExecuteQuery(string query)
{
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

try
{
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet ds = new DataSet();
adapter.Fill(ds);

return ds.Tables[0];
}
finally
{
if ( connection.State == ConnectionState.Open )
connection.Close();
}
}

public static void ExecuteNonQuery(string query)
{
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

try
{
SqlCommand cmd = new SqlCommand();
cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;

cmd.ExecuteNonQuery();
}
finally
{
if ( connection.State == ConnectionState.Open )
connection.Close();
}
}

public static object ExecuteScalar(string query)
{
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

try
{
SqlCommand cmd = new SqlCommand();
cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;

return cmd.ExecuteScalar();
}
finally
{
if ( connection.State == ConnectionState.Open )
connection.Close();
}
}
}

Remove RowGUID columns

2009-11-03T01:38:00.000-08:00

-- drop rowguid indexes
select 'drop index ' + sysobjects.name + '.' + sysindexes.name from
sysindexes
inner join sysobjects
on sysindexes.id = sysobjects.id
where objectproperty(object_id(sysobjects.name),'IsMSShipped') = 0
and sysindexes.indid > 0 and sysindexes.indid < 255 and (sysindexes.status &
64)=0
and index_col(sysobjects.name, sysindexes.indid, 1) = 'rowguid'
order by sysindexes.indid

-- remove rowguid default constraints
select 'alter table ' + b.name + ' drop constraint ' + QUOTENAME(a.name) from
sysobjects a
inner join syscolumns on syscolumns.id = a.parent_obj
inner join sysobjects b on syscolumns.id = b.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(b.name),'IsMSShipped') = 0
and a.xtype = 'D'

-- remove rowguid columns
select 'alter table ' + sysobjects.name + ' drop column '+QUOTENAME(syscolumns.name) from
syscolumns
inner join sysobjects on syscolumns.id = sysobjects.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(sysobjects.name),'IsMSShipped') = 0

Take database to sigle user mode

2009-07-13T21:17:00.000-07:00

ALTER DATABASE TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Take back in to multi user

ALTER DATABASE TEST SET MULTI_USER

Walk-through of CLR Integration with SQL Server

2009-06-09T22:35:00.000-07:00

One of the project requirements is writing all the stored procedures and user-defined functions in CLR integration. Although I don't intent to give you the overview of CLR integration in this article, it's not a bad idea to go over the benefits you get from using CLR integration. Stored procedures and user-defined functions/types/aggregates authored in managed code compiles into native code prior to execution, and you can achieve significant performance increases in scenarios where complex computations or business logics are involved. When you create these CLR objects, you can leverage various functions and services CLR provides for the program execution, such as JIT, thread handling, memory allocating, type conversion etc.

The following step-by-step example will show you the common tasks performed in CLR integration projects.

* Enabling CLR integration in SQL Server
* Create a SQL Server Project in Visual Studio 2005/2008
* Create a managed stored procedure
* Deploy the CLR assembly using Visual Studio
* Execute CLR objects
* Deploy assemblies onto production environment

The Northwind database will be used in the example I created. If you do not have the Northwind database, go ahead and download it here and restore it to your SQL Server 2005/2008 instance.

Enabling CLR integration in SQL Server

CLR integration is disabled by default in the SQL Server 2005/2008. To enable it, connect to the database to which you want to deploy the CLR assembly, execute the SQL statements below and then restart the SQL server instance.

EXEC sp_configure 'clr enabled', '1'

reconfigure

Create a database project in Visual Studio 2005/2008

Creating a CLR project in Visual Studio 2005/2008 is beyond simple. Go to File --> New Project --> Visual C#, and then select "SQL Server Project". Rename the project to NorthwindOrdersCLR.

CreateProject

Click on OK. You will be prompted to add a database reference. Go ahead and select the Northwind database. If it is not already listed in the dialog, add the connection to the Northwind database.

AddDBRef

Click on Ok. The SQL Server project should be created by Visual Studio.

Create a managed stored procedure

Before we go any further, let's spend a few minutes going over the task we try to implement using the CLR integration. Suppose a business partner of Northwind is placing orders in raw XML messages and the order data needs to be parsed from the XML messages and then be populate into the Northwind..Orders table .

Let's go back to the SQL Server project we just created. Right click the project file NorthwindOrdersCLR, select Add New Item, choose Stored Procedure from the "Add new item" dialog, rename the stored procedure to ParseOrders.cs and then hit OK.

CreateSP

Visual Studio generates the stub for the stored procedure for you automatically.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void ParseOrders()

{

// Put your code here

}

};

In CLR, stored procedures are implemented as public static methods of a .NET class. The static methods can either be declared as void, or return an integer value, and the static methods must be marked with the "SqlProcedure" attribute, which is under the Microsoft.SqlServer.Server namespace.

In order to parse the XML message, we need to reference the System.Xml namesapce. Also, we are going to pass a string parameter to to our stored procedure. The string will be the raw message of orders from the Northwind's business partner. The stored procedure stub should look like this after the tweaks.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Xml;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void ParseOrders(string orderXML)

...

}

So far so good. Now we don't need to look at the problem from a database developer's point of view any more. We are back to what we are good at - programming in .NET. We can write this stored procedure in the way we write .NET clients to access the SQL database: create a database connection, create a SqlCommand with the SQL statement for inserting data, add parameters to the command, parse the XML message, and then call ExecuteNonQuery. In addition, we will benefit from the power of try-catch blocks so we can handle the exceptions in the .NET way.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Xml;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void ParseOrders(string orderXML)

{

XmlDocument xd = new XmlDocument();

using (SqlConnection sc = new SqlConnection("context connection=true"))

{

using (SqlCommand cmdInsert = new SqlCommand("INSERT INTO Orders (CustomerID, EmployeeID, OrderDate) VALUES (@OrderID, @EmployeeID, @OrderDate)", sc))

{

try

{

xd.LoadXml(orderXML);

sc.Open();

XmlNode orderNode = xd.DocumentElement;

cmdInsert.Parameters.Add("@CustomerID", SqlDbType.NChar, 5).Value = xd["CustomerID"].InnerText;

cmdInsert.Parameters.Add("@EmployeeID", SqlDbType.Int, 4).Value = Convert.ToInt32(xd["EmployeeID"].InnerText);

cmdInsert.Parameters.Add("@OrderDate", SqlDbType.DateTime, 8).Value = Convert.ToDateTime(xd["OrderDate"].InnerText);

cmdInsert.ExecuteNonQuery();

}

catch (XmlException xe)

{

SqlContext.Pipe.Send("Error parsing XML message: " + xe.Message);

}

catch (SqlException ex)

{

SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);

}

finally

{

sc.Close();

}

}

}

}

};

Although the code is pretty self-descriptive and you get the idea of exactly what this stored procedure does, I do need to mention a couple of things here.

First, the connection string I used to create the SqlConnection looks different from what we usually do in a traditional .NET application. The problem of internal data access is a fairly common scenario. That is, you wish to access the same server on which your CLR stored procedure or function is executing. One option is to create a connection using SqlConnection, specify a connection string that points to the local server, and open the connection. This requires specifying credentials for logging in. The connection is in a different database session than the stored procedure or function, it may have different SET options, it is in a separate transaction, it does not see your temporary tables, and so on. If your managed stored procedure or function code is executing in the SQL Server process, it is because someone connected to that server and executed a SQL statement to invoke it. You probably want the stored procedure or function to execute in the context of that connection, along with its transaction, SET options etc. This is called the context connection. The context connection lets you execute Transact-SQL statements in the same context that your code was invoked in the first place. In order to obtain the context connection, you must use the "context connection" connection string keyword, as in the code snippet above.

The second thing is that I used SqlContext.Pipe.Send several times in the code. For CLR database objects running in SQL Server, you can send results to the connected pipe using the Send methods of the SqlPipe object. Access the Pipe property of the SqlContext object to obtain the SqlPipe object. The SqlPipe class is conceptually similar to the Response class found in ASP.NET. This is extremely useful when you need to watch the execution steps of CLR objects. We will look at a couple of examples of how this works when we run the stored procedure.

Deploy the CLR assembly using Visual Studio

Now that we have created the managed stored procedure to handle the orders in XML format, let's deploy it into SQL server to test it out. Deploying a CLR assembly in Visual Studio cannot be easier. All you have to do is right click the NorthwindOrdersCLR project and choose "Deploy".

Deploy

You may receive an error message saying "The current database compatibility level does not support managed objects." This is because the Northwind database was created in SQL Server 2000 and CLR integration only works with SQL Server 2005/2008. We'll fix this quickly by changing the compatibility level of the database to SQL Server 2005(90). Re-deploy the CLR project after you have changed the compatibility level.

image

Visual Studio should report "Deploy succeeded" if no errors have occurred. Open SQL Server Management Studio, expand the Northwind database's Programmability node, and you should see the ParseOrders stored procedure listed under "Stored Procedures" and the NorthwindOrdersCLR assembly under the "Assemblies" node.

image

Execute CLR objects

CLR objects can be executed the same way native SQL objects are executed. Simply run the following SQL statement to test our ParseOrders.

exec ParseOrders 'FRANK12008/05/02'

If you've been following my code so far, you should receive the "Query executed successfully" message when you executed the query above. We can verify the data in the Orders table by executing a quick SELECT statement.

SELECT * FROM orders WHERE OrderID = (SELECT MAX(OrderID) FROM Orders)

The new order with CustomerID "FRANK" is shown in the result set.

image

I mentioned earlier that the SqlPipe.Send method was very useful for tracing the execution of CLR objects. We can demonstrate this by creating an exception and letting it get caught by the "catch" block, where I called the SqlContext.Pipe.Send to print out the exception message. Execute the SQL statement below.

exec ParseOrders 'FRANK123452008/05/02'

EmployeeID "12345" doesn't exist in the Northwind..Employees table, so this statement will violate the foreign key constraint on the EmployeeID column in Northwind..Orders. We expect to see the exception message coming out of the pipe when this query finishes.

image

Nice and easy!!! The query executed successfully although the insert command failed due to the constraint. The failure was caught by our SqlException catch block and we got a friendly error message.

Deploy CLR assemblies into production environment

It is easy to deploy CLR assemblies with the aid of Visual Studio. But in the production environment, you probably cannot even connect to the SQL server from your Visual Studio. You will need to use the CREATE ASSEMBLY statement in SQL to deploy the assembly. First, copy the compiled DLL file into a path that the SQL Server is able to access. For example, copy the NorthwindOrdersCLR.dll file (located in the bin\debug or bin\release folder under your project folder) to c:\temp on the production SQL server. Open the SQL Management Studio, connect to the production SQL server, and execute the following SQL statement. This will create the CLR assembly in your production database.

CREATE ASSEMBLY NorthwindOrderCLR from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

The "WITH PERMISSION_SET" option sets the permission the assembly can have when it executes on the SQL server. SAFE is applied by default if not specified otherwise, and SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. Another two possible values for PERMISSION_SET are EXTERNAL_ACCESS and UNSAFE. EXTERNAL_ACCESS enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.
UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code as well. SAFE is highly recommended by Microsoft.

CLR Triggers for SQL Server 2005

2009-06-09T21:19:00.000-07:00

Now Microsoft SQL Server 2005 is integrated with Microsoft .NET Framework Common Language Runtime (CLR), so we can use any .NET Framework language to create database objects. The CLR provides the execution environment for all the server side objects that are created using a .NET language. This means the database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. Especially when working with large amounts of server code, developers can easily organize and maintain their code investments.

This article covers different types of triggers supported by Microsoft SQL Server 2005 with basic ideas about them with an example. It then describes the step-by-step approach to create a CLR trigger (a DML type).

What is Trigger?

A trigger is a Database object just like a stored procedure or we can say it is a special kind of Stored procedure which fires after (/before) a specified language event executes. More specifically, it is for the object which is attached to a Table or View or Database schemas for tracking the operations on them. The main difference between a trigger and a stored procedure is that the former is attached to a table or view and is fired only when an INSERT, UPDATE, and/or DELETE occurs, while a stored procedure executes at any time when it is called.

Types of Triggers

There are some added types in SQL Server 2005 for triggering actions:

1. DML Triggers

· AFTER Triggers

· INSTEAD OF Triggers

2. DDL Triggers

3. CLR Triggers
DML Triggers

These triggers are fired when a Data Manipulation Language (DML) event takes place. These are attached to a Table or View and are fired only when an INSERT, UPDATE and/or DELETE event occurs. The trigger and the statement that fires it are treated as a single transaction. Using this we can cascade changes in related tables, can do check operations for satisfying some rules and can get noticed through firing Mails. We can even execute multiple triggering actions by creating multiple Triggers of same action type on a table. We have to specify the modification action(s) at the Table level that fires the trigger when it is created.

AFTER Triggers

As the name specifies, AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. This is the only option available in earlier versions on Microsoft SQL Server. AFTER triggers can be specified on tables only. Here is a sample trigger creation statement on the Users table.

Listing 1 (AFTER Trigger example)

------ Creating a DML trigger in T-SQL -------
SET NOCOUNT ON
CREATE TABLE UserTable (User_ID int IDENTITY, User_Name varchar(30), Type varchar(10))
go
CREATE TRIGGER tr_User_INSERT
ON UserTable
FOR INSERT
AS
PRINT GETDATE()
Go
INSERT UserTable (User_Name, Type) VALUES ('James', 'ADMIN')

------ Result ---------------
Apr 30 2007 7:04AM

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

DDL Triggers

DDL triggers are new to SQL Server 2005. This type of triggers, like regular triggers, fire stored procedures in response to an event. They fire in response to a variety of Data Definition Language (DDL) events. These events are specified by the T-SQL statements that are start with the keywords CREATE, ALTER, and DROP. Certain stored procedures that perform DDL-like operations can also fire this. These are used for administrative tasks like auditing and regulating database operations.

CLR Triggers

A CLR triggers can be any of the above, e.g. can be a DDL or DML one or can also be an AFTER or INSTEAD OF trigger. Here we need to execute one or more methods written in managed codes that are members of an assembly created in the .Net framework. Again, that assembly must be deployed in SQL Server 2005 using CREATE assembly statement.

The Microsoft.SqlServer.Server Namespace contains the required classes and enumerations for this objective.

Steps for Creating CLR Trigger

The following are required steps for creating a CLR-Trigger of DML (After trigger) type for Insert action. This database Object is executed as the result of a user action against a table i.e. for an INSERT statement.

· Creating a .NET class of triggering action

· Making assembly(.DLL) from that Class

· Enabling CLR environment in that database.

· Registering the assembly in SQL Server

· Creating CLR Trigger using that assembly

1. Creating a .NET class

Here we can use any managed language that is supported by .Net Framework such as C++, C#, VB, J#, JScript or XAML, etc. As I am with VB, this managed code is in Visual Basic. Let us discuss the objective of this entity. According to the above example of "tr_User_INSERT" trigger, we have the UserTable for holding the user details. There is a field "Type" which explains the user role (ADMIN, End User, Register User etc.). Our objective is to check the role of the inserted User for ADMIN type and then do the action as we set.

Open the notepad, copy the following codes and save it as MyFirstAssembly.vb.

Listing 2 (.NET Class of Trigger type)

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Partial Public Class MyFirstClrTrigger
= "checkUserRole", Target
= "UserTable", Event
= "FOR INSERT")> _
Public Shared Sub checkUserRole()

Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()
Dim userType As String = String.Empty

If triggContext.TriggerAction = TriggerAction.Insert Then
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim sqlComm As New SqlCommand
Dim sqlPipe As SqlPipe = SqlContext.Pipe()

sqlComm.Connection = connection
sqlComm.CommandText = "SELECT Type from INSERTED"

userType = sqlComm.ExecuteScalar.ToString()

If userType.ToUpper = "ADMIN" Then

sqlPipe.Send("Hello !!! You have the Admin role.")
sqlPipe.Send("We can use e-mail codes here to inform.")

End If
End Using
End If
End Sub

End Class

Let us go into the codes. There are two major Namespaces used, System.Data.SqlClient and Microsoft.SqlServer.Server. Microsoft.SqlServer.Server provides the SqlTriggerAttribute Class, which is used to mark a method definition in an assembly as a trigger in SQL Server. The Sqltrigger attribute requires some parameters to set the created trigger properties, such as Name - the name of the Trigger, Target - the table or view to which the trigger applies (in case of DML type) and Event - the event to fire the trigger for (e.g. FOR INSERT, DELETE and/or UPDATE or INSTEAD OF etc.). Again, that method must be a Static (Shared in VB) one; here checkUserRole() is the target method.

The SqlTriggerContext class provides the required triggering properties of the Trigger for doing action. TriggerAction property of this Class is the global enumeration TriggerAction type of Microsoft.SqlServer.Server namespace which indicates what action fired the Trigger.

Here we use the INSERTED table, which is automatically created and managed by SQL Server 2005. This is used to set the conditions of DML trigger action. There is also another called DELETED, used in case of delete action. The CLR triggers can access the Inserted or Deleted tables through SqlCommand object using context connection.

2. Making assembly (.DLL)

Now we have created the MyFirstAssembly.vb file with MyFirstClrTrigger class, so we need to compile the class to create an assembly. Here I use the VB compiler "vbc.exe," found in .Net Framework library. Use the following DOS command for creating assembly.

Listing 3 (compile the .VB file)

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>vbc
/t:library /out:F:\CLR_Trigger_Test\CLR_Assembly\MyFirstAssembly
F:\CLR_Trigger_Test\MyFirstClrTrigger.vb

This command creates a DLL called MyFirstAssembly.dll at out path F:\CLR_Trigger_Test\CLR_Assembly\.

3. Enabling CLR environment

By default, the CLR functionality is trued off in SQL Server; so we need to enable it. Use the following T-SQL codes to enable the CLR functionality.

Listing 4 (enable CLR action)

------ Enabling CLR action in database -------
sp_configure 'clr enabled', 1
Go
RECONFIGURE with Override
Go

4. Registering the assembly

Our next step is to create an assembly in the Database, based on the compiled DLL (MyFirstAssembly.dll). The following T-SQL codes are useful regarding this objective.

Listing 5 (registering assembly)

------ Registering an Assembly -------
Create Assembly UserAssembly
From 'F:\CLR_Trigger_Test\CLR_Assembly\MyFirstAssembly.dll'
With Permission_Set=Safe
Go

For more information about this check my previous article.

5. Creating CLR Trigger

Now we will create an extended Trigger using CREATE Trigger statement of T-SQL. There is a new clause named EXTERNAL NAME in SQL Server 2005, which allows us to reference a method of the Registered assembly. By doing so we set the triggering action of our Trigger using that managed code method of the assembly.

Listing 6 (creating a Trigger)

------ Creating a DML trigger in CLR -------
Create Trigger tr_User_CheckRole
on UserTable
For INSERT
AS
External Name UserAssembly.MyFirstClrTrigger.checkUserRole
Go

Here we use the checkUserRole() shared method of our MyFirstClrTrigger class of the registered assembly UserAssembly.

Checking the triggering action

Finally, we need to check our Trigger. Let us test the trigger using the same INSERT Statement that we used before.

Listing 7 (Insert into UserTable)

----- Checking the DB objects.----------------
INSERT UserTable (User_Name, Type) VALUES ('James', 'ADMIN')
Go
----- Output Message -----
May 2 2007 8:54AM
Hello !!! You have the Admin role.
We can use e-mail codes here to inform.

The output is the result of two triggering actions; one is of T-SQL type and another one is of CLR type. Both are attached to the UserTable for the same type of action (i.e. after Insert statement). The following image is the output result window of SQL Server Management studio.

Conclusion

Now we have our CLR Trigger in our database with source code stored externally. Hence, externally stored code objects can be more secure than the previous one created by T-SQL. Also now we have the power and expressiveness of .NET language for our database objects. Above all, now we have a brief idea about all types of Triggers supported by SQL Server 2005.

How to Create a SQL Server 2005 Database Snapshot

2009-06-09T21:14:00.000-07:00

Microsoft SQL Server administrators has a new feature with the SQL Server 2005 Edition which is database snapshots. Database snapshots are new with SQL Server 2005 and only available with Microsoft SQL Server 2005 Enterprise Edition. SQL Server database administration and development with SQL Server especially for sql reporting will be easier with ms sql server database snapshots.

A database snapshot can be described as a photo of a database. Snapshots are read-only so we can easily say that their main usage areas cover mostly the reporting applications. Database snaphots are one to one the same of the original database at the time when the snapshot is created. After a snapshot is created it stays as a static view of the original database at the time of snapshot creation. A snapshot can persists until it is dropped. Also multiple snapshots can be created with different names of a database called as source database.

In order to create a database snapshot, CREATE DATABASE statement is used.

How to Create a SQL Server 2005 Database Snapshot

Microsoft SQL Server administrators has a new feature with the SQL Server 2005 Edition which is database snapshots. Database snapshots are new with SQL Server 2005 and only available with Microsoft SQL Server 2005 Enterprise Edition. SQL Server database administration and development with SQL Server especially for sql reporting will be easier with ms sql server database snapshots.

A database snapshot can be described as a photo of a database. Snapshots are read-only so we can easily say that their main usage areas cover mostly the reporting applications. Database snaphots are one to one the same of the original database at the time when the snapshot is created. After a snapshot is created it stays as a static view of the original database at the time of snapshot creation. A snapshot can persists until it is dropped. Also multiple snapshots can be created with different names of a database called as source database.

In order to create a database snapshot, CREATE DATABASE statement is used.

Here is a sample database snapshot create script of the sample database AdventureWorks

First, in order to place the data files of the snapshot, we should define the physical path of the container folder

We can use the source database's data folder for the same purpose for snapshot database

select physical_name from sys.database_files WHERE file_id = 1

The above query will display where the .mdf file exists. So we can use the same folder. I'm going to use "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"

Considering the below note from Books On Line;

When you create a database snapshot, the CREATE DATABASE statement cannot reference log files, offline files, restoring files, and defunct files.

I run the below select statement to get a list of data files that I should reference

select name from sys.database_files WHERE type_desc = 'ROWS'

The returned row set only contains the "AdventureWorks_Data"

Now, we are ready to create a snapshot of the source database.
CREATE DATABASE AdventureWorks_SS ON
(
NAME = AdventureWorks_Data,
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.ss'
)
AS SNAPSHOT OF AdventureWorks

After the above command is completed successfully, we can see the snapshot database in the Database Snapshots folder of the SQL Server Instance

In order to delete or remove a database snapshot after it is not needed any more, you can DROP it by using the DROP DATABASE command

DROP DATABASE AdventureWorks_SS

Note that if database snapshot creation fails, the snapshot is in suspect status and it should be deleted or dropped.

I believe, sql server programmers as well as database administrators will benefit more from Microsoft database snapshots. Especially when sql server performance is an issue than sql server snapshots may be a solution especially for distinguishing reporting databases from the transactional production databases. Administrators or developers do not need to backup and restore sql databases for creating a second database for sql server reporting services, for instance.

Update

2009-06-05T12:20:00.001-07:00

string conString = GetConnectionString();
SqlConnection con = new SqlConnection(conString);
con.Open();
string sql = "UPDATE HumanResources.Employee SET ContactID = "+custObj.ContactID+", ManagerID = "+custObj.ManagerID+" WHERE EmployeeID = " + ID;
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
con.Close();

Grid View Cell double click

2009-06-05T11:57:00.000-07:00

private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
string ss = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();

string gs = dataGridView1.Rows[e.RowIndex].Cells["EmployeeID"].Value.ToString();

Class1 test = new Class1();
DataSet ds = test.GetbyID(gs);

textBox1.Text = ds.Tables[0].Rows[0][0].ToString();
textBox2.Text = ds.Tables[0].Rows[0][1].ToString();

}

Data Retival

2009-06-05T11:55:00.000-07:00

SqlConnection con = new SqlConnection(conString);

string sql = "Select * from HumanResources.Employee";

SqlCommand cmd = new SqlCommand(sql, con);
ds = new DataSet();
SqlDataAdapter adpt = new SqlDataAdapter(cmd);

adpt.Fill(ds);

Nested Case statements

2009-04-03T01:57:00.000-07:00

(CASE
WHEN custid is not NULL THEN custid
WHEN accountid is not null THEN accountid
Else refID
END) AS TranNo

Get Computer Serial number..

2009-02-21T21:10:00.000-08:00

With "GetVolumeInformation", the signature looks as follows:

[DllImport("kernel32.dll")]
private static extern long GetVolumeInformation(string PathName, StringBuilder VolumeNameBuffer, UInt32 VolumeNameSize, ref UInt32 VolumeSerialNumber, ref UInt32 MaximumComponentLength, ref UInt32 FileSystemFlags, StringBuilder FileSystemNameBuffer, UInt32 FileSystemNameSize);

Note that some of the parameters that you would think would be of type "int" must be passed as "UInt32", which corresponds to the .NET type "uint", and oftentimes a string must be passed as StringBuilder.

The actual method , with a string return value for convenience, looks like this:

public string GetVolumeSerial(string strDriveLetter)
{
uint serNum = 0;
uint maxCompLen = 0;
StringBuilder VolLabel = new StringBuilder(256); // Label
UInt32 VolFlags = new UInt32();
StringBuilder FSName = new StringBuilder(256); // File System Name
strDriveLetter+=":\\"; // fix up the passed-in drive letter for the API call
long Ret = GetVolumeInformation(strDriveLetter, VolLabel, (UInt32)VolLabel.Capacity, ref serNum, ref maxCompLen, ref VolFlags, FSName, (UInt32)FSName.Capacity);

return Convert.ToString(serNum);
}

Creating Non-Rectangular Forms

2009-01-13T02:53:00.000-08:00

This is Taken from Net and this is only for educational purpose only

It is amazingly easy to create forms of any size and shape within Visual C#. You can even do it with VB .NET! For the most part, this can be broken down into the following steps:

Create a background image for the form

Set background of image to a color for transparency

Create a project in Visual Studio .NET

Set a couple of property on the form to set the transparency color and to turn off the title bar.

The following walks you through each of these steps.

Creating the Background Image

To create a non-rectangular form, the first step is to create an image that will be used for your background. You can use any program to create your image as long as you can save into a standard format. In general, I use Microsoft Paint since it comes with Windows.

Open Paint and draw an image for your form. I created the image shown in Figure 1 as my non-rectangular form. Be aware, I am a developer and not a graphics artist!

Figure 1: The initial drawing of the non-rectangular form.

It is by using transparency that a non-rectangular form is created. Once you've created your form in your drawing program, you will need to set the remaining background of your image to a color that will be made transparent. In Microsoft Paint, you can select and use a color that you know will not be used elsewhere within your application for forms, buttons, controls, or anything else that will be displayed.

Rather than selecting one of the existing colors, I tend to create a custom color to use. That way I'll know its exact value. To do this in page, select Colors | Edit Colors... to get the Edit Colors Dialog. You can then click on the Define Custom Colors >> button to expand out the dialog so that you can create a custom color. Within the expanded dialog (shown in Figure 2) I use the values of 100 for Red, 0 for Green, and 0 for Blue. This produces a maroon looking color.

Figure 2: Creating a custom color in Microsoft Paint.

Clicking OK saves this color. You can then use it to fill the background of your image. I've done this as shown in Figure 3.

Figure 3: The non-rectangular form with a custom background color.

Now that you have an image to use as your form, save it so that you can use it within Visual Studio .NET

Creating the Visual Studio .NET Project

Open Visual Studio .NET and create a new project. You can create either a C# Windows form or a Visual Basic Windows Form project. I use C# in this article, but the steps are identical for Visual Basic .NET.

Your project will start out with the standard rectangular Windows form. This, however, will be changed.

Select the form and then display the properties. Change the BackgroundImage property the form you created. You may need to then resize the form displayed in the Design editor so that your entire background image is displayed. I've done this in Figure 4 with my form.

Figure 4: The non-rectangular form image within the Design editor of Visual Studio .NET 2005.

In order to drop out the custom color you created, you need to set the TransparencyKey property. To set this property, click on the down arrow next to the entry box. This will display a color dialog with tabs. Select the Custom tab to ensure that the dialog displays similarly to Figure 5.

Figure 5: The Custom tab in the TransparencyKey color selection dialog box.

Right click on one of the empty color boxes in this dialog. This will bring up the dialog for defining a custom color similar to what you saw in Microsoft Paint. Set the colors to the same values you did in Paint (Red 100, Green 0, Blue 0). Click the Add Color button to save this color. This also sets the TransparencyKey property to the color you created.

Now that you've set the background color to be transparent, the remaining step is to get rid of a few items that will make your form looking goofy. Because you are using a non-rectangular form, the Title bar will no longer look good floating above your form. Additionally, your form is not set up to be resized. As such, you should set the FormBorderStyle property to the value of None.

Because you've now turned off the title bar, you will need to make sure your form has a way to exit. For the example I created in the attached zip, I simply dropped a button onto the form and put an Application.End() command into it.

You should now be able to build and run your form as I've done in Figure 6. Of course, the colors looked okay on the screen. The dithering is a result of my capturing the image.

Figure 6: The final form on Windows.

In Conclusion...

In conclusion, creating a custom form is simple. Once you've done the above process, you can drag and drop any controls and features onto your form as you'd like. Creating the rest of a form's functionality does not change from the way you've done it before.

Granted, there are a few things you may want to do such as create non-rectangular buttons, add drag&drop functionality, resize the form, and add other title button functionality back onto the form. This can all be done, but they are tasks for other articles!

How to Convert in SQL

2009-01-07T22:22:00.000-08:00

Convert(Data Type, Field Name)

Eg: Convert(int,Qty)

How to find the number of feilds in a table

2008-12-29T22:18:00.000-08:00

select COUNT(*) as ColumnCount from information_schema.columns
where table_name = 'table_name'

Visual Studio .NET 2003 Keyboard Shortcuts

2008-12-22T21:01:00.000-08:00

http://www.codinghorror.com/blog/files/Visual%20Studio%20.NET%202003%20Keyboard%20Shortcuts.htm

More date formats that does not come standard in SQL Server as part of the CONVERT function

2008-12-16T03:21:00.000-08:00

¹ To make the month name in upper case, simply use the UPPER string function.


SQL Statement	Sample Output
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]	99-01-24
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]	1999-01-24
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]	08/99
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	12/2005
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	99/08
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2005/12
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 2006 ¹
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006 ¹
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2006 ¹
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September ¹
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11 ¹
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 72 ¹
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002 ¹
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]	12/92
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	05-2006
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]	92/12
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2006-05
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252006
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072002
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02 ¹
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002 ¹
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05 ¹
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-2005 ¹

SQL Server Date Formats

2008-12-16T02:56:00.000-08:00


SQL Statement	Sample Output
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2005 1:29PM ¹
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/98
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/1998
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	72.01.01
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]	1972.01.01
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	19/02/72
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]	19/02/1972
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2005
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]	24-01-98
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]	24-01-1998
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 06 ¹
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2006 ¹
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 98 ¹
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 1998 ¹
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2006 12:32:29:253PM ¹
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]	01-01-06
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]	01-01-2006
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]	98/11/23
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]	1998/11/23
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]	980124
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]	19980124
SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2006 00:34:55:190 ¹
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]	11:34:23:013
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)	1972-01-01 13:42:24
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)	1972-02-19 06:35:24.489
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)	1998-11-23T11:25:43:250
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2006 12:39:32:429AM ¹
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2006 12:39:32:429AM

Forcing console logins when the server exceeds its terminal services connections

2008-12-14T21:21:00.000-08:00

MsTSC /Console

Check for an Integer Value-Without Exception

2008-12-11T00:29:00.000-08:00

bool isvalid;
int number;

isvalid = int.TryParse(textBox1.Text,out number);

if (isvalid)
{
MessageBox.Show("Number");
}
if (isvalid == false)
{
MessageBox.Show("Not a number");
}

How to get configuraion Settings

2008-12-08T22:13:00.000-08:00

It is not enough to add the reference in the code. You have to manually add the reference using Add reference. If not ConfigurationManager wont visible.

using System.Collections.Specialized;
using System.Collections;
using System.Configuration;

NameValueCollection appSettings = ConfigurationManager.AppSettings;

string userName = appSettings["UserName"].ToString();

RUN a SP

2008-12-08T20:28:00.000-08:00

SqlConnection con=new SqlConnection("User ID=" + ConfigurationSettings.AppSettings.Get("UserName").ToString() + ";Password=" + ConfigurationSettings.AppSettings.Get("Password").ToString() + ";Initial Catalog=" + ConfigurationSettings.AppSettings.Get("DBName").ToString() + ";Data Source=" + ConfigurationSettings.AppSettings.Get("ServerName").ToString() + ";Persist Security Info= true;");
con.Open();
SqlCommand cmd=new SqlCommand("SPNAME",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@SPPARAM", PARAM));
cmd.CommandTimeout=0;
SqlTransaction t=con.BeginTransaction();
cmd.Transaction=t;

try
{
cmd.ExecuteNonQuery();
t.Commit();
con.Close();
}
catch(Exception ex)
{
t.Rollback();
con.Close();
string s = ex.Message;
}

Creating a transaction

2008-12-08T20:25:00.000-08:00

SqlConnection con=new SqlConnection("User ID=" + ConfigurationSettings.AppSettings.Get("UserName").ToString() + ";Password=" + ConfigurationSettings.AppSettings.Get("Password").ToString() + ";Initial Catalog=" + ConfigurationSettings.AppSettings.Get("DBName").ToString() + ";Data Source=" + ConfigurationSettings.AppSettings.Get("ServerName").ToString() + ";Persist Security Info= true;");
con.Open();
SqlCommand cmd=new SqlCommand(sql,con);
cmd.CommandTimeout=0;
SqlTransaction t=con.BeginTransaction();
cmd.Transaction=t;
try
{
SqlDataAdapter adpt=new SqlDataAdapter(cmd);

adpt.Fill(dsTemp);
t.Commit();
con.Close();
return ds;
}
catch(Exception ex)
{
t.Rollback();
con.Close();
return new DataSet();
}

Nested Case sql

2008-10-20T05:03:00.000-07:00

(CASE
WHEN(Status = 'Goods Issued')
OR (Status = 'Partially Issued')
THEN IssuedQty
ELSE '0' END)
AS QTYISSUED

How to get length of data in Text, NText and Image columns in SQL Server

2008-10-20T02:49:00.000-07:00

Problem
There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?

Solution
In addition to the LEN() function, SQL Server also has a DATALENGTH() function. This function can be used on all data types in your table.

Here is an example of running these commands on an IMAGE data type using the LEN function:

SELECT name, LEN(packagedata)
FROM dbo.sysdtspackages

query results

Here is an example of running these commands on an IMAGE data type using the DATALENGTH function:

SELECT name, DATALENGTH(packagedata)
FROM dbo.sysdtspackages

query results

If you wanted to find out the maximum length used for all of your records you could issue a command such as the following:

SELECT TOP 1 name, DATALENGTH(packagedata)
FROM dbo.sysdtspackages
ORDER BY 2 DESC

That is all there is to it. Another little function that you probably won't use often, but this is helpful to know it is there when you do need to use it.

Working with Timer Control in C#

2008-05-12T22:46:00.000-07:00

The Timer control allows you to set a time interval to execute an event after that interval continuously. It is useful when you want to execute certain applications after a certain interval. Say you want to create a backup of your data processing in every hour. You can make a routine which will take the backup and call that routine on Timer's event and set timer interval for an hour.

Using timer control is pretty simple. To test the control, I'm going to create a Windows Application. I also add two button controls to the form and change their text to Start and Stop as you can see from the following Figure.

In this application, I'm going to create a text file mcb.txt in your C:\temp directory and start writing the time after 5 seconds interval. Stop button stops the timer and Start again start the timer.

Now you can drag a timer control from the toolbox to the form. You can set timer properties from the IDE as well as programmatically. To set the timer properties, right click on the timer control and change Interval property. As you can see from the Figure,

I put 5000 milliseconds (5 seconds).

I sec = 1000 milliseconds (In case your mathematics is poor as mine ;).

Now click on the Events button and write event for the timer click as you can see from the following figure.

Now I add a FileStream and a StreamWriter object in the beginning of the class. As you can see from the following code, FileStream class creates a mcb.txt file and StreamWriter will be used to write to the file.

private static FileStream fs = new FileStream(@"c:\temp\mcb.txt", FileMode.OpenOrCreate, FileAccess.Write);
private static StreamWriter m_streamWriter = new StreamWriter(fs);

Now write the following code on the Form Load event:

private void Form1_Load(object sender, System.EventArgs e)
{
// Write to the file using StreamWriter class
m_streamWriter.BaseStream.Seek(0, SeekOrigin.End);
m_streamWriter.Write(" File Write Operation Starts : ");
m_streamWriter.WriteLine("{0} {1}", DateTime.Now.ToLongTimeString(),DateTime.Now.ToLongDateString());
m_streamWriter.WriteLine(" ===================================== \n");
m_streamWriter.Flush();
}

As you can see from the above code, this code writes some lines to the file.

Now write code on the start and stop button click handlers. As you can see from the following code, the Start button click sets timer's Enabled property as true. Setting timer's Enabled property starts timer to execute the timer event. I set Enabled property as false on the Stop button click event handler, which stops executing the timer tick event.

private void button1_Click(object sender, System.EventArgs e)
{
timer1.Enabled = true;
}
private void button2_Click(object sender, System.EventArgs e)
{
timer1.Enabled = false;
}

Now last step is to write timer's tick event to write current time to the text file. Write the following code on your timer event.

private void timer1_Tick(object sender, System.EventArgs e)
{
m_streamWriter.WriteLine("{0} {1}", DateTime.Now.ToLongTimeString(),DateTime.Now.ToLongDateString());
m_streamWriter.Flush();
}

Now use Start and Stop buttons to start and stop the timer. The output mcb.txt file looks like the following figure.

Using Timer control Programmatically

If you don't have Visual Studio .NET, you can also write the same sample. Just following these steps.

Creating an instance of Timer

The Timer class's constructor is used to create a timer object. The constructor is overloaded.

Public Timer()
Public Timer(double) Sets the interval property to the specified.

Here is how to create a Timer with 5 seconds interval.

Timer myTimer = new Timer(500);

Here are some useful members of the Timer class:

Tick	This event occurs when the Interval has elapsed.
Start	Starts raising the Tick event by setting Enabled to true.
Stop	Stops raising the Tick event by setting Enabled to false.
Close	Releases the resources used by the Timer.
AutoReset	Indicates whether the Timer raises the Tick event each time the specified Interval has elapsed or whether the Tick event is raised only once after the first interval has elapsed.
Interval	Indicates the interval on which to raise the Tick event.
Enabled	Indicates whether the Timer raises the Tick event.

How to use Timer class to raise an event after certain interval?

timer1.Interval = 5000;
timer1.Enabled = true;
timer1.Tick += new System.EventHandler (OnTimerEvent);

Write the event handler

This event will be executed after every 5 secs.

public static void OnTimerEvent(object source, EventArgs e)
{
m_streamWriter.WriteLine("{0} {1}", DateTime.Now.ToLongTimeString(),DateTime.Now.ToLongDateString());
m_streamWriter.Flush();
}

This is originally posted by Mahesh Chand I used this for one of my projects.Hope this will help you all!!!!!

Creating a Windows Service in C#

2008-05-12T22:36:00.000-07:00

NOTE: The project name in this sample code is mcWebService which is a spelling mistake. I meant to put mcWinService. And now I don't want to change all the screen shots and code all over again. I hope it won't confuse you :).

Ok, its time for one more tutorial. This times pick is Windows Services. Creating Windows Services is not a big deal using C# and Visual Studio. Just follow few simple steps and you are all set to run and test your first Windows Service.

Windows Services is new name for NT Services you used to develop in previous versions of Visual Studio. This tutorial walks you through how to create and use your Windows Services. This Service writes some text to a text file when stop and start the service. The base idea is taken from MSDN but its more elaborated. You can modify it according to your needs.

Step 1. Create Skeleton of the Service

To create a new Window Service, pick Windows Service option from your Visual C# Projects, give your service a name, and click OK.

The result look like this. The Wizard adds WebService1.cs class to your project.

Set your ServiceName to your own name so it would be easier to recognize your service during testing OR you can set this property programmatically using this line this.ServiceName = "mcWinService";

This is the name you will be looking for later :).

The default code of WebService1.cs added by the Wizard looks like here

namespace mcWebService
{
using System;
using System.Collections;
using System.Core;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Web.Services;
using System.Diagnostics;
using System.ServiceProcess;
public class WinService1 : System.ServiceProcess.ServiceBase
{
///

/// Required designer variable.
///

private System.ComponentModel.Container components;
public WinService1()
{
// This call is required by the WinForms Component Designer. InitializeComponent();
// TODO: Add any initialization after the InitComponent call
}
// The main entry point for the process
static void Main()
{
System.ServiceProcess.ServiceBase[] ServicesToRun;
// More than one user Service may run within the same process. To add
// another service to this process, change the following line to
/ create a second service object. For example,
//
// ServicesToRun = New System.ServiceProcess.ServiceBase[] {new WinService1(), new
ySecondUserService()};
//
ServicesToRun = new System.ServiceProcess.ServiceBase[] { new WinService1() };
System.ServiceProcess.ServiceBase.Run(ServicesToRun);
}
///

/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///

private void InitializeComponent()
{
components = new System.ComponentModel.Container();
this.ServiceName = "WinService1";
}
///

/// Set things in motion so your service can do its work.
///

protected override void OnStart(string[] args)
{
// TODO: Add code here to start your service.
}
///

/// Stop this service.
///

protected override void OnStop()
{
// TODO: Add code here to perform any tear-down necessary to stop your service.
}
}
}

Step 2. Add functionality to your service

As you saw WebService1.cs, there are two overridden functions OnStart and OnStop. The OnStart function executes when you start your service and the OnStop function gets execute when you stop a service. I write some text to a text file when you start and stop the service.

protected override void OnStart(string[] args)
{
FileStream fs = new FileStream(@"c:\temp\mcWindowsService.txt" ,
FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter m_streamWriter = new StreamWriter(fs);
m_streamWriter.BaseStream.Seek(0, SeekOrigin.End);
m_streamWriter.WriteLine(" mcWindowsService: Service Started \n");
m_streamWriter.Flush();
m_streamWriter.Close();
}
///

/// Stop this service.
///

protected override void OnStop()
{
FileStream fs = new FileStream(@"c:\temp\mcWindowsService.txt" ,
FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter m_streamWriter = new StreamWriter(fs);
m_streamWriter.BaseStream.Seek(0, SeekOrigin.End);
m_streamWriter.WriteLine(" mcWindowsService: Service Stopped \n"); m_streamWriter.Flush();
m_streamWriter.Close();
}

Step 3: Install and Run the Service

Build of this application makes one exe, mcWinService.exe. You need to call installutil to
egister this service from command line.
installutil C:\mcWebService\bin\Debug\mcWebService.exe
You use /u option to uninstall the service.
installutil /u C:\mcWebService\bin\Debug\mcWebService.exe
Run the application

Step 4: Start and Stop the Service

You need to go to the Computer Management to Start to start and stop the service. You can use Manage menu item by right clicking on My Computer.

Under Services and Applications, you will see the service mcWinService. Start and Stop menu item starts and stops the service.

Step 5: Test the Service

Go to your temp directory and see if text file is there with contents or not.

That's it.

This is originally posted by Mahesh Chand I used this for one of my projects hope this will be use full for you all!!!

Hidden Skype Emoticons, Smileys(Sri Lankan Flag)

2008-03-05T22:13:00.000-08:00

Hi Guys,

Wanna Show Sri Lankan Flag when you are chatting. Here what you have to do .Type (flag:lk) Thats it.

Hidden Skype Emoticons, Smileys

2008-03-05T21:29:00.000-08:00

Did you know that Skype have hidden emoticons? No! Below is a list of the all hidden emoticons that I know and use chatting with my friends.

How to use hidden emoticons? Actually it is pretty simple - you just need to enter the smiley code while chatting with your friends - for example type ‘(drunk)’ to display smiley who have had couple beers :)

Icon	Name	Type
	Mooning	(mooning)
	Finger	(finger)
	Bandit	(bandit)
	Drunk	(drunk)
	Smoking	(smoking)(smoke)(ci)
	Toivo	(toivo)
	Rock	(rock)
	Headbang	(headbang)(banghead)
	Bug	(bug)
	Fubar	(fubar)
	Poolparty	(poolparty)
	Swearing	(swear)
	TMI	(tmi)
	Heidy	(heidy)
	MySpace (Skype v3.6)	(myspace)

Skype emoticons (bandit) (drunk) (finger) (headbang) (banghead) (mooning) (rock) (smoking) (smoke) (ci) (toivo) work with Skype 2.5 and 3.0!

Smileys (bug) (fubar) (poolparty) (swear) (tmi) work only with Skype 3.0 and up!

How to Get "Show Hidden Files and Folders" Back if it is Missing(Edit Registry Another Fix)

2008-03-05T20:48:00.000-08:00

Start >> Run >> (type) regedit >> (rightclick on the little my computer icon,
and export the whole thing.)

find this key: HKEY_CURRENT_USER\Software\Microsoft\Windows\
CurrentVersion\Explorer\Advanced. Go to the rightside of the window,
where the values are. Find the value "Hidden" . Rightclick it and modify it to 1.

How to Get "Show Hidden Files and Folders" Back if it is Missing

2008-03-05T20:43:00.000-08:00

If the option to enable "Show Hidden Files and Folders" is MISSING from your Folders Options > View > Advanced Settings, find this key in your registry:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows
\CurrentVersion\Explorer\Advanced\Folder\Hidden]

Modify the string value "Type" and enter "group" as value data.

After the edit is complete hit F5 to refresh, and go look in your Folder Options.

USB Drive AutoRun.inf Tweaking

2008-02-27T22:36:00.000-08:00

The autorun.inf file is the key to getting your USB drive (or CD-ROM drive, for that matter) to perform certain actions automatically and customize it’s look in My Computer. The purpose of this article is to shed some light on how this can be done.

Autorun.inf Structure

The autorun.inf file is a simple text file that can be opened up in any text editor (e.g. notepad). It always starts with a section header of:

[autorun]

Below this header is a list of different options. Each of these options is in the following format:

option=value

where

option is the option that you want to set and value is the value that you are setting for that option. So, if you had an option foo and you wanted to be set to bar, then you would enter:

foo=bar

(Do not use foo=bar in your autorun.inf file as it is only an example, not a real option setting.)

That is all there really is to understand about the structure of an autorun.inf file. On to doing some actual cool stuff with it!

Setting a Custom Icon

To create a custom icon for your USB drive, use the icon option. Set it to the name of the icon file.

Note: Since drive letters can change for USB drives, the file path is relative to the root of the drive. This means that if your USB drive is presently mounted on U: and your icon is located at U:\Icons\MyIcon.ico, then you would enter \Icons\MyIcon.ico for the value of this option.

For example, if you had an icon on the root of the USB drive called coffeecup.ico and you wanted this to be the icon that showed up for the USB drive, you would enter:

icon=coffeecup.ico

You are not limited to .ico files. If, for example, you have an executable with a nice icon, you can specify it as the icon file. For example:

icon=DCoTopen.exe

This is valid as long as DCoTopen.exe is available on the root of the USB drive.

Some files have more than one icon embedded in them. If this is the case, you can select which icon to use by specifing the index number after the file name. For example:

icon=iconlib.dll,2

This will use the second icon in the iconlib.dll file.

Naming Your USB Drive

If you would like your USB drive to display a specific name othr than the drive label created when it is formatted, use the label option. For example, if I wanted to call my drive DCoT Drive, I would add this to my autorun.inf file:

label=DCoT Drive

Now, when you look at your USB drive in My Computer, it will say DCoT Drive by the drive letter.

Setting AutoPlay Options

AutoPlay is a relatively new function of Windows XP. It allows you to set up what file is run when the USB drive is plugged into the computer and the message that you are prompted with. There are two options that work in conjunction with AutoPlay. The first is open. It specifies the program that you can run automatically with AutoPlay. So, if we wanted to run a program called DCoTopen.exe, you would add the this to your autorun.inf file:

open=DCoTopen.exe

The second option that we add is the message the user is prompted with. To set this, we use the action option. If we want the message to say DCoT Open Program, add the following to autorun.inf:

action=DCoT Open Program

Once you have added this information, AutoPlay should look something like this:

Adding Context Menu Items

There are certain basic options such as Open and Explore that are available when you right click on a USB drive. But, wouldn’t it be cool to add your own? You can using a couple of lines in the autorun.inf file.

The first thing that we need to do is create an action, give it a name, and a message. We do all of this using the shell\verb option. For example, let’s say that we would like to create an action called lost. It does not matter what the actin is called. It can be anything you want. We would also like to show the message Help! I’m Lost! in the context menu. We would simply add this line to autorun.inf:

shell\lost=Help! I'm Lost!

This will display Help! I’m Lost! in the context menu so that you can click on it. But, it doesn’t know what to do when you click on it. Tell the system by using shell\verb\command option. In our example, we want to run the Lost.exe application. Adding this line will do the trick:

shell\lost\command=Lost.exe

You can add as many of these line pairs as you want to make the context menu as custom as you want.

Changing Default Action

When you double click on your USB drive, by default it will open up the drive so that you can browse through the files. Often, it is advantageous to perform some other action when the user double clicks the USB drive icon. You do this with the shell option. If we wanted to run the Lost.exe program from the previous section automatically when we double clicked on the USB drive, we would add this line:

shell=lost

because lost is the name of the action that was specified in the earlier lines.

Viewing a File

If you wanted to view a file on your USB drive in the default application instead of running a program on the drive, you can substitute the open option for the shellexecute option. For example, if you wanted to open up a website called, oh, I don’t know, say http://www.DailyCupOfTech.com in the default web browser, you could user the following:

shellexecute=http://www.DailyCupOfTech.com

This will work for any file. This is the equivalent of using Start - Run… and then typing in a file name and clicking OK.

School’s Out, Time To Play!

That is about all there is to customizing the autorun.ing file. Now that you know what you are doing, it’s time to start playing! I have created the Autorun Dabbler’s Toolkit that you can play with.

Download the zip file and extract the contents to the root of an empty USB drive. You can now edit the autorun.inf and get it to do different things. Have fun!

Speed Up Your PC

2008-02-26T23:34:00.000-08:00

5 ways to speed up your PC

By following a few simple guidelines, you can maintain your computer and keep it running smoothly. This article discusses how to use the tools available in Windows XP Service Pack 2 (SP2) and Windows Vista to more efficiently maintain your computer and safeguard your privacy when you're online.

Free up disk space

By freeing disk space, you can improve the performance of your computer. The Disk Cleanup tool helps you free up space on your hard disk. The utility identifies files that you can safely delete, and then enables you to choose whether you want to delete some or all of the identified files.
Use Disk Cleanup to:

•	Remove temporary Internet files.
•	Remove downloaded program files (such as Microsoft ActiveX controls and Java applets).
•	Empty the Recycle Bin.
•	Remove Windows temporary files.
•	Remove optional Windows components that you don't use.
•	Remove installed programs that you no longer use.

Tip: Typically, temporary Internet files take the most amount of space because the browser caches each page you visit for faster access later.

To use Disk Cleanup

1.	Click Start, point to All Programs, point to Accessories, point to System Tools, and then click Disk Cleanup. If several drives are available, you might be prompted to specify which drive you want to clean.
2.	In the Disk Cleanup for dialog box, scroll through the content of the Files to delete list. Choose the files that you want to delete.
3.	Clear the check boxes for files that you don't want to delete, and then click OK.
4.	When prompted to confirm that you want to delete the specified files, click Yes.

After a few minutes, the process completes and the Disk Cleanup dialog box closes, leaving your computer cleaner and performing better.

Speed up access to data

Disk fragmentation slows the overall performance of your system. When files are fragmented, the computer must search the hard disk when the file is opened to piece it back together. The response time can be significantly longer.

Disk Defragmenter is a Windows utility that consolidates fragmented files and folders on your computer's hard disk so that each occupies a single space on the disk. With your files stored neatly end-to-end, without fragmentation, reading and writing to the disk speeds up.

When to run Disk Defragmenter
In addition to running Disk Defragmenter at regular intervals—monthly is optimal—there are other times you should run it too, such as when:

•	You add a large number of files.
•	Your free disk space totals 15 percent or less.
•	You install new programs or a new version of Windows.

To use Disk Defragmenter:

1.	Click Start, point to All Programs, point to Accessories, point to System Tools, and then click Disk Defragmenter. Click Analyze to start the Disk Defragmenter.
2.	In the Disk Defragmenter dialog box, click the drives that you want to defragment, and then click the Analyze button. After the disk is analyzed, a dialog box appears, letting you know whether you should defragment the analyzed drives. Tip: You should analyze a volume before defragmenting it to get an estimate of how long the defragmentation process will take.
3.	To defragment the selected drive or drives, click the Defragment button. Note: In Windows Vista, there is no graphical user interface to demonstrate the progress—but your hard drive is still being defragmented. After the defragmentation is complete, Disk Defragmenter displays the results.
4.	To display detailed information about the defragmented disk or partition, click View Report.
5.	To close the View Report dialog box, click Close.
6.	To close the Disk Defragmenter utility, click the Close button on the title bar of the window.

Detect and repair disk errors

In addition to running Disk Cleanup and Disk Defragmenter to optimize the performance of your computer, you can check the integrity of the files stored on your hard disk by running the Error Checking utility.

As you use your hard drive, it can develop bad sectors. Bad sectors slow down hard disk performance and sometimes make data writing (such as file saving) difficult, or even impossible. The Error Checking utility scans the hard drive for bad sectors, and scans for file system errors to see whether certain files or folders are misplaced.

If you use your computer daily, you should run this utility once a week to help prevent data loss.

To run the Error Checking utility:

1.	Close all open files.
2.	Click Start, and then click My Computer.
3.	In the My Computer window, right-click the hard disk you want to search for bad sectors, and then click Properties.
4.	In the Properties dialog box, click the Tools tab.
5.	Click the Check Now button.
6.	In the Check Disk dialog box, select the Scan for and attempt recovery of bad sectors check box, and then click Start.
7.	If bad sectors are found, choose to fix them.

Tip: Only select the "Automatically fix file system errors" check box if you think that your disk contains bad sectors.

Protect your computer against spyware

Spyware collects personal information without letting you know and without asking for permission. From the Web sites you visit to usernames and passwords, spyware can put you and your confidential information at risk. In addition to privacy concerns, spyware can hamper your computer's performance. To combat spyware, you might want to consider using Microsoft Windows Defender, which is included in Windows Vista, and is available as a free download for Microsoft XP SP2. Alternatively, there are other free anti-spyware software programs available.

Learn all about ReadyBoost

If you're using Windows Vista, you can use ReadyBoost to speed up your system. A new concept in adding memory to a system, it allows you to use non-volatile flash memory—like a USB flash drive or a memory card—to improve performance without having to add additional memory.

This articl is From Microsoft.