1 2 3 4 Previous Next 

SQLCLR: Create a custom function


Store a persons full name in the name field of one my sql express database which I wanted to sort by the last name. 

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function LastWord(ByVal word As SqlString) As SqlString
        ' Add your code here
        Dim strWords() As String = word.ToString.Split(" "c)
        Return New SqlString(strWords(strWords.GetUpperBound(0)))
    End Function
End Class



Using Linq to Sql with Sql Compact Edition


The Linq to Sql supports Sql compact edition unfortunately the designer for Visual Studio 2008 does not.  To generate the dbml file use the SqlMetal.exe utility.

 

First add the sql compact edition database to your project and cancel out of the wizard to generate a typed dataset.  For this example I added the Northwind database.  Open the Visual Studio 2008 command prompt go to the projects directory.  The command to generate the dbml is Sqlmetal.exe /dbml:northwind.dbml northwind.sdf /language:vb

 

After the file is generated from the project menu select add existing item and add the dbml file just generated. 

 

Sample Linq query

 

Dim db As New Northwind("Data Source= Northwind.sdf")
db.Log = Console.Out
Dim q = From p In db.Products _
        Where p.UnitsInStock > 3 _
        Select p.ProductName, p.UnitPrice

Dim bs As New BindingSource

bs.DataSource = q
DataGridView1.DataSource = bs



SQL Server: Create database


You can create an SQL server database by executing sql commands. Here is an example.
Dim conn As SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strConn As String
strConn = "Server = " & Environment.MachineName
strConn += "\VSdotNET; Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

conn.Open()
CreateDataBase()
CreateClientsTable()
conn.Close()
End Sub



Private Sub CreateDataBase()
Dim strSQL As String
strSQL = "if Exists (Select * From master..sysdatabases Where Name = 'VET')"
strSQL += "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"

Dim cmd As New SqlCommand(strSQL, conn)
cmd.CommandType = CommandType.Text

Try
cmd.ExecuteNonQuery()
Catch
MessageBox.Show("Error Creating DB")
Finally
cmd.Dispose()
End Try
End Sub

Private Sub CreateClientsTable()
Me.Text = "Creating Clients Table..."
Dim strSQL As String = _
"USE VET" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM VET.dbo.sysobjects " & _
"WHERE Name = 'Clients' " & _
"AND TYPE = 'u')" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DROP TABLE VET.dbo.Clients" & vbCrLf & _
"END" & vbCrLf & _
"CREATE TABLE Clients (" & _
"ID Int NOT NULL," & _
"LastName NVarChar(20) NOT NULL," & _
"FirstName NVarChar(20) NOT NULL," & _
"Address NVarChar(150) NOT NULL," & _
"City NVarChar(20) NOT NULL," & _
"ZipCode NVarChar(5) NOT NULL," & _
"PhoneNumber NVarChar(20) NOT NULL," & _
"WorkNumber NVarChar(20)," & _
"CellNumber NVarChar(20)," & _
"Email NVarChar(50) NOT NULL," & _
"Balance Money NOT NULL," & _
"BalanceDate DateTime NOT NULL," & _
"CONSTRAINT [ID] PRIMARY KEY CLUSTERED" & _
"(ID))"

Dim cmd As New SqlCommand(strSQL, conn)
cmd.CommandType = CommandType.Text
Try
cmd.ExecuteNonQuery()
Catch ex As SqlException
MessageBox.Show(ex.ToString, "Clients")
Finally
cmd.Dispose()
End Try
End Sub



Private Sub MakeClientStoredProcedure()
Dim strSQL As String = _
"USE VET" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM VET.dbo.sysobjects " & _
"WHERE Name = 'ClientInfo' " & _
"AND TYPE = 'p')" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DROP PROCEDURE ClientInfo" & vbCrLf & _
"END"

Dim cmd As New SqlCommand(strSQL, conn)
cmd.CommandType = CommandType.Text

Try
cmd.ExecuteNonQuery()
cmd.CommandText = "Create Procedure ClientInfo" & vbCrLf & _
"@ClientID int " & vbCrLf & _
"AS Select * " & vbCrLf & _
"FROM VET.dbo.Clients Where ID = @ClientID"
cmd.ExecuteNonQuery()
Catch ex As SqlException
MessageBox.Show(ex.ToString, "Error Creating Stored Procedure")
Finally
cmd.Dispose()
End Try
End Sub


SQL Server: List Database Tables


Use the sp_tables stored procedure to list the tables in a database. This example will list all the databases and tables for the local sql server.
Imports System.Data.SqlClient

Public Module Module1

    Sub Main()
        Dim strConn As String
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim dr, drTables As SqlDataReader

        strConn = "Server =(local);"
        strConn &= "Database = ; Integrated Security = SSPI;"

        conn = New SqlConnection(strConn)
        cmd = New SqlCommand("sp_Databases", conn)

        cmd.CommandType = CommandType.StoredProcedure

        conn.Open()
        dr = cmd.ExecuteReader

        If dr.HasRows Then
            Do While dr.Read
                Console.WriteLine(String.Format("Name {0} Size {1}", _
                        dr.Item("Database_Name"), dr.Item("Database_Size")))
                Dim connTable As SqlConnection
                strConn = "Server =(local);"
                strConn &= "Database = " & dr.Item("Database_Name").ToString
                strConn &= "; Integrated Security = SSPI;"

                connTable = New SqlConnection(strConn)

                Dim cmdTables As New SqlCommand("sp_Tables", connTable)

                connTable.Open()
                drTables = cmdTables.ExecuteReader
                Do While drTables.Read
                    Console.Write("   ")
                    Console.WriteLine(drTables.Item("TABLE_NAME").ToString)
                Loop
                drTables.Close()
                connTable.Close()
            Loop
        End If

        dr.Close()
        conn.Close()
    End Sub

End Module


SQL Server: List Databases and Tables with SMO


Visual studio 2005 introduced a new SMO class.  You can use it to list the database and tables in an sql server.  This example list the database and tables on the local sql server. 

Add a reference to Microsoft.SqlServer.ConnectionInfo and  Microsoft.SqlServer.Smo

Imports Microsoft.SqlServer.Management.Smo

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim srv As New Server(".")
        Dim db As Database
        For Each db In srv.Databases
            Trace.WriteLine(db.Name)
            Trace.Indent()
            For Each tbl As Table In db.Tables
                Trace.WriteLine(tbl.Name)
            Next
            Trace.Unindent()
        Next
    End Sub
End Class



1 2 3 4 Previous Next