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_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">
-->
<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
