Error: Msg 3723, It is being used for PRIMARY KEY constraint enforcement.

When we execute following query to sql management query windows:

DROP INDEX tblEmployees.PK__Table__3214EC079D3D6FE5

When you execute it we will get following msg 3723

will get:

--Msg 3723, Level 16, State 4, Line 1

--An explicit DROP INDEX is not allowed on index 'Table.PK__Table__3214EC079D3D6FE5'.

--It is being used for PRIMARY KEY constraint enforcement.

This error message will get when you are going to drop a clustered index on table using query window.

So it stated that you need to drop primary key constraint of the table. And we know that table has only one clustered index so you can create only one clustered index in the table but you can add more columns to the clustered index.

How to solve this error:

You can write following query to the query window and select

ALTER TABLE [Table]
DROP CONSTRAINT PK__Table__3214EC079D3D6FE5

Now if you will refresh the index folder of table you should see the primary key clustered index removed from it.

You can also remove using graphically for that one you need to expand the table and go to table and expand index folder where you can see primary key clustered index with PK_ prefix right click on it and press on delete. It will delete pk index.

Difference between IEnumerable and IEnumerator

IEnumerable

These two concept IEnumerable and IEnumerator is very useful when you are iterate though a collection. IEnumerator knows its current state where IEnumerable does not. Let’s understand with its definition and example.

IEnumerable returns an enumerator that iterates through the collection or you can say it supports simple iteration simple collection of specified types.

IEnumerable is simple to use through the collection.

IEnumerable does not know its current state.

Example:

using System;
using System.Collections.Generic;

namespace FirstTest
{
    class Program
    {
        static void Main(string[] args)
        {
            List<int> numbers = new List<int>() { 121, 122, 123, 124, 125, 126 };

     //Convert numbers to IEnumerable<int>
            IEnumerable<int> ienum = (IEnumerable<int>)numbers;
    
     //Iterate each elements through loop
            foreach (int data in ienum)
            {
                Console.WriteLine(data);
            }

        }
    }
}

Output:

121

122

123

124

125

126

IEnumerator

Get the element in the collection at the current position of the enumerator. Using current position you can value of from the collection.

It is complex to use.

It knows its current state.

Using MoveNext()true then the enumerator was successfully advanced to the next element; false then the enumerator has passed the end of the collection.

Using Current you can get value from the current collection.

Example:

using System;
using System.Collections.Generic;

namespace FirstTest
{
    class Program
    {
        static void Main(string[] args)
        {
            List<int> numbers = new List<int>() { 121, 122, 123, 124, 125, 126 };

            //List have GetEnumerator method which returns ienumerator.
            IEnumerator<int> ienumrator = numbers.GetEnumerator();
     
     //enumerat to next element if has or else end to the collection
            while (ienumrator.MoveNext())
            {
                Console.WriteLine(ienumrator.Current.ToString());
            }

        }
    }
}

Output:

121

122

123

124

125

126

Difference between IEnumerable and IEnumerator understand with example

Ok till now, let’s take an example with condition which match condition if number greater than some transfer to another method using IEnumerator:

using System;
using System.Collections.Generic;

namespace FirstTest
{
    class Program
    {
        static void Main(string[] args)
        {
            List<int> numbers = new List<int>() { 111, 122, 123, 124, 125, 126 };

            IEnumerator<int> ienum = numbers.GetEnumerator();
            Iterate2001To2003(ienum);

        }

        public static void Iterate2001To2003(IEnumerator<int> o)
        {
            while(o.MoveNext())
            {
                Console.WriteLine(o.Current.ToString());

                if(Convert.ToInt32(o.Current) > 123)
                {
                    Iterate2003To2001(o);
                }
            }
        }

        public static void Iterate2003To2001(IEnumerator<int> o)
        {
            while (o.MoveNext())
            {
                Console.WriteLine(o.Current.ToString());
            }
        }

    }
}

Output:

121

122

123

124

125

126

Now using IEnumerable:

using System;
using System.Collections.Generic;

namespace FirstTest
{
    class Program
    {
        static void Main(string[] args)
        {
            List<int> numbers = new List<int>() { 111, 122, 123, 124, 125, 126 };

            IEnumerable<int> ienum = (IEnumerable<int>)numbers;
            Iterate2001To2003(ienum);

        }

        public static void Iterate2001To2003(IEnumerable<int> o)
        {
            foreach(int i in o)
            {
                Console.WriteLine(i);

                if(i > 123)
                {
                    Iterate2003To2001(o);
                }
            }
        }

        public static void Iterate2003To2001(IEnumerable<int> o)
        {
            foreach(int i in o)
            {
                Console.WriteLine(i);
            }
        }

    }
}

Output:

121

122

123

124

111

122

123

124

So you will see that unexpected output will come.

Conclusion:

IEnumerable is simple to use compare to IEnumerator.

It is up to you which concept you want to like to use with your project.

you can also visit:  www.mybook-lang.com

difference between Convert.ToString() and ToString() methods

Every class or struct in C# implicitly inherits the Object class. Therefore, every object in C# gets the ToString method, which returns a string representation of that object.

.ToString() does not handle null value. It gives and System.NullReferenceException.

But Convert.ToString() handles null value.

Let’s understand this difference using example: Using Convert.ToString()

using System;

namespace ConverString
{
    class Program
    {
        static void Main(string[] args)
        {
            string Test = null;

            try{
            
                //Convert class is converting base data type to another data type.
                string convertTest = Convert.ToString(Test);

                Console.WriteLine("Result: {0}",convertTest);
            }
            catch(Exception ex)
            {
                //if exception caught.
                Console.WriteLine(ex.Message.ToString());
            }
            
        }
    }
}

Output:

Result: null

Example 2: using ToString() method:

namespace ConverString
{
    class Program
    {
        static void Main(string[] args)
        {
            string Test = null;

            try{
            
                Console.WriteLine("Result: {0}", Test.ToString());
            }
            catch(Exception ex)
            {
                //if exception caught.
                Console.WriteLine(ex.Message.ToString());
            }
            
        }
    }
}

Output:

Object reference not set to instance of an object.

So you can differentiate that .ToString() method does not allow null value while Convert.ToString allows null value.

Override .ToString method

When you create a custom class or struct, you should override the ToString method in order to provide information about your type to client code.

using System;

namespace ConverString
{
    class Program
    {
        static void Main(string[] args)
        {
            myClass cls = new myClass() { Name = "I am Sample." };

            Console.WriteLine(cls.ToString());
        }
    }
}

class myClass
{
    public string Name { get; set; }

    public override string ToString()
    {
        return "Hi, " + Name;
    }
}

Output:

Hi, I am Sample

https://www.youtube.com/watch?v=hVhxOdaNbdE

Pagination

Pagination is the most important task for developer to develop application.

There is several way we can get result to a query window of sql server.

using @RowNumber and Sub query we can get exact pagination information from the database.

So how to get 11 to 20 rows from the table. you can use as below

Select * from(
select ROW_NUMBER()over(order by FieldId)as RowN, *
from TableName
) as Field
where Field.RowN between 11 and 20;

Now dynamically want to select data from the table using where cause:

To understand paginatin using RowNumber in T-SQL statment

we created two variable to get exact rows.

Declare @Total int = 10, @Page int = 2

Select * from(
select ROW_NUMBER()over(order by FieldId)as RowN, *
from TableName
) as Field
where Field.RowN  >= ((@Page-1)*@Total+1) and Field.RowN <= @Total*@Page;

and using between:

Declare @Total int = 10, @Page int = 2

Select * from(
select ROW_NUMBER()over(order by FieldId)as RowN, *
from TableName
) as Field
where Field.RowN between ((@Page-1)*@Total+1) and @Total*@Page;

To execute procedure

Execute SelectTableRows 20, 2

How to create pagination using stored procedure?

create procedure SelectTableRows
@Total int, 
@Page int
AS
 Select * from(
 select ROW_NUMBER()over(order by FieldId)as RowN, *
 from TableName
 ) as Field
 where Field.RowN between ((@Page-1)*@Total+1) and @Total*@Page;
GO

From SQL Server 2012, we can use OFFSET and FETCH NEXT Clause to achieve the pagination.

very simple way to get pagination using order by with offset

Declare @Total int = 10, @Page int = 2


Select  *
from TableName
order by FieldId
offset ((@Page-1)*@Total) rows fetch next @Total rows only;

Offset is a function.

instead of getting pagination with the help Row_Number() function, offset is quite more fast to get result.

you can also visit:  www.mybook-lang.com

useful short cut key for visual studio

short cut key for visual studio

Ctr + Shift + N is for create new project;

Ctr+ K, Ctr +C is for comment.

Ctr +K, Ctr+G is for uncomment.

F5 key start execute code with debugging

Ctr+F5 start execute code without debugging code.

you can also visit:  www.mybook-lang.com

.Net Framework

Pronounced as: Dot Net Framework

- .Net Framework is revolutionary platform that helps you to write Windows Application, Web Applications and Web Services
- .Net Framework applications are multi platform applications.
- .Net Framework consists of an enormous library of codes used.

List of version introduce by Microsoft

.Net Framework 1.0
.Net Framework 2.0
.Net Framework 3.0
.Net Framework 3.5
.Net Framework 4.0
.Net Framework 4.5

So, List of components is in .Net Framework

Common Language Runtime (CLR)
Class Library
Common Language Specifications (CLS)
Common Type System (CTS)
Metadata
Assemblies
Windows Forms
Asp.net And Asp.net AJAX
Windows Communication Foundation (WCF)
Windows Presentation Foundation (WPF)
Windows Workflow Foundation (WF)
ADO .NET
LINQ
Entity Framework

It is used to perform memory management, debugging, security checking, exception handling, code execution, thread execution, code safety, managed code, code compilation and verification.

The code is managed by the CLR is called as Managed Code. When managed code is compiled by CLR the computer converts the source code of your application into a CPU intermediate language code (IL).

A just in time compiler (JIT) compiles Intermediate Language (IL) code into native code.

.Net Framework consist a huge amount of library of reusable types, classes, structures, interfaces, enumerated values and delegates.

For example: Show the methods, namespaces, action into console application.

you can also visit:  www.mybook-lang.com

attributes

An attributes is a declarative tag that is used to convey information to runtime about the behaviors of elements like classes, methods, structures, enumerators, assemblies, properties etc…

You can add declarative information to a program by using an attributes.

In simple terms attribute is used to tell more about element.

You can declare attributes using “[]” square brackets placed above the element.

Syntax:

[Attribute (parameters)]

Element

Predefined Attributes

.Net framework provides three types of pre-defined attributes.

AttributeUsage(AttributeTarget: to set class, property, fields…)

Conditional

Obsolete (out of date, old, no longer to used or produced)

AttributeUsage

It is used to define how custom attribute can be used. It is specified that type of elements to be allowed which attribute can be applied.

Syntax:

[AttributeUsage(AttributeTargets.All,AllowMultiple=true,Inherited=false)]

Attribute-Targets: this parameter gives a validation for class, property, field, enum, delegates, methods, assembly, and all

So using attribute targets you can use for specific elements only.

Allow-Multiple: This parameter has Boolean value. If it is true then you can use on multiple elements, or false used only on single element.

Inherited: This parameter has Boolean value. If it is true the attributes is inherited by derived class or else the default value is false.

You can also set multiple attribute-targets using “|”.

Syntax:

[AttributeUsage(AttributeTargets.Class| 
    AttributeTargets.Property | 
    AttributeTargets.Method,
    AllowMultiple=true,
    Inherited=false)]

Example:

using System;

class Program
{

    static void Main()
    {
        myClass obj = new myClass();
        obj.Name = "SampleProgram";
    }
}

//this is myclass
class myClass
{
    //has property
    [Help(maxLength=10)]
    public string Name { get; set; }
}

//Create help class inherit to attribute
[AttributeUsage(AttributeTargets.Property,AllowMultiple=true,Inherited=true)]
class Help : Attribute
{
    public int maxLength { get; set; }
}

This predefined attribute marks a conditional method whose execution depends on a specified preprocessing identifier.

Syntax:

[Conditional(
   conditionalSymbol
)]

Example:

[Conditional("DEBUG")]

It is used to set or marked a program entity that it is no longer in used or it is old.

It tells to compiler that discard particular element.

Syntax:

[Obsolete(message="",iserror)]

For example: if you are going to modify project which has list of class in that list of methods is there now if you want to create a new method to do certain things and still you want to retain the old method in the class. you can mark as obsolete to tell compiler to discard.

[Obsolete(message="This method is no longer in used.")]

Example:

using System;

class Program
{
    static void Main()
    {
        myClass obj = new myClass();

        //if you include this statment it gives error
        //it is no longer in used...
        obj.Add("Sample");

        //Success fully run.
        obj.Add(1, "Sample");
    }
}
class myClass
{
    [Obsolete("It is no longer used.",true)]
    public void Add(string name)
    {
        Console.WriteLine("Print: {0}", name);
    }

    public void Add(int id,string name)
    {
        Console.WriteLine("Print: Id: {0} Name:{1}",id, name);
    }
}

Notes:

Attribute is just for declaration tag, it is not a validation.

You can create your custom attribute with the help of Attribute abstract class.

you can also visit:  www.mybook-lang.com