how to re-write this update code

[purplehaze1]

I can't get commandbuilder to generate insert/update/delete command in Sybase, so I want to write my own update logic. But I want to avoid writing update/insert/delete routine for each table. I've got about 40 tables. Is there a way for doing this?
Thanx.


Public function SaveCustomer() as boolean
Dim strsql As String
Dim myCommand As OdbcCommand
Dim dr As DataRow

Try

strsql = "SELECT * FROM t_customer WHERE cust_id=" & m_custID
myCommand = New OdbcCommand(strsql, myConnection)
Dim da As New OdbcDataAdapter(myCommand)
Dim ds As New DataSet()

da.Fill(ds, "Customer")

If ds.Tables(0).Rows.Count = 0 Then
dr = ds.Tables(0).NewRow()
IsNew = True
GoTo SaveRecord
Else
dr = ds.Tables(0).Rows(0)
End If
SaveRecord:
dr("Address") = IIF(m_address = String.Empty, String.Empty, m_address)
dr("BillingRate") = m_billingrate
...etc

If ds.Tables(0).Rows.Count = 0 Then
InsertRecord(dr)
Else
UpdateRecord(dr)
End If

End Function
----------------------------


Insert/Update/Delete Functions
----------------------------
Public Sub InsertRecord(ByVal dr as DataRow)

Dim strSQL as String
strSQL = "INSERT INTO Employees "
strSQL &= "(Address, "
strSQL &= "BillingRate)"
strSQL &= "VALUES ("
strSQL &= dr("Address") ","
strSQL &= dr("BillingRate") ");"
dbCmd.CommandText = strSQL

Try
dbConn.Open()
dbCmd.ExecuteNonQuery()
Finally
dbConn.Close()
End Try

End Sub


...Same for update & Delete

 

[ThatRickGuy]

Yes, but nothing that I know of will be a direct plug in and go. I just finished writing a system for our Sybase database, but we already had a complete object model in place. So all my code has to do is create a string based on the table name and properties of the data object. Unfortunatly, with out a solid object model structure in place, this would be extremely difficult.

Another option would be to create a program that goes through the database and retreives all of the tables' names and fields, then generates a text file containing all of the insert/update/delete strings for each table. This is the process that we use on SQL Server to generate all of our Stored Procedures.

One more option is to get the table information at run time and build the string dynamicly then. This is similar to how my solution works, except that this would actually get the information from either the database or data table instead of the data object.

Abstration makes me happy. And I would suggest creating a strong data object model and abstraction layer for any database interaction. It's a pita up front, but the standarization it can provide is awsome and really lends to rapid development down the road.

-Rick

----------------------

http://www.ringdev.com