How to pass values to a parameter used within IN statement

Topics: Data Access Application Block
Jun 11, 2010 at 6:39 PM

Hi,

Can someone please help me execute the following SQL using EntLib 5

string mySql = "UPDATE tablename " +

"SET Status = 1 " +

"WHERE RecID IN (@RecID)";

 

This is what I am trying

string sRecIds = "234, 2234, 4345"

using (var sqlCmd = database.GetSqlStringCommand(mySql))
{
     database.AddInParameter(sqlCmd, "RecId", DbType.Int32, sRecIds); // not sure what DbType should I use - tried String & Int32 none is working
     database.ExecuteNonQuery(sqlCmd);
}

I have no problem running other queries. Only those queries are not working which have "IN" statement in sql.

Thanks,

-Ned

Jun 15, 2010 at 12:46 PM

Hello Ned,

I think what you need here is to use Table-Valued Parameter Types. For more details go to http://msdn.microsoft.com/en-us/library/bb675163.aspx  and see if this fit with your needs. You can also check this thread for other reference http://entlib.codeplex.com/Thread/View.aspx?ThreadId=46963.

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Jun 15, 2010 at 8:15 PM

I am using SQL 2005 and the following command gives me an error (Incorrect syntax near the keyword 'AS').

CREATE TYPE dbo.CategoryTableType AS TABLE
( CategoryID int, CategoryName nvarchar(50) )

This is my modified solution using Data Table which still does not work

var dataTable = new DataTable();
dataTable.Columns.Add("MsgID", typeof(Int32));

dataTable.Rows.Add(100);
dataTable.Rows.Add(101);

string mySql = "UPDATE Messages " +
            "SET    Status = 1 " +
            "Where  Status = 0 And MsgID In " +
            "(Select MsgID From @tvMessages)";

using (var sqlCmd = database.GetSqlStringCommand(mySql))
{
     var db = (SqlDatabase)database;
     db.AddInParameter(sqlCmd, "@tvMessages", SqlDbType.Structured, dataTable);
     database.ExecuteNonQuery(sqlCmd);
}

I am using this Enterprise Library first time and maybe doing some stupid stuff but you can correct me?

Thanks,
-Ned
Jun 16, 2010 at 6:55 AM

You might want to consider using this as this will eliminate the need to cast your Database object to SqlDatabase.

string mySql = "UPDATE tablename " +

"SET Status = 1 " +

"WHERE CHARINDEX (CAST(RecID AS nvarchar), @RecID) > 0";

string sRecIds = "'234, 2234, 4345'"

using (var sqlCmd = database.GetSqlStringCommand(mySql))
{
     database.AddInParameter(sqlCmd, "RecID", DbType.String, sRecIds); 
     database.ExecuteNonQuery(sqlCmd);
}

 

Sarah Urmeenta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jun 16, 2010 at 2:01 PM
Edited Jun 16, 2010 at 2:06 PM

Sarah: My problem is solved with your solution, thanks a million.

I am also interested in Table-Valued Parameter Types solution which Gino suggested because that looks very scalable to me.


Jun 17, 2010 at 7:25 AM

Hello Ned,

As far as I know, Table-Valued Parameters are a new feature included in SQL Server 2008. Unfortunately, I don't have SQL 2005 installed in my machine to try and confirm if it really won't work on that. Anyway, just in case you opt to shift to SQL Server 2008. Here's a sample of using a table-valued parameter almost same with your code snippet above. Please take note of the added comments in the code.

Table-Valued Parameter sql script

CREATE TYPE [dbo].[ProductTableType] AS TABLE(
	[PRODUCTID] [int] NULL, [ProductName] [nvarchar](500) NULL)

Application Code

            //This datatable represents your Table-Value Parameter Type created
            //placeholder for parameter values to query in the "IN" statement
            //Note: Make sure that the number and datatype of Columns added here is same with the created TVP
            DataTable dt = new DataTable("ListOfProductIds");
            dt.Columns.Add("Id", typeof(System.Int32));
            dt.Columns.Add("Name", typeof(System.String));
            dt.Rows.Add("1");
            dt.Rows.Add("2");
            
            //use SqlDatabase to utilize SqlDbType
            SqlDatabase DB = EnterpriseLibraryContainer.Current.GetInstance<Database>() as SqlDatabase;


            SqlConnection connection = new SqlConnection(DB.ConnectionString);
            string sqlInsert = "SELECT * FROM [Products].[dbo].[Products]" +
                               "WHERE [ProductID] IN (SELECT ProductID FROM @TVPProductName)";
            
            //use SqlCommand instead of DbCommand
            SqlCommand sqlCommand = new SqlCommand(sqlInsert, connection);
            
            //use SqlParameter to be able to define the TypeName of the parameter
            SqlParameter tvpParam = sqlCommand.Parameters.AddWithValue(
                "@TVPProductName", dt);
            tvpParam.SqlDbType = SqlDbType.Structured;
            //should be the name of your TVP
            tvpParam.TypeName = "dbo.ProductTableType";

            sqlCommand.Connection.Open();
            using (IDataReader reader = sqlCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader[1].ToString());
                }
            }
            sqlCommand.Connection.Close();
            Console.ReadLine();

Hope this helps.

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jun 17, 2010 at 7:02 PM

Beautiful...it worked out in SQL 2008.

Not all of our clients are using this new SQL 2008 so I have to save these settings until everyone migrate to new sql.

Thanks a million,

 

Mar 16, 2011 at 1:27 AM
AvanadeSupport wrote:

Hello Ned,

As far as I know, Table-Valued Parameters are a new feature included in SQL Server 2008. Unfortunately, I don't have SQL 2005 installed in my machine to try and confirm if it really won't work on that. Anyway, just in case you opt to shift to SQL Server 2008. Here's a sample of using a table-valued parameter almost same with your code snippet above. Please take note of the added comments in the code.

Table-Valued Parameter sql script

CREATE TYPE [dbo].[ProductTableType] AS TABLE(
[PRODUCTID] [int] NULL, [ProductName] [nvarchar](500) NULL)

Application Code

            //This datatable represents your Table-Value Parameter Type created
//placeholder for parameter values to query in the "IN" statement
//Note: Make sure that the number and datatype of Columns added here is same with the created TVP
DataTable dt = new DataTable("ListOfProductIds");
dt.Columns.Add("Id", typeof(System.Int32));
dt.Columns.Add("Name", typeof(System.String));
dt.Rows.Add("1");
dt.Rows.Add("2");

//use SqlDatabase to utilize SqlDbType
SqlDatabase DB = EnterpriseLibraryContainer.Current.GetInstance<Database>() as SqlDatabase;


SqlConnection connection = new SqlConnection(DB.ConnectionString);
string sqlInsert = "SELECT * FROM [Products].[dbo].[Products]" +
"WHERE [ProductID] IN (SELECT ProductID FROM @TVPProductName)";

//use SqlCommand instead of DbCommand
SqlCommand sqlCommand = new SqlCommand(sqlInsert, connection);

//use SqlParameter to be able to define the TypeName of the parameter
SqlParameter tvpParam = sqlCommand.Parameters.AddWithValue(
"@TVPProductName", dt);
tvpParam.SqlDbType = SqlDbType.Structured;
//should be the name of your TVP
tvpParam.TypeName = "dbo.ProductTableType";

sqlCommand.Connection.Open();
using (IDataReader reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader[1].ToString());
}
}
sqlCommand.Connection.Close();
Console.ReadLine();

Hope this helps.

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Hi Gino,

First of all, excellent post.  I have been looking around for this.  

By looking at your codes, I have two questions and hope you can answer them:

1.  Is it possible to use stored procedure instead of inline query string?  If so, what would be the syntax?

2.  I am currently using the DatabaseFactory stuff.  My codes are something like: 

            Database db = DatabaseFactory.CreateDatabase();
            //TODO:  FILL IN THE SPROC NAME BELOW
            string sqlString = "sp_FILL_IN_THIS_PROC_NAME_HERE";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlString);
            List<son> rvrList = new List<son>();
            using (var reader = db.ExecuteReader(dbCommand))
            {
                while (reader.Read())

How do I keep using the same Database object here instead of using SqlDatabase?

Once again, thank you,

le9569

Mar 16, 2011 at 2:08 AM
Edited Mar 16, 2011 at 2:09 AM

Yes, you can make use of a stored procedure but you would need to create a user-defined table in order to create the corresponding parameter type for the table-valued parameter.  Refer here on how to create user-defined table.

Here's the other version of the code you want:

DataTable dt = new DataTable("ListOfIds");
dt.Columns.Add("Id", typeof(System.Int32));
dt.Columns.Add("Value", typeof(System.String));
dt.Rows.Add("13");
dt.Rows.Add("15");
Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
DbCommand command = db.GetStoredProcCommand("SelectProducts");

//create a SqlParameter instead of making use of db.AddInParameter 
//since it uses DbType which doesn't have a corresponding type for SqlDbType.Structured
SqlParameter tvpParam = new SqlParameter();
tvpParam.ParameterName = "@idList";
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.Value = dataTable;
command.Parameters.Add(tvpParam);

using (IDataReader reader = db.ExecuteReader(command))
{
          while (reader.Read())
          {
                Console.WriteLine(reader[1].ToString());
          }
}

For your reference, here's the stored proc and user defined table I used for this code 

CREATE TYPE dbo.IdList AS TABLE 
(
	Id int,
	Value nvarchar(50)
)
GO
CREATE PROCEDURE SelectProducts
        @idList IdList READONLY
AS
SELECT * FROM [Products]
WHERE Id IN (SELECT Id FROM @idList)

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Mar 16, 2011 at 4:04 AM

Thank you much,

I have found this thread described exactly what I was looking for:  http://entlib.codeplex.com/discussions/46963

 

Thanks and good day.