Click here to Skip to main content
15,113,153 members
Articles / Web Development / HTML
Posted 25 Jun 2014


11 bookmarked

Creating a Tagging System using & implementing it in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.30/5 (8 votes)
25 Jun 2014CPOL2 min read
An based Tagging System.


This tutorial is used to create Tagging System using ASP.NET, C# and SQL server. This will take input tags from a textbox(values delimited my commas). Each value will be inserted into the database, if it is initially not in the database. Suggestion (using jQuery AutoComplete) will pre provided simultaneously by fetching data from the database.


We will create three tables for the implementation of this tagging system. The tables will be as follows:

Item_ID Item_Name Content
Tag_ID Tag_Name
Tag_ID Item_ID

The Queries to implement this model via SQL Server are:

create table items(tag_id int IDENTITY(1000,1) NOT NULL primary key, item_name varchar(100)  NOT NULL, content varchar(1000))

create table tags(tag_id int IDENTITY(500,1) NOT NULL primary key, tag_name varchar(100)  NOT NULL)

create table tags_relation(tag_id int  CONSTRAINT fk_tag_id FOREIGN KEY REFERENCES tags(tag_id),item_id int CONSTRAINT fk_item_id FOREIGN KEY REFERENCES items(item_id))

A procedure for inserting tags, used below.

create proc tag_insert(@tag_name varchar(100)) as insert into tags(tag_name) values (@tag_name);

Using the code

Now I will show you the Markup of the tagging system. The CSS and Javscript are provided with article. For visual theming of this tagging I have used this. A big thanks to Levy Carneiro Jr. (Creator of Tag-it).The ASP.NET Markup is as follows:

<head runat="server">
    <link href="css/jquery.tagit.css" rel="stylesheet" type="text/css">
    <link href="css/tagit.ui-sumanyu.css" rel="stylesheet" type="text/css">
    <link href="" rel="stylesheet" type="text/css"/>
    <script src="" type="text/javascript" charset="utf-8"></script>
    <script src="" type="text/javascript" charset="utf-8"></script>
    <script src="js/tag-it-soniwal.js" type="text/javascript" charset="utf-8"></script>


        $(function () {
            var sampleTags = <%=listFilter %> /*listFilter is a variable that will be updated via C#. to provide tags for Autocomplete, fetching it from database.*/
                availableTags: sampleTags

    <form id="form1" runat="server">
      <!--Textbox that will take input tags from users -->
        <asp:TextBox name="tags" id="singleFieldTags2" value="Sumanyu,Soniwal" class="autosuggest" runat="server"></asp:TextBox>

        <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />

Now I will show you the business logic which I created:

  • Button1_Click: It will enter all the "new" tags in the Database.
  • BindName(): It will fetch all the tags in the database. Compile them into a single string(comma delimited). and provide it to listFilter (making it available for jQuery enabled Autocomplete Textbox)

The backend C# code is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Web.Services;
using System.Data;
using System.Text;
using System.Configuration;

public partial class tag : System.Web.UI.Page
    BusinessLayer ob = new BusinessLayer();
    SqlConnection conn;
    public string listFilter = null;
    public string listFilter_for_parts = null;
    StringBuilder output_part = new StringBuilder();
    protected void Page_Load(object sender, EventArgs e)
        listFilter = BindName();
        listFilter_for_parts = output_part.ToString();      

    protected void Button1_Click(object sender, EventArgs e)
        string input_tags = singleFieldTags2.Text;
        string[] tags_input_parts = input_tags.Split(',');

        string[] database_tags = listFilter_for_parts.Split(',');

        foreach (string input in tags_input_parts)
            foreach (string database in database_tags)
                if (input == database)
                    goto OUTERCONTINUE; 
            ob.ExecuteScalar("exec tag_insert @tag_name='"+input+"' ");

    private string BindName()
        DataTable dt = null;
        using (conn = new SqlConnection
            using (SqlCommand cmd = conn.CreateCommand())
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select tag_name from tags";
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    dt = new DataTable();

        StringBuilder output = new StringBuilder();
        for (int i = 0; i < dt.Rows.Count; ++i)
            output.Append("\"" + dt.Rows[i]["tag_name"].ToString() + "\"");

            if (i != (dt.Rows.Count - 1))

        return output.ToString();

Note: Business Layer is a separate layer created by me. By which I can directly use OpenConn (for opening connection), CloseConn (for closing connection), ExecuteScalar, ExecuteReader,nd ExecuteNonQuery. It's just for my ease. So instead of "ob.ExecuteScalar()". You will have to use the whole Execute Scalar Method(Opening connection, creating command, executing command, etc.).

Points of Interest

StringBuilder is used instead of String.

Reason: StringBuilder is faster in this case, because we have to append so many things to the same string. Using String instead of StringBuilder would have created too many strings. Thus would have wasted memory nd time.


Comment out my mistakes. If any.

-Sumanyu Soniwal


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Sumanyu Soniwal
Student & Microsoft Technical Associate
India India
I am a student of Inderprastha Engineering College, Ghaziabad. Majoring in Computer Science.
I love developing websites, based on nd C#. I'm in my third year, nd will be graduating by 2015.

Comments and Discussions

Questionwrong creation of items table Pin
Member 1290339130-Dec-17 22:16
MemberMember 1290339130-Dec-17 22:16 
QuestionSource Code Pin
Samuel Koroh29-Nov-17 8:47
MemberSamuel Koroh29-Nov-17 8:47 
QuestionThe Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>). Pin
Victor Tlepshev3-Aug-15 6:18
MemberVictor Tlepshev3-Aug-15 6:18 
AnswerRe: The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>). Pin
Victor Tlepshev3-Aug-15 6:25
MemberVictor Tlepshev3-Aug-15 6:25 
Questionbusiness layer Pin
Abhishek Ray19-Mar-15 0:34
MemberAbhishek Ray19-Mar-15 0:34 
Question. Pin
Saurabh Sankrityayan Pandey25-Jun-14 19:49
MemberSaurabh Sankrityayan Pandey25-Jun-14 19:49 
QuestionComment Pin
Member 1090614125-Jun-14 9:44
MemberMember 1090614125-Jun-14 9:44 
GeneralMy vote of 5 Pin
Brian A Stephens25-Jun-14 9:25
professionalBrian A Stephens25-Jun-14 9:25 
GeneralGreat tutorial Pin
Ankit Agarwal25-Jun-14 9:12
MemberAnkit Agarwal25-Jun-14 9:12 
QuestionS Pin
Saumitra Rawat25-Jun-14 6:31
MemberSaumitra Rawat25-Jun-14 6:31 
AnswerRe: S Pin
Sumanyu Soniwal25-Jun-14 6:32
MemberSumanyu Soniwal25-Jun-14 6:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.