Table Level Access

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    Timothy (4/24/2008)


    Jack Corbett (4/24/2008)


    I actually am not against Linq To SQL either, I just wish MS had made it easier to use sp's in Linq and/or did something like iBATIS.NET does (I just learned about this on this thread and it is not an ORM tool like Linq) where you can map your database queries outside the classes created by Linq To SQL.

    Have you tried out Visual Studio 2008 yet? It has a LINQ to SQL classes template (a DataContext) that makes using stored procedures easy. The way I've been doing it is to create a class in the visual editor that has properties that map to the result set of a stored procedure. The stored procedure itself is added as a method of the DataContext that returns an IEnumerable<> of the class you created earlier. If you don't want to create the aforementioned class yourself though by default Visual Studio will generate a class for you that has properties that map to the result set, it'll be named the same as the stored procedure only ending with the word "Result". All of this is real similar to TableAdapters in Visual Studio 2005, which are still supported.

    In your code you would use the DataContext with something like this:

    TMSDataContext dc = new TMSDataContext();

    DuplicateTimesheetHours[] dups = dc.CheckDuplicateTimesheetHours(fiscalYear, paymentType[0], payPeriod).ToArray();

    I actually have tried it, and it's not impossible to use. The problem I see with that is that it's a manual build process, compare to a lot of the "automated tools" they have out there which can zip through and rebuild the schema in a DataContext based on the base tables. So - 100% of the demos (and as a result, a large percentage of the projects based on information from those demos) end up using direct table access DataContexts....sigh....

    I don't disagree with you that it could be a nice tool - I just see so many pitfalls with it right now, I can't stop treating it like an unexploded hand grenade. LinQ "to Anything" entails an implicit "I don't care what is on the other end" mentality, which with pretty much any implementation of a provider can and invariably will lead to phenomenal "abuses" of the data layer whatever that happens to be. And again - no matter how you slice it - it fundamentally screws with the separation of the layers, and in a way making it very difficult for a specialist in the specific data provider to do anything about the calls.

    I also really wish that the presenters would STOP describing LinQ to SQL as a way to allow developers to have "granular access to all data" without knowledge of SQL Server. That is the very WORST possible outcome for LinQ, and besides wrecking performance on the SQL Server side, will likely end up with the DBA team and the Dev teams meeting in back alleys for duels to the death. The way they're packaging this and pushing this as a feature in the dev community is doing a grave disservice to the accompanying data community.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Timothy-313907

    Ten Centuries

    Points: 1226

    Oh, okay.

    I went back and looked at the DataContext I've been referencing in this thread and it does what you're talking about, that is the classes are set to automatically create queries. That functionality seems a lot like what ADO.NET has always provided. I've never used that functionality to perform database operations, even back in the VB and ASP days. Instead of calling the DataContext's SubmitChanges method to potentially update multiple rows, for instance, I pass an XML structure of the data to a stored procedure to do the update. Could just boil down to coding style, or maybe I need to get out of my habits and learn something new. 😛

  • Grant Fritchey

    SSC Guru

    Points: 396614

    I'm not sure if means anything in relation to LINQ and the other ORM products, but I've seen systems where the developers used ADO to automagically access the database. Small apps with only a few users can seriously take down an large scale production system because they're so chatty and the code is so inefficient. It makes me hesitant to accept that LINQ (or nHibernate or Entity Framework) won't do the same.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    Grant Fritchey (4/24/2008)


    I'm not sure if means anything in relation to LINQ and the other ORM products, but I've seen systems where the developers used ADO to automagically access the database. Small apps with only a few users can seriously take down an large scale production system because they're so chatty and the code is so inefficient. It makes me hesitant to accept that LINQ (or nHibernate or Entity Framework) won't do the same.

    If you want to see a rather extreme version of "automagic CRUD", try and dig up some of the "dynamic data web" demos with Visual Studio 2008.

    Step one. use the wizarsd to build a full schema replica of your database

    Step two. run through some VS wiard to set up a Dynamic Data Web project

    Step three. run. full, basic (not incredibly pretty but functional) CRUD on all tables in DB. Navigate from table to relate table, etc....

    total expended time: 15 minutes, maybe.

    And you step away going "that's impressive", and then you realize - "Where's the security? Whose credentials is this running under? How is this getting logged? WHO THE HELL IS CRUD'ing MY DATABASE?" (web service account had apparently been set up as SA, and sites were anonymous, consistently throughout the demo).

    Maybe it's the negative me talking, but I just had to push back from my desk and walk away, because this kind of "convenience" starts causing me trouble when the mid-level slugs who have no understanding or care for integrity, etc.... get a hold of this and just see it as a directroute to Nirvana....

    that being said - Grant, these are probably the purest/most straightforward versions of what the generated code would look like, so perhaps set one up, turn profiler on, and let it rip and see what comes out....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Grant Fritchey

    SSC Guru

    Points: 396614

    Funny you should say that. I'm stepping through writing up samples in C# using nHibernate as I type this (well, right after I type this and right before). So far, I can see why the developers like it. Loading data into objects that I've already created is incredibly easy. Combine that with generated code and this starts to make a lot of sense... as long as you don't look at the database.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Andy Warren

    SSC Guru

    Points: 119694

    Resist the dark side Grant!

    We all should want data access to be easy, or at least straight forward. Somehow we gotta get knowledgeabout people that aren't zealots from both sides in one room and see what can be done. If nothing else MS needs to issue some really good guidance on when to use what.

  • Jack Corbett

    SSC Guru

    Points: 184381

    Andy Warren (4/24/2008)


    Resist the dark side Grant!

    We all should want data access to be easy, or at least straight forward. Somehow we gotta get knowledgeabout people that aren't zealots from both sides in one room and see what can be done. If nothing else MS needs to issue some really good guidance on when to use what.

    It's only the dark side when uncontrolled.

    It's easy enough to shut down Linq to SQL, just disable Named Pipes.

    IMHO, the nice thing about Linq to SQL isn't removing the drudgery of creating data acces code, it's removing the drudgery of creating the .NET classes with all there properties, etc....

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720425

    Is LINQ-> SQL only named pipes? That would be interesting.

    Not sure it's the dark side, and I think we need more independent information. If there's one thing I've learned from MS is that they don't really exhaustively test things and consider lots of use cases. They go with a few and build things that they think will sell.

  • Jack Corbett

    SSC Guru

    Points: 184381

    Steve Jones - Editor (4/24/2008)


    Is LINQ-> SQL only named pipes? That would be interesting.

    Not sure it's the dark side, and I think we need more independent information. If there's one thing I've learned from MS is that they don't really exhaustively test things and consider lots of use cases. They go with a few and build things that they think will sell.

    Yes, Linq To SQL requires Named Pipes in order to work. I read about, tested it, and blogged about it; and

    just to try to drive some traffic to my blog, here is the blog post about it which contains a link to an MSDN article.

    Also, great discussion so this was definitely a good choice of editorial material Steve. We are 3 days into the discussion and new stuff keeps cropping up.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Samuel Clough

    Old Hand

    Points: 319

    Considering it's only named pipes and only SQL Server, it now really makes it look like this was designed for quick and dirty apps (i.e. my RAD comments) and that MS either assumed large apps wouldn't use it, or they just haven't finished fully developing it for more "enterprise" apps.

  • Grant Fritchey

    SSC Guru

    Points: 396614

    {click-whoosh}{click-whossh} I am the master now, Andy {click-whoosh}

    Anyway, I finished running the simple tests. Here's a question given the following table & query, what might happen?

    CREATE TABLE [dbo].[users](

    [LogonID] [nvarchar](20) NOT NULL DEFAULT ('0'),

    [Name] [nvarchar](40) NULL DEFAULT (NULL),

    [Password] [nvarchar](20) NULL DEFAULT (NULL),

    [EmailAddress] [nvarchar](40) NULL DEFAULT (NULL),

    [LastLogon] [datetime] NULL DEFAULT (NULL),

    PRIMARY KEY CLUSTERED

    (

    [LogonID] ASC

    ) ON [PRIMARY]

    ) ON [PRIMARY]

    And the query:

    exec sp_executesql N'SELECT user0_.LogonId as LogonId0_0_, user0_.Name as Name0_0_, user0_.Password as Password0_0_, user0_.EmailAddress as EmailAdd4_0_0_, user0_.LastLogon as LastLogon0_0_ FROM users user0_ WHERE user0_.LogonId=@p0',N'@p0 nvarchar(8)',@p0=N'joe_cool'

    Yeah, yeah, it violates Jeff's posting standards, but this is what I'm forced to look through when using this tool.

    So, what's the potential problem here? Anyone?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • steve dassin

    SSCrazy

    Points: 2396

    There seems to be a lot of tap dancing around LINQ in the sql server community. And the most salient points about it has little to do with performance questions. People are describing LINQ as a new technology. It's much more than just a new technology, it represents a new point of view and direction for application development. And as a point of view it has consequences. Unfortunately no one seems to want to address what they are (just why is interesting but deserves its own post:) ).

    Tom Garths comment goes to the heart of the issue:

    >Well the point of that session, was that ALL developers should be moving to LINQ. I really have a >hard time whenever I hear that from Microsoft, because... what if they really mean it?"

    Well there's every indication that they do mean it! A review of MS white papers and MS research over the last eight years shows MS has moved to the object model which LINQ is a reflection of. And just as importantly they no longer embrace sql nor the relational model. MS wants something simpler and far less cumbersome for development. This is what Gates was saying in Inforworld:

    Gates talks up declarative modeling language effort

    http://www.infoworld.com/article/08/02/11/gates-declarative_1.html

    "You should be able to do things on a declarative basis," Gates continued. But this has not caught on partially because of weak data models -- first Codasyl and then relational."

    Ballmer is echoing the same idea:

    Steve Ballmer: Microsoft 2008 MVP Summit

    http://www.microsoft.com/Presspass/exec/steve/2008/04-17MVP.mspx

    "The way in which we program is downright primitive. We program almost the same way today that we did 15 years ago.

    How do we ever bring software development up to semantic levels, so we're not banging away, banging away, instruction by instruction, by instruction."

    Does anyone have evidence that anyone in MS upper management is a champion of sql, the language or the relational model? Anyone in MS research? The last sql champion MS had was the late Jim Gray. And he renounced sql as no longer adequate to handle current application development.

    'A Call to Arms' ,April 2005 by JIM GRAY, MICROSOFT and MARK COMPTON, CONSULTANT

    http://www.acmqueue.org/modules.php?name=Content&pa=showpage&pid=293

    He a former sql advocate now calling for others to join him in a new direction. (MS honored his courage in renouncing the relational model with their 'heros' theme. Only heros have the courage to move from something established to something new).

    It would seem that the sql community is a state of denial to think MS wants to advance sql. The truth is they view it as a burden (that they are stuck with for the foreseeable future. The fact they sprinkle it with a few bones each release is another story). Perhaps this will become clearer with each iteration of LINQ. MS as a company needs to generate excitement. How many would associate excitement to sql? Too few. Not all new ideas can be dismissed as marketing hype, some are inconvenient truths.

    Did you ever wonder about the views expressed some forty years ago when Oracle introduced the first commercial version of sql? Was the first thing everyone wondered about performance?

    steve dassin

    http://www.beyondsql.blogspot.com/[/url]

Viewing 12 posts - 46 through 57 (of 57 total)

You must be logged in to reply to this topic. Login to reply