Using a DataTable instead of a DataReader

G

Guest

I've gotten quite comfortable using a DataReader to grab a single value from
a table
as in the following:

Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim dr As SqlDataReader
Dim conMembers As SqlConnection =
MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings
WHERE [Address] = @NUID", conMembers)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value = strNUID

conMembers.Open()
dr = cmd.ExecuteReader(CommandBehavior.SingleRow)
'check if the clinical value is 1
If dr.Read Then
'if it is then the user admin privileges
'find out the level

Return dr("Level")

Else 'if no user is found - it means the user doesn't have any admin
rights
Return 0
End If

conMembers.Close()
conMembers.Dispose()
conMembers = Nothing
End Function

I would like to practice using DataTables but I need a little help getting
started- could someone tranlate this same functionality (above) for a
DataTable?
 
G

Guest

jonefer:

I prefer creating a strongly-typed dataset which contains data tables, etc.
In VS 2005, you can do this by adding an existing item to your project.
Strongly typed datasets allow you to reference tables and columns by their
name (DSTest.TblTest). A dataset created in code doesn't allow for this
(DSTest.Tables("TblTest").

Once the dataset and table has been created, you can use a data adapter to
fill your datatable. Make the following changes to your code:

Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim da As SqlDataAdapter
Dim conMembers As SqlConnection = MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings WHERE
[Address] = @NUID", conMembers)
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value = strNUID
da.selectcommand=cmd
da.fill(mydataset.mydatatable)

You do not have to open the SQLConnection. The DA will open and close the
connection. If you were to open it, you would have to close it. The DA leaves
the connection in the state that it found it in.

Good luck,

Daniel Brittain Dugger
 
G

Guest

That was very helpful and informative. Thank you.

Daniel Brittain Dugger said:
jonefer:

I prefer creating a strongly-typed dataset which contains data tables, etc.
In VS 2005, you can do this by adding an existing item to your project.
Strongly typed datasets allow you to reference tables and columns by their
name (DSTest.TblTest). A dataset created in code doesn't allow for this
(DSTest.Tables("TblTest").

Once the dataset and table has been created, you can use a data adapter to
fill your datatable. Make the following changes to your code:

Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim da As SqlDataAdapter
Dim conMembers As SqlConnection = MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings WHERE
[Address] = @NUID", conMembers)
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value = strNUID
da.selectcommand=cmd
da.fill(mydataset.mydatatable)

You do not have to open the SQLConnection. The DA will open and close the
connection. If you were to open it, you would have to close it. The DA leaves
the connection in the state that it found it in.

Good luck,

Daniel Brittain Dugger





jonefer said:
I've gotten quite comfortable using a DataReader to grab a single value from
a table
as in the following:

Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim dr As SqlDataReader
Dim conMembers As SqlConnection =
MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings
WHERE [Address] = @NUID", conMembers)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value = strNUID

conMembers.Open()
dr = cmd.ExecuteReader(CommandBehavior.SingleRow)
'check if the clinical value is 1
If dr.Read Then
'if it is then the user admin privileges
'find out the level

Return dr("Level")

Else 'if no user is found - it means the user doesn't have any admin
rights
Return 0
End If

conMembers.Close()
conMembers.Dispose()
conMembers = Nothing
End Function

I would like to practice using DataTables but I need a little help getting
started- could someone tranlate this same functionality (above) for a
DataTable?
Click to expand...
Click to expand...
 
G

Guest

Not A Problem. I was very hesitant to move to .net and now I am so happy that
I did.

Let me know if you need anything else.

Daniel

jonefer said:
That was very helpful and informative. Thank you.

Daniel Brittain Dugger said:
jonefer:

I prefer creating a strongly-typed dataset which contains data tables, etc.
In VS 2005, you can do this by adding an existing item to your project.
Strongly typed datasets allow you to reference tables and columns by their
name (DSTest.TblTest). A dataset created in code doesn't allow for this
(DSTest.Tables("TblTest").

Once the dataset and table has been created, you can use a data adapter to
fill your datatable. Make the following changes to your code:

Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim da As SqlDataAdapter
Dim conMembers As SqlConnection = MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings WHERE
[Address] = @NUID", conMembers)
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value = strNUID
da.selectcommand=cmd
da.fill(mydataset.mydatatable)

You do not have to open the SQLConnection. The DA will open and close the
connection. If you were to open it, you would have to close it. The DA leaves
the connection in the state that it found it in.

Good luck,

Daniel Brittain Dugger





jonefer said:
I've gotten quite comfortable using a DataReader to grab a single value from
a table
as in the following:

Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim dr As SqlDataReader
Dim conMembers As SqlConnection =
MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings
WHERE [Address] = @NUID", conMembers)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value = strNUID

conMembers.Open()
dr = cmd.ExecuteReader(CommandBehavior.SingleRow)
'check if the clinical value is 1
If dr.Read Then
'if it is then the user admin privileges
'find out the level

Return dr("Level")

Else 'if no user is found - it means the user doesn't have any admin
rights
Return 0
End If

conMembers.Close()
conMembers.Dispose()
conMembers = Nothing
End Function

I would like to practice using DataTables but I need a little help getting
started- could someone tranlate this same functionality (above) for a
DataTable?
Click to expand...
Click to expand...
Click to expand...
 
C

Cor Ligthert [MVP]

Jonefer,

I would not start with a strongly typed dataset, this addition to your code
gives you a datatable.
Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim dr As SqlDataReader
Dim conMembers As SqlConnection =
MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings
WHERE [Address] = @NUID", conMembers)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value = strNUID

conMembers.Open()
Click to expand...
dim dt as datatable
dim da as new SqlDataAdapter(cmd,conMembers)
da.fill(dt)

I hope this helps,

Cor
?
 
C

Cor Ligthert [MVP]

doh

dt as New datatable

Cor

Cor Ligthert said:
Jonefer,

I would not start with a strongly typed dataset, this addition to your
code gives you a datatable.
Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim dr As SqlDataReader
Dim conMembers As SqlConnection =
MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings
WHERE [Address] = @NUID", conMembers)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value =
strNUID

conMembers.Open()
Click to expand...
dim dt as datatable
dim da as new SqlDataAdapter(cmd,conMembers)
da.fill(dt)

I hope this helps,

Cor
?
Click to expand...
 
G

Guest

I forgot to mention, the portion of the DataTable vs the Reader -that I
struggle with is this part:
how do I check and return the value of a field as in this example below:

dr = cmd.ExecuteReader(CommandBehavior.SingleRow)
'check if the clinical value is 1
If dr.Read Then
'if it is then the user admin privileges
'find out the level

Return dr("Level")


Cor Ligthert said:
doh

dt as New datatable

Cor

Cor Ligthert said:
Jonefer,

I would not start with a strongly typed dataset, this addition to your
code gives you a datatable.
Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim dr As SqlDataReader
Dim conMembers As SqlConnection =
MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings
WHERE [Address] = @NUID", conMembers)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value =
strNUID

conMembers.Open()
Click to expand...
dim dt as datatable
dim da as new SqlDataAdapter(cmd,conMembers)
da.fill(dt)

I hope this helps,

Cor
?
Click to expand...
Click to expand...
 
C

Cor Ligthert [MVP]

Jonefer,

If you need one row, than use a datareader; if you need one field; than use
cmd.ExecuteScalar if you need a datatable; use a DataAdapter
(datataset/datatable).

I hope this gives your answer,

Cor

jonefer said:
I forgot to mention, the portion of the DataTable vs the Reader -that I
struggle with is this part:
how do I check and return the value of a field as in this example below:

dr = cmd.ExecuteReader(CommandBehavior.SingleRow)
'check if the clinical value is 1
If dr.Read Then
'if it is then the user admin privileges
'find out the level

Return dr("Level")


Cor Ligthert said:
doh

dt as New datatable

Cor

Cor Ligthert said:
Jonefer,

I would not start with a strongly typed dataset, this addition to your
code gives you a datatable.

Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long
Dim dr As SqlDataReader
Dim conMembers As SqlConnection =
MembershipDB.GetMembershipConnection()
Dim cmd As New SqlCommand("SELECT [Level] FROM
dbo.user_settings
WHERE [Address] = @NUID", conMembers)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value =
strNUID

conMembers.Open()
dim dt as datatable
dim da as new SqlDataAdapter(cmd,conMembers)
da.fill(dt)

I hope this helps,

Cor
?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Do I need to Close DataReader in USING? 2
Datareader to Datatable gets stuck... 3
Filling a DataTable with a DataReader 4
DataReader with 1 record... 3
Use datareader to get a value for UserNo 5
ADO or String.Format bug ? ...... 4
I want to take List of Datatables with ADODB using VB.Net.. 3
Should I close a datareader returned using daab? 7

Top