This week we panic about the internet killing all the humans, look at the wisdom of Intel and Nokia’s cosy tie-up, marvel at plug-sized PCs, how anyone in their right mind would give their Facebook details to their employer, and look at a super little USB-connected monitor.
PC Pro Podcast 61
This week the UK’s Digital Britain report hits the agenda; we look at the joy of Microsoft’s EU-prompted actions; Opera re-invents the web; Google and Microsoft do some sparring in the playground; and we look at the smallest fully fledged PC we’ve ever seen.
ASP.NET – Returning multiple rows as a DataRowCollection easily
We found whilst programming that on many occasions we need to return a recordset in an similar fashion to what we would have done in Classic ASP – and whilst there are many ways to do this, we found that creating the following functions in a .vb file that we can then call is the fastest way. If we are only returning one row, we have a different function that simply returns a single row as a DataRow, as opposed to the collection of rows. Additionally we have removed all connection string detail out of the individual pages, and into a single place – the web.config file.
To call function (one possible usage)
SQLString = “SELECT id, firstnames, lastname FROM TestTable”
For each dr as DataRow in new dbrecordhelper().GetDataRows(SQLString)
‘Do something here with each item
‘e.g. Create a list of firstnames in a label called lblFirstNames
Me.lblFirstNames.Text += dr(“firstnames”).ToString & “<br />”
Next
——————— APP_Code\dbrecordhelper.vb ————————–
‘ – The functions below (we stored these in a file called “DBRecordHelper.vb” in the App_Code folder)
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Configuration
Public Class DBRecordHelper
Public Function GetDataRows(ByVal SQLString As String) As DataRowCollection
‘Declare variables
Dim sqlConn As SqlConnection
Dim sqlComm As SqlCommand
Dim dataAdapt As SqlDataAdapter
Dim ds As DataSet
‘Declare the dataset
ds = New DataSet
‘Connect to SQL database
sqlConn = New SqlConnection(m_connString)
sqlConn.Open()
‘Build up SqlCommand
sqlComm = New SqlCommand(SQLString, sqlConn)
sqlComm.CommandType = CommandType.Text
‘Create the DataAdapter
dataAdapt = New SqlDataAdapter(sqlComm)
dataAdapt.Fill(ds)
Dim dt As DataTable = ds.Tables(0)
If dt.Rows.Count > 0 Then
GetDataRows = dt.Rows
Else
GetDataRows = Nothing
End If
‘Close connection
sqlConn.Close()
ds = Nothing
dt = Nothing
sqlConn = Nothing
sqlComm = Nothing
dataAdapt = Nothing
End Function
‘ Constructors
Public Sub New()
Me.connString = ConfigurationManager.ConnectionStrings("DataConnectionString").ConnectionString
End Sub
Public Sub New(ByVal ConnectionString As String)
Me.connString = ConnectionString
End Sub
‘ Connection string variable and property
Private m_connString As String
Public Property connString() As String
Get
Return Me.m_connString
End Get
Set(ByVal value As String)
Me.m_connString = value
End Set
End Property
End Class
——————— Web.config ————————–
..and the SQLConnection is specified ONCE in the Web.config file
<connectionStrings>
<add name="DataConnectionString" connectionString="Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=SQLUSERNAME;Password=SQLPASSWORD" providerName="System.Data.SqlClient" />
</connectionStrings>