Tuesday, December 29, 2009

Get total no of columns and rowcount for each table of database

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Monday, December 21, 2009

Auto complete Extender with Dropdown Like binding

<ajaxtoolkit:autocompleteextender behaviorid="AutoCompleteEx" id="autoComplete1" onclientitemselected="IAmSelected" runat="server" servicemethod="GetCompletionListKeyValuePair" servicepath="~/Services/AutoComplete.asmx" targetcontrolid="myTextBox">
</ajaxtoolkit:autocompleteextender>


function IAmSelected( source, eventArgs ) {
alert( " Key : "+ eventArgs.get_text() +" Value : "+eventArgs.get_value());
}

[WebMethod]

public string[] GetCompletionList(string prefixText, int count)
{

if (count == 0)
{

count = 10;

}

if (prefixText.Equals("xyz"))
{

return new string[0];

}

Random random = new Random();

List items = new List(count);

for (int i = 0; i < count; i++)
{

char c1 = (char)random.Next(65, 90);

char c2 = (char)random.Next(97, 122);

char c3 = (char)random.Next(97, 122);

items.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(prefixText + c1 + c2 + c3, i.ToString()));

}

return items.ToArray();

}

Wednesday, October 28, 2009

Insert Query Generator -- Give name of table as paramter and will generate insert quey

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[InsertGenerator]
(@tableName varchar(100))
as
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(max) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0 begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')BEGIN
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+replace(' +@colName+','''''''','''''''''''')+'''''+''''',''NULL'')+'',''+'
print @stringData
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE IF @dataType='datetime'BEGIN
--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+' --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+' -- 'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE IF @dataType='image' BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+' --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END
SET @string=@string+@colName+','FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(Max)
PRINT substring(@stringData,0,len(@stringData)-2)
print substring(@string,0,len(@string))
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2) +'''+'')'' FROM '+@tableName
exec sp_executesql @query--select @query
PRINT @query
CLOSE cursCol
DEALLOCATE cursCol

Wednesday, August 26, 2009

Itextsharp place footer at one page and at bottom

iTextSharp.text.Image footerImage = new Quote().GetFooter();

Rectangle page = document.PageSize();

PdfPTable foot = new PdfPTable(1);
foot.DefaultCell.BorderWidth = 0;
foot.DefaultCell.BorderColorBottom = new Color(255, 255, 255);

foot.AddCell(footerImage);

foot.TotalWidth = page.Width - document.LeftMargin - document.RightMargin;

foot.WriteSelectedRows(0, -1, document.LeftMargin, foot.TotalHeight - 7, writer.DirectContent);

Friday, August 7, 2009

Drag and Drop table row

http://www.isocra.com/2007/07/dragging-and-dropping-table-rows-in-javascript/#demo -- Nice demo for drag and drop Table rows.

Monday, May 18, 2009

js function convert number to money/currency format

function num2money(n_value) {

// validate input
if (isNaN(Number(n_value)))
return 'ERROR';

// save the sign
var b_negative = Boolean(n_value < 0);
n_value = Math.abs(n_value);

// round to 1/100 precision, add ending zeroes if needed
var s_result = String(Math.round(n_value*1e2)%1e2 + '00').substring(0,2);

// separate all orders
var b_first = true;
var s_subresult;
while (n_value > 1) {
s_subresult = (n_value >= 1e3 ? '00' : '') + Math.floor(n_value%1e3);
s_result = s_subresult.slice(-3) + (b_first ? '.' : ',') + s_result;
b_first = false;
n_value = n_value/1e3;
}
// add at least one integer digit
if (b_first)
s_result = '0.' + s_result;

// apply formatting and return
return b_negative
? '($' + s_result + ')'
: '$' + s_result;
}

Friday, April 10, 2009

XML File Generation

-- Generate Parent Element
if(!File.Exist(Path))
{
XmlTextWriter writer = new XmlTextWriter(Path);
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument();
writer.WriteStartElement("ElementName");
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Close();
}
-- Generate Child Element
XmlDocument xmldoc = new XmlDocument();
XMLNode root = xmldoc.DocumentElement;
xmldoc.Load(path);
XmlElement ChildInfo = xmldoc.CreateElement("ChildElement");
ChildInfo.InnerText = "Test";
root.AppendChild(childInfo);

Asp Net Service setup

You can create windows service functionality usinig aspnet service.
On Application_Start Event you have to register cacheitem like below:-
private const string DummyCacheItemKey = "TestKey";
void Application_Start(object sender, EventArgs e)
{
RegisterCacheEntry();
}

private void RegisterCacheEntry()
{
// Prevent duplicate key addition
if (null != HttpContext.Current.Cache[DummyCacheItemKey]) return;

HttpContext.Current.Cache.Add(DummyCacheItemKey, "Test", null, DateTime.MaxValue,
TimeSpan.FromMinutes(1), CacheItemPriority.NotRemovable,
new CacheItemRemovedCallback(CacheItemRemovedCallback));
}
public void CacheItemRemovedCallback(string key, object value, CacheItemRemovedReason reason)
{


// Do the service works
DoWork();

// We need to register another cache item which will expire again in one
// minute. However, as this callback occurs without any HttpContext, we do not
// have access to HttpContext and thus cannot access the Cache object. The
// only way we can access HttpContext is when a request is being processed which
// means a webpage is hit. So, we need to simulate a web page hit and then
// add the cache item.
HitPage();
}
private void DoWork()
{

//task that you want to perform;
}

private void HitPage()
{
System.Net.WebClient client = new System.Net.WebClient();
client.DownloadData(Application["AppUrl"].ToString()+ "/DummyForm.aspx");
}
protected void Application_BeginRequest(object sender, EventArgs e)
{
System.Web.HttpApplication app = (System.Web.HttpApplication)sender;
string url = app.Request.Url.AbsoluteUri.ToString();
if (url.IndexOf("DummyForm.aspx") != -1)
{
// Add the item in cache and when succesful, do the work.
RegisterCacheEntry();
}
}

Thursday, February 26, 2009

JS for Paging HTML

function PagingHTML(PageIndex,PageSize,TotalRecords,LastRecord,fnName){
var strHTML = "<table width=100% border=\"0\"><tbody><tr style=\"font-weight: bold;\"><td><div style='float:right;'>";
var j = parseInt(TotalRecords / PageSize);
if (TotalRecords % PageSize != 0)
j++;
i = parseInt((PageIndex - 1) / 10) * 10 + 1;
if ((j - i) < 10 && j > 10)
i = j - 9;
var k;
if (PageIndex > 10)
strHTML += "<div style='float:left;padding-left:5px;'><a class=\"Paging\" href=\"javascript:"+fnName+"(" + (i - 1) + ")\"> ... </a></div>";
for (k = 1; i <= j && k <= 10; i++, k++){
if (i == PageIndex)
strHTML += "<div style='float:left;padding-left:5px;' class=\"Paging\">" + i + "</div>";
else
strHTML += "<div style='float:left;padding-left:5px;'><a class=\"Paging\" href=\"javascript:"+fnName+"(" + i + ")\">" + i + "</a></div>";
}
if (j >= i)
strHTML += "<div style='float:left;padding-left:5px;'><a class=\"Paging\" href=\"javascript:"+fnName+"(" + i + ")\"> ... </a></div>";
strHTML += "</div><div class=\"Paging\">Showing " + (((PageIndex - 1) * PageSize) + > + " - " + (((PageIndex - 1) * PageSize) + LastRecord) + " of " + TotalRecords + "</div>";
strHTML += "</td></tr></tbody></table>";
return strHTML;
}

Monday, February 23, 2009

Javascript for drag an htmll element

Javascript
function startdrag(t, e) {
if (e.preventDefault) e.preventDefault(); //line for IE compatibility
e.cancelBubble = true;
window.document.onmousemoveOld = window.document.onmousemove;
window.document.onmouseupOld = window.document.onmouseup;
window.document.onmousemove=dodrag;
window.document.onmouseup=stopdrag;
window.document.draged = t;
t.dragX = e.clientX;
t.dragY = e.clientY;
return false;
}

function dodrag(e) {

if (!e) e = event; //line for IE compatibility
t = window.document.draged;
t.style.left = (t.offsetLeft + e.clientX - t.dragX)+"px";
t.style.top = (t.offsetTop + e.clientY - t.dragY)+"px";
t.dragX = e.clientX;
t.dragY = e.clientY;
return false;
}
//restore event-handlers
function stopdrag() {
window.document.onmousemove=window.document.onmousemoveOld;
window.document.onmouseup=window.document.onmouseupOld;
}

HTML
<DIV
style="width:100px;height:100px;background:#EEEEEE;border:1px solid black"
><table style="position:absolute;border-color: black; border-style: solid; border-width: 1px" width="20%" onmousedown="startdrag(this, event);"><tr><td>Darg Me</td></tr></table></DIV>

Thursday, February 5, 2009

How to implement ICallbackEventHandler

-------- code behind---------
public partial class Home : System.Web.UI.Page, ICallbackEventHandler
{
public string sCallBackFunctionInvocation;
sCallBackFunctionInvocation = this.ClientScript.GetCallbackEventReference(this, "sId", "CallBackReturnFunction", "oContext", "OnCallBackError",false) + ";";
}

protected string returnValue;
public string GetCallbackResult()
{
return returnValue;
//throw new Exception("The method or operation is not implemented.");
}

public void RaiseCallbackEvent(string eventArgument)
{
//throw new Exception("The method or operation is not implemented.");
try
{
returnValue = //retrieve data from argument
}
catch (Exception ex)
{
ErrolLogController.LogError(ex.Message);
}
}






--------- Javascript---------------

function clickEv()
{
var sId = id;
var oContext = new Object();
<%=sCallBackFunctionInvocation%>
}

function CallBackReturnFunction(sReturnValue, oContext)
{
//do inner HTML setup
}

function OnCallBackError(exception,context)
{
alert(exception);
}

Monday, February 2, 2009

Popup div using javascript

-----------style------------------------------
#backgroundFilter
{
position: absolute;
top: 0;
left: 0;
overflow: hidden;
padding: 0;
margin: 0;
background-color: #000;
filter: alpha(opacity=70);
opacity: 0.17;
display: none;
z-index: 1000100;
width: 100%;
height: 100%;
}
/* POPUP WINDOW */
#popupWindow
{
position: absolute;
width: 383px;
height: 300px;
padding: 1px;
z-index: 1000300;
display: none;
background-color: #ddd;
border: 1px solid black;
}
#topLeft
{
width:357px;
float:left;
}
#topRight
{
width:23px;
float:left;
}
#popupBody
{
width:380px;
margin: 30px 0 0 0;
}
-------------------script--------------------------------------
if (!document.all)
document.captureEvents(Event.MOUSEMOVE)

// On the move of the mouse, it will call the function getPosition
document.onmousemove = getPosition;

// These varibles will be used to store the position of the mouse
var X = 0
var Y = 0

// This is the function that will set the position in the above varibles
function getPosition(args)
{
// Gets IE browser position
if (document.all)
{
X = event.clientX + document.body.scrollLeft
Y = event.clientY + document.body.scrollTop
}

// Gets position for other browsers
else
{
X = args.pageX
Y = args.pageY
}
}
function backgroundFilter()
{
var div;

if(document.getElementById)
// Standard way to get element
div = document.getElementById('backgroundFilter');
else if(document.all)
// Get the element in old IE's
div = document.all['backgroundFilter'];

// if the style.display value is blank we try to check it out here
if(div.style.display==''&&div.offsetWidth!=undefined&&div.offsetHeight!=undefined)
{
div.style.display = (div.offsetWidth!=0&&div.offsetHeight!=0)?'block':'none';
}

// If the background is hidden ('none') then it will display it ('block').
// If the background is displayed ('block') then it will hide it ('none').
div.style.display = (div.style.display==''||div.style.display=='block')?'none':'block';
}

function popUp()
{
var div;

if(document.getElementById)
// Standard way to get element
div = document.getElementById('popupWindow');
else if(document.all)
// Get the element in old IE's
div = document.all['popupWindow'];

// if the style.display value is blank we try to check it out here
if(div.style.display==''&&div.offsetWidth!=undefined&&div.offsetHeight!=undefined)
{
div.style.display = (div.offsetWidth!=0&&elem.offsetHeight!=0)?'block':'none';
}

// If the PopUp is hidden ('none') then it will display it ('block').
// If the PopUp is displayed ('block') then it will hide it ('none').
div.style.display = (div.style.display==''||div.style.display=='block')?'none':'block';

// Off-sets the X position by 15px
X = X + 15;

// Sets the position of the DIV
div.style.left = X+'px';
div.style.top = Y+'px';
}


----------HTML----------------
<div id="popupWindow" style="display:none">
<div id="topLeft">
<img alt="View Links" height='23' width='357' src='images/title.gif' />
</div>
<div id="topRight">
<a href='javascript:popUp();backgroundFilter();'>
<img height='23' width='23' src='images/close.gif' alt='Close' /></a>
</div>
<div id="popupBody">
<ul>
<li><a href="http://www.blacksoulstrangers.info">Black Soul Strangers</a></li>
<li><a href="http://www.google.com">Google</a></li>
<li><a href="http://www.msn.com">MSN</a></li>
<li><a href="http://www.stephendaly.info">Stephen Daly</a></li>
<li><a href="http://www.yahoo.com">Yahoo!</a></li>
</ul>
</div>
</div>