>We are finally taking the .NET plunge, and I have been pleased with my progress so far in building generic data access objects and having them work well against DBF and SQL Server data (which is a requirement).
>
>I have essentially wrapped ADO.Net objects with utility objects (one for DBF and one for SQL Server) that mimic things like SQLCONNECT(), SQLDISCONNECT, SQLEXEC(), etc. It has worked well up until I am trying to make parameterized queries. I got very spoiled by VFP's SQL Passthrough syntax sugar:
>
>INSERT INTO mytable VALUES (?var1, ?var2, ?var3)
>
>The "?" syntax saves all kinds of problems related to data types and such. Of course, that syntax comes at the price of not being data safe, etc.
>
>In .NET, I am quickly having to get used to the more-strict data typing. I like it, as it will save tons of run-time errors even if the code is a bit harder to knock out up front. But I DO want my SQL statements to be type-agnostic. So far, it looks like .NET is going to require me to set up parameters by declaring their type name, type, and size. One cannot simply add a parameter name and assign it...SqlParameter and OleDbParameter require data types as they are created.
>
>That doesn't make sense to me. If I change my data structure on the database backend from varchar(10) to varchar(15), I don't want to have to go lengthen my parameters throughout my code. I want a generic parameter type that I can assign a value to and have it pass through to the database (like the "?" does in VFP SPT). I'll send it right, so I don't need SQL Server or .NET being a bugger about it.
>
>I have been reading about frameworks, factory classes, interfaces, etc, but nothing seems straightforward. At this point it would be easier for me write a function that simply builds a SQL statement as a string instead of even using parameters. Let's face it: the bottom line on parameters is simply that they make it easier to pass various data types without worrying about single-quotes, {} for VFP dates, etc. There's no magic there, the VFP "?" just saves a lot of tedium.
>
>Can anyone tell me how to relieve that tedium in .NET as well? I just want to send straight SELECT, INSERT, UPDATE, and DELETE statements. That's what we have been using and will continue to use because they are easy, standard, and because we already have tons of statements written in VFP.
>
>Any help would be greatly appreciated!
I have methods from the data class for adding parameters. This one adds a parameter into a collection that I can later on include in my data provider object:
' Add a parameter
' expC1 Field
' expO1 Value
Public Function ParameterAdd(ByVal tcField As String, ByVal toValue As Object) As Boolean
Dim loParameter(1, 2) As Object
loParameter(1, 1) = tcField
loParameter(1, 2) = toValue
oParameters.Add(loParameter)
End FunctionThis one is used to add a parameterized field into a SQL command:
' Add a parameter in the SQL with a proper syntax depending on the backend
' expC1 Field
Public Function ParameterAddSQL(ByVal tcField As String) As String
Dim lcString As String = ""
' Different backends handle it differently
Select Case nBackend
' Visual FoxPro
Case 1
lcString = "?"
' SQL Server
Case 2
lcString = "@" + tcField
End Select
Return lcString
End FunctionSo, basically, you can build a SQL like this:
loDataProvider.ParameterAdd("Numero", nPrimaryKey)
If Not loDataProvider.SQLUpdate("SELECT Member.FirstName " + _
"FROM Member "+ _
"WHERE Member.Numero=" + loDataProvider.ParameterAddSQL("Numero")) Then
Return False
End IfSo, basically, if you would follow such an approach, your client wouldn't rely on the backend as your data class would do. As everything would go through it, you can then just build the required code in the data class to handle different ways of adding parameters, conditions, etc.
