Hi,
My purpose consists on filtering the fields "years" and "clients" of a
project WinForm, in a similar way to as I make it
without any problems in a previous project ASP.NET.
The filtering task must be on the server side because my database is very
extensive and, in this case, filter with Dataview
is not convenient.
I have a database in SQL Server side with a view, two functions and a stored
procedure:
1. The table "tblSales" has three fields: year, client and quantity.
2. The function "fn_com1" is prepared to filter the field "year".
3. The function "fn_com2" has as purpose filtering of the field "client."
4. The stored procedure "sp_fncom" is created starting from the function
"fn_com2" and it contains the year, client and
quantity fields.
5. The SQL Server has a denominated function "CSVToTable ", a Comma Separates
Valued (CSV) that works fine.
II-WinForm1:
Visual Studio WinForm, Form1, Design Mode:
Controls:
ListBox1: It contains as Items the field "year"
("2000","2001", "2002","2003"," 2004","2005").
ListBox2: It contains as Items the field "client". Must be (" 101"," 102","
103")
(Important: Both Listbox has the multiselection activated as Multiextended.)
Button1: To execute the stored procedure already described.
Datagrid1: To show results of the stored procedure.
Also, the corresponding accesses to data, etc. (SqlDataAdpater , Datasets, etc)
III- WinForm1 Visual Basic code:
Here I have the greatest problem. I don't find the correct syntax to execute
the queries.
I don’t find the necessary Visual Studio Space Name. Maybe I need to create
my custom Space Name?
I need inside the Windows form code something similar to:
Sub ChangeWhereClau se (ByVal Sender As System.Object, ByVal e As
System.EvenArgs )
'For example, to filter "year" field I put:
Protected li As System.Web.UI.W ebControls.List Item.
(Used in a previous project ASP.NET and ListItem class not exists in Windows
Forms).
Dim strOrderNumbers As String = ""
For Each li In listbox1.Items
If li.Selected Then
strOrderNumbers &= li.Value & ","
End If
Next
If strOrderNumbers .Length > 0 Then
strOrderNumbers = Left(strOrderNu mbers, _
strOrderNumbers .Length() - 1)
End If
....etc
End sub
---------------------------------------------------------------------------------
In the followings lines I show the Public Class Form1 code:
Imports ADODB
Imports System
Imports System.Collecti ons
Imports System.Data
Imports System.Data.Sql Client
Imports System.io
Imports System.Configur ation
Imports Microsoft.Visua lBasic
Public Class Form1
Inherits System.Windows. Forms.Form
#Region " Código generado por el Diseñador de Windows Forms "
Public Sub New()
MyBase.New()
'El Diseñador de Windows Forms requiere esta llamada.
InitializeCompo nent()
'Agregar cualquier inicialización después de la llamada a
InitializeCompo nent()
End Sub
'Form reemplaza a Dispose para limpiar la lista de componentes.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Disp ose()
End If
End If
MyBase.Dispose( disposing)
End Sub
'Requerido por el Diseñador de Windows Forms
Private components As System.Componen tModel.IContain er
'NOTA: el Diseñador de Windows Forms requiere el siguiente procedimiento
'Puede modificarse utilizando el Diseñador de Windows Forms.
'No lo modifique con el editor de código.
Friend WithEvents ListBox1 As System.Windows. Forms.ListBox
Friend WithEvents SqlDataAdapter1 As System.Data.Sql Client.SqlDataA dapter
Friend WithEvents SqlSelectComman d1 As System.Data.Sql Client.SqlComma nd
Friend WithEvents SqlConnection1 As System.Data.Sql Client.SqlConne ction
Friend WithEvents DataSet11 As Experimental.Da taSet1
Friend WithEvents DataGrid1 As System.Windows. Forms.DataGrid
Friend WithEvents ListBox2 As System.Windows. Forms.ListBox
Friend WithEvents Button1 As System.Windows. Forms.Button
Protected li As System.Web.UI.W ebControls.List Item ‘ Not works in WinForms
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub
InitializeCompo nent()
Me.ListBox1 = New System.Windows. Forms.ListBox
Me.SqlDataAdapt er1 = New System.Data.Sql Client.SqlDataA dapter
Me.SqlSelectCom mand1 = New System.Data.Sql Client.SqlComma nd
Me.SqlConnectio n1 = New System.Data.Sql Client.SqlConne ction
Me.DataSet11 = New Experimental.Da taSet1
Me.DataGrid1 = New System.Windows. Forms.DataGrid
Me.ListBox2 = New System.Windows. Forms.ListBox
Me.Button1 = New System.Windows. Forms.Button
CType(Me.DataSe t11,
System.Componen tModel.ISupport Initialize).Beg inInit()
CType(Me.DataGr id1,
System.Componen tModel.ISupport Initialize).Beg inInit()
Me.SuspendLayou t()
'
'ListBox1
'
Me.ListBox1.Ite ms.AddRange(New Object() {"2000", "2001", "2002",
"2003"})
Me.ListBox1.Loc ation = New System.Drawing. Point(120, 40)
Me.ListBox1.Nam e = "ListBox1"
Me.ListBox1.Sel ectionMode =
System.Windows. Forms.Selection Mode.MultiExten ded
Me.ListBox1.Siz e = New System.Drawing. Size(120, 95)
Me.ListBox1.Tab Index = 0
'
'SqlDataAdapter 1
'
Me.SqlDataAdapt er1.SelectComma nd = Me.SqlSelectCom mand1
Me.SqlDataAdapt er1.TableMappin gs.AddRange(New
System.Data.Com mon.DataTableMa pping()
{New System.Data.Com mon.DataTableMa pping("Table", "spFnCom", New
System.Data.Com mon.DataColumnM apping() {New
System.Data.Com mon.DataColumnM apping("year",
"year"), New System.Data.Com mon.DataColumnM apping("client" , "client"), New
System.Data.Com mon.DataColumnM apping("quantit y", " quantity ")})})
'
'SqlSelectComma nd1
'
Me.SqlSelectCom mand1.CommandTe xt = "[ spFnCom]"
Me.SqlSelectCom mand1.CommandTy pe =
System.Data.Com mandType.Stored Procedure
Me.SqlSelectCom mand1.Connectio n = Me.SqlConnectio n1
Me.SqlSelectCom mand1.Parameter s.Add(New
System.Data.Sql Client.SqlParam eter("@RETURN_V ALUE",
System.Data.Sql DbType.Int, 4,
System.Data.Par ameterDirection .ReturnValue, False, CType(0, Byte), CType(0,
Byte), "",
System.Data.Dat aRowVersion.Cur rent, Nothing))
Me.SqlSelectCom mand1.Parameter s.Add(New
System.Data.Sql Client.SqlParam eter("@year", System.Data.Sql DbType.NVarChar ,
2000))
Me.SqlSelectCom mand1.Parameter s.Add(New
System.Data.Sql Client.SqlParam eter("@client",
System.Data.Sql DbType.NVarChar , 2000))
'
'SqlConnection1
'
Me.SqlConnectio n1.ConnectionSt ring = "workstatio n id=RENEPC;packe t
size=4096;integ rated security=SSPI;d ata source=""RE" & _
"NEPC"";per sist security info=False;init ial catalog=prueba"
'
'DataSet11
'
Me.DataSet11.Da taSetName = "DataSet1"
Me.DataSet11.Lo cale = New System.Globaliz ation.CultureIn fo("es-ES")
'
'DataGrid1
'
Me.DataGrid1.Da taMember = ""
Me.DataGrid1.Da taSource = Me.DataSet11. spFnCom
Me.DataGrid1.He aderForeColor = System.Drawing. SystemColors.Co ntrolText
Me.DataGrid1.Lo cation = New System.Drawing. Point(176, 184)
Me.DataGrid1.Na me = "DataGrid1"
Me.DataGrid1.Si ze = New System.Drawing. Size(296, 80)
Me.DataGrid1.Ta bIndex = 1
'
'ListBox2
'
Me.ListBox2.Ite ms.AddRange(New Object() {"101", "102", "103"})
Me.ListBox2.Loc ation = New System.Drawing. Point(296, 40)
Me.ListBox2.Nam e = "ListBox2"
Me.ListBox2.Sel ectionMode =
System.Windows. Forms.Selection Mode.MultiExten ded
Me.ListBox2.Siz e = New System.Drawing. Size(120, 95)
Me.ListBox2.Tab Index = 2
'
'Button1
'
Me.Button1.Loca tion = New System.Drawing. Point(536, 80)
Me.Button1.Name = "Button1"
Me.Button1.TabI ndex = 3
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBas eSize = New System.Drawing. Size(5, 13)
Me.ClientSize = New System.Drawing. Size(664, 406)
Me.Controls.Add (Me.Button1)
Me.Controls.Add (Me.ListBox2)
Me.Controls.Add (Me.DataGrid1)
Me.Controls.Add (Me.ListBox1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataSe t11,
System.Componen tModel.ISupport Initialize).End Init()
CType(Me.DataGr id1,
System.Componen tModel.ISupport Initialize).End Init()
Me.ResumeLayout (False)
End Sub
#End Region
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s)
Handles Button1.Click
Dim sOrder As String = ""
For Each oList In ListBox1.Items
If oList.Selected Then
sOrder &= oList.Value & ","
End If
Next
If sOrder.Length > 0 Then
sOrder = Strings.Left(sO rder, sOrder.Length() - 1)
End If
'----------------------------------------------------------
Dim sOrder1 As String = ""
For Each oList In ListBox2.Items
If oList.Selected Then
sOrder1 &= oList.Value & ","
End If
Next
If sOrder1.Length > 0 Then
sOrder1 = Strings.Left(sO rder1, sOrder1.Length( ) - 1)
End If
With Me.SqlSelectCom mand1
.Parameters("@y ear").Value = sOrder
.Parameters("@c lient").Value = sOrder1
Me.SqlDataAdapt er1.Fill (DataSet11. spFnCom)
End With
End Sub
End Class
--------------------------------------------------------------------
IV- SQL Server Scripts:
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION CSVToTable (@CSVList varchar(8000))
RETURNS @csvtable table (val varchar(1000))
AS
BEGIN
-- variables for position marking
declare @separatorposit ion int, @arrayvalue varchar(1000)
-- Pad the list if needed
if substring(rtrim (@csvlist),len( rtrim(@csvlist) ),1)<>','
set @csvlist = @csvlist + ','
-- Loop through string
while patindex('%,%', @csvlist) <> 0
begin
select @separatorPosit ion = patindex('%,%', @csvlist)
select @arrayValue = left(@csvlist, @separatorPosit ion - 1)
INSERT into @csvtable(val) values (rtrim(ltrim(@a rrayValue)))
select @csvlist = stuff(@csvlist, 1,@separatorPos ition, '')
end
-- return table
return
END
GO
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------------------------
create view dbo.vs_Sales
as
select year, client, quantity
from dbo.tbl_Sales
go
-------------------------------------------------------------------
Create function dbo.fncom1
(@year nvarchar (2000))
returns table
as
return (select year, client, quantity
from dbo.vs_Sales
where year in (select val from dbo.csvtotable (@year)))
go
-------------------------------------------------------------------
create function dbo.fncom2
(@year nvarchar (2000), @client nvarchar (2000))
returns table
as
return (select year, client, quantity
from dbo. Fncom1 (@year)
where client in (select val from dbo.csvtotable (@client)))
go
---------------------------------------------------------------------
create procedure dbo.spfncom
(@year (2000), @client(2000))
as
select top 100 percent year, client, quantity
from dbo.fncompara1 (@year, @client)
go
----------------------------------------------------------------------
Thank you in advance,
Adis
