Pages

Thursday, January 23, 2014

Windows Azure: Upload and Download Functionality- upload/download image to blob

Following class is useful to download or upload image from blob. Provide necessary parameters to function.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Blob;
using System.Configuration;
using Microsoft.WindowsAzure;
using System.IO;
using Microsoft.WindowsAzure.ServiceRuntime;

namespace AzureDemo
{
    public class AzureBlobManager
    {
        public string uploadFilesToAzureBlob(FileUpload PostedFile, string FileName, string containerName, Guid userid)
        {
            string blobURLs = string.Empty;
            try
            {
                // Retrieve storage account from connection string.
                CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    ConfigurationManager.ConnectionStrings["StorageConnectionString"].ConnectionString);

                // Create the blob client.
                CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

                // Retrieve a reference to a container.              
                CloudBlobContainer container = blobClient.GetContainerReference(containerName);

                // Create the container if it doesn't already exist.
                container.CreateIfNotExists();

                if (PostedFile.PostedFile.ContentLength > 0)
                {
                    System.IO.Stream inputStream = PostedFile.PostedFile.InputStream;
                    inputStream.Position = 0;

                    byte[] myBinary = new byte[PostedFile.PostedFile.ContentLength];
                    //PostedFile.InputStream.Read(myBinary, 0, (int)PostedFile.ContentLength);
                    using (var binaryReader = new System.IO.BinaryReader(inputStream))
                    {
                        myBinary = binaryReader.ReadBytes(PostedFile.PostedFile.ContentLength);
                    }
                    // Retrieve reference to a blob named "myblob".
                    CloudBlockBlob blockBlob = container.GetBlockBlobReference(string.Concat(userid, "_", PostedFile.FileName));

                    // Create or overwrite the "myblob" blob with contents from a local file.                      
                    //blockBlob.UploadFromStream(PostedFile.PostedFile.InputStream);

                    blockBlob.UploadFromStream(new System.IO.MemoryStream(myBinary));
                    // blockBlob.UploadFromStream(PostedFile.PostedFile.InputStream);

                    blobURLs = blockBlob.Uri.ToString();
                }
            }
            catch (Exception ex)
            {
                //HandleException.HandleExceptionLog(ex);
            }
            return blobURLs;
        }


//Directly reads from blob and sends it to user
        public void DownloadFileFromBlob(string fileName,string containerName)
        {
            CloudStorageAccount account = CloudStorageAccount.Parse(ConfigurationManager.ConnectionStrings["StorageConnectionString"].ConnectionString);

            CloudBlobClient blobClient = account.CreateCloudBlobClient();
            CloudBlobContainer container = blobClient.GetContainerReference(containerName);
            CloudBlockBlob blob = container.GetBlockBlobReference(fileName);
            MemoryStream memStream = new MemoryStream();
            blob.DownloadToStream(memStream);

            HttpContext.Current.Response.ContentType = blob.Properties.ContentType;
            HttpContext.Current.Response.AddHeader("Content-Disposition", "Attachment; filename=" + fileName.ToString());
            HttpContext.Current.Response.AddHeader("Content-Length", blob.Properties.Length.ToString());
            HttpContext.Current.Response.BinaryWrite(memStream.ToArray());
        }
    }
}

Friday, October 25, 2013

IE11 and Windows 8.1 : Solution for __doPostBack and Ajax not working

​Following Steps are performed to fix IE 11: ajax and __dopostback issues:
There are 2 solutions:
1. Site Level Fix:
   step 1: Perform Steps from following links
        http://www.nuget.org/packages/App_BrowsersUpdate/
  
   step 2: In ie.browser add mentioned settings from following link
           http://stackoverflow.com/questions/18009636/crosspostback-ajax-controls-and-asp-net-generated-postbacks-work-for-net-4-5

2. Machine Level Fix:
   Main Source: http://www.hanselman.com/blog/IE10AndIE11AndWindows81AndDoPostBack.aspx
   Step 1: Download patch from following link.
     http://support.microsoft.com/kb/2836939
   Step 2: install patch

Wednesday, October 2, 2013

SQL Server: Insert missing Identity Column Values

When we set identity to a particular column, we dont need to pass value for that column.
SQL Server automatically increments its value.

Suppose if we inserts 10 records and deleted last record. Maximum value will be 9 but when we add new record then SQL server takes next value as 11 not it doesn't take 10. Because we already added record in 10th position and deleted after it.
Example:
CREATE TABLE [dbo].[Student](
[Sno] [int] IDENTITY(1,1) NOT NULL,
[Sname] [nvarchar](50) NULL,
[Saddrs] [nvarchar](50) NULL
) ON [PRIMARY]
GO
insert into student(sname,saddrs) values('Abhi','India')
insert into student(sname,saddrs) values('Sourav','Japan')
insert into student(sname,saddrs) values('david','US')
insert into student(sname,saddrs) values('rahul','UK')
insert into student(sname,saddrs) values('gautam','Australia')
insert into student(sname,saddrs) values('jonathan','Germany')
insert into student(sname,saddrs) values('prasad','Pune')
insert into student(sname,saddrs) values('sandeep','Pune')
insert into student(sname,saddrs) values('john','Pune')
insert into student(sname,saddrs) values('sanjay','Pune')

Now we will delete sno=10
delete from student where sno=10
After Record deleted

Now If we add new record then it will take sno=11 but we want to add sno=10

In this case we need to insert value manually by turning off identity feature off. After inserted manually we need to identity turn on  again.
Syntax:
SET IDENTITY_INSERT TABLE-NAME  ON

Example:
SET IDENTITY_INSERT student  ON

Here
SET IDENTITY_INSERT TABLE-NAME  ON :turns off identity increment. Specify table name on which you want to turn off identity

Your Insert Statement, you need to provide identity column value because in previous command you have turned off autoincrement. Provide missing identity values which you want to add.
Example: Student is table in which sno is identity column.
I have 10 records, i have deleted last record i.e sno=10 now i want to add value for sno=10

My query will be
insert into student(Sno,sname,saddrs) values(10,'sachin','Pune')
After Above Query Result will be

Syntax:
SET IDENTITY_INSERT TABLE-NAME  OFF
Example:
SET IDENTITY_INSERT student  OFF

Here
SET IDENTITY_INSERT TABLE-NAME  OFF:turns on identity increment.
Now your autoincrement will be started, you dont need to provide value for identity column.

If you try to provide value then sql server will give you an error.
Example:
insert into student(Sno,sname,saddrs) values(11,'Rakesh','Pune')

Paging In SQL Server with derived table and CTE

First We will create table

create table RecordTable(col1 int, col2 int)

insert into RecordTable
select 1,1
union all
select 1,2
union all
select 1,3
union all
select 1,4
union all
select 1,5
union all
select 1,6
union all

select 1,7

We will do it through in 2 ways.
1. With Derived Table
2. With Common Table Expression.

Mostly performance is same in both ways, in some scenario it may vary.

1.Paging with Derived Table
select * from(
select col1,col2,
ROW_NUMBER() over( order by col1)  as rowno
from RecordTable)
ctedup
where rowno>minNo and rowno<MaxNo

2. Paging with CTE
;with ctedup as(
select col1,col2,
ROW_NUMBER() over( order by col1)  as rowno
from duplicateRecordTable)
select * from ctedup
where rowno>MinNo and rowno<MaxNo

It will gives you records in between min and max no.

Wednesday, August 28, 2013

Helpful SQL Queries

SQL queries are easily readable if they are in a standard SQL format; this can be achieved with the help of an online SQL formatting tool at:http://www.dpriver.com/pp/sqlformat.htm 

1. Following set of queries can be used to get various values from the database.
  • Find all columns in a Database
SELECT SC.name AS ColumnName,
       ST.name AS TableName
FROM   sys.columns SC
       INNER JOIN sys.tables ST
               ON ST.object_id = SC.object_id
ORDER  BY ST.name,
          SC.name 
  • List all the Functions in a Database

SELECT name AS function_name,
       type_desc
FROM   sys.objects
WHERE  type_desc LIKE '%FUNCTION%'; 


  • List all the Stored Procedures in a Database 
SELECT name
FROM   sys.procedures
ORDER  BY name 

  • List all the views in a Database
SELECT name
FROM   sys.views
ORDER  BY name 
  
  • List all the tables in a Database
SELECT name
FROM   sys.tables
  • Find specific column and related table names
SELECT t.name AS table_name,SCHEMA_NAME(schema_idAS schema_name,c.name AS column_name
FROM sys.tables AS 
t
INNER JOIN sys.columns c ON t.OBJECT_ID 
c.OBJECT_ID
WHERE c.name LIKE 
'%column name%'ORDER BY schema_nametable_name
  • Data type of a column

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE 
    c.object_id = OBJECT_ID('YourTableName')
  • Find specific column and related table names
SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%column name%' ORDER BY schema_name, table_name
2. Queries to check if best practice is followed in SPs
SELECT routine_name,
       routine_definition
FROM   information_schema.routines
WHERE  routine_definition LIKE '%select * from%'
       AND routine_type = 'PROCEDURE' 
Note: Verify for each combination, i.e., select*from, select *from, select* from.
  • Query to identify if any SP uses IN or Not IN

SELECT routine_name,
       routine_definition
FROM   information_schema.routines
WHERE  routine_definition LIKE '% in (%'
       AND routine_type = 'PROCEDURE' 
SELECT routine_name,
       routine_definition
FROM   information_schema.routines
WHERE  routine_definition LIKE '% not in (%'
       AND routine_type = 'PROCEDURE' 
Note: Verify for each combination of IN and Not IN, i.e., ‘IN(‘, ‘ IN (‘, ‘NotIN(‘, ‘Not IN(‘, ‘Not IN (‘.
  • SP without initial comment
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION not LIKE '%================%'
AND ROUTINE_TYPE='PROCEDURE'
3. Following queries can be used to get performace details of the database
  • CPU utilization in seconds
SELECT (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, text,*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
order by CPU_Utilized_in_Seconds desc

  • To get longest running query, memory used & other details  
SELECT Top 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC

  • Last query ran on SQL
SELECT deqs.last_execution_time AS [Time]dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handleAS dest
ORDER BY deqs.last_execution_time DESC
  • Plan cache hit ratio
WITH cte1
     AS (SELECT [dopc].[object_name],
                [dopc].[instance_name],
                [dopc].[counter_name],
                [dopc].[cntr_value],
                [dopc].[cntr_type],
                Row_number()
                  OVER (
                    partition BY [dopc].[object_name], [dopc].[instance_name]
                    ORDER BY [dopc].[counter_name] ) AS r_n
         FROM   [sys].[dm_os_performance_counters] AS dopc
         WHERE  [dopc].[counter_name] LIKE '%Cache Hit Ratio%'
                AND ( [dopc].[object_name] LIKE '%Plan Cache%'
                       OR [dopc].[object_name] LIKE '%Buffer Cache%' )
                AND [dopc].[instance_name] LIKE '%_Total%')
SELECT CONVERT(DECIMAL(16, 2), ( [c].[cntr_value] * 1.0 / [c1].[cntr_value] ) *
                               100.0)
       AS [hit_pct]
FROM   [cte1] AS c
       INNER JOIN [cte1] AS c1
               ON c.[object_name] = c1.[object_name]
                  AND c.[instance_name] = c1.[instance_name]
WHERE  [c].[r_n] = 1
       AND [c1].[r_n] = 2;
  • Plan cache reuse
DECLARE @single DECIMAL(18, 2)
DECLARE @reused DECIMAL(18, 2)
DECLARE @total DECIMAL(18, 2)

SELECT @single = Sum(CASE ( usecounts )
                       WHEN 1 THEN 1
                       ELSE 0
                     END) * 1.0,
       @reused = Sum(CASE ( usecounts )
                       WHEN 1 THEN 0
                       ELSE 1
                     END) * 1.0,
       @total = Count(usecounts) * 1.0
FROM   sys.dm_exec_cached_plans;

SELECT ( @single / @total ) * 100.0; 
  • Identify if a column is reaching limit of the datatype
WITH cte_1
     AS (SELECT Object_name(a.object_id) AS table_name,
                a.name AS columnname,
                CONVERT(BIGINT, Isnull(a.last_value, 0)) AS last_value,
                CASE
                  WHEN b.name = 'tinyint' THEN 255
                  WHEN b.name = 'smallint' THEN 32767
                  WHEN b.name = 'int' THEN 2147483647
                  WHEN b.name = 'bigint' THEN 9223372036854775807
                END    AS dt_value
         FROM   sys.identity_columns a
                INNER JOIN sys.types AS b
                        ON a.system_type_id = b.system_type_id),
     cte_2
     AS (SELECT *,
                CONVERT(NUMERIC(18, 2), ( ( CONVERT(FLOAT, last_value) /
                                            CONVERT(FLOAT, dt_value) ) * 100 ))
                AS 'Percent'
         FROM   cte_1)
SELECT *
FROM   cte_2
ORDER  BY 'Percent' DESC;
  • Number of unused indexes
SELECT db_name() as dbname,
       o.name as tablename,
       i.name as indexname,
       i.index_id,
       user_seeks + user_scans + user_lookups as total_reads,
       user_updates as total_writes,
       (SELECT SUM(p.rows)
          FROM sys.partitions p
         WHERE p.index_id = s.index_id
           AND s.object_id = p.object_id) as number_of_rows,
       s.last_user_lookup,
       s.last_user_scan,
       s.last_user_seek
  FROM sys.indexes i
 INNER JOIN sys.objects o
    ON i.object_id = o.object_id
  LEFT OUTER JOIN sys.dm_db_index_usage_stats s
    ON i.index_id = s.index_id
   AND s.object_id = i.object_id
 WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
   AND ISNULL(s.database_id, DB_ID()) = DB_ID()
   AND (
        isnull(s.last_user_seek, '19000101') < datediff(month, -1, getdate()) AND
        isnull(s.last_user_scan, '19000101') < datediff(month, -1, getdate()) AND
        isnull(s.last_user_lookup, '19000101') < datediff(month, -1, getdate())
       )
 ORDER BY total_reads DESC; 
  • Tables without clustered indexes defined
WITH CTE_1
AS
(
  SELECT db_name() as dbname,
         o.name as tablename,
         (SELECT SUM(p.rows)
            FROM sys.partitions p
           WHERE p.index_id = i.index_id
             AND i.object_id = p.object_id) as number_of_rows
    FROM sys.indexes i
   INNER JOIN sys.objects o
      ON i.object_id = o.object_id
   WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
     AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0
)
SELECT *
  FROM CTE_1
 WHERE number_of_rows > 1000;
  • Possible duplicate indexes

WITH  indexcols
        AS (SELECT object_id AS id,
                index_id AS indid,
                name,
                (SELECT CASE keyno
                          WHEN 0 THEN NULL
                          ELSE colid
                        END AS [data()]
                  FROM sys.sysindexkeys AS k
                  WHERE k.id = i.object_id
                    AND k.indid = i.index_id
                  ORDER BY keyno,
                    colid
                FOR
                 XML PATH('')
                ) AS cols,
                (SELECT CASE keyno
                          WHEN 0 THEN colid
                          ELSE NULL
                        END AS [data()]
                  FROM sys.sysindexkeys AS k
                  WHERE k.id = i.object_id
                    AND k.indid = i.index_id
                  ORDER BY colid
                FOR
                 XML PATH('')
                ) AS inc
              FROM sys.indexes AS i
           )
  SELECT DB_NAME() AS 'DBName',
      OBJECT_SCHEMA_NAME(c1.id) + '.'
        + OBJECT_NAME(c1.id) AS 'TableName',
      c1.name + CASE c1.indid
                  WHEN 1 THEN ' (clustered index)'
                  ELSE ' (nonclustered index)'
                END AS 'IndexName',
      c2.name + CASE c2.indid
                  WHEN 1 THEN ' (clustered index)'
                  ELSE ' (nonclustered index)'
                END AS 'ExactDuplicatedIndexName'
    FROM indexcols AS c1
    INNER JOIN indexcols AS c2
    ON
      c1.id = c2.id
      AND c1.indid < c2.indid
      AND c1.cols = c2.cols
      AND c1.inc = c2.inc;
  • Performance related database settings
DECLARE @Result INT;
DECLARE @High INT; -- For settings you feel need to be right
DECLARE @Med INT; -- For settings you want to know about but arent critical
DECLARE @Low INT; -- For settings that you want flagged but are low importance
SELECT @High = 70,
    @Med = 40,
    @Low = 10;
SELECT @Result = SUM(CASE WHEN [d].[compatibility_level] != [d2].[compatibility_level]
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[collation_name] != [d2].[collation_name]
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[user_access] != 0
                          THEN @Low
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_close_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_shrink_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[state] != 0
                          THEN @Low
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_create_stats_on] != 1
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_update_stats_on] != 1
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_nulls_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_padding_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_warnings_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_arithabort_on] = 1
THEN @High
                          ELSE 0
                     END)
  FROM [sys].[databases] AS d
  CROSS JOIN [sys].[databases] AS d2
  WHERE [d2].[name] = 'master'
    AND ([d].[database_id] = DB_ID()
    AND [d].[state_desc] = 'Online');
  SELECT @Result;