Using Microsoft SQL 2012 Sequence – Auto Increment ID

Using Microsoft SQL 2012 Sequence – Auto Increment ID

One of the new feature available in the SQL 2012 is Sequence .We can use this feature for various type of matters.We can use this to generate ID or serial numbers (Eg;Company ID,Customer ID..etc). One of advantage is we can have auto increment column which is varchar type.

We can find this new feature under programmability section.

 

I’m going to explain how to create company ID sequence.

1.       Creating Sequences

Execute the above query .In my sequence I’m starting with 10000 and increment by 1.

2.       Create Table associated with sequence.

CREATE TABLE dbo.Company

(

CompanyID varchar(10) NOT NULL CONSTRAINT DEF_CompanyID DEFAULT 'CM' + CAST((NEXT VALUE FOR dbo.seq_Company) AS varchar),

       CAddress varchar(30),

       Trade varchar(30) ,

      

       CONSTRAINT PK_Company PRIMARY KEY CLUSTERED (CompanyID)

)

GO

Then you can insert some values to table. Company ID will be auto increment column even though it’s type varchar.

Once you pass through above two steps you will be able to get auto increment company ID as below.

Optionally we can restart the sequence by using below query.

Alter SEQUENCE dbo.seq_Company

       RESTART WITH 10000

       GO

We can get current value of sequence using below query

SELECT Current_Value

FROM SYS.Sequences

WHERE name='seq_Company'

            GO

Web Service to Authenticate Users in the Active Directory

 Very recently I was looking for solution to consume active directory authentication for applications like mobile apps,asp.net applications...etc.In this solution web service gets username and password of the active directory user and provide Boolean value whether this user is valid or invalid.Ok let's start building this web service.

1.Create web service application and add custom service to the project.In my case service name ADAuthentication.asmx .Then add reference to the project(System.DirectoryServices)

2.Create Web Method to authenticate users in the  active directory.In my case I'm named web method called ActiveDirectoryAuthentication which gets username and password as parameters.My method will be like this

.
[WebMethod]
        public bool ActiveDirectoryAuthentication(string userName, string password)
        {
                        bool IsUser = false;
            string domain = "Insert your domain here";
            try
            {
                DirectoryEntry entry = new DirectoryEntry("LDAP://" + domain,
                    userName, password);
                object nativeObject = entry.NativeObject;
                IsUser= true;
            }
            catch (DirectoryServicesCOMException) { }
            return IsUser;
        }

According to my web method this return boolean value whether this user valid or not.

3.Deploy web service to your local host.This will show something like this.

4.Once we invoke web service by giving username and password.It will provide xml which contain boolean value(true or false)

Thanks....

Custom web part for search list items and display in custom view

SharePoint 2010 web parts…..

Custom web part for Search list items and display in custom view

 

In here I’ll show you how to view list items, that are compatible with user input data. I’m using custom web part to implement this project.

As the example I’ll use “Task” list and retrieve data according to column “Status”.

Eg: If user input “Completed”, then he will see the list items with status “completed”.

Step 1: Create new visual web part project in Visual Studio 2010.

Step 2: Add new textbox and button to project.

Step 3: Insert below code into button click event.

                     ListViewByQuery mycustomview;

           

            SPWeb thisWeb = null;

           

            thisWeb = SPContext.Current.Web;

           

            mycustomview = new ListViewByQuery();

            mycustomview.List = thisWeb.Lists["Tasks"];

            SPQuery query = new SPQuery(mycustomview.List.DefaultView);

                     

            query.Query = "<Where><Eq><FieldRef Name='Status'/><Value           Type='Text'>"+TextBox1.Text+"</Value></Eq></Where>";

            query.ViewFields = "<FieldRef Name=\"Title\"/><FieldRef Name=\"Status\"/>";

            mycustomview.Query = query;          

            this.Controls.Add(mycustomview);

               

 Step 4: Deploy the solution.

Step 5: Add your new custom web part to SharePoint new page.

Retrieve tasks with status "Completed"

Output will be like this.