.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

.NET Reflection: Dynamically Bind Your Data Layer to Stored Procedures and SQL Commands Using .NET M

Posted By:      Posted Date: August 21, 2010    Points: 0   Category :ASP.Net

One major inconvenience of using SQL stored procedures from code is having to match type information. You have to write long wrapper functions around the procedures just to expose the data types. In the .NET Framework, however, the reflection services of the System.Reflection namespace allow you to query rich metadata that describe data types. This article explains how to use reflection in .NET to end your stored procedure nightmares. The author creates four custom classes, including one that generates a SQLCommand object based on the metadata of a method. Using this library, you'll be on your way to automating the generation of stored procedures.

Atif Aziz

MSDN Magazine August 2002

View Complete Post

More Related Resource Links

No Return Type for a Stored Procedure in a Linq to SQL Data Layer

Hello. First, I don't know if this is the right forum section to post this in, but I couldn't fine a more suitable one. Feel free to move this if it's totally worng. I have a weird problem when using LINQ to SQL and Store Procedures with a return value... The problem is that when I drag my SP's onto the "Methods Pane" the property field "Return Type" is set to (None) and greyed out. And here's the most weird thing... I created a test SP yesterday wich returned SCOPE_IDENTITY() and it worked flawlessly. Dragged it onto the "Methods Pane", could play with the return type property (hence it was not greyed out) and everything was working out nice. I used a ISingleResult<T> and could fetch the .ReturnValue... Today I was going to contiune to create the rest of the SP's i needed with a return type but today when I drag my Store Procedures to my Methods Pane, I couldn't access the Return Type property... I even dragged the SAME procedure I used yesterday (wich then worked great) and now I can't get the dbml-layer to notice that there is a return value... I've tried to create new solutions, new Data Layers, used different databases/servers, but still the same problem... Don't really know if it's LINQ or something else that's causing this... This is how part of my SP wich I used yesterday to test looks

Create Excel file dynamically in SSIS package and fill it by resultset data returned by Stored proce


Hello Every One,

I have a scenario in which i am executing a Stored Procedure which returned a result set.

I want to create a Excel file [name_date_time] dynamically, and fill result set data into Excel file. And then finally sent Excel file to Specific user[Email ID].

Point is that the excel file should have unique name so i want to give name as name_date_time.

So every time when package run new file is created with unique name.


Can any one suggest me who to create Excel file and fill result set data into Excel file.

And then finally send that Excel file ?







Stored Procedures using XQuery to insert XML data into tables


If I have the following XML being passed as a parameter into a stored procedure.


<name>My Bookstore</name><br/>
<location>New York</location><br/>
 <title lang="eng"&g

how to call the Stored procedures dynamically


 hi frndz,

                 ringht now i am working with one web page, in that page

i have 3 sections of data [or 3 set of data]

in 1 set 4 to 5 fields

actions to be taken

when we click on save button

1st it should save the 1st fields data in one of my sp

and it should get the table level id

it should loop that table level id into another set of records & insert into to the another sp

same should be continued twice

pl some one help me


Data View Web part, stored procedures, and sorting


I know I'll probably never get an answer to this because it seems that no one uses the Data View Web Part and there is a dearth of documentation (sigh), but here goes...


In MOSS2007, I need to call a stored procedure and get the data back and display it in the Data View Web Part.  I can do that.


The problem is enabling sorting.  I can enable sorting in the DVWP and it allows me to click the column headings and supposedly sort the data - but it doesn't sort it.  It does a postback, but doesn't affect the output.


I did some tracing with SQL Profiler, and found that if I use a SELECT statement, the sorting causes the web part to add the "ORDER BY" clause using the appropriate field.


However, since this is a stored proc, it does not seem to do anything - it just keeps calling the same stored proc over and over.


What I suppose I need it to do is pass the stored proc a parameter to tell it what order to sort the data - but I don't see any way to do that.  I can pass the stored proc parameters via the DVWP, but how do I figure out what sort field was selected and pass that?  Anyone know?


Transact-SQL Built-In Stored Procedures

To assist you with managing Microsoft SQL Server databases, Transact-SQL provides many built-in stored procedures.

Renaming an Object

Sending Email

SqlDataSource how to change the stored procedures at run time



Is it possible to change the select, insert and update stored procedure of an SqlDataSource at run time. I don't mean to change the definition of the stored procedure, but I mean to change the assignment, i.e. To change the select stored procedure from "selectSP1" to "selectSP2" at run time. Is it possible?



Data Truncation issue with Enterprise Library Logging WriteLog stored Proc


Hi ,

I'm using Enterprise Library Logging  feature for logging. The issue i am facing is when the Logging message is too large(more than 65534 chars) ,complete data  is not logged in the Formatted Mesage column which is  of data Type nText .

I am able insert complete data if i try inserting from Sql insert Query from sql management studio. Do i need to add any attributes to data base listener or do i need to change the sp.

 Is there any way to increase the WriteLog stored proc param size in EnterpriseLibrary.Logging config file ? . Please let me know.


Thanks In Advance.

Persist data in a dynamically populated ListBox in a user control


Hi All,

I am trying to persist data in a dynamically populated ListBox in a user control. Here are the details

I have a user control which contains a a listBox and button. On Page_Load of user control I am populating the ListBox control with some values. On Click of button, I am trying to fetch the values which I have selected in ListBox. Though this seems very simple and straight forward I am unable to fetch values.

My Page_Load Function

void Page_Load(object sender, EventArgs e)










My Click Function

Protected Void Button1_Click(object sender, EventArgs e)


       string strValue = listBox.SelectedValue



I have placed brake point at Page_Load and on click function, I am seeing that the moment the breakpoin

Test Run: Testing SQL Stored Procedures Using LINQ


Language Integrated Query makes lots of things easier. Here we put LINQ, or more specifically the LINQ to SQL provider, to use testing SQL stored procedures.

Dr. James McCaffrey

MSDN Magazine April 2008

Know Thy Code: Simplify Data Layer Unit Testing using Enterprise Services


If you want to employ unit testing and test-driven development techniques in your database application development process, you'll have different factors to consider than you do when you're not involving a database. For example, you have to maintain a consistent state within the database and be able to roll back transactions when necessary. This article shows you how to get the best of unit testing for your database apps in a safe, usable manner.

Roy Osherove

MSDN Magazine June 2005

Advanced T-SQL: Automate the Generation of Stored Procedures for Your Database


Design-time automation makes coding faster and ensures that all the procedures generated use the same naming conventions and structure. In an effort to improve their coding efficiency in a large SQL project, the authors wrote a set of design-time stored procedures that generate run-time stored procedures, and have used them in project after project ever since. Recently, the authors updated their procedures to make use of SQL Server 2000 features, including user-defined functions. This article covers the creation and execution of these dynamic T-SQL scripts to automate the coding of common database stored procedures.

Peter W. DeBetta and J. Byer Hill

MSDN Magazine April 2003

Data Access: Implement a Data Access Layer for Your App with ADO.NET


Implementing data access functionality is a core activity of most developers working with the .NET Framework, and the data access layers they build are an essential part of their applications. This article outlines five ideas to consider when building a data access layer with Visual Studio .NET and the .NET Framework. The tips include taking advantage of object-oriented techniques and the .NET Framework infrastructure by using base classes, making classes easily inheritable by following guidelines, and carefully examining your needs before deciding on a presentation method and external interface.

Dan Fox

MSDN Magazine April 2003

SQL and XML: Use XML to Invoke and Return Stored Procedures Over the Web


Front-end developers frequently want to add functionality to the presentation tier of an n-tier architecture, but such requests can require changes on all tiers just to get the data and present it. This process can be made easier and more flexible by using SQL Server stored procedures to automate the delivery of data in XML format from the database to the front-end components. In the component presented here, stored procedures are invoked by XML strings, XML is returned, converted using XSL, and presented to the client in HTML. The technique supports rapid changes yet doesn't sacrifice the n-tier approach. This approach can be used with either SQL Server 7.0 or SQL Server 2000.

Dave Grundgeiger, Anson Goldade, and Varon Fugman

MSDN Magazine August 2001

Beyond ASP: XML and XSL-based Solutions Simplify Your Data Presentation Layer


The combination of XML and XSL can provide a powerful alternative to ASP development. This article presents arguments for building even small-scale Internet applications on the XML model. An example written with traditional ASP programming is compared to the same example written with XML and XSL in order to show the benefits of this approach. The example is followed by nine good reasons to make the switch. These reasons include separation of presentation and data, reusability, extensibility, division of labor, enhanced testing, and legacy integration. The XML/XSL solutions described hold the promise of greater simplicity, flexibility, and durability than ASP solutions built the traditional way.

Scott Howlett and Jeff Dunmall

MSDN Magazine November 2000

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend