Comparing two database tables for equality

Binary_Checksum can be used to compare two tables for equality.
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) from dbo.[Table1]
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) from dbo.[Table2]

But there are some concerns about the algorithm that it might not always be unique

Example:

select binary_checksum('A') ,binary_checksum('AAAAAAAAAAAAAAAAA')

The out put is 65 and 65

select binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A') ,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAA')

The out put is 577 and 577.

So the other way to compare two tables cell by cell would be to write a query like this


SELECT COUNT(*) as cnt, MIN(ViewName) as TableName, col1, col2, ...
FROM
(
SELECT '[DataBase1].dbo.[TableName or ViewName goes here]' as ViewName,
*
FROM [DataBase1].dbo.[TableName or ViewName goes here] A
UNION ALL
SELECT '[DataBase2].dbo.[TableName or ViewName goes here]' as ViewName,
*
FROM [DataBase2].dbo.[TableName or ViewName goes here] B
) tmp
GROUP BY ALL col1, col2, ...
HAVING COUNT(*)%2 <> 0


UNION concatenates two tables
GROUP BY groups the records by comparing cell to cell of all the columns listed and merges all the matching rows as a single row with COUNT(*) displaying the number of matching rows that have been merged.

If there is a row in View1/Table1 that matches the row in View2/Table2, then COUNT returns an even number (after the entire cell by cell comparison is performed by group by).

If there are any rows in View1/Table1 and View2/Table2 that do match each other, then after the merge performed by GROUP BY, COUNT(*) returns an odd number.

COUNT(*) is an even number when all the data cells in view1 match to view2 and when divided by 2 the reminder is 0. If any rows do not match exactly cell by cell, COUNT(*) is an odd number and when divided by 2 has a reminder.

Hence

HAVING COUNT(*)%2 <> 0
Returns all the mismatched rows. The query does not return any results if View1 and View2 match.

To get the list of columns if you are comparing views run this script:

DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + c.name FROM sys.columns c, sys.views v
WHERE c.object_id = v.object_id
AND v.name = 'View Name here'
Print @Names










The out put is 577 and 577.

Caching using SqlCacheDependency class with command notification

Caching is very simple when caching a table

SqlCacheDependency dep = new SqlCacheDependency("Connection String", "dbo.tablename")

but when caching a sql command I had to struggle and it just did not work until I read this article and changed my query to not have any of these mentioned in the below article. The reason to cache a sql command instead of table was that I had to join more than two tables.

The reasons why cache was not working for me was I was doing two things that I am not supposed to do for the notifications to work.

1. My table names did not have two-part names like in dbo.tablename
It also means that all tables referenced in the statement must be in the same database.

2. I was using 'DISTINCT'. One of the many keywords Query notification does not support.

For full list of requirements for the Query notification to work, read the following article.

http://msdn.microsoft.com/en-us/library/aewzkxxh%28VS.80%29.aspx

Same rules apply if you want to Cache data from a stored procedure. The reason I like to use SqlCacheDependency class is because it uses the notification mechanism instead of polling.

object o = HttpContext.Current.Cache["MITES"];
if (o == null)
{
using (SqlConnection conn = new SqlConnection(GetConnectionString.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand ("GetMites",conn))
{
cmd.CommandType = CommandType.StoredProcedure;
dtMites = new DataTable("dtMites");
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
SqlCacheDependency dep = new SqlCacheDependency(cmd);
da.Fill(dtMites);

// This is just test code to make sure cache is really working
/* DataRow row = dtMites.NewRow();
row[0] = "Retreived from database";
dtMites.Rows.Add(row);
*/
// The data returned has duplicates. dtMites.DefaultView.ToTable(true, "Table1")
// retrieves distinct rows from the datatable
HttpContext.Current.Cache.Add("MITES", dtMites.DefaultView.ToTable(true, "Table1"), dep,
Cache.NoAbsoluteExpiration, TimeSpan.FromHours(1),
CacheItemPriority.Default, null);
return (DataTable)HttpContext.Current.Cache["MITES"];
}
}
}
}
else
{
// This is just test code to make sure cache is really working
/* dtMites = (DataTable)o;
DataRow row = dtMites.NewRow();
row[0] = "Retreived from Cache";
dtMites.Rows.Add(row);
return dtMites;
*/
return (DataTable)o;
}

You need to add some tags to the web.config file for this to work

<system.web>
<caching>
<sqlcachedependency enabled = "true" pollTime = "60000" >
<databases>
<add name="Connection String"
connectionStringName="Connection String"
pollTime="9000000"
/>
</databases>
</sqlCacheDependency>
</caching>


You will also need to add this to Application_Start method in Global.asax.cs file

protected void Application_Start(object sender, EventArgs e)
{
System.Data.SqlClient.SqlDependency.Start(System.Configuration.ConfigurationManager.ConnectionStrings["Connection String"].ConnectionString);
// Code that runs on application startup
}

on the database server run this command
Aspnet_regsql -S ServerName -U username -P Password -d DatabaseName -ed

Using Sql management studio run this script on the database

Run the script

DECLARE @SQL nvarchar(max)
BEGIN TRY
SET @SQL = 'ALTER DATABASE' + DatBaseName + 'SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE '
PRINT @SQL
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
SET @SQL = 'ALTER DATABASE' + DatBaseName + 'SET NEW_BROKER WITH ROLLBACK IMMEDIATE '
PRINT @SQL
EXEC sp_executesql @SQL
END CATCH
GO


If the script is taking too long to run, restart the Database server instance by opening Control Panel > Administrative Tools > Services >

Restrict special characters from being entered into the textbox using Javascript

If the input from the textbox is going to be used as a filter criteria to retrieve records from the database, your application will throw exceptions as sql does not like special characters like '%', ', " in 'Where' and 'Like' clauses. You could write some javascript to restrict users from entering these special characters

function check(e) {
var keynum
var keychar
var numcheck
// For Internet Explorer
if (window.event) {
keynum = e.keyCode
}
// For Netscape/Firefox/Opera
else if (e.which) {
keynum = e.which
}
keychar = String.fromCharCode(keynum)
//List of special characters you want to restrict
if (keychar == "'" || keychar == "`" || keychar == "%" || keychar == "\"" {
return false;
}
else {
return true;
}
}

Here is the asp.net tag

<asp:TextBox ID="txtName" runat="server" onkeypress="return check(event)"></asp:TextBox>

How to pass an array to the stored procedure

If you have a situation where you have to pass multiple selected values from the listbox to a stored procedure and these multiple selected values should be used in a filter criteria "IN", you could do it using dynamic sql. Concatenate the list of selected values with comas. If the search criteria are strings, concatenate quotes to the strings and pass this string to the storedprocedure with dynamic sql in it.

string _selList = "";
int[] indSel = DDL.GetSelectedIndices();
foreach (int i in indSel)
{
_selList += "'" + DDLMfgSite.Items[i].Text + "',";
}
if (_selList.LastIndexOf(",") > 0)
_selList = _selList.Substring(0, _selList.LastIndexOf(","));

Populating a Texbox with ListBox multiple selected items using Javascript

function ddlChange() {
var ddl = document.getElementById('<%=DDL.ClientID%>');
var textBox = document.getElementById('<%=txtSelected.ClientID%>');
len = ddl.length;
textBox.value = "";
for (var j = 0; j < len; j++) {
if (ddl.options[j].selected) {
textBox.value += ddl.options[j].text + "\r\n";
}
}
}


<div>Available Items:</div>
<div>
<asp:ListBox ID="DDL" runat="server" SelectionMode="Multiple" onChange="ddlChange()" Height="170px">
</asp:ListBox>
</div>


<div>Selected Items:</div>
<div>
<asp:TextBox ID="txtSelected" runat="server" TextMode="MultiLine"
ReadOnly="true" Height="170px" Width="365px"></asp:TextBox>
</div>

Setting Textbox to the dropdownlist's selected item using javascript

<script type="text/javascript"/>
function ddlChange() {
var ddl = document.getElementById('<%=DDL.ClientID%>');
var textBox = document.getElementById('<%=txtSelected.ClientID%>');
textBox.value = ddl.options[ddl.selectedIndex].text;
}
</script>

<asp:DropDownList ID="DDL" runat="server" DataTextField="Site" DataValueField="Site" onChange="ddlChange()">
</asp:DropDownList>
<asp:TextBox ID="txtSelected" runat="server" Width="300px"></asp:TextBox>

Setting ListBox scroll position at the Selected Items

If you have a listbox inside your user control and if all of these conditions below apply:
1. Your listbox is inside a user control
2. The user control becomes visible based on other selections on the webform you made.
3. The listbox is bound to the data in the Page_Init and the default SelectedValue is set in the code behind.

In this scenario, when the user control becomes visible, the listbox scroll position is set at the first item in the listbox instead of at the SelectedValue.

If you want the listbox scroll position at the SelectedValue, you have to write javascript.

Write this javascript in your user control:

window.onload = function () {
var ddl = document.getElementById('<%=DDL.ClientID%>');
len = ddl.length;
for (var j = 0; j < len; j++) {
if (ddl.options[j].selected) {
ddl.options[j].selected = false;
ddl.options[j].selected = true;
}
}
}

What you do here is just unselect and reselect the selected items.