How to make dynamic stored procedure .

How to make dynamic stored procedure .

-- =============================================
-- Author: <Author,,Omveer Singh>
-- Create date: <Create Date,29/06/2013,>
-- Description: <Description,tO SHOW COMMUNICATION HISTORY,>
-- Example : EXEC SP_GET_USER_INFO '1/Jan/2013','30/Dec/2013',1,'1'
-- =============================================
CREATE PROCEDURE [dbo].[SP_GET_USER_INFO]
(
@FromDate varchar(50),
@ToDate varchar(50),
@Status varchar(50),
@MemberType varchar(50)
)
AS

Declare @query varchar(8000)
Declare @Where varchar(8000)

BEGIN
SET @query='SELECT * FROM [USER] '
SET @Where=''


IF (ISNULL(@FromDate,'')<>'')
BEGIN
SET @Where=@Where+'[User].RegistrationDate'+'>='''+ @FromDate + Char(39)
END

IF (ISNULL(@ToDate,'')<>'')
BEGIN
IF (LEN(@Where)>1)
begin
SET @Where=@Where + ' AND [User].RegistrationDate<=' + Char(39) + @ToDate + Char(39)
end
Else
begin
SET @Where=@Where + '[dbo].[User].RegistrationDate<='+ Char(39) + @ToDate + Char(39)
END
END

IF (ISNULL(@Status,'')<>'')
BEGIN
IF (LEN(@Where)>1)
begin
SET @Where=@Where + ' AND [dbo].[User].Status='+@Status
END
else
begin
SET @Where=@Where + '[dbo].[User].Status='+@Status
end
END
IF (ISNULL(@MemberType,'')<>'')
BEGIN
IF (LEN(@Where)>1)
begin
SET @Where=@Where + ' AND [dbo].[User].UserTypeId='+@MemberType
END
else
begin
SET @Where=@Where + '[dbo].[User].UserTypeId='+@MemberType
end
END
IF(ISNULL(@Where,'')<>'')
BEGIN
SET @query=@query+ ' WHERE ' + @Where
--PRINT @query
END
EXEC (@query)
END
GO

SORTING ASP.NET GRIDVIEW CONTROL USING JQUERY TABLESORTER PLUGIN

SORTING  ASP.NET GRIDVIEW CONTROL USING JQUERY TABLESORTER PLUGIN


Download jquery first which is given in below .


<script src="scripts/jquery-1.4.3.min.js" type="text/javascript"></script>
<script src="scripts/jquery.tablesorter.min.js" type="text/javascript"></script>


<script type="text/javascript">
   $(document).ready(function() {

      $("#GridView1").tablesorter();

   }); 
</script>


<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" 
   GridLines="Horizontal" Font-Size="9pt" Font-Names="Arial" 
   AutoGenerateColumns="False" BorderColor="#dadada" 
   BorderStyle="Solid" BorderWidth="1px">
   <Columns>
      <asp:BoundField DataField="ID" HeaderText="ID" 
         ItemStyle-Width="40" />
      <asp:BoundField DataField="Name" HeaderText="Name" 
         ItemStyle-Width="80" />
      <asp:BoundField DataField="Fee" DataFormatString="{0:n0}" HeaderText="Fee" 
         ItemStyle-Width="60" />
      <asp:BoundField DataField="Price" DataFormatString="{0:c}" 
         HeaderText="Price" ItemStyle-Width="60" />
      <asp:BoundField DataField="Discount" DataFormatString="{0:p1}" 
         HeaderText="Discount" ItemStyle-Width="70" />
      <asp:BoundField DataField="Difference" DataFormatString="{0:n1}" 
         HeaderText="Difference" ItemStyle-Width="80" />
      <asp:BoundField DataField="Date" DataFormatString="{0:MMM dd, yyyy}" 
         HeaderText="Date" ItemStyle-Width="100" />
      <asp:BoundField DataField="OnSale" HeaderText="OnSale" 
         ItemStyle-Width="60" />
   </Columns> 
</asp:GridView>

IF YOU DOES NOT WANT FILL DATA FROM END THAN YOU CAN BIND THE DATABASE TABLE .

protected void Page_Load(object sender, EventArgs e)
{
   if (!Page.IsPostBack)
   {
      BindData();
   }
}
private void BindData()
{
   int[] ids = {12, 13, 14, 15, 16};
   string[] names = {"Alice", "James", "Peter", "Simon", "David"};
   int[] fee = { 2299, 5123, 7564, 9595, 1600 };
   decimal[] prices = { 12.99m, 122.23m, 25.64m, 66.85m, 1.60m };
   decimal[] discounts = { 0.2m, 0.194m, 0.4564m, 0.209m, 0.310m };
   decimal[] differences = { -12m, 19.4m, -45.64m, 200.9m, 41.60m };
   string[] dates = { "04-12-2010", "07-23-2010", "07-14-2009", "12-12-2010", "11-03-2019" };
   bool[] onSale = { true, false, true, true, false };

   DataTable table = new DataTable();
   table.Columns.Add("ID", typeof(System.Int32));
   table.Columns.Add("Name", typeof(System.String));
   table.Columns.Add("Fee", typeof(System.Decimal));
   table.Columns.Add("Price", typeof(System.Decimal));
   table.Columns.Add("Discount", typeof(System.Decimal));
   table.Columns.Add("Difference", typeof(System.Int32));
   table.Columns.Add("Date", typeof(System.DateTime));
   table.Columns.Add("OnSale", typeof(System.Boolean));

   for (int i = 0; i < 5; i++)
   {
      DataRow row = table.NewRow();

      row["ID"] = ids[i];
      row["Name"] = names[i];
      row["Fee"] = fee[i];
      row["Price"] = prices[i];
      row["Discount"] = discounts[i];
      row["Difference"] = differences[i];
      row["Date"] = DateTime.Parse(dates[i]);
      row["OnSale"] = onSale[i];

      table.Rows.Add(row);
   } 

   GridView1.DataSource = table; 
   GridView1.DataBind();

   GridView1.UseAccessibleHeader = true;
   GridView1.HeaderRow.TableSection = TableRowSection.TableHeader; 
}

<style type="text/css">
   th
   {
      cursor:pointer;
      background-color:#dadada;
      color:Black;
      font-weight:bold;
      text-align:left; 
   }
   th.headerSortUp 
   {     
      background-image: url(images/asc.gif);
      background-position: right center;
      background-repeat:no-repeat; 
   }
   th.headerSortDown 
   {     
      background-image: url(images/desc.gif);   
      background-position: right center;
      background-repeat:no-repeat; 
   } 
   td
   {
      border-bottom: solid 1px #dadada;    
   }
</style>

nchar,nvarchar,char,varchar differences

nchar,nvarchar,char,varchar differences

• nchar and nvarchar can store Unicode characters.
• char and varchar cannot store Unicode characters.
• char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.
• varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.

nchar and nvarchar will take up twice as much storage space, so it may be wise to use them only if you need Unicode support.