Working Without the O/R Designer C# Help

Although the new O/R Designer in Visual Studio 2008 makes the creation of everything you need for LINQ to SQL quite easy, it is important to note that the underlying framework upon which this all rests allows you to do everything from the ground up yourself, This provides the most control over the situation and what is actually happening.

Creating Your Own Custom Object

To accomplish the same task as was accomplished earlier with the Customer table, you will need to expose the Customer table yourself via a class. The first step is to create a new class in your project called Customer. cs.

The code for this class is presented here:

Here, the Customer. cs file defines the Customer object that you want to use with LlNQ to SQL, The class has the Table attribute assigned to it in order to signify the table class, The Table class attribute includes a property called Name, which defines the name of the table to use within the database that is referenced with the connection string. Using the Table attribute also means that you need to make a reference to the System, Data Linq Mapping namespace in your code, In addition to the Table attribute, each of the defined properties in the class makes use of the column attribute, As stated earlier, columns from the SQLServer database will map to properties in your code.

Querying with Your Custom Object And LINQ

With only the Customer class in place, you are then able to query the Northwind database for the Customers table, The code to accomplish this task is illustrated in the following example:

using System;
using System.Data.Linq;

namespace ConsoleApplicationl
{
class Program
{
static void Main()
{
DataContext dc = new DataContextData Source=.\SQLEXPRESS;
AttachDbFilename= IDataDirectcryl \NORTHWND.MDF;
Integrated Security=True;User Instance=True’);
dc.Log = Console.Out; II Used for outputting the SQL used
Table<Customer> myCustomers = dc.GetTable<Customer>();
foreach (Customer item in myCustomers)
{
Console. WriteLine (‘{O) I {1)’,
item.CompanyName; item. Country) ;
Console.ReadLine() ;

In this case, the default DataContext object is used and the connection string to the Northwind SQL Server Express database is passed in as a parameter, A Table class of type Customer is then populated using the GetTable<TEntity> () method, For this example, the GetTable<TEnti ty> () operation uses your custom-defined Customer class:

dc.GetTable<Customer>();

What happens is that LINQ to SQL will use the DataContext object to make the query to the SQLServer database on your behalf and will get the returned rows as strongly typed Customer objects, This will allow you to then iterate through each of the Customer objects in the Table object’s collection and get at the information that you need, as is done with the Console. Wri teLine () statements here:

foreach (Customer item in myCustomers)
{
Console.WriteLine(‘{O) I {1)’,
item.CompanyName, item.Country);

Running this code produces the following results in your console application:

Alfreds Futterkiste I Germany
Ana Trujillo Emparedados y helados I Mexico
Antonio Moreno Taqueria I Mexico

Around the Horn I UK
Berglunds snabbkop I Sweden
II OUtput removed for clarity
Wartian Herkku I Finland
Wellington Importadora I Brazil
White Clover Markets I USA
Wilman Kala I Finland
wolski Zajazd I Poland

Limiting the Columns Called with the Query

You will notice that the query retrieved every single column that was specified in your Customer class file. If you remove the columns that you are not going to need, you can then have a new Customer class file as shown here:

using System.Data.Linq.Mapping;
namespace ConsoleApplicationl
(
[Table(Name = ‘Customers’)]
public class Customer
(
..
[Column(IsPrimaryKey = true)]
public string CustomerID { get; set; }
[Column]
public string CompanyName { get; set;
[Column]
public string Country { get; set; }

In this case, I removed all the columns that are not utilized by tile application. Now if you run the console application and look at the SQL query that is produced, you will see the following results:

SELECT [to]. [CustomerID], [to]. [CompanyName], [to]. [Country]
FROM[Customers] AS [to]

You can see that only the three columns that are defined within the Customer class are utilized in the query to the Customers table.

The property CustomerID is interesting in that you are able to signify that this column is a primary key for the table through the use of the Is PrimaryKey setting in the coloumn attribute, This setting takes a Boolean value and in this case, it is set to true.

Working with Column Names

The other important point of the columns is that the name of the property that you define in the Customer class needs to be the same name as what is used in the database. For instance, if you change the name of the CustomerID property to MyCustomerID, you will get the following exception when you try to run your console application:

System.Data.SqlClient.SqlException was unhandled
Message=’Invalid column name ‘MyCu5tomerID’.’
Source=’.Net Sql __ient Data Provider’
ErrorCode=-2146232060
Class=16
LineNumber=l
Number=207
Procedure=’ ,
Server=’ \\ \\. \\pipe\ \F5E22E37-1AF9-44 \ tsql \ query’

To get around this, you need to define the name of the column in the custom Customer class that you have created, You can do this by using the Column attribute as illustrated here:

[Column(IsPrimaryKey = true. Name = ‘CustomerID’)J
public string MyCustomerID { get; set;

Like the Table attribute, the column attribute includes a Name property that allows you to specify the name of the column as it appears in the Customers table.

Doing this will generate a query as shown here:

SELECT [to]. [CustomerID] AS [MyCustomerID]. [to]. [CompanyName]. [tOl. [Country] FROM [Customers] AS [to]

This also means that you will need to now reference the column using the new name of MyCustomerID (for example, item. MyCustomerID).

Creating Your Own DataContext Object

Now it is probably not the best approach to use the plain-vanilla DataContext object, but instead, you will find that you have more control by creating your own datacontext class, To accomplish this task, create a new class called MyNorthwindDataContext, cs and have the class inherit from DataContext, Your class in its simplest form is illustrated here:

using System. Data.Linq;
namesp~ce ConsoleApplicationl
{
public class MyNorthwindDataContext
(
public Table<Customer> CUstomers;
Da’taContext
public MyNorthwindDataContext()
base(@’Data Source=.\SQLEXPRESS;
AttachDbFilename=IDataD~rectoryl\NORTHWND.MDF;
Integrated Security=True;User Instance=True’)

Here, the class MyNorthwindDataContext inherits from DataContext and provides an instance of the Table<Customer> object from the Customer class that you created earlier The constructor is the other requirement of this class, This constructor uses a base to initialize a new instance of the object referencing a file (in this case a connection to a SQL database file).

Using your own DataContext object now allows you to change the code in your application to the following:

using System;
using System.Data.Linq;
namespace ConsoleApplicationl
{
class Program
(
static void Main()
{
MyNorthwiDdDataCoutext de • new MyNorthwiDdDataCcmtext() ,
Table<Customerr> my customer dc.Cutomer,
foreach (Customer item in myCustomers)
{
Console. WriteLine (.{O) I {I}·,
item.CompanyName, item.Country);
Console.ReadLine();

By creating an instance of the MyNorthwindDataContext object, you are now allowing the class to manage the .connection to the database. You will also notice that now you have direct access to the Customer class through the dc. Customers statement.

Note that the examples provided in this chapter are considered bare-bones examples in that they don’t include all the error handling and logging that would generally go into building your applications.

Posted on October 31, 2015 in LINQ to SQL

Share the Story

Back to Top