Finding Duplicates rows with SQL Server

This article shows how to find duplicated rows in a database table. This is a very common beginner question. Here is a useful sql query to help identify duplicate values in a column and return a count of the number of times that value appears.Following query demonstrates usage of GROUP BY, HAVING in one query and returns the results with duplicate column .  

SELECT ColumeName, COUNT(ColumnName) TotalCount

FROM TableName

GROUP BY ColumeName

HAVING COUNT(ColumnName) > 1

 In my previous article Deleting Duplicate data from MySQL database in PHP

Read More

How to Add Auto Number Column in Asp.net GridView

While working in ASP.NET, you often come across a need to display serial number or Auto-number in a Gridview control. This can be accomplished by adding the Container.DataItemIndex  in the html markup of the Gridview control.

<asp:TemplateField>
     <ItemTemplate>
 <%# Container.DataItemIndex + 1 %>
     </ItemTemplate>
 </asp:TemplateField>

Read More

SQL server Connecting from Another Computer

Configure SQL Server to listen on a specific port

1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click on the server instance you want to configure.
2. In the right pane, double-click TCP/IP.
3. In the TCP/IP Properties dialog box, click the IP Addresses tab.
4. In the TCP Port box of the IPAll section, type an available port number. For this tutorial, we will use 49172.
5. Click OK to close the dialog box, and click OK to the warning that the service must be restarted.
6. In the left pane, click SQL Server Services.
7. In the right pane, right-click the instance of SQL Server, and then click Restart. When the Database Engine restarts, it will listen on port 49172.

To open a port in the Windows firewall for TCP access

Step 1

1. On the Start menu, click Run, type WF.msc, and then click OK.
2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
3. In the Rule Type dialog box, select Port, and then click Next.
4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine. Type 1433 for the default instance. Type 49172 if you are configuring a named instance and configured a fixed port in the previous task. Click Next.
5. In the Action dialog box, select Allow the connection, and then click Next.
6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Setp 2:

1. On the Start menu, click Control Panel.
2. In Control Panel, click Network and Internet Connections, and then open Windows Firewall.
3. In Windows Firewall, click the Exceptions tab, and then click Add Port.
4. In the Add a Port dialog box, in the Name box, type SQL Server "instanceName".
5. In the Port number box, type the port number of the Database Engine instance. Type 1433 for the default instance. Type 49172 if you are configuring a named instance and configured a fixed port in the previous task. Verify that TCP is selected, and then click OK.

Read More

Set date format in jquery ui datepicker

The jQuery UI Datepicker is a highly configurable plugin that adds datepicker functionality to your pages.  You can customize the date format.

1. d – day of month (single digit where applicable)
2. dd – day of month (two digits)
3.  o – day of the year (no leading zeros)
4. oo – day of the year (three digit)
5. m – month of year (single digit where applicable)
6. mm – month of year (two digits)
7.  y – year (two digits)
8.  yy – year (four digits)
9. D – short day name
10.  DD – full day name
11.  M – short month name
12.  MM – long month name
13.  '...' – any literal text string
14.  @ - UNIX timestamp (milliseconds since 01/01/1970)
15. ! – Windows ticks (100ns since 01/01/0001)

 (function(){
    var pickerOpts = {
       dateFormat:"d MM yy"
    };
    $("#date").datepicker(pickerOpts);
 });

predefined date formats for datepicker

The complete set of predefined date formats

Option value	Date format
$.datepicker.ATOM	"yy-mm-dd"
$.datepicker.COOKIE	"D, dd M y"
$.datepicker.ISO_8601	"yy-mm-dd"
$.datepicker.RFC_822	"D, d M y"
$.datepicker.RFC_850	"DD, dd-M-y"
$.datepicker.RFC_1036	"D, d M y"
$.datepicker.RFC_1123	"D, d M yy"
$.datepicker.RFC_2822	"D, d M yy"
$.datepicker.RSS	"D, d M y"
$.datepicker.TIMESTAMP	@ (UNIX timestamp)
$.datepicker.W3C	"yy-mm-dd"

Read More

Coding Standard For C#.net

Namespace standard:

We must be use namespace: Com. [companyname]. [projectname].[modulename]

For example: Com.Codespacies.PIS.Recordmgt.Form

We must separate cs file like this:

Form: cs form

Dao: database integration and business logic

Rpt: crystal report file

Model: model class

Dataset: dataset file

Documentation Comments:

This pattern for class comments:

/// <summary>

/// This class...

/// created by:

/// created Date:

/// modified by:

/// modified Date:

/// Description:

/// </summary>

This pattern for method comments:

/// <summary>

/// this method...

/// Description:

/// ref parameter:

/// </summary>

This pattern for change comments:

/// <summary>

/// why change...

/// Description:

/// Date:

/// requested by:

/// </summary>

Capitalization Styles:

Pascal Case 

The first letter in the identifier and the first letter of each subsequent concatenated word are capitalized.  You can use Pascal case for identifiers of three or more characters. 

For example:  BackColor 

Camel Case 

The first letter of an identifier is lowercase and the first letter of each subsequent concatenated word

is capitalized. 

For example:  backColor 

Uppercase 

All letters in the identifier are capitalized.  Use this convention only for identifiers that consist of two

or fewer letters.

 For example:  System.IO 

            System.Web.IO 

You might also have to capitalize identifiers to maintain compatibility with existing, unmanaged symbol schemes, where all uppercase characters are often used for enumerations and constant values.  In general, these symbols should not be visible outside of the assembly that uses them. 

Class Naming Guidelines

·         Class names must be nouns or noun phrases.

·         UsePascal Casing

·         Do not use any class prefix

Interface Naming Guidelines

·         Name interfaces with nouns or noun phrases or adjectives describing behavior. (Example IComponent or IEnumberable)

·         Use Pascal Casing

·         Do not use any Interface prefix

Enum Naming Guidelines

·         Use Pascal Casing for enum value names and enum type names

·         Don’t prefix (or suffix) a enum type or enum values

·         Use singular names for enums

·         Use plural name for bit fields.

ReadOnly and Const Field Names

·         Name static fields with nouns, noun phrases or abbreviations for nouns

·         Use Pascal Casing

Parameter/non const field Names

·         Do use descriptive names, which should be enough to determine the variable meaning and it’s type. But prefer a name that’s based on the parameter’s meaning.

·         Use Camel Casing

Variable Names

·         Counting variables are preferably called i, j, k, l, m, n when used in 'trivial' counting loops.

·         Use Camel Casing

Method Names

·         Name methods with verbs or verb phrases.

·         Use Pascal Casing

Property Names

·         Name properties using nouns or noun phrases

·         Use Pascal Casing

·         Consider naming a property with the same name as it’s type

Event Names

·         Name event handlers with the EventHandler suffix.

·         Use two parameters named sender and e

·         Use Pascal Casing

·         Name event argument classes with the EventArgs suffix.

·         Name event names that have a concept of pre and post using the present and past tense.

·         Consider naming events using a verb.

Table of capitalization summary

Identifier	Case	Example
Class	Pascal	AppDomain
Struct	Pascal	EmpInfo
Enum type	Pascal	ErrorLevel
Enum values	Pascal	FatalError
Event	Pascal	ValueChange
Exception Class	Pascal	WebException Note: Always ends with the suffix Exception
Read-only Static field	Pascal	RedValue
Interface	Pascal	IDisposable Note: Always begins with the prefix I
Method	Pascal	ToString
Namespace	Pascal	System.Drawing
Parameter	Camel	typeName
Property	Pascal	BackColor
Protected instance field	Camel	redValue Note: Rarely used. A  property is preferable to using a protected instance field
Public instance field	Pascal	RedValue Note: Rarely used. A property is preferable is using a instance fied.
Private instance field	Camel	redValue

Table of Standard Control Prefixes

Control	Prefix	Example
Label	lbl	lblName
LinkLabel	llbl	llblName
Button	btn	btnName
Textbox	txt	txtName
MainMenu	mnu	mnuName
CheckBox	chk	chkName
RadioButton	rdo	rdoName
GroupBox	grp	grpName
PictureBox	pic	picName
Grid	grd	grdName
ListBox	lst	lstName
ComboBox	cmb	cmbName
ListView	lstv	lstvName
TreeView	tre	treName
TabControl	tab	tabName
DateTimePicker	dtp	dtpName
MonthCalendar	mon	monName
ScrollBar	sbr	sbrName
Timer	tmr	tmrName
Splitter	spl	splName
DomainUpDown	dud	dudName
NumericUpDown	nud	nudName
TrackBar	trk	trkName
ProgressBar	pro	proName
RichTextBox	rtxt	rtxtName
ImageList	img	imgName
HelpProvider	hlp	hlpName
ToolTip	tip	tipName
ContextMenu	cmnu	cmnuName
ToolBar	tbr	tbrName
Form	frm	frmName
StatusBar	bar	barName
NotifyIcon	nico	nicoName
OpenFileDialog	ofd	ofdName
SaveFileDialog	sfd	sfdName
FontDialog	fd	fdName
ColorDialog	cd	cdName
PrintDialog	pd	pdName
PrintPreviewDialog	ppd	ppdName
PrintPreviewControl	ppc	ppcName
ErrorProvider	err	errName
PrintDocument	pdoc	pdocName
PageSetupDialog	psd	psdName
CrystalReportViewer	crv	crvName
PrintDialog	pd	pdName
FileSystemWatcher	fsw	fswName
EventLog	log	logName
DirectoryEntry	dire	direName
DirectorySearcher	dirs	dirsName
MessageQueue	msq	msqName
PerformanceCounter	pco	pcoName
Process	pro	proName
ServiceController	ser	serName
ReportDocument	rpt	rptName
DataSet	ds	dsName
Datatable	dt	dtName
OleDbDataAdapter	olea	oleaName
OleDbConnection	olec	olecName
OleDbCommand	oled	oledName
SqlDbDataAdapter	sqla	sqlaName
SqlDbConnection	sqlcon	sqlconName
SqlDbCommand	sqlcmd	sqlcmdName
DataView	dvw	dvwName

Table of Standard Data Type Prefixes

Data Type	Prefix	Example
int	i	iValueName
uint	ui	uiValueName
string	str	strValueName
double	d	dValueName
long	l	lValueName
ulong	ul	ulValueName
bool	b	bValueName
char	c	cValueName
decimal	dec	decValueName
float	f	fValueName
byte	y	yValueName
sbyte	sy	syValueName
short	s	sValueName
ushort	us	usValueName
object	obj	objValueName
Exception	ex

 

Table of Standard Control Prefixes

Control	Prefix	Example
DataGrid	dtg	dtgName
Column	col	colName

Design

Every form has message level which gives any message

Read More