Building a Visual Web Part – Calling SQL data in VB

Recently I posted a ‘How to build a counsellor notes system’ using an External Content
Type. In this post I build a Visual Web Part of the same functionality. A
couple of problems that I have come across building Visual Web Parts include:

Visual Web Parts don’t come with a SQLDataSource control like in ASP, so all CRUD
functions have to be built programatically. I also found that no VB code was
available on the net for calling SQL data, so I will present this function in
VB. If I have request for C# I can place this code in another post.

I make the assumption that readers understand how to create a visual web part for
SharePoint.

Below is a screenshot of the coded example.

The next three routines return records to a gridview based on a missing parameter.

Protected Sub Btn_Search_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Btn_Search.Click

‘THIS ROUTINE UTILISES A STORED PROCEDURE AND RETURNS THE
TOP 5 RECORDS FOR A STUDENT

‘Set variables

Dim ds1 As DataSet

Dim myconnection1 As SqlConnection

Dim mydataadapter1 As SqlDataAdapter

‘Create the connection to sql server

myconnection1 = New SqlConnection(“Data
Source=PAD-SQL-01;Initial Catalog=CounsellorDB;Integrated Security=True”)

‘Create the dataadapter, and then provide the name of the stored procedure

mydataadapter1 = New SqlDataAdapter(“dbo.sp_SCRS_Top5_Desc”, myconnection1)

‘set the commnand type as a stored procedure

mydataadapter1.SelectCommand.CommandType = CommandType.StoredProcedure

 ‘Create and add a parameter to Parameters collection for the stored procedure.

mydataadapter1.SelectCommand.Parameters.Add(NewSqlParameter(“@studentName”,
SqlDbType.NVarChar, 50))

 ‘Assign a search value to the parameter

mydataadapter1.SelectCommand.Parameters(“@studentName”).Value = Trim(txt_NameSearch.Text)

 ‘Create a new dataset to hold the records

ds1 = New DataSet()

mydataadapter1.Fill(ds1, “1”)

ViewState(“CurrentSearchResult”) = ds1 ‘a(“CurrentSearchResult”) = ds1

 ‘Set the datasource for the DataGrid as the DataSet that holds the rows.

GridView1.DataSource = ds1.Tables(“1”)

If (ds1.Tables(“1”).Rows.Count > 0) Then

GridView1.EditIndex = 0

End If

‘Bind the DataSet to the DataGrid

GridView1.DataBind()

‘Dispose of the DataAdapter

mydataadapter1.Dispose()

 ‘Close the connection

myconnection1.Close()

 ‘Set the search criteria entered automatically into the student name entry textbox

txt_StudentName.Text = txt_NameSearch.Text

End Sub

Protected Sub btn_FullHistory_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_FullHistory.Click

‘THIS ROUTINE UTILISES A STORED PROCEDURE AND RETURNS ALL
RECORDS FOR A STUDENT

‘Set variables

Dim ds2 As DataSet

Dim myconnection2 As SqlConnection

Dim mydataadapter2 As SqlDataAdapter

 ‘Create the connection to sql server

myconnection2 = New SqlConnection(“Data
Source=PAD-SQL-01;Initial Catalog=CounsellorDB;Integrated Security=True”)

 ‘Create the dataadapter, and then provide the name of the stored procedure

mydataadapter2 = New SqlDataAdapter(“dbo.sp_Get_All_Desc”, myconnection2)

 ‘set the commnand type as a stored procedure

mydataadapter2.SelectCommand.CommandType = CommandType.StoredProcedure

‘Create and add a parameter to Parameters collection for the stored procedure.

mydataadapter2.SelectCommand.Parameters.Add(NewSqlParameter(“@studentName”,
SqlDbType.NVarChar, 50))

‘Assign a search value to the parameter

mydataadapter2.SelectCommand.Parameters(“@studentName”).Value
= Trim(txt_NameSearch.Text)

 ‘Create a new dataset to hold the records

ds2 = New DataSet()

mydataadapter2.Fill(ds2, “2”)

‘Set the datasource for the DataGrid as the DataSet that holds the rows.

GridView1.DataSource = ds2.Tables(“2”)

 ‘Bind the DataSet to the DataGrid

GridView1.DataBind()

 ‘Dispose of the DataAdapter

mydataadapter2.Dispose()

 ‘Close the connection

myconnection2.Close()

 ‘Set the search criteria entered automatically into the student name entry textbox

txt_StudentName.Text = txt_NameSearch.Text

End Sub

Protected Sub Btn_Search2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Btn_Search2.Click

 ‘THIS ROUTINE UTILISES A STORED PROCEDURE AND RETURNS ALL
RECORDS FOR A PARTICULAR DATE

‘Set variables

Dim ds3 As DataSet

Dim myconnection3 As SqlConnection

Dim mydataadapter3 As SqlDataAdapter

 ‘Create the connection to sql server

myconnection3 = New SqlConnection(“DataSource=PAD-SQL-01;Initial Catalog=CounsellorDB;Integrated Security=True”)

 ‘Create the dataadapter, and then provide the name of the stored procedure

mydataadapter3 = New SqlDataAdapter(“dbo.sp_Get_By_Date”, myconnection3)

 ‘set the commnand type as a stored procedure

mydataadapter3.SelectCommand.CommandType = CommandType.StoredProcedure

‘Create and add a parameter to Parameters collection for the stored procedure.

mydataadapter3.SelectCommand.Parameters.Add(New SqlParameter(“@DateOfVisit”,
SqlDbType.NVarChar, 50))

 ‘Assign a search value to the parameter

mydataadapter3.SelectCommand.Parameters(“@DateofVisit”).Value = Trim(TextBox10.Text)

 ‘Create a new dataset to hold the records

ds3 = New DataSet()

mydataadapter3.Fill(ds3, “3”)

 ‘Set the datasource for the DataGrid as the DataSet that holds the rows.

GridView1.DataSource = ds3.Tables(“3”)

 ‘Bind the DataSet to the DataGrid

GridView1.DataBind()

 ‘Dispose of the DataAdapter

mydataadapter3.Dispose()

 ‘Close the connection

myconnection3.Close()

 ‘Set the search criteria entered automatically into the student name entry textbox

txt_StudentName.Text = txt_NameSearch.Text

End Sub

CRUD – INSERT

Protected Sub Btn_Submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Btn_Submit.Click

‘THIS PROCEDURE INSERTS RECORDS INTO THE SQL DATABASE FROM A SERIES OF TEXTBOXES

 ‘set the connection string as a string variable. Get this connection string from the webconfig file

Dim myConnectionString As String

myConnectionString = “Data Source=PAD-SQL-01;Initial Catalog=CounsellorDB;Integrated Security=True”

Dim connection As SqlConnection = New SqlConnection(myConnectionString)

connection.Open()

Dim command As SqlCommand = New SqlCommand(“sp_Insert_SCR”, connection)

command.CommandType = CommandType.StoredProcedure

Dim StudentNamelbl As String

Dim DateOfVisitlbl As String

Dim NotesOnVisitlbl As String

Dim CategoryOfVisitlbl As String

Dim ReferralNeededlbl As String

Dim ReferralAgentlbl As String

Dim TypeOfContactlbl As String

Dim FollowUpRequiredlbl As String

Dim Counsellor As String

StudentNamelbl = txt_StudentName.Text

DateOfVisitlbl = txt_Date.Text

NotesOnVisitlbl = txt_NotesOnVisit.Text

CategoryOfVisitlbl = cmb_CategoryOfVisit.Text

ReferralNeededlbl = cmb_ReferralNeeded.Text

ReferralAgentlbl = cmb_ReferralAgent.Text

TypeOfContactlbl = cmb_TypeOfContact.Text

FollowUpRequiredlbl = cmb_FollowUp.Text

Counsellor = HttpContext.Current.User.Identity.Name

command.Parameters.AddWithValue(“@StudentName”, StudentNamelbl)

command.Parameters.AddWithValue(“@DateOfVisit”, DateOfVisitlbl)

command.Parameters.AddWithValue(“@NotesOnVisit”, NotesOnVisitlbl)

command.Parameters.AddWithValue(“@CategoryOfVisit”, CategoryOfVisitlbl)

command.Parameters.AddWithValue(“@ReferralNeeded”, ReferralNeededlbl)

command.Parameters.AddWithValue(“@ReferralAgent”, ReferralAgentlbl)

command.Parameters.AddWithValue(“@TypeOfContact”, TypeOfContactlbl)

command.Parameters.AddWithValue(“@FollowUpRequired”, FollowUpRequiredlbl)

command.Parameters.AddWithValue(“@Counsellor”, Counsellor)

Dim adapter As SqlDataAdapter = New SqlDataAdapter

adapter.InsertCommand = command

command.ExecuteNonQuery()

 ‘Close the connection

connection.Close()

 ‘Refresh the gridview

GridView1.DataBind()

txt_StudentName.Text = “”

Button1.Visible = True

End Sub

 

CANCEL

Private Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs)
Handles GridView1.RowCancelingEdit

Dim ds1 As DataSet

ds1 = DirectCast(ViewState(“CurrentSearchResult”), DataSet)

GridView1.DataSource = ds1.Tables(“1”)

‘Reset the edit index.

GridView1.EditIndex = -1

GridView1.DataBind()

End Sub

This routine binds the username to a cell in the grid when the datagrid is in edit mode

Private Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
Handles GridView1.RowDataBound

If (e.Row.RowState And DataControlRowState.Edit) > 0 Then

e.Row.Cells(10).Text = HttpContext.Current.User.Identity.Name

End If

End Sub

 

CRUD – DELETE

Private Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs)
Handles GridView1.RowDeleting

‘THIS PROCEDURE DELETES ROWS FROM THE DATAGRID

 ‘set the connection string as a string variable. Get this connection string from the webconfig file

Dim myConnectionString As String

myConnectionString = “Data Source=PAD-SQL-01;Initial Catalog=CounsellorDB;Integrated Security=True”

 ‘open the connection string

Dim myConnection As New SqlConnection(myConnectionString)

myConnection.Open()

 ‘Create and run the new update query

Dim command As SqlCommand = New SqlCommand(“sp_Delete_SCR”, myConnection)

command.CommandType = CommandType.StoredProcedure

Dim ID As Integer = CInt(GridView1.DataKeys(e.RowIndex).Value.ToString())

command.Parameters.AddWithValue(“@ID”, ID)

command.ExecuteNonQuery()

 ‘Close the connection

command.Connection.Close()

 ‘Refresh the Gridview

GridView1.DataBind()

End Sub

CREATE THE DATASET THAT CAN BE EDITED

Private Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs)
Handles GridView1.RowEditing

Dim ds1 As DataSet

Dim gv As GridView

ds1 = DirectCast(ViewState(“CurrentSearchResult”), DataSet)

gv = DirectCast(sender, GridView)

gv.DataSource = ds1.Tables(“1”)

gv.EditIndex = e.NewEditIndex

gv.DataBind()

End Sub

 

CRUD – UPDATE

Private Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
Handles GridView1.RowUpdating

‘THIS PROCEDURE UPDATES THE DATAGRID

Dim adapter As SqlDataAdapter = New SqlDataAdapter()

 ‘set the connection string as a string variable. Get this connection string from the webconfig file.

Dim myConnectionString As String

myConnectionString = “Data Source=PAD-SQL-01;Initial Catalog=CounsellorDB;Integrated Security=True”

‘open the connection string

Dim myConnection As New SqlConnection(myConnectionString)

Dim command As SqlCommand = New SqlCommand(“sp_Update_SCR”, myConnection)

command.CommandType = CommandType.StoredProcedure

command.Connection = myConnection

Dim StudentNamelbl As String

Dim DateOfVisitlbl As String

Dim NotesOnVisitlbl As String

Dim CategoryOfVisitlbl As String

Dim ReferralNeededlbl As String

Dim ReferralAgentlbl As String

Dim TypeOfContactlbl As String

Dim FollowUpRequiredlbl As String

Dim Counsellor As String

Dim row As GridViewRow = GridView1.Rows(e.RowIndex)

Dim ID As Integer = CInt(GridView1.DataKeys(e.RowIndex).Value.ToString())

StudentNamelbl = DirectCast(row.Cells(2).Controls(0), TextBox).Text

DateOfVisitlbl = DirectCast(row.Cells(3).Controls(0), TextBox).Text

NotesOnVisitlbl = DirectCast(row.Cells(4).Controls(0), TextBox).Text

CategoryOfVisitlbl = DirectCast(row.Cells(5).Controls(0), TextBox).Text

ReferralNeededlbl = DirectCast(row.Cells(6).Controls(0), TextBox).Text

ReferralAgentlbl = DirectCast(row.Cells(7).Controls(0), TextBox).Text

TypeOfContactlbl = DirectCast(row.Cells(8).Controls(0), TextBox).Text

FollowUpRequiredlbl = DirectCast(row.Cells(9).Controls(0), TextBox).Text

Counsellor = DirectCast(row.Cells(10).Controls(0), TextBox).Text

Counsellor = HttpContext.Current.User.Identity.Name

command.Connection.Open()

command.Parameters.AddWithValue(“@ID”,ID)

command.Parameters.AddWithValue(“@StudentName”,StudentNamelbl)

command.Parameters.AddWithValue(“@DateOfVisit”, DateOfVisitlbl)

command.Parameters.AddWithValue(“@NotesOnVisit”,NotesOnVisitlbl)

command.Parameters.AddWithValue(“@CategoryOfVisit”, CategoryOfVisitlbl)

command.Parameters.AddWithValue(“@ReferralNeeded”,ReferralNeededlbl)

command.Parameters.AddWithValue(“@ReferralAgent”, ReferralAgentlbl)

command.Parameters.AddWithValue(“@TypeOfContact”, TypeOfContactlbl)

command.Parameters.AddWithValue(“@FollowUpRequired”, FollowUpRequiredlbl)

command.Parameters.AddWithValue(“@Counsellor”, Counsellor)

command.ExecuteNonQuery()

‘Dispose of the DataAdapter

adapter.Dispose()

‘Close the connection

command.Connection.Close()

 ‘Refresh the gridview

GridView1.DataBind()

End Sub

End Class

 

Advertisements

About whellmuth

Working with the education software industry to build software with standard arhitectures that allows schools to have extensible and well integrated technologies. Member of Software QLD. Member of Microsoft advisory board on cloud computing. My doctorate specialises in Software Architecture in the Education environment. My Master Research specialised in IT change management.
This entry was posted in Microsoft SharePoint, Visual Basic, Visual Studio. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s