An Example of Using DAAB

Accessing data with Data Access Application Block:

using Microsoft.ApplicationBlocks.Data;

SqlHelper.ExecuteNonQuery(connection,"INSERT_PERSON",new
SqlParameter("@Name",txtName.Text) ,new SqlParameter("@Age",txtAge.Text) );



Retrieving Multiple Rows using SqlDataReader and Data Access Application Block:


using Microsoft.ApplicationBlocks.Data; SqlDataReader dr = SqlHelper.ExecuteReader(connection,CommandType.StoredProcedure,"SELECT_PERSON");
STORED PROCEDURE SELECT_PERSON:
SELECT * FROM tblPerson;


Retrieving Multiple Rows using DataSet:

// Selects all the rows from the database
DataSet ds = SqlHelper.ExecuteDataset(connection,CommandType.StoredProcedure,"SELECT_DATA");
// Sends the parameters and returns the dataset
DataSet ds = SqlHelper.ExecuteDataset(connection,CommandType.StoredProcedure,new SqlParameter("@Name",txtName.Text), new SqlParameter("@Age",txtAge.Text));



Retrieving a Single Row


First Create a Stored Proc:

CREATE PROCEDURE getPersonDetails @PersonID int, @Name nvarchar(40) OUTPUT,@Age nvarchar(40) OUTPUTASSELECT @Name = Name,@Age = AgeFROM tblPersonWHERE PersonID = @PersonID


As you see in the stored procedure that we have marked the parameters with OUTPUT which means that when the execution of stored procedure is completed those parameters with OUTPUT keyword will be returned to the caller



SqlParameter [] arParms = new SqlParameter[3];
// @PersonID Input Parameter
arParms[0] = new SqlParameter("@PersonID", SqlDbType.Int );
arParms[0].Value = personID;
// @ProductName Output Parameter
arParms[1] = new SqlParameter("@Name", SqlDbType.NVarChar, 40);
arParms[1].Direction = ParameterDirection.Output;
// @UnitPrice Output Parameter
arParms[2] = new SqlParameter("@Age", SqlDbType.NVarChar,40);
arParms[2].Direction = ParameterDirection.Output;
// Execute the stored procedure
SqlHelper.ExecuteNonQuery( connectionString, CommandType.StoredProcedure, "getPersonDetails", arParms);
// create a string array of return values and assign values returned from stored procedurestring [] arReturnParms = new string[2];
arReturnParms[0] = arParms[1].Value.ToString();
arReturnParms[1] = arParms[2].Value.ToString();



Retrieving XML Data:
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
// Call ExecuteXmlReader static method of SqlHelper class that returns an XmlReader
// We pass in an open database connection object, command type, and command text
XmlReader xreader = SqlHelper.ExecuteXmlReader(conn, CommandType.Text, "SELECT * FROM Products FOR XML AUTO" );
return xreader;