In this quick little post, I would like to document how to travers a SQL SELECT statement to return back a BIT/BOOLEAN. Suppose, you want to return true of false, based on a condition and your application can simply read this BIT/BOOLEAN and show appropriate view/response to end user.
WHEN [Account].Balance - @Value > 0
THEN 1 ELSE 0
END AS Bit)
FROM [dbo].[UserAccounts] [Account]
WHERE [Account].[UserId] = 13
The above statement will return a nice little one column ‘MoneyAvailable’ with a 1 or a 0 to denote if that account has available balance or not. Good stuff.
In this post, I am writing how to employ and utilise Asp.Net Connection Strings in when you don’t have the all-important Web.Config file at your disposal and you are disabled from using the Asp.Net ConfigurationManager class to establish the connection string.
Please review the link below to ensure you are using right Connection String format fitting your development environment and SQL instance.
For establishing SQL Server Connection, running SQL Query / Stored Procedure you would require a Connection String. The code below uses a declarative connection string where Web.Config is not available.
Dim SqlConnectionString As String = "Server=SQLSERVER;Database=DB;User ID=Username;Password=password"
Dim ConnStringSettingObj As ConnectionStringSettings = New ConnectionStringSettings("constr", SqlConnectionString)
Dim path As String = ConnStringSettingObj.ConnectionString
Dim conn As SqlConnection = New SqlConnection(path)
Dim cmdText As String = "StoredProcedure_OR_SQL_Query"
Dim cmd As SqlCommand = New SqlCommand(cmdText, conn)
cmd.CommandType = CommandType.StoredProcedure
Dim _Param As SqlParameter = New SqlParameter("_SqlArgumentIfAny", SqlDbType.VarChar)
_Param.Value = "something"
' If data return is required / otherwise ignore
Dim adaptr As SqlDataAdapter
Dim ds As New DataSet
adaptr = New SqlDataAdapter(cmd)
Catch ex As Exception
Throw New ConfigurationErrorsException
On this quick post, I want to memo how I used a SQL Temporary Table to build a temporary data store of half hour time slots within a 24 hour day on either a selected date or current date.
First I create a temporary table, and then I use two DateTime variables (Timeslots & Counter). Then using the DateAdd() SQL Function, I set the variables to midnight 00:00 until 00:00 next day for a given date or current date.
For example, 21 Dec 2012 00:00 am to 22 Dec 2012 00:00 am to get a perfect 24 hours
DROP TABLE #Times
CREATE TABLE #Times(HalfHourGaps DateTime)
DECLARE @TimeSlots DateTime
DECLARE @Counter DateTime
SET @TimeSlots = getdate()
SET @Counter = getdate()
SET @TimeSlots = dateadd(dd, 0, datediff(dd, 0, @TimeSlots))
SET @Counter = dateadd(dd, 1, datediff(dd, 0, @Counter))
WHILE (@TimeSlots <= @Counter)
INSERT INTO #Times(@TimeSlots)
SET @TimeSlots = dateadd(minute, 30, @TimeSlots)
SELECT HalfHourGaps FROM #Times
Now, when I obtained 30 minutes timeslots in temporary table, I can now check against any existing DateTime column in SQL table to see if datetime column value exists or matches in this temporary table.
This is how you can use SQL Temp Tables for doing some operations that might require a temporary table. A temporary table can be useful if you just want to have a data store of values you just require temporarily and not permanently sitting as a dedicated SQL table.
In this example, I am creating a temporary SQL table called tb1 with one column of type integer that will hold values from 0 to 10. This can be handy if you want to compare values either returned from a SQL Function or SQL Stored Procedures. You can also use temporary table to derive some values without actually changing the values in a SQL Table.
DROP TABLE #tempo1
CREATE TABLE #temp01(ID int)
DECLARE @i AS int
SET @i = 0
WHILE (@i < 10)
INSERT INTO #temp01 VALUES(@i)
SET @i += 1
SELECT * FROM #temp01
While creating stored procedures, it is imperative you name them intuitively corresponding to the process they do.
I have learnt that a good naming convention really makes it easy to find the stored procedure you are looking for and for other team of developers to debug, modify or extend your existing stored procedures.
Needless to say, good naming convention is also best practice in software development process.
Here is how I like or prefer to follow
Here is an example,
Another common practice is to add sp (stored procedure) or something bespoke instead in the front, followed by object and then full action name for instance,
I suppose the best way is to have a naming convention than to not have one.
Sometimes, you would want to obtain only the ‘date’ part of a SQL datetime timestamp column.
Normally, your datetime column will hold dates in the following format.
You can do the following to remove its time section
SELECT CAST(YourColumnName AS varchar(12)) AS DesiredColumnName from YourTableName where YourColumnName > ’2012/05/01′
This will result in the following
Feb 21 2002
It renders date in a more readable format, great when presenting dates on your website using either GridView or Repeater control.
Hope this is helpful.