Timeout exception in stored procedure sql server

How to resolve Timeout exception in Stored Procedure Sql Server.

Using Temp tables.

Today i will be talking about how to improve Stored Procedure performance. I was working on a lengthy stored procedure where some tables are used frequently.

For example tblDoctors, tblPatients and a master table.

In the store procedure i was using these tables around 10 times.

all tables are having around 10 lakh records.

I need to select all records from tblDoctors table based on some key fields and update in patient table or master table.

Likewise i wanted to update around 10 column in each table based on some conditions.

So there were some 10 update and 10 select statements.

So due to that Timeout exception was coming in the stored procedure, Because i was hitting the tables around 10 times and then updating.

So to solve this problem we have added 2 local temporary tables where data was more.

Like #tblDoctors, #tblPatients

Now we will select all records based on some key fields which is used filter those records. Then insert them into Temp tables.

Now the load will be less.

Do your all operations in temp table and at the find Join with actual table and update all the fields from temp to actual tables.

Thanks

How to generate Model Class or Data Object of a Database Table

1. Generate Model Class or Data Object of a Database table

While developing a application, we need to create model classes or data objects and generally those Model classes are the replica of our database tables.

So we create them manually by writing code. 

Today I'm gonna  show you how to auto generate those model classes and use them directly in c#.
you can customize the below code for JAVA classes also.
This article is for generate c# model class from sql server database table.

2. Model Class Generator code.

DECLARE @TableName VARCHAR(MAX) = 'Product' -- Replace 'Product' with your table name

DECLARE @TableSchema VARCHAR(MAX) = 'Production' -- Replace 'Production' with your schema name example-dbo

DECLARE @result varchar(max) = ''

SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)

IF (@TableSchema IS NOT NULL)

BEGIN

    SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13)

END

SET @result = @result + CHAR(9)+ 'public class ' + @TableName + CHAR(13) +CHAR(9)+ '{' + CHAR(13)

SELECT @result = @result + CHAR(13)

    +CHAR(9)+ ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)

FROM

(

SELECT  c.COLUMN_NAME   AS ColumnName

    ,CASE c.DATA_TYPE  

    WHEN 'bigint' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'long?' ELSE 'long' END

    WHEN 'binary' THEN 'Byte[]'

    WHEN 'bit' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'bool?' ELSE 'bool' END           

    WHEN 'char' THEN 'string'

    WHEN 'date' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       

    WHEN 'datetime' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       

    WHEN 'datetime2' THEN 

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       

    WHEN 'datetimeoffset' THEN

        CASE C.IS_NULLABLE

    WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END                                   

    WHEN 'decimal' THEN 

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                   

    WHEN 'float' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'single?' ELSE 'single' END                                    

    WHEN 'image' THEN 'Byte[]'

    WHEN 'int' THEN 

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'int?' ELSE 'int' END

    WHEN 'money' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                               

    WHEN 'nchar' THEN 'string'

    WHEN 'ntext' THEN 'string'

    WHEN 'numeric' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                                           

    WHEN 'nvarchar' THEN 'string'

    WHEN 'real' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'double?' ELSE 'double' END                                                                        

    WHEN 'smalldatetime' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   

    WHEN 'smallint' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'short?' ELSE 'short'END           

    WHEN 'smallmoney' THEN 

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                                                       

    WHEN 'text' THEN 'string'

    WHEN 'time' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END                                                                                   

    WHEN 'timestamp' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   

    WHEN 'tinyint' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'byte?' ELSE 'byte' END                                               

    WHEN 'uniqueidentifier' THEN 'Guid'

    WHEN 'varbinary' THEN 'byte[]'

    WHEN 'varchar' THEN 'string'

    ELSE 'Object'

    END AS ColumnType

    , c.ORDINAL_POSITION

FROM    INFORMATION_SCHEMA.COLUMNS c

WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA 

) t

ORDER BY t.ORDINAL_POSITION

SET @result = @result +CHAR(9) + '}' + CHAR(13)

IF (@TableSchema IS NOT NULL)

BEGIN

    SET @result = @result + CHAR(13) + '}'

END

PRINT @result

The above one is a SQL Code which generates output similar to model class, so that you can copy that and paste it in your C# classes.

Model Class generator code explanation.

It gets the schema information and all the column names using INFORMATION_SCHEMA.COLUMNS of a particular table.

Then there are some switch cases to replace sql data types to C# data types.

So basically the above sql query generates a string similar to Model class.

3. How to use the Model Class Generator

One simple way would to just copy the above and paste it into a new query window and give the table name and schema name.

In my case i have AdventureWorkDB and i want a Model class for Product table.

so i have given as below:

DECLARE @TableName VARCHAR(MAX) = 'Product' -- Replace 'Product' with your table name

DECLARE @TableSchema VARCHAR(MAX) = 'Production' -- Replace 'Production' with your schema name example-dbo

Another best way would be to create a store procedure with the name of "USP_GenerateModelClass" and make table name and schema name as parameter like below:

Create Procedure USP_GenerateModelClass

(

     @tblName varchar(50),

     @schemaName varchar(50)='dbo'

)

as

Begin

DECLARE @TableName VARCHAR(MAX) = @tblName -- Replace 'Product' with your table name

DECLARE @TableSchema VARCHAR(MAX) = @schemaName -- Replace 'Production' with your schema name

DECLARE @result varchar(max) = ''

SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)

IF (@TableSchema IS NOT NULL)

BEGIN

    SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13)

END

SET @result = @result + CHAR(9)+ 'public class ' + @TableName + CHAR(13) +CHAR(9)+ '{' + CHAR(13)

SELECT @result = @result + CHAR(13)

    +CHAR(9)+ ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)

FROM

(

SELECT  c.COLUMN_NAME   AS ColumnName

    ,CASE c.DATA_TYPE  

   
    WHEN 'bigint' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'long?' ELSE 'long' END

    WHEN 'binary' THEN 'Byte[]'

    WHEN 'bit' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'bool?' ELSE 'bool' END           

    WHEN 'char' THEN 'string'

    WHEN 'date' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       

    WHEN 'datetime' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       

    WHEN 'datetime2' THEN 

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       

    WHEN 'datetimeoffset' THEN

        CASE C.IS_NULLABLE

    WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END                                   

    WHEN 'decimal' THEN 

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                   

    WHEN 'float' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'single?' ELSE 'single' END                                    

    WHEN 'image' THEN 'Byte[]'

    WHEN 'int' THEN 

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'int?' ELSE 'int' END

    WHEN 'money' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                               

    WHEN 'nchar' THEN 'string'

    WHEN 'ntext' THEN 'string'

    WHEN 'numeric' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                                           

    WHEN 'nvarchar' THEN 'string'

    WHEN 'real' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'double?' ELSE 'double' END                                                                        

    WHEN 'smalldatetime' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   

    WHEN 'smallint' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'short?' ELSE 'short'END           

    WHEN 'smallmoney' THEN 

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                                                       

    WHEN 'text' THEN 'string'

    WHEN 'time' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END                                                                                   

    WHEN 'timestamp' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   

    WHEN 'tinyint' THEN

        CASE C.IS_NULLABLE

            WHEN 'YES' THEN 'byte?' ELSE 'byte' END                                               

    WHEN 'uniqueidentifier' THEN 'Guid'

    WHEN 'varbinary' THEN 'byte[]'

    WHEN 'varchar' THEN 'string'

    ELSE 'Object'

    END AS ColumnType

    , c.ORDINAL_POSITION

FROM    INFORMATION_SCHEMA.COLUMNS c

WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA 

) t

ORDER BY t.ORDINAL_POSITION

SET @result = @result +CHAR(9) + '}' + CHAR(13)

IF (@TableSchema IS NOT NULL)

BEGIN

    SET @result = @result + CHAR(13) + '}'

END

PRINT @result

END

Now execute the store Procedure like below.

EXEC USP_GenerateModelClass 'Product','Production'

OR 

EXEC USP_GenerateModelClass 'Employee'  //default schema is 'dbo'

And it will return the output as below:

using System;

namespace Production

{

    public class Product

    {

        public int ProductID { get; set; }

        public string Name { get; set; }

        public string ProductNumber { get; set; }

        public int MakeFlag { get; set; }

        public int FinishedGoodsFlag { get; set; }

        public string Color { get; set; }

        public short SafetyStockLevel { get; set; }

        public short ReorderPoint { get; set; }

        public decimal StandardCost { get; set; }

        public decimal ListPrice { get; set; }

        public string Size { get; set; }

        public string SizeUnitMeasureCode { get; set; }

        public string WeightUnitMeasureCode { get; set; }

        public decimal? Weight { get; set; }

        public int DaysToManufacture { get; set; }

        public string ProductLine { get; set; }

        public string Class { get; set; }

        public string Style { get; set; }

        public int? ProductSubcategoryID { get; set; }

        public int? ProductModelID { get; set; }

        public DateTime SellStartDate { get; set; }

        public DateTime? SellEndDate { get; set; }

        public DateTime? DiscontinuedDate { get; set; }

        public Guid rowguid { get; set; }

        public DateTime ModifiedDate { get; set; }

    }

}

That's great, Isn't it. It gave me the output as i wanted.

4. Any Question?

Hope you guys liked my article, Please do write your comments in the comment section.