Table value parameters with Enterprise Library Data Access Application Block

HowTo November 3rd, 2009

I wasn’t finding much with a quick search for enterprise library table value parameters or data access application block table value parameters. After a bit of research I discovered that versions <= 4.1 don't support tvps as well as I'd hoped. The key is you must cast (or create) a SqlDatabase and use SqlDbType.Structured. This will break your database independent code. The easiest way I see to use table parameters is create a DataTable and add columns to match the parameter. The column names of this table do not matter.

            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("ID", typeof(int)));
            table.Columns.Add(new DataColumn("Value", typeof(int)));
            table.Rows.Add(new object[] { ID, value });
 
            SqlDatabase database = (SqlDatabase)DatabaseFactory.CreateDatabase("DatabaseName");
            DbCommand dbCommand = database.GetStoredProcCommand("[StoredProcName]");
            database.AddInParameter(dbCommand, "@MyTableValueParameter", SqlDbType.Structured, table);
            using (IDataReader reader = database.ExecuteReader(dbCommand))
            ...

Tags: ,

No Comments