Windows service – Transfer data btw SQL Server and Oracle 9i

Sunday, November 22, 2009

Windows Service application for Data Transfer

Windows service application is a great resource to automate tasks such as alter messages, data transfer between different databases and more.

Here is a Windows Service application that connects to SQL Server 2000, reads data and writes into two Oracle 9i Tables at specified start time and at interval time(i.e., every 12hrs or 24hrs) configured through app.config.

Both databases connection strings are configured through app.config along with windows service start time and interval time. when the start time is configured as 10AM and time interval is 12hrs, service starts at 10am and again at 10PM.

Windows service is configurable from app.config
SQL Server and Oracle Connection strings are read from app.config
Service start time and Interval time is configurable from app.config xml file
Service handles day light saving timings as well
Note that DataTransfer() method is called from OnTimeElapsed method in service.

App.config
<?xml version="1.0" encoding="utf-8" ?>

<configuration>
<appSettings>

<!--connectionStrings -->

<add key="SqlconnStr" value="Data Source=mySource;

Initial Catalog=myCatalog; Integrated Security=True" />

<add key="oraConnection" value="provider=msdaora;

Data Source=mySource; User ID=logbook;Password=password;" />

<!-- Interval Time to start the service.

Note: 1 minute = 60,000 milliseconds

@@ 24Hrs = 86400000 Milli seconds -->
<add key="intervalTime" value="60000" />
<!--Service Start Time to configure -->
<add key="StartTime" value="1250" />
</appSettings>

Windows Service source

public partial class myService : ServiceBase 
{ 
// This is the timer that will be used to execute TimeElapsed. 
System.Threading.Timer timer; 
// This will be the total number of minutes from midnight to

the time indicated in the app.config file. 
double minutesToStart = 0; 

// This is the last time the service was run. Set it to today,

but it may be changed in the OnStart method. 
DateTime lastRunDate = DateTime.Today; 

public myService() 
{ 
InitializeComponent(); 
}

protected override void OnStart(string[] args) 
{ 
try 
{ 
// Fetch the time of day to run the service from the app.config file. 
string startTimeString =

ConfigurationSettings.AppSettings["StartTime"].ToString(); 

// Instantiate a start time. 
DateTime startTime; 

// Try to parse out the time from the string retrieved

from the app.config. 
if (DateTime.TryParseExact(startTimeString, "HHmm",

CultureInfo.InvariantCulture,

DateTimeStyles.None, out startTime)) 
{ 
// Get the total number of minutes from midnight represented by the 
// start time, and store it in the minutesToStart variable. 
minutesToStart = startTime.Subtract(startTime.Date).TotalMinutes; 
} 
// If the current date, plus the number of minutes we've

determined above hasn't passed yet, then set the lastRunDate

value to yesterday. 
if (DateTime.Today.AddMinutes(minutesToStart) > DateTime.Now) 
lastRunDate = DateTime.Today.AddDays(-1); 

// Start a timer. It should tick every second. 
System.Threading.Timer timer = new

System.Threading.Timer(OnElapsedTime, null, 1000, 1000); 
} 
catch (Exception ex) 
{ 
System.Diagnostics.EventLog.WriteEntry("error in starting

my Service", ex.Message + Environment.NewLine + ex.StackTrace); 
} 
} 
protected override void OnStop() 
{ 
//Nothing to do here 
} 
private void OnElapsedTime(object source) 
{ 
// If today at midnight, plus the number of minutes defined

in the OnStart method has passed, and the lastRunDate is yesterday,

then set the lastRunDate to today and call TransferData(). 
if (DateTime.Today.AddMinutes(minutesToStart)

< DateTime.Now && DateTime.Today > lastRunDate) 
{ 
lastRunDate = DateTime.Today; 
TransferData(); 
} 
} 
}

TransferData() method

/// <summary>
/// Establish DB Connection and write data from SQL Server

/// Table into Oracle Tables
/// records added/updated in last 24hrs
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
#region
Dictionary<CraftKey, List<CraftValue>> sqlDataDictionary =

new Dictionary<CraftKey, List<CraftValue>>();
private List<CraftValue> _ProcessedCrafts = new List<CraftValue>();
private List<CraftKey> CraftKeys = new List<CraftKey>();
private void TransferData()
{
//Variables to store 
string workOrder, completion_date, conDateTest, assetNum, status,

workReq, craft, originator, OriginateDateStr, dateStr;
DateTime originateDate;
//Read connection String from web.config
string connStr = ConfigurationSettings.AppSettings["SqlconnStr"].ToString();
//create connection passing connectionString
//1) SqlConnection conn = new SqlConnection(connStr);
OleDbConnection conn = new OleDbConnection(connStr);
try
{
conn.Open();
}
catch (Exception ex)
{
//write exception to LogEvent
System.Diagnostics.EventLog.WriteEntry("WorkOrdersService",

ex.Message + Environment.NewLine + ex.StackTrace);
}
//Sqlcommand
//2) SqlCommand cmd = conn.CreateCommand();
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText =

"SELECT

WKAW.WO,WKAW.CMPL_DATE,WKAW.ASSETNO,

WKAW.STATUS,WKAW.WORKREQ,"
+ "WKAWAL.CRAFT,WKAWAL.CRAFT_CLO_FLAG,"
+ "WKAW.ORG_DATE,WKAW.ORIGINATOR "
+ "FROM WKAW,WKAWAL"
+ " WHERE "
+ "WKAW.WO = WKAWAL.WO"
+ " AND "
+ "WKAW.STATUS <> 'CLO'"
+ " AND "
+ "WKAW.ORG_DATE BETWEEN SYSDATE - 1 AND SYSDATE "
+ " ORDER BY WKAWAL.CRAFT, WKAW.ORG_DATE";
//DataReader
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
//3) SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable sqlData = new DataTable();
adapter.Fill(sqlData);
EnsureNoDuplicates(sqlData);
PrepareProcessedCrafts();
for (int i = 0; i < _ProcessedCrafts.Count; i++)
{
CraftValue value = _ProcessedCrafts[i];
workOrder = value.WorkOrder;
assetNum = value.AssetNum;
workReq = value.WorkReq;
craft = value.Craft;
originateDate = value.NewDate;
originator = value.Originator;
//Open a Oledb connection to Opralog
using (OleDbConnection connection = new OleDbConnection(

ConfigurationSettings.AppSettings["opralogConnection"].ToString()))
{
//convert Date to String format
OriginateDateStr = originateDate.ToString("dd-MMM-yyyy HH:mm:ss");
dateStr = originateDate.ToString("dd-MMM-yyyy HH:mm:ss");
//Create oledb command instance
OleDbCommand command = new OleDbCommand();
//create an isntance of Transaction
OleDbTransaction transaction = null;
// Set the Connection to the new OleDbConnection
command.Connection = connection;
// Open the connection and execute the transaction
try
{
//open connection
connection.Open();
//Start a local transaction with ReadCommitted isolation level
transaction = connection.BeginTransaction(IsolationLevel.

ReadCommitted);
// Assign transaction object for a pending local transaction.
command.Connection = connection;
command.Transaction = transaction;
//data_no column 
int data_no = 1;
//Insert itno external_event_occurrances
command.CommandText = "INSERT INTO external_event_occurrances

(dcs_link,event_timestamp) VALUES('" + "DPWO_" + craft + "',

to_date('" + OriginateDateStr + "', 'dd-mon-yyyy hh24:mi:ss'))";
command.ExecuteNonQuery();
//Insert into external_event_data 
command.CommandText = "INSERT INTO external_event_data(dcs_link,

event_timestamp,data_no,col_data) VALUES('" + "DPWO_" + craft + "',

to_date('" + OriginateDateStr + "', 'dd-mon-yyyy hh24:mi:ss'), '"

 + data_no + "' , '" + workOrder + "')";
command.ExecuteNonQuery();
data_no = data_no + 1;
command.CommandText = "INSERT INTO external_event_data(dcs_link,

event_timestamp,data_no,col_data) VALUES('" + "DPWO_" + craft + "',

to_date('" + OriginateDateStr + "', 'dd-mon-yyyy hh24:mi:ss'), '"

+ data_no + "' , '" + assetNum + "')";
command.ExecuteNonQuery();
data_no = data_no + 1;
command.CommandText = "INSERT INTO external_event_data(dcs_link,

event_timestamp,data_no,col_data) VALUES('" + "DPWO_" + craft + "',

to_date('" + OriginateDateStr + "', 'dd-mon-yyyy hh24:mi:ss'), '" +

data_no + "' , '" + workReq + "')";
command.ExecuteNonQuery();
data_no = data_no + 1;
command.CommandText = "INSERT INTO external_event_data

(dcs_link, event_timestamp,data_no,col_data) VALUES('" + "DPWO_"

+ craft + "', to_date('" + OriginateDateStr + "',

'dd-mon-yyyy hh24:mi:ss'), '" + data_no + "' ,'" + dateStr + "')";
command.ExecuteNonQuery();
data_no = data_no + 1;
command.CommandText = "INSERT INTO external_event_data(dcs_link,

event_timestamp,data_no,col_data) VALUES('" + "DPWO_" + craft + "',

 to_date('" + OriginateDateStr + "', 'dd-mon-yyyy hh24:mi:ss'), '"

 + data_no + "' , '" + originator + "')";
command.ExecuteNonQuery();
//commit the transaction
transaction.Commit();
//Response.Write("\nRecords inserted into both Tables");
}
catch (Exception ex)
{
//write exception to Event Log
System.Diagnostics.EventLog.WriteEntry("WorkOrdersService",

ex.Message + Environment.NewLine + ex.StackTrace);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch
{
//Do nothing here. Releases resources
}
}
}
}
// connection
conn.Close();
}
private void PrepareProcessedCrafts()
{
for (int i = 0; i < CraftKeys.Count; i++)
{
List<CraftValue> values = sqlDataDictionary[CraftKeys[i]];
for (int j = 0; j < values.Count; j++)
{
_ProcessedCrafts.Add(values[j]);
}
}
}
/// <summary>
/// Handling the data for any duplicates and 
/// processing the data not to have it
/// </summary>
/// <param name="sqlData"></param>
internal void EnsureNoDuplicates(DataTable sqlData)
{
for (int i = 0; i < sqlData.Rows.Count; i++)
{
List<CraftValue> values;
DataRow row = sqlData.Rows[i];
CraftKey key = new CraftKey();
key.Craft = row["craft"].ToString();
key.OrgDate = DateTime.Parse(row["org_date"].ToString());
values = GetCraftValues(key);
if (values != null)
{
//Means we have a duplicate
//Make existing seconds value to zero
//For each of value make seconds starting from zero
//so it always gives guarantee that 59 records are not duplicates
for (int j = 0; j < values.Count; j++)
{
TimeSpan span = new TimeSpan(0, 0, 0, values[j].NewDate.Second);
values[j].NewDate = values[j].NewDate.Subtract(span).AddSeconds(j);
}
//Now Insert our current record with one second
CraftValue newValue = BuildCraftValue(values.Count, row);
values.Add(newValue);
//here we need not to add dictionary as we are using the item

//which the dictionary already has
}
else
{
//If we do not have the craft with the specified date already

//build the key and add it to dictionary
CraftValue newValue = BuildCraftValue(key.OrgDate.Second, row);
values = new List<CraftValue>();
values.Add(newValue);
sqlDataDictionary.Add(key, values);
//Add it to list of CraftKeys so that we can loop through it later
CraftKeys.Add(key);
}
}
}
private List<CraftValue> GetCraftValues(CraftKey key)
{
for (int i = 0; i < CraftKeys.Count; i++)
{
if (CraftKeys[i].Craft == key.Craft &&

CraftKeys[i].OrgDate == key.OrgDate)
{
return sqlDataDictionary[CraftKeys[i]];
}
}
return null;
}
private CraftValue BuildCraftValue(int secondValue, DataRow row)
{
CraftValue value = new CraftValue();
value.Craft = row["craft"].ToString();
value.AssetNum = row["assetno"].ToString();
value.WorkOrder = row["wo"].ToString();
value.WorkReq = row["workreq"].ToString();
DateTime date = (DateTime)row["org_date"];
value.NewDate = date.Subtract(new

TimeSpan(date.Second)).AddSeconds(secondValue);
value.Originator = row["originator"].ToString();
return value;
}
class CraftKey
{
private string _craft;
public string Craft
{
get
{
return _craft;
}
set
{
_craft = value;
}
}
private DateTime _orgDate;
public DateTime OrgDate
{
get
{
return _orgDate;
}
set
{
_orgDate = value;
}
}
}
class CraftValue
{
private string _workOrder;
private string _assetNum;
private string _originator;
private string _workReq;
private DateTime _newDate;
public string WorkOrder
{
get
{
return _workOrder;
}
set
{
_workOrder = value;
}
}
public string AssetNum
{
get
{
return _assetNum;
}
set
{
_assetNum = value;
}
}
private string _craft;
public string Craft
{
get
{
return _craft;
}
set
{
_craft = value;
}
}
public string Originator
{
get
{
return _originator;
}
set
{
_originator = value;
}
}
public string WorkReq
{
get
{
return _workReq;
}
set
{
_workReq = value;
}
}
public DateTime NewDate
{
get
{
return _newDate;
}
set
{
_newDate = value;
}
}
}
#endregion
}
}

References

Creating windows service in C#

Debugging Windows Service Applications

Dealing with Daylight savings Time

Service User Accounts

Create Setup project for a Windows Service app

good.. thank you

oussamafarhati - Sunday, September 18, 2011 9:58:25 PM

The above windows service source only executes once, it seems the intervalTime is not being used to re-execute the transfer every 12 or 24 hours. Is some piece of code missing?

Kevin - Friday, November 1, 2013 4:47:36 PM

2 Comments