Redirecting non-www requests to www requests

Redirection of Non-WWW domain to WWW (301 Redirection – permanent) is very important for ranking on search engine like Google.

The Problem is that the Google search consider http://domain.com to be a different domain than http://www.domain.com. so it makes the difference in search engine back links because you have made it with and without www prefix.

It is better to have every link use exactly the same form of your domain. for this purpose, it is common to redirect request from non-www to www.

We can do it in asp.net by many ways but I mentioned 2 solutions below -

1. web.config : 

       It is most common practice to do the redirection from non-www to www because we don’t have the IIS access on shared hosting environment.

Make sure you replace example.com with the name of your domain.

<configuration>
  <system.webServer>
    <rewrite>
      <rules>
        <rule name="Redirect to WWW" stopProcessing="true">
          <match url=".*" />
          <conditions>
            <add input="{HTTP_HOST}" pattern="^example.com$" />
          </conditions>
          <action type="Redirect" url="http://www.example.com/{R:0}"
                  redirectType="Permanent" />
        </rule>
      </rules>
    </rewrite>
  </system.webServer>
</configuration>

2. Global.asax.cs  : 

          If you wish to do it from within your application, use the Application_BeginRequest in Global.asax.cs to intercept the request and do a 301 (permanent) redirection on the url.

 NOTE - you will need to set up the bindings of the site in IIS to accept both the www.domain.com and domain.com host names.

protected void Application_BeginRequest(object sender, EventArgs ev)
{
   string FromHomeURL = "http://www.example.com";
   string ToHomeURL = "http://example.com";
           
   if(HttpContext.Current.Request.Url.ToString().ToLower().Contains(FromHomeURL))
   {
       HttpContext.Current.Response.Status = "301 Moved Permanently";
       HttpContext.Current.Response.AddHeader("Location",
       Request.Url.ToString().ToLower().Replace(FromHomeURL, ToHomeURL));
   }
}

Happy Coding !! 

Read all keys and values from resource file

I have added Resource1.resx file in my project. This reosurce file contains some key - value entries.

Scenario : Suppose you have localized country list and you have stored localized country list in resource file for performance reason. You can store localized list in sql table but if you want to avoid database hit and read it on server side.


This function reads Resource1.resx file and reads all keys of that resource file.

 public static List<string> GetAllResourceKeyStrings()
        {
            List<string> resourceKeyStrings = new List<string>();
             ResourceSet resourceSet = null;
         
            resourceSet = Resource1.ResourceManager.GetResourceSet(CultureInfo.CurrentCulture, true, true);
         
         
            IDictionaryEnumerator Myenumerator = resourceSet.GetEnumerator();
            while (Myenumerator.MoveNext())
            {
                resourceKeyStrings.Add(Myenumerator.Key.ToString());
            }
            return resourceKeyStrings;
        }

Happy Coding!!

Change SQL Server user login account password

Change SQL Server user login account password script:
Sometimes you need to change the password for a particular user in that case following script will help you to change the password.

in following Example :
Login account user name is : dbdevwebuser
Current password is : MyCurrentPwd
New password is : MyNewCurrentPwd

We can use following script to change the users login password.

ALTER LOGIN dbdevwebuser WITH PASSWORD = N'MyNewCurrentPwd' OLD_PASSWORD = 'MyCurrentPwd'

Happy Coding !! 

Reseeding/reset the identity of table.

Generally, on inserting the new data into table we get next seed value for identity column. When a row is deleted, we cannot use that identity value again. We can resolve it by reseeding the identity of table as follows:

1. SELECT * FROM Sample_Data

​Number	​Value
​1	​First
​2	​First
​3	​First
​4	​First
​5	​First
​6	​First
​7	​First

2. DELETE FROM Sample_Data WHERE Number > 4

​Number​	​Value
​1	​​First
​2	​​First
​3	​​First
​4	​​First

3. DECLARE @MaxID INT

SELECT @MaxID=MAX(Number) FROM Sample_Data

DBCC CHECKIDENT('Sample_Data',RESEED,@MaxID)

GO

RESULT:-

Checking identity information: current identity value '16'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

4. INSERT INTO Sample_Data(Value) VALUES('Second')

   Go 3

​​Number	​​Value​
1​	​​​First
2​	​​​First
3​	​​​First
4​	​​​First
5​	​Second
​6	​Second
7​	​Second

Happy Coding!!

Find Most Expensive Queries Using DMV

Find Most Expensive Queries Using Dynamic Management Views(DMV)

When you try to find out the performance of an sql server, this query will help us.

It records how many times that execution plan get re-used by following execution. This will give us the count of the execution. It records the number of logical reads, logical writes, elapsed time etc. all this counter will help to find poor performer while monitoring SQL Server queries.

The query will use the Dynamic Management Views. DMV returns server state information that can be used to monitor the health of the server instance, diagnose problems and performance.

Following is the query which finds out top 10 most expensive queries

SELECT TOP 10 SUBSTRING(qt.TEXT, (qstat.statement_start_offset/2)+1,

  ((CASE qstat.statement_end_offset

  WHEN -1 THEN DATALENGTH(qt.TEXT)

  ELSE qstat.statement_end_offset

  END - qstat.statement_start_offset)/2)+1),

  qstat.execution_count,

  qstat.total_logical_reads, qstat.last_logical_reads,

  qstat.total_logical_writes, qstat.last_logical_writes,

  qstat.total_worker_time,

  qstat.last_worker_time,

  qstat.total_elapsed_time/1000000 total_elapsed_time_in_S,

  qstat.last_elapsed_time/1000000 last_elapsed_time_in_S,

  qstat.last_execution_time,

  qplan.query_plan

FROM sys.dm_exec_query_stats  qstat

CROSS APPLY sys.dm_exec_query_plan(qstat.plan_handle) qplan

CROSS APPLY sys.dm_exec_sql_text(qstat.sql_handle)  qt

ORDER BY qstat.total_logical_reads DESC -- logical reads

-- ORDER BY qstat.total_logical_writes DESC -- logical writes

-- ORDER BY qstat.total_worker_time DESC -- CPU time

Happy Coding !!

Sql Azure : Way Around for Sp_depends in Sql Azure

You can use following query to find out table used in Stored procedures similar to sp_depends.

Sp_depends helps us to provide information where sql object in referred in Views, stored procedure.
sp_depends is not available in sql azure but following query will provide similar functionality.

SELECT ReferencingObjectType = o1.type,
       ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
       ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
       ReferencedObjectType = o2.type
FROM   sys.sql_expression_dependencies ed
       INNER JOIN  sys.objects o1
         ON ed.referencing_id = o1.object_id
       INNER JOIN sys.objects o2
         ON ed.referenced_id = o2.object_id
WHERE o1.type in ('P','TR','V', 'TF') and ed.referenced_entity_name like '<Table Name>'
ORDER BY ReferencingObjectType, ReferencingObject

replace <Table name>  with your actual table name which you want to search


Happy Coding !!

Steps to Create a customize Role for Azure database

Steps to Create a customize Role for Azure database
Download "Azure User Management Console" tool from https://aumc.codeplex.com/

Create a Login
Use “Azure User Management Console”
EG: dbdevwebuser
Create User for login on master
Connect to master database, and execute the following:
CREATE USER dbdevwebuser FOR LOGIN dbdevwebuser WITH DEFAULT_SCHEMA=[dbo]

This has created user “dbdevwebuser” for the login “dbdevwebuser”.
Note: You can keep the login and the user name separate.

Create User for login on the database to be used by application
Connect to application database, and execute the following:
CREATE USER dbdevwebuser FOR LOGIN dbdevwebuser WITH DEFAULT_SCHEMA=[dbo]

Create Customized Role 
Connect to application database, and execute the following:
CREATE ROLE [db_executor] AUTHORIZATION [dbo]
This will create a role by the name “db_executor”

Grant Permissions to Customized Role 
Here I am trying to create a role to grant execute for all stored procedure
GRANT EXECUTE TO [db_executor]

This will grant execute permission to the role “db_executor”
Note: You can “GRANT” more permission to this role, to make it more customized.

Assign Roles to User Created
Assign different roles of your choice to the user
sp_addrolemember @rolename = 'db_executor', @membername = 'dbdevwebuser'
sp_addrolemember @rolename = 'db_datareader', @membername = 'dbdevwebuser'
sp_addrolemember @rolename = 'db_datawriter', @membername = 'dbdevwebuser'

This will assign roles of “db_executor”, “db_datawrited” and “db_datareader” to the user 'dbdevwebuser' on the application database.
NOTE:
1. These kind of roles are usually used when you want your application to execute procedures, read and write data. But do not want them to manipulate any Schema.

Testing working of the User for the Roles Assigned

This user should
1. Execute procedure
2. Select, Insert, Update, Delete from the database
3. Create Temporary tables
4. Select View
This user should not:
1. Create, ALTER, Delete Table
2. Create, ALTER, Delete Procedure
3. Create, ALTER, Delete View
4. Create, ALTER, Delete Function
5. See Content “sp_helptext” for a Procedure

Happy Coding !!