Binding array of values to SQL Statement

  • Thread starter Thread starter marc.gibian
  • Start date Start date
M

marc.gibian

I need to formulate a query that tests that a field is a member of a
list of acceptable values, but can't figure out the mechanics. Say we
have a table with a column containing an ID. I have a list of
interesting ids in my application, for which I'd like to write a query:

SELECT * FROM table where ID in (list of values)

where (list of values) is the array of interesting ids that my
application has already built. The simple solution of selecting the
interesting IDs within the query is not available as it takes some
significant business logic to build it. Is there some form of Array
parameter that I can use to bind the values I'm interested in into a
parameter in my SQL statement? I'd love something like:

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM table WHERE ID in
(@ArrayParameter)";
cmd.Parameters.Add("@ArrayParameter", ????);
cmd.Parameters["@ArrayParamter"].Values = <ArrayList Variable>;
 
No; you can't put a list of values as a parameter.

You can write a wrapper of SqlCommand, to extend SqlCommand to work with
this types of parameter values (arrays); and internally to put your unsual
parameter value right in the sql string command.CommandText, before send it
to the Sql Server. And this will work transparently like you want. I did
this and is working beautiful.

Dumitru Sbenghe
 
Guys,

Just be aware of sql injection attacks.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Dumitru Sbenghe said:
No; you can't put a list of values as a parameter.

You can write a wrapper of SqlCommand, to extend SqlCommand to work with
this types of parameter values (arrays); and internally to put your unsual
parameter value right in the sql string command.CommandText, before send
it
to the Sql Server. And this will work transparently like you want. I did
this and is working beautiful.

Dumitru Sbenghe


I need to formulate a query that tests that a field is a member of a
list of acceptable values, but can't figure out the mechanics. Say we
have a table with a column containing an ID. I have a list of
interesting ids in my application, for which I'd like to write a query:

SELECT * FROM table where ID in (list of values)

where (list of values) is the array of interesting ids that my
application has already built. The simple solution of selecting the
interesting IDs within the query is not available as it takes some
significant business logic to build it. Is there some form of Array
parameter that I can use to bind the values I'm interested in into a
parameter in my SQL statement? I'd love something like:

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM table WHERE ID in
(@ArrayParameter)";
cmd.Parameters.Add("@ArrayParameter", ????);
cmd.Parameters["@ArrayParamter"].Values = <ArrayList Variable>;
Click to expand...
Click to expand...
 
And be aware of limited stack size when compiling queries. Imagine 20000 IDs
passed ....

There is a technique to pass parameters as binary arrary to stored
procedures and to unpack it as sql server side, but I can't recall the
location of documentation for this.


Miha Markic said:
Guys,

Just be aware of sql injection attacks.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Dumitru Sbenghe said:
No; you can't put a list of values as a parameter.

You can write a wrapper of SqlCommand, to extend SqlCommand to work with
this types of parameter values (arrays); and internally to put your unsual
parameter value right in the sql string command.CommandText, before send
it
to the Sql Server. And this will work transparently like you want. I did
this and is working beautiful.

Dumitru Sbenghe


I need to formulate a query that tests that a field is a member of a
list of acceptable values, but can't figure out the mechanics. Say we
have a table with a column containing an ID. I have a list of
interesting ids in my application, for which I'd like to write a query:

SELECT * FROM table where ID in (list of values)

where (list of values) is the array of interesting ids that my
application has already built. The simple solution of selecting the
interesting IDs within the query is not available as it takes some
significant business logic to build it. Is there some form of Array
parameter that I can use to bind the values I'm interested in into a
parameter in my SQL statement? I'd love something like:

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM table WHERE ID in
(@ArrayParameter)";
cmd.Parameters.Add("@ArrayParameter", ????);
cmd.Parameters["@ArrayParamter"].Values = <ArrayList Variable>;
Click to expand...
Click to expand...
Click to expand...
 
I'am always aware of that :).

This is one of the reason I prefer sql statements with parameters; which is
not possible for "IN" case. And my solution work without problems if you
code it properly ...


Miha Markic said:
Guys,

Just be aware of sql injection attacks.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Dumitru Sbenghe said:
No; you can't put a list of values as a parameter.

You can write a wrapper of SqlCommand, to extend SqlCommand to work with
this types of parameter values (arrays); and internally to put your unsual
parameter value right in the sql string command.CommandText, before send
it
to the Sql Server. And this will work transparently like you want. I did
this and is working beautiful.

Dumitru Sbenghe


I need to formulate a query that tests that a field is a member of a
list of acceptable values, but can't figure out the mechanics. Say we
have a table with a column containing an ID. I have a list of
interesting ids in my application, for which I'd like to write a query:

SELECT * FROM table where ID in (list of values)

where (list of values) is the array of interesting ids that my
application has already built. The simple solution of selecting the
interesting IDs within the query is not available as it takes some
significant business logic to build it. Is there some form of Array
parameter that I can use to bind the values I'm interested in into a
parameter in my SQL statement? I'd love something like:

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM table WHERE ID in
(@ArrayParameter)";
cmd.Parameters.Add("@ArrayParameter", ????);
cmd.Parameters["@ArrayParamter"].Values = <ArrayList Variable>;
Click to expand...
Click to expand...
Click to expand...
 
Dumitru Sbenghe said:
I'am always aware of that :).

This is one of the reason I prefer sql statements with parameters; which
is
not possible for "IN" case.
Click to expand...

Every SQLServer person I know I ask: hey guys, why don't you give us
parameters of array type.
And guess what, nobody knows. Go figure.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com


And my solution work without problems if you
 
Hello, I need immediate help with this problem.
From C# code I am trying to call a stored procedure, which is in a
Click to expand...
oracle 8i db.





First of all here is the definition of the function(oracle function).

//==================================================================

FUNCTION INSERT_COMMENTS( iv_acc_id IN VARCHAR2,

iv_user_id IN VARCHAR2,

iv_comment IN VARCHAR2 )

RETURN BOOLEAN;

//==================================================================









My Catch catches this error code coming for Oracle side.

//==================================================================

:In insertComments2 method => ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to
'INSERT_COMMENTS'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

//==================================================================











And here is my C# code that tries to make the call to the oracle.

//==================================================================

public static void insertComments2(string accID)

{

try

{

string useridv = "DAYBREAK";

string STR_COMMMENT = "Wellcome letter has been
generated";


//==============================================================================

//create an instance of the command object
giving the procedure name

OleDbCommand sqlCmd2 = new
OleDbCommand("ACCP50.INSERT_COMMENTS",myConn) ;



// Define the command type u r executing as a
Stored Procedure.

sqlCmd2.CommandType =
CommandType.StoredProcedure ;




sqlCmd2.Parameters.Add("iv_acc_id",OleDbType.VarChar,20);

sqlCmd2.Parameters["iv_acc_id"].Direction =
ParameterDirection.Input ;




sqlCmd2.Parameters.Add("iv_user_id",OleDbType.VarChar, 20);

sqlCmd2.Parameters["iv_user_id"].Direction =
ParameterDirection.Input ;




sqlCmd2.Parameters.Add("iv_comment",OleDbType.VarChar,40);

sqlCmd2.Parameters["iv_comment"].Direction =
ParameterDirection.Input ;



sqlCmd2.Parameters.Add("RETURN
BOOLEAN",OleDbType.Boolean);

sqlCmd2.Parameters["RETURN BOOLEAN"].Direction
= ParameterDirection.ReturnValue;



detailLog(accID,w);



//sqlCmd2.Parameters["RETURN
BOOLEAN"].Direction = ParameterDirection.ReturnValue;

//sqlCmd2.Parameters["RETURN BOOLEAN"];



sqlCmd2.Parameters["iv_acc_id"].Value = accID;
;



sqlCmd2.Parameters["iv_user_id"].Value =
useridv;



sqlCmd2.Parameters["iv_comment"].Value =
STR_COMMMENT;



// execute the stored procedure

sqlCmd2.ExecuteNonQuery();



// if ((string) (sqlCmd2.Parameters["RETURN
BOOLEAN"].Value.ToString()) == "true")

// detailLog(" Success. Comments has been
inserted successfully.", w);

// else

// detailLog(" FAILED. Comment insertion
failed.", w);



}

catch (Exception error)

{

detailLog("In insertComments2 method => "
+error.Message, w);

System.Console.Write(error.Message);

}

}



//==================================================================
 
here you are
http://www.codeproject.com/cs/database/PassingArraysIntoSPs.asp


Miha Markic said:
Dumitru Sbenghe said:
I'am always aware of that :).

This is one of the reason I prefer sql statements with parameters; which
is
not possible for "IN" case.
Click to expand...

Every SQLServer person I know I ask: hey guys, why don't you give us
parameters of array type.
And guess what, nobody knows. Go figure.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com


And my solution work without problems if you
code it properly ...
Click to expand...
Click to expand...
 
Back
Top