Previously I have been talking about the limitations of Linq to SQL for your data layer. You should read it here. This post is part of the Linq to SQL series. Linq to SQL is a great technology I found that adding the data layer on the web application will just work until you are deploying the application to another computer with another database.
Mixing 2.0 Web app with 3.5 LINQ to SQL
SavedDataContext context = new SavedDataContext(_ConnectionString);
var result = from c in context.viewerContexts
where c.UserID == userName
select new
{
Name = c.Name,
ID = c.ID,
};
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
foreach (var item in result)
{
DataRow row = dt.NewRow();
row["ID"] = item.ID;
row["Name"] = item.Name;
dt.Rows.Add(row);
}
return dt;
Problems with Connection Strings.
If you create the LINQ to SQL on the web application, by now you realized that deploying it is not a good option, as the connection string is embedded in the code. If you created a class library then you are save, just override the connection string in the web.config:
<connectionStrings>
<add name="AssemblyName.Properties.Settings.MyConnectionString"
connectionString="Data Source=myserver;Initial Catalog=mydatabase_TEST;Persist Security Info=True;User ID=myuserid;Password=mypassword"
providerName="System.Data.SqlClient" />
</connectionStrings>
Xml converting types.
When you create a column on SQL as a xml, LINQ to SQL will convert that column as an XElement looks like instead of a string. If you try to set that element from a string you’ll get the error:
Cannot implicitly convert type 'string' to 'System.Xml.Linq.XElement'
So you’ll have to set the content as: Viewer.Element.Add(Mycontent);
Maybe this is smart to only allow you to pass a XElement instead of string, the validation is done in your code instead of raising an exception.
Summary.
The wonderful world of LINQ to SQL is getting better every day. It’s time to jump into the code to start creating your own data layer as a Class Library in 3.5. You won’t have to convert the web application to 3.5, just make sure to return objects supported by framework 2.0.