Select query on datatable

[lidds]

Hi,

At the moment I am using the following code to populate a datatable using a stroed procedure, which return a large amount of information from my MS SQL table. What I would like to do is then apply an SQL select statement to the datatable so that I can filter down the data in the datatable further. The reason why I want to do this is instead of having a large number of stored procedures for each senario, is this possible

Dim myDA As New OleDb.OleDbDataAdapter("spQryParentComments", myDB.myConn)
            myDA.SelectCommand.CommandType = CommandType.StoredProcedure
            myDA.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@projName", OleDb.OleDbType.VarChar)).Value = RISData.Instance.ProjectName
            myDA.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@userID", OleDb.OleDbType.VarChar)).Value = RISData.Instance.UserID

            Dim dataParentTable As DataTable = New DataTable("Parent")

' e.g.
dataParentTable.selectcommand("SELECT * WHERE [column] = [XYZ]")

            myDA.Fill(dataParentTable)

Thanks in advance

Simon

 

[MattP]

Dim dv As DataView = dt.DefaultView

dv.RowFilter = String.Format("Column1 LIKE '{0}%' AND Column2 IS NOT NULL AND Column3 = '{1}'", _
TextBox1.Text, TextBox2.Text)

dv.Sort = "Column4 DESC"

Me.DataGridView1.DataSource = dv

DataView.RowFilter Property (System.Data)

 

[Tom]

        Dim drFilter() As DataRow

        drFilter = DataSet.Tables("TableName").Select("ColumnName = [COLOR="Red"]'XYZ'[/COLOR]")

        For intIndex As Integer = 0 To (drFilter.Length - 1)
            ListBox1.Items.Add(drFilter(intIndex)("ColumnName").ToString)
        Next

 

[markjarz]

Hi Simon,

Both these solutions will work but I think you are solving the wrong problem.

Returning lots of records and then filtering out the ones you don't want is a recipie for a performance disaster.

What you are doing is like shipping the whole bookcase from NY to LA so you can select the book you want in LA. Better to select the book in NY and just ship the book.

You should always keep your recordsets as small as possible.

The right way to do what you want is to do the filtering in the stored proc on the server and pass the minimum amount of data over the wire.

You avoid lots of stored procs by creating one stored proc and pass it parameters to do the filtering for you.

Regards ..... Mark

 

[cjard]

I agree with Mark on all points except the "use one stored procdure to do everything" - I'd use a stored procedure per situation, though I ususally jsut leave the SQL statements generated by the dataset designer.. There's no point writing several stored procedures if all they are going to do is select or update records. I need to have better reason such as additional logic surrounding updates, or logging etc that should be controlled from a central place, before I use stored procedures for everything