Linq + SQLMetal = Cool...
-
So here I am totally bogged down in reluctance to write more data access code and stored procedures. I hate it. It's tedious and bothersome. So... I google C# code generation SQL server and out pops this Linq result that talks about SQLMetal. Not having a clue at all what I'm looking at I bite. I open up the VS 2008 command prompt and type in:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC>sqlmetal /server:{server} /database:{db} /namespace:{new namespace} /code:{ClassName}.cs /language:csharp
I hit enter and think this won't work. What do you know... Out pops a huge monolithic class that has all of the stuff required to Create/Insert/Update/Delete every table in the DB all VIA Linq. {Scratches Head} How the heck do I use this? Turns out a quick Google search and *BOOM* it's out of the park. No inline SQL, no stored procedures and no boredom. It's all done. I just invoke the class, instance my table populate the fields and then it's time to "Smile and Waive" as I call Submit(). I'm now a Linq convert. I will never go back. :cool:
The only problem I have with LINQ is it came out in ASP.NET 3.5 and my Windows customers are still on ASP.NET 2.0. :sigh: Some day I will get to use it but unfortunately not right now.
Simply Elegant Designs JimmyRopes Designs
Think inside the box! ProActive Secure Systems
I'm on-line therefore I am. JimmyRopes -
So here I am totally bogged down in reluctance to write more data access code and stored procedures. I hate it. It's tedious and bothersome. So... I google C# code generation SQL server and out pops this Linq result that talks about SQLMetal. Not having a clue at all what I'm looking at I bite. I open up the VS 2008 command prompt and type in:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC>sqlmetal /server:{server} /database:{db} /namespace:{new namespace} /code:{ClassName}.cs /language:csharp
I hit enter and think this won't work. What do you know... Out pops a huge monolithic class that has all of the stuff required to Create/Insert/Update/Delete every table in the DB all VIA Linq. {Scratches Head} How the heck do I use this? Turns out a quick Google search and *BOOM* it's out of the park. No inline SQL, no stored procedures and no boredom. It's all done. I just invoke the class, instance my table populate the fields and then it's time to "Smile and Waive" as I call Submit(). I'm now a Linq convert. I will never go back. :cool:
You do not have to go to SQLMetal, just add an LINQ to SQL Class to your project (you can have multiples too) and it will open up the designer. Switch to Server Explorer (View/Server Explorer), connect to the database and drag the tables you want onto the design surface. You can add relationships as you will and rename the objects it creates if you have a need. You can also drag a stored procedure over the the design seruver and it will create a method in the object class to call it with its parameters. Slick stuff! The classes it generates are all partial class types so you can add a new file to your project and extended those object classes such as display translations, business logic, etc. Really handy! Of course, this is just LINQ to SQL, LINQ itself works great for other objects and XML. It is a great general data query langauge that spans all data that are objects regardless of where you get the data. One word of caution though, in the beginning you need to watch carefully what is being sent to the server and when. With its lazy loading of objects, a simple query and walking through the data can generate hundreds, thousands or even tens of thousands of queries without you noticing. You have to take control on what is loaded and when. This is a step you already have to take care of manually in the old methods of data access, and for some new to LINQ to SQL, they think all that is automatic as they do not see it. We still have that responsiblity even though we do not notice it at first.
Rocky <>< Recent Blog Post: ViewState and SEO – Move it! Thinking about Silverlight? www.SilverlightCity.com
-
I didn't keep all the generated code. I went in and cut out all the stuff I won't use. I paired about 60% of the mass out of the class. Now it's tight and lean and I've seen all the code too. Just because it hands you a blimp doesn't mean you have to keep it that way. As the documentation suggests it's meant to be a base that you then customize or extend to meet your own needs. Regardless... !SOLD!:rose: {Edit}:thumbsup: Honestly, you should check it out and look at the resulting code. It's not bad. It's strongly typed, field length is enforced and it's structured in a logical way that's usable, easy to modify/update and it will seriously save you a lot of time doing the heavy lifting for you. What it gives you can be changed in any way you see fit. I inherit it all into another class where I do some stuff to the values before they are passed and it's so easy to do. {/Edit}:thumbsup:
A lot of people throw the Repository [^] pattern on top of the LinqToSQL bits.
Todd Smith
-
You do not have to go to SQLMetal, just add an LINQ to SQL Class to your project (you can have multiples too) and it will open up the designer. Switch to Server Explorer (View/Server Explorer), connect to the database and drag the tables you want onto the design surface. You can add relationships as you will and rename the objects it creates if you have a need. You can also drag a stored procedure over the the design seruver and it will create a method in the object class to call it with its parameters. Slick stuff! The classes it generates are all partial class types so you can add a new file to your project and extended those object classes such as display translations, business logic, etc. Really handy! Of course, this is just LINQ to SQL, LINQ itself works great for other objects and XML. It is a great general data query langauge that spans all data that are objects regardless of where you get the data. One word of caution though, in the beginning you need to watch carefully what is being sent to the server and when. With its lazy loading of objects, a simple query and walking through the data can generate hundreds, thousands or even tens of thousands of queries without you noticing. You have to take control on what is loaded and when. This is a step you already have to take care of manually in the old methods of data access, and for some new to LINQ to SQL, they think all that is automatic as they do not see it. We still have that responsiblity even though we do not notice it at first.
Rocky <>< Recent Blog Post: ViewState and SEO – Move it! Thinking about Silverlight? www.SilverlightCity.com
So are you saying load it, use it and dispose it? That what you mean?
-
You do not have to go to SQLMetal, just add an LINQ to SQL Class to your project (you can have multiples too) and it will open up the designer. Switch to Server Explorer (View/Server Explorer), connect to the database and drag the tables you want onto the design surface. You can add relationships as you will and rename the objects it creates if you have a need. You can also drag a stored procedure over the the design seruver and it will create a method in the object class to call it with its parameters. Slick stuff! The classes it generates are all partial class types so you can add a new file to your project and extended those object classes such as display translations, business logic, etc. Really handy! Of course, this is just LINQ to SQL, LINQ itself works great for other objects and XML. It is a great general data query langauge that spans all data that are objects regardless of where you get the data. One word of caution though, in the beginning you need to watch carefully what is being sent to the server and when. With its lazy loading of objects, a simple query and walking through the data can generate hundreds, thousands or even tens of thousands of queries without you noticing. You have to take control on what is loaded and when. This is a step you already have to take care of manually in the old methods of data access, and for some new to LINQ to SQL, they think all that is automatic as they do not see it. We still have that responsiblity even though we do not notice it at first.
Rocky <>< Recent Blog Post: ViewState and SEO – Move it! Thinking about Silverlight? www.SilverlightCity.com
Totally AWESOME tips. I had no idea.
-
So are you saying load it, use it and dispose it? That what you mean?
Here was an issue I ran into with LINQ 2 SQL back with the Orca betas (which applies today): http://reflectedthought.com/TheCoder/archive/0001/01/01/Dark_Side_of_LINQ_2_SQL.aspx[^] And a little whining about another thing I wished they would have improved in LINQ 2 SQL, disconnected data without having to reread the data before updating it: http://reflectedthought.com/TheCoder/archive/0001/01/01/LINQ_Disconnected_Attach_no_Change_tracking_fix.aspx[^] Other than that, I like it. I have not looked much into LINQ 2 EF yet, but I am sure I will in the future. Right now though, I still use LINQ 2 SQL for my projects.
Rocky <>< Recent Blog Post: ViewState and SEO – Move it! Thinking about Silverlight? www.SilverlightCity.com
-
You do not have to go to SQLMetal, just add an LINQ to SQL Class to your project (you can have multiples too) and it will open up the designer. Switch to Server Explorer (View/Server Explorer), connect to the database and drag the tables you want onto the design surface. You can add relationships as you will and rename the objects it creates if you have a need. You can also drag a stored procedure over the the design seruver and it will create a method in the object class to call it with its parameters. Slick stuff! The classes it generates are all partial class types so you can add a new file to your project and extended those object classes such as display translations, business logic, etc. Really handy! Of course, this is just LINQ to SQL, LINQ itself works great for other objects and XML. It is a great general data query langauge that spans all data that are objects regardless of where you get the data. One word of caution though, in the beginning you need to watch carefully what is being sent to the server and when. With its lazy loading of objects, a simple query and walking through the data can generate hundreds, thousands or even tens of thousands of queries without you noticing. You have to take control on what is loaded and when. This is a step you already have to take care of manually in the old methods of data access, and for some new to LINQ to SQL, they think all that is automatic as they do not see it. We still have that responsiblity even though we do not notice it at first.
Rocky <>< Recent Blog Post: ViewState and SEO – Move it! Thinking about Silverlight? www.SilverlightCity.com
Rocky Moore wrote:
just add an LINQ to SQL Class to your project
Where does one get a LINQ to SQL Class, in order to add it to a project. I only have VS2008 Standard. Is it only in the higher versions?
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
So here I am totally bogged down in reluctance to write more data access code and stored procedures. I hate it. It's tedious and bothersome. So... I google C# code generation SQL server and out pops this Linq result that talks about SQLMetal. Not having a clue at all what I'm looking at I bite. I open up the VS 2008 command prompt and type in:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC>sqlmetal /server:{server} /database:{db} /namespace:{new namespace} /code:{ClassName}.cs /language:csharp
I hit enter and think this won't work. What do you know... Out pops a huge monolithic class that has all of the stuff required to Create/Insert/Update/Delete every table in the DB all VIA Linq. {Scratches Head} How the heck do I use this? Turns out a quick Google search and *BOOM* it's out of the park. No inline SQL, no stored procedures and no boredom. It's all done. I just invoke the class, instance my table populate the fields and then it's time to "Smile and Waive" as I call Submit(). I'm now a Linq convert. I will never go back. :cool:
You are absolutely correct. This is waaay cool. Why do they hide this stuff?
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
Rocky Moore wrote:
just add an LINQ to SQL Class to your project
Where does one get a LINQ to SQL Class, in order to add it to a project. I only have VS2008 Standard. Is it only in the higher versions?
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
When you select "Add New Item" within your project, it should be listed as "LINQ to SQL Classes".
Rocky <>< Recent Blog Post: ViewState and SEO – Move it! Thinking about Silverlight? www.SilverlightCity.com
-
You are absolutely correct. This is waaay cool. Why do they hide this stuff?
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
:laugh:
Rocky <>< Recent Blog Post: ViewState and SEO – Move it! Thinking about Silverlight? www.SilverlightCity.com
-
So here I am totally bogged down in reluctance to write more data access code and stored procedures. I hate it. It's tedious and bothersome. So... I google C# code generation SQL server and out pops this Linq result that talks about SQLMetal. Not having a clue at all what I'm looking at I bite. I open up the VS 2008 command prompt and type in:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC>sqlmetal /server:{server} /database:{db} /namespace:{new namespace} /code:{ClassName}.cs /language:csharp
I hit enter and think this won't work. What do you know... Out pops a huge monolithic class that has all of the stuff required to Create/Insert/Update/Delete every table in the DB all VIA Linq. {Scratches Head} How the heck do I use this? Turns out a quick Google search and *BOOM* it's out of the park. No inline SQL, no stored procedures and no boredom. It's all done. I just invoke the class, instance my table populate the fields and then it's time to "Smile and Waive" as I call Submit(). I'm now a Linq convert. I will never go back. :cool:
Now I thought every developer who builds DB centric apps has built one of these, I am always astounded by the comercial code generators that actually survive. The one I use was first built in the mid 90's has been rewritten unmpteen times and does precicely what I want it to do, which is about 85-90% of the SQL code, including the stored procs. How do you guys survive not knowing whats under the hood?
Never underestimate the power of human stupidity RAH
-
When you select "Add New Item" within your project, it should be listed as "LINQ to SQL Classes".
Rocky <>< Recent Blog Post: ViewState and SEO – Move it! Thinking about Silverlight? www.SilverlightCity.com
Nah. Not there. Must be exclusive to you rich guys.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
When you select "Add New Item" within your project, it should be listed as "LINQ to SQL Classes".
Rocky <>< Recent Blog Post: ViewState and SEO – Move it! Thinking about Silverlight? www.SilverlightCity.com
Please ignore my previous post. I had looked there before posting it but had forgotten that the Solution I was working on was targeting .NET 2.0. Doh! When I tried it with a .NET 3.5 Solution, it worked a treat. Excellent tip.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
So here I am totally bogged down in reluctance to write more data access code and stored procedures. I hate it. It's tedious and bothersome. So... I google C# code generation SQL server and out pops this Linq result that talks about SQLMetal. Not having a clue at all what I'm looking at I bite. I open up the VS 2008 command prompt and type in:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC>sqlmetal /server:{server} /database:{db} /namespace:{new namespace} /code:{ClassName}.cs /language:csharp
I hit enter and think this won't work. What do you know... Out pops a huge monolithic class that has all of the stuff required to Create/Insert/Update/Delete every table in the DB all VIA Linq. {Scratches Head} How the heck do I use this? Turns out a quick Google search and *BOOM* it's out of the park. No inline SQL, no stored procedures and no boredom. It's all done. I just invoke the class, instance my table populate the fields and then it's time to "Smile and Waive" as I call Submit(). I'm now a Linq convert. I will never go back. :cool:
And then there's this shameless plug for my latest article.[^] :~ Marc
Available for consulting and full time employment. Contact me. Interacx
-
So here I am totally bogged down in reluctance to write more data access code and stored procedures. I hate it. It's tedious and bothersome. So... I google C# code generation SQL server and out pops this Linq result that talks about SQLMetal. Not having a clue at all what I'm looking at I bite. I open up the VS 2008 command prompt and type in:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC>sqlmetal /server:{server} /database:{db} /namespace:{new namespace} /code:{ClassName}.cs /language:csharp
I hit enter and think this won't work. What do you know... Out pops a huge monolithic class that has all of the stuff required to Create/Insert/Update/Delete every table in the DB all VIA Linq. {Scratches Head} How the heck do I use this? Turns out a quick Google search and *BOOM* it's out of the park. No inline SQL, no stored procedures and no boredom. It's all done. I just invoke the class, instance my table populate the fields and then it's time to "Smile and Waive" as I call Submit(). I'm now a Linq convert. I will never go back. :cool:
I'm working on a linq project now. Trust me, the time you save by having MS do your data access code seem very small compared to the time you spend screaming at your IDE when something doesn't work and you have no idea as to why or what is wrong. I hate programming against a black box.
-
Please ignore my previous post. I had looked there before posting it but had forgotten that the Solution I was working on was targeting .NET 2.0. Doh! When I tried it with a .NET 3.5 Solution, it worked a treat. Excellent tip.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
Another "Aaaaarrgggh! I spoke too soon" then? :doh:
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
-
Another "Aaaaarrgggh! I spoke too soon" then? :doh:
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
Oh yes! And it probably won't be the last. The filter between my brain and mouth/fingers got broken a long time ago.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
So here I am totally bogged down in reluctance to write more data access code and stored procedures. I hate it. It's tedious and bothersome. So... I google C# code generation SQL server and out pops this Linq result that talks about SQLMetal. Not having a clue at all what I'm looking at I bite. I open up the VS 2008 command prompt and type in:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC>sqlmetal /server:{server} /database:{db} /namespace:{new namespace} /code:{ClassName}.cs /language:csharp
I hit enter and think this won't work. What do you know... Out pops a huge monolithic class that has all of the stuff required to Create/Insert/Update/Delete every table in the DB all VIA Linq. {Scratches Head} How the heck do I use this? Turns out a quick Google search and *BOOM* it's out of the park. No inline SQL, no stored procedures and no boredom. It's all done. I just invoke the class, instance my table populate the fields and then it's time to "Smile and Waive" as I call Submit(). I'm now a Linq convert. I will never go back. :cool:
Allow me to retort: Every application that was built with an ORM code generator off a database (and no huge amount of hand tweaking) has a crappy UI that should be taken out and shot, not inflicted on an unsuspecting public. Code Generation without heavy tweaking = crappy software, it's axiomatic. The correct and necessary way to store data in a database and the tasks that the end users need to accomplish are 180 degrees apart and the business objects in a well crafted easy to use application bear little resemblance to the database schema. Our whole job as developers is not just to develop software, it's to abstract away the inner workings of computers and software and present the end user with something that looks and works how they think. If you start below the UI with business objects that are a perfect reflection of the database then you are led down a path that ends with crazy forms with rank upon rank of what are basically database fields exposed directly in the UI and a bunch of pointless extra work and confusion for the end user. People don't need developers for that, they can just hire a sql admin to make a database and users can just enter in the data in the tables directly. The point of being a developer is to be a crafstman who uses a combination of skill, and artistic ability to make the connection between the reality of the end users tasks they need to accomplish throughout their day and the separate reality of how data should be stored in a database. If you say you're using code generation for the heavy lifting then going back and heavily re-tweaking it for anything of consequence then that's fine but if you're generating the code then going on to the interface you're building a foundation on quicksand. And if you really are planning on hand tweaking LINQ has to be about the worst abomination to inflict upon yourself to no useful purpose I can imagine. Dense, unreadable, unmaintainable.... X|
"It's so simple to be wise. Just think of something stupid to say and then don't say it." -Sam Levenson
-
So here I am totally bogged down in reluctance to write more data access code and stored procedures. I hate it. It's tedious and bothersome. So... I google C# code generation SQL server and out pops this Linq result that talks about SQLMetal. Not having a clue at all what I'm looking at I bite. I open up the VS 2008 command prompt and type in:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC>sqlmetal /server:{server} /database:{db} /namespace:{new namespace} /code:{ClassName}.cs /language:csharp
I hit enter and think this won't work. What do you know... Out pops a huge monolithic class that has all of the stuff required to Create/Insert/Update/Delete every table in the DB all VIA Linq. {Scratches Head} How the heck do I use this? Turns out a quick Google search and *BOOM* it's out of the park. No inline SQL, no stored procedures and no boredom. It's all done. I just invoke the class, instance my table populate the fields and then it's time to "Smile and Waive" as I call Submit(). I'm now a Linq convert. I will never go back. :cool:
Just bear in mind that Linq 2 SQL is not being heavily improved or maintained by Microsoft. Their focus is on Entity Framework (many similarities, but more capabilities). A major gripe of mine with Linq 2 SQL is that you cannot update a single table from the DB (using sqlmetal or VS). You have to regenerate the entire class (i.e. all tables).
"For fifty bucks I'd put my face in their soup and blow." - George Costanza
CP article: SmartPager - a Flickr-style pager control with go-to-page popup layer.
-
Allow me to retort: Every application that was built with an ORM code generator off a database (and no huge amount of hand tweaking) has a crappy UI that should be taken out and shot, not inflicted on an unsuspecting public. Code Generation without heavy tweaking = crappy software, it's axiomatic. The correct and necessary way to store data in a database and the tasks that the end users need to accomplish are 180 degrees apart and the business objects in a well crafted easy to use application bear little resemblance to the database schema. Our whole job as developers is not just to develop software, it's to abstract away the inner workings of computers and software and present the end user with something that looks and works how they think. If you start below the UI with business objects that are a perfect reflection of the database then you are led down a path that ends with crazy forms with rank upon rank of what are basically database fields exposed directly in the UI and a bunch of pointless extra work and confusion for the end user. People don't need developers for that, they can just hire a sql admin to make a database and users can just enter in the data in the tables directly. The point of being a developer is to be a crafstman who uses a combination of skill, and artistic ability to make the connection between the reality of the end users tasks they need to accomplish throughout their day and the separate reality of how data should be stored in a database. If you say you're using code generation for the heavy lifting then going back and heavily re-tweaking it for anything of consequence then that's fine but if you're generating the code then going on to the interface you're building a foundation on quicksand. And if you really are planning on hand tweaking LINQ has to be about the worst abomination to inflict upon yourself to no useful purpose I can imagine. Dense, unreadable, unmaintainable.... X|
"It's so simple to be wise. Just think of something stupid to say and then don't say it." -Sam Levenson
I don't think you've seen the generated code at all. Look at it and then come back and post. Until then my comments stand.