DotNet-Jitendra(Asp.Net, C#, Sql Server, Linq, Ajax, Javascript, jQuery, Xml, MVC)

Monday, 30 December 2013

Search GridView records (data) on TextBox KeyPress using jQuery in ASP.Net

In order to search GridView records (data) on TextBox KeyPress event, I am making use of jQuery QuickSearch Plugin which dynamically searches the GridView cells and filters out the unwanted rows and displays only the records (data) that matches the input search term.

Desingner Source Code :

body

{

font-family: Arial;

font-size: 10pt;

}

table

{

border:1px solid #ccc;

}

table th

{

background-color: #F7F7F7;

color: #333;

font-weight: bold;

}

table th, table td

{

padding: 5px;

border-color: #ccc;

}

</style>

</head>

<body>

<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"

runat="server" AutoGenerateColumns="false" OnDataBound="OnDataBound">

<asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />

<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="100" />

<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100" />

</Columns>

</asp:GridView>

$(function () {

$('.search_textbox').each(function (i) {

$(this).quicksearch("[id*=GridView1] tr:not(:has(th))", {

'testQuery': function (query, txt, row) {

return $(row).children(":eq(" + i + ")").text().toLowerCase().indexOf(query[0].toLowerCase()) != -1;

}

});

</script>

</form>

</body>

</html>

Code Behind :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!this.IsPostBack)

{

DataTable dt = new DataTable();

dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),

new DataColumn("Name", typeof(string)),

new DataColumn("Country",typeof(string)) });

dt.Rows.Add(1, "John Hammond", "United States");

dt.Rows.Add(2, "Jitendra Gangwar", "India");

dt.Rows.Add(3, "Suzanne Mathews", "France");

dt.Rows.Add(4, "Robert Schidner", "Russia");

GridView1.DataSource = dt;

GridView1.DataBind();

}

protected void OnDataBound(object sender, EventArgs e)

{

GridViewRow row = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Normal);

for (int i = 0; i < GridView1.Columns.Count; i++)

{

TableHeaderCell cell = new TableHeaderCell();

TextBox txtSearch = new TextBox();

txtSearch.Attributes["placeholder"] = GridView1.Columns[i].HeaderText;

txtSearch.CssClass = "search_textbox";

cell.Controls.Add(txtSearch);

row.Controls.Add(cell);

}

GridView1.HeaderRow.Parent.Controls.AddAt(1, row);

}

Download Here

Friday, 20 December 2013

Get Table Size in SQL Server 2008

To get table size in SQL server we need to write the query like as shown below

Example

SP_SPACEUSED 'USERDETAILS'

Once we run above query we will get output like as shown below

SQL Server - How to Get Database Size in SQL Server 2008

To get database size in SQL server we need to write the query like as shown below

SELECT

database_name = DB_NAME(database_id)

, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))

, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 ASDECIMAL(8,2))

, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))

FROM sys.master_files WITH(NOWAIT)

WHERE DB_NAME(database_id) = 'master' –-- your database name

GROUP BY database_id

Once we run above code we will get output like as shown below

jQuery Set Maxlength for Textbox in Asp.net

To implement this we need to write the code like as shown below

Designer Source Code

<head>

<title>jQuery set maxlength for multiline textbox in asp.net</title>

</script>

$(function() {

$('#txtdesc').keypress(function(e) {

var txt = $(this).val();

if (txt.length > 50) {

e.preventDefault();

}

});

</script>

</head>

<body>

<table>

<tr>

<td>

<b>Enter Text:</b>

</td>

<td>

<asp:TextBox ID="txtdesc" TextMode="MultiLine" runat="server" ></asp:TextBox>

</td>

</tr>

</table>

</form>

</body>

</html>

Wednesday, 18 December 2013

Inner join with Group by and case statement in Linq to sql

I have the following sql below which I am having real trouble getting into a LINQ statement. The specific issue is that for one of the fields I am using a CASE statement both in the select and has part of the HAVING clause. Various reasons why i needed to do this but I cant seem to reproduce it in LINQ.




Here is the sql Query





select Count (DetailID) as MonthCount ,

StoreID, DetailID,

SUM(Amount ) as AnnualAmount, PAYear, 

MIN(CASE when ExportFlag = 'SUCCESS' then 1 when ExportFlag is NULL or 

ExportFlag = '' then 0 else - 1 end) as ExpFlag

from  Input_Financials

inner join DestinationDetail cdd on

cdd.ID =Financials.DetailID

group by StoreID, DetailID, PAYear

Having MIN (CASE when ExportFlag = 'SUCCESS' then 1 when ExportFlag is NULL

then 0 else -1 end ) = 0

and Count (*) >= 1


order by PAYear



Convert This query in Linq to Sql





var query = from inf in inputActs

join cgrdets in cgrDetails on inf.DestinationDetailID

            equals cgrdets.ID


            let export = (inf.ExportFlag == "SUCCESS" ? 2:

            inf.ExportFlag== "ERROR" ? 1:

            inf.ExportFlag == null ? 0: 99)

group inf by new {export, inf.StoreID, 

inf.DestinationDetailID,inf.PAYr } into g

            where g.Key.export == 0 

select new { MonthCount = g.Count(), g.Key.StoreID,

g.Key.PAYr,Amount = g.Sum(inf=>inf.Amount) };










please ignore some minor discrepancies you may find in naming of fields between these two -
its just probably because i renamed some quickly for the sake of this question.

Menu

Monday, 30 December 2013

Search GridView records (data) on TextBox KeyPress using jQuery in ASP.Net

Friday, 20 December 2013

Get Table Size in SQL Server 2008

SQL Server - How to Get Database Size in SQL Server 2008

jQuery Set Maxlength for Textbox in Asp.net

Wednesday, 18 December 2013

Inner join with Group by and case statement in Linq to sql