Create SP with transaction

USE DB NAME SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --Declare Variables --================== Declare @ReleaseRef as integer= 3053 -- our change ref from spreadsheet Declare @Trackref as integer=290147 Declare @ClientRef as integer= 290147 -- incident or work request number Declare @ChangeDesc as varchar(80)='TFS11807 to update YOA' Declare @Env as varchar(4)='V4 Prod' Declare @ReRunnable as varchar ='Y' Declare @RunId int /****************************************/ DECLARE @RecCount INT DECLARE @Msg VARCHAR(200) DECLARE @SuccessInd VARCHAR(1) DECLARE @userid nVARCHAR(50) DECLARE @UpdDate DATETIME DECLARE @Stage VARCHAR(30) SET @Stage = '' SET @Msg = 'Script run incomplete due to errors.' SET @userid = 'DataFix ' SET @UpdDate = GETDATE() SET @RecCount = 0 SET @SuccessInd = 'N' DECLARE @EndTime datetime, @Error int, @StartTime datetime, @sql varchar(1000) SELECT @Error = 0 --Start Procedure --================ SELECT @StartTime = GETDATE() PRINT 'Start Time' PRINT @StartTime SET NOCOUNT OFF BEGIN TRY EXEC sbs_LogCCRun @ReleaseRef, @Trackref, @ClientRef, @ChangeDesc , @Env, @ReRunnable , @RunId OUTPUT IF ISNULL(@RunId,0) <= 0 BEGIN RAISERROR('Error occurred updating the ChangeControlLog',16,1) END END TRY BEGIN CATCH SET @Error = ISNULL(@Error,'') + ISNULL(ERROR_MESSAGE(),'') + ' - ' + CAST(@@Error AS CHAR) PRINT 'Errors Occurred - ' + CONVERT(char, GetDate(), 120) + ' : ' + ISNULL(@Error,'') GOTO Finish END CATCH BEGIN TRAN BEGIN TRY SET @Stage = 'Create archive table' IF OBJECT_ID('Datafixes.dbo.cc3053') IS NULL BEGIN PRINT 'Creating archive tables' CREATE TABLE Datafixes.dbo.cc3053Policy ( [RunId] [int], [PolicyId] [int] NULL, [PrevYOA] int, [UpdYOA] int, [LastUpd] [smalldatetime] NULL, [UpdBy] [nvarchar](50) NULL, --------------------------------------------------- SourceDB varchar(150) ,SourceServer varchar(100) ,HelpdeskRef varchar(20) ,ScriptRunDate datetime ) END IF OBJECT_ID('Datafixes.dbo.cc3053StatsHeader') IS NULL BEGIN PRINT 'Creating archive tables' CREATE TABLE Datafixes.dbo.cc3053StatsHeader ( [RunId] [int], [StatsHeaderId] [int] NULL, [PolicyId] [int] NULL, [PrevYOA] int, [UpdYOA] int, --------------------------------------------------- SourceDB varchar(150) ,SourceServer varchar(100) ,HelpdeskRef varchar(20) ,ScriptRunDate datetime ) END --main processing DECLARE @PolicyLineId int DECLARE @PolicyId int DECLARE @PrevYOA int DECLARE @NewYOA int DECLARE @Upd bit -- populate staging table (paste output from preaparatory script here). insert into datafixes.dbo.[TFS11807StgTable] ([PolicyId], [PrevYOA], [NewYOA]) values (552055, 2014, 2013) -- WRK290147 insert into datafixes.dbo.[TFS11807StgTable] ([PolicyId], [PrevYOA], [NewYOA]) values (552058, 2014, 2013) -- WRK290147 DECLARE DataCursor CURSOR FOR SELECT PolicyId, PrevYOA, NewYOA FROM Datafixes.dbo.TFS11807StgTable WHERE Errors IS NULL AND ISNULL(ProcessedInd,'N')<>'Y' OPEN DataCursor FETCH NEXT FROM DataCursor INTO @PolicyId, @PrevYOA, @NewYOA WHILE @@FETCH_STATUS = 0 BEGIN SET @Upd = 1 -- Check new YOA is valid IF @PrevYOA NOT IN ( SELECT YOA from policy where policyid=@PolicyId ) BEGIN UPDATE Datafixes.dbo.TFS11807StgTable SET Errors = isnull(Errors,'') + 'PrevYOA does not match the YOA on the Policy', ProcessedInd = 'Y', SourceDB=db_name(), SourceServer=@@servername, UserId=@UserId, ScriptRunDate=@UpdDate WHERE PolicyId = @PolicyId AND PrevYOA=@PrevYOA and NewYOA = @NewYOA AND ISNULL(ProcessedInd,'N')<>'Y' SET @Upd = 0 END -- Check new combination is valid IF NOT EXISTS (select 1 from UWLimit u inner join ReportingClass r on u.MajorClassCode = r.Class1 and u.MinorClassCode = r.Class2 and u.Class = r.Class3 and u.ClassType = r.Class4 and u.ProducingTeam = r.ProducingTeam and u.YOA = r.PIMYear inner join ApplicationUser a on a.ApplicationUserId = u.ApplicationUserId inner join policy p on p.UW = a.UserInitials and p.Class1 = r.Class1 and p.Class2 = r.Class2 and p.Class3 = r.Class3 and p.Class4 = r.class4 and @NewYOA = u.YOA inner join synd s on s.SyndId = u.SyndId and s.SyndNo = r.Synd where p.policyid=@policyId) BEGIN UPDATE Datafixes.dbo.TFS11807StgTable SET Errors = isnull(Errors,'') + 'Invalid combination for the Policy', ProcessedInd = 'Y', SourceDB=db_name(), SourceServer=@@servername, UserId=@UserId, ScriptRunDate=@UpdDate WHERE PolicyId = @PolicyId AND PrevYOA=@PrevYOA and NewYOA = @NewYOA AND ISNULL(ProcessedInd,'N')<>'Y' SET @Upd = 0 END UPDATE Policy SET YOA = @NewYOA, LastUpd = @UpdDate, UpdBy = @UserId OUTPUT @RunId, inserted.PolicyId, deleted.YOA, inserted.YOA, deleted.LastUpd, deleted.UpdBy, db_name(), @@servername, @UserId, @UpdDate INTO Datafixes.dbo.cc3053Policy WHERE PolicyId = @PolicyId AND @Upd = 1 IF EXISTS(SELECT 1 FROM dbo.PolicyLine WHERE PolicyId = @PolicyId AND LineStatus = 'written' AND ISNULL(DelDate, 0) = 0 ) BEGIN UPDATE StatsHeader SET YOA=@NewYOA OUTPUT @RunId, inserted.StatsHeaderID, inserted.PolicyId, deleted.YOA, inserted.YOA, db_name(), @@servername, @UserId, @UpdDate INTO Datafixes.dbo.cc3053StatsHeader WHERE PolicyId = @PolicyId AND @Upd = 1 END UPDATE Datafixes.dbo.TFS11807StgTable SET ProcessedInd = 'Y', Errors = isnull(Errors, '') + 'No Errors', SourceDB=db_name(), SourceServer=@@servername, UserId=@UserId, ScriptRunDate=@UpdDate WHERE PolicyId = @PolicyId AND PrevYOA=@PrevYOA and NewYOA = @NewYOA AND Errors IS NULL AND ISNULL(ProcessedInd,'N')<>'Y' FETCH NEXT FROM DataCursor INTO @PolicyID, @PrevYOA, @NewYOA END CLOSE DataCursor DEALLOCATE DataCursor ----------------------------------> SET @STAGE = 'COMMIT & COUNTS' COMMIT TRAN SELECT @Msg = 'Transaction completed successfully', @SuccessInd = 'Y', @RecCount = ISNULL( (SELECT COUNT(*) FROM Datafixes.dbo.TFS11807StgTable WHERE ScriptRunDate = @UpdDate),0) PRINT @Msg END TRY BEGIN CATCH IF @@TRANCOUNT > 0 --Beginning transaction increments this by 1, commiting and rolling back decrements by 1 ROLLBACK TRAN SELECT @Msg = 'Error: ' + ERROR_MESSAGE() + 'Line ' + CAST(ERROR_LINE() AS varchar(4)), @SuccessInd = 'N' PRINT @Msg PRINT GETDATE() END CATCH UPDATE ChangeControlLog SET NumTransUpdated = @RecCount ,RunDateTime = getdate() ,Comments = @msg ,SuccessInd = @SuccessInd WHERE RecNum = @RunId SELECT @EndTime = GETDATE() Finish: PRINT 'Finish Time' PRINT @EndTime

C# Coding Standards

C# Coding Standards and Best Programming Practices By PANKAJ PAREEK-604405 1. Author 3 2. License, Copyrights and Disclaimer 3 3. Revision History 3 4. Introduction 3 5. Purpose of coding standards and best practices 3 6. How to follow the standards across the team 4 7. Naming Conventions and Standards 4 8. Indentation and Spacing 7 9. Good Programming practices 10 10. Architecture 15 11. ASP.NET 16 12. Comments 16 13. Exception Handling 17 1. Author This document is prepared by the pankaj Pareek. Most of the information in this document is compiled from the coding standards and best practices published in various articles at different websites. 2. License, Copyrights and Disclaimer You are permitted to use and distribute this document for any non commercial purpose as long as you retain this license & copyrights information. This document is provided on “As-Is” basis. The author of this document will not be responsible for any kind of loss for you due to any inaccurate information provided in this document. 3. Revision History If you are editing this document, you are required to fill the revision history with your name and time stamp so that anybody can easily distinguish your updates from the original author. Sl# Date Changed By Description 1 4. Introduction Anybody can write code. With a few months of programming experience, you can write 'working applications'. Making it work is easy, but doing it the right way requires more work, than just making it work. Believe it, majority of the programmers write 'working code', but not ‘good code'. Writing 'good code' is an art and you must learn and practice it. Everyone may have different definitions for the term ‘good code’. In my definition, the following are the characteristics of good code. • Reliable • Maintainable • Efficient Most of the developers are inclined towards writing code for higher performance, compromising reliability and maintainability. But considering the long term ROI (Return On Investment), efficiency and performance comes below reliability and maintainability. If your code is not reliable and maintainable, you (and your company) will be spending lot of time to identify issues, trying to understand code etc throughout the life of your application. 5. Purpose of coding standards and best practices To develop reliable and maintainable applications, you must follow coding standards and best practices. The naming conventions, coding standards and best practices described in this document are compiled from our own experience and by referring to various Microsoft and non Microsoft guidelines. There are several standards exists in the programming industry. None of them are wrong or bad and you may follow any of them. What is more important is, selecting one standard approach and ensuring that everyone is following it. 6. How to follow the standards across the team If you have a team of different skills and tastes, you are going to have a tough time convincing everyone to follow the same standards. The best approach is to have a team meeting and developing your own standards document. You may use this document as a template to prepare your own document. Distribute a copy of this document (or your own coding standard document) well ahead of the coding standards meeting. All members should come to the meeting prepared to discuss pros and cons of the various points in the document. Make sure you have a manager present in the meeting to resolve conflicts. Discuss all points in the document. Everyone may have a different opinion about each point, but at the end of the discussion, all members must agree upon the standard you are going to follow. Prepare a new standards document with appropriate changes based on the suggestions from all of the team members. Print copies of it and post it in all workstations. After you start the development, you must schedule code review meetings to ensure that everyone is following the rules. 3 types of code reviews are recommended: 1. Peer review – another team member review the code to ensure that the code follows the coding standards and meets requirements. This level of review can include some unit testing also. Every file in the project must go through this process. 2. Architect review – the architect of the team must review the core modules of the project to ensure that they adhere to the design and there is no “big” mistakes that can affect the project in the long run. 3. Group review – randomly select one or more files and conduct a group review once in a week. Distribute a printed copy of the files to all team members 30 minutes before the meeting. Let them read and come up with points for discussion. In the group review meeting, use a projector to display the file content in the screen. Go through every sections of the code and let every member give their suggestions on how could that piece of code can be written in a better way. (Don’t forget to appreciate the developer for the good work and also make sure he does not get offended by the “group attack”!) 7. Naming Conventions and Standards Note : The terms Pascal Casing and Camel Casing are used throughout this document. Pascal Casing - First character of all words are Upper Case and other characters are lower case. Example: BackColor Camel Casing - First character of all words, except the first word are Upper Case and other characters are lower case. Example: backColor 1. Use Pascal casing for Class names public class HelloWorld { ... } 2. Use Pascal casing for Method names void SayHello(string name) { ... } 3. Use Camel casing for variables and method parameters int totalCount = 0; void SayHello(string name) { string fullMessage = "Hello " + name; ... } 4. Use the prefix “I” with Camel Casing for interfaces ( Example: IEntity ) 5. Do not use Hungarian notation to name variables. In earlier days most of the programmers liked it - having the data type as a prefix for the variable name and using m_ as prefix for member variables. Eg: string m_sName; int nAge; However, in .NET coding standards, this is not recommended. Usage of data type and m_ to represent member variables should not be used. All variables should use camel casing. Some programmers still prefer to use the prefix m_ to represent member variables, since there is no other easy way to identify a member variable. 6. Use Meaningful, descriptive words to name variables. Do not use abbreviations. Good: string address int salary Not Good: string nam string addr int sal 7. Do not use single character variable names like i, n, s etc. Use names like index, temp One exception in this case would be variables used for iterations in loops: for ( int i = 0; i < count; i++ ) { ... } If the variable is used only as a counter for iteration and is not used anywhere else in the loop, many people still like to use a single char variable (i) instead of inventing a different suitable name. 8. Do not use underscores (_) for local variable names. 9. All member variables must be prefixed with underscore (_) so that they can be identified from other local variables. 10. Do not use variable names that resemble keywords. 11. Prefix boolean variables, properties and methods with “is” or similar prefixes. Ex: private bool _isFinished 12. Namespace names should follow the standard pattern ... 13. Use appropriate prefix for the UI elements so that you can identify them from the rest of the variables. There are 2 different approaches recommended here. a. Use a common prefix ( ui_ ) for all UI elements. This will help you group all of the UI elements together and easy to access all of them from the intellisense. b. Use appropriate prefix for each of the ui element. A brief list is given below. Since .NET has given several controls, you may have to arrive at a complete list of standard prefixes for each of the controls (including third party controls) you are using. Control Prefix Label lbl TextBox txt DataGrid dtg Button btn ImageButton imb Hyperlink hlk DropDownList ddl ListBox lst DataList dtl Repeater rep Checkbox chk CheckBoxList cbl RadioButton rdo RadioButtonList rbl Image img Panel pnl PlaceHolder phd Table tbl Validators val 14. File name should match with class name. For example, for the class HelloWorld, the file name should be helloworld.cs (or, helloworld.vb) 15. Use Pascal Case for file names. 8. Indentation and Spacing 1. Use TAB for indentation. Do not use SPACES. Define the Tab size as 4. 2. Comments should be in the same level as the code (use the same level of indentation). Good: // Format a message and display string fullMessage = "Hello " + name; DateTime currentTime = DateTime.Now; string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString(); MessageBox.Show ( message ); Not Good: // Format a message and display string fullMessage = "Hello " + name; DateTime currentTime = DateTime.Now; string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString(); MessageBox.Show ( message ); 3. Curly braces ( {} ) should be in the same level as the code outside the braces. 4. Use one blank line to separate logical groups of code. Good: bool SayHello ( string name ) { string fullMessage = "Hello " + name; DateTime currentTime = DateTime.Now; string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString(); MessageBox.Show ( message ); if ( ... ) { // Do something // ... return false; } return true; } Not Good: bool SayHello (string name) { string fullMessage = "Hello " + name; DateTime currentTime = DateTime.Now; string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString(); MessageBox.Show ( message ); if ( ... ) { // Do something // ... return false; } return true; } 5. There should be one and only one single blank line between each method inside the class. 6. The curly braces should be on a separate line and not in the same line as if, for etc. Good: if ( ... ) { // Do something } Not Good: if ( ... ) { // Do something } 7. Use a single space before and after each operator and brackets. Good: if ( showResult == true ) { for ( int i = 0; i < 10; i++ ) { // } } Not Good: if(showResult==true) { for(int i= 0;i<10;i++) { // } } 8. Use #region to group related pieces of code together. If you use proper grouping using #region, the page should like this when all definitions are collapsed. 9. Keep private member variables, properties and methods in the top of the file and public members in the bottom. 9. Good Programming practices 1. Avoid writing very long methods. A method should typically have 1~25 lines of code. If a method has more than 25 lines of code, you must consider re factoring into separate methods. 2. Method name should tell what it does. Do not use mis-leading names. If the method name is obvious, there is no need of documentation explaining what the method does. Good: void SavePhoneNumber ( string phoneNumber ) { // Save the phone number. } Not Good: // This method will save the phone number. void SaveDetails ( string phoneNumber ) { // Save the phone number. } 3. A method should do only 'one job'. Do not combine more than one job in a single method, even if those jobs are very small. Good: // Save the address. SaveAddress ( address ); // Send an email to the supervisor to inform that the address is updated. SendEmail ( address, email ); void SaveAddress ( string address ) { // Save the address. // ... } void SendEmail ( string address, string email ) { // Send an email to inform the supervisor that the address is changed. // ... } Not Good: // Save address and send an email to the supervisor to inform that // the address is updated. SaveAddress ( address, email ); void SaveAddress ( string address, string email ) { // Job 1. // Save the address. // ... // Job 2. // Send an email to inform the supervisor that the address is changed. // ... } 4. Use the c# or VB.NET specific types (aliases), rather than the types defined in System namespace. int age; (not Int16) string name; (not String) object contactInfo; (not Object) Some developers prefer to use types in Common Type System than language specific aliases. 5. Always watch for unexpected values. For example, if you are using a parameter with 2 possible values, never assume that if one is not matching then the only possibility is the other value. Good: If ( memberType == eMemberTypes.Registered ) { // Registered user… do something… } else if ( memberType == eMemberTypes.Guest ) { // Guest user... do something… } else { // Un expected user type. Throw an exception throw new Exception (“Un expected value “ + memberType.ToString() + “’.”) // If we introduce a new user type in future, we can easily find // the problem here. } Not Good: If ( memberType == eMemberTypes.Registered ) { // Registered user… do something… } else { // Guest user... do something… // If we introduce another user type in future, this code will // fail and will not be noticed. } 6. Do not hardcode numbers. Use constants instead. Declare constant in the top of the file and use it in your code. However, using constants are also not recommended. You should use the constants in the config file or database so that you can change it later. Declare them as constants only if you are sure this value will never need to be changed. 7. Do not hardcode strings. Use resource files. 8. Convert strings to lowercase or upper case before comparing. This will ensure the string will match even if the string being compared has a different case. if ( name.ToLower() == “john” ) { //… } 9. Use String.Empty instead of “” Good: If ( name == String.Empty ) { // do something } Not Good: If ( name == “” ) { // do something } 10. Avoid using member variables. Declare local variables wherever necessary and pass it to other methods instead of sharing a member variable between methods. If you share a member variable between methods, it will be difficult to track which method changed the value and when. 11. Use enum wherever required. Do not use numbers or strings to indicate discrete values. Good: enum MailType { Html, PlainText, Attachment } void SendMail (string message, MailType mailType) { switch ( mailType ) { case MailType.Html: // Do something break; case MailType.PlainText: // Do something break; case MailType.Attachment: // Do something break; default: // Do something break; } } Not Good: void SendMail (string message, string mailType) { switch ( mailType ) { case "Html": // Do something break; case "PlainText": // Do something break; case "Attachment": // Do something break; default: // Do something break; } } 12. Do not make the member variables public or protected. Keep them private and expose public/protected Properties. 13. The event handler should not contain the code to perform the required action. Rather call another method from the event handler. 14. Do not programmatically click a button to execute the same action you have written in the button click event. Rather, call the same method which is called by the button click event handler. 15. Never hardcode a path or drive name in code. Get the application path programmatically and use relative path. 16. Never assume that your code will run from drive "C:". You may never know, some users may run it from network or from a "Z:". 17. In the application start up, do some kind of "self check" and ensure all required files and dependancies are available in the expected locations. Check for database connection in start up, if required. Give a friendly message to the user in case of any problems. 18. If the required configuration file is not found, application should be able to create one with default values. 19. If a wrong value found in the configuration file, application should throw an error or give a message and also should tell the user what are the correct values. 20. Error messages should help the user to solve the problem. Never give error messages like "Error in Application", "There is an error" etc. Instead give specific messages like "Failed to update database. Please make sure the login id and password are correct." 21. When displaying error messages, in addition to telling what is wrong, the message should also tell what should the user do to solve the problem. Instead of message like "Failed to update database.", suggest what should the user do: "Failed to update database. Please make sure the login id and password are correct." 22. Show short and friendly message to the user. But log the actual error with all possible information. This will help a lot in diagnosing problems. 23. Do not have more than one class in a single file. 24. Have your own templates for each of the file types in Visual Studio. You can include your company name, copy right information etc in the template. You can view or edit the Visual Studio file templates in the folder C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\ItemTemplatesCache\CSharp\1033. (This folder has the templates for C#, but you can easily find the corresponding folders or any other language) 25. Avoid having very large files. If a single file has more than 1000 lines of code, it is a good candidate for refactoring. Split them logically into two or more classes. 26. Avoid public methods and properties, unless they really need to be accessed from outside the class. Use “internal” if they are accessed only within the same assembly. 27. Avoid passing too many parameters to a method. If you have more than 4~5 parameters, it is a good candidate to define a class or structure. 28. If you have a method returning a collection, return an empty collection instead of null, if you have no data to return. For example, if you have a method returning an ArrayList, always return a valid ArrayList. If you have no items to return, then return a valid ArrayList with 0 items. This will make it easy for the calling application to just check for the “count” rather than doing an additional check for “null”. 29. Use the AssemblyInfo file to fill information like version number, description, company name, copyright notice etc. 30. Logically organize all your files within appropriate folders. Use 2 level folder hierarchies. You can have up to 10 folders in the root folder and each folder can have up to 5 sub folders. If you have too many folders than cannot be accommodated with the above mentioned 2 level hierarchy, you may need re factoring into multiple assemblies. 16. Make sure you have a good logging class which can be configured to log errors, warning or traces. If you configure to log errors, it should only log errors. But if you configure to log traces, it should record all (errors, warnings and trace). Your log class should be written such a way that in future you can change it easily to log to Windows Event Log, SQL Server, or Email to administrator or to a File etc without any change in any other part of the application. Use the log class extensively throughout the code to record errors, warning and even trace messages that can help you trouble shoot a problem. 17. If you are opening database connections, sockets, file stream etc, always close them in the finally block. This will ensure that even if an exception occurs after opening the connection, it will be safely closed in the finally block. 18. Declare variables as close as possible to where it is first used. Use one variable declaration per line. 19. Use StringBuilder class instead of String when you have to manipulate string objects in a loop. The String object works in weird way in .NET. Each time you append a string, it is actually discarding the old string object and recreating a new object, which is a relatively expensive operations. Consider the following example: public string ComposeMessage (string[] lines) { string message = String.Empty; for (int i = 0; i < lines.Length; i++) { message += lines [i]; } return message; } In the above example, it may look like we are just appending to the string object ‘message’. But what is happening in reality is, the string object is discarded in each iteration and recreated and appending the line to it. If your loop has several iterations, then it is a good idea to use StringBuilder class instead of String object. See the example where the String object is replaced with StringBuilder. public string ComposeMessage (string[] lines) { StringBuilder message = new StringBuilder(); for (int i = 0; i < lines.Length; i++) { message.Append( lines[i] ); } return message.ToString(); } 10. Architecture 1. Always use multi layer (N-Tier) architecture. 2. Never access database from the UI pages. Always have a data layer class which performs all the database related tasks. This will help you support or migrate to another database back end easily. 3. Use try-catch in your data layer to catch all database exceptions. This exception handler should record all exceptions from the database. The details recorded should include the name of the command being executed, stored proc name, parameters, connection string used etc. After recording the exception, it could be re thrown so that another layer in the application can catch it and take appropriate action. 4. Separate your application into multiple assemblies. Group all independent utility classes into a separate class library. All your database related files can be in another class library. 11. ASP.NET 1. Do not use session variables throughout the code. Use session variables only within the classes and expose methods to access the value stored in the session variables. A class can access the session using System.Web.HttpCOntext.Current.Session 2. Do not store large objects in session. Storing large objects in session may consume lot of server memory depending on the number of users. 3. Always use style sheet to control the look and feel of the pages. Never specify font name and font size in any of the pages. Use appropriate style class. This will help you to change the UI of your application easily in future. Also, if you like to support customizing the UI for each customer, it is just a matter of developing another style sheet for them 12. Comments Good and meaningful comments make code more maintainable. However, 1. Do not write comments for every line of code and every variable declared. 2. Use // or /// for comments. Avoid using /* … */ 3. Write comments wherever required. But good readable code will require very less comments. If all variables and method names are meaningful, that would make the code very readable and will not need many comments. 4. Do not write comments if the code is easily understandable without comment. The drawback of having lot of comments is, if you change the code and forget to change the comment, it will lead to more confusion. 5. Fewer lines of comments will make the code more elegant. But if the code is not clean/readable and there are less comments, that is worse. 6. If you have to use some complex or weird logic for any reason, document it very well with sufficient comments. 7. If you initialize a numeric variable to a special number other than 0, -1 etc, document the reason for choosing that value. 8. The bottom line is, write clean, readable code such a way that it doesn't need any comments to understand. 9. Perform spelling check on comments and also make sure proper grammar and punctuation is used. 13. Exception Handling 1. Never do a 'catch exception and do nothing'. If you hide an exception, you will never know if the exception happened or not. Lot of developers uses this handy method to ignore non significant errors. You should always try to avoid exceptions by checking all the error conditions programmatically. In any case, catching an exception and doing nothing is not allowed. In the worst case, you should log the exception and proceed. 2. In case of exceptions, give a friendly message to the user, but log the actual error with all possible details about the error, including the time it occurred, method and class name etc. 3. Always catch only the specific exception, not generic exception. Good: void ReadFromFile ( string fileName ) { try { // read from file. } catch (FileIOException ex) { // log error. // re-throw exception depending on your case. throw; } } Not Good: void ReadFromFile ( string fileName ) { try { // read from file. } catch (Exception ex) { // Catching general exception is bad... we will never know whether // it was a file error or some other error. // Here you are hiding an exception. // In this case no one will ever know that an exception happened. return ""; } } 4. No need to catch the general exception in all your methods. Leave it open and let the application crash. This will help you find most of the errors during development cycle. You can have an application level (thread level) error handler where you can handle all general exceptions. In case of an 'unexpected general error', this error handler should catch the exception and should log the error in addition to giving a friendly message to the user before closing the application, or allowing the user to 'ignore and proceed'. 5. When you re throw an exception, use the throw statement without specifying the original exception. This way, the original call stack is preserved. Good: catch { // do whatever you want to handle the exception throw; } Not Good: catch (Exception ex) { // do whatever you want to handle the exception throw ex; } 6. Do not write try-catch in all your methods. Use it only if there is a possibility that a specific exception may occur and it cannot be prevented by any other means. For example, if you want to insert a record if it does not already exists in database, you should try to select record using the key. Some developers try to insert a record without checking if it already exists. If an exception occurs, they will assume that the record already exists. This is strictly not allowed. You should always explicitly check for errors rather than waiting for exceptions to occur. On the other hand, you should always use exception handlers while you communicate with external systems like network, hardware devices etc. Such systems are subject to failure anytime and error checking is not usually reliable. In those cases, you should use exception handlers and try to recover from error. 7. Do not write very large try-catch blocks. If required, write separate try-catch for each task you perform and enclose only the specific piece of code inside the try-catch. This will help you find which piece of code generated the exception and you can give specific error message to the user. 8. Write your own custom exception classes if required in your application. Do not derive your custom exceptions from the base class SystemException. Instead, inherit from ApplicationException.

collation conflict between two databases

Below error coming when collation conflict between two databases: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. Resolution: INNER JOIN [test1\test1].CRM_PROD_MSCRM.dbo.Brit_TobaExtensionBase BTE ON BTE.Brit_BRMCode COLLATE DATABASE_DEFAULT =O.CRMID COLLATE DATABASE_DEFAULT

Add or subtract time for given date

-- Get London time -- GMT : 5:30 so take 330 minute -- BST: 4:30 so take 270 minute declare @createTime datetime set @createTime = '2014-01-09 10:39' select convert(varchar, dateadd(minute, -330, @createTime), 100); declare @createTime datetime set @createTime = '2014-01-09 10:39' select convert(varchar, dateadd(minute, -270, @createTime), 100);

Selecting data from two different servers in SQL Server

exec sp_addlinkedserver @server = 'MainServer'

select * from [MainServer].[TestDB].[dbo].[Emp]

Selecting SQL Data with Non-Duplicate Column Values

  select A.ClaimNo
from ClaimsDetails A
join ClaimsDetails B
on A.ClaimNo=B.ClaimNo
where B.IsActive='N'
group by A.ClaimNo
having COUNT(A.ClaimNo) = 1

select from another database dynamic query sql server

DECLARE @sql NVARCHAR(1000), @dbName varchar(50),@someValue INT SET @dbName ='OperationalDB' SET @sql = 'SELECT top 10 * FROM ' + QUOTENAME(@dbName) + '..Employee' --EXEC @sql - It will not work EXEC sp_ExecuteSQL @sql _ It will work

EXISTS in sql server

SELECT Name FROM Production.Product WHERE EXISTS (SELECT * FROM Production.ProductSubcategory WHERE ProductSubcategoryID = Production.Product.ProductSubcategoryID AND Name = 'Wheels')

How to use table name in dymanic query

Declare @tableName VARCHAR(100), @SQLString NVARCHAR(MAX) SET @tableName ='employee' SELECT @SQLString ='Select * from ' + QuoteName(@tableName) EXEC @SQLString

How to get max record from each department SQL SERVER

with cte as ( select *, rank() over (partition by User_Name order by Start_Timestamp desc) as [r] from AUDIT_EVENT ) select User_Name,Start_Timestamp from cte where [r] = 1;

Check services on remote server

class Program { static void Main() { ManagementScope scope = new ManagementScope("\\\\172.29.0.213"); //LONRMS0028 scope.Connect(); ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_Process"); ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query); foreach (ManagementObject oReturn in searcher.Get()) { string state = oReturn.Properties["State"].Value.ToString().Trim(); } } }

LINQ To XML Tutorials with Examples

For this article, we will be using a sample file called ‘Employees.xml’ for all our samples which is available with the source code. So make sure you keep it handy with you while are practicing these examples. The mark up for Employees.xml is as follows: 1 Sam Male 423-555-0124 424-555-0545

7A Cox Street Acampo CA 95220 USA

2 Lucy Female 143-555-0763 434-555-0567

Jess Bay Alta CA 95701 USA

3 Kate Female 166-555-0231 233-555-0442

23 Boxen Street Milford CA 96121 USA

4 Chris Male 564-555-0122 442-555-0154

124 Kutbay Montara CA 94037 USA

The application is a console application targeting .NET 3.5 framework, although you can use the latest .NET 4.0 framework too. I have also used ‘query expressions’, instead of Lambda expression in these samples. It is just a matter of preference and you are free to use any of these. This tutorial has been divided into 2 sections: Section 1: Read XML and Traverse the Document using LINQ To XML Section 2: Manipulate XML content and Persist the changes using LINQ To XML The following namespaces are needed while testing the samples: System; System.Collections.Generic; System.Linq; System.Text; System.Xml; System.Xml.Linq; Go grab a hot cup of coffee, put on your developer cap and let us get started: Section 1: Read XML and Traverse the XML Document using LINQ To XML 1. How Do I Read XML using LINQ to XML There are two ways to do so: Using the XElement class or the XDocument class. Both the classes contain the ‘Load()’ method which accepts a file, a URL or XMLReader and allows XML to be loaded. The primary difference between both the classes is that an XDocument can contain XML declaration, XML Document Type (DTD) and processing instructions. Moreover an XDocument contains one root XElement. Using XElement C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); IEnumerable employees = xelement.Elements(); // Read the entire XML foreach (var employee in employees) { Console.WriteLine(employee); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim employees As IEnumerable(Of XElement) = xelement.Elements() ' Read the entire XML For Each employee In employees Console.WriteLine(employee) Next employee Output: Using XDocument C# XDocument xdocument = XDocument.Load("..\\..\\Employees.xml"); IEnumerable employees = xdocument.Elements(); foreach (var employee in employees) { Console.WriteLine(employee); } VB.NET (Converted Code) Dim xdocument As XDocument = XDocument.Load("..\..\Employees.xml") Dim employees As IEnumerable(Of XElement) = xdocument.Elements() For Each employee In employees Console.WriteLine(employee) Next employee Output: Note 1: As you can observe, XDocument contains a single root element (Employees). Note 2: In order to generate an output similar to the one using XElement, use “xdocument.Root.Elements()” instead of “xdocument.Elements()” Note 3: VB.NET users can use a new feature called XML Literal. 2. How Do I Access a Single Element using LINQ to XML Let us see how to access the name of all the Employees and list them over here C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); IEnumerable employees = xelement.Elements(); Console.WriteLine("List of all Employee Names :"); foreach (var employee in employees) { Console.WriteLine(employee.Element("Name").Value); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim employees As IEnumerable(Of XElement) = xelement.Elements() Console.WriteLine("List of all Employee Names :") For Each employee In employees Console.WriteLine(employee.Element("Name").Value) Next employee Output: 3. How Do I Access Multiple Elements using LINQ to XML Let us see how to access the name of all Employees and also list the ID along with it C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); IEnumerable employees = xelement.Elements(); Console.WriteLine("List of all Employee Names along with their ID:"); foreach (var employee in employees) { Console.WriteLine("{0} has Employee ID {1}", employee.Element("Name").Value, employee.Element("EmpId").Value); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim employees As IEnumerable(Of XElement) = xelement.Elements() Console.WriteLine("List of all Employee Names along with their ID:") For Each employee In employees Console.WriteLine("{0} has Employee ID {1}", employee.Element("Name").Value, employee.Element("EmpId").Value) Next employee Output: 4. How Do I Access all Elements having a Specific Attribute using LINQ to XML Let us see how to access details of all Female Employees C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); var name = from nm in xelement.Elements("Employee") where (string)nm.Element("Sex") == "Female" select nm; Console.WriteLine("Details of Female Employees:"); foreach (XElement xEle in name) Console.WriteLine(xEle); VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim name = _ From nm In xelement.Elements("Employee") _ Where CStr(nm.Element("Sex")) = "Female" _ Select nm Console.WriteLine("Details of Female Employees:") For Each xEle As XElement In name Console.WriteLine(xEle) Next xEle Output: 5. How Do I access Specific Element having a Specific Attribute using LINQ to XML Let us see how to list all the Home Phone Nos. C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); var homePhone = from phoneno in xelement.Elements("Employee") where (string)phoneno.Element("Phone").Attribute("Type") == "Home" select phoneno; Console.WriteLine("List HomePhone Nos."); foreach (XElement xEle in homePhone) { Console.WriteLine(xEle.Element("Phone").Value); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim homePhone = _ From phoneno In xelement.Elements("Employee") _ Where CStr(phoneno.Element("Phone").Attribute("Type")) = "Home" _ Select phoneno Console.WriteLine("List HomePhone Nos.") For Each xEle As XElement In homePhone Console.WriteLine(xEle.Element("Phone").Value) Next xEle Output: 6. How Do I Find an Element within another Element using LINQ to XML Let us see how to find the details of Employees living in 'Alta' City C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); var addresses = from address in xelement.Elements("Employee") where (string)address.Element("Address").Element("City") == "Alta" select address; Console.WriteLine("Details of Employees living in Alta City"); foreach (XElement xEle in addresses) Console.WriteLine(xEle); VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim addresses = _ From address In xelement.Elements("Employee") _ Where CStr(address.Element("Address").Element("City")) = "Alta" _ Select address Console.WriteLine("Details of Employees living in Alta City") For Each xEle As XElement In addresses Console.WriteLine(xEle) Next xEle Output: 7. How Do I Find Nested Elements (using Descendants Axis) using LINQ to XML Let us see how to list all the zip codes in the XML file C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); Console.WriteLine("List of all Zip Codes"); foreach (XElement xEle in xelement.Descendants("Zip")) { Console.WriteLine((string)xEle); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Console.WriteLine("List of all Zip Codes") For Each xEle As XElement In xelement.Descendants("Zip") Console.WriteLine(CStr(xEle)) Next xEle Output: 8. How do I apply Sorting on Elements using LINQ to XML Let us see how to List and Sort all Zip Codes in ascending order C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); IEnumerable codes = from code in xelement.Elements("Employee") let zip = (string)code.Element("Address").Element("Zip") orderby zip select zip; Console.WriteLine("List and Sort all Zip Codes"); foreach (string zp in codes) Console.WriteLine(zp); VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim codes As IEnumerable(Of String) = _ From code In xelement.Elements("Employee") _ Let zip = CStr(code.Element("Address").Element("Zip")) _ Order By zip _ Select zip Console.WriteLine("List and Sort all Zip Codes") For Each zp As String In codes Console.WriteLine(zp) Next zp Output: Section 2: Manipulate XML content and Persist the changes using LINQ To XML 9. Create an XML Document with Xml Declaration/Namespace/Comments using LINQ to XML When you need to create an XML document containing XML declaration, XML Document Type (DTD) and processing instructions, Comments, Namespaces, you should go in for the XDocument class. C# XNamespace empNM = "urn:lst-emp:emp"; XDocument xDoc = new XDocument( new XDeclaration("1.0", "UTF-16", null), new XElement(empNM + "Employees", new XElement("Employee", new XComment("Only 3 elements for demo purposes"), new XElement("EmpId", "5"), new XElement("Name", "Kimmy"), new XElement("Sex", "Female") ))); StringWriter sw = new StringWriter(); xDoc.Save(sw); Console.WriteLine(sw); VB.NET (Converted Code) Dim empNM As XNamespace = "urn:lst-emp:emp" Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-16", Nothing), _ New XElement(empNM + "Employees", _ New XElement("Employee", _ New XComment("Only 3 elements for demo purposes"), _ New XElement("EmpId", "5"), _ New XElement("Name", "Kimmy"), _ New XElement("Sex", "Female")))) Dim sw As New StringWriter() xDoc.Save(sw) Console.WriteLine(sw) 10. Save the XML Document to a XMLWriter or to the disk using LINQ to XML Use the following code to save the XML to a XMLWriter or to your physical disk C# XNamespace empNM = "urn:lst-emp:emp"; XDocument xDoc = new XDocument( new XDeclaration("1.0", "UTF-16", null), new XElement(empNM + "Employees", new XElement("Employee", new XComment("Only 3 elements for demo purposes"), new XElement("EmpId", "5"), new XElement("Name", "Kimmy"), new XElement("Sex", "Female") ))); StringWriter sw = new StringWriter(); XmlWriter xWrite = XmlWriter.Create(sw); xDoc.Save(xWrite); xWrite.Close(); // Save to Disk xDoc.Save("C:\\Something.xml"); Console.WriteLine("Saved"); VB.NET (Converted Code) Dim empNM As XNamespace = "urn:lst-emp:emp" Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-16", Nothing),_ New XElement(empNM + "Employees", _ New XElement("Employee", _ New XComment("Only 3 elements for demo purposes"), _ New XElement("EmpId", "5"), _ New XElement("Name", "Kimmy"), _ New XElement("Sex", "Female")))) Dim sw As New StringWriter() Dim xWrite As XmlWriter = XmlWriter.Create(sw) xDoc.Save(xWrite) xWrite.Close() ' Save to Disk xDoc.Save("C:\Something.xml") Console.WriteLine("Saved") 11. Load an XML Document using XML Reader using LINQ to XML Use the following code to load the XML Document into an XML Reader C# XmlReader xRead = XmlReader.Create(@"..\\..\\Employees.xml"); XElement xEle = XElement.Load(xRead); Console.WriteLine(xEle); xRead.Close(); VB.NET (Converted Code) Dim xRead As XmlReader = XmlReader.Create("..\\..\\Employees.xml") Dim xEle As XElement = XElement.Load(xRead) Console.WriteLine(xEle) xRead.Close() 12. Find Element at a Specific Position using LINQ to XML Find the 2nd Employee Element C# // Using XElement Console.WriteLine("Using XElement"); XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emp1 = xEle.Descendants("Employee").ElementAt(1); Console.WriteLine(emp); Console.WriteLine("------------"); //// Using XDocument Console.WriteLine("Using XDocument"); XDocument xDoc = XDocument.Load("..\\..\\Employees.xml"); var emp1 = xDoc.Descendants("Employee").ElementAt(1); Console.WriteLine(emp); VB.NET (Converted Code) ' Using XElement Console.WriteLine("Using XElement") Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emp1 = xEle.Descendants("Employee").ElementAt(1) Console.WriteLine(emp) Console.WriteLine("------------") '// Using XDocument Console.WriteLine("Using XDocument") Dim xDoc As XDocument = XDocument.Load("..\..\Employees.xml") Dim emp1 = xDoc.Descendants("Employee").ElementAt(1) Console.WriteLine(emp) 13. List the First 2 Elements using LINQ to XML List the details of the first 2 Employees C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Take(2); foreach (var emp in emps) Console.WriteLine(emp); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Take(2) For Each emp In emps Console.WriteLine(emp) Next emp 14. List the 2nd and 3rd Element using LINQ to XML List the 2nd and 3rd Employees C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Skip(1).Take(2); foreach (var emp in emps) Console.WriteLine(emp); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Skip(1).Take(2) For Each emp In emps Console.WriteLine(emp) Next emp 15. List the Last 2 Elements using LINQ To XML We have been posting the entire elements as output in our previous examples. Let us say that you want to display only the Employee Name, use this query: C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Reverse().Take(2); foreach (var emp in emps) Console.WriteLine(emp.Element("EmpId") + "" + emp.Element("Name")); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Reverse().Take(2) For Each emp In emps Console.WriteLine(emp.Element("EmpId") + emp.Element("Name")) Next emp To display only the values without the XML tags, use the ‘Value’ property C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Reverse().Take(2); foreach (var emp in emps) Console.WriteLine(emp.Element("EmpId").Value + ". " + emp.Element("Name").Value); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Reverse().Take(2) For Each emp In emps Console.WriteLine(emp.Element("EmpId").Value & ". " & emp.Element("Name").Value) Next emp If you notice, the results are not ordered i.e. the Employee 4 is printed before 3. To order the results, just add call Reverse() again while filtering as shown below: C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Reverse().Take(2).Reverse(); foreach (var emp in emps) Console.WriteLine(emp.Element("EmpId").Value + ". " + emp.Element("Name").Value); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Reverse().Take(2).Reverse() For Each emp In emps Console.WriteLine(emp.Element("EmpId").Value & ". " & emp.Element("Name").Value) Next emp 16. Find the Element Count based on a condition using LINQ to XML Count the number of Employees living in the state CA C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); var stCnt = from address in xelement.Elements("Employee") where (string)address.Element("Address").Element("State") == "CA" select address; Console.WriteLine("No of Employees living in CA State are {0}", stCnt.Count()); VB.NET (Converted Code) XElement xelement = XElement.Load("..\\..\\Employees.xml"); var stCnt = from address in xelement.Elements("Employee") where (string)address.Element("Address").Element("State") == "CA" select address; Console.WriteLine("No of Employees living in CA State are {0}", stCnt.Count()); 17. Add a new Element at runtime using LINQ to XML You can add a new Element to an XML document at runtime by using the Add() method of XElement. The new Element gets added as the last element of the XML document. C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); xEle.Add(new XElement("Employee", new XElement("EmpId", 5), new XElement("Name", "George"))); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") xEle.Add(New XElement("Employee", _ New XElement("EmpId", 5), _ New XElement("Name", "George"))) Console.Write(xEle) 18. Add a new Element as the First Child using LINQ to XML In the previous example, by default the new Element gets added to the end of the XML document. If you want to add the Element as the First Child, use the ‘AddFirst()’ method C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); xEle.AddFirst(new XElement("Employee", new XElement("EmpId", 5), new XElement("Name", "George"))); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") xEle.AddFirst(New XElement("Employee", _ New XElement("EmpId", 5), _ New XElement("Name", "George"))) Console.Write(xEle) 19. Add an attribute to an Element using LINQ to XML To add an attribute to an Element, use the following code: C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); xEle.Add(new XElement("Employee", new XElement("EmpId", 5), new XElement("Phone", "423-555-4224", new XAttribute("Type", "Home")))); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") xEle.Add(New XElement("Employee", _ New XElement("EmpId", 5), _ New XElement("Phone", "423-555-4224", _ New XAttribute("Type", "Home")))) Console.Write(xEle) 20. Replace Contents of an Element/Elements using LINQ to XML Let us say that in the XML file, you want to change the Country from “USA” to “United States of America” for all the Elements. Here’s how to do so: C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var countries = xEle.Elements("Employee").Elements("Address").Elements("Country").ToList(); foreach (XElement cEle in countries) cEle.ReplaceNodes("United States Of America"); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim countries = xEle.Elements("Employee").Elements("Address").Elements("Country").ToList() For Each cEle As XElement In countries cEle.ReplaceNodes("United States Of America") Next cEle Console.Write(xEle) 21. Remove an attribute from all the Elements using LINQ to XML Let us say if you want to remove the Type attribute ( ) attribute for all the elements, then here’s how to do it. C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var phone = xEle.Elements("Employee").Elements("Phone").ToList(); foreach (XElement pEle in phone) pEle.RemoveAttributes(); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim phone = xEle.Elements("Employee").Elements("Phone").ToList() For Each pEle As XElement In phone pEle.RemoveAttributes() Next pEle Console.Write(xEle) To remove attribute of one Element based on a condition, traverse to that Element and SetAttributeValue("Type", null); You can also use SetAttributeValue(XName,object) to update an attribute value. 22. Delete an Element based on a condition using LINQ to XML If you want to delete an entire element based on a condition, here’s how to do it. We are deleting the entire Address Element C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var addr = xEle.Elements("Employee").ToList(); foreach (XElement addEle in addr) addEle.SetElementValue("Address", null); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim addr = xEle.Elements("Employee").ToList() For Each addEle As XElement In addr addEle.SetElementValue("Address", Nothing) Next addEle Console.Write(xEle) SetElementValue() can also be used to Update the content of an Element. 23. Remove ‘n’ number of Elements using LINQ to XML If you have a requirement where you have to remove ‘n’ number of Elements; For E.g. To remove the last 2 Elements, then here’s how to do it C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee"); emps.Reverse().Take(2).Remove(); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee") emps.Reverse().Take(2).Remove() Console.Write(xEle) 24. Save/Persists Changes to the XML using LINQ to XML All the manipulations we have done so far were in the memory and were not persisted in the XML file. If you have been wondering how to persist changes to the XML, once it is modified, then here’s how to do so. It’s quite simple. You just need to call the Save() method. It’s also worth observing that the structure of the code shown below is similar to the structure of the end result (XML document). That’s one of the benefits of LINQ to XML, that it makes life easier for developers by making it so easy to create and structure XML documents. C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); xEle.Add(new XElement("Employee", new XElement("EmpId", 5), new XElement("Name", "George"), new XElement("Sex", "Male"), new XElement("Phone", "423-555-4224", new XAttribute("Type", "Home")), new XElement("Phone", "424-555-0545", new XAttribute("Type", "Work")), new XElement("Address", new XElement("Street", "Fred Park, East Bay"), new XElement("City", "Acampo"), new XElement("State", "CA"), new XElement("Zip", "95220"), new XElement("Country", "USA")))); xEle.Save("..\\..\\Employees.xml"); Console.WriteLine(xEle); Console.ReadLine(); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") xEle.Add(New XElement("Employee", _ New XElement("EmpId", 5), _ New XElement("Name", "George"), _ New XElement("Sex", "Male"), _ New XElement("Phone", "423-555-4224", _ New XAttribute("Type", "Home")), _ New XElement("Phone", "424-555-0545", _ New XAttribute("Type", "Work")), _ New XElement("Address", _ New XElement("Street", "Fred Park, East Bay"), _ New XElement("City", "Acampo"), _ New XElement("State", "CA"), _ New XElement("Zip", "95220"), _ New XElement("Country", "USA")))) xEle.Save("..\..\Employees.xml") Console.WriteLine(xEle) Console.ReadLine() Well with that, we conclude this long article of some 'How Do I' operations while using LINQ to XML. Through this article, we have only attempted to scratch the surface of what can be done using LINQ to XML. LINQ to XML is an amazing API and I hope this set of examples has demonstrated that. The entire source of the article in C# and VB.NET can be downloaded over here. The VB.NET code has been translated using a C# to VB.NET Converting tool.

Insert an explicit value into a timestamp column

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. Suppose we have created OrderHistory table in current database. CREATE TABLE OrderHistory( OrderId BIGINT PRIMARY KEY, OrderDate TIMESTAMP ) Now if will try to insert some records into the OrderHistory table: INSERT INTO OrderHistory(OrderId,OrderDate) VALUES(100,GETDATE()) We will get error message : Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. Cause: We cannot insert explicit value in timestamp column in sql server Solution: Correct way to insert : INSERT INTO OrderHistory(OrderId,OrderDate) VALUES(100,DEFAULT) Or INSERT INTO OrderHistory(OrderId) VALUES(100)

WCF Interview Questions and Answers

Q1. What is WCF? WCF stands for Windows Communication Foundation. It is a Software development kit for developing services on Windows. WCF is introduced in .NET 3.0. in the System.ServiceModel namespace. WCF is based on basic concepts of Service oriented architecture (SOA) Q2. What is endpoint in WCF service? The endpoint is an Interface which defines how a client will communicate with the service. It consists of three main points: Address,Binding and Contract. Q3. Explain Address,Binding and contract for a WCF Service? Address:Address defines where the service resides. Binding:Binding defines how to communicate with the service. Contract:Contract defines what is done by the service. Q4. What are the various address format in WCF? a)HTTP Address Format:–> http://localhost: b)TCP Address Format:–> net.tcp://localhost: c)MSMQ Address Format:–> net.msmq://localhost: Q5. What are the types of binding available in WCF? A binding is identified by the transport it supports and the encoding it uses. Transport may be HTTP,TCP etc and encoding may be text,binary etc. The popular types of binding may be as below: WCF supports nine types of bindings. Basic binding Offered by the BasicHttpBinding class, this is designed to expose a WCF service as a legacy ASMX web service, so that old clients can work with new services. When used by the client, this binding enables new WCF clients to work with old ASMX services. TCP binding Offered by the NetTcpBinding class, this uses TCP for cross-machine communication on the intranet. It supports a variety of features, including reliability, transactions, and security, and is optimized for WCF-to-WCF communication. As a result, it requires both the client and the service to use WCF. Peer network binding Offered by the NetPeerTcpBinding class, this uses peer networking as a transport. The peer network-enabled client and services all subscribe to the same grid and broadcast messages to it. IPC binding Offered by the NetNamedPipeBinding class, this uses named pipes as a transport for same-machine communication. It is the most secure binding since it cannot accept calls from outside the machine and it supports a variety of features similar to the TCP binding. Web Service (WS) binding Offered by the WSHttpBinding class, this uses HTTP or HTTPS for transport, and is designed to offer a variety of features such as reliability, transactions, and security over the Internet. Federated WS binding Offered by the WSFederationHttpBinding class, this is a specialization of the WS binding, offering support for federated security. Duplex WS binding Offered by the WSDualHttpBinding class, this is similar to the WS binding except it also supports bidirectional communication from the service to the client. MSMQ binding Offered by the NetMsmqBinding class, this uses MSMQ for transport and is designed to offer support for disconnected queued calls. MSMQ integration binding Offered by the MsmqIntegrationBinding class, this converts WCF messages to and from MSMQ messages, and is designed to interoperate with legacy MSMQ clients. For WCF binding comparison, see http://www.pluralsight.com/community/blogs/aaron/archive/2007/03/22/46560.aspx Q6. What are the types of contract available in WCF? The main contracts are: a)Service Contract:Describes what operations the client can perform. b)Operation Contract : defines the method inside Interface of Service. c)Data Contract:Defines what data types are passed d)Message Contract:Defines whether a service can interact directly with messages Q7. What are the various ways of hosting a WCF Service? a)IIS b)Self Hosting c)WAS (Windows Activation Service) Q8. WWhat is the proxy for WCF Service? A proxy is a class by which a service client can Interact with the service. By the use of proxy in the client application we are able to call the different methods exposed by the service. Q9. How can we create Proxy for the WCF Service? We can create proxy using the tool svcutil.exe after creating the service. We can use the following command at command line. svcutil.exe *.wsdl *.xsd /language:C# /out:SampleProxy.cs /config:app.config Q10.What is the difference between WCF Service and Web Service? Web service is a part of WCF. WCF offers much more flexibility and portability to develop a service when comparing to web service. The following point provides the detailed differences between them : 1. Hosting : Webservices can be host in IIS, whereas WCF services can be hosted in IIS, Windows Activation Service, Self Hosting. 2. Encoding : Webservices uses XML 1.0, MTOM(Message Transmission Optimization Mechanism), DIME, Custom. WCF uses XML 1.0, MTOM, Binary, Custom. 3. Transports : Webservices can be accessed using HTTP, TCP, Custom. WCF services can be accessed using HTTP, TCP, Named Pipes, MSMQ, P2P, Custom. 4. Protocols : Webservices uses Security porotocols only. Whereas WCF services uses Security, Reliable Messaging, Transactions protocols. Q11.What is DataContract and ServiceContract?Explain Data represented by creating DataContract which expose the data which will be transefered /consumend from the serive to its clients. **Operations which is the functions provided by this service. To write an operation on WCF,you have to write it as an interface,This interface contains the “Signature” of the methods tagged by ServiceContract attribute,and all methods signature will be impelemtned on this interface tagged with OperationContract attribute.To implement these serivce contract you have to create a class which implement the interface and the actual implementation will be on that class.

Widget Application Part 3

.column{ width:306px; margin:14px 0px 14px 14px; background:#fff; float:left; min-height:50px; } .column .dragbox{ background:#fff; position:relative; /*border:1px solid #ddd;*/ margin:0px 0px 14px 0px; } .column .dragbox h2{ margin:0; font-size:12px; padding:5px; color:#000; font-family:Verdana; cursor:move; } .dragbox-content{ background:#fff; min-height:0px; margin:0px 0px 0px 0px; font-family:'Lucida Grande', Verdana; font-size:0.8em; line-height:1.5em; } .column .placeholder{ background: #f0f0f0; /*border:1px dashed #ddd;*/ } .dragbox h2.collapse{ background:#f0f0f0 url('collapse.png') no-repeat top right; } .dragbox h2 .configure{ font-size:11px; font-weight:normal; margin-right:30px; float:right; } .Edit { text-decoration:none; color: #000000; font-weight: normal; font-size: 12px; font-family: Arial; font-style: normal; } .Close {} .content-div { word-break:break-all; word-wrap: break-word; } .butSmallWidget { cursor: pointer; height: 25px; width: 56px; font-family: Arial; font-size: 12px; font-weight: bold; background-color: #000000; background-image: url( '../images/Widgets/btnGo-back.gif' ); border: 0px; background-repeat: no-repeat; }

Widget Application Part 2

Partial Class Widget Inherits System.Web.UI.Page Dim objclsDatabaseLayer As New ALTO.clsDatabaseLayer Dim stqry As String = String.Empty Public GroupID As Integer Public userSelectionList As CheckBoxList Dim FileContents As String = String.Empty Private glbName As ALTO.GetNameFunctions Public BackGroundColorImage As String Public BackGroundColorImageType As Int32 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load AjaxPro.Utility.RegisterTypeForAjax(GetType(CLAS.ClassroomAjaxFunctions)) GroupID = ALTO.Encryption.Utils.DecryptValue(Request.QueryString("GroupId")) 'Set BackGround Color/Image. Dim dtRecords As DataTable = objclsDatabaseLayer.ExecuteProcedure(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, "DL_GlobalGetAllFields_Condition", "@TableName", "WidgetsHomeBackground", ParameterDirection.Input, "@IDFieldName", "GroupID", ParameterDirection.Input, "@IDFieldValue", ALTO.Encryption.Utils.DecryptValue(Request.QueryString("GroupID")), ParameterDirection.Input, "@OrderBy", "0", ParameterDirection.Input, "@IDFieldCharValue", " ", ParameterDirection.Input) BackGroundColorImageType = dtRecords.Rows(0).Item("Type") If IsDBNull(dtRecords.Rows(0).Item("BackGroundColor")) = False Then BackGroundColorImage = dtRecords.Rows(0).Item("BackGroundColor") End If If IsDBNull(dtRecords.Rows(0).Item("BackGroundImagePath")) = False Then BackGroundColorImage = dtRecords.Rows(0).Item("BackGroundImagePath").ToString().Replace("../", "") End If hidWholeColor.Value = dtRecords.Rows(0).Item("WholeBackGroundColor") LoadCuteDetails() LoadWidgets() End Sub Public Sub LoadCuteDetails() Dim dtContent As DataTable = objclsDatabaseLayer.ExecuteProcedure(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, _ "DL_GroupHomePageGetContent", _ "@GroupID", GroupID, ParameterDirection.Input) If dtContent.Rows.Count > 0 Then FileContents = dtContent.Rows(0)("FileContents").ToString() 'For Documents and Page Hit Report' Dim objCommonFunction As New ALTO.commonfunction objCommonFunction.insertDocLog(dtContent.Rows(0)("ContentPageID"), 0, dtContent.Rows(0)("ContentName"), "O", GroupID, "WHP") 'End Here End If glbName = New ALTO.GetNameFunctions() FileContents = Regex.Replace(FileContents, "username", "username", RegexOptions.IgnoreCase) FileContents = Regex.Replace(FileContents, "firstname", "firstname", RegexOptions.IgnoreCase) FileContents = Regex.Replace(FileContents, "lastname", "lastname", RegexOptions.IgnoreCase) FileContents = FileContents.Replace("[username]", glbName.getLoginName(Session("user_id"))) FileContents = FileContents.Replace("[firstname]", glbName.getFirstName(Session("user_id"))) FileContents = FileContents.Replace("[lastname]", glbName.getLastName(Session("user_id"))) divBanner.InnerHtml = FileContents End Sub Public Sub LoadWidgets() Try Dim stqry As String stqry = "SELECT DISTINCT ColumnNo FROM Userwidgets WHERE UserId=" & Session("user_id") & " AND GroupId= " & GroupID & " ORDER BY ColumnNo " Dim dt As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry) Dim strmain As String = String.Empty Dim i, j As Int32 Dim objDiv As HtmlGenericControl Dim objDivChild As HtmlGenericControl 'For BackGround Color. stqry = "SELECT count(*) FROM Userwidgets WHERE IsShow =1 AND UserId=" & Session("user_id") & " AND GroupId= " & GroupID Dim RecordCount As Int32 = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.ScalerValue, stqry) For i = 0 To dt.Rows.Count - 1 objDiv = New HtmlGenericControl("div") objDiv.Attributes.Add("class", "column") objDiv.ID = dt.Rows(i)("ColumnNo") If RecordCount > 0 Then If BackGroundColorImageType = 1 Then objDiv.Style.Add("background-color", BackGroundColorImage) tdmain.Style.Add("background-color", BackGroundColorImage) Else objDiv.Style.Add("background-color", "transparent") tdmain.Style.Add("background-image", "url(" & BackGroundColorImage & ")") End If Else divmain.Style.Add("height", "0px") End If stqry = "SELECT * FROM Userwidgets WHERE IsShow =1 and ColumnNo ='" & dt.Rows(i)("ColumnNo") & "' AND UserId=" & Session("user_id") & " AND GroupId= " & GroupID & " ORDER BY RowNo " Dim dtInner As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry) For j = 0 To dtInner.Rows.Count - 1 objDivChild = New HtmlGenericControl("div") objDivChild.Attributes.Add("class", "dragbox") objDivChild.ID = dtInner.Rows(j)("Widgetid") Dim objh2 As New HtmlGenericControl("h2") Dim objhyp As New HtmlGenericControl("a") objhyp.InnerHtml = "Edit" objhyp.Attributes.Add("class", "Edit") Dim objImg As New System.Web.UI.WebControls.Image objImg.Attributes.Add("class", "Close") objImg.Width = 15 objImg.Height = 15 objImg.ImageUrl = "Images/Widgets/wrong_sign.png" Dim objDivContent As New HtmlGenericControl("div") objDivContent.Attributes.Add("class", "dragbox-content") Dim obj As Object stqry = " Select * FROM widgetDetails WHERE WidgetId=" & dtInner.Rows(j)("WidgetId") Dim dtwidgetDetails As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry) stqry = " Select FontFamily,FontColor,BackGroundColor,Size,IsBold,IsItalic from widgetstyle" Dim dtwidgetStyle As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry) If dtInner.Rows(j)("Title") = "My e-Learning Courses" Then Dim tbl As New HtmlTable() tbl.Style.Add("width", "306px") Dim tr As New HtmlTableRow() Dim tdH2 As New HtmlTableCell() Dim tdEdit As New HtmlTableCell() Dim tdClose As New HtmlTableCell() 'tbl.Style.Add("border-bottom", "1px solid #eee") tdH2.Style.Add("width", "261px") tdH2.Style.Add("word-break", "break-all") tdH2.Style.Add("word-wrap", "break-word") If dtwidgetDetails.Rows(0)("IsHeadingOn") Then objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading") objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily")) objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor")) tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor")) objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size")) objhyp.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor")) If dtwidgetStyle.Rows(0)("IsBold") Then objh2.Style.Add("font-weight", "Bold") Else objh2.Style.Add("font-weight", "normal") End If If dtwidgetStyle.Rows(0)("IsItalic") Then objh2.Style.Add("font-style", "italic") Else objh2.Style.Add("font-style", "normal") End If Else objh2.InnerHtml = "" End If Dim objcontent_div As New HtmlGenericControl("div") objcontent_div.Attributes.Add("class", "content-div") objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId") objcontent_div.Style.Add("display", "none") obj = TryCast(Page.LoadControl("FunctionWidgets/eLearningTables.ascx"), FunctionWidgets_eLearningTables) objcontent_div.Controls.Add(obj) Dim objcontent_div1 As New HtmlGenericControl("div") obj = TryCast(Page.LoadControl("FunctionWidgets/eLearningCourses.ascx"), FunctionWidgets_eLearningCourses) objcontent_div1.Controls.Add(obj) objDivChild.ID = dtInner.Rows(j)("WidgetId") objhyp.ID = "Edit" & dtInner.Rows(j)("WidgetId") objImg.ID = "Close" & dtInner.Rows(j)("WidgetId") objhyp.Style.Add("cursor", "pointer") objImg.Style.Add("cursor", "pointer") tdH2.Controls.Add(objh2) tdEdit.Controls.Add(objhyp) tdClose.Controls.Add(objImg) tr.Controls.Add(tdH2) tr.Controls.Add(tdEdit) tr.Controls.Add(tdClose) tbl.Controls.Add(tr) objDivChild.Controls.Add(tbl) objDivChild.Controls.Add(objDivContent) objDivContent.Controls.Add(objcontent_div) objDivContent.Controls.Add(objcontent_div1) objDiv.Controls.Add(objDivChild) ElseIf dtInner.Rows(j)("Title") = "Media Monitor" Then Dim tbl As New HtmlTable() Dim tr As New HtmlTableRow() Dim tdH2 As New HtmlTableCell() Dim tdClose As New HtmlTableCell() 'tbl.Style.Add("border-bottom", "1px solid #eee") tdH2.Style.Add("width", "281px") tdH2.Style.Add("word-break", "break-all") tdH2.Style.Add("word-wrap", "break-word") If dtwidgetDetails.Rows(0)("IsHeadingOn") Then objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading") objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily")) objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor")) tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor")) objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size")) If dtwidgetStyle.Rows(0)("IsBold") Then objh2.Style.Add("font-weight", "Bold") Else objh2.Style.Add("font-weight", "normal") End If If dtwidgetStyle.Rows(0)("IsItalic") Then objh2.Style.Add("font-style", "italic") Else objh2.Style.Add("font-style", "normal") End If Else objh2.InnerHtml = "" End If Dim objcontent_div As New HtmlGenericControl("div") objcontent_div.Attributes.Add("class", "content-div") objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId") obj = TryCast(Page.LoadControl("FunctionWidgets/MediaMonitorControl.ascx"), FunctionWidgets_MediaMonitorControl) objcontent_div.Controls.Add(obj) objcontent_div.Style.Add("word-break", "keep-all") objDivChild.ID = dtInner.Rows(j)("WidgetId") objImg.ID = "Close" & dtInner.Rows(j)("WidgetId") objImg.Style.Add("cursor", "pointer") tdH2.Controls.Add(objh2) tdClose.Controls.Add(objImg) tr.Controls.Add(tdH2) tr.Controls.Add(tdClose) tbl.Controls.Add(tr) objDivChild.Controls.Add(tbl) objDivChild.Controls.Add(objDivContent) objDivContent.Controls.Add(objcontent_div) objDiv.Controls.Add(objDivChild) ElseIf dtInner.Rows(j)("Title") = "Clock" Then Dim tbl As New HtmlTable() Dim tr As New HtmlTableRow() Dim tdH2 As New HtmlTableCell() Dim tdClose As New HtmlTableCell() 'tbl.Style.Add("border-bottom", "1px solid #eee") tdH2.Style.Add("width", "281px") tdH2.Style.Add("word-break", "break-all") tdH2.Style.Add("word-wrap", "break-word") If dtwidgetDetails.Rows(0)("IsHeadingOn") Then 'objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading") objh2.ID = "clockID" objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily")) objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor")) tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor")) objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size")) If dtwidgetStyle.Rows(0)("IsBold") Then objh2.Style.Add("font-weight", "Bold") Else objh2.Style.Add("font-weight", "normal") End If If dtwidgetStyle.Rows(0)("IsItalic") Then objh2.Style.Add("font-style", "italic") Else objh2.Style.Add("font-style", "normal") End If Else objh2.InnerHtml = "" End If Dim objcontent_div As New HtmlGenericControl("div") objcontent_div.Attributes.Add("class", "content-div") objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId") obj = TryCast(Page.LoadControl("FunctionWidgets/Clock.ascx"), FunctionWidgets_Clock) objcontent_div.Controls.Add(obj) If BackGroundColorImageType = 1 Then objcontent_div.Style.Add("background-color", BackGroundColorImage) Else objcontent_div.Style.Add("background-image", "url(" & BackGroundColorImage & ")") End If objDivChild.ID = dtInner.Rows(j)("WidgetId") objImg.ID = "Close" & dtInner.Rows(j)("WidgetId") objImg.Style.Add("cursor", "pointer") tdH2.Controls.Add(objh2) tdClose.Controls.Add(objImg) tr.Controls.Add(tdH2) tr.Controls.Add(tdClose) tbl.Controls.Add(tr) objDivChild.Controls.Add(tbl) objDivChild.Controls.Add(objDivContent) objDivContent.Controls.Add(objcontent_div) objDiv.Controls.Add(objDivChild) ElseIf dtInner.Rows(j)("Title") = "Forums" Then Dim tbl As New HtmlTable() Dim tr As New HtmlTableRow() Dim tdH2 As New HtmlTableCell() Dim tdClose As New HtmlTableCell() 'tbl.Style.Add("border-bottom", "1px solid #eee") tdH2.Style.Add("width", "281px") tdH2.Style.Add("word-break", "break-all") tdH2.Style.Add("word-wrap", "break-word") If dtwidgetDetails.Rows(0)("IsHeadingOn") Then objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading") objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily")) objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor")) tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor")) objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size")) If dtwidgetStyle.Rows(0)("IsBold") Then objh2.Style.Add("font-weight", "Bold") Else objh2.Style.Add("font-weight", "normal") End If If dtwidgetStyle.Rows(0)("IsItalic") Then objh2.Style.Add("font-style", "italic") Else objh2.Style.Add("font-style", "normal") End If Else objh2.InnerHtml = "" End If Dim objcontent_div As New HtmlGenericControl("div") objcontent_div.Attributes.Add("class", "content-div") objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId") obj = TryCast(Page.LoadControl("FunctionWidgets/AccessForumsWidget.ascx"), FunctionWidgets_AccessForumsWidget) objcontent_div.Controls.Add(obj) objDivChild.ID = dtInner.Rows(j)("WidgetId") objImg.ID = "Close" & dtInner.Rows(j)("WidgetId") objImg.Style.Add("cursor", "pointer") tdH2.Controls.Add(objh2) tdClose.Controls.Add(objImg) tr.Controls.Add(tdH2) tr.Controls.Add(tdClose) tbl.Controls.Add(tr) objDivChild.Controls.Add(tbl) objDivChild.Controls.Add(objDivContent) objDivContent.Controls.Add(objcontent_div) objDiv.Controls.Add(objDivChild) ElseIf dtInner.Rows(j)("Title") = "Training Events And Programmes" Then Dim tbl As New HtmlTable() Dim tr As New HtmlTableRow() Dim tdH2 As New HtmlTableCell() Dim tdClose As New HtmlTableCell() 'tbl.Style.Add("border-bottom", "1px solid #eee") tdH2.Style.Add("width", "281px") tdH2.Style.Add("word-break", "break-all") tdH2.Style.Add("word-wrap", "break-word") If dtwidgetDetails.Rows(0)("IsHeadingOn") Then objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading") objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily")) objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor")) tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor")) objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size")) If dtwidgetStyle.Rows(0)("IsBold") Then objh2.Style.Add("font-weight", "Bold") Else objh2.Style.Add("font-weight", "normal") End If If dtwidgetStyle.Rows(0)("IsItalic") Then objh2.Style.Add("font-style", "italic") Else objh2.Style.Add("font-style", "normal") End If Else objh2.InnerHtml = "" End If Dim objcontent_div As New HtmlGenericControl("div") objcontent_div.Attributes.Add("class", "content-div") objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId") obj = TryCast(Page.LoadControl("FunctionWidgets/TrainingEventsAndProgrammes.ascx"), FunctionWidgets_TrainingEventsAndProgrammes) objcontent_div.Controls.Add(obj) objDivChild.ID = dtInner.Rows(j)("WidgetId") objImg.ID = "Close" & dtInner.Rows(j)("WidgetId") objImg.Style.Add("cursor", "pointer") tdH2.Controls.Add(objh2) tdClose.Controls.Add(objImg) tr.Controls.Add(tdH2) tr.Controls.Add(tdClose) tbl.Controls.Add(tr) objDivChild.Controls.Add(tbl) objDivChild.Controls.Add(objDivContent) objDivContent.Controls.Add(objcontent_div) objDiv.Controls.Add(objDivChild) ElseIf dtInner.Rows(j)("Title") = "Compliance Course" Then Dim tbl As New HtmlTable() Dim tr As New HtmlTableRow() Dim tdH2 As New HtmlTableCell() Dim tdEdit As New HtmlTableCell() Dim tdClose As New HtmlTableCell() 'tbl.Style.Add("border-bottom", "1px solid #eee") tdH2.Style.Add("width", "261px") tdH2.Style.Add("word-break", "break-all") tdH2.Style.Add("word-wrap", "break-word") If dtwidgetDetails.Rows(0)("IsHeadingOn") Then objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading") objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily")) objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor")) tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor")) objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size")) objhyp.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor")) If dtwidgetStyle.Rows(0)("IsBold") Then objh2.Style.Add("font-weight", "Bold") Else objh2.Style.Add("font-weight", "normal") End If If dtwidgetStyle.Rows(0)("IsItalic") Then objh2.Style.Add("font-style", "italic") Else objh2.Style.Add("font-style", "normal") End If Else objh2.InnerHtml = "" End If Dim objcontent_div As New HtmlGenericControl("div") objcontent_div.Attributes.Add("class", "content-div") objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId") objcontent_div.Style.Add("display", "none") obj = TryCast(Page.LoadControl("FunctionWidgets/ComplianceWidgetTables.ascx"), FunctionWidgets_ComplianceWidgetTables_) objcontent_div.Controls.Add(obj) Dim objcontent_div1 As New HtmlGenericControl("div") obj = TryCast(Page.LoadControl("FunctionWidgets/ComplianceWidgetCourses.ascx"), FunctionWidgets_ComplianceWidgetCourses) objcontent_div1.Controls.Add(obj) objDivChild.ID = dtInner.Rows(j)("WidgetId") objhyp.ID = "Edit" & dtInner.Rows(j)("WidgetId") objImg.ID = "Close" & dtInner.Rows(j)("WidgetId") objhyp.Style.Add("cursor", "pointer") objImg.Style.Add("cursor", "pointer") tdH2.Controls.Add(objh2) tdEdit.Controls.Add(objhyp) tdClose.Controls.Add(objImg) tr.Controls.Add(tdH2) tr.Controls.Add(tdEdit) tr.Controls.Add(tdClose) tbl.Controls.Add(tr) objDivChild.Controls.Add(tbl) objDivChild.Controls.Add(objDivContent) objDivContent.Controls.Add(objcontent_div) objDivContent.Controls.Add(objcontent_div1) objDiv.Controls.Add(objDivChild) Else Dim tbl As New HtmlTable() Dim tr As New HtmlTableRow() Dim tdH2 As New HtmlTableCell() Dim tdClose As New HtmlTableCell() 'tbl.Style.Add("border-bottom", "1px solid #eee") tdH2.Style.Add("width", "281px") tdH2.Style.Add("word-break", "break-all") tdH2.Style.Add("word-wrap", "break-word") Dim objcontent_div As New HtmlGenericControl("div") objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId") objcontent_div.Attributes.Add("class", "content-div") 'Html Widgets Dim strHeading As String = String.Empty Dim strSubHeading As String = String.Empty Dim strFooter As String = String.Empty If dtwidgetDetails.Rows(0)("IsHeadingOn") Then objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading") objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily")) objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor")) tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor")) objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size")) If dtwidgetStyle.Rows(0)("IsBold") Then objh2.Style.Add("font-weight", "Bold") Else objh2.Style.Add("font-weight", "normal") End If If dtwidgetStyle.Rows(0)("IsItalic") Then objh2.Style.Add("font-style", "italic") Else objh2.Style.Add("font-style", "normal") End If Else objh2.InnerHtml = "" End If If dtwidgetDetails.Rows(0)("IsSubHeadingOn") Then Dim FontWeightStyle As String = String.Empty If dtwidgetStyle.Rows(1)("IsBold") Then FontWeightStyle = "font-weight:Bold;" Else FontWeightStyle = "font-weight:normal;" End If If dtwidgetStyle.Rows(1)("IsItalic") Then FontWeightStyle = FontWeightStyle & "font-style:italic;" Else FontWeightStyle = FontWeightStyle & "font-style:normal;" End If strSubHeading = "" & dtwidgetDetails.Rows(0)("SubHeading") & " " Else 'strSubHeading = " " End If If dtwidgetDetails.Rows(0)("IsFooterOn") Then Dim FontWeightStyle As String = String.Empty If dtwidgetStyle.Rows(2)("IsBold") Then FontWeightStyle = "font-weight:Bold;" Else FontWeightStyle = "font-weight:normal;" End If If dtwidgetStyle.Rows(2)("IsItalic") Then FontWeightStyle = FontWeightStyle & "font-style:italic;" Else FontWeightStyle = FontWeightStyle & "font-style:normal;" End If strFooter = "" & dtwidgetDetails.Rows(0)("Footer") & " " Else 'strFooter = " " End If Dim strdtwidgetDetails As String = String.Empty strdtwidgetDetails = "" & strHeading & strSubHeading & _ "" & strFooter & "

" & dtwidgetDetails.Rows(0)("BodyContent") & "

" objcontent_div.InnerHtml = strdtwidgetDetails objcontent_div.Style.Add("word-break", "keep-all") 'End Here. objImg.ID = "Close" & dtInner.Rows(j)("WidgetId") objImg.Style.Add("cursor", "pointer") objDivChild.ID = dtInner.Rows(j)("WidgetId") tdH2.Controls.Add(objh2) tdClose.Controls.Add(objImg) tr.Controls.Add(tdH2) tr.Controls.Add(tdClose) tbl.Controls.Add(tr) objDivChild.Controls.Add(tbl) objDivChild.Controls.Add(objDivContent) objDivContent.Controls.Add(objcontent_div) objDiv.Controls.Add(objDivChild) End If Next divmain.Controls.Add(objDiv) Next ShowWidgetStatusTable() Catch ex As Exception End Try End Sub Private Sub ShowWidgetStatusTable() Try Dim i As Integer stqry = "SELECT Userwidgets.WidgetId,widgetDetails.Heading,Userwidgets.IsShow FROM Userwidgets" & _ " INNER JOIN widgetDetails ON widgetDetails.WidgetId =Userwidgets.WidgetId WHERE UserId=" & Session("user_id") & " AND GroupId= " & GroupID & " AND Userwidgets.widgetid NOT IN (1,2,3) ORDER BY Userwidgets.RowNo ,Userwidgets.ColumnNo " Dim dtWidget As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry) If dtWidget.Rows.Count > 0 Then tdResetHomePage.Style.Add("display", "block") userSelectionList = New CheckBoxList userSelectionList.ID = "mylist" userSelectionList.RepeatDirection = RepeatDirection.Horizontal userSelectionList.CellPadding = 3 userSelectionList.CellSpacing = 10 userSelectionList.RepeatColumns = "3" userSelectionList.RepeatLayout = RepeatLayout.Table For i = 0 To dtWidget.Rows.Count - 1 Dim selectedItem As New ListItem(dtWidget.Rows(i)("Heading"), dtWidget.Rows(i)("WidgetId")) selectedItem.Selected = CType(dtWidget.Rows(i)("IsShow"), Boolean) selectedItem.Attributes.Add("ID", dtWidget.Rows(i)("WidgetId")) userSelectionList.Items.Add(selectedItem) Next userSelectionList.Attributes.Add("onclick", "SetCheckedUnchecked();") checkboxContainer.Controls.Add(userSelectionList) End If Catch ex As Exception End Try End Sub Protected Sub btnReset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReset.Click Response.Redirect("Widget.aspx?GroupId=" & Request.QueryString("GroupId")) End Sub End Class