using the sort and rowFilter from a stored procedure dataset

  • Thread starter Thread starter Arthur Yousif
  • Start date Start date
A

Arthur Yousif

Hi,

I'm filling a data set from a stored procedure and then I'd like to do some
more filtering. This additional filtering is not being by the sp so I can
have some control on my side of coding. The code snippet is below and
doesn't work. I'm not sure if I'm using the sort and rowFilter correctly?
I thought if I set these before getting the Table, it would return the data
post sort/filter, but that's not the case, I'm still getting all the
records. Thanks for your help.

SqlConnection sqlConn = new SqlConnection(sConn);
SqlCommand sqlCmd = new SqlCommand("myStoredProcedure", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter p1;

p1 = new SqlParameter("@parm1", SqlDbType.Int);
p1.Direction = ParameterDirection.Input;
p1.Value = m_contactID;
sqlCmd.Parameters.Add(p1);

sqlCmd.Connection = sqlConn;

SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
sda.Fill(ds);

DataViewManager dvm = ds.DefaultViewManager;
DataViewSettingCollection dvsc = dvm.DataViewSettings;

dvsc[0].RowStateFilter = DataViewRowState.ModifiedCurrent;
dvsc[0].Sort = m_sortField + " " + m_sortValue;
dvsc[0].RowFilter = " statusID = 2 AND fileLoc = '" + folder +
"/'";

DataTable dtSorted = dvsc[0].Table;
DataRowCollection drc = dtSorted.Rows;

int iRows = drc.Count;
for (int r=0; r < iRows; r++)
{
DataRow dr = drc[r];

iColumns = dr.ItemArray.Length;
for (int i=0; i < iColumns; i++)
{
sList += dr.ItemArray.GetValue(i).ToString().Trim();
sList += "~";
}
}

Arthur
 
I figured this out. I needed to use the Select method on the Table object
like this:


string sSort = m_sortField + " " + m_sortValue;
string sFilter = " statusID = 2 AND fileLoc = '" + folder + "/'";
DataViewManager dvm = ds.DefaultViewManager;
DataViewSettingCollection dvsc = dvm.DataViewSettings;
DataTable dtModified = dvsc[0].Table;
DataRow[] dRows = dtModified.Select(sFilter, sSort);

foreach (DataRow srcRow in dRows)
{
iColumns = srcRow.ItemArray.Length;
for (int i=0; i < iColumns; i++)
{
sList += srcRow.ToString().Trim();
sList += "~";
}

}

I hope this helps someone else.
 
Back
Top