Hi Dears
I want to create a windows service for read data from DB1 and then insert them into DB2
At first i read data from DB1 and stored into a dataset
Next i copy my data into an other dataset from the first dataset
Until this steps everything is ok
At the end i want to read data from the second dataset and pass the values to my insert stored procedure and insert data into DB2
But i can't do the latest step !!!!!!!!
No one can help me ??????
Here is my full code
namespace WindowSeriveDemo
{
public partial class Service1 : ServiceBase
{
string connection = @"Data Source=DEVELOPER-PC\DEVELOPER;Initial Catalog=NMSys;User ID=;Password=";
string connection2 = @"Data Source=DEVELOPER-PC\DEVELOPER;Initial Catalog=Dpardazesh;User ID=;Password=";
private Timer ServiceTimer = new Timer();
private int inProcess = 0;
public const string SP_dataRead = "usp_Read_Data_From_DpardazeshDB";
public const string SP_insertData = "usp_Write_Data_Into_NMSysDB";
private IEnumerable<DataRow> item;
public Service1()
{
InitializeComponent();
setupTimer();
}
protected override void OnStart(string[] args)
{
}
private void setupTimer()
{
ServiceTimer.Elapsed += new ElapsedEventHandler(ServiceTimer_Tick);
ServiceTimer.Interval = 1000;
ServiceTimer.Enabled = true;
ServiceTimer.Start();
}
protected override void OnStop()
{
}
private void SyncDatabases()
{
inProcess = 1;
CopyData();
}
private void Persist()
{
inProcess = 1;
PersistMethod(updates);
}
private void InsertData()
{
inProcess = 1;
InsertData(ids);
}
private winservDS readData( )
{
inProcess = 1;
winservDS dataSet = new winservDS();
SqlConnection conn2 = new SqlConnection(connection2);
try
{
conn2.Open();
SqlCommand cmd = new SqlCommand(SP_dataRead);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(SP_dataRead, conn2);
adapter.Fill(dataSet, dataSet.usp_Read_Data_From_DpardazeshDB.TableName);
dataSet.AcceptChanges();
}
catch (Exception ex)
{
string text = ex.Message;
}
finally
{
if (conn2 != null)
conn2.Dispose();
inProcess = 0;
}
return dataSet;
}
private static SqlCommand cmdGetIdentity;
private In_Out_RecordsDS updates;
public In_Out_RecordsDS PersistMethod(In_Out_RecordsDS updates)
{
SqlConnection conn = new SqlConnection(connection);
conn.Open();
SqlTransaction tx = conn.BeginTransaction();
try
{
In_Out_RecordsDS.In_Out_RecordsDataTable tbl = updates.In_Out_Records;
SqlDataAdapter dataAdapter = CreateDataAdapter(conn, tx);
cmdGetIdentity = new SqlCommand("SELECT @@IDENTITY", conn);
dataAdapter.RowUpdated += new SqlRowUpdatedEventHandler(HandleRowUpdated);
dataAdapter.Update(tbl.Select("", "", DataViewRowState.Added));
dataAdapter.Update(tbl.Select("", "", DataViewRowState.ModifiedCurrent));
dataAdapter.Update(tbl.Select("", "", DataViewRowState.Deleted));
tx.Commit();
return updates;
}
catch (Exception ex)
{
tx.Rollback();
throw new Exception(ex.Message);
}
finally
{
if (conn != null)
conn.Dispose();
}
}
private SqlDataAdapter CreateDataAdapter(SqlConnection conn, SqlTransaction tx)
{
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.InsertCommand = WriteDatabase(conn);
dataAdapter.InsertCommand.Transaction = tx;
return dataAdapter;
}
public In_Out_RecordsDS InsertData(IDictionary ids)
{
inProcess = 1;
SqlConnection conn = new SqlConnection(connection);
conn.Open();
In_Out_RecordsDS updates = new In_Out_RecordsDS();
DataRow dr;
dr = updates.In_Out_Records.NewRow();
if (ids != null)
foreach (DictionaryEntry i in ids)
{
if (i.Value == null)
dr[i.Key.ToString()] = DBNull.Value;
else
dr[i.Key.ToString()] = i.Value;
}
updates.In_Out_Records.Rows.Add(dr);
try
{
In_Out_RecordsDS.In_Out_RecordsDataTable tbl = updates.In_Out_Records;
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.InsertCommand = WriteDatabase(conn);
dataAdapter.InsertCommand.Connection = conn;
dataAdapter.ContinueUpdateOnError = false;
cmdGetIdentity = new SqlCommand("SELECT @@IDENTITY", conn);
dataAdapter.RowUpdated += new SqlRowUpdatedEventHandler(HandleRowUpdated);
dataAdapter.Update(tbl.Select("", "", DataViewRowState.Added));
return updates;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
inProcess = 0;
}
private static void HandleRowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
if ((e.Status == UpdateStatus.Continue) && (e.StatementType == StatementType.Insert))
{
e.Row["id"] = Convert.ToInt32(cmdGetIdentity.ExecuteScalar());
e.Row.AcceptChanges();
}
}
private static SqlCommand WriteDatabase(SqlConnection conn)
{
SqlCommand cmd = new SqlCommand(SP_insertData);
cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
SqlParameterCollection pc = cmd.Parameters;
pc.Add(CreateParameter("abID", System.Data.SqlDbType.Int));
pc.Add(CreateParameter("fHitType", System.Data.SqlDbType.Int));
pc.Add(CreateParameter("DateOfHit", System.Data.SqlDbType.DateTime));
pc.Add(CreateParameter("TimeOfHit", System.Data.SqlDbType.Int));
pc.Add(CreateParameter("fEmpid", System.Data.SqlDbType.Int));
return cmd;
}
private static SqlParameter CreateParameter(string p, SqlDbType sqlDbType)
{
SqlParameter parameter = new SqlParameter("@" + p, sqlDbType);
parameter.SourceColumn = p;
return parameter;
}
private void ServiceTimer_Tick(object sender, EventArgs e)
{
if (inProcess == 0)
{
ServiceTimer.Stop();
SyncDatabases();
Persist();
InsertData();
ServiceTimer.Start();
}
}
private In_Out_RecordsDS CopyData()
{
inProcess=1;
DataSet ds1 = readData();
In_Out_RecordsDS ds2 = new In_Out_RecordsDS();
foreach (DataRow item in ds1.Tables[0].Rows)
{
ds2.Tables[0].Rows.Add(item.ItemArray);
}
ds2.AcceptChanges();
return ds2 ;
}
public IDictionary ids { get; set; }
}
}