Asp.net C# , Sqlserver Professional

Wednesday, 18 December 2013

Assigning value to sqlvariable from dynamic query - SQL Server

Declare @sql nvarchar(max),
@sql_id INT

SELECT @sql=N'SELECT TOP 1 @sql_id =colName FROM Table1'

EXEC sp_executesql @sql, N'@sql_id INT output', @sql_id output

SELECT @sql_id

Tuesday, 29 October 2013

Compress ViewState and remove whitespace from HTML Using baseclass

1. Create Compressor class
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO.Compression;
using System.IO;
public static class Compressor
{
public static byte[] Compress(byte[] data)
{
MemoryStream output = new MemoryStream();
GZipStream gzip = new GZipStream(output, CompressionMode.Compress, true);
gzip.Write(data, 0, data.Length);
gzip.Close();
return output.ToArray();
}

public static byte[] Decompress(byte[] data)
{
MemoryStream input = new MemoryStream();
input.Write(data, 0, data.Length);
input.Position = 0;
GZipStream gzip = new GZipStream(input, CompressionMode.Decompress, true);
MemoryStream output = new MemoryStream();
byte[] buff = new byte[64];
int read = -1;
read = gzip.Read(buff, 0, buff.Length);
while (read > 0)
{
output.Write(buff, 0, read);
read = gzip.Read(buff, 0, buff.Length);
}
gzip.Close();
return output.ToArray();
}
}
----------------------------------------------

2. create BaseClass

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.IO;

using System.Text.RegularExpressions;

using System.Collections.ObjectModel;

public class BaseClass : Page

{

private static readonly Regex RegexBetweenTags = new Regex(@">(?! )\s+", RegexOptions.Compiled);

private static readonly Regex RegexLineBreaks = new Regex(@"([\n\s])+?(?<= {2,})<", RegexOptions.Compiled);

protected override object LoadPageStateFromPersistenceMedium()

{

string viewState = Request.Form["__VSTATE"];

byte[] bytes = Convert.FromBase64String(viewState);

bytes = Compressor.Decompress(bytes);

LosFormatter formatter = new LosFormatter();

return formatter.Deserialize(Convert.ToBase64String(bytes));

}

protected override void SavePageStateToPersistenceMedium(object viewState)

{

LosFormatter formatter = new LosFormatter();

StringWriter writer = new StringWriter();

formatter.Serialize(writer, viewState);

string viewStateString = writer.ToString();

byte[] bytes = Convert.FromBase64String(viewStateString);

bytes = Compressor.Compress(bytes);

ClientScript.RegisterHiddenField("__VSTATE", Convert.ToBase64String(bytes));

}

protected override void Render(HtmlTextWriter writer)

{

using (HtmlTextWriter htmlwriter = new HtmlTextWriter(new System.IO.StringWriter()))

{

base.Render(htmlwriter);

string html = htmlwriter.InnerWriter.ToString();

html = Regex.Replace(html, @"(?<=[^])\t{2,}|(?<=[>])\s{2,}(?=[<])|(?<=[>])\s{2,11}(?=[<])|(?=[\n])\s{2,}", string.Empty);

writer.Write(RemoveWhitespaceFromHtml(html));

}

public static string RemoveWhitespaceFromHtml(string html)

{

html = RegexBetweenTags.Replace(html, ">");

html = RegexLineBreaks.Replace(html, "<");

return html.Trim();

}

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

3. Now crate a page and used

public partial class portal : BaseClass instand of public partial class portal : System.Web.UI.Page

Thursday, 3 October 2013

How to masking in SQL server

CREATE FUNCTION [dbo].[fn_text_masking]
(
@in_input VARCHAR(MAX),
@in_mask_len INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @mask_text VARCHAR(MAX), @mask_char VARCHAR(20)
SET @mask_char = 'XXXXXXXXXXXXXXXXXXXX'

SELECT @mask_text = REPLACE(@in_input, SUBSTRING(@in_input, 1, LEN(@in_input) - @in_mask_len),
SUBSTRING(@mask_char, 1, LEN(@in_input) - @in_mask_len))

RETURN @mask_text
END
------------------------------------------------
SELECT dbo.fn_text_masking('123456789',4)

Friday, 27 September 2013

jquery ajax call success, how do i change a global variable in the JavaScript function?

Sometimes we have problems on jquery success function.

we are assign a global variable value within the sucess function,
now we want to access that variable, we will see that variable till now hold the 1st value not taking the assign value.
How We solve the problem...

var strValue =null;
function validateEmail() {
var obj = {
emailText: $('#txtemail').val(),
int_mode: 2
};

$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "webservice/remote_service.asmx/webserviceTestEmail",
dataType: "json",
data: "JSON.stringify(obj),
success: onSuccess,
error: function(result) {
alert("Error");
}
});
}

function onSuccess {
if (result.strControlerMessage == 'n') {
strValue = "n";
}
else {
strValue = "y";
}
}
function validateForm(){
validateEmail();

alert(strValue); /////we will see its return ""

}
//////////////////////////////////////////////////////////////
Solution Of this problem
use cache: false,
async: false,
before jquery success

$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "webservice/remote_service.asmx/webserviceTestEmail",
dataType: "json",
cache: false,
async: false,
data: "JSON.stringify(obj),
success: onSuccess ,
error: function(result) {
alert("Error");
}
});

Sunday, 30 June 2013

Generate list of months in sqlserver

DECLARE @year INT
SET @year = 2013

;WITH mths AS(
SELECT 1 AS mth, DATENAME(MONTH, CAST(@year*100+1 AS VARCHAR) + '01') AS monthname
UNION ALL
SELECT mth+1, DATENAME(MONTH, CAST(@year*100+(mth+1) AS VARCHAR) + '01') FROM mths WHERE mth < 12
)
SELECT * FROM mths

Monday, 10 June 2013

Send E-mail by javascript/ jquery using webservice

Friday, 7 June 2013

Whats wrong with IsNumeric() Or Alternative of isnumeric

SQL's ISNUMERIC() function has a problem. It can falsely interpret non-numeric letters and symbols (such as D, E ,',' ), and even tabs (CHAR(9)) as numeric.like 12,90

CREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

DECLARE @pos TINYINT

SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))

RETURN CASE
WHEN PATINDEX('%[^0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
go
----------------------------------
CREATE FUNCTION dbo.isReallyInteger
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

RETURN CASE
WHEN PATINDEX('%[^0-9-]%', @num) = 0
AND CHARINDEX('-', @num) <= 1
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
THEN
1
ELSE
0
END
END
GO