Tuesday, October 27, 2009

DataAccess - Part III

If you’ve already read my previous posts about DataAccess (Part I and Part II), you’ll notice that I said nothing about writing this in 3 parts. However, I realized that I left out another, better methodology for writing DataAccess classes. So, I thought I better address that pronto.

This better methodology involves classes using anonymous delegates. These classes can live directly in your DataAccess base class, or you can put them in your Business base class (so that your Business classes can effectively manage multiple “transactions” involving multiple DataAccess classses, rather than having it all be done at the DataAccess layer). To simplify matters, I will show this methodology only in the DataAccess base class and leave it as an exercise to the reader to extend the concepts to the Business layer.

What follows is a very simplified, but workable, version … I’ve left out some of the “nice-to-have” features that don’t affect the basic usability of the class (such as timeouts & transactions). Some of my method names might seem a bit “crude”, but I didn’t want it to resemble actual code in use. You know, copyright and all that. The point is to get the concepts across and let you expand on those concepts on your own.

So, first let’s look at the few key points. Here are the basic additions to the DataAccess class I showed in Part II that make this whole thing work:

public delegate void TheMethod();

private TheMethod m_DoIt;
private TheMethod m_UndoIt;

public TheMethod DoIt
{
set { m_DoIt = value; }
}
public TheMethod UndoIt
{
set { m_UndoIt = value; }
}

public virtual bool Start()
{
bool success = false;

// Enter the retry loop
while (!success)
{
try
{
// Commented out because I haven't included transactions in my example
//this.BeginTransaction();

this.m_DoIt();

// Here is where you'd commit your database transactions
//this.CommitTransaction();

success = true;
}
catch (Exception e)
{
if (this.RetryRequested(e) && this.attempt < this.maxRetries)
{
// Try to undo any changes before we retry
// If we can't, just abort
if (!this.UndoChanges())
{
this.AddToErrorMessage(e);
this.ForceCleanup();
break;
}

this.attempt++;

// Might want to sleep for random period
//System.Random rand = new System.Random();
//System.Threading.Thread.Sleep(rand.Next(MinSleepTime, MaxSleepTime));
}
else
{
// Max number of retries exceeded or an unrecoverable error
// - fail and return error message
this.AddToErrorMessage(e);
this.ForceCleanup();
break;
}
}
}

return (success);
}
protected bool UndoChanges()
{
try
{
if (this.m_UndoIt != null)
this.m_UndoIt();
}
catch (Exception e)
{
this.AddToErrorMessage(e);
return false;
}

return true;
}


I’ll explain how it works in a minute, but first let’s see the code you would use in your sub-classes to utilize the delegates:

public CustomerDataSet GetCustomer(long customerkey)
{
CustomerDataSet ds = null;

this.DoIt = delegate()
{
ds = new CustomerDataSet();
this.ClearParameters();
this.AddParms("CustomerKey", customerkey);
this.FillData(ds, "csp_CustomerGet");
};

this.Start();
return ds;
}
public bool SaveCustomer(CustomerDataSet dsChanged, CustomerDataSet dsDeleted)
{
this.DoIt = delegate()
{
// you'd also want to wrap all this in a transaction, I just haven't
// shown transactions in this example
this.SaveTable(dsChanged.Customer, "csp_CustomersPut");
this.SaveTable(dsChanged.Orders, "csp_OrdersPut");

if (dsDeleted != null)
{
this.SaveTable(dsDeleted.Orders, "csp_OrdersDelete");
this.SaveTable(dsDeleted.Customer, "csp_CustomerDelete");
}
};
this.UndoIt = delegate()
{
dsChanged.RejectChanges();
dsDeleted.RejectChanges();
};

return this.Start();
}

Now, I obviously have not shown all the methods in the class (I’ll post the entire simplified version of the class at the end of this post). But, let me explain how this works. Basically, you set up two anonymous delegates, DoIt and UndoIt, then run the Start() method which in turn runs the code defined in the delegates. The beauty of it all is that the Start() method contains the only try/catch stuff you need and therefore handles any and all exceptions. It completely does away with the necessity of writing try/catch blocks in all your DataAccess methods, and having to have the developer remember to check return values and or ErrorMessages from every method call.

In the Start() method, the DoIt delegate code runs first. If there are any exceptions thrown, it checks to see if we want to Retry the operation. If so, first we run the UndoIt delegate code, if any exists, and then run through the loop and try again. Note that the GetCustomer() method doesn’t have any UndoIt code, whereas the SaveCustomer() method does. This is because the dsChanged or the dsDeleted DataSets could possibly be changed in the process of attemping to Save, and thus we want to put them back to their original state before attempting the Save again. This isn’t an issue in the GetCustomer() method.

Now, as I mentioned, this greatly simplifies the original DataAccess class that I previously posted (no more try/catch stuff needed), so I’ll post the entire class as it now stands:

public class BBDataAccess
{
#region Declarations associated with the new delegate approach

public delegate void TheMethod();

private TheMethod m_DoIt;
private TheMethod m_UndoIt;

public TheMethod DoIt
{
set { m_DoIt = value; }
}
public TheMethod UndoIt
{
set { m_UndoIt = value; }
}

// you can make these into properties, or make them protected
// so the defaults can be changed
private int maxRetries = 3;
private int attempt;

#endregion

#region Declarations from the original DataAccess class

private IDbConnection oConnection;
private IDbCommand oCommand;
private IDbDataAdapter oAdapter;
private IDataParameterCollection oParms;

public string ConnectionString = "";
public string ErrorMessage = "";

// Tweak, if necessary, for other database servers (Oracle)
private string AtSign = "@";
private static int DeadlockRollbackError = 1205;

#endregion

#region Constructor/Destructor
public BBDataAccess()
{
BBConnection o = new BBConnection();
this.oConnection = o.GetConnection();
this.ConnectionString = o.ConnectionString;
this.oCommand = this.GetIDbCommand();
this.oAdapter = this.GetIDbDataAdapter(this.oCommand);
this.oCommand.Connection = this.oConnection;
this.oCommand.CommandType = CommandType.StoredProcedure;
}
#endregion

#region Methods associated with the new delegate approach

public virtual bool Start()
{
bool success = false;

// Enter the retry loop
while (!success)
{
try
{
// Start the transaction and execute the methods. Any failures from here
// to the final commit may result in an exception being thrown and caught which
// may lead to a retry or an abort depending on the type of error // Here is where you would start your database transactions
// It's commented out because I haven't included it in my example
//this.BeginTransaction();

this.m_DoIt();

// Here is where you'd commit your database transactions
//this.CommitTransaction();

success = true;
}
catch (Exception e)
{
// Typically we'll want a Retry if the database error was the
// result of a deadlock, but you can Retry for any reason you wish.
if (this.RetryRequested(e) && this.attempt < this.maxRetries)
{
// Try to undo any changes before we retry
// If we can't, just abort
if (!this.UndoChanges())
{
this.AddToErrorMessage(e);
this.ForceCleanup();
break;
}

this.attempt++;

// Might want to sleep for random period
//System.Random rand = new System.Random();
//System.Threading.Thread.Sleep(rand.Next(MinSleepTime, MaxSleepTime));
}
else
{
// Max number of retries exceeded or an unrecoverable error
// - fail and return error message
this.AddToErrorMessage(e);
this.ForceCleanup();
break;
}
}
}

return (success);
}
protected bool RetryRequested(Exception e)
{
bool retryRequested = false;

if (e is BBRetryException) retryRequested = true;

while (e.InnerException != null && !retryRequested)
{
e = e.InnerException;
if (e is BBRetryException) retryRequested = true;
}

return retryRequested;
}
/// <summary>
/// Try to undo any changes to the operation parameters so that we can retry
/// If the undo fails, the operation is aborted instead
/// </summary>
/// <returns></returns>
protected bool UndoChanges()
{
try
{
if (this.m_UndoIt != null)
this.m_UndoIt();
}
catch (Exception e)
{
this.AddToErrorMessage(e);
return false;
}

return true;
}

// You can handle error message differently if you wish
// For this example, I simply set up a public ErrorMessage string
// that will be an empty string if there were no errors.
protected void AddToErrorMessage(Exception e)
{
this.AddToErrorMessage(e.Message);
if (e.InnerException != null)
this.AddToErrorMessage(e.InnerException.Message);
}
protected void AddToErrorMessage(string msg)
{
this.ErrorMessage += msg + "\r\n";
}

/// <summary>
/// Force abort the transaction and cleanup the database connection without raising exceptions
/// Any problems are still logged to the error messages list. Used when cleaning up during
/// exception processing where we already know there may be some problem with the database
/// connection
/// </summary>
protected void ForceCleanup()
{
try
{
// commented because I haven't included Transactions in my example
//this.RollbackTransaction();
}
catch (Exception e)
{
this.AddToErrorMessage(e);
}

try
{
this.CloseConnection();
}
catch (Exception e)
{
this.AddToErrorMessage(e);
}
}

#endregion

#region Get/Use IDb Interface Objects
protected IDbCommand GetIDbCommand()
{
return new SqlCommand();
}
protected IDbDataAdapter GetIDbDataAdapter(IDbCommand command)
{
return new SqlDataAdapter((SqlCommand)command);
}
protected IDbDataParameter GetIDbDataParameter(string ParmName, object ParmValue)
{
return new SqlParameter(ParmName, ParmValue);
}
protected void DeriveParameters(IDbCommand command)
{
SqlCommandBuilder.DeriveParameters((SqlCommand)command);
}
#endregion

#region Parameter Methods

// Then there are the various protected methods for adding and setting Parameters, filling a DataSet, etc.
// It's these various methods that get used in the classes that are sub-classed from this "base" class.
// Here's just a few of them:
protected void ClearParameters()
{
this.oCommand.Parameters.Clear();
this.ErrorMessage = "";
}
protected void AddParms(string ParmName, object ParmValue)
{
if (ParmName.StartsWith(this.AtSign) == false)
ParmName = this.AtSign + ParmName;
if (ParmValue != DBNull.Value)
this.oCommand.Parameters.Add(this.GetIDbDataParameter(ParmName, ParmValue));
}
protected void AddParms(string ParmName, object ParmValue, ParameterDirection direction)
{
this.AddParms(ParmName, ParmValue);
this.SetParmDirection(ParmName, direction);
}

protected void SetParmDirection(string ParmName, ParameterDirection direction)
{
if (ParmName.StartsWith(this.AtSign) == false)
ParmName = this.AtSign + ParmName;

if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
((IDbDataParameter)this.oCommand.Parameters[ParmName]).Direction = direction;
}

protected void SetAllParameters(DataRow Row)
{
this.ClearParameters();
for (int i = 0; i < Row.Table.Columns.Count; i++)
{
this.AddParms(Row.Table.Columns[i].ColumnName, Row[i]);
}
}

#endregion

#region Database Methods, using the IDb interface members

protected void FillData(DataSet ds, string StoredProcName)
{
this.oCommand.CommandText = StoredProcName;
this.oAdapter.Fill(ds);
}
/// <summary>
/// In order to reduce programmer error and to mimic the native functionality of the
/// DataAdapter.Fill() command, we will open a connection if it has not already
/// been opened, Execute the Command and then leave it in the state that it was in
/// prior to that (close it if needed or leave it open).
/// </summary>
/// <param name="StoredProcName"></param>
/// <returns></returns>
protected void ExecuteCommand(string StoredProcName)
{
bool IsAlreadyOpen = (this.oConnection.State == ConnectionState.Open);

this.OpenConnection();
this.oCommand.CommandText = StoredProcName;

//
try
{
this.oCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
// Check if the sql exception is because of a deadlock rollback
// If so we can attempt a retry
if (ex.Number == DeadlockRollbackError)
throw new BBRetryException("Deadlock", ex);
else
throw ex;
}
catch (Exception ex)
{
throw ex;
}
if (IsAlreadyOpen == false)
this.CloseConnection();
}
protected void SaveTable(DataTable Table, string SaveProc)
{
// save each row
foreach (DataRow row in Table.Rows)
{
this.SetAllParameters(row);
this.ExecuteCommand(SaveProc);
}
}
#endregion

protected void OpenConnection()
{
if (this.oConnection.State != ConnectionState.Open)
{
this.ErrorMessage = "";
this.oConnection.Open();
}
}
public void CloseConnection()
{
if (this.oConnection.State != ConnectionState.Closed && this.oCommand.Transaction == null)
this.oConnection.Close();
}
}
public class BBRetryException : Exception
{
public BBRetryException() { }
public BBRetryException(string error) : base(error) { }
public BBRetryException(string error, Exception e) : base(error, e) { }
}


34 comments:

  1. Lady, have you ever heard of object oriented programming? I guess not. You should get a text book and check virtual functions - they were created decades ago just for this purpose.

    ReplyDelete
  2. Glad you posted anonymously, because your comment isn't very enlightening. Would you please elaborate and explain exactly what the heck you're talking about (and I don't mean explain OOP or virtual methods ... I certainly know what they are).

    I mean, explain exactly what you're objecting to and how you would propose to make my example better.

    Comments like yours serve no purpose if you can't back up what you've said.

    ReplyDelete
  3. Dear Mrs. B.,
    I did pick up your anti-da position, but I'm not sure exactly what it is that should not be done. I am living dangerously at the moment and have found one instance where I had a problem : I added 2 joins to the "select" sql and my delete, update and insert selects were gone. I had to recreate them which was a pain, but not a big deal. I will probably end up doing what you say (we all do, dear we all do), but at this stage of my learning curve I thought I'd stay with the ease of visual design of the sqls. When I stumble on something major, I promise, you'll be the first to know.

    ReplyDelete
  4. If I understand this correctly, the advantage of your methodology for data-access corresponds the the advantages of base classes for form controls.

    ReplyDelete
  5. Hi Marc,

    To be clear, it's "ta" I object to, not "da". And I object to either one if they're directly on the Form!! ;0)

    The visual designers are fine for designing UI, but Microsoft should have stopped there! IMHO visual designers do NOT work too well for designing DataSet or DataAccess classes ... the designer-generated code is fraught with problems when you need to make changes. Do yourself a favor and learn the basics instead of being spoon-fed the Kool-Aid and never learning it (and creating more problems for yourself in the process).

    Note that I do NOT include Typed DataSets in my above rant, even though they are comprised of generated code. The only time the generated code sucks is when you use the DataSet Designer, rather than the XML Editor. The DataSet Designer generates those horrible TableAdapters, the XML Editor does not.

    Try using Stored Procs instead of hard-coding the SQL Selects directly in your code. It's the much-preferred methodology of DBAs everywhere. =0)

    ReplyDelete
  6. Hi Bonnie

    In SQL Server are you saying that all CRUD routines should be defined in stored procedures on the db?

    Are you also saying that all datasets should be created by hand coding and can you create strongly typed datasets this way? Can you not edit the designer generated xsd to keep it up-to-date and strip or ignore the tableadapters?

    ReplyDelete
  7. Yes, all access to a database should be via Stored Procedures, even the CRUD stuff. DBAs will love you. ;0)

    No, I did *not* say you need to hand-code the DataSets ... unless by that you mean create them some other way besides dragging/dropping from the Server Explorer. If that's what you mean, then yes, I *am* saying that you hand-code them. But not really "by hand". You are probably referring to my other two posts: http://geek-goddess-bonnie.blogspot.com/2009/09/tableadapters-are-crap.html and http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html. That second post explains how to do this with a bit of code. This will create the .xsd file for you and you simply add it to your project (I also suggest a separate project just for DataSets) and run the Typed DataSet generator, as explained in that post. And yes, you can then keep it up-to-date in the .xsd (also explained in that post ... just utilize the XML Editor rather than DataSet Designer).

    Does this answer your questions?

    ReplyDelete
    Replies
    1. "Yes, all access to a database should be via Stored Procedures, even the CRUD stuff. DBAs will love you. ;0"

      Love this woman!

      I'll also add that stored procs also mean you don't have to grant explicit access to underlying objects. You get performance gains, security, small surface area of exposure and .......multiple resultsets if needed.

      Delete
    2. Thank you Highwayman!! You must be a DBA ... or maybe just a very good developer who uses SQL a lot! ;)

      Delete
  8. Hi Bonnie when you drag and drop a sql server table from the server explorer in vs2010 you still get table adapters added to the empty dataset pane. Is it not just as easy to delete the table adapter from this view to get rid of the table adapter, switch to view with the xml editor and then save changes?

    ReplyDelete
  9. Hello Bonnie my friend !

    Here's Jimmy Jimm i m working on Your DAL :) I got and error in line of #endregion

    // Tweak, if necessary, for other database servers (Oracle)
    private string AtSign = "@";
    private static int DeadlockRollbackError = 1205;

    #endregion //here

    My compiler said:
    Error 1 Unexpected preprocessor directive

    how's life? Everything's ok? I will mail You I still working on project I hhave to add some new staff and I am working firstly on this DAL to prepare better programming culture of code :)

    ReplyDelete
  10. Hey ... keeping busy. Regarding the error ... it's because those compiler directives need to be in pairs, and you've left one out:

    #region
    //code
    #endregion

    I'm in the process of finding a better plug-in for posting code to my blog. I know that when you copy/paste these code-blocks, they're unformatted, which makes it hard to spot simple errors. But still, not having a #region to go with that #endregion should be an easy one to spot!!!! ;0)

    ReplyDelete
  11. Hello,

    Yes, I trying Just add a #region clauze, but If i do that:

    #region Cluzules // <=added

    // Tweak, if necessary, for other database servers (Oracle)
    private string AtSign = "@";
    private static int DeadlockRollbackError = 1205;

    #endregion

    then my compiler said:

    Error 3 Type ... .BBDataAccess' already defines a member called 'BBDataAccess' with the same parameter types .. BBDataAccess.cs 75 16 project

    Error 2 The type ...BBDataAccess' already contains a definition for 'DeadlockRollbackError' ... \BBDataAccess.cs 69 24

    Error 1 The type ..BBDataAccess' already contains a definition for 'AtSign' ..\BBDataAccess.cs 68 20


    Error 8 Ambiguity between ..BBDataAccess.DeadlockRollbackError' and 'plain_project.DALBonnie.BBDataAccess.DeadlockRollbackError' ..BBDataAccess.cs 319 30

    ReplyDelete
  12. Robert, I think you combined the code from the second post with code from this post and you shouldn't have. I think in my email to you, I just said something about grabbing the few missing pieces from the code in this post to stick into the code from the second post (like, I think SetParmDirection() was not included in the second post's code ... or whatever methods they were, I don't remember off the top of my head).

    Besides, this particular methodology, using anonymous delegates, is probably too far advanced for you.

    As I also stated in my email to you ... you have GOT TO figure out compiler errors yourself. Especially the simple ones, like those you mentioned!! That is such an elementary, and necessary, programming skill ...

    ReplyDelete
  13. ok i will try again mayby I create something simpler but similar to Your version I will mail or post here effects, thanks for replay,

    wish good day to You

    ReplyDelete
  14. Bonnie

    I am a newbie to the world of VS and of course use VB.net. I too appreciate you Anti-TA war. I have not experimented with your codes but read the whole I, II and III posts.

    But let me tell you about the Wizard which generates the TAs. It is nothing but a PIA and a very painful one. It is finicky. Some times if you delete a relation, it will say the whole thing is just not ok and will throw up 102 errors ( the maximum ).

    I am living this nightmare of using the wizard to code my app because this is the way I have coded my app which has some forty odd tables. It is a technical app using Videos.

    May be you can create a simple plug-in with your code to eliminate the use of the wizard. As of now the only alternative is to do hand coding of the SQL.

    I DO NOT TRUST THE MICROSOFT VS TEAMS TO DO ANYTHING GOOD AND USEFUL FOR THE DEVELOPERS. THE MORE THEY MAKE IT EASY, THE MORE PROBLEMS THEY CREATE. ALSO EVERY TIME THERE IS A HUGE LEARNING CURVE TOO..

    ALSO MORE CRASHES IN WHICH YOU LOSE HOURS OF WORK..

    May be you can become a real Geek Goddess and become a saviour of simple mortals like us who use VS to make apps and make a living.

    Please become one by starting a simple to use Plug in to banish the wretched demon called Wizard.

    Thanks anyway for offering advise in this issue.

    Rgds

    Renga

    ReplyDelete
    Replies
    1. Hi Renga,

      I've never created a plug-in before and don't know if I have the time to learn how and to do it at the moment. However, I do have another blog post that would definitely fit your needs, so that you don't have to hand-code your Typed DataSet:

      http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

      I hope that makes life a little easier for you! ;0)

      Delete
    2. Hi Bonnie

      Sorry for coming back this late. I just now saw this. Thanks for your reply.

      I am now using this script for getting creating a new set of xsd's..

      Rgds

      Renga

      Delete
    3. Cool, Renga! I'm glad that was of some help to you!

      Delete
  15. Thanks for the guide Bonnie!
    I got lost somewhere between parts II and III on this (sorry, too many lines of code calling each other and I lost track). I'm assuming I keep everything from part I, but what comes over from part II (if anything at all)?
    Looking at it I think I need (part I classes) BBDataAccess and MyDataAccess
    and from part II BBConnection at the top.
    As far as I can tell from part III, I build the above, then rewrite BBDataAccess to match between the skeleton code and the data structure on my end right?
    Can ask for a quick list of the classes I worry about and which calls what? BBDataAcess calls BBConnection, etc?

    ReplyDelete
    Replies
    1. What you'll need is BBConnection from Part II and BBDataAccess from this post, Part III. Totally forget about anything that was in Part I.

      I don't know what you mean by "rewrite BBDataAccess to match the skeleton code and the data structure". BBDataAccess is good to go as it is (maybe want to add the IDisposable and TransactionScope stuff from the most recent post, the "Revisiting Yet Again" post).

      After that, you simply write a bunch of your own DataAccess classes that you sub-class from BBDataAccess. Does that make sense?

      Delete
    2. It does. By "rewrite" I meant change the fields/parameter for my database instead of bob's is all. I'm doing this more to learn the methods you're using and the associated code behind it. IDB and anonymous delegates are fairly new to me like that, never done completely disassociated data like this.
      Thanks again!

      Delete
    3. Ah, but "select * from bob" was in the example BBDataAccess class from Part I, which is not what you'll want to use at all. Notice that the BBDataAccess class in this post, Part III, is a completely functional class that will work "as is". All you have to do is sub-class it, like so:

      public class CustomerDataAccess : BBDataAccess
      {
      // all your custom stuff goes here, as I showed above:
      public CustomerDataSet GetCustomer(long customerkey)
      {
      ....
      }
      public bool SaveCustomer(CustomerDataSet dsChanged, CustomerDataSet dsDeleted)
      {
      ....
      }
      }

      Delete
    4. And I won't have to plug in a connection string (just the app setting), field names, query values, updates, etc.? If I need to pull from sales where price > 100 how would this know that? Or even to point at Sales table in the db? I'm blaming a lack of coffee on this particular question, but now I'm really lost lol. "Select" doesn't even appear in the code above or BBConnection in partII, and I was rather firmly under the impression I'd need it for 100% of queries. Could be the lack of query statements throwing me off on this come to think of it, I've been trying to figure out where to put them.

      Delete
    5. Notice that the GetCustomer() method of the CustomerDataAccess class (the class that was sub-classed from BBDataAccess), uses a Stored Procedure when it calls FillData(ds, "csp_CustomerGet"). If you prefer not to use Stored Procs, then substitute the Stored Proc name with your SELECT statement . You'd have to make a change to the BBDataAccess class because it defaults to using Stored Procs. Notice in the constructor it has the line:

      this.oCommand.CommandType = CommandType.StoredProcedure;

      If you'd prefer to use SELECT, INSERT, etc. instead of Stored Procs, this should be changed to CommandType.Text (which is the default anyway, so you could just omit the line if you wish).

      However, I think it would be better to have a method that you call if you want to set the CommandType from your sub-classes. So, you'd be better off adding something like this to the BBDataAccess class:

      protected void SetCommandType(CommandType commandType)
      {
      this.oCommand.CommandType = commandType;
      }

      Then your subclass would have to make the change if you wanted to use Text instead of Stored Procs. For example:

      public CustomerDataSet GetCustomer(long customerkey)
      {
      CustomerDataSet ds = null;

      this.DoIt = delegate()
      {
      ds = new CustomerDataSet();
      this.ClearParameters();
      this.AddParms("CustomerKey", customerkey);
      this.SetCommandType(CommandType.Text);
      this.FillData(ds, "SELECT * FROM Customer WHERE CustomerKey = @CustomerKey");
      };

      this.Start();
      return ds;
      }


      Delete
  16. Hi Bonnie! I've got this class called TaskSaver0 and it's partly working, but for some reason the changes don't actually commit to the database. This is inherited from a base control that set the connection and it works on the initial setup from part 1
    public void get_data()
    {
    OleDbDataAdapter da = new OleDbDataAdapter("Select * FROM Table", this.connection);
    da.Fill(outgoing_table);
    }
    This part works fine, it gets the data to the next few classes that work with the data.

    In the new class I followed the templates but nothing goes back in. I saw mention of transactions here, and I was wondering if you'd recommend either a tutorial or maybe point out the steps I seem to be missing, my class doesn't come with a CommitTransaction(). An event fires the below save, but no popup and no changes. I've checked the readouts, when I removed the error handling it bounces between the connection not being initialized or occasionally saying 4_p parameter has no default value, not sure what the 4_p parameter is.

    OleDbConnection connection;
    public TaskSaver0()
    {
    connection = this.oCon;
    }

    public void Reschedule_Tasks(Task t, DateTime start, DateTime end)
    {
    using (connection)
    {

    string query1 = "Update WorkOrders" +
    "SET ScheduledDate = ?, ScheduledStartDate = ?, ScheduledEndDate = ? " +
    "WHERE (WorkOrder= ?) AND (Worker= ?) AND (Company = ?)";

    using (OleDbCommand MyUpdate = new OleDbCommand(query1, connection))
    {


    OleDbParameter NewParm = new OleDbParameter("ScheduledDate", DateTime.Now);
    NewParm.OleDbType = OleDbType.Date;
    MyUpdate.Parameters.Add(NewParm);

    NewParm = new OleDbParameter("ScheduledStartDate", start);
    NewParm.OleDbType = OleDbType.Date;
    MyUpdate.Parameters.Add(NewParm);

    NewParm = new OleDbParameter("ScheduledEndDate", end);
    NewParm.OleDbType = OleDbType.Date;
    MyUpdate.Parameters.Add(NewParm);

    NewParm = new OleDbParameter("WorkOrder", t.WorkOrder);
    NewParm.OleDbType = OleDbType.Integer;
    MyUpdate.Parameters.Add(NewParm);

    NewParm = new OleDbParameter("Worker", t.Worker);
    NewParm.OleDbType = OleDbType.BSTR;
    MyUpdate.Parameters.Add(NewParm);

    NewParm = new OleDbParameter("Company", t.company);
    NewParm.OleDbType = OleDbType.BSTR;
    MyUpdate.Parameters.Add(NewParm);


    connection.Open();

    int results = MyUpdate.ExecuteNonQuery();
    if (results >= 1)
    {
    MessageBox.Show("worked");
    }
    else
    {

    }
    connection.Close();
    }
    }

    Thanks in advance for any info! Sorry in advance for asking a question where the answer may be far too obvious!

    ReplyDelete
    Replies
    1. Is this TaskSaver class sub-classed from my BBDataAccess class? It doesn't look like it (since you're not using any of the base class methods), so I find it odd that you've asked the question here. The BBDataAccess class that I posted is not supposed to be a template. It's an actual base class that you sub-class for all of your own DataAccess. You don't indicate as to whether you're using SQL Server or not (except that I notice you're using "?", rather than "@" for parameters), but if you're not, all you need to do is change your version of the base BBDataAccess class to use OleDb classes where I've used Sql classes. For example, the GetIDbCommand() method. In my class, it looks like this:

      protected IDbCommand GetIDbCommand()
      {
      return new SqlCommand();
      }

      For non-SQL data access, you'd change it like this:

      protected IDbCommand GetIDbCommand()
      {
      return new OleDbCommand();
      }

      Likewise for other databases (like Oracle, you'd use new OracleCommand(), or whatever it's called). In either case, the method still returns an interface object, IDbCommand, just as I've shown above.

      Before I get into what your sub-class would look like, let me say that the problem you're probably seeing is the typical problem (with Access databases in particular), in that you're looking at the wrong database to see if your changes are there. Take a look at this post:

      https://social.msdn.microsoft.com/Forums/en-US/7596b291-d6d5-4252-bc69-266aaaf0684d/updating-access-db-in-c?forum=adodotnetdataproviders

      You can find more examples by Googling "ado.net access database wrong copy"

      Now, back to sub-classing your data access ... you'd have your class defined like this:

      public class TaskSaver0 : BBDataAccess
      {
      public void Reschedule_Tasks(Task t, DateTime start, DateTime end)
      {
      this.DoIt = delegate()
      {
      // forget about transactions for now
      string query1 = "Update WorkOrders" +
      "SET ScheduledDate = ?, ScheduledStartDate = ?, ScheduledEndDate = ? " +
      "WHERE (WorkOrder= ?) AND (Worker= ?) AND (Company = ?)";

      this.ClearParameters();
      this.AddParms("ScheduledDate", DateTime.Now);
      this.AddParms("ScheduledStartDate", start);
      this.AddParms("ScheduledEndDate", end);
      this.AddParms("WorkOrder", t.WorkOrder);
      this.AddParms("Worker", t.Worker);
      this.AddParms("Company", t.company);

      // think about adding another method to the base class,
      // call it ChangeCommandType() to be able to change the command type
      // to something other than StoredProcedure (which is currently what
      // I set it to in the constructor of BBDataAccess).
      // Or just set it up that way to begin with in the constructor of
      // either your class or BBDataAccess. You'd want to have this line:
      // this.oCommand.CommandType = CommandType.Text;

      this.ExecuteCommand(query1);

      // You also might want to change the ExecuteCommand() method to
      // return an int instead of being void. Then you could use the
      // error handling you've already used by checking the number of
      // rows Updated.
      };
      this.UndoIt = delegate()
      {
      // you probably won't have any code here
      };

      return this.Start();
      }
      }

      Delete
    2. Sorry for the delay, but thank you! So I went your route completely and added the BBConnection and BBDataAccess to my project as you described. The only error it threw was on this (in the protected void ExecuteCommand(string StoredProcName) function ) saying ex.Number doesn't exist, and with OLEDB it wasn't prompted on intellisense. I'm not sure what to change that to.
      catch (OleDbException ex)
      {
      // Check if the OleDb exception is because of a deadlock rollback
      // If so we can attempt a retry
      if (ex.Number == DeadlockRollbackError) //ex.Number gets the red squiggle "no definition" error
      throw new BBRetryException("Deadlock", ex);
      else
      throw ex;
      }
      And once this is fixed, do you already have a guide on adding the stored procedures that are apparently feed into this somewhere?
      I see why you get the title of geek goddess, I thought I was somewhat hardcore until I got a look at your stuff!

      Delete
    3. Unfortunately, I'm having trouble finding what code to use for this particular problem since you're using OleDbException and not SqlException. You're right, there isn't a corresponding ex.Number for OleDbExceptions, but there are other properties (ex.ErrorCode being one of them). However, I don't know what the ErrorCode would be for a Deadlocked OleDb transaction (I only know what it is for SQL).

      One way around the issue is to just not check for a Deadlock (take out the ex.Number if completely and just throw the ex). I've tried to Google for a code to use, but I'm not finding anything.

      I've got something somewhere about stored procedures, but I'm not sure if I've written a blog post about it or not. Let me see if I can dig something up and I'll get back to you!

      Delete
    4. I do feel like an idiot right now lol. All this fuss, it wasn't anything to do with the code really. Whoever filled in the database with dummy data left one of the query columns empty so it was null parameter value going into the attempt. Working and saving just fine now!
      Thanks for everything Bonnie!

      Delete
    5. If your database allows NULLs, then you've got to have code that can handle that possiblity. Obviously you weren't expecting that particular column to be NULL!!! ;0)

      I am still going to write a blog post about generating basic Stored Procedures (I started working on the blog post yesterday, but it will probably be a while before I will have the time to finish it). I don't know if it will be useful to you though, because it would be aimed at SQL Server and I don't have an Access database to test it with OleDb (it probably has to be done a lot differently with Access, and I wouldn't know what those differences might be).

      Delete
  17. Hi Bonnie, Hope you are well. I was looking at updating my DataAccess Classes to be able to deal with transactions so I am looking at your Data Access III code. I am struggling a bit with code for starting the transaction. When I call a stored procedure using the ExexuteCommand method, it implicitly opens and closes the connection with each update. So my question is, would I need to create and close the connection somewhere else in the class? Hope this makes sense!

    ReplyDelete
  18. Something like the following perhaps... ( I have added an IDbTransaction property to the BBConnection class)
    try
    {
    // Open Connection
    this.oConnection.Open();

    // Get Transaction
    oTransaction = this.oConnection.BeginTransaction();

    // Try stuff
    this.m_DoIt();

    // Commit to Database
    oTransaction.Commit();

    success = true;

    // Close Connection
    this.oConnection.Close();

    }

    ReplyDelete
  19. IDbTransaction property was added to BBDataAccess class (not BBConnection class)

    ReplyDelete