الفريق العربي للبرمجةأرشيف المنتديات · 2000 – 2023
نسخة أرشيفية للقراءة فقط — التسجيل والمشاركة مغلقان، والمحتوى محفوظ كما كان.

هل يمكن حل هذه المشكلة بجملة sql واحد

مغلق
بدأه عبد المعز في 3 يونيو 2007 · 5 رد · 649 مشاهدة · في ADO.NET
مشاركة: واتساب X فيسبوك تيليجرام
#1 صاحب الموضوع

وهذا هو الكود :

Dim connectionString As String = "Data Source=DB;Initial Catalog=OnlineTrading;Integrated Security=True"
		Dim con As New Data.SqlClient.SqlConnection(connectionString)
		Dim cmd As New Data.SqlClient.SqlCommand()
		Dim cmd2 As New Data.SqlClient.SqlCommand()
		Dim cmd3 As New Data.SqlClient.SqlCommand()
		Dim cmd4 As New Data.SqlClient.SqlCommand()
		Dim cmd5 As New Data.SqlClient.SqlCommand()
		Dim cmd6 As New Data.SqlClient.SqlCommand()
		Dim cmd7 As New Data.SqlClient.SqlCommand()




		cmd.CommandType = Data.CommandType.Text
		cmd.Connection = con
		cmd.Parameters.Add("@DAT1", Data.SqlDbType.DateTime).Value = Calendar1.SelectedDate
		cmd.Parameters.Add("@DAT2", Data.SqlDbType.DateTime).Value = Calendar2.SelectedDate
		cmd.CommandText = "select count(*) from users where (CreationDate>= @DAT1) AND (CreationDate <= @DAT2)"

		cmd2.CommandType = Data.CommandType.Text
		cmd2.Connection = con
		cmd2.Parameters.Add("@DAT1", Data.SqlDbType.DateTime).Value = Calendar1.SelectedDate
		cmd2.Parameters.Add("@DAT2", Data.SqlDbType.DateTime).Value = Calendar2.SelectedDate
		cmd2.CommandText = "select count(distinct(userid)) from orders where (OrderDate>= @DAT1) AND (OrderDate <= @DAT2)"



		cmd3.CommandType = Data.CommandType.Text
		cmd3.Connection = con
		cmd3.Parameters.Add("@DAT1", Data.SqlDbType.DateTime).Value = Calendar1.SelectedDate
		cmd3.Parameters.Add("@DAT2", Data.SqlDbType.DateTime).Value = Calendar2.SelectedDate
		cmd3.CommandText = "select count (distinct(orderid)) from orders where ((brokerid != 1) and (brokerid != 0 and completed=1)) and ((OrderDate>= @DAT1) AND (OrderDate <= @DAT2))"


		cmd4.CommandType = Data.CommandType.Text
		cmd4.Connection = con
		cmd4.Parameters.Add("@DAT1", Data.SqlDbType.DateTime).Value = Calendar1.SelectedDate
		cmd4.Parameters.Add("@DAT2", Data.SqlDbType.DateTime).Value = Calendar2.SelectedDate
		cmd4.CommandText = "select sum(quantity * price) from orders , tickers where (orders.tickerid=tickers.tickerid and completed=1 and (brokerid = 1 or brokerid = 0 )and marketid=2) and ((OrderDate>= @DAT1) AND (OrderDate <= @DAT2))"

		cmd5.CommandType = Data.CommandType.Text
		cmd5.Connection = con
		cmd5.Parameters.Add("@DAT1", Data.SqlDbType.DateTime).Value = Calendar1.SelectedDate
		cmd5.Parameters.Add("@DAT2", Data.SqlDbType.DateTime).Value = Calendar2.SelectedDate
		cmd5.CommandText = "select sum(quantity * price) from orders , tickers where (orders.tickerid=tickers.tickerid and completed=1 and (brokerid = 1 or brokerid = 0 )and marketid=15) and ((OrderDate>= @DAT1) AND (OrderDate <= @DAT2))"

		cmd6.CommandType = Data.CommandType.Text
		cmd6.Connection = con
		cmd6.Parameters.Add("@DAT1", Data.SqlDbType.DateTime).Value = Calendar1.SelectedDate
		cmd6.Parameters.Add("@DAT2", Data.SqlDbType.DateTime).Value = Calendar2.SelectedDate
		cmd6.CommandText = "select sum(quantity * price) from orders , tickers where (orders.tickerid=tickers.tickerid and completed=1 and (brokerid != 1 and brokerid != 0 )and marketid=2) and ((OrderDate>= @DAT1) AND (OrderDate <= @DAT2))"

		cmd7.CommandType = Data.CommandType.Text
		cmd7.Connection = con
		cmd7.Parameters.Add("@DAT1", Data.SqlDbType.DateTime).Value = Calendar1.SelectedDate
		cmd7.Parameters.Add("@DAT2", Data.SqlDbType.DateTime).Value = Calendar2.SelectedDate
		cmd7.CommandText = "select sum(quantity * price) from orders , tickers where (orders.tickerid=tickers.tickerid and completed=1 and (brokerid != 1 and brokerid != 0 )and marketid=15) and ((OrderDate>= @DAT1) AND (OrderDate <= @DAT2))"







		con.Open()
		Dim numusers As String = CInt(cmd.ExecuteScalar())
		Label1.Text = numusers.ToString

		Dim numtrd As String = CInt(cmd2.ExecuteScalar())
		Label2.Text = numtrd.ToString

		Dim numontrd As String = CInt(cmd3.ExecuteScalar())
		Label3.Text = numontrd.ToString


		Dim numvoldfmotc As String = CInt(cmd4.ExecuteScalar() / 1000)
		Label4.Text = numvoldfmotc.ToString

		Dim numvoladsmotc As String = CInt(cmd5.ExecuteScalar() / 1000)
		Label5.Text = numvoladsmotc.ToString

		Dim numvoldfmot As String = CInt(cmd6.ExecuteScalar() / 1000)
		Label6.Text = numvoldfmot.ToString

		Dim numvoladsmot As String = CInt(cmd7.ExecuteScalar() / 1000)
		Label7.Text = numvoladsmot.ToString

كما ترون في الكود انه تم استخدام 7 كوماند ابجكت , وهذا يبطأ الصفح و كيف يمكن لي ان اختصرها .

#2

يمكنك استخدام OleDbTransaction

>> تجارب برمجية <<

[/وسط][/وسط][/وسط]

#4

اخي TaraqVB

ممكن لي ان توضح لي الطريقة ؟

#5 
create procedure usp_stat
@DAT1 datetime ,@DAT2 datetime,
@p1 int=null out ,
@p2 int=null out ,
@p3 int=null out 
as set nocount on select @p1 = count(*) from users where (CreationDate>= @DAT1) AND (CreationDate <= @DAT2)


        select @p2 = count(distinct(userid)) from orders where (OrderDate>= @DAT1) AND (OrderDate <= @DAT2)

      select @p3 = count (distinct(orderid)) from orders where ((brokerid != 1) and (brokerid != 0 and completed=1)) and ((OrderDate>= @DAT1) AND (OrderDate <= @DAT2))

 select @p4 = .....
select @p5 = .....
set nocount off

Dim cmd As New Data.SqlClient.SqlCommand()

 cmd.Connection = conn
			cmd.CommandType = CommandType.StoredProcedure
			cmd.CommandText = "dbo.usp_stat"

		cmd.Parameters.Add("@DAT1", Data.SqlDbType.DateTime).Value = Calendar1.SelectedDate
		cmd.Parameters.Add("@DAT2", Data.SqlDbType.DateTime).Value = Calendar2.SelectedDate

   Dim  p1 As New SqlParameter("@p1", SqlDbType.int)
			p1.Direction = ParameterDirection.Output
			cmd.Parameters.Add(p1)

 Dim  p2 As New SqlParameter("@p2", SqlDbType.int)
			 p2.Direction = ParameterDirection.Output
			 cmd.Parameters.Add(p1)
.
..
.
.

cmd.ExecuteScalar()

console.print p1.value.tostring
console.print p2.value.tostring
console.print p3.value.tostring
console.print p4.value.tostring
console.print p5.value.tostring

myblog.jpeg

مدونتي

استضافة مواقع asp.net :.Net 3.5,4,4.5, MVC2 & 3,SQL server 2008 R2, Crystal reports

#6

ِاشكرك اخي الكريم ساقرأ الكود واجره وشكرا لك مرة اخرى

هذا الموضوع مغلق.

مواضيع مشابهة