ASP.NET 2.0 and LINQ

项目
08/11/2008

I have been developing and maintaining an internal website at Microsoft IT which monitors the status of multiple master data management systems for almost 1 year. At the beginning of the development phase, I decided to use ADO.NET for querying and updating system status data. I was satisfied with the flexibility of ADO.NET 2.0. Testing the code was as easy as running queries in SQL server management studio 2005. Recently, I watched several videos at https://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/05/10/9322.aspx about .NET integrated query which is a new feature in .NET 3.5. With the aid of Visual Studio 2008, building data model is as easy as few steps of drag and drop into the designer. Visual studio 2008 has a nice designer interface which uses code behind concept to turn database schema into C# code. It all starts with Add New Item -> LINQ to SQL Classes. The file extension is .dbml as you can see in the videos. All I had to do to setup the class was drag the table from Visual Studio’s Server Explorer and drop it to the .dbml designer page.

The next step is to find out the class name of the code behind page. If the .dbml filename is WebServicesSettings.dbml, the code behind file would be WebServicesSettings.designer.cs. Inside the code file, you would find WebServicesSettingsDataContext class inherited from System.Data.Linq.DataContext. The class enables you to manipulate the data in the database. In the example below, I will show you how to query a string value from table EnvironmentEndpoints and use it as the SQL server hostname to connect to another database to query 4 other string values which are MSMQ endpoints. When the 4 MSMQ endpoints are acquired, the code updates the EnvironmentEndpoints table with the 4 MSMQ endpoints with command EnvEndDC.SubmitChanges();

byte envID = kvp.Key;

try

{

// the connection string in web.config is used for the default constructor

EnvEndpointsDataContext EnvEndDC = new EnvEndpointsDataContext();

// Very useful command similar to SqlCommand.ExecuteScalar

EnvironmentEndpoint endpoint = EnvEndDC.EnvironmentEndpoints.Single(end => end.EnvironmentID == envID);

SqlConnectionStringBuilder sqlConSB = new SqlConnectionStringBuilder();

// build the connection string to connect to the MSMQ endpoint database

sqlConSB.DataSource = endpoint.SQLServer;

sqlConSB.InitialCatalog = "ConfigurationStore";

sqlConSB.IntegratedSecurity = true;

// example of using impersonation

ServiceStatus.impersonated = ServiceStatus.impersonateValidUser(serviceAccountUser, serviceAccountDomain, serviceAccountPassword);

if (!ServiceStatus.impersonated)

{

// impersonation failed

string message = DateTime.Now + " - Impersonaion failed. Continue updating MSMQ endpoints...";

Trace.WriteLineIf(ServiceStatus.debugSwitch.TraceError, message);

}

// connect to the MSMQ endpoint database

WebServicesSettingsDataContext WebServiceDC = new WebServicesSettingsDataContext(sqlConSB.ToString());

// WSSetting is a local variable name to the LINQ query for the Single command

// What I am doing here is querying the row which has column ‘Name’ ServiceAsyncAddressSecondServer

WebServicesSetting ServiceAsyncAddressSecondServer = WebServiceDC.WebServicesSettings.Single(WSSetting => WSSetting.Name == "ServiceAsyncAddressSecondServer");
// Update the values in EnvironmentEndpoints table

endpoint.CorpNetAsyncWebService = ServiceAsyncAddressSecondServer.Value;

WebServicesSetting AcknowledgmentServiceAddressSecondServer = WebServiceDC.WebServicesSettings.Single(WSSetting => WSSetting.Name == "AcknowledgmentServiceAddressSecondServer");

endpoint.CorpNetAckWebService = AcknowledgmentServiceAddressSecondServer.Value;

WebServicesSetting ServiceAsyncAddress = WebServiceDC.WebServicesSettings.Single(WSSetting => WSSetting.Name == "ServiceAsyncAddress");

endpoint.ExtraNetAsyncWebService = ServiceAsyncAddress.Value == ServiceAsyncAddressSecondServer.Value ? string.Empty : ServiceAsyncAddress.Value;

WebServicesSetting AcknowledgmentServiceAddress = WebServiceDC.WebServicesSettings.Single(WSSetting => WSSetting.Name == "AcknowledgmentServiceAddress");

endpoint.ExtraNetAckWebService = AcknowledgmentServiceAddress.Value == AcknowledgmentServiceAddressSecondServer.Value ? string.Empty : AcknowledgmentServiceAddress.Value;

if (ServiceStatus.impersonated)

{

ServiceStatus.undoImpersonation();

ServiceStatus.impersonated = false;

}

// LINQ runtime generates a SQL update when the line is executed

EnvEndDC.SubmitChanges();

}

catch (Exception ex)

{

string message = DateTime.Now + " - Failed to update MSMQ endpoints on Environment ID " + envID + " -> " + ex.GetType() + ": " + ex.Message;