Comparing RPG to C# and SQL

In an RPG program (One of IBM's languages on the AS/400) I can "chain" out to a file to see if a record (say, a certain customer record) exists in the file. If it does, then I can update that record instantly with new data. If the record doesn't exist, I can write a new record. The code would look like this:

Customer  Chain CustFile   71 ;turn on indicator 71 if not found
          if    *in71         ;if 71 is "on"
          eval  CustID = Customer;
          eval  CustCredit = 10000;
          write CustRecord
          else                ;71 not on, record found.
          CustCredit = 10000;
          update CustRecord
          endif

Not being real familiar with SQL/C#, I'm wondering if there is a way to do a random retrieval from a file (whic开发者_运维知识库h is what "chain" does in RPG). Basically I want to see if a record exists. If it does, update the record with some new information. If it does not, then I want to write a new record. I'm sure it's possible, but not quite sure how to go about doing it. Any advice would be greatly appreciated.

RPG has intrinsic connectivity to the database tables on the system. That makes it easy to write such succinct operations.

C#, on the other hand, requires you to implement your own database routines (or use a framework like LINQ).

If I was doing this, I would create a class responsible for database manipulation using the System.OLEDB objects.

Some methods might be (general idea, not actual code):

public boolean CheckExists(string TableName, string ColumnName, int ID) {
  //Connect to database
  // Create Command with query  "SELECT COUNT(1) FROM " + TableName.Replace(";","") + " WHERE " + ColumnName.Replace(";","") + " = " + ID 

  Return int.Parse(myQuery.ExecuteScalar) > 0

 //disconnect

}


 public boolean UpdateCredit(int CustID, int newCredit) {
  //Connect to database
  // Create Command with query  "UPDATE CustTable SET CustCredit = " + newCredit.ToString() + " WHERE = CustId = " + CustID 

  myQuery.ExecuteNonQuery

//disconnect

}

public boolean CreateCredit(int CustID, int newCredit) {
  //Connect to database
  // Create Command with query  "INSERT INTO CustTable (CustID, CustCredit) VALUES (" + CustId.ToString + ", " + newCredit.ToString + ")"

  myQuery.ExecuteNonQuery

  //disconnect
}

Then your main method world read something like

If (CheckExists("CustTable", "CustId", CustID)) {
    UpdateCredit(CustID, 10000)
} else {
    CreateCredit(CustId, 10000)
}