using SqlDataSource in code

Sometimes there is a need to use a SqlDataSource object in the code...

ASPX:
<asp:SqlDataSource ID="sqlSelectUsers" runat="server"
ConnectionString="<%$ConnectionStrings:MainConnectionString %>"
ProviderName="<%$ ConnectionStrings:MainConnectionString.ProviderName %>"
SelectCommandType="StoredProcedure"
    SelectCommand="spSelectUsers">

<SelectParameters>
<asp:Parameter Name="RequestingUserID" Type="Int16" />
</SelectParameters>
</asp:SqlDataSource>


VB:
sqlSelectUsers.SelectParameters("RequestingUserID").DefaultValue = 2 //hard coded value for demo purposes
Dim dv As DataView = sqlSelectUsers.Select(DataSourceSelectArguments.Empty)
Dim dt As DataTable = dv.ToTable()

This will result in a DataTable for manual consumption.

Another way to parse CSV parameters for values not necessarily in a table

-- http://www.projectdmx.com/tsql/tblnumbers.aspx
CREATE TABLE dbo.Nbrs(n INT NOT NULL IDENTITY) ;
INSERT dbo.Nbrs DEFAULT VALUES ;
WHILE SCOPE_IDENTITY() < 500 INSERT dbo.Nbrs DEFAULT VALUES ;

-- http://www.projectdmx.com/tsql/sqlarrays.aspx

DECLARE @p VARCHAR(50)
SET @p = 'ALFKI,LILAS,PERIC,HUNGC,SAVEA,SPLIR,LONEP,GROSR'

SELECT SUBSTRING( ',' + @p + ',', n + 1, CHARINDEX( ',', ',' + @p + ',', n + 1 ) - n - 1 ) AS "value"
FROM Nbrs
WHERE SUBSTRING( ',' + @p + ',', n, 1 ) = ',' AND n < LEN( ',' + @p + ',' ) ;

drop table Nbrs    

Convert a SQL CSV Parameter into a table

Consider the need to pass in a CSV parameter like this:

DECLARE @UserIDs_CSV VARCHAR(50)
SET @UserIDs_CSV = '1,2,4,5,8,9'

If that CSV parameter happens to correlate to a table, like in this case, Users... Use a query like this to convert the CSV parameter into a temp table...

SELECT UserID
into #SelectedUserIDs
FROM Users
WHERE CHARINDEX(
',' + cast(UserID as varchar(10)) + ',',
',' + @UserIDs_CSV + ','
) > 0

So now just use the temp table as you need for future queries...

select * from #SelectedUserIDs

Credit goes to http://www.projectdmx.com/tsql/sqlarrays.aspx

Getting a quicker SQL row count

Credit goes to Rick Caminiti at http://rickcaminiti.com/tips/alternative-efficient-tables-rowcount-sql/

SELECT COUNT(*) statements make a full table scans to return the total table’s row count, it can take a lot of time and a lot of “energy” for a large table.

There is another way to determine the total row count in a table by using the sysindexes system table. There is a column (ROWS) in it that contains the total row count for each table in your database.

You can use the syntax below to speed up the query by several times.  You can see this by setting statistics on, as shown below.




SET STATISTICS IO ON
GO

SELECT count(*) FROM tbTest
GO

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2
GO

SET STATISTICS IO OFF
GO

I tried my own test, and here are my results:


(1 row(s) affected)
Table 'myTable'. Scan count 1, logical reads 116, physical reads 1, read-ahead reads 176, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table 'sysidxstats'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.