C# - Create Windows Service with Timer

Below is an example to create Windows Service with timer operation:

Ref.:
http://www.aspfree.com/c/a/C-Sharp/Timer-Objects-in-Windows-Services-with-C-sharp-dot-NET/1/

1. Use Visual Studio (C#) to select project of "Windows Service"

2. Include namespace of
using System.IO;
using System.Timers;

3. Double click "service1.cs" to change property (please refer to screen capture in above URL, to set nature to Auto-start). Change both of "Name" and "Service Name".

Name of project is "mcs_daily_report"
set name of service to "mcs_report"

3.1 Click on the hyperlink Add Installer (Click to select the file Service1.cs, in the center gray area of Visual Studio, click right button and select "Add Installer"). A design screen will be added to the project with 2 controls: serviceProcessInstaller1 and ServiceInstaller1. Click the serviceProcessInstaller1 control and, in the properties dialog, change the account to LocalSystem.

3.2 In the serviceInstaller control, change the start type to Automatic, and give it a nice display name.

4. Add code as below for timer operation

OnStart()
{ ....
Timer timer = new Timer();

timer.Elapsed += new ElapsedEventHandler(OnElapsedTime);
timer.Interval = 60000;
timer.Enabled = true;
timer.Start();
....
}

public static void OnElapsedTime(object source, ElapsedEventArgs e)
{
DateTime dtNow = System.DateTime.Now;

string strHeader = dtNow.ToString("yyyy-MM-dd HH:mm:ss");
LogToFile(1, strHeader);
}

5. Compile and execute as below for registration:

InstallUtil mcs_daily_report.exe
net start mcs_report

other commands for to remove/stop the service:

net stop mcs_report
InstallUtil /u mcs_daily_report.exe


6. Below are commands which can be used to write log in "Event Log"

public static EventLog g_myLog;

public OnStart()
{
....
this.AutoLog = false; // turn off event log

if (!System.Diagnostics.EventLog.SourceExists("MySource"))
{
System.Diagnostics.EventLog.CreateEventSource("MySource", "MyLog");
}
// configure the event log instance to use this source name
g_myLog = new EventLog();
g_myLog.Source = "MySource";
g_myLog.WriteEntry("In OnStart.");
...
}

C# - example of UTF8 encoding

1) Command to read file which is encoded in UTF8

StreamReader sread = new StreamReader(resStream, Encoding.UTF8);

2) Command to read file whichis encoded in GB2312

StreamReader sread = new StreamReader(resStream, Encoding.GetEncoding("GB2312"));

3) Code below to create a file in UTF8

FileStream hStream = null;
FileInfo hFile = new FileInfo(strPath);

iResult = 0;
try
{
hStream = hFile.Create();

/*
szHeader = new byte[10];
szHeader[0] = 0xef;
szHeader[1] = 0xbb;
szHeader[2] = 0xbf; // or 0xef, 0xbc, 0xbb
hStream.Write(szHeader, 0, 3);
*/
//szMess = new System.Text.UTF8Encoding().GetBytes(strContent);

//false: without UTF-8 BOM header, true:with BOM header
szMess = new System.Text.UTF8Encoding(false).GetBytes(strContent);

hStream.Write(szMess, 0, szMess.Length);
}
catch (Exception ex)
{
....
}

if (hStream != null)
{
hStream.Flush();
hStream.Close();
}

C# - example of UTF8 encoding

SQL - retrieve variable in Stored Procedure

Below are some examples of retrieve content from a table within a SP:

select @iNumRec = count(*) from XXXX where mess_content like '%test%'

select @MessID = a.mess_id from XXXX as a where a.service_timestamp>'2009-12-31'

select @MessID = a.mess_id from XXXX as a order by service_timestamp desc

C# - example of string manipulation

[Convert string to decimal]
string s = "123.45";
decimal de = decimal.Parse(s);

[Convert string to integer]
string s = "123";
int iSIN = Int32.Parse(s);

[Read field of tinyint from DB and convert to int and string]
int iTemp = (int)reader_src.GetOrdinal(g_strField[iLpCnt]);
strTemp = iTemp.ToString();

[Format integer to string with fixed length]
String.Format("{0:X}", iRecID).PadLeft(5, '0')

[Convert byte array to string]
byte [] szContent;
szContent = System.Convert.FromBase64String(strContentIn);
System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
string strLog = enc.GetString(szContent);

SQL - usage of Transaction and Lock

Below is an example of using "Transaction" / "Lock" to update / insert a record

{
Random random = new Random();
strTxID = "TX_" + random.Next(1000).ToString();

strCmd = @"BEGIN TRANSACTION " + strTxID;
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();

// check if record with specified recid already exist
strCmd = "select count(*) as NumRec from XXX with (HOLDLOCK) where SIN='...
cmd.CommandText = strCmd;
iNumRec = (int)cmd.ExecuteScalar();
....

// form value of SID (session ID)
strCmd = "insert into XXX with (HOLDLOCK) (SID,...
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();
…

// commit Tx
strCmd = @"COMMIT TRANSACTION " + strTxID;
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();
....
}

SQL - memory usage

Ref URL:
http://www.sqlservercentral.com/articles/Performance+Tuning/analyzingmemoryrequirementsforsqlserver/2498/

http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx

SQL - Usage of SMO

Below is an example of using SMO (by using C#) to check field(s) of SQL Database:

1. Download SMO Library and install the tool to the SQL server

2. Need to add below asemblies (add reference) for the C# program:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum

3. Include the following:
using Microsoft.SqlServer.Management.Smo;

4. Example to login server
Server SrcServer = new Server(strSrcServer); // Server myServer = new Server(@"ARSHADALI\SQL2008");

//Using SQL Server authentication
SrcServer.ConnectionContext.LoginSecure = false;
SrcServer.ConnectionContext.Login = "sa";
SrcServer.ConnectionContext.Password = "boni_2005";

SrcServer.ConnectionContext.Connect();
if (SrcServer.ConnectionContext.IsOpen)
{
....

// close connection
SrcServer.ConnectionContext.Disconnect();
}

5. Example to check property of "field":

Server svr = new Server();
//strLog = svr.Name + " " + svr.Information.VersionString;
//MessageBox.Show(strLog);

foreach (Database myDatabase in svr.Databases)
{
//strLog = myDatabase.Name;
//MessageBox.Show(strLog);

if (myDatabase.Name == "MyClick")
{
foreach (Table myTbl in myDatabase.Tables)
{
if (myTbl.Name == "MMS_Usage")
{
foreach (Column myCol in myTbl.Columns)
{
strLog = "[" + myCol.Name + "],[" + myCol.DataType.ToString() + "]";
MessageBox.Show(strLog);
}

}
}
}
}

SQL - CLR (access DBase through Context)

Below is an example which shows how to use "context" to access dbase within a CLR assembly

{
...
[Microsoft.SqlServer.Server.SqlFunction]
public static int DBBackup()
{ // local variables
SqlConnection conn = null;
SqlCommand cmd = null;

try
{ conn = new SqlConnection("context connection=true");
conn.Open();

strCmd = "insert DBase_Ctrl..dbase_backup_log (log_event) values('Test')";
cmd = new SqlCommand(strCmd, conn);
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ if (conn != null) { conn.Close(); conn = null; }
}
...
}

SQL - CLR (External Access)

To register a CLR assembly (with external access) for SQL, need to do the following

1. Set property of the Assembly to "external access" (by using Visual Studio)

2. Set "TRUSTWORTHY" of the database to ON
Use DBase_Ctrl
ALTER DATABASE DBase_Ctrl SET TRUSTWORTHY ON

3. Issue command as below in SSMS to register the assembly:

Use DBase_Ctrl
CREATE ASSEMBLY SQL_Backup FROM
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SQL_Backup.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS