Sql Query - "0" for Inactive "1' for Active

select *, case status when '1' then 'Active' when '0' then 'In-Active' end as Status1 from tblArticleMasterDestination order by Destname

Dal Class-lIbrary

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
public sealed class DAL
{
private DAL() { }
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}

private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
}
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}

for (int i = 0, j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}
}
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
cmd.CommandTimeout = 120;
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
}
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
cmd.CommandTimeout = 120;
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
public static DataTable ExecuteDatatable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteDatatable(cn, commandType, commandText, commandParameters);
}
}
public static DataTable ExecuteDatatable(string connectionString, string spName, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDatatable(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDatatable(connectionString, CommandType.StoredProcedure, spName);
}
}
public static DataTable ExecuteDatatable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteScalar(cn, commandType, commandText, commandParameters);
}
}
public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
cmd.CommandTimeout = 120;
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return retval;
}
}
public sealed class SqlHelperParameterCache
{
private SqlHelperParameterCache() { }
private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(spName, cn))
{
cn.Open();
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);

if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}

SqlParameter[] discoveredParameters = new
SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
return discoveredParameters;
}
}
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (SqlParameter)
((ICloneable)originalParameters[i]).Clone();
}
return clonedParameters;
}
public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
{
string hashKey = connectionString + ":" + commandText;
paramCache[hashKey] = commandParameters;
}
public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
string hashKey = connectionString + ":" + commandText;
SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];
if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
SqlParameter[] cachedParameters;
cachedParameters = (SqlParameter[])paramCache[hashKey];
if (cachedParameters == null)
{
cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));
}
return CloneParameters(cachedParameters);
}
}

Active/Inactive

In Gridview Define templatefield inside column

<asp:GridView ID="gvContent" runat="server" CssClass="body-text-black" Width="750px" AllowPaging="True" AutoGenerateColumns="False" HeaderStyle-CssClass="gridhead" CellPadding="5" RowStyle-CssClass="gridrow1" AlternatingRowStyle-CssClass="gridrow2" BorderWidth="0px" GridLines="None" HeaderStyle-VerticalAlign="top" HorizontalAlign="Left" HeaderStyle-HorizontalAlign="left" RowStyle-VerticalAlign="top" onpageindexchanging="gvContent_PageIndexChanging" onrowcommand="gvContent_RowCommand" PageSize=30 onrowdatabound="gvContent_RowDataBound">
 <Columns>

<asp:TemplateField HeaderText="S.No.">
 <ItemTemplate>
<%# Container.DataItemIndex + 1 %> </ItemTemplate>
 </asp:TemplateField>

<asp:templatefield headertext="Active/InActive">
 <itemtemplate>
<asp:button causesvalidation="false" commandargument="<%#Eval("DestID")%>" commandname="Status" cssclass="btn" id="btnStatus" onclientclick="return
ConfirmStatus()" runat="server" text="<%#Eval("Status1")%>"> </asp:button>
</itemtemplate>

</asp:templatefield>
</Columns>

</asp:GridView>
I have used command name and command arguement and it will handle inside rowcommand event.

Row command Code of Cs page

protected void gvContent_RowCommand(object sender, GridViewCommandEventArgs e)
{
 if (e.CommandName == "Status")
{
 int index = Convert.ToInt32(e.CommandArgument.ToString());
 try
{
 int iRow = DAL.ExecuteNonQuery(ConnectionString, "prcDesttemp", "4", index, "","", "", "", "","", "","","","","", "1", "0" ,0, 0 ,0,"0"); Fillgridview(); lblMsg.Text = "Status Changed Successfully.";
 }
 catch (Exception ex)
 { }
 }
 }
I have used pocedure to do this task -which is easy to maintain.And used a Dal Class for this where i am passing connection string , procedure name, action Id and parameter.


CREATE procedure [dbo].[prcDesttemp]
(
@ActionId tinyint,
@DestID int,
@DestName nvarchar(100),
@DestiShortDesc nvarchar (200),
@DestiLongDesc text,
@PlaceDesc text,
@WeatherDesc text,
@HistoryDesc text,
@VisitDesc text,
@SmallImage nvarchar(200),
@LongImage nvarchar(200),
@MetaTitle text,
@MetaDesc text, @Status tinyint ,
@Result tinyint output,
@RegionID int,
@CountryID int,
@CityID int ,
@Displayonhome tinyint
)
--sp_help tblArticleMasterClient
as
begin
set nocount on
if @ActionId=1 --Add Destination
begin
insert into tblArticleMasterDestination (DestName,DestiShortDesc,DestiLongDesc,PlaceDesc,WeatherDesc, HistoryDesc, VisitDesc,SmallImage,LongImage,MetaTitle,MetaDesc,Status,CreateDate , RegionID , CountryID , CityID,Displayonhome ) values (@DestName, @DestiShortDesc, @DestiLongDesc,@PlaceDesc, @WeatherDesc, @HistoryDesc, @VisitDesc, @SmallImage , @LongImage , @MetaTitle, @MetaDesc, '1', getdate() , @RegionID , @CountryID , @CityID,@Displayonhome)
end
if @ActionId=2 --Update Destination
begin
update tblArticleMasterDestination set
DestName = @DestName,
DestiShortDesc = @DestiShortDesc ,
DestiLongDesc = @DestiLongDesc ,
PlaceDesc = @PlaceDesc ,
WeatherDesc=@WeatherDesc,
HistoryDesc=@HistoryDesc,
VisitDesc=@VisitDesc,
SmallImage=@SmallImage,
LongImage=@LongImage,
MetaTitle=@MetaTitle,
MetaDesc=@MetaDesc,
UpdateDate = getdate()
, RegionID = @RegionID ,
CountryID = @CountryID ,
CityID = @CityID
where DestID = @DestID
end
if @ActionId=3 --Select records
begin
select *, case status when '1' then 'Active' when '0' then 'In-Active' end as Status1 from tblArticleMasterDestination order by Destname end
if @ActionId=4 --Change Status
begin
select @Status=Status from tblArticleMasterDestination where DestID=@DestID
if @Status=0 --InActive
begin
Update tblArticleMasterDestination set Status=1 where DestID=@DestID end
else --Active
begin
update tblArticleMasterDestination set Status=0 where DestID=@DestID end
end end --set nocount off end

Get Dal class And copy it and put this in app_code library

Database Normalization Basics

Normalization or data normalization is a process to organize the data into tabular format (database tables). A good database design includes the normalization, without normalization a database system may slow, inefficient and might not produce the expected result. Normalization reduces the data redundancy and inconsistent data dependency.

Normal Forms

We organize the data into database tables by using normal forms rules or conditions. Normal forms help us to make a good database design. Generally we organize the data up to third normal form. We rarely use the fourth and fifth normal form.
To understand normal forms consider the folowing unnormalized database table. Now we will normalize the data of below table using normal forms.

1. First Normal Form (1NF)

   A database table is said to be in 1NF if it contains no repeating fields/columns. The process of converting the UNF table into 1NF is as follows:
   
   1. Separate the repeating fields into new database tables along with the key from unnormalized database table.
   2. The primary key of new database tables may be a composite key
   1NF of above UNF table is as follows:
   

2. Second Normal Form (2NF)

   A database table is said to be in 2NF if it is in 1NF and contains only those fields/columns that are functionally dependent(means the value of field is determined by the value of another field(s)) on the primary key. In 2NF we remove the partial dependencies of any non-key field.
   The process of converting the database table into 2NF is as follows:
   1. Remove the partial dependencies(A type of functional dependency where a field is only functionally dependent on the part of primary key) of any non-key field.
   2. If field B depends on field A and vice versa. Also for a given value of B, we have only one possible value of A and vice versa, Then we put the field B in to new database table where B will be primary key and also marked as foreign key in parent table.
   2NF of above 1NF tables is as follows:
   

3. Third Normal Form (3NF)

   A database table is said to be in 3NF if it is in 2NF and all non keys fields should be dependent on primary key or We can also said a table to be in 3NF if it is in 2NF and no fields of the table is transitively functionally dependent on the primary key.The process of converting the table into 3NF is as follows:
   
   1. Remove the transitive dependecies(A type of functional dependency where a field is functionally dependent on the Field that is not the primary key.Hence its value is determined, indirectly by the primary key )
   2. Make separate table for transitive dependent Field.
   3NF of above 2NF tables is as follows:
   

4. Boyce Code Normal Form (BCNF)

   A database table is said to be in BCNF if it is in 3NF and contains each and every determinant as a candidate key.The process of converting the table into BCNF is as follows:
   
   1. Remove the non trival functional dependency.
   2. Make separate table for the determinants.
   BCNF of below table is as follows:
   

5. Fourth Normal Form (4NF)

   A database table is said to be in 4NF if it is in BCNF and primary key has one-to-one relationship to all non keys fields or We can also said a table to be in 4NF if it is in BCNF and contains no multi-valued dependencies.The process of converting the table into 4NF is as follows:
   
   1. Remove the multivalued dependency.
   2. Make separate table for multivalued Fields.
   4NF of below table is as follows:
   

6. Fifth Normal Form (5NF)

   A database table is said to be in 5NF if it is in 4NF and contains no redundant values or We can also said a table to be in 5NF if it is in 4NF and contains no join dependencies.The process of converting the table into 5NF is as follows:
   
   1. Remove the join dependency.
   2. Break the database table into smaller and smaller tables to remove all data redundancy.
   5NF of below table is as follows:
   

Summary

In this article I try to explain the Normalization with example. I hope after reading this article you will be able to understand Normal Forms. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

Introduction to SVN

SVN is an open source version control system that is broadly used by many organizations for its project history. It is free to use and manage source code different versions. You can find below two types of svn.

Ankh SVN is a open source subversion Source control for Visual Studio for managing your's code different versions like VSS and TFS.
Tortoise SVN is also a open source subversion Source control for managing your's source code versions. Your source code could be in any language and could support window or unix or apache platform
It has the following listed features:-

1. SVN tracks the structure of all folders present in your project.
2. It assigns a global revision number to your source code repository that is used to manage your source code history.
3. SVN commits and updates are atomic.
4. SVN maintains the revision history of moved or copied files.
5. SVN can be installed on Window, Unix and Apache platform.
6. SVN Client and SVN Server are used to manage source code versions. SVN Server version is installed on Server and SVN Client version is installed on Client side.

You can go to this link for installing and managing Ankh SVN on your computer.
You can go to this link for installing and managing Tortoise SVN on your computer.