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




DataGrid: Paging without N Tile


In the orginal version of this article we used the Sql Server 2005 NTile function to break the data into pages.
We have since found a DataAdapter fill overload that allows you to specify the records you want to load.
This example pages data from the NorthWind access database.
Dim da As OleDbDataAdapter
Dim conn As OleDbConnection
Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As String
        Dim cmd As OleDbCommand
        With nuPage
            .Maximum = 10
            .Minimum = 1
            .Increment = 1
            .Value = 1
        End With

        strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = c:\Northwind.mdb;"

        conn = New OleDbConnection(strConn)
        cmd = New OleDbCommand("Select count(ProductName) From Products", conn)

        Try
            da = New OleDbDataAdapter("Select * from Products", conn)
            conn.Open()
            With nuPage
                .Maximum = Math.Ceiling(cmd.ExecuteScalar / 10)
                .Minimum = 1
                .Increment = 1
                .Value = 1
            End With
            conn.Close()
            da.Fill(ds, 0, 10, "Products")
            ds.Tables("Products").DefaultView.AllowNew = False
            DataGrid1.DataSource = ds.Tables("Products")
        Catch ex As Exception
            Trace.WriteLine(ex.ToString)
        End Try

        Dim ts As New DataGridTableStyle
        ts.MappingName = "Products"
        ts.SelectionBackColor = Color.Yellow
        ts.SelectionForeColor = Color.Red
        Dim colName As New DataGridTextBoxColumn

        With colName
           .MappingName = "ProductName"
            .HeaderText = "Name"
            .Width = 200
            .ReadOnly = True
        End With

        Dim cm As CurrencyManager = CType(Me.BindingContext(DataGrid1.DataSource), CurrencyManager)
        ' Use the ListManager to get the PropertyDescriptor for the new column.
        Dim pd As PropertyDescriptor = cm.GetItemProperties()("UnitPrice")
        ' Create a new DataTimeFormat object.
        Dim colBDay As New DataGridTextBoxColumn(pd, "c")

        With colBDay
            .MappingName = "UnitPrice"
            .HeaderText = "Price"
            .Width = 50
        End With

        ts.GridColumnStyles.Add(colName)
        ts.GridColumnStyles.Add(colBDay)
        DataGrid1.TableStyles.Add(ts)
        ts = Nothing
        colName = Nothing
End Sub

Private Sub nuPage_ValueChanged(ByVal sender As System.Object,  _
                   ByVal e As System.EventArgs) Handles nuPage.ValueChanged
        If da Is Nothing Then Return
        Dim intStart As Integer = (nuPage.Value - 1) * 10
        ds.Clear()
        da.Fill(ds, intStart, 10, "Products")
End Sub






SQL Server: Create database


You can create an SQL server database by executing sql commands. Here is an example.
Imports System.Data.SqlClient
Public Class Form1
    Private Conn As SqlConnection
    Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgsHandles MyBase.Load
        Dim strConn As String = "Server = " & Environment.MachineName & "\YourServerName; Database = ; Integrated Security = SSPI;"
        Conn = New SqlConnection(strConn)
        Conn.Open()
        CreateDataBase()
        CreateClientsTable()
        Conn.Close()
    End Sub
 
    Private Sub CreateDataBase()
        Dim strSQL = "if Exists (Select * From master..sysdatabases Where Name = 'VET')"
        strSQL &= "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"
        Using cmd As New SqlCommand(strSQL, Conn)
            cmd.CommandType = CommandType.Text
            Try
                cmd.ExecuteNonQuery()
            Catch
                MessageBox.Show("Error Creating DB")
            End Try
        End Using
    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))"
 
        Using cmd As New SqlCommand(strSQL, Conn)
            Try
                cmd.ExecuteNonQuery()
            Catch ex As SqlException
                MessageBox.Show(ex.ToString, "Clients")
            End Try
        End Using
    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"
        Using 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")
            End Try
        End Using
    End Sub
End Class



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



1 2 3 4 Previous Next