Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Invoking Stored Procedures through a One Line Native .NET Method Call Expand / Collapse
Author
Message
Posted Friday, November 14, 2008 6:13 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
This is definitely an interesting article, but, to echo some of the others, it does have the feel of an ad.

Moreover, it seems that this would buy you relatively little that Linq does not do for you. I agree that the SQL Linq sends may not be fully optimized for most things, but it seems to handle stored procedure calls relatively elegantly.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #603169
Posted Monday, November 17, 2008 7:14 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 12:36 PM
Points: 256, Visits: 1,063
As a 24 year software developer moving into the DBA role, I was impressed with your solution, and your understanding of the problems.

I couldn't agree more about the need to have a seemless integration of the source code, intellisense, and the current, underlying database schema for developers. In my 8 years as a SQL Programmer having used MFC's, ADO's, a self-designed set of datatype sensitive container classes, and in one case using purely dynamic SQL to eliminate some of the current difficulties associated with using stored procedures, getting data into and out of the database, from and to memory using software, was a difficult proposition fraught with the potential for programmer error. There were too many issues and variables to be considered for every database access to do it reliably 100% of the time.

Combine that with database alterations unknown to the software development staff and you have a failed application.

You are absolutely correct. The errors should show up at compile time, not run time. And it should be much easier for programmers to implement database access correctly. That can only occur if they have the proper tools.

Yours are a well-thought-out set of tools.

LC
Post #604061
Posted Tuesday, November 18, 2008 4:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 02, 2013 8:21 AM
Points: 283, Visits: 268
Just a word of warning. This all looks good at first, but you will find it largely unmaintainable in the future. Having to constantly regenerate "wrappers" for all you're procs every time there is one little change to the parameters is daunting. Couple this with the fact that developers cannot even begin to code their business layers until you're done regenerating all the procs makes it very, very unproductive.

Our company does this today and they've been using this strategy for a couple of years now. It's made developer productivity go from a couple of days to a couple of weeks. Not to mention that the Assembly that they generate all these wrappers in has now become an extra 4 - 10mb of assembly bloat that must be passed around with all applications (I'm not worried about the space, but rather about how much memory is needed to host it). We will soon look into changing this and they are very interested in the strategy I mentioned earlier. The DAL I created to do this was created long before LINQ existed, and having used LINQ myself, I will probably continue using my own because of the pure power that it has over LINQ. For one, I have the ability to Datamap my objects with simple Attributes and can datamap to Columns (including multiple columns) and Parameters. This allows for seemless integration between the Business Objects and DataReaders, DataTables, and Commands.


Here is what a sample business object might look like. There is no code for datamapping and no direct invocation of Stored Procedures. There's only one requirement that you override the "OnSave" base method. Also, how you fill the object is up to you (Notice the constructors), usually I write a Manager / Director / BusinessService (whatever we're calling them these days) that has all the surrogate methods for my object (ex. ContactDataManager.FillByID(Guid ID)). Take a look at the static method on the class (ex. Contact c = Contact.GetByID(Guid.Empty)).

[DataMap("dbo.usp_save_Contact", System.Data.StatementType.Insert)]
[DataMap("dbo.usp_save_Contact", System.Data.StatementType.Update)]
[DataMap("dbo.usp_delete_Contact", System.Data.StatementType.Delete)]
public class Contact : BusinessBase
{
private string fname;

[DataMap("FirstName"), DataMap("ContactFirstName"), DataMap("@FirstName")]
[RequiredRule(), MinLengthRule(1), MaxLengthRule(25)]
public string FirstName
{
get { return (this.fname == null ? string.Empty : this.fname); }
set {
if (this.fname == value) return;
base.OnPropertyChanging("FirstName"); // support for DataBinding and LINQ
this.fname = value;
base.OnPropertyChanged("FirstName"); // support for DataBinding and LINQ
}
}

protected overrides OnSave(EventArgs e)
{
base.OnSave(e);
if (e.Cancel) return;
// Save the object to the Database
DataService.Update((IUpdatableObject)this);
}

public static Contact GetByID(Guid ID)
{
DataTable dt = ContactDataManager.GetByID(ID);
if (dt.Rows.Count == 0) return null;
return new Contact(dt.Rows[0]);
}

// Constructors
public Contact() : base() {}

public Contact(DbDataReader Data) : base(Data) {}

public Contact(DataRow Data) : base(Data) {}
}

Example:
Contact c = Contact.GetByID(Guid.Empty);
if (c == null) c = new Contact();
c.FirstName = "John";
if (!c.Save()) {
List rules = c.BrokenRules();
// TODO: Display the broken rules
// See the Rule Attributes applied to each Property...
}

LINQ is nice, as does a lot of this for you. However, if you ever need your business object to come from multiple sources and be fill from two different Column Names (not that uncommon when you work with Tables AND Views or alias columns in Procs), then you will have to create an entirely new set of LINQ objects because they can't map that way (believe me I tried). LINQ is used at best in very simple scenarios to get developers going fast, once you need Enterprise level code, then it severely falls short. I agree, if all you want are the nice little Proc wrapper code it creates, then use it. By the way, the Business Object I wrote above is supported by LINQ and can be also passed into the base "System.Data.Linq.DataContext" and stored in a LINQ ChangeSet for later persistance.



Post #604208
Posted Tuesday, November 18, 2008 9:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 28, 2013 1:28 PM
Points: 24, Visits: 165
if you make a proxy class with the changes you want, it will not get overwritten when you make changes. I have done this. Make a new class in a separate file that inherits the generated class.
Post #604515
Posted Wednesday, November 19, 2008 6:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 02, 2013 8:21 AM
Points: 283, Visits: 268
If you're talking about "partial" classes, yes, I am aware of that. LINQ generates all the class definitions as "partial" classes so you can simply add extra files. You shouldn't need to Inherit from the LINQ generated classes.


Post #605081
Posted Wednesday, November 19, 2008 7:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 3:34 AM
Points: 139, Visits: 4,614

In fact, using LINQ to SQL, or Entities without Partial classes can be quite painful, I think. It's nice when you want to create functions to get data filtered, for example GetBuyersHasBoughtThisMonth(). You've the function and you don't have to worry about it when you refresh the autogenerated files...

Post #605110
Posted Monday, June 22, 2009 9:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 4:06 PM
Points: 14, Visits: 230
This is brilliant!

Writing repetitive code for the myriad of mundane tasks that are required when writing complex applications can be a barrier to getting the important functionality complete. Your approach gives a simple and consistent mechanism to roll all that "code-glue". Great idea, keep up the good work.
Post #739528
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse