The ETU Data Layer Control stands for "Easy To Use" data layer. As simple as that, it is easy to use. It handles open/close of database connections and takes care of the mapping and plumbing of parameters passed into stored procedures, while providing reusable DataSet and DataReader methods. Below are samples of the data layer at work:

1. Ensure the following connection string name is in your app/web.config file and the name must be "ETU_common_Helper_Connection" -- you can add as many connection strings as you want and use the connection string override, but this string must exist at least once.

"<add name="ETU_common_Helper_Connection" connectionString="Data Source=xxxxx;Initial Catalog=xxxxx;Integrated Security=True" providerName="System.Data.SqlClient" />"

2. The data layer uses object arrays to pass in parameters to the underlying stored procedures. This provides flexibility in allowing many object types (string, integer, etc) to be passed into the data layer. Parameters must be passed in using the EXACT order of the stored procedure.

3. To Update, Delete, or Insert with NO RETURN VALUE use the following method (example taken from live production application):

int messageID = 1;
string messageText = "Test Application";
string messageSubject = "Test Subject";
DataBridgeConnector dbc = new DataBridgeConnector(string.Empty); //can pass in connection string here or use default
object[] objDB = new object[3];
objDB.SetValue(messageID, 0);
objDB.SetValue(messageText, 1);
objDB.SetValue(messageSubject, 2);
dbc.ExecuteSqlNonQuery("StoredProcedureNameHere", false, objDB); //second parameter is always false. it is to indicate whether the is an output parameter, however, there is a new method to handle output

4. To Update, Delete, or Insert with RETURN VALUE use the following method (example taken from live production application):

DataBridgeConnector dbc = new DataBridgeConnector(string.Empty);
object[] objArray = new object[4];
objArray.SetValue("Jack", 0);
objArray.SetValue(null, 1);
objArray.SetValue(string.Empty, 2);
objArray.SetValue(0, 3); //the last value shown here is a bigint output parameter from the stored procedure. set the output parameter to match the type of output, if string pass in "string.Empty", if integer
pass in an integer, etc

object[] outputQuery = dbc.ExecuteSQLNonQueryWithOutput("StoredProcedureNameHere", objArray); //use an object array to accept the results of the procedure, from this object you can access the result

int messageID = Convert.ToInt32(outputQuery[0]); //this is the result that was mapped to objArray, item 3 as shown above. if there are more output parameters, reference outputQuery[n] on the right side

5.  To return a DataSet, use the following method (example taken from live production application):

DataBridgeConnector dbc = new DataBridgeConnector(string.Empty);
object[] objArray = new object[1];
objArray.SetValue("UserName", 0);

DataSet ds = dbc.GetSqlDataSet("StoredProcedureNameHere", objArray);
dgvUpdateRecords2.DataSource = ds.Tables[0].DefaultView;   //dgvUpdateRecords2 is a GridView
dgvUpdateRecords2.DataBind();

ds = null;

6.  To return a DataReader, use the following method (example taken from live production application):

DataBridgeConnector dlc = new DataBridgeConnector(string.Empty);

object[] obParam = new object[4];
obParam.SetValue(55, 0);
obParam.SetValue("SELECTPAGES", 1);
obParam.SetValue("", 2);
obParam.SetValue("", 3);

SqlDataReader sdr = dlc.ExecuteReader("StoredProcedureNameHere", obParam);
while (sdr.Read())
{
            ListItem li = new ListItem();
            li.Text = sdr["PageName"].ToString();
            li.Value = sdr["PageName"].ToString();
            ddlEdit.Items.Add(li);
            ddlPageNameDelete.Items.Add(li);

}

sdr.Close();

sdr.Dispose();

7.  To use multiple connection strings directly in the DataBridgeConnector constructor, follow as below:

string connString = "Connection String Settings Here:";

DataBridgeConnector dlc = new DataBridgeConnector(connString);

8.  Do NOT reuse DataBrideConnector objects without creating a new object instance, as it can result in memory leaks:

Incorrect --

DataBridgeConnector dlc = new DataBridgeConnector(string.Empty);

dlc.ExecuteNonQuery();

dlc.ExecuteNonQuery();

Correct --

DataBridgeConnector dlc = new DataBridgeConnector(string.Empty);

dlc.ExecuteNonQuery();

dlc = new DataBridgeConnector(string.Empty);

dlc.ExecuteNonQuery();

 

Last edited Nov 20, 2013 at 5:34 PM by salvation06, version 9