How to add Meta Tag dynamically in ASPX Page in ASP.NET?

Introduction: 

In this article I will explain how to change or add Meta tags dynamically in asp.net to aspx page in  c#.net, vb.net. We can add meta tag and page title statically, which is easy. But some time it is needed to add or change Page title and meta tag dynamically i.e. from code behind.

Read More

Number or Numeric Validation on TextBox using onkeypress event handler in Javascript

In this article I will explain how to apply validation (allow only numeric value) on textbox using Javascript. I have entered only numeric values in textbox and alphabets or special characters are not allowed.

In this article I have a TextBox for which I have specified three event handlers, onkeypress, ondrop and onpaste.

Read More

How to Merge Multiple Tables into a Single Table in JavaScript?

Introduction: 

In this article, I will explain how to use jQuery or javascript to merge multiple tables into a single table.

Read More

Convert textbox value to Uppercase Or Lowercase in Asp.net using JavaScript

Introduction: In this article I will explain how change first letter of the word to uppercase and Lowercase.

We can use jquery or javascript to complete this task.

Read More

Explain CTE (Common Table expression) with example?

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set which can be referenced in the same query just as a view. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries.

Syntax:

; WITH CTEName (Column aliases)

AS (Subquery)

SELECT statement

FROM CTEName;  

Example:

Complex query Using sub-query:

SELECT * FROM (

SELECT Dept.Department, Emp.Name, Emp.Age From Department Dept

Inner join Employee Emp on Emp.EID = Dept.EID) Temp

WHERE Temp.Age > 40

ORDER BY Temp.NAME

By Using CTE:

;With CTE1(Department, Name, Age)--Column names

AS

(

SELECT Dept.Department, Emp.Name, Emp.Age from Department Dept

INNER JOIN EMP Emp ON Emp.EID = Dept.EID

)

SELECT * FROM CTE1 --Using CTE

WHERE CTE1.Age > 40

ORDER BY CTE1.NAME

When to Use CTE

1.       This is a substitute of complex sub query.

2.       Substitute for a view when the general use of a view is not required.

3.       This is also used to create a recursive query or non-recursive query.

4.       We can also update data using CTE (Common table expression). 

Read More

Magic tables or Logical Tables in SQL server and how to access Magic Tables?

Magic tables in SQL:

Magic tables are the logical tables in SQL server. There are two types of logical tables in SQL server:

• Inserted
• Deleted

 These tables are automatically created and managed by SQL Server internally. These tables hold the recently inserted, deleted and updated values during Insert, Update and Delete operations on a table. These tables are not visible and accessible directly. There are two methods to access these tables

•  Using Triggers operation either After Trigger or Instead of trigger.
• Without Triggers Using “OUTPUT” Clause 

Using Triggers:

Inserted Logical Table:

Inserted logical table holds the latest inserted value or updated value in the table.

Whenever we do insertion or updating the record in table in database, a table gets created automatically by the SQL server, named as INSERTED.

CREATE TABLE [dbo].[emp_details](             [empid] [numeric](18, 0) NOT NULL,             [empname] [varchar](100) NULL,             [salary] [numeric](18, 0) NULL,)

Now Create a trigger

CREATE TRIGGER emp _Insertion ON emp_details FOR INSERT AS begin SELECT * FROM INSERTED SELECT * FROM DELETED End

Now Inert data in above table:

INSERT INTO  emp_details (empid, empname, salary) VALUES (201, ’XYZ’ ,1000)

Deleted Logical Table:

When we update the record in table then  two tables are created, one is INSERTED and another is called DELETED. Deleted table will hold the previous record after the updations and  Inserted table consists of the updated record.

CREATE TRIGGER emp_update ON emp_details FOR update AS begin SELECT * FROM INSERTED SELECT * FROM DELETED End

Update Record

update emp_details set empname='ABC' where empid=201

Without Triggers Using Output Clause:

Output Clause  with Insert Command:

INSERT into emp_details  (  [EmpID],  EmpName, Salary  ) OUTPUT  Inserted.[EmpID], Inserted.EmpName, Inserted.Salary VALUES (208, 'Delton', 15000);

Result:

Read More

What is the difference between string and string builder?

In this article I will explain the  difference between string and stringbuilder in c#.

String

String is immutable object  (i.e.  once created cannot be changed ) and It always create new object of string type in memory.  If we want to append or replace something in string then it will discard the old value and will create new instance in memory to hold the new value. String belongs to “System” namespace.

 Example

 string   str = "Hello Visitor";

 // create a new string instance instead of changing the old one

 str += "How Are";

 str += "You ??";

 Stringbuilder

 StringBuilder is mutable, means if create string builder object then you can make modification in the data i.e. you can append and replace the string  without creating new instance for every time.it will update string at one place in memory doesn’t create new space in memory. Stringbuilder belongs to “System.Text”  namespace.

Example:

StringBuilder strbuilder = new StringBuilder("");

strbuilder.Append("Hello");

strbuilder.Append("Visitor ");

string str = sb.ToString();

Please mention in the comments in case you have any doubts related to the post: difference between string and stringbuilder.

Read More

What do you mean by SQL dynamic queries? How to execute SQl Dynamic Commands in SQl Server?

SQL Dynamic Query:

Sometime hard coded queries are not suitable to achieve the result. In that case we create dynamic query. SQL provide us different ways to create dynamic queries:

1.     Query With parameter

2.     Use Exec

3.     Use sp_executesql

1.     Query With Parameter:

This first approach is to pass parameters into WHERE clause of your SQL statement.  Suppose we need to find all records from the “EmDetails” table where City = 'Chandigarh'.  This can be done as:

 

  DECLARE @city varchar(75)

  SET @city = 'Chandigarh'

  SELECT * FROM EmDetails WHERE City = @city

2.     Use Exec:

Second option is to use EXEC, with this approach we built the SQL statement on the runtime (dynamically). 

Suppose we want to get columns EmpID, Name and City where City = 'Chandigarh'.

DECLARE @sqlCommand varchar(1000)

DECLARE @columnList varchar(75)

DECLARE @city varchar(75)

SET @columnList = 'EmpID, Name, City'

SET @city = '''Chandigarh'''

SET @sqlCommand = 'SELECT ' + @columnList + ' FROM EmDetails WHERE City = ' + @city

EXEC (@sqlCommand)

As we can see from above example handling the @city value is not at straight forward, because you also need to define the extra quotes in order to pass a character value into the query.  These extra quotes could also be done within the statement, but either way you need to specify the extra single quotes in order for the query to be built correctly and therefore run.

3.     Use Sp_ExecuteSql:

 In this approach we will use both first and second approach in one query. With this approach we have the ability to dynamically build the query and also able to still use parameters as you could in example 1. This saves the need to have to deal with the extra quotes to get the query to build correctly.  In addition, with using this approach you can ensure that the data values being passed into the query are the correct datatypes.

DECLARE @sqlCommand nvarchar(1000)

DECLARE @columnList varchar(75)

DECLARE @city varchar(75)

SET @columnList = 'EmpID, Name, City'

SET @city = 'Chandigarh'

SET @sqlCommand = 'SELECT ' + @columnList + ' FROM EmDetails WHERE City = @city'

EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Read More