How to change width of textbox dynamically using JQuery

Introduction
               In the previous post, I had show how to get Comma seperated values in Sql Server. In this post I will show you how to set textbox width dynamically.

Description                 When I click on search textbox then it checks wheather it has some value if no, then it keeps textbox width at 40px and text color as gray. And if have some text in search box then it will keep width as 80px and text as gray.

if ((tbSearch.val() == null) || (tbSearch.val() == '') || (tbSearch.val() == 'Search')) {

    tbSearch.val('Search');

    tbSearch.css("color", "Gray");

    tbSearch.css("width", 40);

} else {

    tbSearch.css("color", "Gray");

    tbSearch.css("width", 80);

}

               when I click in search box or when textbox gets focus, It will expand with animation from width of 40px to 80px and get fore color as black.

tbSearch.focus(function () {

    if (tbSearch.val() == 'Search')

        $(this).val('');

    $(this).css("color", "black");

    $(this).animate({ width: 80 }, 500);

});

              When I focus out of search box and if it contains some search data then width remains at 80px and fore color as gray, else it animate and shrink to 40px.

tbSearch.focusout(function () {

    $(this).css("color", "Gray");

    if ($(this).val() == '') {

        $(this).val('Search');

        $(this).animate({ width: 40 }, 500);

    }

});

How to get Comma seperated list of values in SQL Server

Description:

                In SQL Server, we could query table column values into comma separated list. Here I could found two efficient ways to do the same. The first will be used mostly in procedures as it requires temporary variable, while other could be used in single select query with the help of stuff() function and XML FOR PATH() methods.

Data:

We will be having following data to execute Queries.

Employee Table

CREATE TABLE [dbo].[employee](

      [empid] [int] NOT NULL,

      [name] [varchar](100) NULL,

      [salary] [decimal](18, 0) NULL,

      [deptid] [int] NULL)

Department Table

CREATE TABLE [dbo].[department](

      [deptid] [int] NOT NULL,

      [deptname] [varchar](100) NULL)

Method 1:

Query:

DECLARE @list VARCHAR(MAX)

SELECT @list = COALESCE(@list + ',' , '') + name FROM employee

SELECT @list 'Comma Seperated List'

Result:

Comma Seperated List

-----------------------------------------------------------------------

Allyn,Bailey,Becky,Calista,Elisa,Franklyn,Hervey,Jackson,Kimberly,Lecia

(1 row(s) affected)

Method 2:

Query:

SELECT STUFF

((SELECT ',' + NAME

FROM employee

FOR XML PATH(''))

,1,1,'') 'Comma Seperated List'

Result:

Comma Seperated List

-----------------------------------------------------------------------
Allyn,Bailey,Becky,Calista,Elisa,Franklyn,Hervey,Jackson,Kimberly,Lecia

(1 row(s) affected)

Example:

                In example we will be showing employee working in particular department as comma separated values.

Query:

SELECT T1.deptname 'Department Name',

STUFF

((SELECT ',' + name FROM employee T2 WHERE T2.deptid = T1.deptid

FOR XML PATH('')),1,1,'') Employees

FROM

department T1

Result:

deptname                                           Employees

----------------------------------------------------------------------------------------------------

Administration                                            Allyn,Elisa

Marketing                                                  Bailey,Kimberly

Shipping                                                     Jackson

Information Technology                              Becky,Hervey,Lecia

Sales                                                          Calista

Accounting                                                 Franklyn

(6 row(s) affected)

SQL Server - Stuff()

Define:

             Stuff is a TSQL function which is use to delete specified length of Character with in a string and replace with another set of characters.

Syntax:

STUFF(Character_Expression, Start, Length, replaceWith_expresstion)

Character_Expression:

              It is an expression of character data, which can be constant, variable or table column.

Start :

             This indicates the starting position of the character in character_expression.

Length:

              It is the length of characters which need to be replaced.

replaceWith_expresstion:

             It is the string that will be replaced from the start position to the length position.

Return Type:

             Return type will be same as of Character_Expression.

Example:

     SELECT STUFF('Have a good day',8,4,'nice');

Result:

             Have a nice day

How to use Datasets with Linq in Asp.net

Define:

             Linq to Datasets provides a runtime infrastructure for querying Datable in Datasets as object without losing ability to querying. Linq to Data ships in .Net Framework 4.0.  Lin treats dataset as a Collection of columns which hold relational data. Let’s start with Simple Linq to dataset query and further in deep functionality of query.

Data:

First we will be having data in two data tables which resides in function GetEmployee() and GetDepartment(). Below is brief look at two data tables and structure.

Employee Table

Department Table

These datatables can be further added to datasets in the following ways.

DataSet ds = new DataSet();

       ds.Tables.Add(getEmployee());

       ds.Tables[0].TableName = "Emp";

       ds.Tables.Add(getDapartment());

       ds.Tables[1].TableName = "Dept";

We can even define Foreign key relation between two tables in dataset.

                DataColumn[] dc;

       dc = new DataColumn[1];

dc[0] = ds.Tables["Dept"].Columns["DeptID"];

       ds.Tables["Dept"].PrimaryKey = dc;

ForeignKeyConstraint fkcDept = new ForeignKeyConstraint(ds.Tables["Dept"].Columns["DeptID"],

ds.Tables["Emp"].Columns["DeptID"]);

       ds.Tables["Emp"].Constraints.Add(fkcDept);

Select Linq Query on Dataset

Employee Table

var query = (from a in ds.Tables["Emp"].AsEnumerable()

       select new

       {

EmpId = a["EmpId"],

              Name = a["Name"],

              Salary = a["Salary"],

});

gvData.DataSource = query;

       gvData.DataBind();

Result

Department Table

var query = (from a in ds.Tables["Dept"].AsEnumerable()

       select new

       {

DeptId = a["DeptId"],

              Name = a["DeptName"],

});

gvData.DataSource = query;

       gvData.DataBind();

Result

Select Join Linq Query on Dataset

var query = (from a in ds.Tables["Emp"].AsEnumerable()

join b in ds.Tables["Dept"].AsEnumerable()

on a.Field<Int32>("DeptId") equals b.Field<Int32>("DeptId")

              select new

              {

                     EmpId = a["EmpId"],

                     Name = a["Name"],

                     DeptName = b["DeptName"],

                     Salary = a["Salary"],

});

Result

Where Linq Query on Dataset

var query = (from a in ds.Tables["Emp"].AsEnumerable()

join b in ds.Tables["Dept"].AsEnumerable()

on a.Field<Int32>("DeptId") equals b.Field<Int32>("DeptId")

       where b.Field<String>("DeptName") == "Information Technology"

       && a.Field<Decimal>("Salary") > 15000

       select new

       {

              EmpId = a["EmpId"],

              Name = a["Name"],

              DeptName = b["DeptName"],

              Salary = a["Salary"],

});

Result

SQL Server – Avg()

Define:

             Avg() is an Aggregate function which is use to get average of the numbers of columns.

Syntax:

AVG([All | Distinct]numeric_expression)

All:

             By default AVG(average) function applies to all.

Distinct:

             Specifies that AVG(average) is performed on each unique instance of a value, regardless how many times value had occurred.

Numeric_Expression:

              It is a numeric expression on which average to be performed. It could be column with numeric values or any unit which can be measured.

Return Types:

             Int, BigInt, Decimal, Money, Float.                                                                       

Example Table:

Employee Table:

empid       name                                salary

----------- ------------------------ --------

1001        Allyn                                10000 

1002        Bailey                               14000 

1003        Becky                              12000 

1004        Calista                              12000 

1005        Elisa                                 14000 

1006        Franklyn                           12000 

1007        Hervey                             14000 

1008        Jackson                            15000 

1009        Kimberly                          18000 

1010        Lecia                                16000 

(10 row(s) affected)

Example: All

             Suppose we have table employee, which have following data and we need average of salary of all employees.

     SELECT AVG(SALARY) AS 'Average Salary' FROM EMP;

    

Result:

Average Salary

-----------------

13700.000000

(1 row(s) affected)

Example: Distinct

             If we need average salary for all employee who have distinct salary.

             SELECT AVG(DISTINCT SALARY) AS 'Average Salary' FROM EMP;

Result:

Average Salary

-----------------

14166.666666

(1 row(s) affected)