Как я могу использовать VB.NET для настройки DataAdapter для использования хранимых процедур на сервере Sql для команд выбора, обновления, вставки и удаления?

Question

Я создал тестовый проект, который должен просто заставить DataGridView работать с Sql Server с помощью хранимых процедур. Я забочусь обо всех махинациях SQL Server в классе Employee и действиях по просмотру данных в форме frmMain.

По своей природе я собираюсь использовать одни и те же столбцы во всех четырех командах.

Я хочу определить каждый параметр один раз, а затем добавить его ко всем командам, которые в нем нуждаются. Проблема в том, что я получаю сообщение об ошибке "SqlParameter уже содержится в другой SqlParameterCollection". Кто-то сказал мне, что мне придется называть каждый параметр по-разному для каждой из команд, которые его используют. Я могу видеть, как это было бы так, но я надеюсь, что кто-то там знает, как заставить подход с одним столбцом одним параметром работать.

Employee Class

Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient

Public Class Employees
    'Declarations
    Private dtEmployees As DataTable
    Private daEmployeer As SqlDataAdapter
    Const HotmixCn As String = "Data Source=MyServer;Initial Catalog=MyDatabase;User ID=USER;Password=password"
    Private Cn As SqlConnection

    'Properties
    Public Property EmployeeList As DataTable

        Get
            Return dtEmployees
        End Get
        Set(value As DataTable)
            dtEmployees = value

        End Set
    End Property


    'Methods
    Public Sub New()
        ' Try
        Cn = New SqlConnection(HotmixCn)
        daEmployeer = New SqlDataAdapter
        Dim cmdSelectEmployees As New SqlCommand
        Dim cmdInsertEmployee As New SqlCommand
        Dim cmdUpdateEmployee As New SqlCommand
        Dim cmdDeleteEmployee As New SqlCommand
        'Configure the Select command (!! have to for SP)
        With cmdSelectEmployees
            .CommandType = CommandType.StoredProcedure
            .CommandText = "uspTestGetEmployees"
            .Connection = Cn

        End With

    --- Same thing for Insert, Update and Delete
        '
        '/// Add The Parameters To All Three Commands ///
        Dim parm As SqlParameter
        parm = New SqlParameter
        With parm
            .ParameterName = "@EmpNo"
            .SqlDbType = SqlDbType.Int
            .Direction = ParameterDirection.Input
            .SourceColumn = "EmpNo"
        End With
        cmdInsertEmployee.Parameters.Add(parm)
        cmdUpdateEmployee.Parameters.Add(parm)
        'cmdDeleteEmployee.Parameters.Add(parm)
        '
----- Similar for remaining parameters            

        '
        'Include the individual commands in the dataadapter
        daEmployeer.SelectCommand = cmdSelectEmployees
        daEmployeer.UpdateCommand = cmdUpdateEmployee
        daEmployeer.InsertCommand = cmdInsertEmployee
        daEmployeer.DeleteCommand = cmdDeleteEmployee
        '/// Fill the Datatable
        dtEmployees = New DataTable

        Cn.Open()
        daEmployeer.Fill(dtEmployees)
        Cn.Close()


        'Catch ex As Exception
        'MsgBox(ex.Message)

        'End Try

    End Sub
    'Events

End Class

Это небольшой код, который идет с DataGridView:

Imports DataLayer

Public Class frmMain
    Dim emp As New Employees

    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        dgvEmployees.DataSource = emp.EmployeeList

    End Sub
End Class

Answer 1

Кажется, вы пытаетесь добавить один и тот же параметр в два разных набора параметров в двух разных SqlCommand. Это невозможно, как видно из сообщения об ошибке.

Возможный обходной путь - создать дубликат исходной команды и добавить его в другую команду. Это утомительная работа, поэтому лучше создать метод расширения, который сделает за вас грязную работу.

Imports System.Runtime.CompilerServices

Module SqlClientExtensions

    <Extension()> 
    Public Function Duplicate(ByVal src As SqlParameter) As SqlParameter
        Dim copy = New SqlParameter(src.ParameterName,src.SqlDbType, src.Size, src.Direction,
                                      src.IsNullable, src.Precision, src.Scale, src.SourceColumn,
                                      src.SourceVersion, src.Value)

        Return copy; 
    End Function 

End Module

И теперь вы можете написать что-то вроде этого

Dim parm As SqlParameter
parm = New SqlParameter
With parm
    .ParameterName = "@EmpNo"
    .SqlDbType = SqlDbType.Int
    .Direction = ParameterDirection.Input
    .SourceColumn = "EmpNo"
End With
cmdInsertEmployee.Parameters.Add(parm)
cmdUpdateEmployee.Parameters.Add(parm.Duplicate())
cmdDeleteEmployee.Parameters.Add(parm.Duplicate())

Я также заметил, что SqlParameter реализует интерфейс ICloneable. Итак, на первый взгляд кажется, что вы могли бы написать что-то вроде этого

Dim b = new SqlCommand()
Dim p = new SqlParameter()
p.ParameterName = "@Test"
b.Parameters.Add(p)

Dim b1 = new SqlCommand()
Dim p1 = CType(CType(p, ICloneable),SqlParameter)
b1.Parameters.Add(p1)

но это возвращается к исходной ошибке "SqlParameter уже содержится в другой SqlParameterCollection" (и, даже если бы это работало, я бы предпочел метод расширения для его ясности)

Answer 2

Я хочу определить каждый параметр один раз, а затем добавить его ко всем командам, которые в нем нуждаются.

К сожалению, это просто запрещено. Вам нужно будет прикусить пулю и переопределить параметр для команд выбора, обновления и удаления.