A blog for programing ..

Understanding Parameter Sniffing in SQL Server with Real-world Examples

2025-01-27T15:49:00.000-08:00

Introduction:

Parameter sniffing is a performance-related issue that can affect SQL Server databases. It occurs when the SQL Server's query optimizer generates an execution plan that is optimized for a specific parameter value. However, if this execution plan is cached and reused for other parameter values, it may result in suboptimal performance. In this blog post, we will explore parameter sniffing, understand its impact on query performance, and explore some real-world examples to illustrate the concept.

1. What is Parameter Sniffing?

Parameter sniffing occurs when SQL Server compiles and optimizes a query's execution plan based on the first set of parameter values passed during execution. The compiled plan is then cached and reused for subsequent executions, regardless of the actual parameter values, leading to potential performance issues.

2. The Impact of Parameter Sniffing:

When the compiled plan is optimal for a specific parameter value, it can result in significantly improved performance. However, if the plan is not well-suited for other parameter values, it can lead to the following problems:

a. Performance Degradation: The execution plan may not be efficient for other parameter values, causing slower query execution times.

b. Resource Contention: Suboptimal plans may consume more resources, affecting the overall server performance.

3. Real-world Examples:

Let's examine a couple of scenarios where parameter sniffing can cause performance issues:

Example 1: Suppose we have a table named "Orders" with millions of rows, and we want to retrieve orders placed on a specific date. We create a stored procedure "GetOrdersByDate" with a date parameter "@OrderDate."

The query plan generated for the first execution is optimized for the initial date passed. If this plan is cached and used for subsequent dates, it might not be efficient for other dates, leading to performance degradation.

Example 2: Consider a table named "Products" with a column "CategoryID." We create a stored procedure "GetProductsByCategory" with a parameter "@CategoryID."

The query optimizer generates a plan based on the first category ID passed, optimizing it for that specific category. However, when the plan is reused for other category IDs, it might not perform as well, causing performance issues.

4. Strategies to Mitigate Parameter Sniffing:

To address parameter sniffing, we can use one of the following approaches:

a. Local Variables: Instead of using parameters directly in the query, assign them to local variables within the stored procedure. This way, SQL Server will use the values of these variables during optimization, reducing the chances of parameter-specific plans.

b. Option (RECOMPILE): Adding the "OPTION (RECOMPILE)" query hint to the stored procedure forces SQL Server to recompile the execution plan at each execution, ensuring the plan is optimized for the actual parameter values.

c. Optimization Techniques: Use indexing, statistics, and other optimization techniques to make the query optimizer more adaptive to different parameter values.

Conclusion:

Parameter sniffing is a significant performance consideration in SQL Server. While it can lead to substantial performance gains for specific parameter values, it can also cause performance degradation for others. By understanding parameter sniffing and employing appropriate mitigation strategies, we can ensure our queries perform optimally across various parameter values, leading to a more efficient

How to Use Transactions and Error Handling in SQL Server with TRY...CATCH and XACT_ABORT

2025-01-27T15:39:00.000-08:00

Introduction

Managing transactions in SQL Server is critical to ensure data consistency and handle errors effectively. One common approach is to use TRY...CATCH blocks combined with SET XACT_ABORT ON. This ensures that all runtime errors automatically roll back the transaction, reducing the risk of leaving incomplete or corrupted data.

In this article, we will walk you through the proper way to handle transactions and errors in SQL Server.

Key Concepts

Transactions: Used to group a set of operations into a single unit. Either all operations succeed, or none of them are committed.
TRY...CATCH: A structured way to handle errors in SQL Server.
SET XACT_ABORT: Ensures that if a runtime error occurs, SQL Server automatically rolls back the transaction.

SQL Script for Error Handling with Transactions

Below is a sample script that demonstrates how to handle transactions and errors using BEGIN TRY, BEGIN CATCH, and SET XACT_ABORT in SQL Server.

SET XACT_ABORT ON; -- Automatically rollback on runtime errors

BEGIN TRY
    BEGIN TRANSACTION;

    -- Your SQL operations
    INSERT INTO Table1 (Column1) VALUES ('Value1');
    INSERT INTO Table2 (Column2) VALUES ('Value2');

    -- Commit the transaction if all operations succeed
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Rollback the transaction if an error occurs
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Capture and print the error message
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    PRINT 'Error: ' + @ErrorMessage;
END CATCH;

How It Works

SET XACT_ABORT ON:
- Placed at the start of the script, it ensures that SQL Server rolls back the transaction automatically if a runtime error occurs.
- This minimizes the risk of leaving open transactions that could cause locks.
TRY Block:
- All transactional operations (e.g., INSERT, UPDATE, DELETE) are placed here.
- If no error occurs, the transaction is committed.
CATCH Block:
- If an error occurs, the transaction is rolled back using ROLLBACK TRANSACTION.
- The error is captured with ERROR_MESSAGE() and logged or displayed for troubleshooting.

Why Use This Approach?

Automatic Rollbacks:
- SET XACT_ABORT ON ensures data integrity by automatically rolling back on runtime errors.
Error Logging:
- The CATCH block allows you to log meaningful error messages, improving debugging.
Data Consistency:
- Transactions ensure that either all operations succeed or none of them are applied.

Conclusion

Combining TRY...CATCH with SET XACT_ABORT ON is a robust method for managing errors and ensuring data consistency in SQL Server. This approach simplifies error handling and minimizes the risk of incomplete transactions.

By following this structure, you can build reliable, error-tolerant database operations that ensure your data remains consistent and secure.

Generate Insert,Update,Delete Trigger for all table of a database in SQL SERVER

2015-10-29T04:51:00.001-07:00

When managing databases in SQL Server, ensuring data integrity and tracking changes is crucial. Triggers are powerful tools for automating tasks, logging changes, and maintaining consistent data across tables. This blog demonstrates how to generate Insert, Update, and Delete triggers dynamically for all tables in a database using SQL scripts.

This script allows database administrators and developers to create triggers for audit purposes or business logic enforcement without manually writing them for each table. It also supports a mode to either generate the script for review or execute it directly to create triggers.

SQL Script to Generate Triggers for All Tables

Here’s the SQL script to dynamically generate triggers for every table in a specific schema of a database.

/* 
Parameters 
@Schemaname            - SchemaName to which the table belongs. Default value 'dbo'. 
@DbName                - Database name where audit data will be stored (log table location). 
@Tablename             - TableName for which the triggers will be generated. 
@GenerateScriptOnly    - When passed 1, this will generate the scripts only. 
                         When passed 0, this will create the audit tables and triggers in the current database. 
                         Default value is 1. 
*/

SET NOCOUNT OFF

DECLARE @table NVARCHAR(128)
DECLARE @SQLTrigger NVARCHAR(MAX)
DECLARE @Schemaname SYSNAME = 'dbo' -- schema name
       ,@DbName SYSNAME = 'dbname' -- name of the database in which triggers will be generated
       ,@Tablename SYSNAME
       ,@GenerateScriptOnly BIT = 1 -- 1 for generating scripts only, 0 to create triggers directly.

DECLARE AllTables CURSOR
FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'

OPEN AllTables

FETCH NEXT
FROM AllTables
INTO @table

WHILE @@fetch_status = 0
BEGIN
    SELECT @Tablename = @table

    SELECT @SQLTrigger = '-- =============================================
-- Author:           Programerzone
-- Create date: '+CAST(GETDATE() AS NVARCHAR(256))+'
-- Description: Trigger for ' + @TableName + '
-- =============================================
'

    SELECT @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER TR_' + @Tablename + ' ON dbo.' + @TableName + '
    FOR INSERT, UPDATE, DELETE
    AS
    IF (SELECT COUNT(*) FROM inserted) > 0
    BEGIN
        IF (SELECT COUNT(*) FROM deleted) > 0
        BEGIN
            -- Update!
            INSERT INTO ' + @DbName + '.' + @Schemaname + '.' + @Tablename + ' SELECT *,''Update'' FROM inserted
        END
        ELSE
        BEGIN
            -- Insert!
            INSERT INTO ' + @DbName + '.' + @Schemaname + '.' + @Tablename + ' SELECT *,''Insert'' FROM inserted
        END
    END
    ELSE
    BEGIN
        -- Delete!
        INSERT INTO ' + @DbName + '.' + @Schemaname + '.' + @Tablename + ' SELECT *,''Delete'' FROM deleted
    END'

    IF @GenerateScriptOnly = 1
    BEGIN
        PRINT @SQLTrigger
        PRINT 'GO'
    END
    ELSE
    BEGIN
        PRINT 'Creating Audit Trigger TR_' + @Tablename + ' for ' + @Schemaname + '.' + @Tablename

        EXEC (@SQLTrigger)

        PRINT 'Trigger ' + @Schemaname + '.' + 'TR_' + @Tablename + ' Created successfully'
    END

    FETCH NEXT
    FROM AllTables
    INTO @table
END

CLOSE AllTables
DEALLOCATE AllTables

Explanation of the Script

Parameters:

@Schemaname: The schema of the table (default: dbo).

@DbName: The database name where log/audit tables exist or will be created.

@GenerateScriptOnly: If set to 1, it generates the SQL scripts for the triggers. If set to 0, it directly creates the triggers.

Cursor: The script uses a cursor to loop through all the tables in the schema and dynamically create SQL trigger scripts for each table.

Trigger Logic:

Insert Operation: Data from the inserted table is logged into the audit table with an "Insert" operation type.

Update Operation: When records exist in both inserted and deleted tables, it's treated as an update. Both old and new values can be captured.

Delete Operation: Data from the deleted table is logged into the audit table with a "Delete" operation type.

Benefits of Using This Script

1. Automation: Saves time by generating triggers for all tables automatically.

2. Customization: You can modify the script to add specific logic or change audit table structure.

3. Flexibility: Option to generate scripts only or directly create triggers.

Use Cases

Audit Logging: Track all changes made to tables for audit purposes.

Data Backup: Log every change to maintain a history of data changes.

Debugging: Monitor database changes during application testing.

Conclusion

This script is a practical way to automate the creation of triggers across multiple tables in a database. Whether you need triggers for audit logging, data change monitoring, or enforcing business rules, this script offers a flexible solution. By leveraging the @GenerateScriptOnly parameter, you can review the generated SQL before execution or create triggers directly.

Feel free to customize this script according to your needs and ensure you test it in a development environment before applying it to production.

Connect To Remote Database Without A Linked Server using OPENDATASOURCE In SQL SERVER

2015-10-29T02:42:00.000-07:00

According to MSDN, OPENDATASOURCE Provides ad hoc connection information as part of a four-part object name without using a linked server name.

The syntax is:- OPENDATASOURCE ( provider_name, init_string ) click here to know more about this.First You need to enable Ad Hoc Distributed Queries component of SQL Server.

sp_configure 'show advanced options', 1;

RECONFIGURE;
GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

Then also enable Allow Inprocess property of SQL SERVER NATIVE CLIENT SQLNCL from Server Objects/Linked Server/Provider in SSMS.Then run your sql query as

SELECT *

FROM OPENDATASOURCE('SQLNCLI', 'Data Source=servername;user id=username;password=password')

.[databasename].schemaname.tablename

OR, you can pass dyanamic parameter by using dyanamic sql query as

DECLARE @server nvarchar(256)='servername'

EXEC ('

SELECT *

FROM OPENDATASOURCE(''SQLNCLI'', ''Data Source='+@server+';user id=username;password=password'')

.[databasename].schemaname.tablename')

Using the OpenDataSource function is the same as using a four-part name to access a Linked Server, except the OpenDataSource() function defines the link within the function instead of referencing a predefined linked server. While defining the link in code bypasses the linked server requirement, if the link location changes, then the change will affect every query that uses OpenDataSource(). In addition, OpenDataSource() will not accept variables as parameters.

List all columns of specified table in a database in SQL SERVER

2015-10-28T23:08:00.001-07:00

SELECT isc.COLUMN_NAME

,DATA_TYPE

,CHARACTER_MAXIMUM_LENGTH

,c.is_identity

,c.is_nullable

,CASE

WHEN ccu.COLUMN_NAME IS NULL

THEN 0

ELSE 1

END AS is_primary

FROM INFORMATION_SCHEMA.COLUMNS isc

INNER JOIN [sys].[columns] c ON isc.COLUMN_NAME = c.NAME

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.TABLE_NAME = isc.TABLE_NAME

AND tc.CONSTRAINT_TYPE = 'Primary Key'

LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name

AND ccu.COLUMN_NAME = isc.COLUMN_NAME

WHERE isc.TABLE_NAME = 'aspnet_users'

AND c.OBJECT_ID = OBJECT_ID(isc.TABLE_NAME)

The output is:-

Pad a string with leading zeros in SQL Server

2015-10-27T23:29:00.004-07:00

The problem is:-

I have a string that is up to 5 characters long when its first created in SQL Server 2008 R2.

I would like to pad it with leading zeros, so if its original value was '1' then new value would be '00001' Or if its original value was '23' the new value is '00023' OR if its original value is '12345' then new value is the same as original value.

Solution is:-

If the field is already a string, this will work

 SELECT RIGHT('00000'+ISNULL(field,''),5)

If you want nulls to show as '00000'

It might be an integer -- then you would want

 SELECT RIGHT('00000'+CAST(field AS VARCHAR(5)),5)

OR

SELECT REPLACE(STR(field ,5),' ','0')
Reference taken from StackOverFlow.

CRUD(Insert, Update, Select, Delete) stored procedure generator for SQL SERVER

2015-09-29T01:19:00.001-07:00

Today, I am going to share you the script that generate CRUD script that create four stored procedure of any table for insert, update, select and delete.You need to have a identity column on a table to work this SQL script properly.You need to pass name of the table that you want to create stored procedure in @InsertSelectblName variable and @IsExecute is need to set 1 to execute this generated script directly and 0 to print only, Which is set 0(false) by default.

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

-- ================================================

-- CRUD script generater writtern by Kamal Khanal

-- http://programerzone.blogspot.com

-- ================================================

-- EXEC [dbo].[usp_GenerateCRUD] 'HumanResources.Employee','Kamal Khanal' -- table name and author name

-- EXEC [dbo].[usp_GenerateCRUD] 'HumanResources.Employee','Kamal Khanal',0 --table name, author name with nolock hint

-- EXEC [dbo].[usp_GenerateCRUD] 'HumanResources.Employee','Kamal Khanal',0,1 --table name, author name with nolock hint and execute

-- EXEC [dbo].[usp_GenerateCRUD] 'HumanResources.Employee_Temporal','Kamal Khanal'

CREATE PROCEDURE [dbo].[usp_GenerateCRUD]

@TableName NVARCHAR(256), --table name

@AuthorName NVARCHAR(256), -- author name

@IsAddNoLockHint BIT = 1, -- add nolock hint to select default 1

@IsExecute BIT = 0 -- execute or print only default 0

BEGIN

SET NOCOUNT ON;

PRINT '-- ================================================';

PRINT '-- CRUD script generater writtern by Kamal Khanal';

PRINT '-- http://programerzone.blogspot.com';

PRINT '-- ================================================';

PRINT '

' ;

DECLARE @SchemaName sysname = 'dbo',

@InsertSelectblName NVARCHAR(256),

@NolockHint NVARCHAR(50) = N'';

IF (ISNULL(@TableName, '') <> '')

BEGIN

IF CHARINDEX('.', @TableName) > 0

BEGIN

SET @SchemaName = REPLACE(REPLACE(SUBSTRING(@TableName, 0, CHARINDEX('.', @TableName)), '[', ''), ']', '');

SET @TableName

= REPLACE(

REPLACE(SUBSTRING(@TableName, CHARINDEX('.', @TableName) + 1, LEN(@TableName)), '[', ''),

']',

);

END;

ELSE

SET @TableName = REPLACE(REPLACE(@TableName, '[', ''), ']', '');

END;

IF NOT EXISTS

(

SELECT 1

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = @SchemaName

AND TABLE_NAME = @TableName

)

BEGIN

RAISERROR('Table may not exists.Please provide table name with schema name eg. schemaname.tablename', 16, 1);

RETURN;

END;

SELECT @InsertSelectblName = @TableName; --name of the table to generated crud script

IF (@IsAddNoLockHint = 1)

SET @NolockHint = N'(NOLOCK)';

IF OBJECT_ID('tempdb..#tmptablcol') IS NOT NULL

DROP TABLE #tmptablcol;

SELECT c.COLUMN_NAME,

DATA_TYPE,

CHARACTER_MAXIMUM_LENGTH,

IS_NULLABLE,

ISNULL(is_identity, 0) AS is_identity,

ISNULL(is_computed, 0) AS is_computed,

ISNULL(generated_always_type,0) AS generated_always_type,

c.DATETIME_PRECISION AS scale,

c.NUMERIC_PRECISION AS NPRECISION,

c.NUMERIC_SCALE AS Nscale

INTO #tmptablcol

FROM INFORMATION_SCHEMA.COLUMNS c

LEFT JOIN

(

SELECT name,

is_identity,

is_computed,

generated_always_type

FROM sys.columns

WHERE object_id = OBJECT_ID('' + @SchemaName + '' + '.' + '' + @InsertSelectblName)

) const

ON const.name = c.COLUMN_NAME

WHERE c.TABLE_SCHEMA = @SchemaName

AND c.TABLE_NAME = @InsertSelectblName;

DECLARE @column_name NVARCHAR(256),

@data_type NVARCHAR(256),

@character_maximum_length INT,

@is_nullable NCHAR(5),

@is_identity BIT,

@is_computed BIT,

@generated_always_type TINYINT,

@scale INT,

@NPRECISION INT,

@Nscale INT;

DECLARE @insertcolumn_sql NVARCHAR(MAX) = N'';

DECLARE @UpdateSelectpdatecolumn_sql NVARCHAR(MAX) = N'';

DECLARE @select_sql NVARCHAR(MAX) = N'';

DECLARE @insert_sql NVARCHAR(MAX) = N'';

DECLARE @insertupdate_sql NVARCHAR(MAX) = N'';

DECLARE @selectlist_sql NVARCHAR(MAX) = N'';

DECLARE @update_sql NVARCHAR(MAX) = N'';

DECLARE @delete_sql NVARCHAR(MAX) = N'';

DECLARE @identity_col NVARCHAR(256) = N'';

DECLARE @where_col NVARCHAR(256) = N'';

DECLARE @IsNumeric BIT = 0;

DECLARE db_cursor CURSOR FOR SELECT * FROM #tmptablcol;

OPEN db_cursor;

FETCH NEXT FROM db_cursor

INTO @column_name,

@data_type,

@character_maximum_length,

@is_nullable,

@is_identity,

@is_computed,

@generated_always_type,

@scale,

@NPRECISION,

@Nscale;

WHILE @@FETCH_STATUS = 0

BEGIN

IF (@is_identity = 1)

BEGIN

SELECT @identity_col = @column_name,

@where_col = @column_name;

SELECT @UpdateSelectpdatecolumn_sql += N'

@' + @column_name + N' ' + @data_type;

IF (@character_maximum_length IS NOT NULL)

SELECT @UpdateSelectpdatecolumn_sql += N'(' + CAST(@character_maximum_length AS NVARCHAR) + N')';

SELECT @UpdateSelectpdatecolumn_sql += N' = NULL,';

IF (@data_type IN ( 'bigint', 'int', 'smallint', 'tinyint', 'decimal', 'numeric', 'money', 'smallmoney',

'float', 'real'

)

SET @IsNumeric = 1;

END;

ELSE

BEGIN

IF (ISNULL(@where_col, '') = '' AND @is_computed = 0 AND @generated_always_type =0)

BEGIN

SELECT @where_col = @column_name;

SELECT @UpdateSelectpdatecolumn_sql += N'

@' + @column_name + N' ' + @data_type;

IF (@character_maximum_length IS NOT NULL)

SELECT @UpdateSelectpdatecolumn_sql += N'(' + CAST(@character_maximum_length AS NVARCHAR) + N')';

SELECT @UpdateSelectpdatecolumn_sql += N' = NULL,';

IF (@data_type IN ( 'bigint', 'int', 'smallint', 'tinyint', 'decimal', 'numeric', 'money',

'smallmoney', 'float', 'real'

)

SET @IsNumeric = 1;

END;

IF (@is_computed = 0 AND @generated_always_type =0)

BEGIN

IF (

(

ISNULL(@scale, 0) = 0

OR @data_type = 'datetime'

)

AND @data_type NOT IN ( 'datetime2', 'decimal' )

)

SELECT @insertcolumn_sql += N'

@' + @column_name + N' ' + @data_type;

ELSE IF (@data_type = 'decimal')

SELECT @insertcolumn_sql += N'

@' + @column_name + N' ' + @data_type + N'(' + CAST(@NPRECISION AS NVARCHAR) + N','

+ CAST(@Nscale AS NVARCHAR) + N')';

ELSE

SELECT @insertcolumn_sql += N'

@' + @column_name + N' ' + @data_type + N'(' + CAST(@scale AS NVARCHAR) + N')';

IF (@data_type <> 'hierarchyid' AND @character_maximum_length IS NOT NULL)

SELECT @insertcolumn_sql += N'(' + CASE

WHEN @character_maximum_length > 0 THEN

CAST(@character_maximum_length AS NVARCHAR)

ELSE

'MAX'

END + N')';

IF (@is_nullable = 'YES')

SELECT @insertcolumn_sql += N' = NULL,';

ELSE

SELECT @insertcolumn_sql += N',';

END;

FETCH NEXT FROM db_cursor

INTO @column_name,

@data_type,

@character_maximum_length,

@is_nullable,

@is_identity,

@is_computed,

@generated_always_type,

@scale,

@NPRECISION,

@Nscale;

END;

CLOSE db_cursor;

DEALLOCATE db_cursor;

SELECT @insertcolumn_sql = SUBSTRING(@insertcolumn_sql, 1, LEN(@insertcolumn_sql) - 1);

DECLARE @GetSelect NVARCHAR(MAX);

DECLARE @InsertSelect NVARCHAR(MAX);

DECLARE @InsertSelectVal NVARCHAR(MAX);

DECLARE @UpdateSelect NVARCHAR(MAX);

SELECT @GetSelect = COALESCE(@GetSelect + ',', '') + N'[' + COLUMN_NAME + N']'

FROM #tmptablcol;

SELECT @InsertSelect = COALESCE(@InsertSelect + ',', '') + N'[' + COLUMN_NAME + N']'

FROM #tmptablcol

WHERE is_identity = 0

AND is_computed = 0

AND generated_always_type=0

SELECT @InsertSelectVal = COALESCE(@InsertSelectVal + ',', '') + N'@' + COLUMN_NAME

FROM #tmptablcol

WHERE is_identity = 0

AND is_computed = 0

AND generated_always_type=0

SELECT @UpdateSelect = COALESCE(@UpdateSelect + ',', '') + N'[' + COLUMN_NAME + N'] = @' + COLUMN_NAME

FROM #tmptablcol

WHERE is_identity = 0

AND is_computed = 0

AND generated_always_type=0

SELECT @insert_sql = N'-- =============================================

-- Author: ' + @AuthorName + N'

-- Create date: ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR(25)) + N'

-- Description: Add data to ' + +@InsertSelectblName + N'

-- =============================================';

SELECT @insert_sql += N'

CREATE PROCEDURE [' + @SchemaName + N'].[usp_Add' + @InsertSelectblName + N']

' + @insertcolumn_sql + N',

@Output INT OUTPUT

BEGIN

SET NOCOUNT ON;

SELECT @Output = 0

INSERT INTO ' + N'[' + @SchemaName + N'].[' + @InsertSelectblName + N']' + N'(' + @InsertSelect

+ N')

VALUES(' + @InsertSelectVal + N')' + N'

IF @@ROWCOUNT>0

SELECT @Output = 1

END

' ;

SELECT @update_sql += N'-- =============================================

-- Author: ' + @AuthorName + N'

-- Create date: ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR(25)) + N'

-- Description: update data to ' + +@InsertSelectblName + N'

-- =============================================';

SELECT @update_sql += N'

CREATE PROCEDURE [' + @SchemaName + N'].[usp_Update' + @InsertSelectblName + N']

' + IIF(@identity_col <> '', @UpdateSelectpdatecolumn_sql, '') + @insertcolumn_sql

+ N',

@Output INT OUTPUT

BEGIN

SET NOCOUNT ON;

SELECT @Output = 0

UPDATE ' + N'[' + @SchemaName + N'].[' + @InsertSelectblName + N']' + N' SET ' + @UpdateSelect

+ N'

WHERE [' + @where_col + N'] =@' + @where_col

+ N'

IF @@ROWCOUNT>0

SELECT @Output = 1

END

' ;

SELECT @select_sql += N'-- =============================================

-- Author: ' + @AuthorName + N'

-- Create date: ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR(25)) + N'

-- Description: Get data from ' + +@InsertSelectblName + N'

-- =============================================';

SELECT @select_sql += N'

CREATE PROCEDURE [' + @SchemaName + N'].[usp_Get' + @InsertSelectblName + N']

' + SUBSTRING(@UpdateSelectpdatecolumn_sql, 1, LEN(@UpdateSelectpdatecolumn_sql) - 1)

+ N'

BEGIN

SET NOCOUNT ON;

SELECT ' + @GetSelect + N' FROM [' + @SchemaName + N'].[' + @InsertSelectblName + N']' + @NolockHint

+ N'

WHERE ([' + @where_col + N'] =@' + @where_col + N' OR @' + @where_col + N' IS NULL)' + N'

END

' ;

SELECT @delete_sql += N'-- =============================================

-- Author: ' + @AuthorName + N'

-- Create date: ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR(25)) + N'

-- Description: Delete data from ' + +@InsertSelectblName + N'

-- =============================================';

SELECT @delete_sql += N'

CREATE PROCEDURE [' + @SchemaName + N'].[usp_Delete' + @InsertSelectblName + N']

' + SUBSTRING(@UpdateSelectpdatecolumn_sql, 1, LEN(@UpdateSelectpdatecolumn_sql) - 1)

+ N',

@Output INT OUTPUT

BEGIN

SET NOCOUNT ON;

SELECT @Output = 0

DELETE FROM ' + N'[' + @SchemaName + N'].[' + @InsertSelectblName + N']' + N'

WHERE [' + @where_col + N'] =@' + @where_col

+ N'

IF @@ROWCOUNT>0

SELECT @Output = 1

END

' ;

SELECT @insertupdate_sql = N'-- =============================================

-- Author: ' + @AuthorName + N'

-- Create date: ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR(25)) + N'

-- Description: Add Update data to ' + +@InsertSelectblName + N'

-- =============================================';

SELECT @insertupdate_sql += N'

CREATE PROCEDURE [' + @SchemaName + N'].[usp_AddUpdate' + @InsertSelectblName + N']

' + IIF(@identity_col <> '', @UpdateSelectpdatecolumn_sql, '') + @insertcolumn_sql

+ N',

@Output INT OUTPUT

BEGIN

SET NOCOUNT ON;

SELECT @Output = 0

IF(@' + @where_col + N' = ' + CASE @IsNumeric

WHEN 1 THEN

'0'

ELSE

''''''

END + N')

BEGIN

INSERT INTO ' + N'[' + @SchemaName + N'].[' + @InsertSelectblName + N']' + N'(' + @InsertSelect

+ N')

VALUES(' + @InsertSelectVal + N')'

+ N'

SELECT @Output = 1

END

ELSE

BEGIN

UPDATE ' + N'[' + @SchemaName + N'].[' + @InsertSelectblName + N']' + N' SET ' + @UpdateSelect

+ N'

WHERE [' + @where_col + N'] =@' + @where_col

+ N'

SELECT @Output = 2

END

' ;

SELECT @selectlist_sql += N'-- =============================================

-- Author: ' + @AuthorName + N'

-- Create date: ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR(25)) + N'

-- Description: Get data from ' + +@InsertSelectblName

+ N' with pagination

-- =============================================';

SELECT @selectlist_sql += N'

CREATE PROCEDURE [' + @SchemaName + N'].[usp_Get' + @InsertSelectblName

+ N'List]

@offset INT,

@limit INT

-- extra parameter as needed

BEGIN

SET NOCOUNT ON;

DECLARE @RowTotal INT

SELECT @RowTotal = COUNT(1) FROM [' + @SchemaName + N'].[' + @InsertSelectblName + N']' + @NolockHint

+ N'

-- WHERE extra condition here

SELECT @RowTotal AS RowTotal, ' + @GetSelect + N' FROM [' + @SchemaName + N'].[' + @InsertSelectblName + N']'

+ @NolockHint + N'

-- WHERE extra condition here

ORDER BY [' + @where_col + N'] OFFSET (@offset-1) ROWS FETCH NEXT @limit ROWS ONLY

END

' ;

IF (@IsExecute = 1)

BEGIN

EXEC (@insert_sql);

EXEC (@update_sql);

EXEC (@select_sql);

EXEC (@delete_sql);

EXEC (@insertupdate_sql);

EXEC (@selectlist_sql);

END;

ELSE

BEGIN

PRINT @insert_sql;

PRINT @update_sql;

PRINT @select_sql;

PRINT @delete_sql;

PRINT @insertupdate_sql;

PRINT @selectlist_sql;

END;

Just copy above sql script or download script file here

https://github.com/jdcomplex/CRUD-Stored-Procedures-Generator-For-SQL-SERVER

Data Insertion In any Table of Database

2015-02-02T08:31:00.000-08:00

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using Microsoft.Win32;

namespace DataMigration

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

string dbName, tblName;

int cLeft = 1;

string conString = string.Empty;

private void comboBox1_SelectedIndexChanged_1(object sender, EventArgs e)

{

try

{

//comboBox1.Items.Clear();

dbName = (string)comboBox1.SelectedItem;

comboBox2.DataSource = GetTableList(dbName);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

private void Form1_Load(object sender, EventArgs e)

{

try

{

comboBox3.DataSource = GetServerList();

//string compName = Environment.MachineName;

//string server = (string)comboBox3.SelectedItem;

//conString = "server=" + compName + "\\" + server + ";integrated security=true";

//comboBox1.DataSource = GetDatabaseList();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

public List<string> GetServerList()

{

List<string> list = new List<string>();

RegistryKey baseKey = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry64);

RegistryKey key = baseKey.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL");

foreach (string s in key.GetValueNames())

{

list.Add(s);

}

key.Close();

baseKey.Close();

return list;

}

public List<string> GetDatabaseList()

{

List<string> list = new List<string>();

// Open connection to the database

//string conString = "server=ICECUBES\\SQLSERVER2008;uid=sa;pwd=sa123";

using (SqlConnection con = new SqlConnection(conString))

{

con.Open();

// Set up a command with the given query and associate

// this with the current connection.

using (SqlCommand cmd = new SqlCommand("select * from sys.databases where database_id not in (1,2,3,4)", con))

{

using (IDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

{

list.Add(dr[0].ToString());

}

return list;

}

private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)

{

try

{

tblName = (string)comboBox2.SelectedItem;

// comboBox3.DataSource = GetColumnList(tblName);

AddNewTextBox(GetColumnList(tblName));

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

public List<string> GetTableList(string databasename)

{

List<string> list = new List<string>();

// Open connection to the database

// string conString = "server=ICECUBES\\SQLSERVER2008;uid=sa;pwd=sa123";

using (SqlConnection con = new SqlConnection(conString))

{

con.Open();

// Set up a command with the given query and associate

// this with the current connection.

using (SqlCommand cmd = new SqlCommand("select table_name from " + databasename + ".INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'", con))

{

using (IDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

{

list.Add(dr[0].ToString());

}

return list;

}

public List<string> GetColumnList(string tblName)

{

List<string> list = new List<string>();

// Open connection to the database

// string conString = "server=ICECUBES\\SQLSERVER2008;uid=sa;pwd=sa123";

using (SqlConnection con = new SqlConnection(conString))

{

con.Open();

// Set up a command with the given query and associate

// this with the current connection.

using (SqlCommand cmd = new SqlCommand("USE " + dbName + " select name from " + dbName + ".sys.columns WHERE is_identity = 0 and object_id=object_id('" + tblName + "')", con))

{

using (IDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

{

list.Add(dr[0].ToString());

}

return list;

}

public void AddNewTextBox(List<string> Columns)

{

cLeft = 1;

panel1.Controls.Clear();

List<string> col = Columns;

int count = col.Count;

for (int i = 0; i < count; i++)

{

System.Windows.Forms.TextBox txt = new System.Windows.Forms.TextBox();

panel1.Controls.Add(txt);

txt.Top = cLeft + 25;

txt.Left = 150;

txt.Name = col[i];

System.Windows.Forms.Label lbl = new System.Windows.Forms.Label();

panel1.Controls.Add(lbl);

lbl.Top = cLeft + 25;

lbl.Left = 40;

lbl.Text = col[i];

cLeft = cLeft + 25;

}

private void button1_Click(object sender, EventArgs e)

{

//AddNewTextBox();

}

private void panel1_Paint(object sender, PaintEventArgs e)

{

}

private void button1_Click_1(object sender, EventArgs e)

{

List<PairValue> obj = new List<PairValue>();

foreach (Control ctr in panel1.Controls)

{

if (ctr is TextBox)

{

PairValue p = new PairValue();

p.value = ((TextBox)ctr).Text;

p.name = ((TextBox)ctr).Name;

obj.Add(p);

}

string query1 = string.Empty;

string query2 = string.Empty;

foreach (PairValue p in obj)

{

query1 += p.name + ",";

query2 += "'" + p.value + "'" + ",";

}

query1 = query1.Substring(0, query1.LastIndexOf(","));

query2 = query2.Substring(0, query2.LastIndexOf(","));

string sql = "INSERT INTO " + dbName + ".dbo." + tblName + "(" + query1 + ") VALUES(" + query2 + ")";

//string conString = "server=ICECUBES\\SQLSERVER2008;uid=sa;pwd=sa123";

SqlConnection con = new SqlConnection(conString);

try

{

SqlCommand cmd = new SqlCommand(sql, con);

if (con.State == ConnectionState.Closed)

{

con.Open();

}

int temp = cmd.ExecuteNonQuery();

if (temp > 0)

{

MessageBox.Show("Data Are Added Successfully");

}

else

{

MessageBox.Show("Try Again");

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

con.Close();

}

private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)

{

try

{

string compName = Environment.MachineName;

string server = (string)comboBox3.SelectedItem;

conString = "server=" + compName + "\\" + server + ";integrated security=true";

comboBox1.DataSource = GetDatabaseList();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

public class PairValue

{

public string name { get; set; }

public string value { get; set; }

}

}

To Download Source Code Of Project Click Here

Coalesce in sql server to concatenate many rows into a single string

2015-01-08T23:22:00.000-08:00

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

DECLARE @str NVARCHAR(256)

SELECT @str = COALESCE(@str + ', ', '') + cast(CatID AS NVARCHAR)

FROM TableName

WHERE ID = 1

SELECT @str

END

How to truncate all table in a SQL SERVER database

2014-12-24T21:41:00.001-08:00

SET NOCOUNT ON;

DECLARE @TBL TABLE (

rowNum INT identity(1, 1)

,table_name NVARCHAR(100)

)

DECLARE @counter INT

,@count INT

INSERT INTO @TBL

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

SELECT @count = @@rowcount

,@counter = 1

SELECT *

FROM @TBL

WHILE (@counter <= @count)

BEGIN

DECLARE @tblName NVARCHAR(100)

,@sql1 NVARCHAR(256)

SELECT @tblName = table_name

FROM @TBL

WHERE rowNum = @counter

SET @sql1 = 'TRUNCATE TABLE ' + @tblName

PRINT (@sql1)

PRINT 'GO'

SET @counter = @counter + 1

END

Sql query to delete constraint and alter column of table in sql server

2014-12-11T03:53:00.001-08:00

DECLARE @TBL1 TABLE (

rowNum INT identity(1, 1)

,table_name NVARCHAR(200)

,column_name NVARCHAR(100)

)

DECLARE @counter1 INT

,@count1 INT

INSERT INTO @TBL1

SELECT OBJECT_NAME(OBJECT_ID)

,OBJECT_NAME(parent_object_id)

FROM sys.objects

WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT')

-- type_desc IN ('PRIMARY_KEY_CONSTRAINT') to delete primary key from all table in a db

SELECT @count1 = @@rowcount

,@counter1 = 1

SELECT *

FROM @TBL1

WHILE (@counter1 <= @count1)

BEGIN

DECLARE @tblName1 NVARCHAR(100)

DECLARE @clmName1 NVARCHAR(100)

,@sql11 NVARCHAR(256)

SELECT @clmName1 = table_name

,@tblName1 = column_name

FROM @TBL1

WHERE rowNum = @counter1

SET @sql11 = 'ALTER TABLE ' + @tblName1 + ' DROP CONSTRAINT ' + @clmName1

EXEC (@sql11)

SET @counter1 = @counter1 + 1

END

DECLARE @TBL TABLE (

rowNum INT identity(1, 1)

,table_name NVARCHAR(100)

,column_name NVARCHAR(100)

)

DECLARE @counter INT

,@count INT

INSERT INTO @TBL

SELECT object_name(object_id)

,NAME

FROM sys.columns

WHERE is_identity = 1

AND NAME NOT LIKE 'queuing%'

SELECT @count = @@rowcount

,@counter = 1

SELECT *

FROM @TBL

WHILE (@counter <= @count)

BEGIN

DECLARE @tblName NVARCHAR(100)

DECLARE @clmName NVARCHAR(100)

,@sql1 NVARCHAR(256)

SELECT @tblName = table_name

,@clmName = column_name

FROM @TBL

WHERE rowNum = @counter

SET @sql1 = 'ALTER TABLE ' + @tblName + ' ALTER COLUMN ' + @clmName + ' BIGINT not null'

EXEC (@sql1)

PRINT @sql1

SET @counter = @counter + 1

END

EXEC sp_MSforeachtable 'ALTER TABLE ? ADD PRIMARY KEY(AuditLogID) '

How can I change from SQL Server Windows mode to mixed mode (SQL Server)

2014-10-30T22:50:00.002-07:00

When installing SQL SERVER, i have installed it on Windows Authentication mode.But after according to my needs, i want to have mixed mode, so that i can use sa account.

To change security authentication mode:

In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

In Object Explorer, right-click your server, and then click Restart.

To enable sa user graphically :

In Object Explorer, expand Security, expand Logins, right-click SA, and then click Properties.

On the General page, you might have to create and confirm a password for the login.

On the Status page, in the Login section, click Enabled, and then click OK.

To enable sa user through code :

USE [master]

GO

ALTER LOGIN [sa] WITH PASSWORD=N'sa123'

GO

ALTER LOGIN [sa] ENABLE

GO

Turn Your PC into WIFI HotSpot

2014-09-23T23:56:00.001-07:00

I want to make my PC as a WiFi hotspot, so that i can able to share my internet to my friends and my family members without cable connection.I have search through Google and find one of the most popular software that turns my PC into WiFi hotspot. An Virtual Router open source software by Chris Pietschmann, which is supported in Windows 8, Windows 7 and 2008 R2.With this users can wirelessly share any internet connection (Wifi, LAN, Cable Modem, Dial-up, Cellular, etc.) with any Wifi device (Laptop, Smart Phone, iPod Touch, iPhone, Android Phone, Zune, Netbook, wireless printer, etc.) These devices connect to Virtual Router just like any other access point, and the connection is completely secured using WPA2 (the most secure wireless encryption.)

Where can Virtual Router be used?

Anywhere you are!

Home
Office
School
Airport
Bus Station
The Park
Grandmas House
The In-Laws
Absolutely Anywhere!

For download Click Here

The new version of AspxCommerce is released(AspxCommerce 2.6)

2014-09-23T02:56:00.000-07:00

The new version of .Net based eCommerce software AspxCommerce is released with exciting features and major improvement on security and performance optimization.

What is AspxCommerce:-

AspxCommerce is an open source ecommerce tool that perks on the content management system SageFrame filling in all the essential functions of an online enterprise to build up attractive, rich and fully functional ecommerce websites in no time. AspxCommerce lets you create a customizable storefront; manage products through categories and subcategories, accept payments through credit cards and ship the ordered products to the customers. AspxCommerce has everything set up for you, so that you can focus on building your fully functional online store right away. Besides the existing power of its predecessor, AspxCommerce with its new community release 2.6 additionally comes empowered with features like Real-Time Notification using SignalR, Index Management, Responsive Design, Improved Product Types and Price Rules, Dynamic Attributes, Friendly UI and many more making your online store reap core business benefits.

Unique Features & Improvements in AspxCommerce2.6:-

• Significant Performance Optimization
• Security Enhancements
• Localization
• Dynamic Image Resizing
• Simplified Image Cropping Logic

For demo and download visit https://aspxcommerce.codeplex.com/
For quick demo of latest AspxCommerce 2.6 Click Here

Run any size SQL script in SQL SERVER

2014-09-17T04:39:00.001-07:00

In the real world application, for database developers or database system administrator or others, they can encounter the case: "Backup database without using .bak files". As usual, they will open the sql script file and run it directly inside Sql Server Management Studio(SSMS) tool or via command line. For <= 300MB sql script files, SSMS can do this for you easily and reliably. But for > 300MB these sql script files, SSMS can't open and run it directly. Even though so, there is an useful utility called "sqlcmd" can do this for you. However, sqlcmd tool doesn't support to rollback and stop&continue running the script files. From disadvantage points, I had a good idea to do this by developing a tool to run these sql script files for any size. This tool is called BigRunner. It consists of these following features:

Run the sql script file for any given size.
Enable log the running status to file.
Support both command line and graphic user interface(GUI).
Support rollback the running script (in the next release).
Support stop & continue running script (in the next release).

Usage
BigRunner supports two user interfaces: command line and GUI. For command line, you click into BigRunnerCmd.exe executable file and input these necessary data and run it. For GUI, you also do same as for command line instead of clicking BigRunnerGui.exe executable file.

Requirements

You have to meet the following requirements before running the program:

Install .Net Framework 4.0 or later if not having on your machine.
If you run the sql script file directly on your database server on your machine, please make sure that you must install Sql Server on it.
Your t-sql script in the file have to be separated by GO batch.

Examples
Exp1) We will backup 1 database called Test. This database is in empty status. As assumed that we had the backup data in the sql script files about more than 2GB. We will demonstrate and do this by using BigRunnerCmd.exe:

Step 1: download file BigRunnerCmd.exe from this page at Downloads section.

Step 2: Double-click at this file.

Step 3: This console program is shown as follows:

Step 4: you enter the valid connection string based upon example connection string as above. Here's my valid connection string at my local point to the database called "Test". Then, press Enter to continue

Step 5: Input the big script file full path on your hard drive. Here's my local file path and press Enter to continue

Step 6: Enable log the running status to file if you input "yes" value. Otherwise input "no" to continue. In this case, I will input "yes" to write log to the file and press Enter to continue:

Step 7: Input the full file path to write the log. If this file doesn't exist, the program will automatically create it for you. Otherwise, it will get this file for logging. Here's my existed log file path

Step 8: you can wait until running completely. In additional, in order to stop running, you press any key to do this. Here I pressed "r" key to cancel running.

Step 9: you press Esc key to exit console program.

Last Step: open SSMS tool and view the results.

Exp 2) You also do this like as Exp 1) except for that I demonstrate on graphic user interface

Step 1: you download BigRunnerGui.exe from Downloads section.

Step 2: you double-click into this file to run it.

Step 3: you input these necessary data like as Exp 1) on GUI as follows

Step 4: click Run button to run the sql script

Step 5: this step is like as step 8 at Exp 1) instead of GUI

Last step: exit GUI and then open SSMS and view the results.

For download Click Here

Referencing https://bigsqlrunner.codeplex.com

A Simple Windows Calculater Application using Visual C# for Beginner

2014-09-03T08:42:00.000-07:00

In this article, i am going to make a simple calculator in C# using Microsoft Visual Studio.Let's start to make an application.
First open the Microsoft Visual Studio(no matter which version of Visual Studio you have) , and then click file menu at the top left side corner of Visual Studio and finally click new project.

When after clicking a new popup window will open which is shown below. Write the name of the project on the text after the name label and specify the location of project where you want to save it. And then Click OK button.

After Clicking button popup will disappear and it goes back to main windows, now the windows look like this.

Hover through View menu and click toolbox and properties respectively to open toolbox and properties windows and the screenshot will be look like the picture below.

Drag and drop one text box and 17 button from the toolbox on Form1 design window as like above screenshot and click on each button then after change text property of each button as shown above.
And double click on each button to write some code which is execute while clicking it.

//A simple Calculater Program

//Author http://programerzone.blogspot.com

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace Kamal_Calculater

{

public partial class Form1 : Form

{

int value1=0;

int value2=0;

char opt;

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

textBox1.Text+= "1";

}

private void button2_Click(object sender, EventArgs e)

{

textBox1.Text+= "2";

}

private void button3_Click(object sender, EventArgs e)

{

textBox1.Text+= "3";

}

private void button4_Click(object sender, EventArgs e)

{

textBox1.Text+= "4";

}

private void button5_Click(object sender, EventArgs e)

{

textBox1.Text+= "5";

}

private void button6_Click(object sender, EventArgs e)

{

textBox1.Text+= "6";

}

private void button7_Click(object sender, EventArgs e)

{

textBox1.Text+= "7";

}

private void button8_Click(object sender, EventArgs e)

{

textBox1.Text+= "8";

}

private void button9_Click(object sender, EventArgs e)

{

textBox1.Text+= "9";

}

private void button10_Click(object sender, EventArgs e)

{

textBox1.Text+= "0";

}

private void button15_Click(object sender, EventArgs e)

{

textBox1.Text+= ".";

}

private void button17_Click(object sender, EventArgs e)

{

textBox1.Clear();

textBox1.Focus();

}

private void Form1_Load(object sender, EventArgs e)

{

}

private void button11_Click(object sender, EventArgs e)

{

opt = '/';

value1= Int32.Parse(textBox1.Text);

textBox1.Text = "";

textBox1.Focus();

}

private void button12_Click(object sender, EventArgs e)

{

opt = '*';

value1 = 1;

value1= Int32.Parse(textBox1.Text);

textBox1.Text = "";

textBox1.Focus();

}

private void button13_Click(object sender, EventArgs e)

{

opt = '+';

value1+= Int32.Parse(textBox1.Text);

textBox1.Clear();

textBox1.Focus();

}

private void button14_Click(object sender, EventArgs e)

{

opt = '-';

value1 =Int32.Parse( textBox1.Text)-value1;

textBox1.Text = "";

textBox1.Focus();

}

private void button16_Click(object sender, EventArgs e)

{

int result;

value2=Int32.Parse(textBox1.Text);

textBox1.Clear();

switch (opt)

{

case '/':

result = value1 / value2;

textBox1.Text = result.ToString();

value1 = 0;

break;

case '*':

result = value1 * value2;

textBox1.Text = result.ToString();

value1 = 0;

break;

case '+':

result = value1 + value2;

textBox1.Text = result.ToString();

value1 = 0;

break;

case '-':

result = value1 - value2;

textBox1.Text = result.ToString();

value1 = 0;

break;

default:

textBox1.Text = "Error";

break;

}

Now the application is ready to run and press F5 to run it, The final screenshot is below.

Click here to download source code

Updating table itself on SQL SERVER and use of CHARINDEX, SUBSTRING and LEN SQL built in functions.

2014-06-16T00:21:00.000-07:00

In this article, i will be show you the full example how you can able to update single table itself by using some built in SQL SERVER system function.In this example, i will present you the problem that i faced and how i solved it. The problem is that when i have saved data on my table it also joined unwanted commas at the last of email, which will be one of the column on my table.For this first of all, i am going to create a table and insert some data on it.

CREATE TABLE table1(id INT IDENTITY(1,1) NOT NULL,email NVARCHAR(256))

INSERT INTO table1(email) values('kamal@yahoo.com')

INSERT INTO table1(email) values('kamal1@yahoo.com,')

INSERT INTO table1(email) values('kamal2@yahoo.com')

INSERT INTO table1(email) values('kamal3@yahoo.com,,')

INSERT INTO table1(email) values('kamal4@yahoo.com,')

At before, when i was not running below updated query, the table has data like..

ID	EMAIL
1	kamal@yahoo.com
2	kamal1@yahoo.com,
3	kamal2@yahoo.com
4	kamal3@yahoo.com,,
5	kamal4@yahoo.com,

And after that i need to remove comma from the email and save it on same table, to overcome from this problem i wrote the following query.

UPDATE a

SET a.email = substring(b.email, 1, CASE

WHEN charindex(',', b.email) > 0

THEN charindex(',', b.email) - 1

ELSE len(b.email)

END)

FROM table1 AS a

INNER JOIN table1 AS b ON a.id = b.id

This SQL query remove the ',' from email and i got the required result.

ID	EMAIL
1	kamal@yahoo.com
2	kamal1@yahoo.com
3	kamal2@yahoo.com
4	kamal3@yahoo.com
5	kamal4@yahoo.com

Alternatives:- You can get the same result by using cursor but i would not recommended it.

DECLARE @id INT

Declare @email nvarchar(256)

DECLARE myCursor CURSOR

FOR SELECT id,email FROM table1

OPEN myCursor

FETCH NEXT FROM myCursor

INTO @id, @email

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE table1

SET email = substring(@email, 1, CASE

WHEN charindex(',', @email) > 0

THEN charindex(',', @email) - 1

ELSE len(@email)

END)

WHERE id=@id

FETCH NEXT FROM myCursor

INTO @id, @email

END

CLOSE myCursor

DEALLOCATE myCursor

SageFrame 3.0 Released- An Open Source CMS

2014-03-25T23:07:00.002-07:00

SageFrame is a powerful Content Management System (CMS) built on top of ASP.NET. A rigorous research and analysis has led to the development of SageFrame. The main objective behind the development is to provide a highly comprehensive management system that helps web developers and designers accomplish projects with ease and efficacy. From novice personnel to seasoned professionals, SageFrame caters every developer group enabling them to easily build various purpose web sites and applications in an effective way. In addition to its easy user interface, site optimization features and page management system, SageFrame contains all other necessary ingredients required to give your site the added dynamism over its competitors.

After the success of SageFrame 1.0, SageFrame 2.0 and SageFrame2.1, SageFrame 3.0 is here itching to marvel you with its appealing new features. With SageFrame 3.0 users can now make use of the whole CMF experience to have the hold of an excellent content management framework, mobile CMS and digital marketing system. On its high extensibility, scalability, flexibility and security, the optimum use of tools and technologies have resulted in the creation and management of dynamic web sites with SageFrame3.0. With Responsive Design armed from its central core, the CMS now copes with an absolute fluid grid experience following the 100 grid system by using adaptive techniques to facilitate the faster mobile browsing experience. Offloading the traffic served directly from the origin infrastructure, CSS and JS bundling likewise minify the snags increasing the overall site’s performance resulting largely in the cost savings. Managing multiple web portals at a time, it also allows the users to use a single hosting account for multiple domain names thereby giving the freedom to assign any portal as the parent or child portal. Similarly the customization and control of every module by making it effortlessly user-friendly, the dashboard personalization option in it rearranges how the information appears to best suit the user’s needs. SageFrame team thus has incorporated various handy features of several CMSs to SageFrame 3.0.

Notable Features

Modular architecture. On demand module integration and selection.
WYSIWYG editor.
Highly customizable design. Layout design in SageFrame is XML based.
User friendly template manager and an online template editor.
Drag and drop widget feature.
User friendly as well as developer friendly URL management.
Responsive Design/ adaptive banner
Multi-Parent Portal support
JS and CSS bundling
SEO friendly website management
Core Integrated OpenID
Cache Management and Site Analytics
Visual layout Manipulation
Personalized Dashboard
To-Do-List
Page-Help
Premium modules
Rich Templating

Useful Links

Demo
Overview
Blogs Extensions Directory Resources Forums

Free Templates

Fashion
Blogger
Health
Medicare
Mobile

Find on CodePlex:- http://sageframe.codeplex.com/

Note:- Documentation taken from SageFrame sites.

SQL Command to clear cache on MSSQL

2014-03-21T03:06:00.003-07:00

DBCC FREEPROCCACHE

DBCC FREESESSIONCACHE

DBCC FREESYSTEMCACHE ('ALL')

Unable to modify table. Timeout expired.(Solution)

2014-03-21T03:03:00.001-07:00

Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.I got this error when modifying my table structure.The table has lots of data and what is the solution?, the solution is to increase timeout setting in option menu along designer tab which is 30 sec by default.

How To Find All Store Procedure That Contain Text....

2014-02-10T22:12:00.001-08:00

Method 1:-

SELECT SPECIFIC_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%searchtext%'

--Option 1

SELECT DISTINCT so.NAME

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id = so.id

WHERE sc.TEXT LIKE '%Employee%'

--Option 2

SELECT DISTINCT o.NAME

,o.xtype

FROM syscomments c

INNER JOIN sysobjects o ON c.id = o.id

WHERE c.TEXT LIKE '%Employee%'

--SQL SERVER 2005--

USE AdventureWorks

--Searching for Empoloyee table

SELECT NAME

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

--Searching for Empoloyee table and RateChangeDate column together

SELECT NAME

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%'

Refrencing http://blog.sqlauthority.com/2007/11/10/sql-server-2005-2000-search-string-in-stored-procedure/

Covering Rato Rani Phule Jhai By Kamal Khanal

2014-01-16T20:26:00.001-08:00

SageFrame an open source CMS

2013-11-07T20:19:00.000-08:00

SageFrame is a powerful Content Management System (CMS) built on top of ASP.NET. A rigorous research and analysis has led to the development of SageFrame. The main objective behind the development was to provide a highly comprehensive management system that could help web developers and designers accomplish projects with ease and efficacy. From novice personnel to seasoned professionals, SageFrame caters to every developer group enabling them to build various purpose web sites and applications easily and effectively. In addition to its easy user interface, site optimization features and page management system, SageFrame contains all other necessary ingredients required to give your site the added dynamism over its competitors.

After the success with SageFrame 1.0 and SageFrame 2.0, SageFrame2.1 is here itching to marvel you with its appealing new features – with SageFrame 2.1 users can now use Open IDs like Facebook to login to the SageFrame. They can implement cache management; look at the site analytics, priorities events on system start ups, schedule events, create and manage multi portals, and make their sites multi lingual. Furthermore, SageFrame users can customize their page by adding preset templates to their site. While adding such templates, the original data associated with the templates is also fetched. Another notable improvement that SageFrame 2.1 includes is that the pages built on SageFrame 2.1 are compatible with both handheld devices and PCs. SageFrame team has incorporated various handy features of several CMSes to SageFrame. It is upto you to unleash your site’s potential with SageFrame 2.1.

Notable Features

Modular architecture. On demand module integration and selection.
WYSIWYG editor.
Highly customizable design. Layout design in SageFrame is XML based.
User friendly template manager and an online template editor.
Drag and drop widget feature.
User friendly as well as developer friendly URL management.
SEO friendly website management.
Core Integrated OpenID
Cache Management and Site Analytics
Visual layout Manipulation
System Event Startup.

Go to download page

Ecommerce Sites In Asp.Net

2013-10-31T00:40:00.002-07:00

Ecommerce means commercial transactions conducted electronically on the Internet.It is way of selling products online via internet. Now days ecommerce sites are growing and many eCommerce software are available.Here i am going to list out eCommerce framework built on asp.net.

AspxCommerce:- AspxCommerce is an Asp.Net based complete open source e-Commerce solution built on top of SageFrame (Asp.Net web application framework) which enables you to maximize the success of your online business. AspxCommerce is a complete package for both new or existing merchants who need to design, configure, customize, manage and open their online storefronts.
Kartris:- Kartris is an ASP.NET e-commerce application that features a CMS catalog system, shopping cart, payment systems and full administration back end. It is designed to run on Microsoft's IIS web server, and exploits the powerful caching and optimization features of that platform.
DotShoppingCart:- DotShoppingCart combines ecommerce with the powerful content management publishing tools. The pluggable modularized architecture meets all your needs to create and manage your web sites. Whether it's a personal blog, family Web site, or ecommerce business, DotShoppingCart is the best one stop shop software for building web site.
Kentico:- Kentico CMS for ASP.NET empowers marketers and developers in creating websites without limits. It's the ultimate solution for your website, store, community and on-line marketing initiatives.
Dashcommerce:- dashCommerce is a flexible and robust ASP.NET Open Source e-commerce application that you can use to set up your online store.
Nopcommerce:- nopCommerce is an open source ecommerce software that contains both a catalog frontend and an administration tool backend. nopCommerce is a fully customizable shopping cart. It's stable and highly usable. From downloads to documentation, nopCommerce.com offers a comprehensive base of information, resources, and support to the nopCommerce community.
Ablecommerce:- Ablecommerce is another Asp.Net Shopping cart software.
Vevocart:- Vevocart is ecommerce software built on asp.net

Split function using XML in SQL SERVER

2013-10-30T04:56:00.001-07:00


CREATE FUNCTION [dbo].[Split] (



 @String [nvarchar](max)

 ,@Delimiter [char](1)
)


RETURNS @temptable
TABLE ([items] [nvarchar](max))

AS

BEGIN

 DECLARE @xml  XML



SET @xml = cast(('<Node>' 

+ replace(@String, @Delimiter, '</Node><Node>')

+ '</Node>') AS XML)




INSERT INTO @temptable

SELECT N.value('.', 'nvarchar(max)') AS items 

FROM @xml.nodes('Node') AS T(N) 
RETURN

END