Hi Kevin.
Thanks for that... yes I am getting the picture.

Regards,

Gerard



>Gerard,
>
>I’m going to give you three sets of posts, one at a time.
>
>You asked the following question...
>
>I found myself using about 8 lines of verbose code in c# (set connection string, set select command, set the dataadapter, set Dataset etc.) for commands in VFP that would have been maybe 2 (Use MyTable Alias 'MYT)
>
>The main reason for the question was maybe there's a better, different way of doing things. I have a farly big app to convert/rewrite (300 forms) and I did not want to have to write 8 lines of code X 300 if I could maybe do this more economically.
>
>...so I figured the best way to start was by building some reusable code, so that you only have to deal with tasks like setting connection string, setting the adapter, etc., only once.
>
>This first post builds a small but useful data access component [DAC] with a base method for retrieving data into a dataset, with a variable number of parameters.
>
>The next two posts will cover INSERT/UPDATE operations in the database, and the third will cover things you can do with a dataset once you get the data back [i.e. the equivalent of SEEK/REPLACE statements that you might do against VFP cursors]
>
>The idea of a DAC is to isolate and abstract all the ‘work’ that your application has to do, when talking to SQL Server. Things like creating a connection string, making the connection, retrieving data, etc. So let’s start by creating a simple .NET project/class, called MyDataAccess.
>
>Our first goal is to create a general method that executes a stored procedure [or a constructed query, if you’d prefer to use SPT]. We’ll call this method RetrieveFromSP. The method does the following…
>
>- It receives the name of a stored proc [or query string], a collection of SQL parameters, and a custom timeout, if you need to set a specific timeout.
>
>- It opens a SQL connection
>
>- It opens up data adapter and associates it with the stored procedure
>
>- It loops through the collection of parameters you passed, and sets them into the adapter
>
>- It executes the stored proc, fills a dataset with the results, and passes the results back .
>
>Now, this will become a little clearer, once we show a block of code that actually utilizes this component. But for now, he’re’s the underlying reusable data access component, a .NET class project with a single class called MyDataAccess.cs.
>
>

>
>using System;
>using System.Data;
>using System.Data.SqlClient;
>using System.ComponentModel;
>using System.Collections;
>using System.Collections.Generic;
>using System.Collections.ObjectModel;
>
>
>
>namespace MyBaseClasses.MyDataAccess
>
>{
>
>    public class MyDataAccess
>
>    {
>
>        public MyDataAccess()
>        {
>
>            // TODO: Add constructor logic here
>
>        }
>
>        private SqlConnection GetConnection()
>
>        {
>            return new SqlConnection(this.BuildDatabaseConnectionString  ());
>        }
>
>
>
>        public string BuildDatabaseConnectionString()
>        {
>
>            string cUserID, cPassWord, cDataSource, cInitialCatalog;
>            int nStandardTimeout;
>            bool lAsyncProcessing, lMultipleActiveResultSets;
>
>             // Here is where you’d have your code to handle the connection string….
>             // You WOULDN’T do exactly what I’m doing here [hard-coding the values]...you'd handle it by
>             // reading it out of an encrypted file, out of a config file, etc.
>
>             cUserID = "KEVIN";
>            cPassWord = "KEVINPW";
>            cDataSource = "MYDBSERVER";
>            cInitialCatalog = "MYDATABASE";
>            nStandardTimeout = 30;
>            lAsyncProcessing = true;
>            lMultipleActiveResultSets = true;
>
>
>            SqlConnectionStringBuilder oStringBuilder = new SqlConnectionStringBuilder();
>            oStringBuilder.UserID = cUserID;
>            oStringBuilder.Password = cPassWord;
>            oStringBuilder.InitialCatalog = cInitialCatalog;
>            oStringBuilder.DataSource = cDataSource;
>            oStringBuilder.ConnectTimeout = nStandardTimeout;
>            oStringBuilder.MultipleActiveResultSets = lMultipleActiveResultSets;
>            oStringBuilder.AsynchronousProcessing = lAsyncProcessing;
>
>            return oStringBuilder.ConnectionString;
>
>        }
>
>
>        public DataSet RetrieveFromSP(string cStoredProc, List<SqlParameter> oParmList, int nCommandTimeOut)
>        {
>
>            DataSet dsReturn = new DataSet();
>            SqlConnection oSqlConn = this.GetConnection();
>            SqlDataAdapter oSqlAdapter = new SqlDataAdapter(cStoredProc, oSqlConn);
>
>            oSqlAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
>
>            if (nCommandTimeOut > 0)   // custom timeout
>                oSqlAdapter.SelectCommand.CommandTimeout = nCommandTimeOut;
>
>            // this might be one parm, might be 3, etc.
>            foreach (SqlParameter oParm in oParmList)
>                oSqlAdapter.SelectCommand.Parameters.Add(oParm);
>
>
>             oSqlAdapter.Fill(dsReturn);    // this actually executes the stored proc
>             oSqlConn.Close();
>
>            return dsReturn;
>
>        }
>
>    }
>
>}
>
>

>
>
>OK, now we want to use it. Since we have this nice base method in our DAC called RetrieveFromSP, we can create a class that derives from our DAC, to give immediate access to RetrieveFromSP.
>
>Suppose we have a stored procedure to return the results for an aging report. It has three parameters…an ‘as of’ date, a boolean for whether we want to show details or summary data, and a string containing a list of clients. However, this instead could be the results for another stored procedure that takes 5 parameters…or two….or none. The idea is to build a collection of SQL parameters and send to RetrieveFromSP, which will parse them out.
>
>So, in a separate project, let’s create a class that derives from our DAC. You’ll need to set a reference to the DLL that was created, when you built the DAC project above.
>
>

>using System;
>using System.Collections.Generic;
>using System.Text;
>using System.Data;
>using MyBaseClasses.MyDataAccess;
>
>
>
>namespace MyApplication.DataAccess
>
>{
>
>    public class daAgingReport :  MyDataAccess
>
>    {
>        public DataSet GetAgingReportData(DateTime dAsOfDate, bool lDetails, string cClientList)
>        {
>            DataSet dsResults = new DataSet();
>
>            string cSPName = "[dbo].[GetAgingReceivables]";
>
>            List<SqlParameter> oParms = new List<SqlParameter>();
>            oParms.Add(new SqlParameter("@cCustomerList", cClientList));
>            oParms.Add(new SqlParameter("@dAgingDate", dAsOfDate));
>            oParms.Add(new SqlParameter("@lShowDetails", lDetails));
>
>            dsResults = this.RetrieveFromSP(cSPName,oParms,20);   // timeout after 20 seconds
>
>            return dsResults;
>
>        }
>
>
>    }
>}
>
>

>
>
>
>Notice how all we have to do is pass the name of the stored proc, and a collection of parameters.
>
>There’s a lot more you’d want to do to this….in the form of error checking….evaluating the parameters at the end to check for SQL injection, etc. In your application’s data access, you may want to abstract this out further, to store all your SP names and parameters in some kind of class repository, depending on how many you have, what level of abstraction you want, etc. But hopefully this will get your started and give you some ideas.
>
>However you do it….it’s important, in building an app, to think about how you’re going to structure [i.e. architect] your classes and data access. While the ultimate questions [equivalent of APPEND/SEEK/etc] are important and need answers, it's important to handle the context of using htem.
>
>
>I’ll move on to next one, which will include what you'd need to do if you wanted to override a base method….just want to make sure you’re OK up to this point.
>
>Kevin
>
>
>
>
>
>
>
>
>
>
>
>
>