none
One statement to update or insert records RRS feed

  • Question

  •  

    I posted this in the Transact SQL forum. I did receive some answers but none that completely answered my question.

     

    In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net?
    My old VB6 code
    Dim dbData As New ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    Dim strParm As String
    Dim strCusNo As String
    '
    strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password"
    dbData.Open strParm
    '

    strCusNo = "MS"

    '
    strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'"
    rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText
    If rs1.BOF And rs1.EOF Then
        rs1.AddNew
    Else

    End If
    With rs1
        !CusNo = strCusNo
        .Update
    End With
    rs1.Close
    '
    Set rs1 = Nothing
    dbData.Close
    Set dbData = Nothing

    Is there an ADO.Net equivalent?

    thanks,

    Thursday, May 8, 2008 10:37 AM

All replies

  • I personally like to create stored procedures that have the logic in them to do either an update or an insert depending on if the row exists or not.  Then you just use code similar to the following (C# - sorry):

     

    parameters = new object[]

    {

    _col1,

    _col2,

    _col3,

    _col4

    };

     

    try

    {

    SqlHelper.ExecuteNonQuery(Config.ConnectionString1,

    "sp_name", parameters);

    }

    catch (Exception e1)

    {

    MessageBox.Show(

    e1.Message,

    "Error Saving", MessageBoxButtons.OK, MessageBoxIcon.Error);

    }

     

     

    Wednesday, October 8, 2008 4:30 PM
  • There are similar things in ADO.Net.

     

    If you are selecting from a single table, the SqlDataAdapter w/ SqlCommandBuilder may do what you want - using the DataSet to store your data.  Visual Studio also has wizards to help wrap some of the details into a TableAdapter.

     

    If you have complex queries, joins, etc... or just prefer to use objects/collections instead of rows/tables consider using the ADO.Net Entity Framework or Linq to Sql.

    Friday, October 10, 2008 2:04 AM
    Moderator
  • Ah, this is like asking... "Well, I usually bike to work but I have trouble on the hills so I shift to a lower gear. How do I do this when I take the bus?"

     

    VB6 (ADO classic) and ADO.NET are very different. They approach data access problems in very different ways. For example, In ADO classic you worked with cursors and local Recordset data structures that had specific properties, methods and events and side-effects for which there is often no equivalent in ADO.NET--nor would you want them to be. ADO classic (generally) uses a "connected" approach where ADO.NET uses a disconnected approach.

     

    I, for one, would not (ever) insert a string into SQL as you have done as it opens your code up to SQL injection attacks.

    Next, I would attempt to write a stored procedure to perform (in pseudocode)

     

    Determine if the row in question exists:

    IF EXISTS(....)

    Update the row in place given input parameters passed to the SP.

    ELSE

    INSERT a new row based on these parameters.

     

    this code is executed on the server so it's faster, safer and provides a common way to deal with updates.

    Another approach is to simply post the INSERT using a PrimaryKey that if it already exists, fails to run. This, however requires another round trip.

     

    I expect my book ADO and ADO.NET Examples and Best Practices might help--it was written for developers transitioning from VB6 to VB.NET. My most current book has updated ADO.NET 2.0 concepts and best practices as well.

     

    hth

     

     

    Sunday, October 19, 2008 12:23 AM
    Moderator