How to find already exist Value in sql server

How to find already exist Value in sql server

Use this query, If you want the value, which is already exists in the database:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[SP_insert]--(Store Procedure)

@b_Barcode varchar(100)--(Column name)

as begin

if exists (select b_Barcode from Barcode where b_Barcode=@b_Barcode)

return 0

else

insert into Barcode (b_Barcode) values(@b_Barcode)

end

How to check SQl Server Edition .Product Version,Product Level

How to check SQl Server Edition :

with the help of this single line query you can check which Edition of server you are using:

--Query

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

It gives you this output as below:

How to Delete All Tables Data in a single query

How to Delete All Tables Data in a single query in SQL SERVER

use this query to delete data in all tables in sql server

- disable all constraints

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- delete data in all tables

EXEC sp_MSForEachTable 'DELETE FROM ?'

-- enable all constraints                                  

exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

How to create Data table Dynamically in C#

How to create Data table Dynamically in C#

Use This Code to  create Data table Dynamically in C#

   public void DynamicDatatable()

        {

            try

            {

                DataTable dt = new DataTable();

                DataColumn dc;//DataColumn

                dc = new DataColumn("Itemcode", typeof(string));

                dt.Columns.Add(dc);

                dc = new DataColumn("Qty", typeof(string));

                dt.Columns.Add(dc);

                dc = new DataColumn("DispQty", typeof(string));

                dt.Columns.Add(dc);

                string[] arr;

                arr = sResponse.Split('-');

                for (int i = 0; i < arr.Length - 1; i++)

                {

                    DataRow dr1 = dt.NewRow();//DataRow

                    string[] strarr = arr[i].Split('~');

                    if (strarr.Length >= 3)

                    {

                        dr1["Itemcode"] = Convert.ToString(strarr[0]);

                        dr1["Qty"] = Convert.ToString(strarr[1]);

                        dr1["DispQty"] = Convert.ToString(strarr[2]);

                        dt.Rows.Add(dr1);

                    }

                }

                this.dataGrid1.DataSource = dt.DefaultView;

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

"Date Time-Format" Custom Date Time Formats in ASP.NET

 "Date Time -Formats" Custom  Date Time Formats in ASP.NET

Below the complete list of custom date time formats in asp.net:

INPUTS                                                                          OUTPUTS

Response.Write(String.Format("{0:MM/dd/yyyy}", dt) + "</br>“);                   01/16/2014

Response.Write(String.Format("{0:t}", dt) + "</br>“);                                         5:55 PM

Response.Write(String.Format("{0:d}", dt) + "</br>“);                                      1/16/2014

Response.Write(String.Format("{0:T}", dt) + "</br>“);                                      5:55:31 PM

Response.Write(String.Format("{0:D}", dt) + "</br>“);                                    Thursday, January 16, 2014

Response.Write(String.Format("{0:f}", dt) + "</br>“);                                     Thursday, January 16, 2014 5:55 PM

Response.Write(String.Format("{0:F}", dt) + "</br>“);                        Thursday, January 16, 2014 5:55:31 PM

Response.Write(String.Format("{0:g}", dt) + "</br>“);                                   1/16/2014 5:55 PM

Response.Write(String.Format("{0:G}", dt) + "</br>“);                                   1/16/2014 5:55:31 PM

Response.Write(String.Format("{0:m}", dt) + "</br>“);                                  January 16

Response.Write(String.Format("{0:y}", dt) + "</br>“);                                   January, 2014

Response.Write(String.Format("{0:r}", dt) + "</br>“);                                   Thu, 16 Jan 2014 17:55:31 GMT

Response.Write(String.Format("{0:s}", dt) + "</br>“);                                     2014-01-16T17:55:31

Response.Write(String.Format("{0:u}", dt) + "</br>“);                                    2014-01-16 17:55:31Z

Response.Write(dt + "</br>“);                                                                           1/16/2014 6:13:04 PM Response.Write(String.Format("{0:y yy yyy yyyy}", dt) + "</br>“);                     14 14 2014 2014 Response.Write(String.Format("{0:M MM MMM MMMM}", dt) + "</br>“);      1 01 Jan January Response.Write(String.Format("{0:d dd ddd dddd}", dt) + "</br                         16 16 Thu Thursday Response.Write(String.Format("{0:h hh H HH}", dt) + "</br>“);                          5 05 17 17 Response.Write(String.Format("{0:m mm}", dt) + "</br>“);                                 55 55 Response.Write(String.Format("{0:s ss}", dt) + "</br>“);                                      31 31 Response.Write(String.Format("{0:f ff fff ffff}", dt) + "</br>“);                           5 50 502 5022 Response.Write(String.Format("{0:F FF FFF FFFF}", dt) + "</br>“);             5 5 502 5022 Response.Write(String.Format("{0:t tt}", dt) + "</br>“);                                      P PM Response.Write(String.Format("{0:z zz zzz}", dt) + "</br>“);                              +5 +05 +05:30 Response.Write(String.Format("{0:d/M/yyyy HH:mm:ss}", dt) + "</br>“);       16/1/2014 17:55:31 Response.Write(String.Format("{0:d/M/yyyy HH:mm:ss}", dt) + "</br>“);       16/1/2014 17:55:31 Response.Write(String.Format("{0:M/d/yyyy}", dt) + "</br>“);                          1/16/2014 Response.Write(String.Format("{0:MM/dd/yyyy}", dt) + "</br>“);                     01/16/2014 Response.Write(String.Format("{0:ddd, MMM d, yyyy}", dt) + "</br>“);           Thu, Jan 16, 2014 Response.Write(String.Format("{0:dddd, MMMM d, yyyy}", dt) + "</br>“);      Thursday, January 16, 2014 Response.Write(String.Format("{0:MM/dd/yy}", dt) + "</br>“);                            01/16/14

"SQL SERVER " Interview Questions And Answers for SQL SERVER

"SQL SERVER " Interview Questions And Answers for SQL SERVER 

1.   What is an Entity?

The basic data item stored in database is called entity. An entity can be any object, item, place, person, concept, or activity about which data is stored.

2.  What is an attribute?

An attribute is a property of an entity. It describes a part of an entity. Entity could have one or more attributes.

3.    What is ER diagram?

An Entity Relationship Diagram is diagrammatic representation of the logical structure of a database system.

4.     Describe the concept of keys.

Candidate key

An attribute that uniquely identifies a row is called candidate key. It is also called das surrogate key.

Primary key

A candidate key that you choose to identify rows uniquely is called a primary key.

Alternate key

If there are multiple candidate keys in a table, the candidate keys that are chosen as primary key are called the alternate keys.

Composite key

When the key that uniquely identifies the rows of a table is made up of more than one attribute, it is called as a composite key.

Foreign key

Two tables can be related using a common attribute. When a primary key of one table is also available as an attribute in another related table it is called a foreign key.

5.    What are joins?

Sometimes, data from multiple tables is to be displayed using select statement. For this purpose, the tables in use must have a column that is equated. This is termed as simple join or multiple join.

Sometimes, you might want to display all records from one table and some from another. This type of join is called an outer join. An outer join is only possible between two tables.

There are two types of outer join, namely left and right. In a left outer join, all the rows of the first table named in the FROM clause are displayed. In a right outer join all the rows from the second table mentioned in the FROM clause are displayed. In either case, all the matching rows from other table are displayed.

6.    What is a sub query?

Sometimes the results of one query are dependant on the results of another query. For this purpose one query is nested inside another query, this is called as sub query.

7.    What are the types of constraints?

You can enforce data integrity by using constraints. Constraints are divided in to five categories.

Primary key constraint

A primary key constraint is defined on a column are a set of columns whose values uniquely identify the rows in a table. It cannot contain null values.

Unique constraint

Unique constraints are used to enforce uniqueness on non-primary key columns. It allows null values but only one row can have a null value.

Multiple unique constraints can be created on a table.

Foreign Key constraint

You can use the foreign key constraint to remove the inconsistency in two tables when the data in the one table is dependant on the other table.

Check constraint

It enforces domain integrity by restricting the values to be inserted in a column. It is possible to define multiple check constraints on a single column. These are evaluated in the order in which they are defined.

Default Constraint

A default constraint can be use to assign a constant value to a column and the user need not insert values in to that column.

8.   What is a rule?

The required integrity can be enforced by specifying a check constraint or by defining a rule. But check constraint modifies the table structure. The constraint can there fore be implemented using rules with out changing the table structure. This rule is applied before an insert or update statement .

A rule must be bound to a column or a user-define d data type. This is done using stored procedure sp_bindrule. Rules do not apply to data that has already been inserted in the table. The existing values in tables do not have to meet the criteria specified by the rule.

9.     What are indexes?

To speed up data retrieval indexes are used. Indexes also enforce the uniqueness of rows.

Advantages

1.     Improves the speed of execution.

2.     Enforces uniqueness of data.

3.     Speeds up joins between tables.

Disadvantages

1.Takes disk space to store.

2.Data modification takes longer.

3.Takes time to create index.

Types of indexes

Clustered index

1.The data is physically sorted

2.One clustered index can be created per table, so you should build it on attributes that have a high percentage of unique values and that are not modified often.

Nonclustered index

1.The physical order of the rows is not the same as the index order.

2.There can be as many as 249 nonclustered indexes per column.

10.  What are views?

A view is a virtual table, which gives access to a subset of columns from one or more tables. It is a query stored as an object in the database. Hence a view is an object that derives it data from one or more tables.

Advantages

1.A view serves as a security mechanism.

2.A view simplifies the usage of complex queries.

11.   What are store procedures and its advantages?

A stored procedure is collection or batch of Transact-SQL statements and control flow language that is stored under one name, and executed as single unit. It helps in improving the performance of a query. It is a precompiled object. As it is ready to execute no time is needed for parsing and compiling the procedure.

Advantages

Improved performance – Applications do not have to compile the procedure over and over again.

Reduction in network congestion – applications need not submit multiple SQL statements to server for the purpose of processing.

Enhanced accuracy – SQL statements included in a procedure are designed by experienced programmers and are therefore more efficient, error free, and tested.

Better security mechanism – users can be granted permission to execute a stored procedure even if they do not own it.

Types of stored procedures

1.User-defined

2.System defined

These are prefixed with sp_. These are for administrative purpose and are stored in the database and are accessible to all users.

3.Temporary

These are prefixed with #, stored in tempdb and are automatically dropped when connection terminates.

4.Remote

These are created and stored in remote servers and can accessed by users with appropriate permissions.

ASP.NET Interview Questions and Answers (3.5)

Asp.net  Interview Questions and Answers (3.5)

What is the Windows Presentation Foundation

Answer :: The Windows Presentation Foundation is the unified presentation subsystem for Windows. It

consists of a display engine and a managed-code framework. The Windows Presentation Foundation unifies

how Windows creates, displays, and manipulates documents, media, and user interface, which enables

developers and designers to create visually-stunning, differentiated user experiences that improve customer

connection. When it ships, scheduled for 2006, the Windows Presentation Foundation will be available on

Windows XP, Windows Server 2003 and all future releases of the Windows operating system. When delivered,

the Windows Presentation Foundation will become Microsoft&rsquo;s strategic user interface technology. 

What is XAML

Answer :: XAML, Extensible Application Markup Language, is Microsoft&rsquo;s XML-based language for

creating a rich graphical user interface....XAML was introduced in 2003 as the language behind Windows

Presentation Foundation, then known as Avalon.

What is the Windows Presentation Foundation

Answer :: The Windows Communication Foundation is a set of technologies for building and running connected

systems.

The Windows Communication Foundation is a new breed of communications infrastructure built around the

Web services architecture. Advanced Web services support in the Windows Communication Foundation

provides secure, reliable, and transacted messaging along with interoperability. The Windows Communication

Foundation&rsquo;s service-oriented programming model is built on the .NET Framework and simplifies

development of connected systems. The Windows Communication Foundation unifies a broad array of

distributed systems capabilities in an extensible architecture, spanning transports, security systems, messaging

patterns, encodings, network topologies and hosting models. The Windows Communication Foundation will be

an integral capability of Windows Vista and will also be supported on Windows XP and Windows Server 2003.

Microsoft has also done significant work to integrate the Windows Communication Foundation with existing

Microsoft technologies for building distributed systems including COM+, MSMQ, and ASP.NET Web services.

Applications built with those existing technologies can now be exposed as services without modification to the

application. This infrastructure-level solution greatly assists developers in exposing existing applications as

services. The Windows Communication Foundation also provides simple and mechanical mechanisms to

migrate applications that use .NET remoting, ASP.NET Web services, and .NET Enterprise Services to natively

use the Windows Communication Foundation programming model. 

Describe the usefulness Visual Studio 2008 Split View feature?

Answer :: Visual Studio allows you to set the default view (Split, Source or Design) and to choose between a

horizontal split and a vertical split. Especially this last option is very handy when you have a widescreen

monitor.

To enable vertical split-view orientation in VS 2008, select the tools-&gt;options menu item and go to the HTML

Designer-&gt;General section. Then check the "Split views vertically" checkbox:

Can you compare Nested Master Page Support in Visual Studio 2008 as compared to VS 2005?

Answer :: Already Visual Studio 2005 supports nested master pages concept with .NET 2.0, but the problem

with this Visual Studio 2005 that pages based on nested masters can't be edited using WYSIWYG web

designer. But now in VS 2008 you can even edit the nested master pages.

What is a Nested Master Page and how will you use it ?

Answer :: Master pages can be nested, with one master page referencing another as its master. Nested master

pages allow you to create componentized master pages. For example, a large site might contain an overall

master page that defines the look of the site. Different site content partners can then define their own child

master pages that reference the site master and that in turn define the look for that partner's content.

A child master page has the file name extension .master, as with any master page. The child master page

typically contains content controls that are mapped to content placeholders on the parent master page. In this

respect, the child master page is laid out like any content page. However, the child master page also has

content placeholders of its own to display content supplied by its own child pages. The following three page

listings show a simple nested master page configuration.

What is the enhanced ajax support for Asp.net in 3.5 as compared to the previous versions of Asp.net ?

Answer :: Previously developer has to install AJAX control library separately that does not come from VS, but

now if you install Visual Studio 2008, you can built-in AJAX control library. This Ajax Library contains plenty of

rich AJAX controls like Menu, TreeView, webparts and also these components support JSON and VS 2008

contains in built ASP.NET AJAX Control Extenders.

What is VS 2008 Multi-Targeting

Answer :: Earlier you were not able to working with .NET 1.1 applications directly in visual studio 2005. Now in

Visual studio 2008 you are able to create, run, debug the .NET 2.0, .NET 3.0 and .NET 3.5 applications. You

can also deploy .NET 2.0 applications in the machines which contains only .NET 2.0 not .NET 3.x. 

C# interview Questions and Answers

C# interview Questions and Answers :

What is the syntax to inherit from a class in C#?

Place a colon and then the name of the base class. Example: class MyNewClass :

MyBaseClass

Can you prevent your class from being inherited by another class?

Yes. The keyword “sealed” will prevent the class from being inherited.

Can you allow a class to be inherited, but prevent the method from being overridden?

Yes. Just leave the class public and make the method sealed.

What’s an abstract class?

A class that cannot be instantiated. An abstract class is a class that must be inherited and

have the methods overridden. An abstract class is essentially a blueprint for a class

without any implementation.

When do you absolutely have to declare a class as abstract?

1. When the class itself is inherited from an abstract class, but not all base abstract

methods have been overridden.

2. When at least one of the methods in the class is abstract.

What is an interface class?

Interfaces, like classes, define a set of properties, methods, and events. But unlike classes,

interfaces do not provide implementation. They are implemented by classes, and defined

as separate entities from classes.

Why can’t you specify the accessibility modifier for methods inside the interface?

They all must be public, and are therefore public by default.

Can you inherit multiple interfaces?

Yes. .NET does support multiple interfaces.

What happens if you inherit multiple interfaces and they have conflicting method

names?

It’s up to you to implement the method inside your own class, so implementation is left

entirely up to you. This might cause a problem on a higher-level scale if similarly named

methods from different interfaces expect different data, but as far as compiler cares

you’re okay. To Do: Investigate

What’s the top .NET class that everything is derived from?

System.Object.

What does the term immutable mean?

The data value may not be changed. Note: The variable value may be changed, but the

original immutable data value was discarded and a new data value was created in

memory.

What’s the difference between System.String and System.Text.StringBuilder

classes?

System.String is immutable. System.StringBuilder was designed with the purpose of

having a mutable string where a variety of operations can be performed.

What’s the advantage of using System.Text.StringBuilder over System.String?

StringBuilder is more efficient in cases where there is a large amount of string

manipulation. Strings are immutable, so each time a string is changed, a new instance in

memory is created.

Can you store multiple data types in System.Array?

No.

What’s the difference between the System.Array.CopyTo() and

System.Array.Clone()?

The Clone() method returns a new array (a shallow copy) object containing all the

elements in the original array. The CopyTo() method copies the elements into another

existing array. Both perform a shallow copy. A shallow copy means the contents (each

array element) contains references to the same object as the elements in the original array.

A deep copy (which neither of these methods performs) would create a new instance of

each element's object, resulting in a different, yet identacle object.

How can you sort the elements of the array in descending order?

By calling Sort() and then Reverse() methods.

What’s the .NET collection class that allows an element to be accessed using a

unique key?

HashTable.

What class is underneath the SortedList class?

A sorted HashTable.

Will the finally block get executed if an exception has not occurred?

Yes.

What’s the C# syntax to catch any possible exception?

A catch block that catches the exception of type System.Exception. You can also omit the