1) Accetta una lista di parametri (una terna composta da nome del campo del db, valore da inserire e tipo di dato)
2) Generare una stringa di INSERT o UPDATE accettando come parametro la tabella dove eseguirla (e la condizione where nel caso in cui sia una update)
4) Ritornare un SqlCommand da poter collegare ad una connection ed eseguirlo.
Public Class _CreateParametrizedCommand
Private toReturn As SqlClient.SqlCommand
Private ParamToInsert As New Dictionary(Of String, SqlDbType)
Public Function AddParameter(Of K)(ByVal ParamName As String, ByVal value As K, ByVal _type As SqlDbType)
If IsNothing(toReturn) Then
toReturn = New SqlClient.SqlCommand()
toReturn.Parameters.Clear()
End If
' parametro già inserito
If ParamToInsert.ContainsKey(ParamName) Then
Throw New Exception("Esiste già un parametro di nome: " & ParamName)
Return False
End If
ParamToInsert.Add(ParamName, _type)
'#se non è nothing lo aggiunge
If Not IsNothing(value) Then
toReturn.Parameters.AddWithValue(ParamName, _type).Value = value
End If
Return True
End Function
Public Function Get_INSERT_InstanceOfParametrizedCommand(ByVal TableName As String) As SqlClient.SqlCommand
If ParamToInsert.Count = 0 Then
Throw New Exception("Nessun parametro inserito.")
End If
toReturn.CommandText = "INSERT INTO " & TableName & " ("
For Each it As KeyValuePair(Of String, SqlDbType) In ParamToInsert
toReturn.CommandText &= it.Key & ","
Next it
toReturn.CommandText = Mid(toReturn.CommandText, 1, Len(toReturn.CommandText) - 1) & ") "
toReturn.CommandText &= " VALUES ("
For Each it As KeyValuePair(Of String, SqlDbType) In ParamToInsert
toReturn.CommandText &= "@" & it.Key & ","
Next it
toReturn.CommandText = Mid(toReturn.CommandText, 1, Len(toReturn.CommandText) - 1) & ") "
Return toReturn
End Function
Public Function Get_UPDATE_InstanceOfParametrizedCommand(ByVal TableName As String, ByVal WHERE_CONDITION As String) As SqlClient.SqlCommand
If ParamToInsert.Count = 0 Then
Throw New Exception("Nessun parametro inserito.")
End If
toReturn.CommandText = "UPDATE " & TableName & " SET "
For Each it As KeyValuePair(Of String, SqlDbType) In ParamToInsert
toReturn.CommandText &= it.Key & "=@" & it.Key & ","
Next it
toReturn.CommandText = Mid(toReturn.CommandText, 1, Len(toReturn.CommandText) - 1) & " "
toReturn.CommandText &= " " & WHERE_CONDITION
Return toReturn
End Function
End Class
End Class