Wednesday, July 20, 2011

Sql query to find table, column names where columns are having unique index

Select distinct Tbl.name, col.name From
sys.tables Tbl Join sys.columns col on col.[object_id] = Tbl .[object_id]Join sys.index_columns idx_cols on idx_cols.[column_id] = col.[column_id] and idx_cols.[object_id] = col.[object_id]
Join sys.indexes idx on idx_cols.[index_id] = idx.[index_id] and idx.[object_id] = col.[object_id]where col.name = 'INT_EMP_ID'and idx.is_unique = 1


This query finds table names,column names where columns are having primary key or unique key or its unique index is set to 1.
By default this query works on all tables in database thus time efficient.
User can alter this query for selectiong scope to a single table or group of tables or complete database depending upon his necessity.

Query in sql to find the names of table having some particular column with some particular data and also find the number of appearances of data

DECLARE @table_name VARCHAR(1000) -- table name Declare @COUNT VARCHAR(8000) -- count of the occurence of emp_id
DECLARE db_cursor CURSOR FOR select table_name from INFORMATION_SCHEMA.COLUMNSwhere COLUMN_NAMe like '%INT_EMP_ID%'
OPEN
db_cursor FETCH NEXT FROM db_cursor INTO @table_nameWHILE @@FETCH_STATUS = 0 BEGIN
SET @COUNT = 'select count(*) AS ' +@table_name+ ' from ' + @table_name + ' where' + ' INT_EMP_ID = 222201787 'EXEC(@COUNT)
FETCH NEXT FROM db_cursor INTO @table_name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Sunday, May 22, 2011

How to create and use web user controls in asp.net(c#)?

Sometimes our requirement is to use same asp controls in a number of web pages. To avoid lot of effort , we can create user controls that can be automatically replicated in different web pages.

There are some applications which require changes in individual asp controls of user control. For example - One of my application most webpages require requiredfield validation on two textboxes but some webpages require requiredfield validation on one textbox only and everything else is the same. So now there is a question " How to do this by making only one user control?"This can be easily done by creating property of user control in its .cs file,by using that property we can enable or disable some or all controls depending upon the requirement of webpage.

1. 1.Add web user control to your project(WebUserControl1.ascx)

2. Design your user control using toolbox.

3. Now you can drag WebUserControl1.ascx (from solution explorer) to your aspx page that will create the same user control on your webpage.

We have successfully created the user control and even embedded it on our web page but still to use it ,we need to define its properties such as enable,disable, load data(depending upon our need) in WebUserControl1.ascx.

In the following example it is shown that how to create different properties of user control in WebUserControl1.ascx.cs and how to use them in WebForm1.aspx.cs.

WebUserControl1.ascx

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="WebUserControl1.ascx.cs"Inherits="WebApplication1.controls.WebUserControl1" %>

<asp:Label ID="Label1" runat="server" Text="Label">asp:Label>

<asp:TextBox ID="TextBox1" runat="server">asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"

ControlToValidate="TextBox1"ErrorMessage="RequiredFieldValidator">asp:RequiredFieldValidator>

<br />

<asp:Label ID="Label2" runat="server" Text="Label">asp:Label>

<asp:TextBox ID="TextBox2" runat="server">asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"

ControlToValidate="TextBox2"ErrorMessage="RequiredFieldValidator">asp:RequiredFieldValidator>

<br />

WebUserControl1.ascx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace WebApplication1.controls

{

public partial class WebUserControl1 : System.Web.UI.UserControl

{

bool regular;

protected void Page_Load(object sender, EventArgs e)

{

}

public bool requiredexp

{

get

{

return regular;

}

set

{

regular = value;

RequiredFieldValidator2.Enabled = value;

}

}

public string text1

{

get

{

return TextBox1.Text.ToString();

}

set

{

TextBox1.Text = value;

}

}

}

}

WebForm1.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs"Inherits="WebApplication1.WebForm1" %>

<%@ Register src="controls/WebUserControl1.ascx" tagname="WebUserControl1" tagprefix="uc1"%>

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>title>

head>

<body>

<form id="form1" runat="server">

<div>

<uc1:WebUserControl1 ID="WebUserControl11" runat="server" />

<br />

<asp:TextBox ID="TextBox1" runat="server" ReadOnly="True">asp:TextBox>

<br />

<asp:TextBox ID="TextBox2" runat="server" ReadOnly="True">asp:TextBox>

<br />

<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />

div>

form>

body>

html>

WebForm1.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace WebApplication1

{

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

{

protected void Page_Load(object sender, EventArgs e)

{

WebUserControl11.requiredexp = false;

}

protected void Button1_Click(object sender, EventArgs e)

{

TextBox1.Text = WebUserControl11.text1;

}

}

}

Thursday, May 19, 2011

How to install silverlight 4?

Silverlight 4 is the latest version of silverlight and for its proper functioning , you need to install its complete package with toolkits. Toolkits provides dll for various functionalities.I recommend you to install the following tools before you start working on silverlight. WCF,RIA services provide easy access to database.Please note that as silverlight applications work on client side so it is necessary to use wcf services to connect it with database.

silverlight4tools.exe

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b3deb194-ca86-4fb6-a716-b67c2604a139&displaylang=en

siverlight_developer.exe

http://go.microsoft.com/fwlink/?LinkID=188039

silverlight4_toolkit.exe

http://silverlight.codeplex.com/

wcf ria services toolkit

http://go.microsoft.com/fwlink/?LinkID=205088

riaservices.msi

http://go.microsoft.com/fwlink/?LinkId=205085

Thursday, May 12, 2011

Silverlight 4: Experience the new era of web designing.

Silverlight is a tool, which supports Rich Internet Applications(applications similar to windows applicactions require internet to execute), media applications(video, webcam, microphone applications) and mobile applications.Browsers require silverlight plugin to create runtime environment for silverlight applications, which makes silverlight applications different from javascript applications(browsers do not require plugins).

Presently, silverlight has released its four versions. Silverlight 4.0 is the latest version used by developers to create silverlight applications.