Pages

Dec 30, 2008

SharePoint

SharePoint:
What Is SharePoint?
SharePoint Facts
Why Should You Use SharePoint?
Microsoft Office SharePoint Server 2007
Windows SharePoint Services 3.0
Which SharePoint Technology Is Right For You?
MOSS 2007
Top 10 Benefits of Windows SharePoint Services

What is SharePoint?
SharePoint is an enterprise information portal, from Microsoft, that can be configured to run Intranet, Extranet and Internet sites. Microsoft Office SharePoint Server 2007 allows people, teams and expertise to connect and collaborate. A SharePoint enterprise portal is composed of both SharePoint Portal and Windows SharePoint Services, with SharePoint being built upon WSS. WSS is typically used by small teams, projects and companies. SharePoint Server is designed for individuals, teams and projects within a medium to large company wide enterprise portal.


Some SharePoint facts
• SharePoint is the fastest-growing product in the history of Microsoft
• Over 75 million licenses of SharePoint have been sold worldwide
• SharePoint is listed, by Forrester, as the number 1 portal product
• SharePoint is positioned as a leader within the Gartner Magic Quadrant for Horizontal Portals products
• Over 400 case studies have been published on SharePoint

Why should you use SharePoint?
SharePoint solves four main problems:

• As companies grow so does the amount of their files. It soon becomes difficult to keep track of the multiplying documents and their locations. SharePoint overcomes this by allowing you to store and locate your files in a central site. Files can also be located through company wide searches of your SharePoint enterprise portal.

• Sharing work files through email is a cumbersome process. SharePoint eliminates this by allowing files to be stored in one location, allowing easy access to all team members.

• Today’s work occurs over multiple locations, whether it is in different countries, office locations, separate departments or at your home office. SharePoint enables teams and individuals to connect and collaborate together regardless of where they are located.

• It’s difficult and time consuming to create and maintain sites. SharePoint allows anyone to create sites for use within their company’s Intranet, as they are needed, whether they are departmental sites, document libraries, meetings sites, survey sites, or discussion boards.

Microsoft Office SharePoint Server 2007:

Microsoft Office SharePoint Server 2007 is a collaborative enterprise portal that is built upon WSS 3.0. MOSS 2007 allows people, teams and expertise to connect and collaborate. Unlike WSS, SharePoint Server is not free and requires an additional license. MOSS 2007 comes in two versions – Standard and Enterprise.
The main components of SharePoint 2007 are

collaboration,
portals,
enterprise search,
enterprise content management,
business process and forms,
and business intelligence.

Previous versions of SharePoint Server included SharePoint Portal Server 2003 and before that SharePoint Portal Server 2001. To preview SharePoint’s new features visit the Microsoft Office SharePoint Server 2007 demo.

Windows SharePoint Services 3.0 :

Windows SharePoint Services 3.0 is the platform on which all SharePoint Products and Technologies are built. WSS 3.0 is for is suitable for small teams, projects and organizations.
WSS’s project collaboration, document workspace, meeting sub-site, and discussion board features allow individuals and small teams to collaborate and share information online. Past versions of Windows SharePoint Services included Windows SharePoint Services 2.0 and SharePoint Team Services. New features in WSS 3.0 include integrated workflows, RSS feeds, blogs, wikis and ASP-style Web parts. To preview more of WSS 3.0’s new features visit the Microsoft
Windows SharePoint Services 3.0 demo.
Not sure which SharePoint technology is right for you?
Visit Microsoft online to find out which SharePoint technology meets your company’s requirements.

Microsoft Office SharePoint Server 2007 Top 10 Benefits:

1. Provide a simple, familiar, and consistent user experience.

Office SharePoint Server 2007 is tightly integrated with familiar client desktop applications, e-mail, and Web browsers to provide a consistent user experience that simplifies how people interact with content, processes, and business data. This tight integration, coupled with robust out-of-the-box functionality, helps you employ services themselves and facilitates product adoption.

2. Boost employee productivity by simplifying everyday business activities.

Take advantage of out-of-the-box workflows for initiating, tracking, and reporting common business activities such as document review and approval, issue tracking, and signature collection. You can complete these activities without any coding. Tight integration with familiar client applications, e-mail, and Web browsers provide you with a simple, consistent experience. Modifying and extending these out-of-the-box workflow processes is made easy through tools like Microsoft Office SharePoint Designer 2007 (the next release of Microsoft Office FrontPage).

3. Help meet regulatory requirements through comprehensive control over content.

By specifying security settings, storage policies, auditing policies, and expiration actions for business records in accordance with compliance regulations, you can help ensure your sensitive business information can be controlled and managed effectively. And you can reduce litigation risk for your organization. Tight integration of Office SharePoint Server 2007 with familiar desktop applications means that policy settings are rendered onto client applications in the Microsoft Office system, making it simpler for employees to be aware of and comply with regulatory requirements.

4. Effectively manage and repurpose content to gain increased business value.

Business users and content authors can create and submit content for approval and scheduled deployment to intranet or Internet sites. Managing multilingual content is simplified through new document library templates that are specifically designed to maintain a relationship between the original version and different translations of a document.

5. Simplify organization-wide access to both structured and unstructured information across disparate systems.

Give your users access to business data found in common line-of-business systems like SAP and Siebel through Office SharePoint Server 2007. Users can also create personalized views and interactions with business systems through a browser by dragging configurable back-end connections. Enterprise-wide Managed Document Repositories help your organizations store and organize business documents in one central location.

6. Connect people with information and expertise.

Enterprise Search in Office SharePoint Server 2007 incorporates business data along with information about documents, people, and Web pages to produce comprehensive, relevant results. Features like duplicate collapsing, spelling correction, and alerts improve the relevance of the results, so you can easily find what you need.

7. Accelerate shared business processes across organizational boundaries.

Without coding any custom applications, you can use smart, electronic forms–driven solutions to collect critical business information from customers, partners, and suppliers through a Web browser. Built-in data validation rules help you gather accurate and consistent data that can be directly integrated into back-end systems to avoid redundancy and errors that result from manual data re-entry.

8. Share business data without divulging sensitive information.

Give your employees access to real-time, interactive Microsoft Office Excel spreadsheets from a Web browser through Excel Services running on Office SharePoint Server 2007. Use these spreadsheets to maintain and efficiently share one central and up-to-date version while helping to protect any proprietary information embedded in the documents (such as financial models).

9. Enable people to make better-informed decisions by presenting business-critical information in one central location.

Office SharePoint Server 2007 makes it easy to create live, interactive business intelligence (BI) portals that assemble and display business-critical information from disparate sources, using integrated BI capabilities such as dashboards, Web Parts, scorecards, key performance indicators (KPIs), and business data connectivity technologies. Centralized Report Center sites give users a single place for locating the latest reports, spreadsheets, or KPIs.

10. Provide a single, integrated platform to manage intranet, extranet, and Internet applications across the enterprise.

Office SharePoint Server 2007 is built on an open, scalable architecture, with support for Web services and interoperability standards including XML and Simple Object Access Protocol (SOAP). The server has rich, open application programming interfaces (APIs) and event handlers for lists and documents. These features provide integration with existing systems and the flexibility to incorporate new non-Microsoft IT investments.

Top 10 Benefits of Windows SharePoint Services:

1. Improve team productivity with easy-to-use collaborative tools
Connect people with the information and resources they need. Users can create team workspaces, coordinate calendars, organize documents, and receive important notifications and updates through communication features including announcements and alerts, as well as the new templates for creating blogs and wikis. While mobile, users can take advantage of convenient offline synchronization capabilities.


2. Easily manage documents and help ensure integrity of content
With enhanced document management capabilities including the option to activate required document checkout before editing, the ability to view revisions to documents and restore to previous versions, and the control to set document- and item-level security, Windows SharePoint Services can help ensure the integrity of documents stored on team sites.


3. Get users up to speed quickly
User interface improvements in Windows SharePoint Services 3.0 include enhanced views and menus that simplify navigation within and among SharePoint sites. Integration with familiar productivity tools, including programs in the Microsoft Office system, makes it easy for users to get up to speed quickly. For example, users can create workspaces, post and edit documents, and view and update calendars on SharePoint sites, all while working within Microsoft Office system files and programs.

4. Deploy solutions tailored to your business processes
While standard workspaces in Windows SharePoint Services are easy to implement, organizations seeking a more customized deployment can get started quickly with application templates for addressing specific business processes or sets of tasks.

5. Build a collaboration environment quickly and easily
Easy to manage and easy to scale, Windows SharePoint Services enables IT departments to deploy a collaborative environment with minimal administrative time and effort, from simple, single-server configurations to more robust enterprise configurations. Because deployment settings can be flexibly changed, less pre-planning time is required and companies can get started even faster.

6. Reduce the complexity of securing business information
Windows SharePoint Services provides IT with advanced administrative controls for increasing the security of information resources, while decreasing cost and complexity associated with site provisioning, site management, and support. Take advantage of better controls for site life-cycle management, site memberships and permissions, and storage limits.

7. Provide sophisticated controls for securing company resources
IT departments can now set permissions as deep down as the document or item level, and site managers, teams, and other work groups can initiate self-service collaborative workspaces and tasks within these preset parameters. New features enable IT to set top-down policies for better content recovery and users, groups, and team workspace site administration.

8. Take file sharing to a new level with robust storage capabilities
Windows SharePoint Services supplies workspaces with document storage and retrieval features, including check-in/check-out functionality, version history, custom metadata, and customizable views. New features in Windows SharePoint Services include enhanced recycle bin functionality for easier recovery of content and improved backup and restoration.

9. Easily scale your collaboration solution to meet business needs
Quickly and easily manage and configure Windows SharePoint Services using a Web browser or command-line utilities. Manage server farms, servers, and sites using the Microsoft .NET Framework, which enables a variety of custom and third-party administration solution offerings.

10. Provide a cost-effective foundation for building Web-based applications
Windows SharePoint Services exposes a common framework for document management and collaboration from which flexible and scalable Web applications and Internet sites, specific to the needs of the organization, can be built. Integration with Microsoft Office SharePoint Server 2007 expands these capabilities further to offer enterprise-wide functionality for records management, search, workflows, portals, personalized sites, and more.

Ref : http://www.sharepointhq.com/

How to use CASE statements in SQL Query : + SQL SERVER 2005

First lets create a sample table named testable

CREATE TABLE [dbo]. [testable]
(

[MaterialID] [int] NOT NULL,
[type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[value] [decimal](18, 0) NULL

) ON [PRIMARY]

Now lets insert some values like

INSERT INTO testtable
SELECT 1,'cem','RAO',2
UNION ALL
SELECT 5,'wtr','Water',4
UNION ALL
SELECT 6,'cem','rao',5


Now write a query to that selects the column MaterialID, and two assigned columns named CementDescription and TestDescription where the CementDescription column should be filled with ‘CEMEMT’ for each row in the table where the type is ‘cem’ and the WaterDescription column should be filled with ‘Water’ for each row of the table where the type is ‘wtr’.

SELECT MaterialID,
CASE WHEN type = 'cem' THEN 'CEMENT' ELSE NULL END as CementDescription ,
CASE WHEN type = 'wtr' THEN 'WATER' ELSE NULL END as WaterDescription
FROM testtable


OUTPUT:

MaterialID CementDescription WaterDescription

1 CEMENT NULL
2 NULL WATER
3 CEMENT NULL


Not only this eg. there are lot to do with CASE in SQL.

Dec 23, 2008

Threads Implementation in C# + source code

Thread in C# :
Introduction to Threads In C# :
Threads: Threads are often called lightweight processes. However they are not process.es

A Thread is a small set of executable instructions, which can be used to isolate a task from a process.
Multiple threads are efficient way to obtain parallelism of hardware and give interactive user interaction to your applications.

C# Thread:.
. Net Framework has thread-associated classes in System.Threading namespace. The following steps demonstrate how to create a thread in C#.

Step 1. Create a System.Threading.Thread object.
Creating an object to System.Threading.Thread creates a managed thread in .Net environment. The Thread class has only one constructor, which takes a ThreadStart delegate as parameter. The ThreadStart delegate is wrap around the callback method, which will be called when we start the thread.


Step 2: Create the call back function
This method will be a starting point for our new thread. It may be an instance function of a class or a static function. Incase of instance function, we should create an object of the class, before we create the ThreadStart delegate. For static functions we can directly use the function name to instantiate the delegate. The callback function should have void as both return type and parameter. Because the ThreadStart delegate function is declared like this. (For more information on delegate see MSDN for “Delegates”).


Step 3: Starting the Thread.
We can start the newly created thread using the Thread’s Start method. This is an asynchronous method, which requests the operating system to start the current thread.
For Example:
// This is the Call back function for thread.

Public static void MyCallbackFunction()
{
while (true)
{
System.Console.WriteLine(“ Hey!, My Thread Function Running”); ………
}
}

public static void Main(String []args)
{
// Create an object for Thread
Thread MyThread = new Thread(new ThreadStart (MyCallbackFunction));
MyThread.Start() ……
}

Killing a Thread:
We can kill a thread by calling the Abort method of the thread. Calling the Abort method causes the current thread to exit by throwing the ThreadAbortException.

MyThread.Abort();

Suspend and Resuming Thread:

We can suspend the execution of a thread and once again start its execution from another thread using the Thread object’s Suspend and Resume methods.
MyThread.Suspend();

// causes suspend the Thread Execution.
MyThread.Resume() ;
// causes the suspended Thread to resume its execution.


Thread State:
A Thread can be in one the following state.
Unstarted - Thread is Created within the common language run time but not Started still.
Running - After a Thread calls Start method
WaitSleepJoin - After a Thread calls its wait or Sleep or Join method.
Suspended - Thread Responds to a Suspend method call.
Stopped - The Thread is Stopped, either normally or Aborted.

We can check the current state of a thread using the Thread’s ThreadState property.


Thread Priorty:
The Thread class’s ThreadPriority property is used to set the priority of the Thread.

A Thread may have one of the following values as its Priority:

Lowest

BelowNormal

Normal

AboveNormal

Highest.

The default property of a thread is Normal.

REF: Code project

How to select an item in a DropDownList by Value


1. //How to select an item in a DropDownList by Value

ListItem li = yourDropDownlist.Items.FindByValue(”yourValue”);

if (li != null)

yourDropDownlist.SelectedIndex = yourDropDownlist.Items.IndexOf(li);

2. //How to check if value exists in DropDownList

public static bool IsValueInDropdownList(DropDownList controlName,string strValue)

{

if (controlName.Items.FindByValue(strValue) != null)

return true;

else
return false;

}

How to Remove item from DropDownList by Value

// 1st method, to Remove item from DropDownList by Value
ListItem li = dropdownlist.Items.FindByValue(strValue);
if(li != null)
dropdownlist.Items.Remove(li);
// 2nd method, combined form
dropdownlist.Items.RemoveAt(
dropdownlist.Items.IndexOf(dropdownlist.Items.FindByValue(strValue))
);

Dec 22, 2008

CLR Stored Procedure to decrypt the encrypted value

Here is a CLR Stored Proc to decrypt the encrypted value and return it through output variable.

After launching Visual Studio 2005 choose File -> New Project.
In the dialog box under Project Type choose Visual C# -> Database and then choose SQL Server Project on the right side.
I named my project CLR_Decrypt . This creates a solution and a project both named CLR_Decrypt . Visual Studio will also ask you to create a database reference or use an existing one.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Security.Cryptography;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CLR_Decrypt(SqlString employeeID, out SqlBinary decryptedSignatureBytes)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";
string signatureString = string.Empty;
string signatureString1 = string.Empty;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"SELECT EncryptedSignatureImage
FROM
[dbo].[LDI_EmployeeDigitalSignature]
WHERE
EncryptedEmployeeID = '" + employeeID.ToString() + "'";
conn.Open();

SqlDataReader rdr = cmd.ExecuteReader();
// This to call the Decrypt method to Decrypt the already encrypted text.
using (rdr)
{
while( rdr.Read() )
{
signatureString = rdr.GetString(0).ToString();
signatureString1 = DecryptText(signatureString);
}
}

// To convert string to SQL binary
decryptedSignatureBytes = Convert.FromBase64String(signatureString1);
rdr.Close();
conn.Close();
}

Dec 18, 2008

how to Export Grid view To Excel + Asp.Net 2.0 + C#

The below code Exports the Grid view to Excel in C#.

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;

///
/// Exports Grid To Excel
///

private void ExportToExcel(GridView gv)
{
StringWriter stw = null;
HtmlTextWriter htextw = null;
string fileName = string.Empty;

fileName = "Test";
try
{
HtmlForm form = new HtmlForm();
string attachment = "attachment; filename=" + fileName + ".xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
Response.Buffer = true;
stw = new StringWriter();
htextw = new HtmlTextWriter(stw);
form.Controls.Add(gv);
this.Controls.Add(form);
form.RenderControl(htextw);
Response.Write(stw.ToString());
gv.Columns[10].Visible = true;
Response.End();
}
finally
{
stw = null;
htextw = null;
}
}


This code will open a open or Save dialog to open or save the .xls files created.

How to write a Property for ListBox to Generate ID List for Selected Items + C#

This is Property for ListBox to Generate ID List for Multiple Selected Items.
Use this namespace because here we use regex in the property.
using System.Text.RegularExpressions;

///
/// Gets Or Sets the IDsList.
///

private string IDsList
{
get
{
string IDs = string.Empty;
foreach (ListItem item in lbSample.Items)
{
if (item.Selected)
IDs = IDs + item.Value + ",";
}
IDs = System.Text.RegularExpressions.Regex.Replace(IDs, ",$", "");
return IDs;
}
set
{
foreach (int item in value)
{
if (lbSample.Items.FindByValue(item.ToString()) != null)
lbSample.Items.FindByValue(item.ToString()).Selected = true;
}
}
}

This will automatically return the list of IDs selected in the List box.

Oct 24, 2008

SQL SERVER 2005 SYNONYMS - Usage And Limitations


SYNONYMS:

Problem:

At work we face situations like ,

1. where tables created in the production database are required to move to another DB as a minimal time period, Moving the table with data's is a tedious job

2. And also we use various SPs, TVF Functions in some DB say DB1. When we are working in some other DB2 and we have situation to use the same SPs and Functions in DB1 for a test process, here we need to run the whole scripts in DB2 and we have to drop it if it is no more useful which comsume time

Solution:

The feature is SYNONYMs in SQL Server 2005. SYNONYMs is new to SQL Server 2005. It is a way to give an alias to an already existing or potential new object(May be a Table,SP,Functions,Views etc). It is just a pointer or reference, so it is not considered to be an object.

Required Permissions:

In order to create a synonym, you need to have CREATE SYNONYM permissions. If you are a sysadmin or db_owner you will have these privileges or you can grant this permission to other users. Also, you create a synonym that crosses databases you will need permissions for the other database as well..

CREATING SYNONYMs:

A SYNONYM can be created within a DB or Between DBs and also Between DBs in Different Servers.

Example 1:

Here is an Example for Creating a Synonyms within a DB

Syntax:

CREATE SYNONYM [SynName] FOR [ObjectName]

USE AdventureWorks

GO

CREATE SYNONYM MySyn FOR Production.Location

GO
To check that this works you can issue a query like

SELECT * FROM MySyn

This returns the values of the table Production.Location . This any modifications to this Synonyms will reflect in Production.Location table.

Say for example

UPDATE MySyn

SET Name = 'MukundsIdeas'

WHERE LocationID = 1

Executing this query will affect the values in the
Production.Location table of AdventureWorks DB.

Example 2:

Here is an example to create the SYNONYM in one database that references an object in another database.


USE master

GO

CREATE SYNONYM dbo.MySyn FOR AdventureWorks.Production.Location

GO

This creates a Synonym that can be accessible form any DB inside the Same Server.

Note: Make note on what Schema your DB use. For eg : if ur DB uses schema dbo then u should mention it as

CREATE SYNONYM dbo.MySyn FOR AdventureWorks.dbo.Production.Location

Example 3:

USE master
GO
CREATE SYNONYM dbo.MySyn FOR [UrServerName].[DBName].[TableName or SPname or Functionname or viewname etc]
GO

To get the meta data for all synonyms use the following command

SELECT * FROM sysobjects WHERE xtype = 'SN' ORDER BY NAME

And to drop the synonym use the following command

USE AdventureWorks;

GO

DROP SYNONYM MySyn

GO

SYNONYM's can be very useful and can be created for

1. Tables
2. Views
3. Assembly Stored Procedures,

4. Table Valued Functions,

5. Aggregations
6. SQL Scalar Functions
7. SQL Stored Procedures
8. SQL Table Valued Functions
9. SQL Inline-Table-Valued Functions
10. Local and Global Temporary Tables
11. Replication-filter-procedures
12. Extended Stored Procedures

Benefits :

SYNONYMs provide a layer of abstraction over the referenced object
Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.


Provides flexibility for changing the location of objects without changing existing code.


SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.


SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.


Limitations:


SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.


Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.


Obviously consumes possible object names, as you can not create a table with the same name of a synonym


The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.


SYNONYM can not be referenced in a DDL statement

Oct 23, 2008

User Defined SPLIT Functions in SQL SERVER 2005

User Defined Function for Splitting the string value using the delimiter:
This is a function that requires the string value and the delimiter as input and returns a column of splitted values

CREATE FUNCTION [dbo].[Split]
(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([ID] BIGINT identity(1,1), [Value] nvarchar(4000))

BEGIN
DECLARE @NextString nvarchar(4000)
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @CommaCheck nvarchar(1)

--Initialize
SET @NextString = ''
SET @CommaCheck = right(@String,1)

--Check for trailing Delimiter, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
SET @String = @String + @Delimiter

--Get position of first Delimiter

SET @Pos = charindex(@Delimiter,@String) SET @NextPos = 1

--Loop while there is still a Delimiter in the String of levels
WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
INSERT INTO @ValueTable ( [Value]) Values (@NextString)
SET @String = substring(@String,@pos +1,len(@String))
SET @NextPos = @Pos
SET @pos = charindex(@Delimiter,@String)
END
RETURN
END

For eg Executing the Query
SELECT [Value] FROM [dbo].[split]('1,2,3',',') will result in

table
Value

1
2
3
This function is useful when we pass a string list to a SP.


Oct 15, 2008

Mathematical Calculations in SQL server



Calculating Mathematical Values in SQL Server :

Problem:

In our application we have the need to perform mathematical calculations.Right now we are doing so in our front end application. Unfortunately we are starting to experience performance problems with large data sets and differences in calculations due to developers using different logic. We are seeking some other options to perform the calculations.

Does SQL Server perform basic mathematical calculations?
Solution: Yes - SQL Server can perform basic addition, subtraction, multiplication and division. So if you need some of those basic building blocks those are available and we will walk through some examples in this tip.

In addition, SQL Server can calculate SUM, COUNT, AVG, etc. For these type of calculations, check out SQL Server T-SQL Aggregate Functions.

To address the multiple code issues, I would recommend researching stored procedures. This tip Getting started with SQL Server stored procedures is probably a good place to start.In this tip, let's focus on some basic building blocks to perform your calculations with the T-SQL language.

Here are the examples we will cover:
• Calculations on Values

• Calculations on Parameters

• Calculations on Columns

• Calculations in Computed Columns


Calculations on Values:
As a starting point, values themselves can have mathematical operations performed on them. So in the code below we are performing addition (+), subtraction (-), multiplication (*), division (/) and a combination of operations.

In these examples, both positive and negative whole numbers and decimals are used. The value calculated by the statement is below the code to serve as a point of reference. Review and copy the code into a SQL Server 2005 Management Studio window to get a feel for these calculations. Certainly this list is not comprehensive, so feel free to test some operations in your applications or from your experiences.


Calculations on Values:
-- Example 1 - Addition (+) :

SELECT 1 + 1

-- = 2

SELECT 1 + 2 + 3 + 4 + 99 + 704

-- = 813

SELECT 1.5 + 1.5 -- = 3.0

SELECT .25678 + .00096356

-- = 0.25774356

SELECT 1.75 + -2.25

-- = -0.50

Example 2 - Subtraction (-):

SELECT 1 - 1-- = 0

SELECT 918 - 704-- = 214

SELECT 3.2 - 1.9-- = 1.3

SELECT 1.9 - 3.2-- = -1.3

SELECT 9 - 3 - 3 -- = 3

SELECT .75 - .68-- = 0.07--

Example 3 - Multiplication (*):

SELECT 1 * 1-- = 1

SELECT 2 * -4-- = -8

SELECT 2 * 5 * 10-- = 100

SELECT 1.25 * 3-- = 3.75

SELECT .4 * .5-- = .20--

Example 4 - Division (/):

SELECT 1/2-- = 0

SELECT 1.0/2.0-- = 0.500000

SELECT 0/5-- = 0

SELECT 100/12-- = 8

SELECT 100.0/12.0-- = 8.333333

SELECT -75.0/4.5-- = -16.666666

SELECT .5/.1-- = 5.000000-- Example 5 - Combination

SELECT ((100 + 100) * .05)-- = 10.00

SELECT (10 - 5)/2-- = 2

SELECT (10.0 - 5.0)/2.0-- = 2.500000

SELECT ((100 + 100) - (50 + 50))-- = 100


Calculations on Parameters:
Since we have covered the bulk of the calculations in the section above let's just focus on a few different operations to show how parameters with specific data types play an important role. Since this set of examples are not comprehensive, feel free to copy the code below into a SQL Server 2005 Management Studio window and test the code with some of your own logic.
Calculations on Parameters
-- Variable declaration

DECLARE @i1 int

DECLARE @i2 int

DECLARE @i3 int

DECLARE @d1 decimal(10,2)

DECLARE @d2 decimal(10,2)

DECLARE @d3 decimal(10,2)

-- Initialize variables

SET @i1 = 100

SET @i2 = 75

SET @i3 = 50

SET @d1 = 1.5

SET @d2 = 5.5

SET @d3 = .575--

Example 1 - Addition (+)

SELECT @i1 + @i2 + @i3 + @d1 + @d2 + @d3 -- = 232.58

SELECT @d2 +

-@d3-- = 4.92

-- Example 2 - Subtraction (-):

SELECT @i2 - @i3

-- = 25

SELECT @d2 - @d3

-- = 4.92

-- Example 3 - Multiplication (*):

SELECT @i2 * @i3

-- = 3750

SELECT @d2 * @d3

-- = 3.1900

-- Example 4 - Division (/):

SELECT @i2 / @i3

-- = 1

SELECT @d2 / @d3

-- = 9.4827586206896

-- Example 5 - Combination:

SELECT ((@i1 + @i2) * @d2)

-- = 962.50

SELECT ((@i1 + @i2) - (@d1 + @d2))

-- = 168.00


Calculations on Columns:

Another option is to calculate the values based on a SELECT statement as the example below shows. If the data is in 1 or more columns, this approach eliminates the need to calculate the values based on additional parameters or logic.
Calculations on Columns
-- Sample Table

CREATE TABLE dbo.CalculationExample

(

ProductID int NOT NULL,

Cost decimal(10,2) NOT NULL

)

GO
-- Populate Table

INSERT INTO dbo.CalculationExample (ProductID, Cost)

SELECT 1, 100.00

UNION

SELECT 2, 50.00UNION SELECT 3, 25.00

GO
-- Verify Insertion

SELECT * FROM dbo.CalculationExample

GO
-- Declare Variables

DECLARE @MarginPercent decimal(10, 2)

DECLARE @TaxPercent decimal(10, 2)


-- Initialize Variables

SET @MarginPercent = .20

SET @TaxPercent = .05


-- Calculate Values

SELECT ProductID, Cost, Cost * @MarginPercent AS 'Margin',

Cost * @TaxPercent AS 'Tax',

Cost + (Cost * @MarginPercent) + (Cost * @TaxPercent) AS 'FinalCost'

FROM dbo.CalculationExample

GO


Calculations in Computed Columns:

Let's take those same calculations and now move them to computed columns in the table. Here is that example:


Calculations in Computed Columns


CREATE TABLE [dbo].[CalculationExample_ComputedColumns]

([ProductID] [int] NOT NULL,

[Cost] [decimal](18, 0) NOT NULL,

[Tax] AS ([Cost]*(0.05)),

[Margin] AS ([Cost]*(0.20)),

[FinalCost] AS (([Cost]+[Cost]*(0.20))+[Cost]*(0.05)),

CONSTRAINT [PK_CalculationExample_ComputedColumns] PRIMARY KEY CLUSTERED ([ProductID] ASC)

WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]


-- Populate Table

INSERT INTO dbo.CalculationExample_ComputedColumns

(ProductID, Cost)

SELECT 1, 100.00

UNION

SELECT 2, 50.00

UNION

SELECT 3, 25.00

GO
-- Review the values

SELECT * FROM dbo.CalculationExample_ComputedColumnsGO

Ref: http://www.mssqltips.com/tip.asp?tip=1547

Sep 22, 2008

Useful - Date Fucntions in SQL SERVER 2005


Here are some of the Most useful DateTime Functions that used to reduce many lines of code.

—-Today

SELECT GETDATE() ‘Today’

—-Yesterday

SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’

—-First Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) ‘First Day of Current Week’

—-Last Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’

—-First Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’

—-Last Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’

—-First Day of Current Month

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ‘First Day of Current Month’

—-Last Day of Current Month

SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ‘Last Day of Current Month’

—-First Day of Last Month

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ‘First Day of Last Month’

—-Last Day of Last Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

‘Last Day of Last Month’

—-First Day of Current Year

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ‘First Day of Current Year’

—-Last Day of Current Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

'Last Day of Current Year’

—-First Day of Last Year

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ‘First Day of Last Year’

—-Last Day of Last Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

‘Last Day of Last Year’

REF:http://blog.sqlauthority.com/2008/08/29/sql-server-few-useful-datetime-functions-to-find-specific-dates/

Sep 15, 2008

Searching a column name using Sounds in SQL SERVER 2005

SQL SERVER 2005 Even adds a Advanced search option like Searching using Sounds.
For eg:
We can search using the sounds of the word say, Column name having the sounds like rich..using the SOUNDEX Function May contain
Name:
Rex

Rich
Relay
Ross
Raj

The Query looks like

SELECT COLUMN_NAME FORM [dbo].[TABLE_NAMEWHERE]
WHERE SOUNDEX(COLUMN_NAME) = SOUNDEX('Rich')

This will return the column values which sound like rich.. We cant do this using 'like' Operator

How to find the the Primary Key columns in a DB in SQL SERVER 2005

How to List Primary Key columns in a DB:
This the Query that ll list all the Primary Key columns with its table name and column name in a DataBase,

SELECT p.TABLE_NAME,c.CONSTRAINT_NAME,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = p.TABLE_NAMEAND
AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
ORDER by c.TABLE_NAME

Sep 4, 2008

Automatically Running Stored Procedures at SQL Server Startup

Here in the below link u can find a very simple implementation of Automatically running SPs at SQL SERVER startup.
http://www.mssqltips.com/tip.asp?tip=1574

Aug 27, 2008

SQL SERVER 2000 VS 2005

DIFFERENCES BETWEEN SQL 2000 & SQL 2005:
I am a C# Asp.Net Developer with little knowledge in SQL. I myself had a question what is the difference between SQL server 2000 & SQL 2005. Not only me , more Developer have the same Question . So i analysed surffing in the net mostly from SQLServerCentral an Excellant site for SQL, i have provided a major differnces in tabular form.

First we can discuss the Adminsitrative Differences one by one

The Development differences are more in SQL 2005 which make the major differnces & advantages than SQL Server 2000.





Now we can easily answer the questions regarding the differences in SQL 2000 & SQL 2005

Aug 26, 2008

SQL Server Connection Strings - An overview

SQL Server Connection Strings :
Some common and not so common connection strings for the .NET SqlConnection object.
The article includes .NET sample code and some tricks to increase the supportability of your application.


Trusted Authentication:

Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI;

Trusted authentication uses the security credentials of the current user to make the connection to SQL Server. SQL Server uses Windows (or Active Directory) to validate the current user. ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2.
ServerName can also be expressed as an IP address.
SSPI stands for Security Support Provider Interface (in you were curious).

SQL Server Security Authentication:

Data Source=ServerName; Initial Catalog=DatabaseName; User Id=UserName; Password=UserPassword;
In SQL Server authentication SQL Server stores the username and password.
ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2. ServerName can also be expressed as an IP address.

Setting the Application Name:

Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; Application Name=MyAppName;
I often set the Application Name when I construct connections strings. Whatever text you assign to Application Name will appear in a couple of different places:

  • It will be displayed in Profiler under the Application Name column.
  • It will be shown in the output of sp_who2 in the Program Name column.
  • It will be shown in the Activity Monitor in the Application column.
You can get to the Activity Monitor in SQL Server Management Studio by
Management -> Activity Monitor.

  • It will appear in the program_name column if you select from master.dbo.sysprocesses (for SQL Server 2000)
  • It will appear int he program_name column if you select from sys.dm_exec_sessions (for SQL Server 2005 and later).

Setting the application name makes it very easy to find out what applications are issuing particular SQL statements against my database.
Setting the application name can also lead to an increase in the number of connections to your SQL Server. Each client that uses connection pooling will create one pool inside each application per unique connection string.
If you use multiple application names you have the possibility to increase the number of pools and thus the number of connections to SQL Server.

I've always found it more beneficial to have the application name than to have a few less connections to my database servers.

Using MARS (Multiple Active Result Sets):
Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; MultipleActiveResultSets=True;
If you want to use MARS you'll need to enable it in the connection string.

Sample .NET code:
There are two common ways to create a connection string in .NET.

The first is to use an explicit connection string.
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=L40;
Initial Catalog=master; Integrated Security=SSPI;";

The second is to use the Connection String Builder object in .NET to construct a connection string.
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();

csb.DataSource = "L40";

csb.InitialCatalog = "master";
csb.IntegratedSecurity = true;
SqlConnection conn = new SqlConnection();

conn.ConnectionString = csb.ToString();

Ref: http://www.sqlteam.com/article/sql-server-connection-string


Aug 20, 2008

C# Generics with examples

Introduction:
Parametric Polymorphism is a well-established programming language feature. Generics offers this feature to C#.
The best way to understand generics is to study some C# code that would benefit from generics. The code stated below is about a simple Stack class with two methods:

Push () and Pop ().
First, without using generics example you can get a clear idea about two issues:
a) Boxing and unboxing overhead and
b) No strong type information at compile type.
After that the same Stack class with the use of generics explains how these two issues are solved.
Example Code
Code without using generics:

public class Stack

{
object[] store;
int size;
public void Push(object x)
{...}
public object Pop()
{...}
}
Boxing and unboxing overhead:
You can push a value of any type onto a stack. To retrieve, the result of the Pop method must be explicitly cast back. For example if an integer passed to the Push method, it is automatically boxed. While retrieving, it must be unboxed with an explicit type cast.


Stack stack = new Stack();
stack.Push(3);
int i = (int)stack.Pop(); //unboxing with explicit int casting

Such boxing and unboxing operations add performance overhead since they involve dynamic memory allocations and run-time type checks.
No strong Type information at Compile Time


Another issue with the Stack class:
It is not possible to enforce the kind of data placed on a stack.
For example, a string can be pushed on a stack and then accidentally cast to the wrong type like integer after it is retrieved:

Stack stack = new Stack();
stack.Push("SomeName");
//pushing the string
int i = (int)stack.Pop();
//run-time exception will be thrown at this point
The above code is technically correct and you will not get any compile time error. The problem does not become visible until the code is executed; at that point an InvalidCastException is thrown.

Code with generics
In C# with generics, you declare class Stack {...}, where T is the type parameter.

Within class Stack you can use T as if it were a type. You can create a Stack as Integer by declaring
Stack or Stack as Customer object by declaring
Stack.
Simply your type arguments get substituted for the type parameter. All of the Ts become ints or Customers, you don't have to downcast, and there is strong type checking everywhere.
public class Stack
OpenTag(T)CloseTag
{
// items are of type T, which is kown when you create the object
T[] items;
int count;
public void Push(T item) {...}
//type of method pop will be decided when you creat the object
public T Pop() {...}
}
In the following example, int is given as the type argument for T:

Stack OpenTag Int CloseTag stack = new Stack OpenTag Int CloseTag ();
stack.Push(3);
int i = stack.Pop();

The Stack type is called a constructed type. In the Stack type, every occurrence of T is replaced with the type argument int. The Push and Pop methods of a Stack operate on int values, making it a compile-time error to push values of other types onto the stack, and eliminating the need to explicitly cast values back to their original type when they are retrieved.
You can use parameterization not only for classes but also for interfaces, structs, methods and delegates.
//For Interfaces interface IComparable OpenTag T CloseTag

//for structs struct HashBucket OpenTag T CloseTag
//for methods static void Reverse OpenTag T CloseTag (T[] arr)
//for delegates delegate void Action OpenTag T CloseTag (T arg)

Inside the CLR
When you compile StackOpenTag T CloseTag , or any other generic type, it compiles down to IL and metadata just like any normal type. The IL and metadata contains additional information that knows there's a type parameter. This means you have the type information at compile time.
Implementation of parametric polymorphism can be done in two ways

1. Code Specialization: Specializing the code for each instantiation
2. Code sharing: Generating common code for all instantiations.

The C# implementation of generics uses both code specialization and code sharing as explained below.
At runtime, when your application makes its first reference to StackOpenTag T CloseTag , the system looks to see if anyone already asked for Stack . If not, it feeds into the JIT the IL and metadata for Stack and the type argument int.


The .NET Common Language Runtime creates a specialized copy of the native code for each generic type instantiation with a value type, but shares a single copy of the native code for all reference types (since, at the native code level, references are just pointers with the same representation).

In other words, for instantiations those are value types: such as Stack OpenTag int CloseTag , Stack OpenTag Long CloseTag , Stack OpenTag Double CloseTag , Stack OpenTag Float CloseTag, CLR creates a unique copy of the executable native code.

So StackOpenTag int CloseTag gets its own code. Stack OpenTag Long CloseTag gets its own code. Stack OpenTag Float CloseTag gets its own code. Stack OpenTag int CloseTag uses 32 bits and Stack OpenTag Long CloseTag uses 64 bits. While reference types, Stack OpenTag Dog CloseTag is different from Stack OpenTag Cat CloseTag , but they actually share all the same method code and both are 32-bit pointers.

This code sharing avoids code bloat and gives better performance.
To support generics, Microsoft did some changes to CLR, metadata, type-loader,language compilers, IL instructions and so on for the next release of Visual Studio.NET(code named Whidbey).


What you can get with Generics
Generics can make the C# code more efficient, type-safe and maintainable.
Efficiency: Following points states that how performance is boosted.
Instantiations of parameterized classes are loaded dynamically and the code for their methods is generated on demand [Just in Time].
Where ever possible, compiled code and data representations are shared between different instantiations.
Due to type specialization, the implementation never needs to box values of primitive types.


Safety: Strong type checking at compile time, hence more bugs caught at compile time itself.
Maintainability: Maintainability is achieved with fewer explicit conversions between data types and code with generics improves clarity and expressively.


Conclusion
Generics gives better performance, type safety and clarity to the C# programs. Generics will increase program reliability by adding strong type checking. Learning how to use generics is straightforward, hopefully this article has inspired you to look deeper into how you can use them.

How to write property for CheckBox control in Asp.Net 2.0

Say chActive is the checkBox ,

private string Active
{
get
{
return chkActive.Checked ? "Y" : "N";
}

set
{
chkActive.Checked = (string.Compare(value, "Y", true) == 0 ? true : false);
}
}

Thus a check box property has been written

Aug 5, 2008

SQL server Count Aggregate


Count Aggregate:

Just declare a table, say

DECLARE @t table

(

id int identity(1,1),

value varchar(50)

)

Insert some values,

INSERT INTO @t values(NULL)

INSERT INTO @t values(NULL)

INSERT INTO @t values(NULL)

INSERT INTO @t values('1')

INSERT INTO @t values('2')

INSERT INTO @t values('3')

What is the output?

SELECT count(id), count(value) FROM @t

It's 6,3 actually because, The Count aggregate does not include NULL values, so the count will be 3 rows for the 2nd column

Jul 29, 2008

SQL DateTime Functions

SQL - Timestamp
A timestamp servers as the catch all for dates and times. Retrieving a timestamp is very simple and the result can be converted or manipulated in nearly every way imaginable.

SQL Code:
SELECT CURRENT_TIMESTAMP;
Return a Timestamp:2004-06-22 10:33:11.840
Keep in mind that each platform of SQL (DB2, Oracle, SQL Server, etc...) may return dates and times which are formatted differently.
SQL - Date FunctionsAs we just mentioned, it is possible to breakdown timestamps into their individual pieces using any of the following date functions.

SQL Code:
SELECT MONTH(CURRENT_TIMESTAMP);
Return a Month:6 SQL Code:
SELECT DAY(CURRENT_TIMESTAMP);
Return a Day:22

There are many more functions available, including functions to extract milliseconds, names of the months, names of each week day, etc.
Each SQL platform varies in the actual naming of date functions. Here's a few c\The following is a list of other date functions available to most platforms of SQL with the exception of MS's SQL Server.

SQL Function Code:
SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)
SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840)
SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7)
SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31)
SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365)
SELECT MONTHNAME(CURRENT_TIMESTAMP); - returns the month name (January - December
SELECT DAYNAME(CURRENT_TIMESTAMP); - returns the name of the day (Sunday - Saturday)
SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)


Timestamps are often the easiest to work with, but we certainly are not limited to using only the current_timestamp as our parameter. We can send any date, time, or timestamp to the function which then returns our result.

SQL Code:
SELECT MONTHNAME('2004-11-27');
Return a Month Name: MONTHNAME('2004-11-27') November
Date functions can also be performed on table columns similarly to numeric and mathematical functions such as SUM() or AVG().

SQL Code:
SELECT DAYOFYEAR(column_name) FROM table_name WHERE name = 'Joe';
SQL will return a numeric result from 1 - 365 representing the day of the year that Joe's record was created/inserted.We can expand this concept one step further with the use of a subquery. Say we have a table with a column named timestamp. In this table column are timestamps of when each record was entered and we would like to know what was the numeric day of the year that a record was entered.

SQL Code:
SELECT DAYOFYEAR((SELECT DATE(timestamp) FROM employees WHERE name = 'James Bond'));
Above you can see how it is possible to combine several date functions as well as a subquery to return very specific information about Mr. James Bond.
SQL - Inserting Date DataDate data exists as numbers, strings, and timestamps. Built into most platforms are several date column types such as DATE or TIMESTAMP. By setting the default value to the current date or timestamp, the table column will automatically be filled with a current date/timestamp as each record is inserted.
Here's the code to add a timestamp column to an existing table.
SQL Code:
ALTER TABLE `orders` ADD `order_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
Now each time an order is placed in our make believe business, a timestamp of that order is also recorded.A date or timestamp table column will only allow date data types to be inserted as values so be sure to convert any strings or numbers to dates and timestamps before trying to insert them.

SQL - Datepart()

Microsoft's SQL Server takes a little different approach to working with dates. It is still possible to extract individual parts of a timestamp and several other functions also work as outlined in SQL - Date. The difference is that SQL Server uses one main function as oppose to several different functions ie the DATEPART() function.
Datepart() requires two parameters, a part argument and a date argument. By part we mean year, day of the year, day of the week, etc. Let's look at an example.

SQL Code:
SELECT DATEPART(week, '2005-12-31');Return the Week Number:53

Here we have successfully pulled the "week number" from existing date.
SQL's CURRENT_DATE function could be also be substituted or a string value representing the year ('Dec 31, 2005').

Jul 25, 2008

Handy Keyboard Shortcuts for c# 2005


Handy Keyboard Shortcuts for c# 2005

The following keyboard shortcuts I find invaluable. It's amazing how many people still use the mouse to do everything.
Document navigation :

Ctrl+Tab Switch documents

Ctrl+Shift+Tab Reverse switch documents

Ctrl+kk Drop a bookmark

Ctrl+kn Itterate through bookmarks

F7 Switch from HTML to Codebehind view

Ctrl+- Navigate backward through last cursor locations


Code Navigation :

F12 Goto Definition

Ctrl+] Jump to matching brace


Editing :

Ctrl+c Copy a whole line

Ctrl+v When a whole line in the clipboard (as above) this will instet a whole copied line.. handy for quick duplication

Ctrl+u Change to lower case

Ctrl+Shift+U Change to UPPER case


Macros :

Ctrl+Shift+R Record a quick Macro

Ctrl+Shift+P Run the quick Macro you just recorded
Comments

Ctrl+kc Comment out selected lines

Ctrl+ku Uncomment selected lines


Formatting

Ctrl+kd Autoformat selected lines

Hope this is more useful to attract others!Happy Programming

Jul 22, 2008

Add,Edit, Delete and View in GridView in Asp.net 2.0

Description
DataGrid plays very important component in ASP.Net Application. It covers most of the basic reports for which you do not need any reporting component like Crystal Report, Report Viewer etc.
Major concern with any data entry form is Add, Edit, Delete and View.


Assumptions-
We are using SQL Server Express editions as Database.
Table Name- Employees
Fields- EmpId, EmpName, Designation
Take GridView control on page and set the ID property to dgEmployees.
Go to property builder by right click on

GridView -> Show Smart Tag -> Edit Columns.

Uncheck Auto-Generate Field from the Property window.
Add three TemplateField Column for Employee Name, Designation, & Delete Button. Add Edit button from CommandField Group from Property Window.
TemplateField Colums have ItemTemplate,

Alternating Item Template,

Edit Template, Header Template, Footer Template.


Each template columns Item Template field contains Label Control & Edit Template contains TextBox control for Editing item. Set the Binding Field name to the Text Property of both controls for each template field to the respective Database Column Name

i.e Eval("EmpName").


Set the DataKeyNames property of GridView to Primary Key Column of DataBase i.e. EmpId.

This property binds the database column field value to each row of gridview as a Unique Identifier.
Set the data bindings for Delete Button for CommandArgument Eval("EmpId"); for saving the ID column value from database for fetching the ID field value while Deleting the Record.

Set the CommandName property to Delete Button to CMDDelete. The CommandName property can contain any string name which can be used to recognize the type of command invoked from gridview. Because when any of the event generated in GridVeiw it fires RowCommand Event. In this event we have to handle the Delete Button Code. Instead if you are using default Delete Button of GridView then register for RowDeleting event of GridView and for accessing Unique ID columnvalue from database you need to fetch the id from DataKeys collection of GridView.

For e.g.
int EmpId = Convert.ToInt32(dgEmployees.DataKeys[e.RowIndex].Value);


Place the Textbox control in the grids Footer template for Adding new record. Set the CommandName to CMDAdd for Add button.
Register events for Edit, Update, Cancel button of gridview RowEditing, RowUpdating, RowCancelEditing.


View in GridView
To view data in gridview is very simple. Just create a DataSet using SqlDataAdapter̢۪s Fill method and set the GridViews DataSource Property to DataSet.
Create a Method to Bind the GridView to DataSource named BindGrid. This method fetches data from the GetEmployees method which returns DataSet from Employees table.
Call the BindGrid on Page_Load in !IsPostBack block to fill the grid by default.
private void BindGrid()
{
dgEmployees.DataSource = GetEmployees();
dgEmployees.DataBind();
}
private DataSet GetEmployees()
{
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection();
conn.ConnectionString =ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
SqlDataAdapter da = new SqlDataAdapter("Select * From Employees", conn);
try
{
da.Fill(ds);
return ds;
}
catch { }
finally
{
conn.Close();
conn.Dispose();
}
return null;
}


Edit in GridView
For Editing Register RowEditing event of GridView. To switch the normal mode to Edit mode of gridview EditIndex property plays important role. EditIndex specifies which row is in edit mode by setting RowIndex to it. By default EditIndex of gridview is -1 (Normal mode).

If you want to edit 3rd Row then set the EditIndex to 2 (Row index starts from 0,1,2..).
After setting editindex refresh the grid by calling BinGrid. GridViewEditEventArgs object knows the current row index so getting row index of the selected row in gridveiw is not big deal; just

e.NewEditIndex (e object of GridViewEditEventArgs).


protected void dgEmployees_RowEditing(object sender, GridViewEditEventArgs e)
{
dgEmployees.EditIndex = e.NewEditIndex;
BindGrid();
}


Cancel in GridView
For Cancel just reset the GridView editindex to default i.e. -1 and refresh the grid.
protected void dgEmployees_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
dgEmployees.EditIndex = -1;
BindGrid();
}


Update in GridView
For Update register RowUpdating event of the gridview. Find the Unique id for updating the row from DataKeys collection of gridview.
int EmpId = Convert.ToInt32(dgEmployees.DataKeys[e.RowIndex].Value);
Find the controls in the selected row by using FindControl method of gridviews rows collection and collect data from the text boxes.


TextBox txtname = dgEmployees.Rows[e.RowIndex].FindControl("txtEmpName") as TextBox;


TextBox txtdesign = dgEmployees.Rows[e.RowIndex].FindControl("txtDesignation") as TextBox;


Finally update the row and refresh the grid.


if(txtname!=null && txtdesign!=null)
UpdateEmployee(empId, txtname.Text.Trim(), txtdesign.Text.Trim());
dgEmployees.EditIndex = -1;
BindGrid();


Complete code-
protected void dgEmployees_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int empId = Convert.ToInt32(dgEmployees.DataKeys[e.RowIndex].Value);
//Find Text boxex
TextBox txtname = dgEmployees.Rows[e.RowIndex].FindControl("txtEmpName") as TextBox;
TextBoxtxtdesign=dgEmployees.Rows[e.RowIndex].FindControl("txtDesignation") as TextBox;
if(txtname!=null && txtdesign!=null)
UpdateEmployee(empId, txtname.Text.Trim(), txtdesign.Text.Trim());
dgEmployees.EditIndex = -1;
BindGrid();
}


Custom Delete in GridView
For Delete register RowCommand event of the gridview. Find the Unique id for deleting the row from DataKeys collection of gridview. Check for CommanName and invoke delete method for the selected row.
protected void dgEmployees_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("CMDDelete"))
{
int EmpId = Convert.ToInt32(e.CommandArgument);
DeleteEmployee(EmpId);
//Refresh Grid
BindGrid();
}
}
Add in GridView
Adding from GridView is just some trick with Footer Template. I added textboxes and a add button in the footer row of the gridview. When u are in Normal mode it is visible else it is invisible to synchronize between edit and add.
Like Updating find the Textbox and pass the values to Addemployee method like
else if (e.CommandName.Equals("CMDAdd"))
{
TextBox txtname = dgEmployees.FooterRow.FindControl("txtEmpName") asTextBox;
TextBox txtdesign = dgEmployees.FooterRow.FindControl("txtDesignation") as TextBox;
if (txtname != null && txtdesign != null)
{
AddEmployee(txtname.Text.Trim(), txtdesign.Text.Trim());
BindGrid();
}
}
The Complete code for EditEmployee, AddEmployee, UpdateEmployee, DeleteEmployee is in Source File.

The code should be in RowCommand event only. Due to this we use CommandName for different button control to differentiate between the type of code to be handled by gridview.