ASP. NET MVC Tip #23 – Use POCO LINQ to SQL Entities. Tuesday, July 22, 2008. In this tip, I demonstrate how you can create LINQ to SQL entities that do not contain any special attributes. I show you how you can use an external XML file to map LINQ to SQL entities to database objects.
I’ve talked to several people recently who are deeply bothered by the fact that the LINQ to SQL classes generated by the Visual Studio Object Relational Designer contain attributes. They want to take advantage of the Object Relational Designer to generate their entity classes. However, they don’t like the fact that the generated entities are decorated with a bunch of attributes.
How do you insert a DateTime null. Parameter DbType='DateTime' Name='DATE' /> <asp. Passing Null Value of DateTime of Asp.Net control in C#. One thought on “ ASP.NET – Handling Null Return Values from the Database using IsDBNull ” Pingback: ASP.NET - Handling Null Return Values from the.
For example, if you use the Object Relational Designer to generate a LINQ to SQL class that corresponds to the Movies database table, then you get the class in Listing 1. This class is generated in the Movies. Designer. cs file. Listing 1 – Movie Class (abbreviated). Notice that the class in Listing 1 includes both [Table] and [Column] attributes.
![asp.net dbtype null asp.net dbtype null](http://moule.in.ua/store/8/f/video-1425872728-35.jpg)
![asp.net dbtype null asp.net dbtype null](http://www.c-sharpcorner.com/UploadFile/dhananjaycoder/controller-in-Asp-Net-mvc-framework/Images/7.gif)
LINQ to SQL uses these attributes to map classes and properties to database tables and database table columns. Some people are disturbed by these attributes.
![asp.net dbtype null asp.net dbtype null](http://www.thescripts.net/wp-content/uploads/2014/12/8679143_Yuclone_Kit-191x120.png)
They don’t want to mix their persistence logic with their domain entities. They want to use POCO objects (Plain Old CLR Objects) for their entities. Fortunately, LINQ to SQL supports two methods of mapping classes to database objects. Instead of using the default AttributeMappingSource, you can use the XmlMappingSource. When you use the XmlMappingSource, you use an external XML file to map classes to database objects.
You can create the XML file by hand or you can use the SqlMetal. exe command line tool. You run SqlMetal. exe from the Visual Studio Command Prompt (Start, All Programs, Microsoft Visual Studio 2008, Visual Studio Tools, Visual Studio 2008 Command Prompt). Here’s how you use SqlMetal. exe to create an XML mapping file from a RANU SQL Express database named MoviesDB. mdf:.
1. Navigate to the folder containing the MoviesDB.
Convert.net type to DbType (SqlDbType). ASP.NET.NET SQL Server 2005. /// <remarks>This function enable to set null DbNullValue to parameter with dbType. Home of the Microsoft ASP.NET development community. Download Visual Studio, post to the forums, read ASP.NET blogs and learn about ASP.NET. System.Data.OleDb OleDbType Enumeration. OleDbType Enumeration. string, binary, or date data, and also the special values Empty and Null (DBTYPE_VARIANT).
mdf database. 2. Execute the following command:. SqlMetal /dbml:movies. dbml MoviesDB.
mdf. 3. Execute the following command:. SqlMetal /code:movies. cs /map:movies. map movies.
dbml. After you execute these commands, you will end up with three files:. · movies. dbml – The movies database markup file. · movies. cs – The movie classes that correspond to the database objects. · movies.
map – The XML map file that maps the classes to the database objects. After you generate these files, you can add the movies. cs and movies. map file to your ASP. NET MVC application’s Models folder.
The C# Movie class file from the movies. cs is contained in Listing 2. The file in Listing 2 is almost exactly the same as the file in Listing 1 except for the fact that the file does not contain any special attributes. The Movie class in Listing 2 is a POCO object. Listing 2 – Movie Class (abbreviated). The file in Listing 3 contains the XML mapping file generated by the SqlMetal. exe tool.
You could create this file by hand. Listing 3 – movies. map. After you create an external XML mapping file, you must pass the mapping file to a DataContext object when you initialize the DataContext. For example, the controller in Listing 4 uses the movies.
map file within its Index() method. Listing 4 – HomeController. cs. The Index() starts by retrieving a connection string from the Web configuration file.
Next, the XML mapping file is loaded from the Models folder. The connection string and mapping source are passed to the DataContext constructor when the DataContext is created. Finally, a list of movies is retrieved from the database and sent to the view.
The point of this tip was to demonstrate that you can use an external XML file instead of attributes within your LINQ to SQL classes to map your classes to your database objects. Some people don’t want to dirty their classes with database persistence logic.
LINQ to SQL is flexible enough to make these people happy. And of course all those "this. " are extra to make the code look simpler. If both the cs and xml files are embedded into the assembly than I really don't see any benefit of using xml instead of attributes. In general I prefer using attributes.
If you could show a way of breaking the SQL file into multiple files that would definitely make for a good tip ;). Anyway thanks. and keep them coming. Great tip, didn't know that! That moves Linq-to-Sql a little more towards Linq-to-Entities. And thereby makes Linq-to-Sql an even more attractive choice. In order to have a complete POCO Model, you still have to get rid of the EntityRef and EntitySet collections for associations, which is a bad thing because you lose all the benefits of lazy loading. Also, I would not implement the data context initialization in the controller itself, it makes the controller method very hard to test.
Specially for all the references to configuration that you have in there. Anyway, great work.
Thanks. Here's an interesting explanation of why/how the INotifyPropertyChanging, INotifyPropertyChanged interfaces are used.
If your table changed you still have to re-generate the code of entities and compile your project? Is there work around not need to do that just replace the mapping file. @Michael - You can change the mapping file directly without a recompile. You also can change the entity class files by hand (changing the class files would, of course, require a recompile). There is no reason that you can't completely ignore SqlMetal.
exe and do everything by hand. Linq still doesn't appear to be loosely coupled even with the map file.
With sqlmetal, the map and partial entity class is created but the class still has references to Data. Linq which means it's tightly coupled to Linq. Is there a way to use the map configuration without needing to reference Linq in the model.