Zulfiqar's web

c#: using reflection for parameters list

I have seen this question come up over and over again on how to build parameter list automatically for a given object. An obvious application is enumerating through all the properties and building up list of parameters before saving an object to the database.

How would you do it?

Simple way to do it would be to hand code each parameter name, its type, and value.

Another way would be to use reflection. Here is a sample code on how to achieve it in a very simple manner.

Here are the relevant name spaces

using System;
using System.Reflection;

A sample class:

   

public class Employee
    {

        private string fname;
        private string lname;
        private int salary;

        public Employee(){}
        public Employee(string fname,string lname,int salary)
        {
            this.fname = fname;
            this.lname = lname;
            this.salary = salary;
        }

        public string FirstName
        {
            get {return this.fname;}
            set {this.fname = value;}
        }
        public string LastName
        {
            get {return this.lname;}
            set {this.lname = value;}
        }

        public int Salary
        {
            get {return this.salary;}
            set {this.salary = value;}
        }
    }

And the test code to create the parameter list

            

Employee e = new Employee("zulfiqar","syed",33);
            
            foreach(PropertyInfo pinfo in typeof(Employee).GetProperties())
            {
                Console.WriteLine("Propety Name: {0} Type = {1} Value = {2}",
                    pinfo.Name,
                    pinfo.PropertyType,
                    pinfo.GetValue(e,new Object[] {})
                );
               
            }
            Console.WriteLine("all done");
            Console.ReadLine();

Once you execute the code, the output would look as follows:

Propety Name: FirstName Type = System.String Value = zulfiqar
Propety Name: LastName Type = System.String Value = syed
Propety Name: Salary Type = System.Int32 Value = 33
all done

hth..

Happy coding..

ZULFIQAR SYED

TSQL: Conditional WHERE clause

There are different ways of programming conditional WHERE clause.

Method 1
========

Dynamic sql
------------

declare @select varchar(7000)
declare @where varchar(7000)
declare @i int

set @select = "select * from mytable"
set @i = 1

if @i = 1 then
    set @where = " where mycol = 1 "
else
    set @where = " where mycol = 2 "

set @select = @select + @where
exec(@select)

Method 2
========

if else conditions
------------------

if @i = 1
    select * from mytable where mycol = 1
else
    select * from mytable where mycol = 2

Method 3
==========

Case instruction
-----------------

select * from mytable
where
mycol =  case when @i = 1 then 1 else 2 end

You have to make choice pending your situation but I tend to stay away from
dynamic sql as much as possible and try to use better techniques such as case
instruction (in my opinion).

Some time ago I ran into the following situation where I had to choose between the
above 3 approaches and I was able to solve it using case statement. Here is the
scenario. Lets say you have @custid and it could be matched to primary customer id
pcustid or secondary customer id scustid depending upon status parameter
@statusid in table called mytable. So if @statusid is 1 then primary matching
else secondary matching.

Our input parameters are declared as follows

declare @custid int
declare @statusid int

Here are the use cases

1. When none of the params are initialized then return all the rows.
2. When only custid is passed then return all the rows for that customer.
3. When @custid is passed along with the @statusid then only the related row
would show up.

Dynamic sql way is very easy to implement so I will not address that.
Lets look at the if then else statement.

    if (@custid is not null)
    begin
        if (@statusid = 1)
            select
                *
            from mytable t
            where t.pcustid  = @custid

        else if (@statusid= 2)
            select
                *
            from mytable t
            where t.scustid = @custid
        else
            select
                *
            from mytable t
            where
            (t.pcustid = coalesce( @custid,t.pcustid) )
            or
            (t.scustid = coalesce( @custid, t.scustid) )

    end
    else
            select
                *
            from mytable

This could be implemented as follws in terms of case statement.

    select * from mytable t
    where coalesce(t.pcustid,0) = (
                        case when (@custid is not null) then
                            case when (@pcustid = 1) then
                                @custid
                            else coalesce(t.pcustid,0)
                            end
                        else
                            coalesce(t.pcustid,0)
                        end
        )
        and coalesce(t.scustid,0) = (
                        case when (@custid is not null) then
                            case when (@pcustid = 2) then
                                @custid
                            else coalesce(t.scustid,0)
                            end
                        else
                            coalesce(t.scustid,0)
                        end
        )
        and (
            (coalesce(t.pcustid,0) = coalesce( @custid, coalesce(t.pcustid,0)) )
            or
            (coalesce(t.scustid,0)= coalesce( @custid, coalesce(t.scustid,0)) )
        )

Coaslesce is heaven sent function. In case pcustid column is null, I will not
be able to do exact match with it self if all else fails to render it as
TRUE result. So the following match would return true

coalesce(t.pcustid,0)  = coalesce(t.pcustid,0)

Otherwise null column would not result as equal

t.pcustid = t.pcustid does not return true if column is null.

So see if you could revisit your dynamic sql statements and see if you
could REFACTOR them into nice CASE driven where clause.

Thanks

ZULFIQAR SYED

SSIS: stored proc and ado.net

Here is another way to pass value out of stored procedure. This one is using ado.net as Connection type for EXECUTE SQL TASK.

variables are declared as follows:

Name: inp
Scope: Package1
Datatype : Int32
value: 1

Name: outp
Scope: Package1
Datatype : Int32
value: 0

Hers are the settings for the task.

Genereal tab:
    Resultset  = none
    Connection type = ado.net
    Connection = localhost.AdventureWorks
    SqlSourceType = DirectInput
    SQL Statement =

declare @o int
exec testproc @in=@i1,@out = @o output
select @o1 = @o

    IsQueryStoredProcedure = False

Parameter mapping tab
    Variable Name = User::outp
    Direction = Output
    Datatype = Int32
    Parameter Name = @o1

    Variable Name = User::inp
    Direction = Input
    Datatype = Int32
    Parameter Name = @i1

Resultset and Expressions tabs are left emtpy.

The stored proc looks like this..

create PROCEDURE [dbo].[testproc]
    @in int ,     @out int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select 
        @out = count(*)
        from a_cust where customerid = @in
    set nocount off
END

You can connect the output of this sql execute task to script task

Script tab
    ReadOnlyVariables = User::outp

Inside the script add the following line right under neatch public sub main so the whole method looks like this..

    Public Sub Main()
        '
        ' Add your code here
        '
        MsgBox(Dts.Variables(0).Value)

        Dts.TaskResult = Dts.Results.Success
    End Sub

Feel free to change the values of inp in the variable section and check out the output via messagebox. The variable inp could be set using any mechanism such as a database table, file , or any other source.

I hope this helps answering some of the questions regarding stored procs and sql execute task.

Thanks

ZULFIQAR SYED