How to get data from the Database using Stored Procedures

OK, so here is some basic code which shows you how to retrieve data from the database by using a Stored Procedure. The method calls a stored procedure from the database and execute it. After execution, it will read the query result set of the stored procedure and fill in the properties of our object.

CREATE PROCEDURE [dbo].[usp_GetContentFromDB]
    @Param int
AS
BEGIN
    SELECT Column1, Column2, Column3 FROM table
    WHERE Column1 = @Param
END
GO
public Object GetObjectDataFromDB(String value)
{
	Object object = new Object();
	DbConnection connection = null;
	try
    {
		connection = database.CreateConnection();
		connection.Open();

		DbCommand command = database.GetStoredProcCommand("usp_GetContentFromDB");
		command.Connection = connection;
		database.AddInParameter(command, "@Param", DbType.Int32, value);
		reader = command.ExecuteReader();

		while (reader.Read())
        {
			object.Property1 = Convert.ToString(reader["Column1"]);
			object.Property2 = Convert.ToString(reader["Column2"]);
			object.Property3 = Convert.ToString(reader["Column3"]);
        }
	}
	catch (Exception ex)
	{
		throw ex;
	}
	finally
	{
		if (connection != null)
		{
			connection.Close();
		}
	}
	return object;
}

You can do exactly the same to insert, update or delete data but you’ll have to use the ExecuteNonQuery method instead of ExecuteReader.

DbCommand command = database.GetStoredProcCommand("usp_InsertData");
command.Connection = connection;
database.AddInParameter(command, "@Param", DbType.Int32, value);
command.ExecuteNonQuery();
Advertisements

Comments are closed.

%d bloggers like this: