Click here to Skip to main content
Click here to Skip to main content

Creating a Tagging System using ASP.net & implementing it in SQL Server

, 25 Jun 2014
Rate this:
Please Sign up or sign in to vote.
An ASP.net based Tagging System.

Introduction

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.

Background

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

Items
Item_ID Item_Name Content
Tags
Tag_ID Tag_Name
Tags_Relation
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 ASP.net 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="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js" type="text/javascript" charset="utf-8"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/jquery-ui.min.js" type="text/javascript" charset="utf-8"></script>
    <script src="js/tag-it-soniwal.js" type="text/javascript" charset="utf-8"></script>

    

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

    
</head>
<body>
    <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" />
    </form>
</body>

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+"' ");
             
         OUTERCONTINUE: ;
        }
        Response.Write("Success");
    }

    private string BindName()
    {
        DataTable dt = null;
        using (conn = new SqlConnection
                      (ConfigurationManager.ConnectionStrings["mystpcon"].ConnectionString))
        {
            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();
                    da.Fill(dt);
                }
            }
        }

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

            if (i != (dt.Rows.Count - 1))
            {
                output_part.Append(",");
                output.Append(",");
            }
        }
        output.Append("];");

        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.

History

Comment out my mistakes. If any.

-Sumanyu Soniwal

License

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 ASP.net nd C#. I'm in my third year, nd will be graduating by 2015.

Comments and Discussions

 
Question. PinmemberSaurabh Sankrityayan Pandey25-Jun-14 18:49 
QuestionComment PinmemberMember 1090614125-Jun-14 8:44 
GeneralMy vote of 5 PinprofessionalBrian A Stephens25-Jun-14 8:25 
GeneralGreat tutorial PinmemberAnkit Agarwal25-Jun-14 8:12 
QuestionS PinmemberSaumitra Rawat25-Jun-14 5:31 
AnswerRe: S PinmemberSumanyu Soniwal25-Jun-14 5:32 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 25 Jun 2014
Article Copyright 2014 by Sumanyu Soniwal
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid