how to use dynamic c# code from the database

[Antonio]

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!

 

[Remy]

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

 

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

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.

 

[Ignacio Machin \( .NET/ C# MVP \)]

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,

 

[Guest]

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