Ok, here's basically a routine....I'm only addressing the
problem you mentioned, you can initialize the connection
string, command object as you see fit.
Public Enum myErrCode as Integer
Success
ConnectionFailure
EmployeeDoesNotExist
SyntaxFailure
ParameterMismatch
MissingParams
'Whatever other codes you want
UnknownFailure
End Enum
'Pass in a DataTable or whatever other structure you want
'that contains the param name in field one, the type in
field two (I used a DataTable so you can take advantage
of DB type checking, but there are many other ways to do
this), value in column3 in the order that you list below
EmpName, EmpNo etc
Public Function OpetionalParamSql(ByVal myParams as
DataTable) as myErrCode
If myParams.Rows.Count = 0 Then Return
myErrcode.MissingParams
cmd.Parameters.Clear
cmd.Parameters.Add(myParams.Rows(0).Columns(0),
myParams.Rows(0).Columns(2))
cmd.Parameters.Add(myParams.Rows(1).Columns(0)),
myParams.Rows(1).Columns(2))
'These Params in the first column need to be strings that
match parameter names in your query...If you use Oledb,
you can just substitute ? instead and forget about the
named params...the logic is the same.
'Step one, verify that EmpNum or EmpName exist
'Add this SQL to your command object "SELECT COUNT(*) From
myTable where EmpNum = @EmpNum or EmpName = @EmpName"
Dim dr as DataReader = cmd.executereader
if Not dr.HasRows 'if you have 2003, or use While Dr.REad
and increment an integeer then test for that int > 1
Then return myerrCode.EmployeeDoesNotExist
'Now declare an arraylist that holds the datatypes of
each field.
Dim ar as New ArrayList
With ar
.Add("string")'EmpName
.Add("int") 'EmpNum
.Add("string") 'sex etc
End With
Dim x as Integer
Dim b as Boolean = False
For x = 0 to myParams.Rows.Count -1
If myParams.Rows(x).Columns(1) = ar(0) Then
b=True
Else
Return myErrCode.ParamMismatch
End If
Next x
'Now, you know you have met the first 3 of your criteria,
you've verified that empnum or empname exist, you know
that you have at least one parameter, and you know your
types match...
Now, grab all of the values and determine if they
exist, you can set blank values in the call to something
easily recognizable like "blank"
Dim sb as New System.Text.StringBuilder
sb.Append("Update MyTable Set ") 'Now iterate through
your table and dynamically build your sql like this
For z = 0 to myParams.Rows.Count
If myParams.Rows(z).Columns(2) is Not Null ' or your
blank code
Then sb.Append(myParams.Rows(z).Columns(0)) "='" &
myParams.Rows(z).Columns(2)) & "', "
'You should also use some logic to accomodate the comma,
you can either test for the first non null and use the
comma beforehand, or use the after method that I did and
make sure you remove it if it's the last
Character....like
If sb.ToString.EndsWith(",") Then Dim s as String = Left
(sb.ToString, sb.ToSTring.Length -1)
Finally execute the command with the dynamic sql as it's
commandtext.
Next z
End Function
Now, you can also write a bunch of individual SQL
statements and thereby write a ton of overloads, but
that will grow exponentially for each time you may have
to add a field.
This code is effectively psuedo code b/c I don't have an
IDE in front of me, but it should give you an idea about
how to go about it. You would probably want to
modularize it and break each step into a function using
the enum for a returncode...if you use the enum, you
won't have to remember return codes and you'll get a ton
of functionality.
Now, you don't have to construct a DataTable and pass it
in to accomplish this. You could use a multidimensional
array, a custom collection, three arraylists or whatever
else suits you, I just used the DataTable for the sake of
illustration
I know there is a lot to this, so if you have any
questions let me know. I'll be in the office tomorrow
and I have a class that takes dynamic parameter lists
that I can send you. Checking the existence of the first
two fields and the datatypes is all pretty
straightforward. Actually , the hardest part is the
dynamic string building but it's not that hard.
IF you send me your email address where I can send it to
you tomorrow, I'll be glad to send you what I have. In
the interim, I'll be up for a while, so let me know if
you have any questions. My email at work is
(e-mail address removed) (work)
(e-mail address removed)
Good Luck,
Bill
