JavaScript - Web Services and Dynamic HTML

Few days ago I did a study of how a dynamic browser based client software can be, below is some findings related to usage of "Web Service" and Update of ListBox dynamically:

[Access of Web Service by using JavaScritp]
http://www.daniweb.com/forums/thread145927.html
http://msdn.microsoft.com/en-us/library/bb263974(VS.85).aspx

[Change content of HTML page dynamically]
http://dev.aol.com/accessibility/bestpractices/selectlist

Crystal Report - Grouping and Page Width

Below is an example of using "Group" feaure of Crystal Report Wizard. The example show that the first two fields are related to the group while remained fields are related to each record of the group.

However, this kind of grouping is done in client station instead of Server (based on Query result of SQL command), and will have performance issue if there are a large number of records.

According to user manual of Crystal Report 2008, chapter 6, it is possible to change width of a page (in function "Page Setup") so that information of large number of fields can be displayed properly. However, seems this feature is missing in the build-in Crystal Report tools of Visual Studio 2008.

Crystal Report - Query in Server

Try to use Crystal Report in these few days. Below is an example of passing parameter to a SQL command to ensure the query is done in server side.

There is no significant different in performance while "where" is used instead of "on".

However, if using "Selection Formula --> Record", all records will be send to client station before records selection and it will cause serious performance downgrade if number of records is large.

e-form and WorkFlow

Try to evaluate different e-form products in the past few days. Now have a clearer concept about difference between "e-Form" and "WorkFlow":

Basically "WorkFlow" management and "e-form" are two separated modules (or functions).

An "e-form" is an entity which allows user to "open/fill/save" content of a form. Each “e-form entity” is a single entity which may be stored as a physical file (or a XML record in database). Additional programming effort is required to store the content to separated fields of a relational database.

"WorkFlow” is build on top of "e-form" with other tasks. For example, for a workflow which related to OT cliam/approval. The work flow involves the following tasks:

• Open e-form of OT claim, fill-in information, save the form to repository and database.
• Submit (eg: through email) the e-form to Staff A for checking
• Checked by Staff A and submit (eg: through email) the e-form to Staff B for approval
• Approved by Staff B and return the e-form to the staff who claim for OT.

The above workflow involves 4 tasks which base on handling and status update related to ONE e-form.

To provide above functions in a flexible e-form system with prelim workflow function, one of the solution will be the following:

The "e-form" provides functions of "e-form filling" with 3 buttons (Submit, Check, Approval) When button of “Submit”, “Check”, “Approval” is clicked, it will call related “Web Services (or function)” of WorkFlow Engine to provide related operation.

• [Submit]: Save e-form as entity and record in database. Update workflow status and send email alert to Staff A
• [Check]: Update workflow status and send email alert to Staff B
• [Approval]: Update workflow status and send email alert to staff who submit the OT Claim.

The e-form interpreter should be capable to enable/disable the button(s) which depends on status of the work flow. Status should be an entity of the XML which represents the e-form.

For simplicity, may consider above is a web-service based solution in which "e-form" is an entity which provides "open/fill/save" operation. Other logic related to workflow operation is done through web service.

web 4.0

Find an image related to web 4.0, seems interesting and it is related to intelligent personal agent.

C# - Send email by using CDO

Ref:
http://support.microsoft.com/kb/310212


Example:
try
{
CDO.Message oMsg = new CDO.Message();
CDO.IConfiguration iConfg;

iConfg = oMsg.Configuration;

ADODB.Fields oFields;
oFields = iConfg.Fields;

// Set configuration.
ADODB.Field oField = oFields["http://schemas.microsoft.com/cdo/configuration/sendusing"];

//TODO: To send by using the smart host, uncomment the following lines:
//oField.Value = CDO.CdoSendUsing.cdoSendUsingPort;
//oField = oFields["http://schemas.microsoft.com/cdo/configuration/smtpserver"];
//oField.Value = "smarthost";

// TODO: To send by using local SMTP service.
//oField = oFields["http://schemas.microsoft.com/cdo/configuration/sendusing"];
//oField.Value = 1;

oFields.Update();

// Set common properties from message.

//TODO: To send text body, uncomment the following line:
//oMsg.TextBody = "Hello, how are you doing?";


//TODO: To send HTML body, uncomment the following lines:
//String sHtml;
//sHtml = "\n" +
// "\n" +
// "\n" +
// "\n" +
// "

\n" +
// "

Inline graphics

\n" +
// "\n" +
// "";
//oMsg.HTMLBody = sHtml;

//TOTO: To send WEb page in an e-mail, uncomment the following lines and make changes in TODO section.
//TODO: Replace with your preferred Web page
//oMsg.CreateMHTMLBody("http://www.microsoft.com",
// CDO.CdoMHTMLFlags.cdoSuppressNone,
// "", "");
oMsg.Subject = "Test SMTP";

//TODO: Change the To and From address to reflect your information.
oMsg.From = "someone@example.com";
oMsg.To = "someone@example.com";
//ADD attachment.
//TODO: Change the path to the file that you want to attach.
oMsg.AddAttachment("C:\\Hello.txt", "", "");
oMsg.AddAttachment("C:\\Test.doc", "", "");
oMsg.Send();
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
}
return;
}

PDF Writer

http://www.cutepdf.com/Products/CutePDF/writer.asp

C# - Example of using Web Application for HTTP POST request

Below is an example of using ASP.NET (with C#) to create Web Application (.ASPX file, with code-behind DLL written in C#) for HTTP POST command:

1) HTTP Post Request (from a windows form application)

string strResult = "";
string strXML = "";
string strMD5 = "key";
string strUserID = "12345678";

try
{
// ----------------------------------------------------------------
// Send HTTP POST command
// ----------------------------------------------------------------
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(
"http://172.18.2...../Test_HTTPPost.ASPX");
request.Method = "POST";

// additional parameters for HTTP header
request.Headers.Add("Encrypt", strMD5);
request.Headers.Add("UserID", strUserID);

// encode HTTP POST data in base-64 format
byte[] szBase64Bytes;

strXML = "....";

szBase64Bytes = new System.Text.UTF8Encoding().GetBytes(strXML);
strXML = System.Convert.ToBase64String(szBase64Bytes);
byte[] byteArray = Encoding.UTF8.GetBytes(strXML);

request.ContentType = "application/x-www-form-urlencoded";
request.ContentLength = byteArray.Length;

Stream dataStream = request.GetRequestStream();
dataStream.Write(byteArray, 0, byteArray.Length);
dataStream.Close();

// Get response
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
dataStream = response.GetResponseStream();
StreamReader reader = new StreamReader(dataStream);

string strTemp = "";
while ((strTemp = reader.ReadLine()) != null)
{
strResult = strResult + strTemp;
}
dataStream.Close();
response.Close();

MessageBox.Show("Result=[" + strResult + "]");
}
catch (Exception ex)
{
MessageBox.Show("Fault. Error=[" + ex.Message + "]");
}

2) Retrieve data for HTTP Post Request (from a ASPX Web Application)

protected void Page_Load(object sender, EventArgs e)
{
string strLogDir = this.Server.MapPath(".\\") + "Log";
Directory.CreateDirectory(strLogDir);
strLogDir = strLogDir + "\\";

LogToFile(strLogDir, "Load Page");

// get request information
int iSize = Request.ContentLength;

// get HTTP header
string strMD5 = Request.Headers["Encrypt"];
string strUserID = Request.Headers["UserID"];

// get HTTP post data
Stream dataStream = Request.InputStream;
StreamReader reader = new StreamReader(dataStream);

string strTemp = "";
string strResult = "";
while ((strTemp = reader.ReadLine()) != null)
{
strResult = strResult + strTemp;
}
dataStream.Close();

if (strResult != "")
{ // Base64 decode of input parameter
byte[] byteArray = System.Convert.FromBase64String(strResult);

System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
strResult = enc.GetString(byteArray);
}

LogToFile(strLogDir, "Size=[" + iSize + "]");
LogToFile(strLogDir, "MD5=[" + strMD5 + "]");
LogToFile(strLogDir, "UserID=[" + strUserID + "]");
LogToFile(strLogDir, "Data=[" + strResult + "]");

// response for testing purpose
Response.Write("Test");
Response.End();
}

private static void LogToFile(string strURL,
string strMess)
{
DateTime Now = System.DateTime.Now;
string strLog = "";
string strNow = Now.ToString("yyyyMMdd");

string REQUEST_LOG = strURL + "XXX" + strNow + ".txt";

StreamWriter sr = File.Exists(REQUEST_LOG) ? File.AppendText(REQUEST_LOG) : File.CreateText(REQUEST_LOG);

strLog = Now.ToString("yyyy-MM-dd HH:mm:ss:") + Now.Millisecond.ToString() + "," + strMess;
sr.WriteLine(strLog);

sr.Close();
}

ASP.NET - Web Application

Types of ASP.NET project:

1. ASP.NET Server Control: A custom control which can be added by developer in web site through Visual Studio

2. ASP.NET Web Service: Web Service programming, access APIs through reference of ASMX file

3. ASP.NET Web Application: access C# code-behind function through access of .ASPX file. "Page load" function of code behind C# file will be invoked while the .ASPX page is accessed.

For both of "Web Service" and "Web Application", need to build / convert and / publish the files to server. Also, need to use IIS to create "Virtaul Directory" and select property to create "application" for the two functions.

ASP.NET - client script to pop-up window

Below is an example to use C# and client script to pop-up an explorer
{
....

string strCmd = "window.open('admin_report_mms_view.aspx?start=" + str_from + "&end=" + str_to + "&CID=" + cid + "', 'window', config='height=450,width=800,resizable=yes,toolbar=0,menubar=0,location=0')";

((ImageButton)e.Row.Cells[4].FindControl("btn_mms")).Attributes.Add("onclick", "return " + strCmd);

//((ImageButton)e.Row.Cells[4].FindControl("btn_mms")).Attributes.Add ("onclick", "return confirm('Test');"); ....

}

e-Learning : Microsoft Content Preparation Tools

http://www.microsoft.com/learning/en/us/training/lcds.aspx#tab1

The Microsoft Learning Content Development System (LCDS) is a free tool that enables the Microsoft Learning community to create high-quality, interactive, online courses.

C# - usage of static class

Below is an example of using "static class" in C#:

Ref.: http://msdn.microsoft.com/en-us/library/79b3xss3(VS.80).aspx

Main class()
{
...

public static class UtilFunc
{
private static string g_strLogFileURL = "test";

public static void SetLogDir(string strURL)
{
if (strURL != "") g_strLogFileURL = strURL;
}

public static string LogToFile()
{
return g_strLogFileURL;
}
}
...
}

Other Class()
{
....

// static class can't be instantiated, simply use its APIs as below
MessageBox.Show(UtilFunc.LogToFile());

....
}

SQL : comparison with Oracle Server

Below is an article which compares difference between SQL and Oracle server:

http://www.sql-server-performance.com/articles/dba/oracle_sql_server_comparison_p1.aspx

C# - save content to CSV file from GridView

Below is an example to save content from GridView to CSV file:

{
....
string strPath = ".\\XXX_" + strFName + ".csv";
if (File.Exists(strPath))
{
File.Delete(strPath);
}

StringBuilder strColu = new StringBuilder();
StringBuilder strValue = new StringBuilder();
int i = 0;
int j = 0;

try
{
StreamWriter sw = new StreamWriter(new FileStream(strPath,
FileMode.CreateNew), Encoding.GetEncoding("GB2312"));
// ----------------------------------------------------------------
// Write header to CSV file
// ----------------------------------------------------------------
for (i = 0; i <= iTotalCol; i++)
{
strColu.Append(GridView_Task.Columns[i].HeaderText);
strColu.Append(",");
}
strColu.Remove(strColu.Length - 1, 1); // remove last character
sw.WriteLine(strColu);

// ----------------------------------------------------------------
// Write content to CSV file
// ----------------------------------------------------------------
for (i = 0; i < iTotalRow; i++)
{
strValue.Remove(0, strValue.Length);
DataGridViewRow dr = GridView_Task.Rows[i];

for (j = 0; j <= iTotalCol; j++)
{
strValue.Append(dr.Cells[j].Value.ToString());
strValue.Append(",");
}
strValue.Remove(strValue.Length - 1, 1); // remove last character
sw.WriteLine(strValue);
}
sw.Close();

MessageBox.Show("OK. Success to generate CSV file.");
}
catch (Exception ex)
{
MessageBox.Show("Fail to generate CSV file. Error=[" + ex.Message + "]");
}
...
}

C# - Example of HTTP Post

Below is an eample for HTTP POST (with additional HTTP header)

{ ....
try
{
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create
("http://xxxx/Mt.do");
request.Method = "POST";

string parameter = "HTTP body content";
byte[] byteArray = Encoding.UTF8.GetBytes(parameter);

request.Headers.Add("Encrypt", strMD5);
request.Headers.Add("Mobile", "xxxx");
request.Headers.Add("Vas", "xxxx");

request.ContentType = "application/x-www-form-urlencoded";
request.ContentLength = byteArray.Length;

Stream dataStream = request.GetRequestStream();
dataStream.Write(byteArray, 0, byteArray.Length);
dataStream.Close();

HttpWebResponse response = (HttpWebResponse)request.GetResponse();
if (HttpStatusCode.OK == response.StatusCode)
{
dataStream = response.GetResponseStream();
StreamReader reader = new StreamReader(dataStream);

string line;
while ((line = reader.ReadLine()) != null)
{
MessageBox.Show(line.ToString());
}
dataStream.Close();
response.Close();
}
else MessageBox.Show("Response code not OK");
....

}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
...
}

C# - Example to create MD5 hash value

Below is an example to create MD5 has value of "strIn"
{
....

string strIn = "mc20101065806299113601931094";

// Create a new instance of the MD5CryptoServiceProvider object.
MD5 md5Hasher = MD5.Create();

// Convert the input string to a byte array and compute the hash.
byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(strIn));

// Create a new Stringbuilder to collect the bytes
// and create a string.
StringBuilder sBuilder = new StringBuilder();

// Loop through each byte of the hashed data
// and format each one as a hexadecimal string.
for (int i = 0; i < data.Length; i++)
{
sBuilder.Append(data[i].ToString("x2"));
}

// Return the hexadecimal string.
MessageBox.Show(sBuilder.ToString());
...
}

C# - read XML from SQL2005

Below is an exmaple to read XML content from SQL2005. Use code as below, otherwise, the returned XML may be truncated with size of 2K bytyes

{
....
System.Xml.XmlReader reader = null;

try
{
conn = new SqlConnection(g_strConnDB);
conn.Open();
cmd = new SqlCommand(strCmd, conn);

strCmd = "select *** from *** FOR XML RAW";
cmd.CommandText = strCmd;
reader = cmd.ExecuteXmlReader();
reader.Read();

strResult = "";
while (reader.ReadState != ReadState.EndOfFile)
{
strResult += reader.ReadOuterXml();
}
reader.Close();

....
}
}

C# - Example to use CheckListBox in a Windows Form

Below is some example to use a CheckListBox:

1) Clear content
ChkBoxList_TID.Items.Clear();

2) Add CheckBox
ChkBoxList_TID.Items.Add("ChkBox Text1");

3) Deteremine # of elements and its status
iTotal = ChkBoxList_TID.Items.Count;
int iIndex = ChkBoxList_TID.SelectedIndex;
ChkBoxList_TID.SelectedItem.ToString()

C# - Example to do logging in Web Service

Below is an example to write log file in Web Service:

1) Call the following function is a Web Service

strLog = "UserName=[" + strUser + "]. PW=[" + strUserPW + "]";
LogToFile(this.Server.MapPath(".\\"), strLog);

2) Provide function of LogToFile as below:

private static void LogToFile(string strURL,
string strMess)
{
DateTime Now = System.DateTime.Now;
string strNow = Now.ToString("yyyyMMdd");

string REQUEST_LOG = strURL + "\\TextFile_" + strNow + ".txt";
StreamWriter sr = File.Exists(REQUEST_LOG) ? File.AppendText
(REQUEST_LOG) : File.CreateText(REQUEST_LOG);
sr.WriteLine(strMess);
sr.Close();
}

C# - Example to create web service

Below is an example to create / utilize Web Service:
1) By using Visual Studio 2008, select to create a project of "ASP .NET Web Service" using C#

2) Change namespace to Microsoft as below:
[WebService(Namespace = "http://microsoft.com/webservices/")]

3) Right Click file "Service1.asmx", select "View in Browser" to test functionality of the "Web Service".

4) Right Click the project file, select "Rebuild", "Convert to Web Application" and then "Pubish" to publish the web service to proper Virtual Directory of IIS.

5) For the Virtual Directory of the folder which stores the Web Service, right click to sleect "Property" and then click button "CReate" to make the directory to support "Application".

These complete the building and publishing of a web service.

6) Use Visual Studio to create aWeb Form application. Click to select "Add web reference" and declare URL as below to add the service for usage:

http://server_ip/xxx/yyy/zzz/Service1.asmx?op=HelloWorld

7) Add code as below to use the "Web Service":

{ ....

WebReference.Service1 service = new WebReference.Service1();
string message = service.HelloWorld();
MessageBox.Show(message);
....
}

C# - Example of TCPServer

1) Sample code of TCP Server
using System;
using System.Text;
using System.Net.Sockets;
using System.Threading;
using System.Net;

using System.Windows.Forms;

using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Reflection;

namespace TCP_Server
{
class Server
{
private TcpListener tcpListener;
private Thread listenThread;

// ------------------------------------------------
// To start TCP Service
// ------------------------------------------------
public Server()
{
try
{
this.tcpListener = new TcpListener(IPAddress.Any, 3003);
this.listenThread = new Thread(new ThreadStart(ListenForClients));
this.listenThread.Start();

LogToFile("Success to start TCPServer");
}
catch (Exception ex)
{
LogToFile("!!!Fail to start TCPServer. Error=[" + ex.Message + "]");
}
}

// ------------------------------------------------
// To stop TCP Service
// ------------------------------------------------
public void Stop_TCPService()
{
try
{
listenThread.Abort();
LogToFile("Success to stop TCPServer");
}
catch (Exception ex)
{
LogToFile("!!!Fail to stop TCPServer. Error=[" + ex.Message + "]");
}
}

private void ListenForClients()
{
try
{
this.tcpListener.Start();
while (true)
{
//blocks until a client has connected to the server
TcpClient client = this.tcpListener.AcceptTcpClient();

//create a thread to handle communication
//with connected client
Thread clientThread = new Thread(new ParameterizedThreadStart(HandleClientComm));
clientThread.Start(client);
}
}
catch (Exception ex)
{
LogToFile("!!!Fail to ListenForClients. Error=[" + ex.Message + "]");
}
}

private void HandleClientComm(object client)
{
TcpClient tcpClient = (TcpClient)client;
NetworkStream clientStream = tcpClient.GetStream();

byte[] message = new byte[4096];
int bytesRead;

while (true)
{
bytesRead = 0;

try
{ //blocks until a client sends a message
bytesRead = clientStream.Read(message, 0, 4096);

if (bytesRead != 0)
{
System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
string strLog = enc.GetString(message);
LogToFile("Data read;[" + strLog + "]");
}
}
catch (Exception ex)
{
//a socket error has occured
LogToFile("!!!Fail to HandleClientComm. Error=[" + ex.Message + "]");
break;
}

if (bytesRead == 0)
{
//the client has disconnected from the server
break;
}

//message has successfully been received
ASCIIEncoding encoder = new ASCIIEncoding();
System.Diagnostics.Debug.WriteLine(encoder.GetString(message, 0, bytesRead));
}

tcpClient.Close();
}

2) Sample code of client using the service
// TCP Client: Test operation
TcpClient client = new TcpClient();

IPEndPoint serverEndPoint = new IPEndPoint(IPAddress.Parse("127.0.0.1"), 3003);

client.Connect(serverEndPoint);

NetworkStream clientStream = client.GetStream();

ASCIIEncoding encoder = new ASCIIEncoding();
byte[] buffer = encoder.GetBytes("Hello Server!");

clientStream.Write(buffer, 0, buffer.Length);
clientStream.Flush();

ASP.NET - modify content of GridView

Below is an example which modify content of a cell of GridView, after data binding:

{
............. // do data binding

int count = GridView_Result1.Rows.Count;
if (count > 0)
{
iCnt = 0;
foreach (GridViewRow row in GridView_Result1.Rows)
{
if (row.Cells[7].Text.Length > 10)
{
iCnt++;
strTemp = row.Cells[7].Text;
row.Cells[7].Text = "View";
}
}
}
....
}

C# - example of using Web Service

Assume a web service (with .ASMX file) have been created and registered properly.

Below please find steps to utilize the Web Serice;

1) Use "Add Reference --> Add Web reference" to add the web service by using "Visual Studio" (to specify the URL of the ASMX file, e.g.: http://xxx/sample.asmx). It will create a name space of the service.

2) Use code as below to call function(s) provided by the service:

try
{
//身份验证
cn.xxx.MySoapHeader soapHeader = new cn.xxx.MySoapHeader();
soapHeader.UserName = "test";
soapHeader.PassWord = "test";
string FileName = "";
byte[] AccessoriesByte =null;

cn.xxx.SendEmail service = new cn.xxx.SendEmail();
service.MySoapHeaderValue = soapHeader;

int result=service.SendXXXEmail("111", "222");
if (result==1) MessageBox.Show("Success"); }
else MessageBox.Show("Failure");+ "]");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
...
}

C# - example of HTTP call

1. Sample to use HTTP GET protocol

using system.net;

{
....
string strURL;
strURL = "http://xxxx/default.aspx";
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(strURL);

request.Method = "GET";
try
{
using (WebResponse wr = request.GetResponse())
{
Stream strm = wr.GetResponseStream();
StreamReader sr = new StreamReader(strm);
string line;

while ((line = sr.ReadLine()) != null)
{
MessageBox.Show(line.ToString());
}
strm.Close();
wr.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
....
}

2. Example of reading content of a file through URL

string strURL = "http://xxx/test.html";
string rl = "";
string strContent = "";

try
{
WebRequest myReq = WebRequest.Create(strURL);
WebResponse myRes = myReq.GetResponse();
Stream resStream = myRes.GetResponseStream();
StreamReader sr = new StreamReader(resStream, Encoding.Default);
StringBuilder sb = new StringBuilder();

while ((rl = sr.ReadLine()) != null)
{
sb.Append(rl);
}
strContent = sb.ToString();
myRes.Close();
}

SQL - Simple example (Display Date, truncate string, etc)

Below is a simple example to only display "date" information from a field of DateTime:

CONVERT(varchar,create_timestamp,101) as create_date

Below is an example to truncate string:

left(reason,25) as reason

Below is an example to display image file (or folder content) by using a web browser:

1) Define region of a "webbrowser" component in a windows form

2) Call below command to activate the browser:

webBrowser1.Navigate(g_strPath); // System.Windows.Forms.WebBrowser()

Example of g_strPath: http://myclick.cn/mcs/MIU/testmmsii.htm");

Example of activate a window form:

Form2 webObj = new Form2(strURL);
webObj.Show();

C# - display content in table

Below is a simple example to display content in table:

{
// create table for display
DataTable table = new DataTable();
table.Columns.Add("TimeStamp", typeof(string));
table.Columns.Add("UserID", typeof(string));
table.Columns.Add("TID", typeof(string));

// loop to add records
while (...)
{
...
table.Rows.Add(strMess1, strMess2, strMess3);
...
}

// display content to GridView
// (Assume GridView has been defined in designer of Visual Studio)
dataGridView1.DataSource = table;
}

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