SQL SERVER – Introduction and Explanation to SYNONYM – Helpful T-SQL Feature for Developer

I learned about SYNONYM feature in SQL Server 2005.

DBA have been referencing database objects in four part names. SQL Server 2005 introduces the concept of a synonym. A synonyms is a single-part name which can replace multi part name in SQL Statement. Use of synonyms cuts down typing long multi part server name and can replace it with one synonyms. It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).

Create Synonyms :
USE AdventureWorks;GOCREATE SYNONYM MyLocationFOR AdventureWorks.Production.Location;GO
Use Synonyms :
USE AdventureWorks;GOSELECT TOP 5 *FROM MyLocation;GO
Drop Synonyms :
USE AdventureWorks;GODROP SYNONYM MyLocation;GO
Synonyms can be created on only following objects.

• Assembly (CLR) Stored Procedure
• Assembly (CLR) Table-valued Function
• Assembly (CLR) Scalar Function
• Assembly Aggregate (CLR) Aggregate Functions
• Replication-filter-procedure
• Extended Stored Procedure
• SQL Scalar Function
• SQL Table-valued Function
• SQL Inline-table-valued Function
• SQL Stored Procedure
• View
• Table (User-defined)

Additionally SYNONYMS can be used only to change data of object not the schema of the object. SYNONYMS can be used with only SELECT, UPDATE, INSERT, DELETE, EXECUTE commands.

Following is image demonstrates use of SYNONYMS.

An example of the usefulness of this might be if you had a stored procedure on a Users database that needed to access a Clients table on another production server. Assuming you created the stored procedure in the database Users, you might want to set up a synonym such as the following:
USE Users;GOCREATE SYNONYM ClientsFOR Offsite01.Production.dbo.Clients;GO
Now when writing the stored procedure instead of having to write out that entire alias every time you accessed the table you can just use the alias Clients. Furthermore, if you ever change the location or the name of the production database location all you need to do is modify one synonym instead of having to modify all of the stored procedures which reference the old server.

Dashboard plugin -- switching dashboards

Excellent Dashboard plugin:
http://connect.gxsoftware.com/dashboardplugin/demo/dashboard.html

for asp.net Application
you just need to add MIME Type for .json file
add following line in web.config

  <system.webServer>
    <staticContent>
      <mimeMap fileExtension=".json" mimeType="application/json"/>
    </staticContent>
  </system.webServer> 

ASP.NET 4.0 Client ID Feature

Why Client Ids

Client Ids have always been a problem for us. But now a days, in new age applications, we are moving more towards the client side programming in new Rich Internet applications. Many new technologies and the way of programming have evolved in the last few years to make very rich UI like JQuery, JSon, Dojo.

In DOM, to access a control, client id plays a pivotal role. So Microsoft is also trying to make our life easier by providing the capability to have control over the client id generation which will ensure easy, simple and less error prone RIA development.

Client Ids Earlier

So let's discuss how the ClientIds were generated earlier. First, I will start with normal control, say textbox control or a label. So here the client Ids that are generated were starting with prefix of all the naming containers from top to bottom separated as underscore. And actually this was a good idea to generate the unique id at client side. But as I discussed, ClientIds are the key part of new age development. Let's look at the example for a simple textbox server control. My aspx looks like:

Fig: Normal Page

So from the above picture, we can see that my label and textbox are inside a contentplaceholder. Now let's look at the ClientId:

Fig: View Source - Normal Page

Now here client Id is ctl00_ContentPlaceHolder1_myTextBox. If we go one by one, the ctl00 is the counter, the next one is ID of contentplaceholder and next id of textbox.

So one thing, as you move the control to some other part, the Client Id will get changed as well.

So although we know the ID is going to be unique on the page, but still you don't have any control over it. .NET engine will generate the ClientIds according to its algorithm for you ).

So this is all about of the simple controls. Now lets talk about some data controls, like gridview, listview,dropdown, etc. Here in these controls, what we do is we just bind the datasource to the control. And at runtime based on the data, the number of rows(controls) are generated. So what about the client Ids here. Here also, the Client Ids are being generated in the same way as I already discussed with prefix of rownumber. So let's have a look at the example.

This is my aspx code for GridView. Here I am showing ID, Book Name and Price:

Fig: Page with data control

So in the above example, I have taken a gridview. And in this, I have three labels in different ItemTemplates. Thegridview is in contentplaceholder.

Now look to the ClientIds:

Fig: View Source - Page with data control

You can see the id is like ctl00_ContentPlaceHolder1_gridviewBooks_ctl02_lblID. So here if we go one by one, first the counter the contentplaceholder id again rowcounter generated in sequence for every row by .NET engine to make it unique and last label ID.

So it becomes very uneasy to use.

But as in new era web development, when we doing lots of work at client side, the Client ids become a key part in web development.

Control Client Ids Generation with ASP.NET 4.0

ASP.NET 4.0 provides the power to control Client Ids generation. For that, it provides the new propertyClientIDMode property to handle this. This property enables us to specify how the Client Ids will be generated for the controls. This provides four options:

• AutoID
• Static
• Predictable
• Inherit

We'll discuss them one by one.

AutoID: This property is the same as the earlier version of .NET. Specify this value if you don't want any changes in the Client Id generation from the earlier version as I discussed in ClientIDs in earlier versions.

Static: This means the you want the static id of your control. You should use this property when you know the ID is going to be unique on the page. Here .NET engine will generate the Client Ids as it is, without adding any suffixes or prefixes in it. This mode is mainly used for normal single control. Let's look at the example.

Fig: Normal page with ASP.NET 4.0

Here as you seen in the picture, I set the ClientIDMode for Label AutoID and for TextBox I set it Static. Now let's see the generated Client Ids:

Fig: View Source: Normal page with ASP.NET 4.0

Now if you see the client ID of Label, it is the same as the earlier one because here I set it to Auto.

But for the TextBox, I set it static. So here the Client Id is the same as the ID that we set it on aspx page. This is the beauty of ASP.NET 4.0. So if we set it to static, the .NET engine won't generate a new client id for the control; it will use the ID of the control as Client ID.

Note: One thing needs to be made sure here, if we set the mode to static then there should be no control on the page with the same id because it will have the same client id on the page and it may be disastrous when we access it from Clientside.

Predictable: This is another mode that I liked for the ClientId generation. When you exactly don't know whether the Id is unique on the page or not, then you can use this property. This value enables us to the predict the client ids on the rendered page. When you set mode to this, you need to set some more properties according to your own choice.

Now I will take the example as above. Now here the aspx code would be like:

Fig: Data control page with ASP.NET 4.0

Here one thing is we are using datacontrol, then we cannot set it as static because there are going to be multiple controls generated based on the data.

So here we will be using mode as Predictable so that we can predict what will be the id of the control. We need to set one more property ClientIDRowSuffix here. I set it ID means the ID column.

Now let's look at the generated Client Ids:

Fig: View Source: Data control page with ASP.NET 4.0

Now here if we look at the ClientID, here it is MainContent_gridviewBooks_lblName_1. So if we look at it deeply, then we find that there is no counter like thing. Here we have first id of contentplaceholder, the id of gridview, the id of label, the suffix id that we set. So it's really predictable  and similarly for other rows.

Inherit: This is also value to set to this property. As the name suggests, it means the Id generation of the control will be the same as the parent control. This is the default behavior of the control.

Setting the Property at Various Levels

There are various places where we can set the ClientIDMode property.This can be set at control level, page level as well as application. The behavior will be the same. We can set it at page directive as below:

Fig: ClientIDMode at Page level

To set it at Application level, we need to set it in config file as:

and that will be applied across all the pages in the application.

Fig: ClientIDMode at Application level

Feedback and Suggestions

Feedback is key for improvement. I would request you all to share your feedback and give me some suggestions, which would encourage and help in more and quality writing.

Get All The Site Groups in SharePoint 2010 Using ECMAScript/JavaScript

Introduction

In this article you will see how to get all the site groups in SharePoint 2010 using ECMAScript. I have the following site groups from which I need to get the group name (Navigate to the site, click on Site Actions. Click on Site Settings. In the Users and Permissions section, click on People and Groups. In the left pane, click on Groups).



Steps Involved

1. Navigate to the SharePoint site (I have a site page created in my site where I am going to add the content editor web part).
2. Go to Site Actions, click on Edit Page.
   
   
    
3. Click on Insert tab in the ribbon interface and then click on Web Part button.
   
   
    
4. Select Media and Content in the Categories section and then click on Content Editor.
   
   
    
5. Click on Add.
6. A Content editor web part will be added to the site page.
7. Click on the down arrow and then click on Edit Web Part.
   
   
    
8. Click on "Click here to add new content".
   
   
    
9. Click on Format Text tab in the ribbon interface and then click on HTML drop down.
   
   
    
10. Click on Edit HTML source.
11. HTML source window will pop up.
    
    
     
12. Copy and paste the following script.
    <script language="ecmascript" type="text/ecmascript">
            var groupCollection;
            function getSiteGroups() {
                var clientContext = new SP.ClientContext();
                this.groupCollection = clientContext.get_web().get_siteGroups();            clientContext.load(groupCollection);
                clientContext.executeQueryAsync(Function.createDelegate(this,this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
            }
            function onQuerySucceeded() {
                var groupName = 'Site Groups: \n';
                var groupsEnumerator = this.groupCollection.getEnumerator();
                while (groupsEnumerator.moveNext()) {
                    var group = groupsEnumerator.get_current();
                    groupName += group.get_title() + '\n';
                }
                alert(groupName);
            }
            function onQueryFailed(sender, args) {
                alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
            }</script>    <input id="btnGetSiteGroups" onclick="getSiteGroups()" type="button" value="Get Site Groups" />
    
     
13. Click on Ok.
14. In the ribbon interface click on Save & Close button.
    
    
     
15. In the content editor web part you should find a button named "Get Site Groups", click on that button.
    
    
     
16. An alert will pop up displaying all the site groups as shown in the following.
    
    

Reference

SP.Web.siteGroups Property - http://msdn.microsoft.com/en-us/library/ee554059.aspx 

Summary

Thus in this article you have seen how to get all the site groups in SharePoint 2010 using ECMAScript.

Programmatically sending mail in SharePoint using SPUtility class

To send mails using SharePoint API, we can use SPUtility class sendMail method. For referencehttp://msdn.microsoft.com/en-us/library/microsoft.sharepoint.utilities.sputility.sendemail.aspx

To use this method we will require:

using Microsoft.SharePoint.Utilities;

using System.Collections.Specialized;

You can assign to, cc, bcc, from, subject by creating stringdictionary, like

StringDictionary headers = new StringDictionary();
headers.add("to",strTo);
headers.add("cc",strCC);
headers.add("bcc",strbcc);
headers.add("from",strFrom);
headers.add("subject",strSubject);
headers.add("content-type","text/html");

string emailBody = “Hi<br><br>Sending mails using SPUtility.”;

SPUtility.SendEmail(web, headers, emailBody);

Where web is SPWeb object that represents site from which you want to send mail.

Navigate to Previous Page in ASP.NET

There was a requirement to get the URL of the previous page from which I was redirected to the New page in the Button click event handler of the New Page, so that I can redirect the user to the previous page.

In this article, you will see how to get the URL of the previous page from which you were redirected to the new page.

We have to use UrlReferrer property of Request object to access the previous page URL. The UrlReferrerproperty is used to get information about the URL of the client's previous request that linked to the current URL.

Please follow the below steps for the requirement given above.

Step 1: Creating a New Website: PrvsPageDemo

1. Open Visual Web Developer or Visual Studio 2008.
2. On the File menu, click New Website, in the Dialog box that appears Under VisualStudioInstalled templates, select ASP.NET Website.
3. Then Type the location (Foldername) where you want to create your website such as D:\ PrvsPageDemo in the second location box.
4. Select the language you want to create a website in. Let us select C#.
5. Click OK. This will create a website with the name PrvsPageDemo. This website by default contains a Webpage named Default.aspx.
6. Add 2 more Webpages, namely Management.aspx and Development.aspx page to the website. (Go to View tab -> click on SolutionExplorer -> In the Solution Explorer window, right click on the Project name (PrvsPageDemo) -> Select Add New Item -> In the name Box: type Management.aspx and then click on Add. Similarly add Development.aspx page.)

Step 2: In the Default Page Source View, Add the Following Code under Form Tag.

This is the Default Page.

<asp:Button runat="server" ID="btnBack" Text="Back" onclick="btnBack_Click" />

Go to Default.aspx.cs file and add the following code as shown below:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack) //check if the webpage is loaded for the first time.
        {
            ViewState["PreviousPage"] = 
  Request.UrlReferrer;//Saves the Previous page url in ViewState
        }
    }
    protected void btnBack_Click(object sender, EventArgs e)
    {
        if (ViewState["PreviousPage"] != null) //Check if the ViewState 
      //contains Previous page URL
        {
            Response.Redirect(ViewState["PreviousPage"].ToString());//Redirect to 
  //Previous page by retrieving the PreviousPage Url from ViewState.
        }
    }

Here, in the Default.aspx page, we have added some static text and a Back Button.

In the Default.aspx.cs page, in the PageLoad event, in !IsPostBack section, we are saving the value of theRequest.UrlReferrer in viewState which is the previous page URL and using the same in the Back Button click event handler.

In the Button click, we first check if ViewState contains the previous page URL. If the ViewState contains the previous page URL, then we use the viewstate information to redirect the user back to the previous page.

Step 3: In the Management.aspx Page under Form Tag, Add the Following Code

This is the management Page.

<asp:Button ID="btnDefault" runat="server" Text="Go to Default Page" 
            onclick="btnDefault_Click" />

In the code-behind file (Management.aspx.cs), add the below code:

protected void btnDefault_Click(object sender, EventArgs e)
    {
        Response.Redirect("~/Default.aspx");
    }

Here, when you click the button, User is redirected to Default.aspx page as written in the btnDefault_clickevent handler of the button.

Step 4: In the Development.aspx Page under Form tag, Add the Following Code

This is the Development Page.

<asp:Button ID="btnDefault" runat="server" Text="Go to Default Page" 
            onclick="btnDefault_Click" />

In the code-behind file (Development.aspx.cs), add the below code:

protected void btnDefault_Click(object sender, EventArgs e)
    {
        Response.Redirect("~/Default.aspx");
    }

Here, when you click the button, the user is redirected to Default.aspx page.

Step 5: Press CTRL + F5 to Execute the Default Page

The Webpage (Default.aspx page) appears in the web browser. Click the Back button. Nothing happens.

Now view the Management.aspx page and then click on the button on this page which will redirect you to the Default Page. When you click on Back button on Default.aspx page now, it will redirect you to the Management.aspx page. Similarly try accessing Development.aspx page.

Conclusion

Using Request.UrlReferrer, we can get the Previous page URL of the current request. Previous Page information is available in the Request.UrlReferrer property only if user is redirected to the current page from some other page and is available in the !Page.IsPostBack of Page Load event of the form. Here we useViewState because it can be used if you want to store the values on postback to the same form. ViewState is used for retaining values between multiple requests for the same page. This is the default method that the page uses to preserve page and control property values between round trips.