Category: Microsoft

Create Database and Tables in SQL Server using VB.Net

Option Strict Off
Option Explicit On
Friend Class frmSQL
Inherits System.Windows.Forms.Form
#Region “Windows Form Designer generated code ”
Public Sub New()
MyBase.New()
If m_vb6FormDefInstance Is Nothing Then
If m_InitializingDefInstance Then
m_vb6FormDefInstance = Me
Else
Try
‘For the start-up form, the first instance created is the default instance.
If System.Reflection.Assembly.GetExecutingAssembly.EntryPoint.DeclaringType Is Me.GetType Then
m_vb6FormDefInstance = Me
End If
Catch
End Try
End If
End If
‘This call is required by the Windows Form Designer.
InitializeComponent()
End Sub
‘Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal Disposing As Boolean)
If Disposing Then
If Not components Is Nothing Then
components.Dispose()
End If
End If
MyBase.Dispose(Disposing)
End Sub
‘Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
Public ToolTip1 As System.Windows.Forms.ToolTip
Public WithEvents Command2 As System.Windows.Forms.Button
Public WithEvents Command1 As System.Windows.Forms.Button
‘NOTE: The following procedure is required by the Windows Form Designer
‘It can be modified using the Windows Form Designer.
‘Do not modify it using the code editor.
Private Sub InitializeComponent()
Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(frmSQL))
Me.components = New System.ComponentModel.Container()
Me.ToolTip1 = New System.Windows.Forms.ToolTip(components)
Me.ToolTip1.Active = True
Me.Command2 = New System.Windows.Forms.Button
Me.Command1 = New System.Windows.Forms.Button
Me.Text = “Work with SQL server”
Me.ClientSize = New System.Drawing.Size(288, 186)
Me.Location = New System.Drawing.Point(4, 23)
Me.StartPosition = System.Windows.Forms.FormStartPosition.WindowsDefaultLocation
Me.Font = New System.Drawing.Font(“Arial”, 8!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.BackColor = System.Drawing.SystemColors.Control
Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Sizable
Me.ControlBox = True
Me.Enabled = True
Me.KeyPreview = False
Me.MaximizeBox = True
Me.MinimizeBox = True
Me.Cursor = System.Windows.Forms.Cursors.Default
Me.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.ShowInTaskbar = True
Me.HelpButton = False
Me.WindowState = System.Windows.Forms.FormWindowState.Normal
Me.Name = “frmSQL”
Me.Command2.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
Me.Command2.Text = “Create Table”
Me.Command2.Size = New System.Drawing.Size(89, 33)
Me.Command2.Location = New System.Drawing.Point(136, 72)
Me.Command2.TabIndex = 1
Me.Command2.Font = New System.Drawing.Font(“Arial”, 8!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Command2.BackColor = System.Drawing.SystemColors.Control
Me.Command2.CausesValidation = True
Me.Command2.Enabled = True
Me.Command2.ForeColor = System.Drawing.SystemColors.ControlText
Me.Command2.Cursor = System.Windows.Forms.Cursors.Default
Me.Command2.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.Command2.TabStop = True
Me.Command2.Name = “Command2”
Me.Command1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
Me.Command1.Text = “Create DB”
Me.Command1.Size = New System.Drawing.Size(89, 33)
Me.Command1.Location = New System.Drawing.Point(40, 72)
Me.Command1.TabIndex = 0
Me.Command1.Font = New System.Drawing.Font(“Arial”, 8!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Command1.BackColor = System.Drawing.SystemColors.Control
Me.Command1.CausesValidation = True
Me.Command1.Enabled = True
Me.Command1.ForeColor = System.Drawing.SystemColors.ControlText
Me.Command1.Cursor = System.Windows.Forms.Cursors.Default
Me.Command1.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.Command1.TabStop = True
Me.Command1.Name = “Command1”
Me.Controls.Add(Command2)
Me.Controls.Add(Command1)
End Sub
#End Region
#Region “Upgrade Support ”
Private Shared m_vb6FormDefInstance As frmSQL
Private Shared m_InitializingDefInstance As Boolean
Public Shared Property DefInstance() As frmSQL
Get
If m_vb6FormDefInstance Is Nothing OrElse m_vb6FormDefInstance.IsDisposed Then
m_InitializingDefInstance = True
m_vb6FormDefInstance = New frmSQL()
m_InitializingDefInstance = False
End If
DefInstance = m_vb6FormDefInstance
End Get
Set
m_vb6FormDefInstance = Value
End Set
End Property
#End Region
Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset

Dim db As New SQL_BD

Private Sub CreateTavle_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command1.Click
Call db.Create_db(“TESTDB”)
End Sub

Private Sub CreateDB_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Command2.Click
Dim conStr, ColamList As String
conStr = “Data Source=DBNAME\DBTABLE;Initial Catalog= mydb;Integrated Security=SSPI;User ID=sa;Password=password;Connection Timeout=60”
ColamList = “AddressId int,adBName varchar(30), adbAddress varchar(80)”
Call db.Create_Table(conStr, “AddressBook”, ColamList)
End Sub
End Class

Create_Table and Create_db class

Option Strict Off
Option Explicit On
Friend Class SQL_BD
Dim con As New ADODB.Connection

”Create Database in SQL Server
Public Function Create_db(ByVal db_name As String) As Object
On Error Resume Next
Dim SQl As String
If con.State = ADODB.ObjectStateEnum.adStateOpen Then con.Close()
‘Connect with Master Database
con.Open((“Data Source=GETAFIX\OPTIMATIX;Initial Catalog= mydb;Integrated Security=SSPI;User ID=sa;Password=womble;Connection Timeout=60”))

SQl = “CREATE DATABASE ” & db_name & ” ” & ” ON” & “( NAME = ” & db_name & “_dat,” & ” FILENAME = ‘c:\program files\microsoft sql server\mssql\data\” & db_name & “dat.mdf’, ” & ” SIZE = 10, ” & ” MAXSIZE = 50,” & ” FILEGROWTH = 5 ) ”
SQl = SQl & ” LOG ON ” & ” ( NAME = ‘” & db_name & “_log’, ” & ” FILENAME = ‘c:\program files\microsoft sql server\mssql\data\” & db_name & “log.ldf’,” & ” SIZE = 5MB,” & ” MAXSIZE = 25MB, ” & ” FILEGROWTH = 5MB )”
con.Execute(SQl)
con.Close()
End Function

”Create table in working database
Public Function Create_Table(ByVal conString As String, ByVal tab_name As String, ByVal table_colams_list As String) As Object
On Error GoTo e
If con.State = ADODB.ObjectStateEnum.adStateOpen Then con.Close()
‘Connect with working Database
con.Open((conString))
Dim SQl As String
SQl = “if exists (select table_name from INFORMATION_SCHEMA.TABLES where table_name='” & tab_name & “‘) ” & ” drop table ” & tab_name
con.Execute(SQl)
SQl = “CREATE table ” & tab_name & “( ” & table_colams_list & ” )”
con.Execute(SQl)
con.Close()
e:
If Err.Number Then
MsgBox(Err.Description, MsgBoxStyle.Critical)
Err.Clear()
End If
End Function
End Class

Storing images as BLOB in db in VB.Net

Private Sub UploadedFiles_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles UploadedFiles.DoubleClick
‘
‘ Downloads the selected file and displays it in the picture box.
‘

‘
‘ Finds the unique id of the file.
‘
Dim drv As DataRowView = CType(UploadedFiles.SelectedItem, DataRowView)
Dim selectedText As String = drv.Row(“Name”).ToString()
Dim id As Long=-1
‘
‘ the id is stored in text. The structure is: id – FileName.
‘
id = Long.Parse(selectedText.Substring(0,selectedText.IndexOf(” – “,0)).Trim())

Dim filename As String=Nothing
Dim up As TransferPictures = New TransferPictures()
Dim result As Byte() = up.DownloadFile(id,filename)
up = Nothing

Try
‘
‘ We cannot assign a byte array directly to an image.
‘ We use MemoryStream, an object that creates a file in memory
‘ and than we pass this to create the image object.
‘
Dim ms As MemoryStream = New MemoryStream(result, 0, result.Length)
Dim im As Image = Image.FromStream(ms)
Picture.Image = im
Catch ee As Exception
MessageBox.Show(“An error has occured.\n” + ee.Message)
End Try
End Sub
End Class

Connect to Datasource in VB.Net

Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
Try
ConnectionString = “Data Source= ” + txtdatasource.Text + “;Initial Catalog=” + txtinitialcat.Text + “;Integrated Security=SSPI;User ID=sa;Password=test;Connection Timeout=60”
MessageBox.Show(ConnectionString)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Update Fact tables for Data Warehouse in VB.Net

Private Sub btnUpdateFact_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateFact.Click
try
Dim sqlupdatetable As String
Dim counter As Integer
counter = 0
If (btnUpdateFact.Text = “Update Facts”) Then

btnAddFact.Enabled = False

If (cbFTableName.SelectedIndex = -1) Then
MessageBox.Show(“Please select a Table Name to update”, “Information”, MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
cmd.Connection = New System.Data.SqlClient.SqlConnection(ConnectionString)
cmd.Connection.Open()
sqlupdatetable = “Select syscolumns.name, systypes.name, syscolumns.length from syscolumns, SysObjects, systypes where((Sysobjects.name = ‘” & cbFTableName.SelectedItem.ToString & “‘) and (Sysobjects.xtype = ‘U’) and (sysobjects.xtype not like ‘PK’) and (Sysobjects.id = syscolumns.id) and (systypes.xtype = syscolumns.xtype)) order by syscolumns.colorder”

cmd = New System.Data.SqlClient.SqlCommand(sqlupdatetable, cmd.Connection)

objreader = cmd.ExecuteReader

While objreader.Read
lbFFields.Items.Add(objreader(0) + ” ” + objreader(1) + ” (” + objreader(2).ToString + “)”)
lbField.Items.Add(objreader(0))
lbType.Items.Add(objreader(1))
lbVal.Items.Add(objreader(2))
End While

cmd.Connection.Close()

btnUpdateFact.Text = “Save Facts”
ElseIf (btnUpdateFact.Text = “Save Facts”) Then
btnAdd.Enabled = True
btnUpdateFact.Text = “Update Facts”
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Create Fact Tables for Data Warehouse in VB.Net

Private Sub btnCreateFact_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateFact.Click
Try
Dim tblerror As String
tblerror = “”

If (cbFTableName.SelectedIndex = -1) Then
tblerror = “Please select a Table Name”
End If
If ((tblerror.Length > 0)) Then
MessageBox.Show(tblerror + vbCrLf, “Information”, MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If

cmd.Connection = New System.Data.SqlClient.SqlConnection(ConnectionString)
cmd.Connection.Open()

sql = “CREATE TABLE ” + cbFTableName.SelectedItem.ToString + “(” + “RowID INT IDENTITY (0, 1) NOT NULL PRIMARY KEY, ” + sqlstrfact + strFKcmd + “)”
MessageBox.Show(sql)
cmd = New System.Data.SqlClient.SqlCommand(sql, cmd.Connection)

cmd.ExecuteNonQuery()
MessageBox.Show(“The table ” + cbFTableName.SelectedItem.ToString + ” was successfully created”)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Update Dimensions Table for Data Warehouse in VB.Net

Private Sub btnUpdateDim_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateDim.Click
Try
Dim sqlupdatetable As String
Dim counter As Integer
counter = 0
If (btnUpdateDim.Text = “Update Dimensions”) Then

btnAdd.Enabled = False
txtUnique.Enabled = False
cbTypeUnique.Enabled = False
txtPK.Enabled = False
txtValUnique.Enabled = False

If (cbTableName.SelectedIndex = -1) Then
MessageBox.Show(“Please select a Table Name to update”, “Information”, MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
cmd.Connection = New System.Data.SqlClient.SqlConnection(ConnectionString)
cmd.Connection.Open()
sqlupdatetable = “Select syscolumns.name, systypes.name, syscolumns.length from syscolumns, SysObjects, systypes where((Sysobjects.name = ‘” & cbTableName.SelectedItem.ToString & “‘) and (Sysobjects.xtype = ‘U’) and (sysobjects.xtype not like ‘PK’) and (Sysobjects.id = syscolumns.id) and (systypes.xtype = syscolumns.xtype)) order by syscolumns.colorder”

cmd = New System.Data.SqlClient.SqlCommand(sqlupdatetable, cmd.Connection)

objreader = cmd.ExecuteReader

While objreader.Read
counter += 1
If (counter = 1) Then
txtPK.Text = objreader(0)
End If
If (counter = 2) Then
txtUnique.Text = objreader(0)
cbTypeUnique.SelectedItem = objreader(1)
txtValUnique.Text = objreader(2).ToString
End If
If (counter > 2) Then
lbFields.Items.Add(objreader(0) + ” ” + objreader(1) + ” (” + objreader(2).ToString + “)”)
lbField.Items.Add(objreader(0))
lbType.Items.Add(objreader(1))
lbVal.Items.Add(objreader(2))
End If
End While

cmd.Connection.Close()

btnUpdateDim.Text = “Save Dimensions”
ElseIf (btnUpdateDim.Text = “Save Dimensions”) Then
btnAdd.Enabled = True
btnUpdateDim.Text = “Update Dimensions”
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Create Dimension Tables for Data Warehouse in VB.Net

Private Sub btnCreateDim_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDim.Click
Try
Dim tblerror, unqerror, pkerror, valerror As String
tblerror = “”
unqerror = “”
pkerror = “”
valerror = “”

Dim sqlcmd As System.Data.SqlClient.SqlCommand
Dim sqladd As System.Data.SqlClient.SqlCommand

If (cbTableName.SelectedIndex = -1) Then
tblerror = “Please select a Table Name”
End If

If (txtUnique.Text = “”) Then
unqerror = “Please complete the Unique field”
End If

If (txtPK.Text = “”) Then
pkerror = “Please complete the Primary Key field”
End If
If (txtValUnique.Text = “”) Then
valerror = “Please supply a val for the unique field”
End If
If ((tblerror.Length > 0) Or (unqerror.Length > 0) Or (pkerror.Length > 0) Or (valerror.Length > 0)) Then
MessageBox.Show(tblerror + vbCrLf + unqerror + vbCrLf + pkerror + vbCrLf + valerror, “Information”, MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If

If (cbTypeUnique.SelectedItem.ToString = “varchar” Or cbTypeUnique.SelectedItem.ToString = “float” Or cbTypeUnique.SelectedItem.ToString = “decimal”) Then
sqltestUniquePK = “Select syscolumns.name from syscolumns where((syscolumns.name = ‘” & txtUnique.Text & “‘) or (syscolumns.name = ‘” & txtPK.Text & “‘))”
sqlcmd = New System.Data.SqlClient.SqlCommand(sqltestUniquePK, New System.Data.SqlClient.SqlConnection(ConnectionString))
sqlcmd.Connection.Open()
objreader = sqlcmd.ExecuteReader

While objreader.Read
MessageBox.Show(“Please rename the following field before proceeding: ” + objreader.GetString(0), “Information”, MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End While
sqlcmd.Connection.Close()

sql = “CREATE TABLE ” + cbTableName.SelectedItem.ToString + “(” + txtPK.Text + ” INT IDENTITY (0, 1) NOT NULL PRIMARY KEY,” + txtUnique.Text + ” ” + cbTypeUnique.SelectedItem.ToString + ” (” + txtValUnique.Text + “) ” + ” unique ” + “, ” + sqlstrdim + “)”
sqladd = New System.Data.SqlClient.SqlCommand(sql, New System.Data.SqlClient.SqlConnection(ConnectionString))

sqladd.Connection.Open()
sqladd.ExecuteNonQuery()
sqladd.Connection.Close()

Else

sqltestUniquePK = “Select syscolumns.name from syscolumns where((syscolumns.name = ‘” & txtUnique.Text & “‘) or (syscolumns.name = ‘” & txtPK.Text & “‘))”
sqlcmd = New System.Data.SqlClient.SqlCommand(sqltestUniquePK, New System.Data.SqlClient.SqlConnection(ConnectionString))
sqlcmd.Connection.Open()
objreader = sqlcmd.ExecuteReader

While objreader.Read
MessageBox.Show(“Please rename the following field: ” + objreader.GetString(0), “Information”, MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End While

sqlcmd.Connection.Close()

sql = “CREATE TABLE ” + cbTableName.SelectedItem.ToString + “(” + txtPK.Text + ” INT IDENTITY (0, 1) NOT NULL PRIMARY KEY,” + txtUnique.Text + ” ” + cbTypeUnique.SelectedItem.ToString + txtValUnique.Text + ” unique ” + ” , ” + sqlstrdim + “)”
sqladd = New System.Data.SqlClient.SqlCommand(sql, New System.Data.SqlClient.SqlConnection(ConnectionString))
sqladd.Connection.Open()
sqladd.ExecuteNonQuery()
sqladd.Connection.Close()

End If

MessageBox.Show(“The table ” + cbTableName.SelectedItem.ToString + ” was successfully created”, “Created Table”, MessageBoxButtons.OK, MessageBoxIcon.Information)

Dim cmdFTableload As New System.Data.SqlClient.SqlCommand

cmdFTableload.Connection = New System.Data.SqlClient.SqlConnection(ConnectionString)
cmdFTableload.Connection.Open()
sqltesttable = “Select Sysobjects.name from SysObjects where((Sysobjects.name like ‘%Fact%’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)”
cmdFTableload = New System.Data.SqlClient.SqlCommand(sqltesttable, cmdFTableload.Connection)

objreader = cmdFTableload.ExecuteReader

While objreader.Read
cbFTableName.Items.Add(objreader.GetString(0))

End While

cmdFTableload.Connection.Close()

‘Add a new row for the newly created table
‘Dim sqlcmdaddtotable As New System.Data.SqlClient.SqlCommand
‘sqlcmdaddtotable.CommandText = “INSERT INTO ” + cbTableName.SelectedItem.ToString + ” (” + txtPK.Text + ” , ” + txtUnique.Text + ” , ” + fieldname + “) ” + “Values()”

‘sqlcmdaddtotable.Connection = New System.Data.SqlClient.SqlConnection(ConnectionString)
‘sqlcmdaddtotable.Connection.Open()
‘sqlcmdaddtotable.ExecuteNonQuery()
‘sqlcmdaddtotable.Connection.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub