how to use dynamic c# code from the database

Discussion in 'ASP .Net' started by Antonio, Dec 29, 2005.

  1. Antonio

    Antonio Guest

    hello, I have a data grid being populated from any number of stored
    procedures. I need to have the totals in the footer, so I use the
    ItemDataBound event to sum up and display the results.

    this works fine, but since each stored procedure has different
    structures (columns, datatypes...) I either have to code one data grid
    for each stored procedure or do something else.

    also note, i cannot use in my T-SQL the options COMPUTE, WITH ROLLUP,
    or WITH CUBE. those are not available.

    so I was thinking I could store the code from the ItemDataBound event
    in the database and when I call the stored procedure i could call the
    stored procedure's appropriate ItemDataBound event code.

    my question is how? i need to store C# code in a table (that is the
    easy part) but when I retrieve it, how do I make my code use it at
    runtime?

    Thanks!
    Antonio, Dec 29, 2005
    #1
    1. Advertising

  2. Antonio

    Remy Guest

    To compile and run C# code on the fly, the following should give you
    some hints:

    using( CSharpCodeProvider provider = new CSharpCodeProvider() )
    {
    ICodeCompiler compiler = provider.CreateCompiler();
    ...
    string fullSource = this.MakeCode( code );
    CompilerResults results =
    compiler.CompileAssemblyFromSource(options, fullSource);
    ...
    try
    {
    Assembly assembly = results.CompiledAssembly;
    Type type = assembly.GetType( NAMESPACE_NAME + "." + CLASS_NAME
    );

    object obj = Activator.CreateInstance( type );
    MethodInfo method = type.GetMethod(METHOD_NAME);
    method.Invoke( obj, null );
    }
    ...
    }

    It's copied from:
    http://msdn.microsoft.com/smartclie...brary/en-us/dndotnet/html/automationmodel.asp

    But in general I don't think that is a very good approach. Populating
    the datagrid generically from any datasource should not really be an
    issue, right? Your core problem is how to compute the total?
    Could you use some naming convention and then have a stored proc that
    returns the total for every stored proc that you use to fill the
    datagrid?

    Remy Blaettler
    http://www.collaboral.com
    Remy, Dec 29, 2005
    #2
    1. Advertising

  3. Antonio,

    This would seem like an extreme waste. Why not just code your data grid
    to check the type of the column. If the column is a number, then sum it up.
    It's not like you can apply totals to bit fields, text fields, or date
    fields (not without some sort of conversion, but if you need to do that, the
    design of your database is corrupted).

    Hope this helps.


    --
    - Nicholas Paldino [.NET/C# MVP]
    -

    "Antonio" <> wrote in message
    news:...
    > hello, I have a data grid being populated from any number of stored
    > procedures. I need to have the totals in the footer, so I use the
    > ItemDataBound event to sum up and display the results.
    >
    > this works fine, but since each stored procedure has different
    > structures (columns, datatypes...) I either have to code one data grid
    > for each stored procedure or do something else.
    >
    > also note, i cannot use in my T-SQL the options COMPUTE, WITH ROLLUP,
    > or WITH CUBE. those are not available.
    >
    > so I was thinking I could store the code from the ItemDataBound event
    > in the database and when I call the stored procedure i could call the
    > stored procedure's appropriate ItemDataBound event code.
    >
    > my question is how? i need to store C# code in a table (that is the
    > easy part) but when I retrieve it, how do I make my code use it at
    > runtime?
    >
    > Thanks!
    >
    Nicholas Paldino [.NET/C# MVP], Dec 29, 2005
    #3
  4. Hi,


    > this works fine, but since each stored procedure has different
    > structures (columns, datatypes...) I either have to code one data grid
    > for each stored procedure or do something else.
    >
    > also note, i cannot use in my T-SQL the options COMPUTE, WITH ROLLUP,
    > or WITH CUBE. those are not available.


    What if you return your total as output parameters?


    > so I was thinking I could store the code from the ItemDataBound event
    > in the database and when I call the stored procedure i could call the
    > stored procedure's appropriate ItemDataBound event code.


    I do not think that is possible (store the code ), what you can do is store
    the method name accordingly, and then using reflection assign the correct
    method to the event.

    Now, unless that your logic inside the SP is complex I will create another
    SP (in case the output parameters can not be used) to return just the
    totals, you will get two hit to the DB (which you will have if store the
    method name) and probably will save the ItemDataBound handler.



    cheers,

    --
    Ignacio Machin,
    ignacio.machin AT dot.state.fl.us
    Florida Department Of Transportation
    Ignacio Machin \( .NET/ C# MVP \), Dec 29, 2005
    #4
  5. Antonio,
    What I would consider in this case is to include a second select statement
    in your stored procedure for each that returns a totals column. Your DataSet
    will now have an extra table, and you can use this to provide the information
    in each case.

    Hope it helps.
    Peter

    --
    Co-founder, Eggheadcafe.com developer portal:
    http://www.eggheadcafe.com
    UnBlog:
    http://petesbloggerama.blogspot.com




    "Antonio" wrote:

    > hello, I have a data grid being populated from any number of stored
    > procedures. I need to have the totals in the footer, so I use the
    > ItemDataBound event to sum up and display the results.
    >
    > this works fine, but since each stored procedure has different
    > structures (columns, datatypes...) I either have to code one data grid
    > for each stored procedure or do something else.
    >
    > also note, i cannot use in my T-SQL the options COMPUTE, WITH ROLLUP,
    > or WITH CUBE. those are not available.
    >
    > so I was thinking I could store the code from the ItemDataBound event
    > in the database and when I call the stored procedure i could call the
    > stored procedure's appropriate ItemDataBound event code.
    >
    > my question is how? i need to store C# code in a table (that is the
    > easy part) but when I retrieve it, how do I make my code use it at
    > runtime?
    >
    > Thanks!
    >
    >
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=, Dec 29, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Martin Eyles

    reusing code for repeated database use

    Martin Eyles, Jun 8, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    377
    Martin Eyles
    Jun 8, 2006
  2. pedestrian via DotNetMonster.com

    ASP.NET 2.0: To use or not to use code behind?

    pedestrian via DotNetMonster.com, Sep 27, 2006, in forum: ASP .Net
    Replies:
    5
    Views:
    331
    Steven Nagy
    Sep 28, 2006
  3. Andy B.

    Database code or application code.

    Andy B., Feb 24, 2009, in forum: ASP .Net
    Replies:
    5
    Views:
    308
    Andy B.
    Feb 25, 2009
  4. Mel
    Replies:
    2
    Views:
    181
  5. jonjon
    Replies:
    0
    Views:
    200
    jonjon
    Oct 29, 2003
Loading...

Share This Page