Output Clause in SQL 2005 / 2008 / 2012

SQL Server 2005 and above has new Output clause.
This is very help to create a copy of what is inserted / updated / deleted.
Output clause has an access to inserted and deleted tables (like triggers) and the data can be copied to
table variable / temp table / permanent table.

Lets see an example:

Note: Table variable declaration need to be selected while running query.

Example using Insert statement:

create table customer (name varchar(100), joindate date)
go

declare @customer table (name varchar(100), joindate date)

-- Insert into table and also output to table variable
insert into customer (name, joindate)
output inserted.name, inserted.joindate into @customer 
values ('John', '01/01/2012')

-- check results
select * from customer 
select * from @customer 

VB Datatable SQL functions

Some DataTable Functions in VB.net

Create table in VB.net:

      Dim table As New DataTable("Orders")
      table.Columns.Add("OrderID", GetType(Int32))
      table.Columns.Add("OrderQuantity", GetType(Int32))
      table.Columns.Add("CompanyName", GetType(String))
      table.Columns.Add("Date", GetType(DateTime))

      Dim newRow As DataRow = table.NewRow()
      newRow("OrderID") = 1
      newRow("OrderQuantity") = 3
      newRow("CompanyName") = "NewCompanyName"
      newRow("Date") = "2012, 1, 31"

      ' Add the row to the rows collection.
      table.Rows.Add(newRow)

Plain Select

Private Sub GetAllRows()
    ' Get the DataTable of a DataSet. 
    Dim table As DataTable = DataSet1.Tables("Shippers")
    Dim rows() As DataRow = table.Select()

    Dim i As Integer
    ' Print the value one column of each DataRow. 
    For i = 0 to rows.GetUpperBound(0)
       Console.WriteLine(rows(i)("Name"))
    Next i
End Sub


Select with Expression

Private Sub GetFilteredRows()
    Dim table As DataTable = DataSet1.Tables("Books")
    ' Presuming the DataTable has a column named PublishedDate. 
    Dim expression As String
    expression = "PublishedDate > #1/1/10#"
    Dim foundRows() As DataRow

    ' Use the Select method to find all rows matching the filter.
    foundRows = table.Select(expression)

    Dim i As Integer
    ' Print column 0 of each returned row. 
    For i = 0 to foundRows.GetUpperBound(0)
       Console.WriteLine(foundRows(i)(0))
    Next i
End Sub

Multiple Filters 

Dim foundRows() As DataRow
foundRows()  = table.Select("Col1 = 'foo' AND Col2 = 'bar'")

Sort
Dim foundRows() As DataRow
' Sort descending by column named Column1
Dim sortOrder As String = "Col1 ASC" 
foundRows() = table.Select("Col1 = 'foo' AND Col2 = 'bar'", sortOrder)

Reference to Microsoft.SQLServer.ManagedDTS.dll - SQL Server 2012

To reference above dll from SQL 2012 Client tools, the existing project need to have framework of 4.0 or higher.

Also, need to add following code in config file under configuration tag. 
Make sure this is added after  <configsections> tag if any.

 <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
 </startup>

 
Else you will get error:

Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information   

Parse Excel file using vb.net

The following method will load excel file in datareader which will be inserted to Sql server via Sqlbulkcopy.


Imports Microsoft.Office.Interop
Imports System.Data.OleDb
Imports System.Data.SqlClient


Public Class clsLoadExcel

Function Process(ByVal filePath As String) As Boolean

Dim objExcelConn As _
New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")

Dim objQuery As String
Dim objCMD As OleDbCommand
Dim objDR As OleDbDataReader

Dim sqlConn As New SqlConnection
Dim sqlCmd As SqlCommand = New SqlCommand(sqlConn)
Dim sqlBCopy As New SqlBulkCopy(sqlConn)

sqlConn.ConnectionString = "data source=servername;Integrated security=true;Initial Catalog=db;"

objQuery = "SELECT * FROM [Sheet1$]" 'you can change your sheet name

Try
objCMD = New OleDbCommand(objQuery, objExcelConn)
objExcelConn.Open()          
objDR = objCMD.ExecuteReader

' open sql connection
sqlConn.Open()

' now write to sql
sqlBCopy.DestinationTableName = "TempTable"
sqlBCopy.WriteToServer(objDR)
       
Catch ex As Exception

Finally

If Not IsNothing(objDR) Then

objDR.Close()
End If

sqlConn.Close()
sqlBCopy.Close()
End Try

End Function

End Class

Read file in vb.net

One way

  Dim data As String

        Using sr As New StreamReader("C:\test.txt")
            Do
                data = sr.ReadLine()
                If Not data Is Nothing Then
                    ' do some parsing
                End If
                Console.WriteLine(data)
            Loop Until data Is Nothing
        End Using

2nd way - READ it ALL

Imports System
Imports System.IO

Class ReadALLFile
    Public Shared Sub Main()
        Try 
            Using sr As New StreamReader("TestFile.txt")
                Dim line As String
                line = sr.ReadToEnd()
                Console.WriteLine(line)
            End Using 
        Catch e As Exception         
            Console.WriteLine(e.Message)
        End Try 
    End Sub 
End Class

Read file Character by Character in vb.net

Dim currChar As Char
Dim data As String

  Using sr As New StreamReader("C:\test.txt")
            Do While sr.Peek <> -1
                currChar = Chr(sr.Read)
                data = data & currChar
                Console.WriteLine(data)
            Loop
   End Using

match ANYTHING using regular expression

Hi,

If you looking a way to match anything in .net using regular expression, the tool you need is:

(?s)

This mean anything including newline as well.

Here is how to use it:
(?s).*    -- comment: Replace angle brackets and mystring word with your word.

HTH.

Convert nvarchar to int in SQL

If your table has nvarchar column and there is integer data in this column , then inserting that data into int column type is not a problem.
But if data is float, then the data need to be converted to float datatype before cast to int to insert into int column type.

Example:
SELECT SUM(CAST(amount AS INT)),  title   FROM books GROUP BY title
...will give you:

Conversion failed when converting the nvarchar value '4500.00' to data type int.

Correct way

4500.00 is not integer so CAST via float first

sum(CAST(CAST(amount AS float) AS INT))

Why float?

• no idea of precision or scale across all rows
• empty string will cast to zero for float, fails on decimal
• float accepts stuff like 7E-02, fails on decimal 

HTH

STATISTICS_NORECOMPUTE in Rebuild Index

It specifies whether distribution statistics are recomputed. The default is OFF.

ON

Out-of-date statistics are not automatically recomputed.

OFF

Automatic statistics updating are enabled.

Indeed, statistics are recomputed during the index rebuild.

Dont think that it stops SQL Server from updating the statistics at the time of a rebuild; that is NOT what it does. 
Statistics are ALWAYS updated at the time of a REBUILD; this cannot be turned off (nor would you want to).

Instead, STATISTICS_NORECOMPUTE stops the database-wide auto-updating from updating the specific statistics for an index (or column-level statistic) that was created (or rebuilt) with this option turned on.

STATISTICS_NORECOMPUTE – When would anyone want to use it?

HTH

Delegates, Invoke, InvokeRequired

Question: What is InvokeRequired? Why this word is so difficult and what is it do? 

Answer: InvokeRequired is a property of a control which tells whether or not you can safely access the control. It is a boolean to check if you are on the UI thread before you try to update a form or control from a worker thread. 

If the property is True, then an invocation is required to access the control as the caller is on different thread than the one that owns the control's handle.

The invocation is performed by calling the control's Invoke or BeginInvoke method. 
You create a delegate, which is an object that contains the reference to a method. Then we pass this delegate to Invoke or BeginInvoke method which essentially call the reference method again but on thread that owns the control's handle.

Summary:
InvokeRequired asks 'Am I on the right thread?', if so carry on, else I need a delegate.

I got help and understanding of this from: 

Accessing Controls from Worker Threads

HTH.

Insert Data in DataGridView

Hi,

Lets say we already have DataGridView control as 'dgView' defined on our form with our columns defined as  FirstName, LastName and Address.
All columns were added from Design view of the form.
Now in code behind, if we just to add row without binding it to some datasource, it would be:
 

dgView.Rows.Add(New String() {"John","Doe","123 Main St, City UK"})

 

If we want to bind it with dataset or datatable then it would be :

 

Dim dt As New DataTable
dt.Columns.Add("FirstName") dt.Columns.Add("LastName") dt.Rows.Add(New String() {"saqib", "mahmood"}) ' This property is set if we dont have predefined columns in DataGridView.
dgView.AutoGenerateColumns = True dgView.DataSource = dt

Happy Coding!

Delegates vb.net

Delegates are function pointers that store function address.

There are 3 steps to understand Delegates:

1. Defining Delegate
2. Instantiating Delegate
3. Assigning Address of function to Delegate

Nothing fancy; just memorize the above steps to absorb the whole idea.

'How to define?
Public Delegate Sub testfile(Byval filename As String)

'instantiating the delegate
Dim mytestfile As testfile

Private Sub DoWork()
    'now assign the address of function to the delegate

    mytestfile = AddressOf FileTester

    'it can be called as

    mytestfile("test.txt") 

    'or

    mytestfile.Invoke("test.txt")

End Sub

Public Sub FileTester(ByRef filename As String)
     MsgBox(filename)
End Sub

Now FileTester is really the function will be called which just shows the filename

Happy coding!

Script Trigger in SQL Server 2008

Here is the query to script out all Triggers in SQL Server 2008:

set nocount on

DECLARE name nvarchar(128)

DECLARE procCursor CURSOR
FOR
SELECT distinct Name FROM sysobjects o
Inner join syscomments c on OBJECTPROPERTY(o.id, N'IsTrigger') = 1 and c.id= o.id

OPEN procCursor
FETCH
NEXT FROM procCursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'if exists (select * from dbo.sysobjects '

PRINT ' where id = object_id(N' + char(39) + '[dbo].[' + @Name + ']' + char(39) + ')'

PRINT ' and OBJECTPROPERTY(id, N' + char(39) + 'IsTrigger' + char(39) + ') = 1) '

PRINT '  DROP TRIGGER ' + @Name 

PRINT ' GO '

PRINT ' SET QUOTED_IDENTIFIER on '

PRINT ' GO '

PRINT ' SET ANSI_NULLS on '

PRINT ' GO'

exec sp_helptext @Name

PRINT ' GO '

FETCH NEXT FROM procCursor INTO @name

END

CLOSE procCursor

DEALLOCATE procCursor

GO

get Transaction Log size SQL Server 2008

Here is quick way to get Transaction Log size

DBCC SQLPERF(LOGSPACE)

Generate Row number

This method will only work when you do "select into from "

Syntax is :
select identity(int,1,1) as RowNumber, *
into newtable
from oldtable

Table Size / Count Rows in SQL Server Table

Here is quick way to get table size / rows in sql server tables:

SELECT t.NAME AS TableName, p.rows AS RowCounts
, SUM(a.total_pages) * 8 AS TotalSpaceKB
, SUM(a.used_pages) * 8 AS UsedSpaceKB
, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i
ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p
ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.Name, p.Rows
ORDER BY t.Name    

Register 32 bit DLL on 64 bit machine

To register 32 bit dll, we should go C:\Windows\Syswow64 and issue regsvr32 command.

Please see following screen to register and unregister dll.

Procedure or function has too many arguments specified

This is the error I got when I had Gridview bind with SqlDataSource.
Very hard to track this error if u dont have mismatch parameters.

Things to check when get this error:

1. Check all spellings for all parameters.
2. Check parameters defined in Stored Procedure
3. If DataKeyNames parameter is set for Gridview, then the Stored Procedure must have the same parameter in its parameter list.

Thanks,