C# Code Snippet - Check Record Exists in SQL Server Database

C# Code Snippet - Check Record Exists in SQL Server Database

This .Net C# code snippet connects to SQL server and executes SQL statement to determine whether the given record exists in the database. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to execute sql statement and return logical (True/False) result to check record exists or not in the database. Modify the exception handling section for your project requirements.

public bool RecordExists(
        ref System.Data.SqlClient.SqlConnection _SqlConnection,
        string _SQL)
{
    System.Data.SqlClient.SqlDataReader _SqlDataReader = null;

    try
    {
        // Pass the connection to a command object
        System.Data.SqlClient.SqlCommand _SqlCommand
                = new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);

        // get query results
        _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;
    }
}

Here is a simple example showing how to use above function (RecordExists) to login to SQL server and check whether the record exists in the database.

// set temporary variable for database connection
System.Data.SqlClient.SqlConnection _SqlConnection = new System.Data.SqlClient.SqlConnection();

// assign database connection string
_SqlConnection.ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;";

// Connect to database
try
{
    _SqlConnection.Open();
}
catch (Exception _Exception)
{
    // Error occurred while trying to connect to database
    Console.WriteLine(_Exception.Message);
}


// Check for valid open database connection before query database
if (_SqlConnection != null && _SqlConnection.State == ConnectionState.Open)
{
    if (RecordExists(ref _SqlConnection, "SELECT name FROM sampletable WHERE productid = 2"))
    {
        // record found in DB, lets do record found task
        Console.WriteLine("Record exists");
    }
    else
    {
        // record not found in DB, lets do record not found task
        Console.WriteLine("Record not found");
    }

    // close database connection
    //_SqlConnection.Close();
}

C# Keywords Used:

• SqlDataReader
• SqlConnection
• ConnectionString
• SqlCommand
• ExecuteReader
• Exception

Code Snippet Information:

• Applies To: .Net, C#, CLI, SQL, Record Exists, SQL Server, SQL Client, Connection String, Database Connection, SQL Data Reader
• Programming Language : C# (C-Sharp)

External Resources:

• SqlDataReader Class
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• SqlCommand.ExecuteReader Method
• Exception Class

mehul :: March 23-2011 :: 05:15 AM

Great........
thank you so much........

Leave a comment