If this is your first visit, be sure to
check out the
FAQ
by clicking the
link above. You may have to
register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.
I think this has been answered here by TechGnome but I want to ask again in case, since then, there is a better way.
I have a strongly typed ADO.NET DataSet with a table of accounts. I added a parameter in the DataSet Designer for the table to pass an integer value. Now I need to change that to be an array because I'm changing the condition to accept multiple integer values using "WHERE IN". From what I have read here and elsewhere parameters only accept a single value. I can think of two ways to do this.
-
I think the best way is to send it a string and create the text one would use as a set in the SQL command and change the parameter to be string.
-
I don't have any threat of SQL injection so I think one can modify the text of the SQL command at run time where could add the values and not use a parameter.
-
Other?
Which do you think is the best approach? I'd rather be more proper than pragmatic.
I tried #1 but it will not accept the syntax without the parenthesis. So I tried moving the parenthesis but then I get "Conversion failed when converting the varchar value '(' to data type int." It doesn't appear that this will work. It appears that it's smart enough to know that i'm trying to mix types.
That's what I've done... you could also split the values into a temp table or table variable and use that instead. Any of the three ways works.
You could create a query in the xsd with a simple where condition then take the structure of the function in the designer file which is setup as virtual and place it into your dataset file by right clicking on the TableAdapter, select view code and do custom code.
Say the query is as follows
Code:
SELECT CustomerIdentifier ,
CompanyName ,
ContactName ,
Address ,
City ,
Region ,
PostalCode ,
Country ,
Phone ,
Fax ,
ContactTypeIdentifier
FROM Customers
WHERE ( CustomerIdentifier = @InValues );
In the TableAdapter class/Fill method find your query by the parameter (in this case @InValue) and strip the where condition, pass in your values (I setup the following to accept int array), use string.Join to concatenate to the select command text then configure the SelectCommand, finally execute the query.
Here I'm working against a modified version of NorthWind Customer table
Code:
using System.Data.SqlClient;
namespace Where_In_CS
partial class DataSet1
namespace Where_In_CS.DataSet1TableAdapters {
/// <summary>
/// This is generated by selecting the adapter in the xsd file, right click and select "view code"
/// which createsa shell for us to create our own implementation of the query that was originally
/// created in the xsd file.
/// </summary>
public partial class CustomersTableAdapter
/// <summary>
/// Return CustomerDataTable using a WHERE CustomerIdentifier IN clause e.g. WHERE CustomerIdentifier IN (1,2,4)
/// </summary>
/// <param name="dataTable"></param>
/// <param name="Values"></param>
/// <returns></returns>
/// <remarks>
/// In this case I created a new SELECT in the xsd for the Customer table with a WHERE condition that
/// has a parameter named @InValues which is unique, meaning no other queries in the xsd have this name.
/// The code below then finds that query, removes the WHERE condition in place of one with a IN clause and
/// uses the int array (converted to a string array) for returning records.
/// </remarks>
public int FillByInByCustomerIdentifier(DataSet1.CustomersDataTable dataTable, int[] Values)
for (int i = 0; i < CommandCollection.Length; i++)
if (CommandCollection[i].Parameters.Count >0)
foreach (SqlParameter item in CommandCollection[i].Parameters)
if (item.ParameterName == "@InValues")
Adapter.SelectCommand = CommandCollection[i].Clone();
int whereLocation = Adapter.SelectCommand.CommandText.IndexOf("WHERE", System.StringComparison.Ordinal);
var selectTextNoWhereCondition = Adapter.SelectCommand.CommandText.Substring(0, whereLocation);
var newSelectStatmentText = selectTextNoWhereCondition + " WHERE CustomerIdentifier IN (" + string.Join(",", Values) + ")";
Adapter.SelectCommand = new SqlCommand() { Connection = this.Connection };
Adapter.SelectCommand.CommandText = newSelectStatmentText;
if ((ClearBeforeFill == true))
dataTable.Clear();
int returnValue = this.Adapter.Fill(dataTable);
return returnValue;
throw new System.Exception("Failed to locate SELECT");
I admit it's fragile in that if you change the parameter name the code will fail but if you test your app before deployment this will raise up as an exception.
Code:
CREATE FUNCTION [dbo].[DelimitedStringToTable](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
return
Here is using it:
insert into #groupstatusKeys select items from dbo.DelimitedStringToTable(@GroupStatusKeys,',')
@GroupStatusKeys,',' =
1. The delimited string
2. The delimiter.
Thank you all. I learned much. Upon consideration I decided that adding a "selected" Boolean column to the extant table was a simpler solution. In this case i don't have to worry about multiple users stepping on other user's selections by making selection at the same time. And if this ever becomes a possibility I can add some code to check and prevent.
Advertiser Disclosure:
Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
