Monday, October 25, 2010

Check Record Exists in SQL Server Database [C#.NET] via Checksum field

Main Function:

public void Add(Dictionary fields, string ConnString)
{
if(_SqlConnection != null && _SqlConnection.State == ConnectionState.Open)
{
foreach (KeyValuePair _item in fields)
{
_columnn += (_columnn.Trim().Length != 0 ? "," : "") + _item.Key ;
_values += (_values.Trim().Length !=0 ? ",'" :"'" ) + _item.Value + "'";

if(_columnn == "ID")
{
_chkSum = ComputeCheckSum(_values);
}
}

if (RecordExists(ref _SqlConnection, "SELECT CheckSumValue
FROM dbo.CER_PubMed
WHERE CheckSumValue = " + "'" + _chkSum + "'"))
{
// record found in DB, lets do record found task
//UPDATE
//String _sqlUpdate = "UPDATE dbo.CER_PubMed
SET CheckSumValue = 27
WHERE CheckSumValue =" + _chkSum;


Console.WriteLine("Record exists");
}
else
{
// record not found in DB, lets do record not found task
_columnn += ",CheckSumValue";
_values += ",'" + _chkSum + "'" ;

//INSERT
_sql = string.Format(_sql, _columnn, _values);
SqlCommand _command = new SqlCommand(_sql, _SqlConnection);
_command.ExecuteNonQuery();

Console.WriteLine("Record not found");
}
_SqlConnection.Close();
}
}

*************************************************************************************

Validate Function:

public bool RecordExists( ref System.Data.SqlClient.SqlConnection _SqlConnection, string _SQL)
{
SqlDataReader _SqlDataReader = null;
try
{
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
_SqlDataReader = _SqlCommand.ExecuteReader();
}

catch (Exception _Exception)
{
// Error occurred while trying to execute reader
// send error message to console (change below line to customize error handling)
Console.WriteLine(_Exception.Message);
return false;
}

if (_SqlDataReader != null && _SqlDataReader.Read())
{
// close sql reader before exit
if (_SqlDataReader != null)
{
_SqlDataReader.Close();
_SqlDataReader.Dispose();
}
// record found
return true;
}
else
{
// close sql reader before exit
if (_SqlDataReader != null)
{
_SqlDataReader.Close();
_SqlDataReader.Dispose();
}

// record not found
return false;
}
}

************************************************************************************

Compute CheckSum:

public string ComputeCheckSum(string chkFieldStr)
{
string _chkSum = string .Empty ;
char[] char1a = null;
Byte[] byte1a = null;
byte[] hash1 = null;

if (!string.IsNullOrEmpty(chkFieldStr))
{
char1a = chkFieldStr.ToCharArray();
byte1a = new byte[char1a.Length];

for (int i = 0; i <>
{
byte1a[i] = (Byte)char1a[i];
}

hash1 = ((HashAlgorithm)CryptoConfig.CreateFromName("SHA1")).ComputeHash(byte1a);
return _chkSum = BitConverter.ToString(hash1) ;
}
return _chkSum ;
}

*************************************************************************************

1 comment:

  1. Here is a database compatible with .NET, Silverlight, Windows Phone, Mono, Monodroid, and Monotouch:
    http://www.kellermansoftware.com/p-43-ninja-net-database-pro.aspx

    ReplyDelete