Hi
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
Hi
This is quite common requirement where Master – Detail reporting functionality is implemented using two ASP.NET forms.
For example, GridView control is used to display all user details and one of the Grid view columns contains View/Open, so that each user record can be opened on next page when relevant row is selected. This can be achieved in various ways, most common approach is by passing UserID in URL. On second page using Query String parameter UserID is accessed and the detailed user record is displayed using DetailsView control.
Now DetailsView control displays a single record on second page. When it is required to provide functionality to Edit the DetailsView, simply you can change DetailsView mode to Edit as below. Note that in this example the button is out of DetailsView control.
/// <summary>
/// Change DeailsView mode to Edit
///and data bind
/// Change DetailsView mode to Edit
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnEditUser_Click(object sender, EventArgs e)
{
dvUserRecord.ChangeMode(DetailsViewMode.Edit);
//Bind DetailsView with your method
dvUserRecord.DataSource = GetUserRecord();
dvUserRecord.DataBind();
}
Note that initially DetailsView displays the data that is with in ItemTemplate section of asp:TemplateField section, in the below example code snippet country (i.e., United kingdom) is shown where it related to current user record.
When DetailsView changes to Edit mode, it is important to provide list of countries using List control such as DetailsView, so that the end user can change the country to desired one. Note that the controls and binded data with in EditItemTemplate section is shown when the DetailsView is in Edit mode. As shown in below code snippet, Drop Down List control is shown with binded list of countries.
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:Label ID="lblCountry" runat="server" Text='<%# bind("country")%>' />
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddlCountry" runat="server"
AppendDataBoundItems="True" >
<asp:ListItem Value="" Text="" />
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
How to bind Drop Down List in Details View when Details View is in Edit mode
This article concentrates and explains how to bind Drop Down List control with custom method from code behind page.
In order to bind drop down list with custom method, the right event is Details View Data Bound.
All it requires is checking DetailsView current mode is Edit and finding drop down List control and binding the custom method to it as shown below.
protected void dvUserRecord_DataBound(object sender, EventArgs e)
{
if (dvUserRecord.CurrentMode == DetailsViewMode.Edit)
{
//Find Drop down list from aspx page
DropDownList ddlCountry = dvUserRecord.FindControl("ddlCountry")
as DropDownList;
//check ddlCountry is not null || country drop down list is found
if(ddlCountry != null)
{
//Bind countries data to ddlCountry
ddlCountry.DataTextField = "countryText";
ddlCountry.DataValueField = "IndexValue";
ddlCountry.DataSource = GetCountries;
//custom method that gets all countries
ddlCountry.DataBind();
}
}
Thats all! you are done!!
References
http://msdn.microsoft.com/en-us/library/aa581793.aspx
http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/detailsview.aspx