(29-12-20, 03:58 PM)amonem كتب :

(29-12-20, 02:25 PM)mol كتب :

كود :

OleDbDataAdapter da = new OleDbDataAdapter(@"SELECT ID, SUM(total) AS total FROM (SELECT ID, SUM(Table_2.Numpr_Total) AS total FROM Table_2 GROUP BY ID UNION ALL SELECT Table_3.ID, SUM(Table_3.AS_Total) AS total FROM TABLE_3 GROUP BY ID) GROUP BY ID", cnn);

---

كود :

SqlDataAdapter da = new SqlDataAdapter(@"SELECT ID, SUM(total) AS total FROM (SELECT ID, SUM(Table_2.Numpr_Total) AS total FROM Table_2 GROUP BY ID UNION ALL SELECT Table_3.ID, SUM(Table_3.AS_Total) AS total FROM TABLE_3 GROUP BY ID) GROUP BY ID", cnn);

نفس رساله الخطا للاسف

(29-12-20, 07:52 PM)mol كتب :

كود :

SqlDataAdapter da = new SqlDataAdapter(@"SELECT ID, SUM(total) AS total FROM (SELECT ID, SUM(Numpr_Total) AS total FROM Table_2 GROUP BY ID UNION ALL SELECT ID, SUM(AS_Total) AS total FROM Table_3 GROUP BY ID) AS T GROUP BY ID", cnn);

(30-12-20, 12:44 PM)amonem كتب :

(29-12-20, 07:52 PM)mol كتب :

كود :

SqlDataAdapter da = new SqlDataAdapter(@"SELECT ID, SUM(total) AS total FROM (SELECT ID, SUM(Numpr_Total) AS total FROM Table_2 GROUP BY ID UNION ALL SELECT ID, SUM(AS_Total) AS total FROM Table_3 GROUP BY ID) AS T GROUP BY ID", cnn);

تمام الكود دا اشتغل والنتيجه 100% بس تقيل فى التنفيذ بياخد وقت طويل حوالى 3 دقائق

(30-12-20, 09:59 PM)mol كتب :

(30-12-20, 12:44 PM)amonem كتب :

(29-12-20, 07:52 PM)mol كتب :

كود :

SqlDataAdapter da = new SqlDataAdapter(@"SELECT ID, SUM(total) AS total FROM (SELECT ID, SUM(Numpr_Total) AS total FROM Table_2 GROUP BY ID UNION ALL SELECT ID, SUM(AS_Total) AS total FROM Table_3 GROUP BY ID) AS T GROUP BY ID", cnn);

تمام الكود دا اشتغل والنتيجه 100% بس تقيل فى التنفيذ بياخد وقت طويل حوالى 3 دقائق

---

اضف StoredProcedure

كود :

CREATE PROCEDURE dbo.StoredProcedure1
AS

  DECLARE @ID    AS INT
  DECLARE @Total AS INT

  DECLARE row_cursor CURSOR FOR  

  SELECT ID,
         SUM(total) AS Total
  FROM   ( SELECT ID,
                  SUM(Table_2.Numpr_Total) AS total
           FROM   Table_2
           GROUP  BY ID
           UNION  ALL
           SELECT Table_3.ID,
                  SUM(Table_3.AS_Total) AS total
           FROM   TABLE_3
           GROUP  BY ID
         ) AS T
  GROUP  BY ID;

  OPEN row_cursor;  
     FETCH NEXT FROM row_cursor INTO @ID, @Total;  
     WHILE @@FETCH_STATUS = 0  
        BEGIN  

           UPDATE Table_1
           SET    Total = @Total
           WHERE  (ID = @ID);

           FETCH NEXT FROM row_cursor INTO @ID, @Total;  
     END;  
  CLOSE row_cursor;  

  DEALLOCATE row_cursor;  


RETURN

كود التنفيذ

كود :

SqlCommand cmd = new SqlCommand("StoredProcedure1", cnn);
cmd.CommandType = CommandType.StoredProcedure;
if (cnn.State != ConnectionState.Open) cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();