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 ChangeWhereClause (ByVal Sender As System.Object, ByVal e As
System.EvenArgs)
'For example, to filter "year" field I put:
Protected li As System.Web.UI.WebControls.ListItem.
(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(strOrderNumbers, _
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.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.io
Imports System.Configuration
Imports Microsoft.VisualBasic
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.
InitializeComponent()
'Agregar cualquier inicialización después de la llamada a
InitializeComponent()
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.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Requerido por el Diseñador de Windows Forms
Private components As System.ComponentModel.IContainer
'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.SqlClient.SqlDataAdapter
Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Friend WithEvents DataSet11 As Experimental.DataSet1
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.WebControls.ListItem ‘ Not works in WinForms
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.ListBox1 = New System.Windows.Forms.ListBox
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.DataSet11 = New Experimental.DataSet1
Me.DataGrid1 = New System.Windows.Forms.DataGrid
Me.ListBox2 = New System.Windows.Forms.ListBox
Me.Button1 = New System.Windows.Forms.Button
CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).BeginIni t()
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginIni t()
Me.SuspendLayout()
'
'ListBox1
'
Me.ListBox1.Items.AddRange(New Object() {"2000", "2001", "2002",
"2003"})
Me.ListBox1.Location = New System.Drawing.Point(120, 40)
Me.ListBox1.Name = "ListBox1"
Me.ListBox1.SelectionMode =
System.Windows.Forms.SelectionMode.MultiExtended
Me.ListBox1.Size = New System.Drawing.Size(120, 95)
Me.ListBox1.TabIndex = 0
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping()
{New System.Data.Common.DataTableMapping("Table", "spFnCom", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("year",
"year"), New System.Data.Common.DataColumnMapping("client", "client"), New
System.Data.Common.DataColumnMapping("quantity", " quantity ")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "[ spFnCom]"
Me.SqlSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE" ,
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0,
Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@year", System.Data.SqlDbType.NVarChar,
2000))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@client",
System.Data.SqlDbType.NVarChar, 2000))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=RENEPC;packet
size=4096;integrated security=SSPI;data source=""RE" & _
"NEPC"";persist security info=False;initial catalog=prueba"
'
'DataSet11
'
Me.DataSet11.DataSetName = "DataSet1"
Me.DataSet11.Locale = New System.Globalization.CultureInfo("es-ES")
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.DataSource = Me.DataSet11. spFnCom
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(176, 184)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(296, 80)
Me.DataGrid1.TabIndex = 1
'
'ListBox2
'
Me.ListBox2.Items.AddRange(New Object() {"101", "102", "103"})
Me.ListBox2.Location = New System.Drawing.Point(296, 40)
Me.ListBox2.Name = "ListBox2"
Me.ListBox2.SelectionMode =
System.Windows.Forms.SelectionMode.MultiExtended
Me.ListBox2.Size = New System.Drawing.Size(120, 95)
Me.ListBox2.TabIndex = 2
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(536, 80)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 3
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = 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.DataSet11,
System.ComponentModel.ISupportInitialize).EndInit( )
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit( )
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)
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(sOrder, 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(sOrder1, sOrder1.Length() - 1)
End If
With Me.SqlSelectCommand1
.Parameters("@year").Value = sOrder
.Parameters("@client").Value = sOrder1
Me.SqlDataAdapter1.Fill (DataSet11. spFnCom)
End With
End Sub
End Class
--------------------------------------------------------------------
IV- SQL Server Scripts:
SET QUOTED_IDENTIFIER 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 @separatorposition 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 @separatorPosition = patindex('%,%', @csvlist)
select @arrayValue = left(@csvlist, @separatorPosition - 1)
INSERT into @csvtable(val) values (rtrim(ltrim(@arrayValue)))
select @csvlist = stuff(@csvlist,1,@separatorPosition, '')
end
-- return table
return
END
GO
SET QUOTED_IDENTIFIER 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
