EXCEL Operation in .NET
This post is regarding how we can read and write EXCEL based on our requirement. I have worked for many applications and each application requirement is different so based on my experience I have written this blog. May be it will save your time.
Below are the three techniques by which we can automate Excel
1. OLE DB Provider
2. COM Component
3. Open XML
Below is comparison for this three
Below are the examples for it
1. OLE DB
private string ExcelConnection()
{
return
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=" + _strExcelFilename + ";" +
@"Extended Properties=" + Convert.ToChar(34).ToString() +
@"Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString();
}
_oleConn = new OleDbConnection(ExcelConnection());
_oleConn.Open();
_oleCmdSelect =new OleDbCommand(
@"SELECT * FROM ["
+ _strSheetName
+ "$" + _strSheetRange
+ "]", _oleConn);
OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = _oleCmdSelect;
DataTable dt = new DataTable(strTableName);
oleAdapter.FillSchema(dt,SchemaType.Source);
oleAdapter.Fill(dt);
2. COM Component by adding reference of Excel component
3. Private Sub TestWorksheetFunction()
4. Dim ws As Excel.Worksheet = _
5. DirectCast(ThisWorkbook.ActiveSheet, Excel.Worksheet)
6. Dim rng As Excel.Range = ws.Range("RandomNumbers")
7. Dim rnd As New System.Random
8.
9. Dim i As Integer
10. For i = 1 To 20
11. ws.Cells(i, 2) = rnd.Next(100)
12. Next i
13. rng.Sort(rng, _
14. Orientation:=Excel.XlSortOrientation.xlSortColumns)
15.
16. With ThisApplication.WorksheetFunction
17. ws.Range("Min").Value = .Min(rng)
18. ws.Range("Max").Value = .Max(rng)
19. ws.Range("Median").Value = .Median(rng)
20. ws.Range("Average").Value = .Average(rng)
21. ws.Range("StDev").Value = .StDev(rng)
22. End With
23. End Sub
24.
3. OPEN XML
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
private void ReadUserInputExcel(string filename)
{
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(filename, true);
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
}
Mohit Jethva's Blog
Tuesday, May 10, 2011
Multi-Threading and Delegates
There are 2 ways of using multi-threading in .NET.
1. By making use of delegates
2. By using Thread class
-- Multi Threading using Delegate
This is best approch for passing parameter and checking status of current execution.
E.g.
Delegate Function MyDelegate(ByVal text as String) as String
Function PrintStuff(ByVal text as String) as String
Console.WriteLine(text)
Thread.Sleep(5000)
Return "Success"
End Function
Sub Main()
Dim d as MyDelegate = AddressOf PrintStuff
d.BeginInvoke("hello world", Nothing, Nothing)
'I can do more stuff here
End Sub
Here we have a Main thread and a second thread to print some stuff. Also, a delegate with the same signature as the function we want to run (PrintStuff) already exists. Very simply, the Main thread creates a delegate that points to PrintsStuff and calls for the delegate to start executing PrintStuff on a separate thread. A parameter is passed to PrintStuff in the process and Main can continue working while PrintStuff is busy.
Now suppose you want to know when PrintStuff finishes because you want the result “Success”. We can do this by the use of a callback function.
First, we create the callback function
Sub MyCallback(ByVal result as IAsyncResult)
Console.WriteLine("Now I know PrintStuff finished")
End Sub
Then we make sure the callback function gets called by changing the previous BeginInvoke command to
d.BeginInvoke("hello world", New AsyncCallback(AddressOf MyCallback),Nothing)
1. By making use of delegates
2. By using Thread class
-- Multi Threading using Delegate
This is best approch for passing parameter and checking status of current execution.
E.g.
Delegate Function MyDelegate(ByVal text as String) as String
Function PrintStuff(ByVal text as String) as String
Console.WriteLine(text)
Thread.Sleep(5000)
Return "Success"
End Function
Sub Main()
Dim d as MyDelegate = AddressOf PrintStuff
d.BeginInvoke("hello world", Nothing, Nothing)
'I can do more stuff here
End Sub
Here we have a Main thread and a second thread to print some stuff. Also, a delegate with the same signature as the function we want to run (PrintStuff) already exists. Very simply, the Main thread creates a delegate that points to PrintsStuff and calls for the delegate to start executing PrintStuff on a separate thread. A parameter is passed to PrintStuff in the process and Main can continue working while PrintStuff is busy.
Now suppose you want to know when PrintStuff finishes because you want the result “Success”. We can do this by the use of a callback function.
First, we create the callback function
Sub MyCallback(ByVal result as IAsyncResult)
Console.WriteLine("Now I know PrintStuff finished")
End Sub
Then we make sure the callback function gets called by changing the previous BeginInvoke command to
d.BeginInvoke("hello world", New AsyncCallback(AddressOf MyCallback),Nothing)
Useful Query for SQL Server
Sql server where not in multiple columns
SELECT *
FROM Table1
WHERE not exists (SELECT 1 FROM Table2 where Table1.ClientNumber=Table2.ClientNumber and Table1.CaseNumber=Table2.CaseNumber)
Second highest salary
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Find Duplicate record from Table
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
SELECT *
FROM Table1
WHERE not exists (SELECT 1 FROM Table2 where Table1.ClientNumber=Table2.ClientNumber and Table1.CaseNumber=Table2.CaseNumber)
Second highest salary
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Find Duplicate record from Table
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
Genralize function for reading data from Excel
public void PopulateToExcel(IList list,int TabNum , int iRow)
{
try
{
//System.Threading.Thread.Sleep(30000);
worksheet = package.Workbook.Worksheets[TabNum];
Type elementType = typeof(T);
foreach (T item in list)
{
int iColumn = 1;
foreach (var propInfo in elementType.GetProperties())
{
worksheet.Cells[iRow, iColumn].Value = propInfo.GetValue(item, null) ?? DBNull.Value;
iColumn = iColumn + 1;
}
iRow = iRow + 1;
}
}
catch (Exception ex)
{
}
}
{
try
{
//System.Threading.Thread.Sleep(30000);
worksheet = package.Workbook.Worksheets[TabNum];
Type elementType = typeof(T);
foreach (T item in list)
{
int iColumn = 1;
foreach (var propInfo in elementType.GetProperties())
{
worksheet.Cells[iRow, iColumn].Value = propInfo.GetValue(item, null) ?? DBNull.Value;
iColumn = iColumn + 1;
}
iRow = iRow + 1;
}
}
catch (Exception ex)
{
}
}
Monday, May 9, 2011
Sharepoint Fundamental Questions
Q. Sharepoint 2010 New Features.
Ans.
1. Visual Web parts
2. 64 bit hardware require
3. Linq Query support using Microsoft.sharepoint.linq it translate linq query in CAML.
4. While creating a Look-up column, you can either choose a) Restrict Delete or b) Cascade Delete to define a relationship.
5. You can deliver alert as SMS.
Q. What is 14 hive in SharePoint?
Ans. See SharePoint 2010 Object Model
Q. How would you re-deploy the old custom solutions in SharePoint 2010.What Changes are needed to the old Solution files.
Ans. SharePoint 2010 object model contains many changes and enhancements, but our custom code will still compile and, will run as expected. You should however, rewrite and recompile any code that refers to files and resources in "12 hive".
For Details See :
See SharePoint 2010 Object Model - Backward Compatibility
Q. How many types of Client Object model extension are available in 2010 and when would you use one or the other.
Ans. To develop rich client side solutions, three set of client-side APIs has been introduced in the Microsoft.SharePoint.Client namespace. The three APIs are targeted for three different types of clients.
1. .net Managed applications – These are used when we have to create console applications or window applications, web applications which are not running inside SharePoint Contex.
2. For Silverlight applications
3. ECMAScript – It is a client object model extension for using with JavaScript or JScript. This is used for creating applications which are hosted inside SharePoint. For example, web part deployed in SharePoint site can use this JavaScript API for accessing SharePoint from browser using JavaScript.
Q. What are the security improvements in SharePoint 2010 ?
Ans. In SharePoint 2010 a variety of security methods have been introduced.
Claims-Based Authentication - Claims based authentication is based on identity. and trust.
Code Access Security - in which you can specify your own code access
security (CAS) policy for your web parts.
Sandbox Solutions - Sandbox Solutions which when deployed to the server, SharePoint runs in a special process that has limited permissions.
Cross-Site Scripting - Introduced to prevent Cross - Site Scripting (XSS) attacks.
Q. Whats New with SharePoint WebParts?
A developer can create two types of webparts using Visual Studio 2010.
1. Visual Webparts - Allows you to Drag and Drop the controls from the Toolbox to WebPart Design surface. You can of course write your custom code in the code file. You can also package and deploy your webparts directly to Sharepoint from VS by pressing Clt+F5. Visual studio 2010 also provides you with three different views for developing webparts. The views are split view, design view and Source view(as we have in designer 2007).
Note : The Visual Webpart project Item basically loads a User Control as a WebPart.
2. ASP.Net WebParts - Where a developer can build up User Interface and logic in a class file. You do not have designer for drag and drop of controls. This webpart inherits from standard ASP.Net webpart. For Deployment we can again use ctrl+f5 to deploy this webpart.
Q. What are the Visual Studio 2010 Tools for SharePoint.
Ans. Visual Studio 2010 includes SharePoint-specific project types and project item types, and includes powerful packaging, deployment, and debugging features that help increase your efficiency as a SharePoint 2010 developer.
Some of the Templates avaiable are :
1.Visual Web Part project template.
2. List defination template.
3. Content Type template.
4. Empty Project template.
5. Event Receiver template.
6. some workflow template.
7. the Site Definition template
and many more....
Q. What are SharePoint Sandboxed soultions ?
Ans. SharePoint 2010 provides a new sandboxed environment that enables you to run user solutions without affecting the rest of the SharePoint farm. This environment means that users can upload their own custom solutions without requiring intervention from administrators, and without putting the rest of the farm at risk. This means that the existing sites\pages or components will not be effected by the newly added soultion.
Users can deploy the below four things as sandboxed soultions :
1. WebParts.
2. Event Receivers.
3. List Definations.
4. Workflows.
Q. What are Requirenments for SharePoint 2010.
Ans. SharePoint Server 2010 will support only 64 - bit. It will require 64 bit Windows Server 2008 or 64 bit Windows Server 2008 R2. In addition to this, it will require 64 bit version of SQL Server 2008 or 64-bit version of SQL Server 2005.
Q. What is LINQ. How is it used in Sharepoint ?
Ans. LINQ is a feature of the programming languages C# 3.0 and Visual Basic .NET. LINQ allows you to query in an object-oriented way, supports compile-time check, gives you intellisense support in Visual Studio and defines a unified, SQL like syntax to query any data source. But unlike other languages and query syntaxes which vary from one type of data source to another, LINQ can be used to query, in principle, any data source whatsoever. It is commonly used to query objects collections, XML and SQL server data sources.
The LINQ to SharePoint Provider is defined in theMicrosoft.SharePoint.Linq namespace. It translates LINQ queries into Collaborative Application Markup Language (CAML) queries.
Q. What Changes are made in SharePoint 2010 to enforce Referential Integrity?
Ans. In SharePoint 2010, Referential Integrity is enforced using two options, available with Look-up columns.
While creating a Look-up column, you can either choose a) Restrict Delete or b) Cascade Delete to define a relationship between the Look-up list and the list containing the look-up Column. Read Details at SharePoint 2010 Referential integrity - Using LookUp Column
Q. Whats Ribbon in SharePoint 2010?
Ans. See the Post Ribbon in SharePoint 2010
Q . Whats New in SPALerts ?
Ans. In SharePoint 2007, alerts were send only through e-mails, but in SP2010 users can also send an alert to mobile devices as SMS Message. A New property DeliveryChannels is introduced to indicate, whether the alert is delivered as E-mail or as an SMS Message.
Q. What Has Changed with SSP in SharePoint 2010.
Ans. In SharePoint 2010 Shared Service Providers (SSP's) are replaced by Service Applications. Services are no longer combined into a SSP. They are running independent as a service application. The service application architecture is now also built into Microsoft SharePoint Foundation 2010, in contrast to the Shared Services Provider (SSP) architecture that was only part of Office SharePoint Server 2007.
A key benefit here is that all services are installed by default and there is no SSP setup.
Q. What Do you know about SharePoint Object Model?
Ans. In Sharepoint Object model there are two Important namespaces.
The Microsoft.Office.Server namespace is the root namespace of all Office Server objects and Microsoft.SharePoint is the root namespace for all WSS objects.
Read More at SharePoint 2007 Object Model
Q. Can you develop webparts and other SharePoint solutions at your local machine?
Ans. In order to run and debug sharePoint solutions, the project must reside on the server which has Windows sharePoint services installed. However, you can reference theMicrosoft.SharePoint dll in your project at your local, but you won’t be able to run it.
Q. How do you debug SharePoint Webparts?
Ans. To debug SharePoint webpart (or any solution) you can simply drag and drop your complied .dll in GAC and recycle the app pool. You can also run upgrade solution command from stsadm.
Q. How would you retrieve large number of Items from the list ?
Ans. To retrieve large number of items with a better performance we can either use SPQuery or PortalSiteMapProvider Class. Read More with Examples
Retrieving large number of Items from sharepoint list
Q. How Do you implement Impersonation in ShrePoint.
Ans. By Using RunWithElevatedPrivileges method provided by SPSecurity class.
See e.g Impersonation in Sharepoint
Q. Can we use our custom master page with the application pages in SharePoint 2010 ?
Ans. With 2010, you can now set whether the pages under _Layouts use the same Master Page as the rest of your site. You can enable or disable this functionality through the web application settings in Central Administration. This however, is not applicable to your custom application pages. If you want your custom applictaion page to inherit the site master page you must derive it from Microsoft.SharePoint.WebControls.LayoutsPageBase class.
Q. What does CMDUI.XML contain?
Ans. The definitions for the out-of-the-box ribbon elements are split across several files in the SharePoint root, with TEMPLATE\GLOBAL\XML\CMDUI.XML being the main one.
Q. Why would you use LINQ over CAML for data retrieval?
Ans. Unlike CAML, with LINQ to SharePoint provider, you are working with strongly typed list item objects. For example, an item in the Announcements list is an object of type Announcement and an item on a Tasks list is an object of type Task. You can then enumerate the objects and get the properties for your use. Also, you can use LINQ syntax and the LINQ keywords built into C# and VB for LINQ queries.
Q. How do you write to SharePoint ULS logs in 2010 ?
Ans. In SharePoint Foundation, ULS exposes configurable settings in two ways, through the – Object model and Windows PowerShell cmdlets. For writing to SharePoint ULS logs developers can can use Diagnostics Service, which will make the customized categories viewable in the administrative UI for our errors.
or they can use the number of cmdlets available for accessing ULS logs using powershell. Some of the cmdlets are Get-SPDiagnosticConfig ,Get-SPLogEvent etc.
Q. How does Client object model works ?
Ans. When we use SharePoint client API’s to perform a specific task, the SharePoint Foundation 2010 managed client object model bundles up these uses of the API into XML and sends it to the server that runs SharePoint Foundation. The server receives this request, and makes appropriate calls into the object model on the server, collects the responses, forms them into JavaScript Object Notation (JSON), and sends that JSON back to the SharePoint Foundation 2010 managed client object model. The client object model parses the JSON and presents the results to the application as .NET Framework objects (or ECMAScript objects for ECMAScript).
Q. What is difference between Load() and LoadQuery() methods ?
Ans. Load method populates the client object directly with what it gets data from the server i.e. a collection object like ListItemCollection etc. but LoadQuery returns the data as a completely new collection in IEnumerable format. Other major difference is that the Collections that you load using the Load() method are eligible for garbage collection only when the client context variable itself goes out of scope where as, in these collections go out of scope at the end of IEnumerable list.
Q. How do you access ECMAScript object model API’s ?
Ans. The ECMAScript library is available in a number of JS files in the LAYOUTS folder. The main file among number of .js files is SP.js. When you include this file in the APSX page using a ScriptLink control, all other required JS files are loaded automatically. By linking SP.js to your page, the SP namespace gets registered. SP is the SharePoint namespace that contains all objects. For debugging purposes every js file also has a ‘debug’ equivalent in the same folder.
Q. What is the purpose of calling clientContext.ExecuteQuery() ?
Ans. ExecuteQuery gives you the option to minimize the number of roundtrips to the server from your client code. All the components loaded into the clientcontext are executed in one go.
Q: What is the performance impact of RunWithElevatedPrivileges?
Ans. RunWithElevatedPrivileges creates a new thread with the App Pool's credentials, blocking your current thread until it finishes.
Q. How will you add Code behind to a Custom Applictaion Page or a Layout Page in SharePoint?
Ans. You do not deploy a code behind file with your custom Layouts page. Instead, you can have the page inherit from the complied dll of the solution to access the code behind.
Q. What is the difference between a Site Definition and a Site Template?
Ans. Site Definitions are stored on the hard drive of the SharePoint front end servers. They are used by the SharePoint application to generate the sites users can create. Site Templates are created by users as a copy of a site they have configured and modified so that they do not have to recreate lists, libraries, views and columns every time they need a new instance of a site.
Q. Why do you use Feature Receivers?
Ans. Feature Receivers are used to execute any code on Activation\Deactivation of a Feature. You can use it for various purposes.
Q. Can you give a example where feature receivers are used.
Ans. You can use it to assign an event receiver feature to a specific type of list or can write a code in a feature receivers Deactivate method to remove a webpart from webpart gallery.
Q. Where do you deploy the additional files used in your webpart, like css or javascript files, and how do you use them in your WebPart?
Ans. You can deploy the css or javascript files in _layouts folder in SharePoint's 12 hive. To use them in your webpart, you need to first register them to your webpart page and then specify a virtual path for the file for e.g. _layouts\MyCSS.css See Code examples at Using External Javascript, CSS or Image File in a WebPart.
Q: When should you dispose SPWeb and SPSite objects?
Ans. According to the best Practices you should always dispose them if you have created them in your code. You can dispose them in Finally block or you can use the "Using" clause, so that they gets disposed when not required. If you are using SPContext then you need not dispose the spsite or spweb objects.
Q. What are the best practices for SharePoint development.
Ans. Some of the best practices are:
1. You should always dispose SPsite and SPWeb objects, once you refer them in your code. Using the "Using" clause is recommended.
2. Use RunwithelevatePrivilages to avoid errors for end users.
3. Try writing your errors to SharePoint error logs (ULS Logs). Since it’s a bad idea to fill-up event log for your production environment.
4. Use SPQuery instead of foreach loop while retrieving Items from the list.
5. Deploy additional files used in your webpart to 12 hive. Use your solution package to drop the files in 12 hive. Also, make sure that all the references (for e.g. Css or .js files) get removed when the solution is retracted.
Also See : Best Practices to Improve Site Performance
Q.What is the main difference between using SPListItem.Update() and SPListItem.SystemUpdate()?
Ans. Using SystemUpdate() will not create a new version and will also retain timestamps.
Q. When do you use SPSiteDataQuery ?
Ans. You can use SPSiteDataQuery when you need to extract data from more than one list\library in your site colletcion. The data is extracted on the basis of the query you write and is
returened as a Datatable. You can also specify the GUID for the lists\libraries you want to query against.
Q. How do you create a Custom action for an item in a list ?
Ans. This can be done by adding a new feature into SharePoint. You would need to use customaction tag in your elements.xml file and will have to set various properties like imageurl or UrlAction for your customaction. You can later add this feature into sharepoint using stsadm install feature command.
Q. How would you bind this CustomAction to a specific list ?
Ans. To do this you can either create a new list type(again a feature) and use the list type number for the new list in your Registration Type property of the Custom action. The Custom Action will then show up only for the items of this list type. or You can create a new content type and then use that content type's id in your cutsomaction to bind the custom action to items of just that content type. Add the new content type to the list where you need this customaction.
Q. How will you deploy an existing asp.net webapplication or website in SharePoint?
Ans. You would need to wrap the web application in a solution package in order to deploy it in 12 hive or say ShraePoint. It is recommended to create a feature first, and then wrap everything in a Solution package. See exampleDepoly a Custom aspx Page in SharePoint
Q. How will you cancel a deployment from central admin -> solution managment, if its stuck at “deploying” or “Error”.
Ans. You can either try to force execute timer jobs using execadmsvcjobs command or can cancel the dpeloyment using stsadm command stsadm –o cancaldeployment –id {GUID} command. The Id here would be GUID of the timer or deployment job. You can get the Id from stsadm enumdeployment command. This will display all the deployments which are process or are stuck with Error.
Q. How do make an existing non-publishing site Publishing?
Ans. You can simply activate the SharePoint Publishing Feature for the Site, you want to make publishing.
Q. Can you name some of the tools used for SharePoint Administration?
Ans. Tools Used for SharePoint Administration
Q. What are Application Pages in SharePoint?
Ans. Unlike site pages (for example, default.aspx), a custom application page is deployed once per Web server and cannot be customized on a site-by-site basis. Application pages are based in the virtual _layouts directory. In addition, they are compiled into a single assembly DLL.
A good example of an Application Page is the default Site Settings page: every site has one, and it's not customizable on a per site basis (although the contents can be different for sites).
With application pages, you can also add inline code. With site pages, you cannot add inline code.
Q. What is Authentication and Authorization?
Ans . An authentication system is how you identify yourself to the computer. The goal behind an authentication system is to verify that the user is actually who they say they are.
Once the system knows who the user is through authentication, authorization is how the system decides what the user can do.
Q. How do you deploy a User Control in SharePoint ?
Ans. You deploy your User Control either by a Custom webpart, which will simply load the control on the page or can use tools like SmartPart, which is again a webpart to load user control on the page. User Control can be deployed using a custom solution package for the webapplication or you can also the control in the webpart solution package so that it gets deployed in _controlstemplate folder.
Q. Which is faster a WebPart or a User Control?
Ans. A WebPart renders faster than a User Control. A User Control in SharePoint is usually loaded by a webpart which adds an overhead. User Controls however, gives you an Interface to add controls and styles.
Q. What SharePoint Databases are Created during the standard Install?
Ans. During standard install, the following databases are created :
SharePoint_AdminContent
SharePoint_Config
WWS_Search_SERVERNAME%_%GUID_3%
SharedServicesContent_%GUID_4%
SharedServices1_DB_%GUID_5%
SharedServices1_Search_DB_%
GUID_6%WSS_Content_%GUID_7%
Q. What are content types?
Ans. A content type is a flexible and reusable WSS type definition (or we can a template) that defines the columns and behavior for an item in a list or a document in a document library. For example, you can create a content type for a leave approval document with a unique set of columns, an event handler, and its own document template and attach it with a document library/libraries.
Q. Can a content type have receivers associated with it?
Ans. Yes, a content type can have an event receiver associated with it, either inheriting from the SPListEventReciever base class for list level events, or inheriting from the SPItemEventReciever base class. Whenever the content type is instantiated, it will be subject to the event receivers that are associated with it.
Q. Can you add a Cutsom Http Handler in SharePoint ?
Ans. Yes, a Custom httphandler can be deployed in _layouts folder in SharePoint. Also, we need to be register the handler in the webapp's webconfig file.
Q. While creating a Web part, which is the ideal location to Initialize my new controls?
Override the CreateChildControls method to include your new controls. You can control the exact rendering of your controls by calling the .Render method in the web parts Render method.
Q. How do you return SharePoint List items using SharePoint web services?
Ans.
In order to retrieve list items from a SharePoint list through Web Services, you should use the lists.asmx web service by establishing a web reference in Visual Studio. The lists.asmx exposes the GetListItems method, which will allow the return of the full content of the list in an XML node. It will take parameters like the GUID of the name of the list you are querying against, the GUID of the view you are going to query, etc.
Q. How Do you deploy Files in 12 hive when using wspbuilder or vsewss?
Ans. Typically, you can add these files in the 12 hive folder structure in your project. In Vsewss however, you will have to create this structure manually.
Q. What files gets created on a file system, when a Site collection is created ?
Ans. Windows SharePoint Services does not create any files or folders on the file system when the site collection or sites are created; everything is created in the content database. The Pages for the site collection are created as instances in the content database. These instances refer to the actual file on the file system.
Q. What are Customized and Uncustomized Files in SharePoint ?
Ans. There are two types of Pages in SharePoint; site pages (also known as content pages) and application pages.
Uncustomized :
When you create a new SharePoint site in a site collection, Windows SharePoint Services provisions instances of files into the content database that resides on the file system. That means if you create a new Site "xyz" of type Team Site(orTeam sIte Definition), an instance of the Team Site Definition( Which resides on the File System), i.e. "xyz" gets created in the Content database. So, When ASP.NET receives a request for the file, it first finds the file in the content database. This entry in the content database tells ASP.NET that the file is actually based on a file on the file system and therefore, ASP.NET retrieves the source of the file on the file system when it constructs the page.
Customized :
A customized file is one in which the source of the file lives exclusively in the site collection's content database. This happens When you modify the file in any way through the SharePoint API, or by SharePoint Designer 2007,which uses the SharePoint API via RPC and Web service calls to change files in sites. So, When the file is requested, ASP.NET first finds the file in the content database. The entry in the database tells ASP.NET whether the file is customized or uncustomized. If it is customized, it contains the source of the file, which is used by ASP.NET in the page contraction phase.
Q. What are event receivers?
Ans. Event receivers are classes that inherit from the SpItemEventReciever or SPListEventReciever base class (both of which derive out of the abstract base class SPEventRecieverBase), and provide the option of responding to events as they occur within SharePoint, such as adding an item or deleting an item.
Q. When would you use an event receiver?
Ans. Since event receivers respond to events, you could use a receiver for something as simple as canceling an action, such as deleting a document library by using the Cancel property. This would essentially prevent users from deleting any documents if you wanted to maintain retention of stored data.
Q. If I wanted to restrict the deletion of the documents from a document library, how would I go about it?
Ans. You would create a event receiver for that list/library and implement the ItemDeleting method. Simply, set: properties.Cancel= true and display a friendly message using Properties.Message("How can u delete this... Its not your stuff!");
Q. What is the difference between an asynchronous and synchronous event receivers?
Ans. An asynchronous event occurs after an action has taken place, and a synchronous event occurs before an action has take place. For example, an asynchronous event is ItemAdded, and its sister synchronous event is ItemAdding
Q. How do you Increase trust level for a single WebPart in the WebConfig file.
Ans. To list a Web Part with Full Permissions within your Web Application while still retaining a WSS_Minimal permission set for all other Web Parts, You need to create a Custom policy file. This file will be then referenced in SharePoint Web.config file and will allow your specific webpart to be of Full trust.
Steps :
1. Make a copy of the WSS_Minimal.Config file from the 12\Config folder and paste it into the same folder renaming it to Custom_WSS_Minimal.Config. Now, edit the Custom_WSS_Minimal.Config file using NotePad. Obtain the Public Key Token for the Web Part assembly that you want to deploy, using the following command: sn –Tp filename.dll. Create a new entry in your Custom_WSS_Minimal.Config file for your WebPart. Save the File.
Finally, Create a new TrustLevel element for your config file in the Web.Config called Custom_WSS_Minimal that points to your custom file in the 12\config folder. Recycle the Application Pool and You’re Done.
Q. How does Windows SharePoint Services help render the Webapplictaion in ShrePoint?
Ans. When a new web applictaion is created via Central Admin, Windows SharePoint Services creates a new Web application in IIS. Then the WSS, loads the custom HTTP application and replaces all installed HTTP handlers and modules with Windows SharePoint Services–specific ones. These handlers and modules essentially tell IIS to route all file requests through the ASP.NET 2.0 pipeline. This is because most files in a SharePoint site are stored in a Microsoft SQL Server database.
Q. How would you pass user credentials while using SharePoint WebService from your Web Part or application.
Ans. The web service needs credentials to be set before making calls.
Examples:
listService.UseDefaultCredentials = true; // use currently logged on user
listService.Credentials = new System.Net.NetworkCredential("user", "pass", "domain"); // use specified user
Q. How would you remove a webapart from the WebPart gallery? Does it get removed with Webpart retraction?
Ans. No, Webpart does not get removed from the WebPart gallery on retraction. You can write a feature receiver on Featuredeactivating method to remove the empty webpart from the gallery.
Q. What is a SharePoint Feature? Features are installed at what scope
Ans. A SharePoint Feature is a functional component that can be activated and deactivate at various scopes throughout a SharePoint instances, scope of which are defined as
1. Farm level 2. Web Application level 3. Site level 4. Web level
Features have their own receiver architecture, which allow you to trap events such as when a feature is Installing, Uninstalling, Activated, or Deactivated.
Q. What type of components can be created or deployed as a feature?
Ans. We can create menu commands, Custom Actions,page templates, page instances, list definitions, list instances,event handlers,webparts and workflows as feature.
Q. How Do you bind a Drop-Down Listbox with a Column in SharePoint List ?
Ans.
Method 1 : You can get a datatable for all items in the list and add that table to a data set. Finally, specify the dataset table as datasource for dropdown listbox.
Method 2 : You can also use SPDatasource in your aspx or design page.
See Code example Binding Drop-Down with Sharepoint List data
Q. How Does SharePoint work?
Ans. The browser sends a DAV packet to IIS asking to perform a document check in. PKMDASL.DLL, an ISAPI DLL, parses the packet and sees that it has the proprietary INVOKE command. Because of the existence of this command, the packet is passed off to msdmserv.exe, who in turn processes the packet and uses EXOLEDB to access the WSS, perform the operation and send the results back to the user in the form of XML.
Q. What is CAML?
Ans. CAML stands for Collaborative Application Markup Language and is an XML-based languagethat is used inMicrosoft Windows SharePoint Services to define sites and lists, including, for Eg, fields, views, or forms, but CAML is also used to define tables in the Windows SharePoint Servies database during site provisioning. Developers mostly use CAML Queries to retrieve data from Lists\libraries.
Q. Can you display\add a Custom aspx or WebApplication Page in SharePoint Context ?
Ans. You need to make some modification in the aspx file to display it in SharePoint Context. Firstly, add the references for various sharepoint assemblies on the Page. Then wrap the Code in PlaceHolderMain contentPlaceholder, so that it gets displayed as a content page. Lastly, add a reference to SharePoint Master Page in aspx file and swicth it in Code behind if needed. See Code Example at Display aspx Page in SharePoint context
1) What are the two base classes a WebPart you are going to use within SharePoint 2007 can inherit from?
There are two base classes that a WebPart which is going to be consumed by SharePoint can inherit from, either the SharePoint WebPart Base class or the ASP.NET 2.0 WebPart base class. When inheriting from the SharePoint WebPart Base class your derived WebPart class will inherit from Microsoft.SharePoint.WebPartPages.WebPart. When inheriting from the ASP.NET 2.0 WebPart base class your derived WebPart class will inherit from System.Web.UI.WebControls.WebParts.WebPart. It is considered good practice to use the ASP.NET WebPart base class since the old base class is meant for backwards compatibility with previous version of SharePoint, however there are four exception when it is better to leverage functionality from the SharePoint WebPart base class:
Cross page connections
Connections between Web Parts that are outside of a Web Part zone
Client-side connections (Web Part Page Services Component)
Data caching infrastructure
2) What are the differences between the two base classes and what are the inherit benefits of using one over another?
The difference is the Microsoft.SharePoint.WebPartPages.WebPart base class is meant for backward compatibility with previous versions of SharePoint. The benefit of using the SharePoint WebPart base class is it supported:
Cross page connections
Connections between Web Parts that are outside of a Web Part zone
Client-side connections (Web Part Page Services Component)
Data caching infrastructure
ASP.NET 2.0 WebParts are generally considered better to use because SharePoint is built upon the ASP.NET 2.0 web architecture. Inheriting from the ASP.NET 2.0 base class offers you features that inherit to ASP.NET 2.0, such as embedding resources as opposed to use ClassResources for deployment of said types.
3) What is the GAC?
The GAC stands for the global assembly cache. It is the machine wide code cache which will give custom binaries place into the full trust code group for SharePoint. Certain SharePoint assets, such as Feature Receivers need full trust to run correctly, and therefore are put into the GAC. You should always try to avoid deployment to the GAC as much as possible since it will possibly allow development code to do more than it was intended to do.
4) What is strong naming (signing) a WebPart assembly file mean?
Signing an assembly with a strong name (a.k.a strong naming) uses a cryptographic key pair that gives a unique identity to a component that is being built. This identity can then be referred throughout the rest of the environment. In order to install assemblies into the GAC, they must be strongly named. After signing, the binary will have a public key token identifier which can be use to register the component in various other places on the server.
5) What are safe controls, and what type of information, is placed in that element in a SharePoint web.config file?
When you deploy a WebPart to SharePoint, you must first make it as a safe control to use within SharePoint in the web.config file. Entries made in the safe controls element of SharePoint are encountered by the SharePointHandler object and will be loaded in the SharePoint environment properly, those not will not be loaded and will throw an error.
In the generic safe control entry (this is general, there could be more), there is generally the Assembly name, the namespace, the public key token numeric, the typename, and the safe declaration (whether it is safe or not). There are other optional elements.
6) What is the CreateChildControls() method? How can you use it to do something simple like displaying a Label control?
The CreateChildControls method in WebParts is used to notify the WebPart that there are children controls that should be output for rendering. Basically, it will add any child ASP.NET controls that are called instantiating each control with its relevant properties set, wire any relevant event handlers to the control, etc. Then the add method of the control class will add the control to the controls collection. In the relevant WebPart render method, the EnsureChildControls method can be called (or set to false if no child controls should be called) to ensure that the CreateChildControls method is run. When using CreateChildControls it implies that your WebPart contains a composition of child controls.
In order to create something like a label control in Create, you would create a new label control using the new keyword, set the various properties of the control like Visible=True and ForeColor = Color.Red, and then use Controls.Add(myLabelControl) to add the control to the controls collection. Then you can declare EnsureChildControls in the Render method of the WebPart.
7) What does the RenderContents method do in an ASP.NET 2.0 WebPart?
The render contents method will render the WebPart content to the writer, usually an HtmlTextWriter since WebParts will output to an HTML stream. RenderContents is used to tell how the controls that are going to be displayed in the WebPart should be rendered on the page.
*** Side Question: I got asked what the difference between CreateChildControls and the RenderContents method. The CreateChildControls method is used to add controls to the WebPart, and the RenderContents method is used to tell the page framework how to render the control into HTML to display on a page.
8) What is the WebPartManager sealed class? What is its purpose?
The WebPartManager sealed class is responsible for managing everything occurring on a WebPart page, such as the WebParts (controls), events, and misc. functionality that will occur in WebPartZones. For example, the WebPartManager is responsible for the functionality that is provided when you are working with moving a WebPart from WebPartZone to WebPartZone. It is known as the “the central class of the Web Part Control Set.”
*** Side Question: I got asked how many WebPartManager controls should be on a page. In order to have WebParts on a page there has to be just one WebPartManager control to manage all the WebParts on the page.
9) What is a SPSite and SPWeb object, and what is the difference between each of the objects?
The SPSite object represents a collection of sites (site collection [a top level sites and all its subsites]). The SPWeb object represents an instance SharePoint Web, and SPWeb object contains things like the actual content. A SPSite object contains the various subsites and the information regarding them.
10) How would you go about getting a reference to a site?
PLAIN TEXT
C#:
1. oSPSite = new SPSite("http:/server");
2.
3. oSPWeb = oSPSite.OpenWeb();
11) What does a SPWebApplication object represent?
The SPWebApplication objects represents a SharePoint Web Application, which essentially is an IIS virtual server. Using the class you can instigate high level operations, such as getting all the features of an entire Web Application instance, or doing high level creation operations like creating new Web Applications through code.
12) Would you use SPWebApplication to get information like the SMTP address of the SharePoint site?
Yes, since this is a Web Application level setting. You would iterate through each SPWebApplication in the SPWebApplication collection, and then use the appropriate property calls (OutboundMailServiceInstance) in order to return settings regarding the mail service such as the SMTP address.
Side Question: I got asked if there are other ways to send emails from SharePoint. The answer is yes, there is. You can use the SendMail method from the SPutility class to send simple emails, however it is not as robust as using the System.Net.Mail functionality since it doesn’t allow things like setting priorities on the email.
13) How do you connect (reference) to a SharePoint list, and how do you insert a new List Item?
PLAIN TEXT
C#:
1. using(SPSite mySite = new SPSite("yourserver"))
2. {
3. using(SPWeb myWeb = mySite.OpenWeb())
4. {
5. SPList interviewList = myWeb.Lists["listtoinsert"];
6. SPListItem newItem = interviewList.Items.Add();
7.
8. newItem["interview"] = "interview";
9. newItem.Update();
10. }
11. }
14) How would you loop using SPList through all SharePont List items, assuming you know the name (in a string value) of the list you want to iterate through, and already have all the site code written?
PLAIN TEXT
C#:
1. SPList interviewList = myWeb.Lists["listtoiterate"];
2. foreach (SPListItem interview in interviewList)
3. {
4. // Do Something
5. }
15) How do you return SharePoint List items using SharePoint web services?
In order to retrieve list items from a SharePoint list through Web Services, you should use the lists.asmx web service by establishing a web reference in Visual Studio. The lists.asmx exposes the GetListItems method, which will allow the return of the full content of the list in an XML node. It will take parameters like the GUID of the name of the list you are querying against, the GUID of the view you are going to query, etc.
Side Question: I got asked how I built queries with the lists.asmx web service. In order to build queries with this service, one of the parameters that the GetListItems method exposes is the option to build a CAML query. There are other ways to do this as well, but that was how I answered it.
16) When retrieving List items using SharePoint Web Services, how do you specify explicit credentials to be passed to access the list items?
In order to specify explicit credentials with a Web Service, you generally instantiate the web service, and then using the credentials properties of the Web Service object you use the System.Net.NetworkCredential class to specify the username, password, and domain that you wish to pass when making the web service call and operations.
*** Side Question: I got asked when you should state the credentials in code. You must state the credentials you are going to pass to the web service before you call any of the methods of the web service, otherwise the call will fail.
17) What is CAML, and why would you use it?
CAML stands for Collaborative Application Markup Language. CAML is an XML based language which provides data constructs that build up the SharePoint fields, view, and is used for table definition during site provisioning. CAML is responsible for rending data and the resulting HTML that is output to the user in SharePoint. CAML can be used for a variety of circumstances, overall is used to query, build and customize SharePoint based sites. A general use would be building a CAML query in a SharePoint WebPart in order to retrieve values from a SharePoint list.
18) What is impersonation, and when would you use impersonation?
Impersonation can basically provide the functionality of executing something in the context of a different identity, for example assigning an account to users with anonymous access. You would use impersonation in order to access resources on behalf of the user with a different account, that normally, that wouldn’t be able to access or execute something.
19) What is the IDesignTimeHtmlProvider interface, and when can you use it in WebParts?
The IDesignTimeHtmlProvider interface uses the function GetDesignTimeHtml() which can contain your relevant render methods. It was helpful to use in 2003 since it allowed your WebPart to have a preview while a page was edited in FrontPage with the Webpart on it, because the GetDesignTimeHtml() method contains the HTML for the designer to render.
20) What are WebPart properties, and what are some of the attributes you see when declaring WebPart properties in code?
WebPart properties are just like ASP.NET control properties, they are used to interact with and specify attributes that should be applied to a WebPart by a user. Some of the attributes you see with ASP.NET 2.0 properties are WebDescription, WebDisplayName, Category, Personalizable, and WebBrowsable. Although most of these properties come from the System.Web.UI.WebControls.WebParts class, ones like Category come out of System.ComponentModel namespace.
21) Why are properties important in WebPart development, and how have you exploited them in past development projects? What must each custom property have?
Properties are important because WebParts allow levels of personalization for each user. WebPart properties make it possible for a user to interact, adjust, and increase overall experience value with the programmatic assets that you develop without having the need to use an external editor or right any code. A very simple example of exploiting a property would be something like allowing the user to change the text on the WebPart design interface so that they can display whatever string of text they desire.
Each custom property that you have must have the appropriate get and set accessor methods.
22) What are ClassResources? How do you reference and deploy resources with an ASP.NET 2.0 WebPart?
ClassResources are used when inheriting from the SharePoint.WebPart.WebPartPages.WebPart base class, and are defined in the SharePoint solution file as things that should be stored in the wpresources directory on the server. It is a helpful directory to use in order to deploy custom images. In ASP.NET 2.0, typically things such as images are referenced by embedding them as resources within an assembly. The good part about ClassResources is they can help to eliminate recompiles to change small interface adjustments or alterations to external JavaScript files.
23) What is a SharePoint Solution File? How does it differ from WebPart .cab files in legacy development? What does it contain?
A SharePoint solution file is essentially a .cabinet file with all a developers ustom componets suffixed with a .wsp extension that aids in deployment. The big difference with SharePoint solution files is is that a solution:
allows deployment to all WFE’s in a farm
is highly manageable from the interface allowing deployment, retraction, and versioning
Can package all types of assets like site definitions, feature definitions (and associated components), Webparts, etc.
Can provide Code Access Security provisioning to avoid GAC deployments
Just to name a few things…
24) What is a .ddf file and what does it have to do with SharePoint Solution creation?
A .ddf file is a data directive file and is used when building the SharePoint solution bundle specifying the source files and their destination locations. The important thing for someone to understand is that the .ddf file will be passed as a parameter to the MAKECAB utility to orchestrate construction of the SharePoint solution fiel.
25) What file does a SharePoint solution package use to orchestrate (describe) its packaged contents?
The solution Manifest.XML file.
26) What deployment mechanism can you use to instigate Code Access Security attributes for your WebParts?
SharePoint solution files can add in order to handle code access security deployment issues. This is done in the element in the SharePoint solution manifest.XML, which makes it easier to get assemblies the appropriate permissions in order to operate in the bin directory of the web application.
27) What is a SharePoint Feature? What files are used to define a feature?
A SharePoint Feature is a functional component that can be activated and deactivate at various scopes throughout a SharePoint instances, such as at the farm, site collection, web, etc. Features have their own receiver architecture, which allow you to trap events such as when a feature is installing, uninstalling, activated, or deactivated. They are helpful because they allow ease of upgrades and versioning.
The two files that are used to define a feature are the feature.xml and manifest file. The feature XML file defines the actual feature and will make SharePoint aware of the installed feature. The manifest file contains details about the feature such as functionality.
Side Question: I got asked how the introduction of features has changed the concept of site definitions. SharePoint features are important when understanding the architecture of site definitions, since the ONET.XML file has been vastly truncated since it has several feature stapled on it.
28) What types of SharePoint assets can be deployed with a SharePoint feature?
Features can do a lot. For example, you could deploy
Simple site customizations
Custom site navigation
WebParts
pages
list types
list instances
event handlers
workflows
custom actions
just to name a few….
29) What are event receivers?
Event receivers are classes that inherit from the SpItemEventReciever or SPListEventReciever base class (both of which derive out of the abstract base class SPEventRecieverBase), and provide the option of responding to events as they occur within SharePoint, such as adding an item or deleting an item.
30) When would you use an event receiver?
Since event receivers respond to events, you could use a receiver for something as simple as canceling an action, such as deleting a document library by using the Cancel property. This would essentially prevent users from deleting any documents if you wanted to maintain retention of stored data.
31) What base class do event receivers inherit from?
Event receivers either inherit from the SPListEventReciever base class or the SPItemEventReciever base class, both which derive from the abstract base class SPEventReceiverBase.
32) If I wanted to not allow people to delete documents from a document library, how would I go about it?
You would on the ItemDeleting event set: properties.Cancel= true.
33) What is the difference between an asynchronous and synchronous event receivers?
An asynchronous event occurs after an action has taken place, and a synchronous event occurs before an action has take place. For example, an asynchronous event is ItemAdded, and its sister synchronous event is ItemAdding.
34) How could you append a string to the title of a site when it is provisioned?
In the OnActivated event:
PLAIN TEXT
C#:
1. SPWeb site = siteCollection.RootWeb;
2. site.Title += "interview";
3. site.Update();
35) Can an event receiver be deployed through a SharePoint feature?
Yes.
36) What is a content type?
A content type is an information blueprint basically that can be re-used throughout a SharePoint environment for defining things like metadata and associated behaviors. It is basically an extension of a SharePoint list, however makes it portable for use throughout an instance regardless of where the instantiation occurs, ergo has location independence. Multiple content types can exist in one document library assuming that the appropriate document library settings are enabled. The content type will contain things like the metadata, listform pages, workflows, templates (if a document content type), and associated custom written functionality.
37) Can a content type have receivers associated with it?
Yes, a content type can have an event receiver associated with it, either inheriting from the SPListEventReciever base class for list level events, or inheriting from the SPItemEventReciever base class. Whenever the content type is instantiated, it will be subject to the event receivers that are associated with it.
38) What two files are typically (this is kept generally) included when developing a content type, and what is the purpose of each?
There is generally the main content type file that holds things like the content type ID, name, group, description, and version. There is also the ContentType.Fields file which contains the fields to include in the content type that has the ID, Type, Name, DisplayName, StaticName, Hidden, Required, and Sealed elements. They are related by the FieldRefs element in the main content type file.
39) What is an ancestral type and what does it have to do with content types?
An ancestral type is the base type that the content type is deriving from, such as Document (0x0101). The ancestral type will define the metadata fields that are included with the custom content type.
40) Can a list definition be derived from a custom content type?
Yes, a list definition can derive from a content type which can be seen in the schema.XML of the list definition in the element.
41) When creating a list definition, how can you create an instance of the list?
You can create a new instance of a list by creating an instance.XML file.
42) What is a Field Control?
Field controls are simple ASP.NET 2.0 server controls that provide the basic field functionality of SharePoint. They provide basic general functionality such as displaying or editing list data as it appears on SharePoint list pages.
43) What base class do custom Field Controls inherit from?
This varies. Generally, custom field controls inherit from the Microsoft.SharePoint.WebControls.BaseFieldControl namespace, but you can inherit from the default field controls.
44) What is a SharePoint site definition? What is ghosted (uncustomized) and unghosted (customized)?
SharePoint site definitions are the core set of functionality from which SharePoint site are built from, building from the SiteTemplates directory in the SharePoint 12 hive. Site definitions allow several sites to inherit from a core set of files on the file system, although appear to have unique pages, thereby increasing performance and allowing changes that happen to a site propagate to all sites that inherit from a site definition. Ghosted means that when SharePoint creates a new site it will reference the files in the related site definition upon site provisioning. Unghosted means that the site has been edited with an external editor, and therefore the customizations are instead stored in the database, breaking the inheritance of those files from the file system.
45) How does one deploy new SharePoint site definitions so that they are made aware to the SharePoint system?
The best way to deploy site definitions in the SharePoint 2007 framework is to use a SharePoint solution file, so that the new site definition is automatically populated to all WFE’s in the SharePoint farm.
http://www.aired.in/2011/03/sharepoint-2007-moss-2007-developer.html
Ans.
1. Visual Web parts
2. 64 bit hardware require
3. Linq Query support using Microsoft.sharepoint.linq it translate linq query in CAML.
4. While creating a Look-up column, you can either choose a) Restrict Delete or b) Cascade Delete to define a relationship.
5. You can deliver alert as SMS.
Q. What is 14 hive in SharePoint?
Ans. See SharePoint 2010 Object Model
Q. How would you re-deploy the old custom solutions in SharePoint 2010.What Changes are needed to the old Solution files.
Ans. SharePoint 2010 object model contains many changes and enhancements, but our custom code will still compile and, will run as expected. You should however, rewrite and recompile any code that refers to files and resources in "12 hive".
For Details See :
See SharePoint 2010 Object Model - Backward Compatibility
Q. How many types of Client Object model extension are available in 2010 and when would you use one or the other.
Ans. To develop rich client side solutions, three set of client-side APIs has been introduced in the Microsoft.SharePoint.Client namespace. The three APIs are targeted for three different types of clients.
1. .net Managed applications – These are used when we have to create console applications or window applications, web applications which are not running inside SharePoint Contex.
2. For Silverlight applications
3. ECMAScript – It is a client object model extension for using with JavaScript or JScript. This is used for creating applications which are hosted inside SharePoint. For example, web part deployed in SharePoint site can use this JavaScript API for accessing SharePoint from browser using JavaScript.
Q. What are the security improvements in SharePoint 2010 ?
Ans. In SharePoint 2010 a variety of security methods have been introduced.
Claims-Based Authentication - Claims based authentication is based on identity. and trust.
Code Access Security - in which you can specify your own code access
security (CAS) policy for your web parts.
Sandbox Solutions - Sandbox Solutions which when deployed to the server, SharePoint runs in a special process that has limited permissions.
Cross-Site Scripting - Introduced to prevent Cross - Site Scripting (XSS) attacks.
Q. Whats New with SharePoint WebParts?
A developer can create two types of webparts using Visual Studio 2010.
1. Visual Webparts - Allows you to Drag and Drop the controls from the Toolbox to WebPart Design surface. You can of course write your custom code in the code file. You can also package and deploy your webparts directly to Sharepoint from VS by pressing Clt+F5. Visual studio 2010 also provides you with three different views for developing webparts. The views are split view, design view and Source view(as we have in designer 2007).
Note : The Visual Webpart project Item basically loads a User Control as a WebPart.
2. ASP.Net WebParts - Where a developer can build up User Interface and logic in a class file. You do not have designer for drag and drop of controls. This webpart inherits from standard ASP.Net webpart. For Deployment we can again use ctrl+f5 to deploy this webpart.
Q. What are the Visual Studio 2010 Tools for SharePoint.
Ans. Visual Studio 2010 includes SharePoint-specific project types and project item types, and includes powerful packaging, deployment, and debugging features that help increase your efficiency as a SharePoint 2010 developer.
Some of the Templates avaiable are :
1.Visual Web Part project template.
2. List defination template.
3. Content Type template.
4. Empty Project template.
5. Event Receiver template.
6. some workflow template.
7. the Site Definition template
and many more....
Q. What are SharePoint Sandboxed soultions ?
Ans. SharePoint 2010 provides a new sandboxed environment that enables you to run user solutions without affecting the rest of the SharePoint farm. This environment means that users can upload their own custom solutions without requiring intervention from administrators, and without putting the rest of the farm at risk. This means that the existing sites\pages or components will not be effected by the newly added soultion.
Users can deploy the below four things as sandboxed soultions :
1. WebParts.
2. Event Receivers.
3. List Definations.
4. Workflows.
Q. What are Requirenments for SharePoint 2010.
Ans. SharePoint Server 2010 will support only 64 - bit. It will require 64 bit Windows Server 2008 or 64 bit Windows Server 2008 R2. In addition to this, it will require 64 bit version of SQL Server 2008 or 64-bit version of SQL Server 2005.
Q. What is LINQ. How is it used in Sharepoint ?
Ans. LINQ is a feature of the programming languages C# 3.0 and Visual Basic .NET. LINQ allows you to query in an object-oriented way, supports compile-time check, gives you intellisense support in Visual Studio and defines a unified, SQL like syntax to query any data source. But unlike other languages and query syntaxes which vary from one type of data source to another, LINQ can be used to query, in principle, any data source whatsoever. It is commonly used to query objects collections, XML and SQL server data sources.
The LINQ to SharePoint Provider is defined in theMicrosoft.SharePoint.Linq namespace. It translates LINQ queries into Collaborative Application Markup Language (CAML) queries.
Q. What Changes are made in SharePoint 2010 to enforce Referential Integrity?
Ans. In SharePoint 2010, Referential Integrity is enforced using two options, available with Look-up columns.
While creating a Look-up column, you can either choose a) Restrict Delete or b) Cascade Delete to define a relationship between the Look-up list and the list containing the look-up Column. Read Details at SharePoint 2010 Referential integrity - Using LookUp Column
Q. Whats Ribbon in SharePoint 2010?
Ans. See the Post Ribbon in SharePoint 2010
Q . Whats New in SPALerts ?
Ans. In SharePoint 2007, alerts were send only through e-mails, but in SP2010 users can also send an alert to mobile devices as SMS Message. A New property DeliveryChannels is introduced to indicate, whether the alert is delivered as E-mail or as an SMS Message.
Q. What Has Changed with SSP in SharePoint 2010.
Ans. In SharePoint 2010 Shared Service Providers (SSP's) are replaced by Service Applications. Services are no longer combined into a SSP. They are running independent as a service application. The service application architecture is now also built into Microsoft SharePoint Foundation 2010, in contrast to the Shared Services Provider (SSP) architecture that was only part of Office SharePoint Server 2007.
A key benefit here is that all services are installed by default and there is no SSP setup.
Q. What Do you know about SharePoint Object Model?
Ans. In Sharepoint Object model there are two Important namespaces.
The Microsoft.Office.Server namespace is the root namespace of all Office Server objects and Microsoft.SharePoint is the root namespace for all WSS objects.
Read More at SharePoint 2007 Object Model
Q. Can you develop webparts and other SharePoint solutions at your local machine?
Ans. In order to run and debug sharePoint solutions, the project must reside on the server which has Windows sharePoint services installed. However, you can reference theMicrosoft.SharePoint dll in your project at your local, but you won’t be able to run it.
Q. How do you debug SharePoint Webparts?
Ans. To debug SharePoint webpart (or any solution) you can simply drag and drop your complied .dll in GAC and recycle the app pool. You can also run upgrade solution command from stsadm.
Q. How would you retrieve large number of Items from the list ?
Ans. To retrieve large number of items with a better performance we can either use SPQuery or PortalSiteMapProvider Class. Read More with Examples
Retrieving large number of Items from sharepoint list
Q. How Do you implement Impersonation in ShrePoint.
Ans. By Using RunWithElevatedPrivileges method provided by SPSecurity class.
See e.g Impersonation in Sharepoint
Q. Can we use our custom master page with the application pages in SharePoint 2010 ?
Ans. With 2010, you can now set whether the pages under _Layouts use the same Master Page as the rest of your site. You can enable or disable this functionality through the web application settings in Central Administration. This however, is not applicable to your custom application pages. If you want your custom applictaion page to inherit the site master page you must derive it from Microsoft.SharePoint.WebControls.LayoutsPageBase class.
Q. What does CMDUI.XML contain?
Ans. The definitions for the out-of-the-box ribbon elements are split across several files in the SharePoint root, with TEMPLATE\GLOBAL\XML\CMDUI.XML being the main one.
Q. Why would you use LINQ over CAML for data retrieval?
Ans. Unlike CAML, with LINQ to SharePoint provider, you are working with strongly typed list item objects. For example, an item in the Announcements list is an object of type Announcement and an item on a Tasks list is an object of type Task. You can then enumerate the objects and get the properties for your use. Also, you can use LINQ syntax and the LINQ keywords built into C# and VB for LINQ queries.
Q. How do you write to SharePoint ULS logs in 2010 ?
Ans. In SharePoint Foundation, ULS exposes configurable settings in two ways, through the – Object model and Windows PowerShell cmdlets. For writing to SharePoint ULS logs developers can can use Diagnostics Service, which will make the customized categories viewable in the administrative UI for our errors.
or they can use the number of cmdlets available for accessing ULS logs using powershell. Some of the cmdlets are Get-SPDiagnosticConfig ,Get-SPLogEvent etc.
Q. How does Client object model works ?
Ans. When we use SharePoint client API’s to perform a specific task, the SharePoint Foundation 2010 managed client object model bundles up these uses of the API into XML and sends it to the server that runs SharePoint Foundation. The server receives this request, and makes appropriate calls into the object model on the server, collects the responses, forms them into JavaScript Object Notation (JSON), and sends that JSON back to the SharePoint Foundation 2010 managed client object model. The client object model parses the JSON and presents the results to the application as .NET Framework objects (or ECMAScript objects for ECMAScript).
Q. What is difference between Load() and LoadQuery() methods ?
Ans. Load method populates the client object directly with what it gets data from the server i.e. a collection object like ListItemCollection etc. but LoadQuery returns the data as a completely new collection in IEnumerable format. Other major difference is that the Collections that you load using the Load() method are eligible for garbage collection only when the client context variable itself goes out of scope where as, in these collections go out of scope at the end of IEnumerable list.
Q. How do you access ECMAScript object model API’s ?
Ans. The ECMAScript library is available in a number of JS files in the LAYOUTS folder. The main file among number of .js files is SP.js. When you include this file in the APSX page using a ScriptLink control, all other required JS files are loaded automatically. By linking SP.js to your page, the SP namespace gets registered. SP is the SharePoint namespace that contains all objects. For debugging purposes every js file also has a ‘debug’ equivalent in the same folder.
Q. What is the purpose of calling clientContext.ExecuteQuery() ?
Ans. ExecuteQuery gives you the option to minimize the number of roundtrips to the server from your client code. All the components loaded into the clientcontext are executed in one go.
Q: What is the performance impact of RunWithElevatedPrivileges?
Ans. RunWithElevatedPrivileges creates a new thread with the App Pool's credentials, blocking your current thread until it finishes.
Q. How will you add Code behind to a Custom Applictaion Page or a Layout Page in SharePoint?
Ans. You do not deploy a code behind file with your custom Layouts page. Instead, you can have the page inherit from the complied dll of the solution to access the code behind.
Q. What is the difference between a Site Definition and a Site Template?
Ans. Site Definitions are stored on the hard drive of the SharePoint front end servers. They are used by the SharePoint application to generate the sites users can create. Site Templates are created by users as a copy of a site they have configured and modified so that they do not have to recreate lists, libraries, views and columns every time they need a new instance of a site.
Q. Why do you use Feature Receivers?
Ans. Feature Receivers are used to execute any code on Activation\Deactivation of a Feature. You can use it for various purposes.
Q. Can you give a example where feature receivers are used.
Ans. You can use it to assign an event receiver feature to a specific type of list or can write a code in a feature receivers Deactivate method to remove a webpart from webpart gallery.
Q. Where do you deploy the additional files used in your webpart, like css or javascript files, and how do you use them in your WebPart?
Ans. You can deploy the css or javascript files in _layouts folder in SharePoint's 12 hive. To use them in your webpart, you need to first register them to your webpart page and then specify a virtual path for the file for e.g. _layouts\MyCSS.css See Code examples at Using External Javascript, CSS or Image File in a WebPart.
Q: When should you dispose SPWeb and SPSite objects?
Ans. According to the best Practices you should always dispose them if you have created them in your code. You can dispose them in Finally block or you can use the "Using" clause, so that they gets disposed when not required. If you are using SPContext then you need not dispose the spsite or spweb objects.
Q. What are the best practices for SharePoint development.
Ans. Some of the best practices are:
1. You should always dispose SPsite and SPWeb objects, once you refer them in your code. Using the "Using" clause is recommended.
2. Use RunwithelevatePrivilages to avoid errors for end users.
3. Try writing your errors to SharePoint error logs (ULS Logs). Since it’s a bad idea to fill-up event log for your production environment.
4. Use SPQuery instead of foreach loop while retrieving Items from the list.
5. Deploy additional files used in your webpart to 12 hive. Use your solution package to drop the files in 12 hive. Also, make sure that all the references (for e.g. Css or .js files) get removed when the solution is retracted.
Also See : Best Practices to Improve Site Performance
Q.What is the main difference between using SPListItem.Update() and SPListItem.SystemUpdate()?
Ans. Using SystemUpdate() will not create a new version and will also retain timestamps.
Q. When do you use SPSiteDataQuery ?
Ans. You can use SPSiteDataQuery when you need to extract data from more than one list\library in your site colletcion. The data is extracted on the basis of the query you write and is
returened as a Datatable. You can also specify the GUID for the lists\libraries you want to query against.
Q. How do you create a Custom action for an item in a list ?
Ans. This can be done by adding a new feature into SharePoint. You would need to use customaction tag in your elements.xml file and will have to set various properties like imageurl or UrlAction for your customaction. You can later add this feature into sharepoint using stsadm install feature command.
Q. How would you bind this CustomAction to a specific list ?
Ans. To do this you can either create a new list type(again a feature) and use the list type number for the new list in your Registration Type property of the Custom action. The Custom Action will then show up only for the items of this list type. or You can create a new content type and then use that content type's id in your cutsomaction to bind the custom action to items of just that content type. Add the new content type to the list where you need this customaction.
Q. How will you deploy an existing asp.net webapplication or website in SharePoint?
Ans. You would need to wrap the web application in a solution package in order to deploy it in 12 hive or say ShraePoint. It is recommended to create a feature first, and then wrap everything in a Solution package. See exampleDepoly a Custom aspx Page in SharePoint
Q. How will you cancel a deployment from central admin -> solution managment, if its stuck at “deploying” or “Error”.
Ans. You can either try to force execute timer jobs using execadmsvcjobs command or can cancel the dpeloyment using stsadm command stsadm –o cancaldeployment –id {GUID} command. The Id here would be GUID of the timer or deployment job. You can get the Id from stsadm enumdeployment command. This will display all the deployments which are process or are stuck with Error.
Q. How do make an existing non-publishing site Publishing?
Ans. You can simply activate the SharePoint Publishing Feature for the Site, you want to make publishing.
Q. Can you name some of the tools used for SharePoint Administration?
Ans. Tools Used for SharePoint Administration
Q. What are Application Pages in SharePoint?
Ans. Unlike site pages (for example, default.aspx), a custom application page is deployed once per Web server and cannot be customized on a site-by-site basis. Application pages are based in the virtual _layouts directory. In addition, they are compiled into a single assembly DLL.
A good example of an Application Page is the default Site Settings page: every site has one, and it's not customizable on a per site basis (although the contents can be different for sites).
With application pages, you can also add inline code. With site pages, you cannot add inline code.
Q. What is Authentication and Authorization?
Ans . An authentication system is how you identify yourself to the computer. The goal behind an authentication system is to verify that the user is actually who they say they are.
Once the system knows who the user is through authentication, authorization is how the system decides what the user can do.
Q. How do you deploy a User Control in SharePoint ?
Ans. You deploy your User Control either by a Custom webpart, which will simply load the control on the page or can use tools like SmartPart, which is again a webpart to load user control on the page. User Control can be deployed using a custom solution package for the webapplication or you can also the control in the webpart solution package so that it gets deployed in _controlstemplate folder.
Q. Which is faster a WebPart or a User Control?
Ans. A WebPart renders faster than a User Control. A User Control in SharePoint is usually loaded by a webpart which adds an overhead. User Controls however, gives you an Interface to add controls and styles.
Q. What SharePoint Databases are Created during the standard Install?
Ans. During standard install, the following databases are created :
SharePoint_AdminContent
SharePoint_Config
WWS_Search_SERVERNAME%_%GUID_3%
SharedServicesContent_%GUID_4%
SharedServices1_DB_%GUID_5%
SharedServices1_Search_DB_%
GUID_6%WSS_Content_%GUID_7%
Q. What are content types?
Ans. A content type is a flexible and reusable WSS type definition (or we can a template) that defines the columns and behavior for an item in a list or a document in a document library. For example, you can create a content type for a leave approval document with a unique set of columns, an event handler, and its own document template and attach it with a document library/libraries.
Q. Can a content type have receivers associated with it?
Ans. Yes, a content type can have an event receiver associated with it, either inheriting from the SPListEventReciever base class for list level events, or inheriting from the SPItemEventReciever base class. Whenever the content type is instantiated, it will be subject to the event receivers that are associated with it.
Q. Can you add a Cutsom Http Handler in SharePoint ?
Ans. Yes, a Custom httphandler can be deployed in _layouts folder in SharePoint. Also, we need to be register the handler in the webapp's webconfig file.
Q. While creating a Web part, which is the ideal location to Initialize my new controls?
Override the CreateChildControls method to include your new controls. You can control the exact rendering of your controls by calling the .Render method in the web parts Render method.
Q. How do you return SharePoint List items using SharePoint web services?
Ans.
In order to retrieve list items from a SharePoint list through Web Services, you should use the lists.asmx web service by establishing a web reference in Visual Studio. The lists.asmx exposes the GetListItems method, which will allow the return of the full content of the list in an XML node. It will take parameters like the GUID of the name of the list you are querying against, the GUID of the view you are going to query, etc.
Q. How Do you deploy Files in 12 hive when using wspbuilder or vsewss?
Ans. Typically, you can add these files in the 12 hive folder structure in your project. In Vsewss however, you will have to create this structure manually.
Q. What files gets created on a file system, when a Site collection is created ?
Ans. Windows SharePoint Services does not create any files or folders on the file system when the site collection or sites are created; everything is created in the content database. The Pages for the site collection are created as instances in the content database. These instances refer to the actual file on the file system.
Q. What are Customized and Uncustomized Files in SharePoint ?
Ans. There are two types of Pages in SharePoint; site pages (also known as content pages) and application pages.
Uncustomized :
When you create a new SharePoint site in a site collection, Windows SharePoint Services provisions instances of files into the content database that resides on the file system. That means if you create a new Site "xyz" of type Team Site(orTeam sIte Definition), an instance of the Team Site Definition( Which resides on the File System), i.e. "xyz" gets created in the Content database. So, When ASP.NET receives a request for the file, it first finds the file in the content database. This entry in the content database tells ASP.NET that the file is actually based on a file on the file system and therefore, ASP.NET retrieves the source of the file on the file system when it constructs the page.
Customized :
A customized file is one in which the source of the file lives exclusively in the site collection's content database. This happens When you modify the file in any way through the SharePoint API, or by SharePoint Designer 2007,which uses the SharePoint API via RPC and Web service calls to change files in sites. So, When the file is requested, ASP.NET first finds the file in the content database. The entry in the database tells ASP.NET whether the file is customized or uncustomized. If it is customized, it contains the source of the file, which is used by ASP.NET in the page contraction phase.
Q. What are event receivers?
Ans. Event receivers are classes that inherit from the SpItemEventReciever or SPListEventReciever base class (both of which derive out of the abstract base class SPEventRecieverBase), and provide the option of responding to events as they occur within SharePoint, such as adding an item or deleting an item.
Q. When would you use an event receiver?
Ans. Since event receivers respond to events, you could use a receiver for something as simple as canceling an action, such as deleting a document library by using the Cancel property. This would essentially prevent users from deleting any documents if you wanted to maintain retention of stored data.
Q. If I wanted to restrict the deletion of the documents from a document library, how would I go about it?
Ans. You would create a event receiver for that list/library and implement the ItemDeleting method. Simply, set: properties.Cancel= true and display a friendly message using Properties.Message("How can u delete this... Its not your stuff!");
Q. What is the difference between an asynchronous and synchronous event receivers?
Ans. An asynchronous event occurs after an action has taken place, and a synchronous event occurs before an action has take place. For example, an asynchronous event is ItemAdded, and its sister synchronous event is ItemAdding
Q. How do you Increase trust level for a single WebPart in the WebConfig file.
Ans. To list a Web Part with Full Permissions within your Web Application while still retaining a WSS_Minimal permission set for all other Web Parts, You need to create a Custom policy file. This file will be then referenced in SharePoint Web.config file and will allow your specific webpart to be of Full trust.
Steps :
1. Make a copy of the WSS_Minimal.Config file from the 12\Config folder and paste it into the same folder renaming it to Custom_WSS_Minimal.Config. Now, edit the Custom_WSS_Minimal.Config file using NotePad. Obtain the Public Key Token for the Web Part assembly that you want to deploy, using the following command: sn –Tp filename.dll. Create a new entry in your Custom_WSS_Minimal.Config file for your WebPart. Save the File.
Finally, Create a new TrustLevel element for your config file in the Web.Config called Custom_WSS_Minimal that points to your custom file in the 12\config folder. Recycle the Application Pool and You’re Done.
Q. How does Windows SharePoint Services help render the Webapplictaion in ShrePoint?
Ans. When a new web applictaion is created via Central Admin, Windows SharePoint Services creates a new Web application in IIS. Then the WSS, loads the custom HTTP application and replaces all installed HTTP handlers and modules with Windows SharePoint Services–specific ones. These handlers and modules essentially tell IIS to route all file requests through the ASP.NET 2.0 pipeline. This is because most files in a SharePoint site are stored in a Microsoft SQL Server database.
Q. How would you pass user credentials while using SharePoint WebService from your Web Part or application.
Ans. The web service needs credentials to be set before making calls.
Examples:
listService.UseDefaultCredentials = true; // use currently logged on user
listService.Credentials = new System.Net.NetworkCredential("user", "pass", "domain"); // use specified user
Q. How would you remove a webapart from the WebPart gallery? Does it get removed with Webpart retraction?
Ans. No, Webpart does not get removed from the WebPart gallery on retraction. You can write a feature receiver on Featuredeactivating method to remove the empty webpart from the gallery.
Q. What is a SharePoint Feature? Features are installed at what scope
Ans. A SharePoint Feature is a functional component that can be activated and deactivate at various scopes throughout a SharePoint instances, scope of which are defined as
1. Farm level 2. Web Application level 3. Site level 4. Web level
Features have their own receiver architecture, which allow you to trap events such as when a feature is Installing, Uninstalling, Activated, or Deactivated.
Q. What type of components can be created or deployed as a feature?
Ans. We can create menu commands, Custom Actions,page templates, page instances, list definitions, list instances,event handlers,webparts and workflows as feature.
Q. How Do you bind a Drop-Down Listbox with a Column in SharePoint List ?
Ans.
Method 1 : You can get a datatable for all items in the list and add that table to a data set. Finally, specify the dataset table as datasource for dropdown listbox.
Method 2 : You can also use SPDatasource in your aspx or design page.
See Code example Binding Drop-Down with Sharepoint List data
Q. How Does SharePoint work?
Ans. The browser sends a DAV packet to IIS asking to perform a document check in. PKMDASL.DLL, an ISAPI DLL, parses the packet and sees that it has the proprietary INVOKE command. Because of the existence of this command, the packet is passed off to msdmserv.exe, who in turn processes the packet and uses EXOLEDB to access the WSS, perform the operation and send the results back to the user in the form of XML.
Q. What is CAML?
Ans. CAML stands for Collaborative Application Markup Language and is an XML-based languagethat is used inMicrosoft Windows SharePoint Services to define sites and lists, including, for Eg, fields, views, or forms, but CAML is also used to define tables in the Windows SharePoint Servies database during site provisioning. Developers mostly use CAML Queries to retrieve data from Lists\libraries.
Q. Can you display\add a Custom aspx or WebApplication Page in SharePoint Context ?
Ans. You need to make some modification in the aspx file to display it in SharePoint Context. Firstly, add the references for various sharepoint assemblies on the Page. Then wrap the Code in PlaceHolderMain contentPlaceholder, so that it gets displayed as a content page. Lastly, add a reference to SharePoint Master Page in aspx file and swicth it in Code behind if needed. See Code Example at Display aspx Page in SharePoint context
1) What are the two base classes a WebPart you are going to use within SharePoint 2007 can inherit from?
There are two base classes that a WebPart which is going to be consumed by SharePoint can inherit from, either the SharePoint WebPart Base class or the ASP.NET 2.0 WebPart base class. When inheriting from the SharePoint WebPart Base class your derived WebPart class will inherit from Microsoft.SharePoint.WebPartPages.WebPart. When inheriting from the ASP.NET 2.0 WebPart base class your derived WebPart class will inherit from System.Web.UI.WebControls.WebParts.WebPart. It is considered good practice to use the ASP.NET WebPart base class since the old base class is meant for backwards compatibility with previous version of SharePoint, however there are four exception when it is better to leverage functionality from the SharePoint WebPart base class:
Cross page connections
Connections between Web Parts that are outside of a Web Part zone
Client-side connections (Web Part Page Services Component)
Data caching infrastructure
2) What are the differences between the two base classes and what are the inherit benefits of using one over another?
The difference is the Microsoft.SharePoint.WebPartPages.WebPart base class is meant for backward compatibility with previous versions of SharePoint. The benefit of using the SharePoint WebPart base class is it supported:
Cross page connections
Connections between Web Parts that are outside of a Web Part zone
Client-side connections (Web Part Page Services Component)
Data caching infrastructure
ASP.NET 2.0 WebParts are generally considered better to use because SharePoint is built upon the ASP.NET 2.0 web architecture. Inheriting from the ASP.NET 2.0 base class offers you features that inherit to ASP.NET 2.0, such as embedding resources as opposed to use ClassResources for deployment of said types.
3) What is the GAC?
The GAC stands for the global assembly cache. It is the machine wide code cache which will give custom binaries place into the full trust code group for SharePoint. Certain SharePoint assets, such as Feature Receivers need full trust to run correctly, and therefore are put into the GAC. You should always try to avoid deployment to the GAC as much as possible since it will possibly allow development code to do more than it was intended to do.
4) What is strong naming (signing) a WebPart assembly file mean?
Signing an assembly with a strong name (a.k.a strong naming) uses a cryptographic key pair that gives a unique identity to a component that is being built. This identity can then be referred throughout the rest of the environment. In order to install assemblies into the GAC, they must be strongly named. After signing, the binary will have a public key token identifier which can be use to register the component in various other places on the server.
5) What are safe controls, and what type of information, is placed in that element in a SharePoint web.config file?
When you deploy a WebPart to SharePoint, you must first make it as a safe control to use within SharePoint in the web.config file. Entries made in the safe controls element of SharePoint are encountered by the SharePointHandler object and will be loaded in the SharePoint environment properly, those not will not be loaded and will throw an error.
In the generic safe control entry (this is general, there could be more), there is generally the Assembly name, the namespace, the public key token numeric, the typename, and the safe declaration (whether it is safe or not). There are other optional elements.
6) What is the CreateChildControls() method? How can you use it to do something simple like displaying a Label control?
The CreateChildControls method in WebParts is used to notify the WebPart that there are children controls that should be output for rendering. Basically, it will add any child ASP.NET controls that are called instantiating each control with its relevant properties set, wire any relevant event handlers to the control, etc. Then the add method of the control class will add the control to the controls collection. In the relevant WebPart render method, the EnsureChildControls method can be called (or set to false if no child controls should be called) to ensure that the CreateChildControls method is run. When using CreateChildControls it implies that your WebPart contains a composition of child controls.
In order to create something like a label control in Create, you would create a new label control using the new keyword, set the various properties of the control like Visible=True and ForeColor = Color.Red, and then use Controls.Add(myLabelControl) to add the control to the controls collection. Then you can declare EnsureChildControls in the Render method of the WebPart.
7) What does the RenderContents method do in an ASP.NET 2.0 WebPart?
The render contents method will render the WebPart content to the writer, usually an HtmlTextWriter since WebParts will output to an HTML stream. RenderContents is used to tell how the controls that are going to be displayed in the WebPart should be rendered on the page.
*** Side Question: I got asked what the difference between CreateChildControls and the RenderContents method. The CreateChildControls method is used to add controls to the WebPart, and the RenderContents method is used to tell the page framework how to render the control into HTML to display on a page.
8) What is the WebPartManager sealed class? What is its purpose?
The WebPartManager sealed class is responsible for managing everything occurring on a WebPart page, such as the WebParts (controls), events, and misc. functionality that will occur in WebPartZones. For example, the WebPartManager is responsible for the functionality that is provided when you are working with moving a WebPart from WebPartZone to WebPartZone. It is known as the “the central class of the Web Part Control Set.”
*** Side Question: I got asked how many WebPartManager controls should be on a page. In order to have WebParts on a page there has to be just one WebPartManager control to manage all the WebParts on the page.
9) What is a SPSite and SPWeb object, and what is the difference between each of the objects?
The SPSite object represents a collection of sites (site collection [a top level sites and all its subsites]). The SPWeb object represents an instance SharePoint Web, and SPWeb object contains things like the actual content. A SPSite object contains the various subsites and the information regarding them.
10) How would you go about getting a reference to a site?
PLAIN TEXT
C#:
1. oSPSite = new SPSite("http:/server");
2.
3. oSPWeb = oSPSite.OpenWeb();
11) What does a SPWebApplication object represent?
The SPWebApplication objects represents a SharePoint Web Application, which essentially is an IIS virtual server. Using the class you can instigate high level operations, such as getting all the features of an entire Web Application instance, or doing high level creation operations like creating new Web Applications through code.
12) Would you use SPWebApplication to get information like the SMTP address of the SharePoint site?
Yes, since this is a Web Application level setting. You would iterate through each SPWebApplication in the SPWebApplication collection, and then use the appropriate property calls (OutboundMailServiceInstance) in order to return settings regarding the mail service such as the SMTP address.
Side Question: I got asked if there are other ways to send emails from SharePoint. The answer is yes, there is. You can use the SendMail method from the SPutility class to send simple emails, however it is not as robust as using the System.Net.Mail functionality since it doesn’t allow things like setting priorities on the email.
13) How do you connect (reference) to a SharePoint list, and how do you insert a new List Item?
PLAIN TEXT
C#:
1. using(SPSite mySite = new SPSite("yourserver"))
2. {
3. using(SPWeb myWeb = mySite.OpenWeb())
4. {
5. SPList interviewList = myWeb.Lists["listtoinsert"];
6. SPListItem newItem = interviewList.Items.Add();
7.
8. newItem["interview"] = "interview";
9. newItem.Update();
10. }
11. }
14) How would you loop using SPList through all SharePont List items, assuming you know the name (in a string value) of the list you want to iterate through, and already have all the site code written?
PLAIN TEXT
C#:
1. SPList interviewList = myWeb.Lists["listtoiterate"];
2. foreach (SPListItem interview in interviewList)
3. {
4. // Do Something
5. }
15) How do you return SharePoint List items using SharePoint web services?
In order to retrieve list items from a SharePoint list through Web Services, you should use the lists.asmx web service by establishing a web reference in Visual Studio. The lists.asmx exposes the GetListItems method, which will allow the return of the full content of the list in an XML node. It will take parameters like the GUID of the name of the list you are querying against, the GUID of the view you are going to query, etc.
Side Question: I got asked how I built queries with the lists.asmx web service. In order to build queries with this service, one of the parameters that the GetListItems method exposes is the option to build a CAML query. There are other ways to do this as well, but that was how I answered it.
16) When retrieving List items using SharePoint Web Services, how do you specify explicit credentials to be passed to access the list items?
In order to specify explicit credentials with a Web Service, you generally instantiate the web service, and then using the credentials properties of the Web Service object you use the System.Net.NetworkCredential class to specify the username, password, and domain that you wish to pass when making the web service call and operations.
*** Side Question: I got asked when you should state the credentials in code. You must state the credentials you are going to pass to the web service before you call any of the methods of the web service, otherwise the call will fail.
17) What is CAML, and why would you use it?
CAML stands for Collaborative Application Markup Language. CAML is an XML based language which provides data constructs that build up the SharePoint fields, view, and is used for table definition during site provisioning. CAML is responsible for rending data and the resulting HTML that is output to the user in SharePoint. CAML can be used for a variety of circumstances, overall is used to query, build and customize SharePoint based sites. A general use would be building a CAML query in a SharePoint WebPart in order to retrieve values from a SharePoint list.
18) What is impersonation, and when would you use impersonation?
Impersonation can basically provide the functionality of executing something in the context of a different identity, for example assigning an account to users with anonymous access. You would use impersonation in order to access resources on behalf of the user with a different account, that normally, that wouldn’t be able to access or execute something.
19) What is the IDesignTimeHtmlProvider interface, and when can you use it in WebParts?
The IDesignTimeHtmlProvider interface uses the function GetDesignTimeHtml() which can contain your relevant render methods. It was helpful to use in 2003 since it allowed your WebPart to have a preview while a page was edited in FrontPage with the Webpart on it, because the GetDesignTimeHtml() method contains the HTML for the designer to render.
20) What are WebPart properties, and what are some of the attributes you see when declaring WebPart properties in code?
WebPart properties are just like ASP.NET control properties, they are used to interact with and specify attributes that should be applied to a WebPart by a user. Some of the attributes you see with ASP.NET 2.0 properties are WebDescription, WebDisplayName, Category, Personalizable, and WebBrowsable. Although most of these properties come from the System.Web.UI.WebControls.WebParts class, ones like Category come out of System.ComponentModel namespace.
21) Why are properties important in WebPart development, and how have you exploited them in past development projects? What must each custom property have?
Properties are important because WebParts allow levels of personalization for each user. WebPart properties make it possible for a user to interact, adjust, and increase overall experience value with the programmatic assets that you develop without having the need to use an external editor or right any code. A very simple example of exploiting a property would be something like allowing the user to change the text on the WebPart design interface so that they can display whatever string of text they desire.
Each custom property that you have must have the appropriate get and set accessor methods.
22) What are ClassResources? How do you reference and deploy resources with an ASP.NET 2.0 WebPart?
ClassResources are used when inheriting from the SharePoint.WebPart.WebPartPages.WebPart base class, and are defined in the SharePoint solution file as things that should be stored in the wpresources directory on the server. It is a helpful directory to use in order to deploy custom images. In ASP.NET 2.0, typically things such as images are referenced by embedding them as resources within an assembly. The good part about ClassResources is they can help to eliminate recompiles to change small interface adjustments or alterations to external JavaScript files.
23) What is a SharePoint Solution File? How does it differ from WebPart .cab files in legacy development? What does it contain?
A SharePoint solution file is essentially a .cabinet file with all a developers ustom componets suffixed with a .wsp extension that aids in deployment. The big difference with SharePoint solution files is is that a solution:
allows deployment to all WFE’s in a farm
is highly manageable from the interface allowing deployment, retraction, and versioning
Can package all types of assets like site definitions, feature definitions (and associated components), Webparts, etc.
Can provide Code Access Security provisioning to avoid GAC deployments
Just to name a few things…
24) What is a .ddf file and what does it have to do with SharePoint Solution creation?
A .ddf file is a data directive file and is used when building the SharePoint solution bundle specifying the source files and their destination locations. The important thing for someone to understand is that the .ddf file will be passed as a parameter to the MAKECAB utility to orchestrate construction of the SharePoint solution fiel.
25) What file does a SharePoint solution package use to orchestrate (describe) its packaged contents?
The solution Manifest.XML file.
26) What deployment mechanism can you use to instigate Code Access Security attributes for your WebParts?
SharePoint solution files can add in order to handle code access security deployment issues. This is done in the element in the SharePoint solution manifest.XML, which makes it easier to get assemblies the appropriate permissions in order to operate in the bin directory of the web application.
27) What is a SharePoint Feature? What files are used to define a feature?
A SharePoint Feature is a functional component that can be activated and deactivate at various scopes throughout a SharePoint instances, such as at the farm, site collection, web, etc. Features have their own receiver architecture, which allow you to trap events such as when a feature is installing, uninstalling, activated, or deactivated. They are helpful because they allow ease of upgrades and versioning.
The two files that are used to define a feature are the feature.xml and manifest file. The feature XML file defines the actual feature and will make SharePoint aware of the installed feature. The manifest file contains details about the feature such as functionality.
Side Question: I got asked how the introduction of features has changed the concept of site definitions. SharePoint features are important when understanding the architecture of site definitions, since the ONET.XML file has been vastly truncated since it has several feature stapled on it.
28) What types of SharePoint assets can be deployed with a SharePoint feature?
Features can do a lot. For example, you could deploy
Simple site customizations
Custom site navigation
WebParts
pages
list types
list instances
event handlers
workflows
custom actions
just to name a few….
29) What are event receivers?
Event receivers are classes that inherit from the SpItemEventReciever or SPListEventReciever base class (both of which derive out of the abstract base class SPEventRecieverBase), and provide the option of responding to events as they occur within SharePoint, such as adding an item or deleting an item.
30) When would you use an event receiver?
Since event receivers respond to events, you could use a receiver for something as simple as canceling an action, such as deleting a document library by using the Cancel property. This would essentially prevent users from deleting any documents if you wanted to maintain retention of stored data.
31) What base class do event receivers inherit from?
Event receivers either inherit from the SPListEventReciever base class or the SPItemEventReciever base class, both which derive from the abstract base class SPEventReceiverBase.
32) If I wanted to not allow people to delete documents from a document library, how would I go about it?
You would on the ItemDeleting event set: properties.Cancel= true.
33) What is the difference between an asynchronous and synchronous event receivers?
An asynchronous event occurs after an action has taken place, and a synchronous event occurs before an action has take place. For example, an asynchronous event is ItemAdded, and its sister synchronous event is ItemAdding.
34) How could you append a string to the title of a site when it is provisioned?
In the OnActivated event:
PLAIN TEXT
C#:
1. SPWeb site = siteCollection.RootWeb;
2. site.Title += "interview";
3. site.Update();
35) Can an event receiver be deployed through a SharePoint feature?
Yes.
36) What is a content type?
A content type is an information blueprint basically that can be re-used throughout a SharePoint environment for defining things like metadata and associated behaviors. It is basically an extension of a SharePoint list, however makes it portable for use throughout an instance regardless of where the instantiation occurs, ergo has location independence. Multiple content types can exist in one document library assuming that the appropriate document library settings are enabled. The content type will contain things like the metadata, listform pages, workflows, templates (if a document content type), and associated custom written functionality.
37) Can a content type have receivers associated with it?
Yes, a content type can have an event receiver associated with it, either inheriting from the SPListEventReciever base class for list level events, or inheriting from the SPItemEventReciever base class. Whenever the content type is instantiated, it will be subject to the event receivers that are associated with it.
38) What two files are typically (this is kept generally) included when developing a content type, and what is the purpose of each?
There is generally the main content type file that holds things like the content type ID, name, group, description, and version. There is also the ContentType.Fields file which contains the fields to include in the content type that has the ID, Type, Name, DisplayName, StaticName, Hidden, Required, and Sealed elements. They are related by the FieldRefs element in the main content type file.
39) What is an ancestral type and what does it have to do with content types?
An ancestral type is the base type that the content type is deriving from, such as Document (0x0101). The ancestral type will define the metadata fields that are included with the custom content type.
40) Can a list definition be derived from a custom content type?
Yes, a list definition can derive from a content type which can be seen in the schema.XML of the list definition in the element.
41) When creating a list definition, how can you create an instance of the list?
You can create a new instance of a list by creating an instance.XML file.
42) What is a Field Control?
Field controls are simple ASP.NET 2.0 server controls that provide the basic field functionality of SharePoint. They provide basic general functionality such as displaying or editing list data as it appears on SharePoint list pages.
43) What base class do custom Field Controls inherit from?
This varies. Generally, custom field controls inherit from the Microsoft.SharePoint.WebControls.BaseFieldControl namespace, but you can inherit from the default field controls.
44) What is a SharePoint site definition? What is ghosted (uncustomized) and unghosted (customized)?
SharePoint site definitions are the core set of functionality from which SharePoint site are built from, building from the SiteTemplates directory in the SharePoint 12 hive. Site definitions allow several sites to inherit from a core set of files on the file system, although appear to have unique pages, thereby increasing performance and allowing changes that happen to a site propagate to all sites that inherit from a site definition. Ghosted means that when SharePoint creates a new site it will reference the files in the related site definition upon site provisioning. Unghosted means that the site has been edited with an external editor, and therefore the customizations are instead stored in the database, breaking the inheritance of those files from the file system.
45) How does one deploy new SharePoint site definitions so that they are made aware to the SharePoint system?
The best way to deploy site definitions in the SharePoint 2007 framework is to use a SharePoint solution file, so that the new site definition is automatically populated to all WFE’s in the SharePoint farm.
http://www.aired.in/2011/03/sharepoint-2007-moss-2007-developer.html
.NET 4.0 New Feature
Feature Categories
Microsoft breaks the new features into the following four categories so I will maintain the pattern:
•Named and Optional Parameters
•Dynamic Support
Optional Parameters
Support for optional parameters allows you to give a method parameter a default value so that you do not have to specify it every time you call the method. This comes in handy when you have overloaded methods that are chained together.
The Old Way
public void Process( string data )
{
Process( data, false );
}
public void Process( string data, bool ignoreWS )
{
Process( data, ignoreWS, null );
}
public void Process( string data, bool ignoreWS, ArrayList moreData )
{
// Actual work done here
}The reason for overloading Process in this way is to avoid always having to include "false, null" in the third method call. Suppose 99% of the time there will not be 'moreData' provided. It seems ridiculous to type and pass null so many times.
// These 3 calls are equivalent
Process( "foo", false, null );
Process( "foo", false );
Process( "foo" );
The New Way
public void Process( string data, bool ignoreWS = false, ArrayList moreData = null )
{
// Actual work done here
}
// Note: data must always be provided because it does not have a default valueNow we have one method instead of three, but the three ways we called Process above are still valid and still equivalent.
ArrayList myArrayList = new ArrayList();
Process( "foo" ); // valid
Process( "foo", true ); // valid
Process( "foo", false, myArrayList ); // valid
Process( "foo", myArrayList ); // Invalid! See next section
Dynamic
The dynamic keyword is new to C# 4.0, and is used to tell the compiler that a variable's type can change or that it is not known until runtime. Think of it as being able to interact with an Object without having to cast it.
dynamic cust = GetCustomer();
cust.FirstName = "foo"; // works as expected
cust.Process(); // works as expected
cust.MissingMethod(); // No method found!Notice we did not need to cast nor declare cust as type Customer. Because we declared it dynamic, the runtime takes over and then searches and sets the FirstName property for us. Now, of course, when you are using a dynamic variable, you are giving up compiler type checking. This means the call cust.MissingMethod() will compile and not fail until runtime. The result of this operation is a RuntimeBinderException because MissingMethod is not defined on the Customer class.
The example above shows how dynamic works when calling methods and properties. Another powerful (and potentially dangerous) feature is being able to reuse variables for different types of data. I'm sure the Python, Ruby, and Perl programmers out there can think of a million ways to take advantage of this, but I've been using C# so long that it just feels "wrong" to me.
dynamic foo = 123;
foo = "bar";OK, so you most likely will not be writing code like the above very often. There may be times, however, when variable reuse can come in handy or clean up a dirty piece of legacy code. One simple case I run into often is constantly having to cast between decimal and double.
decimal foo = GetDecimalValue();
foo = foo / 2.5; // Does not compile
foo = Math.Sqrt(foo); // Does not compile
string bar = foo.ToString("c");The second line does not compile because 2.5 is typed as a double and line 3 does not compile because Math.Sqrt expects a double. Obviously, all you have to do is cast and/or change your variable type, but there may be situations where dynamic makes sense to use.
dynamic foo = GetDecimalValue(); // still returns a decimal
foo = foo / 2.5; // The runtime takes care of this for us
foo = Math.Sqrt(foo); // Again, the DLR works its magic
string bar = foo.ToString("c");
Microsoft breaks the new features into the following four categories so I will maintain the pattern:
•Named and Optional Parameters
•Dynamic Support
Optional Parameters
Support for optional parameters allows you to give a method parameter a default value so that you do not have to specify it every time you call the method. This comes in handy when you have overloaded methods that are chained together.
The Old Way
public void Process( string data )
{
Process( data, false );
}
public void Process( string data, bool ignoreWS )
{
Process( data, ignoreWS, null );
}
public void Process( string data, bool ignoreWS, ArrayList moreData )
{
// Actual work done here
}The reason for overloading Process in this way is to avoid always having to include "false, null" in the third method call. Suppose 99% of the time there will not be 'moreData' provided. It seems ridiculous to type and pass null so many times.
// These 3 calls are equivalent
Process( "foo", false, null );
Process( "foo", false );
Process( "foo" );
The New Way
public void Process( string data, bool ignoreWS = false, ArrayList moreData = null )
{
// Actual work done here
}
// Note: data must always be provided because it does not have a default valueNow we have one method instead of three, but the three ways we called Process above are still valid and still equivalent.
ArrayList myArrayList = new ArrayList();
Process( "foo" ); // valid
Process( "foo", true ); // valid
Process( "foo", false, myArrayList ); // valid
Process( "foo", myArrayList ); // Invalid! See next section
Dynamic
The dynamic keyword is new to C# 4.0, and is used to tell the compiler that a variable's type can change or that it is not known until runtime. Think of it as being able to interact with an Object without having to cast it.
dynamic cust = GetCustomer();
cust.FirstName = "foo"; // works as expected
cust.Process(); // works as expected
cust.MissingMethod(); // No method found!Notice we did not need to cast nor declare cust as type Customer. Because we declared it dynamic, the runtime takes over and then searches and sets the FirstName property for us. Now, of course, when you are using a dynamic variable, you are giving up compiler type checking. This means the call cust.MissingMethod() will compile and not fail until runtime. The result of this operation is a RuntimeBinderException because MissingMethod is not defined on the Customer class.
The example above shows how dynamic works when calling methods and properties. Another powerful (and potentially dangerous) feature is being able to reuse variables for different types of data. I'm sure the Python, Ruby, and Perl programmers out there can think of a million ways to take advantage of this, but I've been using C# so long that it just feels "wrong" to me.
dynamic foo = 123;
foo = "bar";OK, so you most likely will not be writing code like the above very often. There may be times, however, when variable reuse can come in handy or clean up a dirty piece of legacy code. One simple case I run into often is constantly having to cast between decimal and double.
decimal foo = GetDecimalValue();
foo = foo / 2.5; // Does not compile
foo = Math.Sqrt(foo); // Does not compile
string bar = foo.ToString("c");The second line does not compile because 2.5 is typed as a double and line 3 does not compile because Math.Sqrt expects a double. Obviously, all you have to do is cast and/or change your variable type, but there may be situations where dynamic makes sense to use.
dynamic foo = GetDecimalValue(); // still returns a decimal
foo = foo / 2.5; // The runtime takes care of this for us
foo = Math.Sqrt(foo); // Again, the DLR works its magic
string bar = foo.ToString("c");
Saturday, May 15, 2010
Convert comma separated string to table column
I have seen a few questions asking how to transform a delimited values into columns, so I thought I would talk about it here. In most cases it is recommended to use a string parser function to split the string; however, today I want to talk about another method. This method takes advantage of the XML data type that was introduced in SQL Server 2005. What is nice about the XML data type is it preserves the document order. The document order is critical because it guarantees the string is kept in the same order when it is converted to XML. Let’s start by creating a sample table with a few rows of data.
DECLARE @t TABLE(
ProductId INT,
ProductName VARCHAR(25),
SupplierId INT,
Descr VARCHAR(50)
)
INSERT INTO @t VALUES (1,'Product1',1,'A1,10in,30in,2lbs');
INSERT INTO @t VALUES (2,'Product2',2,'T6,15in,30in,');
INSERT INTO @t VALUES (3,'Product3',1,'A2,1in,,0.5lbs');
Okay now we have our sample data, let’s talk about our data. The column “Descr” contains 4 attributes of the product. The attributes that we are storing are Type,Length,Height, and Weight. Anyone of these attributes can be null in the string but MUST be represented in the string. For example, “A1,10in,30in,” has a comma at the end because the weight is unknown and a string like “A1,10in,,1lbs” has a empty space for height. If we do not have this space, how could we ever determine which attributes are associated with a particular column? The answer is there would be no real way of knowing with this method or any other method.
Now that we have laid all the ground work, it is time to start building our query. We will start off by creating a CTE (common table expression) making sure to convert our delimited string into valid XML. Below is the first part of our CTE (this is not the complete code).
;WITH cte (ProductId, ProductName,SupplierId,Prod_Attributes)
AS
(
SELECT
[ProductId],
[ProductName],
[SupplierId],
CONVERT(XML,''
+ REPLACE([Descr],',', ' ')
+ ' ') AS Prod_Attributes
FROM @t
)
You may be asking yourself, what does this code do? This CTE code selects all the data from the table @t, but it also converts the delimited string into valid XML. How does it do that? Well let’s break down the code to figure it out.
This is the conversion code of interest:
CONVERT(XML,''
+ REPLACE([Descr],',', ' ')
+ ' ') AS Prod_Attributes
This code takes the input string and uses the replace function to insert the XML tags, so that it has the look and feel of valid xml. For example, the string “A1,10in,30in,5lbs” will be transformed into
A1
10in
30in
5lbs
Note: It should be noted that we are using a comma delimitation. If your delimitation is different, you will need to change the delimiter in the replace function. The delimiter is between the column and the closing XML tag. In the code following, REPLACE([Descr],',', ' . the delimiter is denoted in Red.
Now that the product description values are in a valid XML format, we can easily get the values by using a hardcoded singleton value, as shown below.
;WITH cte (ProductId, ProductName,SupplierId,Prod_Attributes)
AS
(
SELECT
[ProductId],
[ProductName],
[SupplierId],
CONVERT(XML,''
+ REPLACE([Descr],',', ' ')
+ ' ') AS Prod_Attributes
FROM @t
)
SELECT
[ProductID],
[SupplierId],
Prod_Attributes.value('/Product[1]/Attribute[1]','varchar(25)') AS [Type],
Prod_Attributes.value('/Product[1]/Attribute[2]','varchar(25)') AS [Length],
Prod_Attributes.value('/Product[1]/Attribute[3]','varchar(25)') AS [Height],
Prod_Attributes.value('/Product[1]/Attribute[4]','varchar(25)') AS [Weight]
FROM cte
The singleton of each attribute element is denoted by a hardcoded value 1-4. If you have more columns you will need to specify more singletons. Here are the results:
So there you have it. An easy to implement solution to “pivot” or transform delimited values into columns.
**************** Update ********************
I have reworked my code to work with XML special characters. XML special characters are quite problematic but Brad Schulz, http://bradsruminations.blogspot.com/, has come up with a great method to work around the encoding issues. Here is the modified code:
SELECT
[ProductID],
[SupplierId],
NewXML.value('/Product[1]/Attribute[1]','varchar(25)') AS [Type],
NewXML.value('/Product[1]/Attribute[2]','varchar(25)') AS [Length],
NewXML.value('/Product[1]/Attribute[3]','varchar(25)') AS [Height],
NewXML.value('/Product[1]/Attribute[4]','varchar(25)') AS [Weight]
FROM @t t1
CROSS APPLY (SELECT XMLEncoded=(SELECT Descr AS [*] FROM @t t2 WHERE t1.ProductId = t2.[ProductId] FOR XML PATH(''))) EncodeXML
CROSS APPLY (SELECT NewXML=CAST(''+REPLACE(XMLEncoded,',',' ')+' ' AS XML)) CastXML
DECLARE @t TABLE(
ProductId INT,
ProductName VARCHAR(25),
SupplierId INT,
Descr VARCHAR(50)
)
INSERT INTO @t VALUES (1,'Product1',1,'A1,10in,30in,2lbs');
INSERT INTO @t VALUES (2,'Product2',2,'T6,15in,30in,');
INSERT INTO @t VALUES (3,'Product3',1,'A2,1in,,0.5lbs');
Okay now we have our sample data, let’s talk about our data. The column “Descr” contains 4 attributes of the product. The attributes that we are storing are Type,Length,Height, and Weight. Anyone of these attributes can be null in the string but MUST be represented in the string. For example, “A1,10in,30in,” has a comma at the end because the weight is unknown and a string like “A1,10in,,1lbs” has a empty space for height. If we do not have this space, how could we ever determine which attributes are associated with a particular column? The answer is there would be no real way of knowing with this method or any other method.
Now that we have laid all the ground work, it is time to start building our query. We will start off by creating a CTE (common table expression) making sure to convert our delimited string into valid XML. Below is the first part of our CTE (this is not the complete code).
;WITH cte (ProductId, ProductName,SupplierId,Prod_Attributes)
AS
(
SELECT
[ProductId],
[ProductName],
[SupplierId],
CONVERT(XML,'
+ REPLACE([Descr],',', '
+ '
FROM @t
)
You may be asking yourself, what does this code do? This CTE code selects all the data from the table @t, but it also converts the delimited string into valid XML. How does it do that? Well let’s break down the code to figure it out.
This is the conversion code of interest:
CONVERT(XML,'
+ REPLACE([Descr],',', '
+ '
This code takes the input string and uses the replace function to insert the XML tags, so that it has the look and feel of valid xml. For example, the string “A1,10in,30in,5lbs” will be transformed into
A1
10in
30in
5lbs
Note: It should be noted that we are using a comma delimitation. If your delimitation is different, you will need to change the delimiter in the replace function. The delimiter is between the column and the closing XML tag. In the code following, REPLACE([Descr],',', ' . the delimiter is denoted in Red.
Now that the product description values are in a valid XML format, we can easily get the values by using a hardcoded singleton value, as shown below.
;WITH cte (ProductId, ProductName,SupplierId,Prod_Attributes)
AS
(
SELECT
[ProductId],
[ProductName],
[SupplierId],
CONVERT(XML,'
+ REPLACE([Descr],',', '
+ '
FROM @t
)
SELECT
[ProductID],
[SupplierId],
Prod_Attributes.value('/Product[1]/Attribute[1]','varchar(25)') AS [Type],
Prod_Attributes.value('/Product[1]/Attribute[2]','varchar(25)') AS [Length],
Prod_Attributes.value('/Product[1]/Attribute[3]','varchar(25)') AS [Height],
Prod_Attributes.value('/Product[1]/Attribute[4]','varchar(25)') AS [Weight]
FROM cte
The singleton of each attribute element is denoted by a hardcoded value 1-4. If you have more columns you will need to specify more singletons. Here are the results:
So there you have it. An easy to implement solution to “pivot” or transform delimited values into columns.
**************** Update ********************
I have reworked my code to work with XML special characters. XML special characters are quite problematic but Brad Schulz, http://bradsruminations.blogspot.com/, has come up with a great method to work around the encoding issues. Here is the modified code:
SELECT
[ProductID],
[SupplierId],
NewXML.value('/Product[1]/Attribute[1]','varchar(25)') AS [Type],
NewXML.value('/Product[1]/Attribute[2]','varchar(25)') AS [Length],
NewXML.value('/Product[1]/Attribute[3]','varchar(25)') AS [Height],
NewXML.value('/Product[1]/Attribute[4]','varchar(25)') AS [Weight]
FROM @t t1
CROSS APPLY (SELECT XMLEncoded=(SELECT Descr AS [*] FROM @t t2 WHERE t1.ProductId = t2.[ProductId] FOR XML PATH(''))) EncodeXML
CROSS APPLY (SELECT NewXML=CAST('
Subscribe to:
Posts (Atom)