Thank you SOOOOOO much Bonnie. I truly appreciate this detailed suggestion. It is now apparent to me how this could work and how I will implement it. Thank you for sharing your time and talent.
Glenn
>Glenn,
>
>
>>Thanks Bonnie, but how would you code this example in VB.Net/ ADO.Net? At this point I am not pursuing MM.Net to solve this problem but just looking for a reasonable code sample which will give me a "bootstrap" so that I may "get" the concepts of Objectified Data Processing.<>
>First, what I would do would be to have a Stored Proc in Sql Server to do the bulk of the work. This Stored Proc would perform either the update or an insert. It would look like this:
>
>CREATE PROCEDURE SP_UpdateMyTable
> @id int = NULL,
> @field1 int = NULL,
> @field2 int = NULL
>AS
> SET NOCOUNT ON
>
> IF EXISTS (SELECT id FROM MyTable WHERE id = @id)
> UPDATE MyTable
> SET field1 = ISNULL(@field1, field1),
> field1 = ISNULL(@field2, field2)
> WHERE id = @id
> ELSE
> INSERT MyTable
> (id,
> field1,
> field2)
> SELECT @id,
> @field1,
> @field2)
> SELECT @activitykey = SCOPE_IDENTITY()
>
>I've not used DataReaders, but in this case, it would probably be faster than reading the VFP table into a DataSet. But either way (DataSet or DataReader), you would need to iterate through each row, set parameters and call the Stored Proc. I'll show you how to do it with a DataSet because that's what I'm more familiar with (but the same principle applies). Sorry that this code is in C#, but you can convert it to VB using one of the online converters.
>
>Once you have either your DataSet or your DataReader:
>
>SqlCommand sc = new SqlCommand("SP_UpdateMyTable", MySqlConnection);
>sc.CommandType = CommandType.StoredProcedure;
>MySqlConnection.Open()
>
>foreach(DataRow row in MyVFPData.Tables[0].Rows)
>{
> this.SetParameters(sc, MyVFPData.Tables[0], row);
> sc.ExecuteNonQuery();
>}
>
>MySqlConnection.Close();
>
>...
>
>// This could be in the above for loop, but I broke it out for clarity
>// (plus, I would do it this way anyway, since this type of method is
>// typically in my DataAccess base class).
>
>public void SetParameters(SqlCommand sc, DataTable Table, DataRow Row)
>{
> sc.Parameters.Clear();
> for (int i = 0; i < Table.Columns.Count; i++)
> {
> if (Row[i] != System.DBNull.Value)
> {
> sc.Parameters.Add("@" + Table.Columns[i].ColumnName, Row[i]);
> }
> }
>}
>>
>~~Bonnie
