using (DatabaseObjectSQL dbObj = new DatabaseObjectSQL())

// The RunSQLReturnDataset method has multiple overloads, and we saw in the first tutorial

// that it can take a SQL statement and return a DataSet. In this example, we will see how to

// run a SELECT statement that expects parameters.

// Create an instance of ArrayList that will hold the parameter objects

ArrayList arrayParams = new ArrayList();

// Get an instance that is appropriate for the currently selected database. The GetNewParameterInstance method returns a new

// instance of a parameter object that will work with the configured database type i.e. OracleParameter if the configured database is Oracle

// or SqlParameter if the configured database is SQL Server. Since both these and many other parameter types support the IDataParameter interface

// we simply use the interface to declare a variable and hence achieve database independent code.

IDataParameter param = dbObj.GetNewParameterInstance();

param.ParameterName = "CustomerID";

param.DbType = DbType.Int32;

param.Direction = ParameterDirection.Input;

param.Value = int.Parse(this.TextBox_CustomerID.Text);

arrayParams.Add(param);

// Run the method with the SQL statement and pass the parameters (held in the ArrayList object)

DataSet ds = dbObj.RunSQLReturnDataset("SELECT * FROM Customers WHERE CustomerID = @CustomerID", arrayParams);

// AffectedRecords property can be used to determine the number of records returned or affected by any database command

// This property is always set, no matter what method you use or wheather the method actually returns any records

// or inserts, updates, deletes records.

if (dbObj.ErrorString == null && dbObj.AffectedRecords > 0)

{

if (ds.Tables[0].Rows.Count > 0)

{

// Here we are going to pass two Parameters to an Update SQL Statement

// Since UPDATE Statement does not return any data and it only affects (i.e. Updates) one or more database records

// we use the RunSQLWithNoReturn method. This method has multiple overloads, the being used below

// takes a SQL statement and an ArrayList of parameters objects and runs the SQL command on the database.

// The number of records affected by the command are returned by the method, and also stored in the AffectedRecords property.

IDataParameter param1 = dbObj.GetNewParameterInstance();

param1.ParameterName = "FirstName";

param1.DbType = DbType.String;

param1.Direction = ParameterDirection.Input;

param1.Value = this.TextBox_FirstName.Text;

arrayParams.Add(param1);

IDataParameter param2 = dbObj.GetNewParameterInstance();

param2.ParameterName = "LastName";

param2.DbType = DbType.String;

param2.Direction = ParameterDirection.Input;

param2.Value = this.TextBox_LastName.Text;

arrayParams.Add(param2);

int affectedRecs = dbObj.RunSQLWithNoReturn("UPDATE Customers SET FirstName = @FirstName, LastName = @LastName WHERE CustomerID = @CustomerID;", arrayParams);

// Another way to see the records affected is to check the AffectedRecords property, as seen in the above SELECT code.

if (affectedRecs > 0)

{

MessageBox.Show("Record Saved!");

this.ResetTextBoxes();

}

else if (dbObj.ErrorString != null)

{

// If the command generated any exception that is caught and the error message is stored in the ErrorString property

MessageBox.Show(dbObj.ErrorString);

this.ResetTextBoxes();

}

}

else

{

MessageBox.Show("Customer does not exist");

this.ResetTextBoxes();

}

}

else

{

if (dbObj.ErrorString != null)

{

MessageBox.Show(dbObj.ErrorString);

this.ResetTextBoxes();

}

this.ResetTextBoxes();

}