App Design Help

A

Answer

Freshman
Joined
Jul 2, 2002
Messages
46
Location
USA
Hey guys,

i have been pondering this question for the last two weeks and have yet to come up with a good answer. What i got, is a custom E-commerce shopping cart that runs off of a filesystem database. I am trying to create a "offline" ( meaning not webbased ) way of maintaining products, categorys...all that good stuff. What im running into however. Is that each time a product or category is created, its assigned an ID value, which acts as a primary key. This primary key is used to identify the object in other tables, for example, what product belongs to what category. In my app i am going to be using MSDE and plan on importing and exporting products through a custom script. I am stuck as to how to handle this id issue though...becuase thje ids must be unqiue. SO if i have a user that is using the web interface, and another using the app, i might get a overlapping id, even though its actually two completely different products.


Any ideas?
 
Is it possible to simply determine the next id at the time the item is added to the table regardless of the interface being used to add it?

So, both the web and non-web interface would have a LookupNextID(Product) function that looks to see if an ID already exists for a given Product and adds a new ID if the product is unique.

Here is a code snippet from an application where I basically do the same thing with purchasing data:
C#: 
// obtain description id
int intDescID = LookupDescID(strDescription);
if (intDescID < 1)	// id does not exist in master list
{
     InsertNewDescription(strDescription, CategoryCode.CSCPurchase);
     intDescID = LookupDescID(strDescription);
}

If this sounds to be on the right track and you want to see the code for LookupDescID and InsertNewDescription, just let me know.
 
Thanks for the reply.

Well, the problem is keeping the id's syncronized between the access database and the sql database. I suppose it could have the windows app call a php scipt and return an id in xml format. But that will require the windows app user to have a internet connection.

what i thought of doing is making another field in the sql database called web_id. And after i send the new rows to the webserver, it will return with the all the ids that were created on the web end. Then just store those values in the web_id column. for later use.

If you dont mind, i would like to see that extra code as it may give me some ideas.

Thanks!
 
Well, I didn't realize we were talking about two separate databases here; that changes everything.

Here is the code for LookupDescID and InsertNewDescription:
C#: 
private int LookupDescID(string strDescription)
{
   // initialize master data connection object
   dbMaster masterdb = new dbMaster();
   OleDbConnection master = masterdb.m_master;

   if (strDescription.IndexOf("'") > 0)
      strDescription = strDescription.Replace("'", "''");
   
   string strScalar = "SELECT DescID FROM REF_MasterDescription " +
      "WHERE (Description='" + strDescription + "')";
   OleDbCommand cmdScalar = new OleDbCommand(strScalar, master);

   Object oDescID = null;
   try
   {
      master.Open();
      oDescID = cmdScalar.ExecuteScalar();
   }
   finally
   {
      master.Close();
   }

   int DescID = 0;

   if (oDescID != null)
   {
      if (oDescID.EqualsDBNull.Value))
         DescID = 0;
      else
         DescID = Convert.ToInt16(oDescID);
   }
   return DescID;
}


private void InsertNewDescription(string strDescription, CategoryCode eCode)
{
   // initialize master data connection object
   dbMaster masterdb = new dbMaster();
   OleDbConnection master = masterdb.m_master;

   if (strDescription.IndexOf("'") > 0)
      strDescription = strDescription.Replace("'", "''");

   int intDescID = GetNextID(strDescription);
   ushort iCode = (ushort)eCode;
   string strInsert = "INSERT INTO REF_MasterDescription (DescID, Description, CategoryCode) " +
      "VALUES(" + intDescID.ToString() + ", '" + strDescription + "', " + iCode.ToString() + ")";
   OleDbCommand cmdMasterDescription = new OleDbCommand(strInsert, master);

   try
   {
      master.Open();
      cmdMasterDescription.ExecuteNonQuery();
   }
   finally
   {
      master.Close();
   }
}

I may not be following proper conventions or procedures, but I use the IF snippet from the previous post along with these functions in a routine that processes about 350,000 records. It seems to perform fairly well.
 
Back
Top