Question of General Data Access Function...

[User 4122428]

I have a growing number of functions that look like some variation of the below, and it seems like there's got to be a way to generalize the whole process.   Any suggestions? Basically what I want is to load datatables from stored procedures.       Public Function UsersGetIconList(ByVal UserID As String, ByVal SPName As String, ByVal ParamArray args() As String) As DataTable             Dim DS As New DataSet("ResultSet")             Dim dt As DataTable = DS.Tables.Add("ResultTable")             Dim CON As New SqlConnection(strConnString)             Dim CMD As New SqlCommand(SPName, CON)             Dim reader As SqlDataReader             CMD.CommandType = CommandType.StoredProcedure             Try                   'Use Parameters defined in Stored Procedure to pass on Variables.                   Dim Param As SqlParameter = CMD.Parameters.Add("@UserID", SqlDbType.VarChar, 36)                   Param.Value = UserID                   Param = CMD.Parameters.Add("@User_Icon_FileLocation", SqlDbType.NChar, 256)                   CMD.Parameters("@User_Icon_FileLocation").Direction = ParameterDirection.Output                   'Open Database and Execute Query as defined in Stored Procedure.                   CON.Open()                   reader = CMD.ExecuteReader()                   dt.Load(reader)             Catch ex As Exception                   'MsgBox(ex.Message)             Finally                   CON.Close()    &nbs

[N a v a n e e t h]

You need a generic method that gives you a DataTable after executing a stored-procedure? If yes, how about a function signature like Public Function GetDataTable(ByVal SPName As String, ByVal ParamArray parameters() As SqlParameter) As DataTable You need to create and pass SqlParameter instances. Inside this function, add all parameters to the command and fill a datatable. Using strongly typed data structures is the preferred method. If you want to use DataTable, use typed DataTable and enjoy the advantages of type safety. I'd personally avoid DataTable or DataSet. If I have to use, I prefer typed ones. Usually data access methods should return typed POCO (plain old CLR object) or DTO (data transfer object) objects. You will have a common method that will do data access and return a SqlDataReader or IDataReader if you care about extending to other databases. Your UsersGetIconList() method can utilize the common method and get a data reader. Using this reader, create your own DTO object and return.

Navaneeth How to use google | Ask smart questions

[User 4122428]

Thanks N a v a n e e t h. Of course now I'm thoroughly confused. I'm a bit beyond my comfort zone here.   I know how to deal with datatables, and it seems like Stored Procedures are something that I definately need to learn about.   I just bought a SQL Server Dev book, so hopefully that'll help. It seems to me like getting data into the Stored Procedure is easier than getting it back out, and I think I'm thinking about something dreadfully wrong about the process...

[Christian Graus]

Member 4125480 wrote:

It seems to me like getting data into the Stored Procedure is easier than getting it back out, and I think I'm thinking about something dreadfully wrong about the process...

You push data into a stored proc as parameters, then you call it, and the data comes out. The data coming out is really no different to if you just ran SQL that you mashed together.

Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.