SQLHelper Help - New to C#

Mar 5, 2011 at 8:43 AM

I used SQLHelper from years ago, yes the old version, I have quite a few projects with it, and now I'm a clueless as to how to use the new SQLHelper

old way... vb, never saw c#...

dim ds as dataset = SQHelper.ExecuteDataset(cnn, cmd,...)

if Not IsDbNull(ds) then....

Please for me to show a sample soup to nuts SQLHelper cmd in c# using my pseudo; usp_Main @action ,@recid ,@param1 ,@param2

Or, perhaps a link to a good sample...

TIA

JeffP...

Mar 8, 2011 at 5:15 PM

I'm guessing you're talking about the SqlHelper class from the Data Access Application Block. The code found in my utility library here has nothing to do with it and was developed without ever knowing it existed. You could use mine but it's a bit different (hence there isn't an ExecuteDataset function and in fact my code doesn't deal with data sets). I've never used the SqlHelper class from Microsoft so I can't really help you. I've always built on the ADO.Net stuff.

Mar 8, 2011 at 6:13 PM

Thanks for your reply.

The vb version of SQLHelper uses ADO.Net, but eliminates the need to do things like add parameters, you just pass it all in similar to executing a sProc in TSQL

I use datasets because I get many tables back, table(0) contains meta data/and/or error codes & messages, table(1,..n..) contains my data which can be instructions on how to build the controls that will render the data, dynamic web pages that have an admin interface or even none, but are controled by another program inherited lookups and buiness rules, so I need many tables.

Anyway, I've given up finding a c# version of SQLHelper :(

I am still looking an example of how to use your block in c#; as I'm new to c# (just a couple of weeks in) and have been focusing most of my time in XSLT/Saxon in c#.

TIA

JeffP...

Mar 8, 2011 at 8:18 PM

Well, if you use my code it will look something like this:

            using (Utilities.SQL.SQLHelper Helper = new Utilities.SQL.SQLHelper("usp_Main", "CONNECTION STRING", System.Data.CommandType.StoredProcedure))
            {
                try
                {
                    Helper.Open();
                    Helper.AddParameter("@action", ActionValue, System.Data.SqlDbType.Int);
                    Helper.AddParameter("@recid", RecIDValue, System.Data.SqlDbType.Int);
                    Helper.AddParameter("@param1", Param1Value, System.Data.SqlDbType.Int);
                    Helper.AddParameter("@param2", Param2Value, System.Data.SqlDbType.Int);
                    Helper.ExecuteReader();
                    while (Helper.Read())
                    {
                        int Value1=Helper.GetParameter("Value1", 0);
                        int Value2 = Helper.GetParameter("Value2", 0);
                    }
                }
                catch { }
                finally { Helper.Close(); }
            }
With the connection string needing to be replaced. Also, since I didn't know the db type of the various items, you'll need to set that (note that for varchar/nvarchar, you switch out the db type with the varchar length and in order to do nvarchar/varchar max, enter in -1). When getting the values, you can set a default value so you never have to deal with null values unless you want to. But that is a basic example of using it (I'm going to be adding features to the class fairly soon, so I'll look into adding dataset support to make things like what you do a bit easier and also look into the old vb SQLHelper and see if I can get some similar functionality, but for now this is the best I can do for you).

May 13, 2011 at 2:24 PM

Note that I've updated SQLHelper so you could actually do the following (which would be closer to what you are used to):

using(Utilities.SQL.SQLHelper Helper=new Utilities.SQL.SQLHelper("usp_Main","CONNECTION STRING",System.Data.CommandType.StoredProcedure))
{
    try
    {
        Helper.Open();
        Helper.AddParameter("@action",ActionValue);
        Helper.AddParameter("@recid",RecIDValue);
        Helper.AddParameter("@param1",Param1Value);
        Helper.AddParameter("@param2",Param2Value);
        DataSet MyDataSet=Helper.ExecuteDataSet();
    }
    catch{ }
    finally{Helper.Close();}
}