.NET (Core and Framework)

Member 10388494 wrote:

And now i want my dataset to affect the database(sql server)
without using DataAdapter.(It's a method for lazy people)

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

• Create a DbCommand and relevant parameters to delete a row;
• Call GetChanges(DataRowState.Deleted) on the DataTable;
• Iterate through the returned rows, assigning the relevant values to the parameters and executing the command to delete the row from the database;
• Create a DbCommand and relevant parameters to update a row;
• Call GetChanges(DataRowState.Modified) on the DataTable;
• Iterate through the returned rows, assigning the relevant values to the parameters and executing the command to update the row in the database;
• Create a DbCommand and relevant parameters to insert a new row;
• Call GetChanges(DataRowState.Added) on the DataTable;
• Iterate through the returned rows, assigning the relevant values to the parameters and executing the command to insert the row in the database;

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
        _isadded = True
        txtAddress.Text = ""
        txtName.Text = ""
        txtEmail.Text = ""
        txtserial.Text = ""
    End Sub

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click

        If _isadded = False Then
            _DataSet.Tables(0).Rows(txtCurrent.Text - 1).Item("Name") = txtName.Text
            _DataSet.Tables(0).Rows(txtCurrent.Text - 1).Item("Address") = txtAddress.Text
            _DataSet.Tables(0).Rows(txtCurrent.Text - 1).Item("Email") = txtEmail.Text
        Else
            txtCount.Text = CStr(CInt(txtCount.Text) + 1)
            txtCurrent.Text = txtCount.Text
            Dim _dr As DataRow = _DataSet.Tables(0).NewRow
            _dr.Item(1) = txtCurrent.Text
            _dr.Item(2) = txtName.Text
            _dr.Item(3) = txtEmail.Text
            _dr.Item(4) = txtAddress.Text
            _DataSet.Tables(0).Rows.Add(_dr)
        End If

Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
     DataGridView2.Columns.Item(0).Visible = False
     If _isdeleted = True Then
         Dim cmd As New SqlCommand
         If _cn.State = ConnectionState.Closed Then _cn.Open()
         cmd.Connection = _cn
         cmd.CommandText = "delete from tblcustomer where serial = " & Val(txtserial.Text)
         cmd.ExecuteNonQuery()
         txtCurrent.Text = (txtCount.Text) - 1
         RefreshData(True)
     ElseIf _isadded = False Then
         Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=DatabaseConnection;Integrated Security=True;"
         Dim _cn As SqlConnection = New SqlConnection(strconnection)
         Dim cmd As New SqlCommand
         _cn.Open()


         cmd.CommandText = "update tblCustomer set ID=@ID, Name=@Name, Email=@Email, Address=@Address where serial = @serial"
         cmd.Connection = _cn
         cmd.Parameters.AddWithValue("@ID", Me.txtIDD.Text)
         cmd.Parameters.AddWithValue("@Name", Me.txtName.Text)
         cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text)
         cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text)
         cmd.Parameters.AddWithValue("@serial", Me.txtserial.Text)
         cmd.ExecuteNonQuery()
     Else
         Try
             Dim strconnection As String = "Data Source=eliane-vaio\sqlexpress;Initial Catalog=DatabaseConnection;Integrated Security=True;"
             Dim _cn As SqlConnection = New SqlConnection(strconnection)
             Dim cmd As New SqlCommand
             _cn.Open()
             cmd.CommandText = "INSERT INTO tblCustomer([ID],[Name], [Email], [Address]) VALUES (@ID, @Name, @Email, @Address) select @@IDENTITY;"
             cmd.Connection = _cn
             cmd.Parameters.AddWithValue("@ID", Me.txtCurrent.Text)
             cmd.Parameters.AddWithValue("@Name", Me.txtName.Text)
             cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text)
             cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text)
             txtserial.Text = cmd.ExecuteScalar.ToString()
             Me._DataAdapter.Fill(Me._DataSet)
             cmdRequery.PerformClick()
             cmdLast.PerformClick()
             'txtAddress.Enabled = False
             'txtEmail.Enabled = False
             'txtName.Enabled = False
         Catch ex As Exception
             MsgBox("Press add before you continue")
         End Try
     End If
 End Sub

Member 10388494 wrote:

I didn't mean it in a bad way

Member 10388494 wrote:

I am just trying to find how to write in database without the DataAdapter

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=DatabaseConnection;Integrated Security=True;"
        Dim _cn As SqlConnection = New SqlConnection(strconnection)
        Dim cmd As New SqlCommand
        _cn.Open()

        cmd.CommandText = "update tblcustomer set ([ID],[Name],[Email], [Address], [DeptID]) VALUES (<a href="/Members/id">@ID</a>, <a href="/Members/Name">@Name</a>, <a href="/Members/email">@Email</a>, <a href="/Members/address">@Address</a>, @DeptID)"
        cmd.Connection = _cn
        cmd.Parameters.AddWithValue("<a href="/Members/id">@ID</a>", Me.txtID.Text)
        cmd.Parameters.AddWithValue("<a href="/Members/Name">@Name</a>", Me.txtName.Text)
        cmd.Parameters.AddWithValue("<a href="/Members/email">@Email</a>", Me.txtEmail.Text)
        cmd.Parameters.AddWithValue("<a href="/Members/address">@Address</a>", Me.txtAddress.Text)
        cmd.Parameters.AddWithValue("@DeptID", Me.cbDept.SelectedValue)
        txtserial.Text = cmd.ExecuteScalar()
End Sub

txtserial.Text = cmd.ExecuteScalar()

INSERT INTO tblcustomer 
(
   [ID],
   [Name],
   [Email],
   [Address],
   [DeptID]
) 
VALUES 
(
   @ID, 
   @Name, 
   @Email, 
   @Address, 
   @DeptID
)

UPDATE 
   tblcustomer 
SET 
   ID = @ID,
   Name = @Name,
   Email = @Email,
   Address = @Address,
   DeptID = @DeptID
WHERE
   Serial = @Serial

txtserial.Text = cmd.ExecuteScalar.ToString()

Pete O'Hanlon wrote:

SELECT IDENTITY_SCOPE()