Unique Key Vs Primary Key

One difference is that a unique key constraint can be created over columns with null values, while a primary key constraint does not allow null values. The other difference is that there can only be one primary key constraint defined on a table, but there can be multiple unique key constraints defined on a table.

Sync Foreign Keys In Sql Server 2005

List all foreigh keys in sql 2005
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName


FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
Order By f.name , OBJECT_NAME(f.parent_object_id),fc.constraint_column_id


Sync foreigh key in sql 2005



BEGIN TRAN
BEGIN TRY

set nocount on

Declare @idoc int
set @Error_Code=0
set @Error_Desc=''

Declare @ConstraintTable Table
(
Sr_No Int Identity(1,1),
ForeignKey Varchar(Max),
TableName Varchar(Max),
ColumnName Varchar(Max),
ReferenceTableName Varchar(Max),
ReferenceColumnName Varchar(Max)
)
exec sp_xml_preparedocument @idoc output , @ConstraintsXML
Insert Into @ConstraintTable
Select ForeignKey,
TableName,
ColumnName,
ReferenceTableName,
ReferenceColumnName
From OpenXML(@idoc,'/NewDataSet/Table',2)
With (
ForeignKey Varchar(Max),
TableName Varchar(Max),
ColumnName Varchar(Max),
ReferenceTableName Varchar(Max),
ReferenceColumnName Varchar(Max)
)
Order By ForeignKey,TableName
exec sp_xml_removedocument @idoc

Declare @ForeignKey Varchar(Max)
Declare @TableName Varchar(Max)
Declare @ColumnName Varchar(Max)
Declare @ReferenceTableName Varchar(Max)
Declare @ReferenceColumnName Varchar(Max)

Declare @CTableName Varchar(Max)
Declare @CColumnName Varchar(Max)
Declare @CReferenceTableName Varchar(Max)
Declare @CReferenceColumnName Varchar(Max)

Declare @SrNo Int
Declare @ForeignKeyString Varchar(Max)
Declare @Rows Int
Declare @CommandStr Varchar(Max)

Set @SrNo = 1
Set @ForeignKeyString = ''
Select @Rows = Count(*) From @ConstraintTable
--Select * From @ConstraintTable

Set @CTableName = ''
Set @CColumnName = ''
Set @CReferenceTableName = ''
Set @CReferenceColumnName = ''

Select * From @ConstraintTable


While @SrNo < = @Rows + 1
Begin
Select @ForeignKey = ForeignKey,
@TableName = TableName,
@ColumnName = ColumnName,
@ReferenceTableName = ReferenceTableName,
@ReferenceColumnName = ReferenceColumnName
From @ConstraintTable
Where Sr_No = @SrNo

--print @ForeignKey
--print @SrNo


IF (@ForeignKeyString = @ForeignKey) And @SrNo <= @Rows
Begin --Same Key Append Column Names
set @CColumnName = @CColumnName + ',' + @ColumnName
set @CReferenceColumnName = @CReferenceColumnName + ',' + @ReferenceColumnName
End --Same Key Append Column Names
Else
Begin --Another Foreigh Key Make new command

If Len(@ForeignKeyString)>0
Begin



Set @CommandStr = 'If Exists( select * FROM sys.foreign_keys Where Name=' + CHAR(39) + @ForeignKeyString + Char(39) +') ALTER TABLE '
Set @CommandStr = @CommandStr + @CTableName + ' DROP CONSTRAINT [' + @ForeignKeyString +']'

--print @CommandStr
exec (@CommandStr)

Set @CommandStr = ' ALTER TABLE ' + @CTableName
Set @CommandStr = @CommandStr + ' WITH NOCHECK ADD CONSTRAINT [' + @ForeignKeyString + ']'
Set @CommandStr = @CommandStr + ' FOREIGN KEY (' + @CColumnName + ') '
Set @CommandStr = @CommandStr + ' REFERENCES ' + @CReferenceTableName + '(' + @CReferenceColumnName + ')'

--print @CommandStr
exec (@CommandStr)

End

Set @ForeignKeyString = @ForeignKey
Set @CTableName = @TableName
Set @CColumnName = @ColumnName
Set @CReferenceTableName = @ReferenceTableName
Set @CReferenceColumnName = @ReferenceColumnName



End --Another Foreigh Key Make new command


Set @SrNo = @SrNo + 1
End



COMMIT TRAN
print 'Sync Successfull !'
END TRY

BEGIN CATCH
ROLLBACK TRAN
Select @Error_Code = cast(ERROR_NUMBER() as varchar(5))
Select @Error_Desc = @CommandStr + char(13) + CAST(ERROR_LINE() AS VARCHAR(4000)) + 'Ö' + CAST(ERROR_MESSAGE() as VARCHAR(4000)) /*+ 'll'+ @query*/
print @Error_Code
print @Error_Desc
END CATCH

ADO.NET Functions for Running Query and Procedures

public DataSet RunQuery(string Query)
{
OpenDB();
SqlCommand sqlCmd = new SqlCommand(Query, sqlConn);
sqlCmd.CommandType = CommandType.Text;

SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlCmd);
DataSet dtSetObject = new DataSet();
dtAdapter.Fill(dtSetObject);

sqlCmd.Dispose();
this.Dispose();

return dtSetObject;
}

public int RunProc(string procName)
{
SqlCommand sqlCmd = CreateCommand(procName, null);
sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
this.Dispose();
return (int)sqlCmd.Parameters["ReturnValue"].Value;
}

public int RunProc(string procName, SqlParameter[] @params)
{
SqlCommand sqlCmd = CreateCommand(procName, @params);
sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
this.Dispose();
return (int)sqlCmd.Parameters["ReturnValue"].Value;
}

private void RunProc(string procName, ref SqlDataReader dataReader)
{
SqlCommand sqlCmd = CreateCommand(procName, null);
dataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
}

public void RunProc(string procName, ref DataSet dataSetObject)
{
SqlCommand sqlCmd = CreateCommand(procName, null);
SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlCmd);
DataSet dtSetObject = new DataSet();
dtAdapter.Fill(dtSetObject);
dataSetObject = dtSetObject;
sqlCmd.Dispose();
this.Dispose();
}

public void RunProc(string procName, SqlParameter[] @params, ref DataSet dataSetObject)
{
SqlCommand sqlCmd = CreateCommand(procName, @params);
SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlCmd);
DataSet dtSetObject = new DataSet();
dtAdapter.Fill(dtSetObject);
dataSetObject = dtSetObject;
sqlCmd.Dispose();
this.Dispose();
}

private void RunProc(string procName, SqlParameter[] @params, ref SqlDataReader dataReader)
{
SqlCommand sqlCmd = CreateCommand(procName, @params);
dataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
}


private SqlCommand CreateCommand( string procName,SqlParameter[] @params )
{
OpenDB();
SqlCommand sqlCmd = null;
sqlCmd = new SqlCommand(procName, sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
if (@params != null)
{
foreach (SqlParameter parameter in @params)
{
sqlCmd.Parameters.Add(parameter);
}
}
sqlCmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return sqlCmd;
}

public SqlParameter MakeInParams(string paramName, SqlDbType paramType, int size, object obj)
{
return MakeParameter(paramName, paramType, size, ParameterDirection.Input, obj);

}

public SqlParameter MakeOutParams(string paramName, SqlDbType paramType, int size)
{
return MakeParameter(paramName, paramType, size, ParameterDirection.Output, null);
}

private SqlParameter MakeParameter(string paramName, SqlDbType paramType, int size, ParameterDirection paramDirection, object obj)
{
SqlParameter sqlParam = null;
if (size > 0)
{
sqlParam = new SqlParameter(paramName, paramType, size);
}
else
{
sqlParam = new SqlParameter(paramName, paramType);
}
sqlParam.Direction = paramDirection;
if (!(sqlParam.Direction == paramDirection & obj == null))
{
sqlParam.Value = obj;
}
return sqlParam;
}

private string getPoolConSting()
{
if (DBConnection.DBPoolConString == null)
{
string DBServer = ConfigurationManager.AppSettings.Get("DBServer");
string DBCredential = ConfigurationManager.AppSettings.Get("DBCredential");
DBCredential = Util.DecryptToString(DBCredential);
string PoolOn = ConfigurationManager.AppSettings.Get("PoolOn");
DBConnection.DBPoolConString = DBServer + DBCredential + PoolOn;
}
return DBConnection.DBPoolConString;
}

private string getnonPoolConString()
{
if (DBConnection.DBNonPoolConString == null)
{
string DBServer = ConfigurationManager.AppSettings.Get("DBServer");
string DBCredential = ConfigurationManager.AppSettings.Get("DBCredential");
DBCredential = Util.DecryptToString(DBCredential);
string PoolOff = ConfigurationManager.AppSettings.Get("PoolOff");
DBConnection.DBNonPoolConString = DBServer + DBCredential + PoolOff;
}
return DBConnection.DBNonPoolConString;
}

private void OpenDB()
{
if (sqlConn == null)
{
try
{
sqlConn = new SqlConnection(getPoolConSting());
sqlConn.Open();
}
catch (System.Data.SqlClient.SqlException)
{
if (sqlConn.State != ConnectionState.Closed)
{
sqlConn.Close();
sqlConn = new SqlConnection(getnonPoolConString());
sqlConn.Open();
}
}
}
}

public void Dispose()
{
if (!(System.Convert.IsDBNull(sqlConn)))
{
sqlConn.Dispose();
sqlConn = null;
}
}

public void Close()
{
if (!(System.Convert.IsDBNull(sqlConn)))
{
sqlConn.Close();
}
}

Get Query String In JavaScript With Split

< script type= "text/javascript" >

function querySt(qsFind)
{
qs = window.location.search.substring(1);

str = qs.split("&"); //Split Query String

for (i=0 ; i < str.length ; i++)
{

cs = str[i].split("="); /Split Individual Query String with =
if (cs[0] == qsFind) //Check for particular query string and if found return value
{
return cs[1];
}
}
}


< / script >

var Ledger_Id = querySt("Ledger_Id");