Click here to Skip to main content
15,846,342 members
Please Sign up or sign in to vote.
1.67/5 (4 votes)
See more:
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

C#
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;
        //Timer timer1 = new Timer();

        public Service1()
        {
            InitializeComponent();
            setupTimer();
        }


        protected override void OnStart(string[] args)
        {


            //ServiceTimer.Elapsed += new ElapsedEventHandler(timer1_Elapsed);

        }


        private void setupTimer()
        {
            ServiceTimer.Elapsed += new ElapsedEventHandler(ServiceTimer_Tick);
            ServiceTimer.Interval = 1000;
            ServiceTimer.Enabled = true;
            ServiceTimer.Start();
        }


        protected override void OnStop()
        {
            //timer1.Enabled = false;
        }


        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;
                //AddParameter(cmd);
                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;
                     //Create the adapter initial
                     SqlDataAdapter dataAdapter = CreateDataAdapter(conn, tx);

                     cmdGetIdentity = new SqlCommand("SELECT @@IDENTITY", conn);
                     dataAdapter.RowUpdated += new SqlRowUpdatedEventHandler(HandleRowUpdated);

                     //First Add Users
                     dataAdapter.Update(tbl.Select("", "", DataViewRowState.Added));
                     //Next Modified
                     dataAdapter.Update(tbl.Select("", "", DataViewRowState.ModifiedCurrent));
                     //Next Deleted
                     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;
                 //Create the adapter initial
                 SqlDataAdapter dataAdapter = new SqlDataAdapter();
                 dataAdapter.InsertCommand = WriteDatabase(conn);
                 dataAdapter.InsertCommand.Connection = conn;
                 //Roll Back the changes if some one error have
                 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));
                //cmd.ExecuteNonQuery();
                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; }

        

        
    }
}
Posted
Updated 19-Apr-14 19:22pm
v3
Comments
kedar001 19-Apr-14 5:40am    
any ERROR ?
unhappyman3 19-Apr-14 5:42am    
I don't have any error
my problem is in the last estep
I don't know how can i pass the values from my table in dataset to my stored procedure !!!!
Sampath Lokuge 19-Apr-14 5:55am    
Why do you need to do so ?
unhappyman3 19-Apr-14 5:57am    
I diden't get it !!!
Please ask clearly
Sampath Lokuge 19-Apr-14 5:58am    
Why do you need to 'pass the values from my table in dataset to my stored procedure ' ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900