To Generate Table Structure in Excel Sheet.

This blog help the people who want to generate a excel sheet of the all table structure for specified database. Following is the procedure which generate a Excel sheet in your D drive for all table structure of specified database.

Before Executing this procedure you have to follow following settings.

1).Microsoft SQL Server 2005 -> Configuration Tools -> Surface Area Configuration -> Surface Area Configuration for Features

2) Mark True on Enable Web Assistant.

Or

Run Following query in sql server which will enable web assistant procedures.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Web Assistant Procedures', 1;

GO

RECONFIGURE

GO

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SP_CREATE_EXCEL_STRUCTURE_FOR_ALLDATABASE]

(

@db_name VARCHAR(100)

)

AS

BEGIN

DECLARE @columns VARCHAR(8000), @sql VARCHAR(8000), @data_file VARCHAR(100)

DECLARE @file_name varchar(100),@table_name VARCHAR(100)

DECLARE @Constraint VARCHAR(50)

DECLARE C1 CURSOR FOR select name from sys.tables

OPEN C1

FETCH NEXT FROM C1 INTO @table_name

WHILE @@FETCH_STATUS=0

BEGIN

drop TABLE MYTMP

SELECT @Constraint=B.COLUMN_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A,

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND A.TABLE_NAME=@table_name

SELECT column_name FieldName,Data_type DataType,

character_maximum_length Size,is_nullable AllowNulls

INTO MYTMP FROM information_schema.columns

WHERE table_name=@table_name

ALTER TABLE MYTMP ADD PrimaryKey VARCHAR(20)

ALTER TABLE MYTMP ADD ForeignKey VARCHAR(70)

ALTER TABLE MYTMP ADD AutoIncrement VARCHAR(20)

ALTER TABLE MYTMP ADD DefaultValue VARCHAR(50)

ALTER TABLE MYTMP ADD Description VARCHAR(500)

UPDATE MYTMP SET PrimaryKey='YES' WHERE FieldName=@Constraint

UPDATE MYTMP SET PrimaryKey='' WHERE FieldName<>@Constraint

---=================To Add Foreign key=================================

UPDATE MYTMP SET ForeignKey=Tmp.ForeignKey

From

(SELECT COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id)

AS ReferenceColumnName,

OBJECT_NAME (f.referenced_object_id)+

'('+COL_NAME(fc.referenced_object_id,fc.referenced_column_id)+')'

As ForeignKey

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc ON

f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME(f.parent_object_id)=@TABLE_NAME)As Tmp

WHERE FieldName=Tmp.ColumnName

UPDATE MYTMP SET ForeignKey='' WHERE ForeignKey IS NULL

----======================To Set Default Value===============================

UPDATE MYTMP SET DefaultValue=Tmp.TEXT

FROM

(SELECT name,REPLACE(REPLACE(TEXT,'(',''),')','')TEXT FROM syscolumns

inner join syscomments on(syscomments.id=syscolumns.cdefault)

WHERE syscolumns.id = object_id(@Table_name) AND cdefault > 0)As Tmp

WHERE FieldName=Tmp.Name

UPDATE MYTMP SET DefaultValue='' WHERE DefaultValue IS NULL

----======================= To set Auto Increment============================

UPDATE MYTMP SET AutoIncrement='YES'

FROM

(SELECT NAME AS COLUMNNAME FROM SYS.IDENTITY_COLUMNS

WHERE OBJECT_NAME(OBJECT_ID)=@Table_name)AS TMP

WHERE FieldName=Tmp.ColumnName

UPDATE MYTMP SET AutoIncrement='' WHERE AutoIncrement IS NULL

----========================To Set Description ==============================

UPDATE MYTMP SET Description=Tmp.Description

FROM

(SELECT convert(varchar(500),ep.value) AS Description,c.name AS ColumnName

FROM sys.objects o

INNER JOIN sys.extended_properties ep ON o.object_id = ep.major_id

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

LEFT JOIN syscolumns c ON ep.minor_id = c.colid AND ep.major_id = c.id

WHERE o.type='U' and o.name=@table_name)As Tmp

Where FieldName=Tmp.ColumnName

UPDATE MYTMP SET Description='' WHERE Description IS NULL

---===============================================================

SET @file_name='D:\'+@table_name+'.xls'

--===================================

exec sys.sp_makewebtask @outputfile = @file_name,

@query = 'Select * from mytmp',

@colheaders =1,

@FixedFont=0,@lastupdated=1,@resultstitle=@table_name,

@dbname =@db_name

--========================================

FETCH NEXT FROM C1 INTO @table_name

END

CLOSE C1

DEALLOCATE C1

END

------------------TO EXECUTE

--SP_CREATE_EXCEL_STRUCTURE_FOR_ALLDATABASE 'jito'

Split String using XML

This blog will help developers looking to split strings in a single query using XML. We generally use a user defined function, which we have used in our database that splits the string based on the delimiter passed. But, when it comes to separate the string in a single query without any help of user defined function we often get panic. I have found a much optimized and shorter way of splitting any string based on the delimiter passed. I will be using the power of XML to do the same.

Let's say for example there is a string 'A,B,C,D,E' and I want to split it based on the delimiter ','. The first step would be to convert that string into XML, replacing the delimiter with some start and end XML tag.

Declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)

SET @str='A,B,C,D,E'

SET @delimiter =','

SET @xml = cast((''+replace(@str,@delimiter ,'')+'') as xml)

SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

--OUTPUT

--A

--B

--C

--D

--E

It will improve your performance.

Enjoy!!!!!!

Take Screen Shot Through Coding

Imports Microsoft.VisualBasic
Imports System
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.Windows.Forms
Imports System.Diagnostics

Namespace Lions

Public Class GetImage
Private S_Height As Integer
Private S_Width As Integer
Private F_Height As Integer
Private F_Width As Integer
Private MyURL As String

Property ScreenHeight() As Integer
Get
Return S_Height
End Get
Set(ByVal value As Integer)
S_Height = value
End Set
End Property

Property ScreenWidth() As Integer
Get
Return S_Width
End Get
Set(ByVal value As Integer)
S_Width = value
End Set
End Property

Property ImageHeight() As Integer
Get
Return F_Height
End Get
Set(ByVal value As Integer)
F_Height = value
End Set
End Property

Property ImageWidth() As Integer
Get
Return F_Width
End Get
Set(ByVal value As Integer)
F_Width = value
End Set
End Property

Property WebSite() As String
Get
Return MyURL
End Get
Set(ByVal value As String)
MyURL = value
End Set
End Property

Sub New(ByVal WebSite As String, ByVal ScreenWidth As Integer, ByVal ScreenHeight As Integer, ByVal ImageWidth As Integer, ByVal ImageHeight As Integer)
Me.WebSite = WebSite
Me.ScreenWidth = ScreenWidth
Me.ScreenHeight = ScreenHeight
Me.ImageHeight = ImageHeight
Me.ImageWidth = ImageWidth
End Sub

Function GetBitmap() As Bitmap
Dim Shot As New WebPageBitmap(Me.WebSite, Me.ScreenWidth, Me.ScreenHeight)
Shot.GetIt()
Dim Pic As Bitmap = Shot.DrawBitmap(Me.ImageHeight, Me.ImageWidth)
Return Pic
End Function
End Class

Public Class WebPageBitmap
Dim MyBrowser As WebBrowser
Dim URL As String
Dim Height As Integer
Dim Width As Integer
Dim Ready As Boolean

Sub New(ByVal url As String, ByVal width As Integer, ByVal height As Integer)
Me.Height = height
Me.Width = width
Me.URL = url
MyBrowser = New WebBrowser
MyBrowser.ScrollBarsEnabled = False
MyBrowser.Bounds.Inflate(MyBrowser.Size)
MyBrowser.Size = New Size(Me.Width, Me.Height)

End Sub

Sub GetIt()
MyBrowser.Navigate(Me.URL)
While MyBrowser.ReadyState <> WebBrowserReadyState.Complete
MyBrowser.Height = MyBrowser.Bounds.Height
MyBrowser.Width = MyBrowser.Bounds.Width
Application.DoEvents()
End While
End Sub

Function DrawBitmap(ByVal theight As Integer, ByVal twidth As Integer) As Bitmap

Dim myBitmap As New Bitmap(MyBrowser.ClientSize.Width, MyBrowser.ClientSize.Height)
Dim DrawRect As New Rectangle(0, 0, Width, Height)
MyBrowser.DrawToBitmap(myBitmap, DrawRect)
Dim imgOutput As System.Drawing.Image = myBitmap
Dim oThumbNail As System.Drawing.Image = New Bitmap(twidth, theight, imgOutput.PixelFormat)
Dim g As Graphics = Graphics.FromImage(oThumbNail)
g.CompositingQuality = Drawing2D.CompositingQuality.HighSpeed
g.SmoothingMode = Drawing2D.SmoothingMode.HighSpeed
g.InterpolationMode = Drawing2D.InterpolationMode.HighQualityBilinear
Dim oRectangle As Rectangle = New Rectangle(0, 0, twidth, theight)
g.DrawImage(imgOutput, oRectangle)
Try
Return oThumbNail
Catch ex As Exception
Finally
imgOutput.Dispose()
imgOutput = Nothing
MyBrowser.Dispose()
MyBrowser = Nothing
End Try
End Function
End Class

End Namespace

_________________________________________________________________



Dim sPath = Server.MapPath("EmailTemplate/emailtmplt.html")
'Dim sFile = Request.QueryString("MID")
Dim sSource = sPath

Dim Writer As New IO.StreamWriter(File.Open(sSource, FileMode.Open))
Writer.Write(HtmlEditor1.Text)
Writer.Close()
Dim NewTh As New Threading.Thread(AddressOf DoIT)
NewTh.SetApartmentState(Threading.ApartmentState.STA)
NewTh.Start()

Duplicate Remove Ms. Excel Macro

Sub KillDupes()

Dim rConstRange As Range, rFormRange As Range
Dim rAllRange As Range, rCell As Range
Dim iCount As Long
Dim strAdd As String

On Error Resume Next
Set rAllRange = Selection

If WorksheetFunction.CountA(rAllRange) < 2 Then
MsgBox "You selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If

Set rConstRange = rAllRange.SpecialCells(xlCellTypeConstants)
Set rFormRange = rAllRange.SpecialCells(xlCellTypeFormulas)

If Not rConstRange Is Nothing And Not rFormRange Is Nothing Then
Set rAllRange = Union(rConstRange, rFormRange)

ElseIf Not rConstRange Is Nothing Then
Set rAllRange = rConstRange

ElseIf Not rFormRange Is Nothing Then
Set rAllRange = rFormRange

Else
MsgBox "You selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If



Application.Calculation = xlCalculationManual



For Each rCell In rAllRange
strAdd = rCell.Address
strAdd = rAllRange.Find(What:=rCell, After:=rCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Address

If strAdd <> rCell.Address Then
rCell.Clear
End If
Next rCell

Application.Calculation = xlCalculationAutomatic

On Error GoTo 0

End Sub

Assembly error

 Error 4 The type 'ASP.aspx_controls_maintop_ascx' exists in both 'c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\online_store\7e5e2cc2\6d0e9d9d\App_Web_iv00njwa.dll' and 'c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\online_store\7e5e2cc2\6d0e9d9d\App_Web_ndsosjuw.dll' D:\Projects\Online_Store\aspx\Confirm.aspx

If you get error like this that 2 assemblies exists in same path then go to the specified path 

c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\

and delete ur project.

Because this is all temporary files and restart ur visual studio.

How to give image url at runtime in any databound controls of asp.net

Take a image tag in ur databound control like gridview,datalist.
If you take asp image tag then set path like bellow
ImageURL = ImageTest(DataBinder.Eval(Container.DataItem, "sLogo") in asp tags.
Here ImageTest is a function in which you can give your imageurl. For example view the following :-
protected object ImageTest(object s)
{
object path = "~/aspx/productimages/" + Session["Member"] + "\\logo\\" + s;
return path;
}
set ur image url in path object variable.
Thank you

BIND MULTIPLE VALUES IN GRIDVIEW WITH ITEAM TEMPLATE

Text = '<%# DataBinder.Eval(Container.DataItem, "sResumeTitle") + " [" + DataBinder.Eval(Container.DataItem, "sCareerLevel") + "]" %>'
Just use DataBinder.Eval(Container.DataItem, "sResumeTitle") on muliple and join it with + sign. You can also put string on that.

<%# DataBinder.Eval(Container, "DataItem.ProfileID, DataItem.ManId", "temppage.aspx?profileid={0}&manid={1}") %>'

Difference Resume, C.v. and Biodata

How to make treeview expanded even after user gets redirected to another page.....

protected void Page_Load(object sender, EventArgs e)
{
if (Session["tn"] != null || Convert.ToString(Session["tn"]) != "")
{
TreeNode tn = (TreeNode)Session["tn"];
ExpandAll(TreeView1);
}
}

protected void TreeView1_TreeNodeExpanded(object sender, TreeNodeEventArgs e)
{
TreeNode tn = e.Node;
Session["tn"] = tn;
}

public void ExpandAll(TreeView tvwExpand)
{
foreach (TreeNode nodExpand in tvwExpand.Nodes)
{
if (nodExpand.Text == ((TreeNode)Session["tn"]).Text)
nodExpand.Expanded = true;
}
}