How to use SQL LIKE operator in asp.net c#

Today when we wrote a program for online job portal. On that time i want to show the job detail from job table using single English alphabet letter. So first of all i bind Dropdownlist with the alphabet letters then also bind Gridview from selected DropdownList letter. For this type of query i need SQL LIKE operator because i want to search items from the table on the basis of selected letter.  Bind the gridview with SqlDataSource with where clause.

 <p>
        Please Select any one letter from given list:</p>

    <asp:DropDownList ID="Letters" DataSource='<%# Alphabet %>' runat="server" />

    <asp:Button ID="Button1" runat="server" Text="Get Job List"  />
<p>


        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="JobId" DataSourceID="SqlDataSource1">

            <Columns>

                <asp:BoundField DataField="JobId" HeaderText="JobId" InsertVisible="False" ReadOnly="True" SortExpression="JobId" />

                <asp:BoundField DataField="JobName" HeaderText="JobName" SortExpression="JobName" />

                <asp:BoundField DataField="Salary" HeaderText="Salary" SortExpression="Salary" />

                <asp:HyperLinkField DataNavigateUrlFields="JobId" DataNavigateUrlFormatString="jobdetail.aspx?JobId={0}" HeaderText="Full Detail" Text="Details" />
            </Columns>

        </asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [AddJob] WHERE (JobName LIKE @JobName + '%')">

            <SelectParameters>
                <asp:ControlParameter ControlID="Letters" Name="JobName" PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>

Code Behind page

public partial class Bynamejobsearch : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            DataBind();
        }
    }
    private List<char> _Alphabet;
    protected List<char> Alphabet
    {
        get
        {
            if (_Alphabet == null)
            {
                _Alphabet = new List<char>();
                for (int i = 65; i < 91; i++)
                {
                    _Alphabet.Add(Convert.ToChar(i));
                }
            }
            return _Alphabet;
        }
    }

}

Code generate the following output:

Dark highlighted black line show that how to use select command with like operator. By using LIKE operator we got job detail which is start from letter which is selected in DropdownList. I mean to say first letter come from List and remaining letter can any other letters of English alphabet.

Data Definition Language (DDL) Triggers in SQL

A DDL trigger is fired in response to DDL statements, such as CREATE TABLE or ALTER TABLE. DDL triggers can be used to perform administrative tasks, such as database auditing.

Database auditing helps in monitoring the DDL operations on a database. DDL operation can include operations such as creation of a table or view, or modifications of a table or procedure. Consider an example, where you want the database administrator to be notified whenever a table is created in the Master Database. For this purpose, you can create a DDL trigger.

Depending on the way in which triggers are fired, they are categorized as:

After Triggers

The after trigger can be created on any table for the insert, update or delete operation just like other triggers. The main difference in the functionality of an after trigger is that it is fired after the execution of the DML operation for which it has been defined. The after trigger is executed when all the constraints and triggers defined on the table are successfully executed.

By default, if more than one after trigger is created on a table is for a DML operation such as insert, update, or delete, then the sequence of execution is the order in which they were created.
For example, the EmpSalary table stores the salary and tax details for all the employees in an organization. You need to ensure that after the salary details of an employee are updated in the EmpSalary table, the tax details are also recalculated and updated. In such a scenario, you can implement an after trigger to update the tax details when the salary details are updated.

Instead of Triggers

The instead of triggers can be primarily used to perform an action, such as a DML operation on another table or view. This type of trigger can be created on both a table as well as a view.

An instead of trigger can be used for the following actions:

• Ignoring parts of a batch.
• Not processing a part of a batch and logging the problem rows.
• Taking an alternative action when an error condition is encountered.

For example, if a view is created with multiple columns from two or more tables, then an insert operation on the view is only possible if the primary key fields from all the base tables are used in the query. Alternatively, if you use an instead of trigger, you can insert data in the base tables individually. This makes the view logically updateable.

You can even create an Instead of trigger to restrict deletion in a master table. For example, you can display a message “Master record cannot be deleted” if a delete statement is executed on the Employee table of the AdventureWorks database.

Unlike after triggers, you cannot, create more than one Instead of trigger for a DML operation on the same table or view.

Nested Triggers

Nested triggers are fired due to actions of other triggers. For example, you delete a row from TableA. A trigger on TableA deletes rows from TableB. Because you are deleting rows from TableB, a trigger is executed on TableB to record the deleted rows.

Recursive Triggers

Recursive triggers are a special case of nested triggers. Unlike nested triggers, support for recursive triggers is at the database level. As the name implies, a recursive trigger eventually calls itself. There are two types of recursive triggers, Direct and Indirect.

Direct Recursive Trigger

A direct trigger is a trigger that performs the same operation (insert, update, or delete) on the same table causing the trigger to fire itself again.

Indirect Recursive Trigger

An indirect trigger is a trigger that fires a trigger on another table and eventually the nested trigger ends up firing the first trigger again. For instance, an UPDATE on TableA fires a trigger that in turn fires an update on TableB. The update on TableB fires another trigger that performs an update on TableC. TableC has a trigger that causes an update on TableA again. The update trigger of TableA is fired again.

Data Modification Language (DML) Triggers in SQL

A DML trigger is fired when data in the underlying table is affected by DML statements, such as INSERT, UPDATE, or DELETE. These triggers help in maintaining consistent, reliable, and correct data in tables. They enable the performance of complex action and cascade these actions to other dependent tables. Cascading is the process of reflecting the changes made in a table in the other related tables.
The DML triggers have the following characteristics:
Fired automatically by the SQL Server whenever any data modification statement is issued.
Cannot be explicitly invoked or executed, as in the case of the stored procedures.
Prevents incorrect, unauthorized, and inconsistent changes in data.
Cannot return data to the user.
Can be nested up to 32 levels. The nesting of triggers occurs when a trigger performs an action that initiates another trigger.

Whenever a trigger is fired in response to the INSERT, DELETE, or UPDATE statement, the SQL Server creates two temporary tables, called magic tables. The magic tables are called inserted and deleted. The magic tables are conceptual tables and are similar in structure to the table on which the trigger is defined.
The inserted tale contains a copy of all records that are inserted in the trigger table. The Deleted table contains all records that have been deleted from the trigger table. Whenever you update data in a table, the trigger uses both the inserted and the deleted tables.
Depending on the operation that is performed, the DML trigger can be further categorized as:
Insert trigger: is fired whenever and attempt is made to insert a row in the trigger table. When an INSERT statement is executed, a new row is added to both the trigger and the inserted tables.
Delete trigger: is fired whenever an attempt is made to delete a row from the trigger table. When a DELETE statement is executed, the specified rows from the trigger table are deleted and are added to the deleted table. The deleted and trigger tables do not have any rows in common, as in the case of the inserted and trigger tables.
There are three ways of implementing referential integrity by using a DELETE trigger. These are:
The cascade method: Deletes records from the dependent tables whenever a record is deleted from the master table.
The restrict method: Restricts the deletion of records from the master table if the related records are present in the dependent tables.
The nullify method: Nullifies the values in the specified columns of the dependent tables whenever a record is deleted form the master table.
Update trigger: Is fired when a UPDATE statement is executed in the trigger table. It uses two logical tables for its operations, the deleted table that contains the original rows (the rows with the values before updating) and the inserted table that stores the new tows (the modified rows). After all the rows are updated, the deleted and inserted tables are populated and the trigger is fired.
For example, you have a table with three columns. The table stores the details of hardware devices. You updated a value in column 2 from ‘Printer’ to ‘Lex New Printer’. During the update process, the deleted table holds the original row (the row with the values before updating), and inserted table stores the new row (the modified row) with the value ‘Lex New Printer’ in Column2.

Execute Batches multiple times using Stored Procedures in SQL

Batches are temporary in nature. To execute a batch more than once, you need to recreate SQL statements and submit them to the server. This leads to an increase in the overhead, as the server needs to compile and create the execution plan for these statements again. Therefore, if you need to execute a batch multiple times, you can save it within a stored procedure. A stored procedure is a precompiled object stored in the database.

Stored procedures can invoke the Data Definition Language (DDL) and Data Manipulation Language (DML) statements and can return values. If you need to assign values to the variables declared in the procedures at the run time, you can pass parameters while executing them. You can also execute a procedure from another procedure. This helps in using the functionality of the called procedure within the calling procedure.

Creating Stored Procedures

You can create a stored procedure by using the CREATE PROCEDURE statement. The syntax of the CREATE PROCEDURE statement is:

CREATE PROCEDURE proc_name
AS
BEGIN
Sql_statement1
Sql_statement2
END

Where Proc_name specifies the name of the stored procedure.

The following example create a stored procedure to view the department names from the Department table:

CREATE PROCEDURE prcDept
AS
BEGIN
SELECT Name FROM HumanResources.Department
END

When the CREATE PROCEDURE statement is executed, the server compiles the procedure and saves it as a database object. The procedure is then available for various applications to execute. The process of compiling a stored procedure involves the following steps:

• The procedure is compiled and its components are broken into various pieces. This process is known as parsing.
• The existence of the referred objects, such as tables and views, are checked. This process is known as resolving.
• The name of the procedure is stored in the sysobjects table and the code that creates the stored procedure is stored in the syscomments table.
• The procedure is compiled and a blueprint for how the query will run is created. This blueprint is specified as execution plan. The execution plan is saved in the procedure cache.
• When the procedure is executed for the first time. The execution plan will be read and fully optimized and then run. The net time the procedure is executed in the same session, it will be read directly from the cache. This increases performance, as there is no repeated compilation.

After creating the stored procedure, you can view the code of the procedure by using the sp_helptext command.

Implementing Triggers and its Types in SQL

In a relational database, data in a table is related to other tables. Therefore, while manipulating data in one table, you need to verify and validate its effect on data in the related tables. In addition, you might need to manipulate data in a table after inserting or updating data in another table.

You also need to ensure that if an error occurs while updating the data in a table, the changes are reverted. This helps in maintaining data integrity. The SQL Server allows you to implement triggers and transactions to maintain data integrity.

This article explains different types of triggers that can be created in SQL Server. Next, we will discusses how to implement triggers to enforce data integrity. Further, we will discuss about how to implement transactions.

Implement Triggers

At times, while performing data manipulation on a database object, you might also need to perform another manipulation on another object. For example, in an organization, the employees use the Online Leave Approval system to apply for leaves. When an employee applies for a leave, the leave details are stored in the Leave-Details table. In addition, a new record is added to the Leaves-For-Approval table. When the supervisors log on to the system, all the leaves pending for their approval are retrieved from the Leaves-For-Approval table and displayed to them.

To perform such operations, the SQL Server allows you to implement triggers. A trigger is a block of code that constitutes a set of T-SQL statements activated in response to certain actions, such as insert or delete. Triggers are used to ensure data integrity before or after performing data manipulations.

Before you implement a trigger, it is important to know the different types of triggers that can be created by using SQL Server.

Identifying Types of Triggers

In the SQL Server, various kinds of triggers can be used for different types of data manipulation operations. The SQL Server supports the following types of triggers:

Data Modification Language (DML) triggers

A DML trigger is fired when data in the underlying table is affected by DML statements, such as INSERT, UPDATE, or DELETE. These triggers help in maintaining consistent, reliable, and correct data in tables. They enable the performance of complex action and cascade these actions to other dependent tables. Cascading is the process of reflecting the changes made in a table in the other related tables.

Data Definition Language (DDL) triggers

A DDL trigger is fired in response to DDL statements, such as CREATE TABLE or ALTER TABLE. DDL triggers can be used to perform administrative tasks, such as database auditing.

Creating Table-Valued Functions in SQL

A table-valued function returns a table as an output, which can be derived as a part of a SELECT statement. Table-valued function return the output as a table data type. The table data is a special data type used to store a set of rows, which return the result set of a table-valued function. Table-valued functions are of two types:

Inline Table-Valued Function

An inline table-valued function returns a variable of a table data type from the result set of a single SELECT statement. An inline function does not contain a function body within the BEGIN and END statements.

Consider an example where the inline table-valued function, fx_Department_GName, accepts a group name as parameter and returns the details of the departments that belong to the group from the Department table. You can create the function by using the following statement:

CREATE FUNCTION fx_Department_GName ( @GrName nvarchar (20) )
RETURNS table
AS
RETURN (
SELECT *
FROM HumanResources.Department
WHERE GroupName=@GrName
)
GO

You can use the following statement to execute the fx_Department_Gname function with a specified argument:

SELECT * FROM fx_Department_GName (‘Manufacturing’)

The preceding statement will return a result set having the group name ‘Manufacturing’.

Consider another example of an inline function that accepts rate a a parameter and returns all the records that have a rate value greater than the parameter value:

CREATE FUNCTION HumanResources.Emp_Pay (@Rate int)
RETURNS table
AS
RETURN (
SELECT e.EmployeeID, e.Title, er.Rate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS er
ON e.EmployeeID=er.EmployeeID WHERE er.Rate<@Rate
)
GO
The preceding function will return a result set that displays all the records of the employees who have the pay rate greater that the parameter.

Multistatement Table-Valued Function

A Multistatement table-valued function uses multiple statements to build the table that is returned to the calling statement. The function body contains a BEGIN…END block, which holds a series of T-SQL statements to build and insert rows into a temporary table. The temporary table is returned in the result set and is created based on the specification mentioned in the function.

Consider an example where the Multistatement table-valued function, PayRate, is created to return a set of records from the EmployeePayHistory table by using the following statements:

CREATE FUNCTION PayRate (@rate money)
RETURNS @table TABLE
(EmployeeID int NOT NULL,
RateChangeDate datetime NOT NULL,
Rate money NOT NULL,
PayFrequency tinyint NOT NULL,
modifiedDate datatime NOT NULL)
AS
BEGIN
INSERT @table
SELECT *
FROM HumanResources.EmployeePayHistory
WHERE Rate > @rate
RETURN
END

The function returns a result set in from of a temporary table, @table, created within the function. You can execute the function by using the following statement:

SELECT * FROM PayRate (45)

Depending on the result set returned by a function can be categorized as deterministic or nondeterministic. Deterministic functions always return the same result whenever they are called with a specific set of input values. However, nondeterministic function may return different results each time they are called with a specific set of input values.

An example of a deterministic function is date add, which returns the same result for any given set of argument values for its three parameters. Get date is a nondeterministic function because it is always invoked without any argument, but the return value changes on every execution.