Passing SqlDataSource object an array as a parameter

Hi,

I am trying to get the selected options from a listbox and either pass a SqlDataSource object the array or loop through it and pass each element of the array. I then need to modify the returned databtable to graphing function, but first drop the last column. I was wondering if anyone can help me with the following:

1. Pass an array into SqlDataSource Select OR
2. Pass a single argument into the Select statement and populate a datatable without it writing over the current row each time it iterates through the foreach statement. I am looking for the dataview to append to dt each time it loops. Is there a property for dataview that behaves like the "ClearBeforeFill" for table adapters?
3. Update a parameter programmatically

Below code works, but I think it can be more efficient. Any suggestions would be greatly appreciated.

Thanks in advance!!

DataTable dt = new DataTable();
DataTable dt2 = new DataTable();
DataView dv = new DataView();

foreach(ListItem liOptions in ListBox1.Items)
{
if(liOptions.Selected)
{
SqlDataSource1.SelectParameters.Add("Parameter1", liOptions);
dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
dt2 = dv.Table;
dt.Merge(dt2);
dt2.Dispose();
SqlDataSource1.SelectParameters.Clear();
}
}

if (dt.Rows.Count > 0)
{
Graph(dt); //Pass original datatable (dt) to Graph();
dt.Columns.RemoveAt(2); //Reformat datatable (dt) and remove last column before binding to Gridview1

GridView1.DataSource = dt;
GridView1.DataBind();
} else {

errorMessage.Text = "No data was returned!";
}

Dear sillyrabbit,

XML. It's a new feature in SQL Server 2005. If you don't have SS2005, then I don't think this will work for you.

The best way to pass an array to is to use XML.

XmlDocument xmlDocument =new XmlDocument();XmlElement rootNode = xmlDocument.CreateElement("RootName");xmlDocument.AppendChild(rootNode);XmlElement xNode;XmlText xText;for (int intList = 0; intArray.Length > intList; ++intList){if (intArray[intList] != 0){xNode = xmlDoc.CreateElement("IdName");xText = xmlDoc.CreateTextNode(intArray[intList].ToString());rootNode.AppendChild(xNode);xNode.AppendChild(xText);}}return xmlDocument.InnerXml;

http://grahamsibley.typepad.com/thoughtfactory/2006/09/stored_procedur.html

hope this helps,

fathrDave

|||

Also,

Your SqlDbType needs to be XML.

In the Stored Procedure I used "CROSS APPLY @.xmlName.nodes('//IdName') as R(nref)" to join to my DB tables (after the FROM). I did a "dbField = nref.value('.', 'int')" to connect it to DB table (after the WHERE).

I did have some trouble with ArithAbort and had to Set it to ON before I ran the proc, and then OFF afterwards. Also, the time (timeout) is a factor and I am trying to solve that currently.

hope this helps too.

fathrDave

|||Thanks very much for the suggestion. I'll give it a try.