Imports System.Data.SqlClient
Imports System.Data
Public Class dbConn
Public Shared Function GetConnectionstring() As String
Return "Server=.\SQL2008;database=Workshopdb;Max Pool Size=400;Connect Timeout=600;"
End Function
Public Function GetDataTable(ByVal strsql As String, Optional ByVal strTableName As String = "") As DataTable
Dim objConn As New SqlConnection
Dim strConnString As String
strConnString = GetConnectionstring()
strsql = strsql
Dim dbConnection As New SqlConnection(strConnString)
Dim cmd As New SqlCommand(strsql, dbConnection)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds, strTableName)
If ds.Tables.Count > 0 Then
If ds.Tables(0).Rows.Count > 0 Then
Return ds.Tables(0)
End If
End If
Return New DataTable
End Function
Public Function ExecuteNonQuery(ByVal strSQL As String) As Integer
Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim strConnString As String
strConnString = GetConnectionstring()
objConn.ConnectionString = strConnString
objConn.Open()
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With
Try
objCmd.ExecuteNonQuery()
Catch ex As Exception
objConn.Close()
objConn = Nothing
Return -1
End Try
objConn.Close()
objConn = Nothing
Return 0
End Function
Public Function ExecuteNonQuerywithErrorMsg(ByVal strSQL As String) As String
Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim strConnString As String
strConnString = GetConnectionstring()
objConn.ConnectionString = strConnString
objConn.Open()
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With
Try
objCmd.ExecuteNonQuery()
Catch ex As Exception
objConn.Close()
objConn = Nothing
Return ex.Message.ToString()
End Try
objConn.Close()
objConn = Nothing
Return ""
End Function
Public Function ExecuteScalar(ByVal strSQL As String) As Integer
Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim strConnString As String
Dim intNumRows As Integer
strConnString = GetConnectionstring()
objConn.ConnectionString = strConnString
objConn.Open()
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With
Try
intNumRows = objCmd.ExecuteScalar()
Catch ex As Exception
objConn.Close()
objConn = Nothing
Return -1
End Try
objConn.Close()
objConn = Nothing
Return intNumRows
End Function
End Class
Imports System.Data.SqlClient
Public Class Register
Inherits System.Web.UI.Page
Dim conn As dbConn
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
LoadTable()
End Sub
Protected Sub BtnRegis_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnRegis.Click
End Sub
Public Sub LoadTable()
Me.GridView1.DataSource = conn.GetDataTable("select * From Account", "Username")
Me.GridView1.DataBind()
End Sub
End Class
Tag : ASP.NET Ms SQL Server 2008, VS 2010 (.NET 4.x)
Imports System.Data
Imports System.Data.OleDb
Module Module1
Public CONN As New OleDbConnection
Public Const condb As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PartDataBase"
Public Sub ConnectDB()
With CONN
If .State = ConnectionState.Open Then .Close()
.ConnectionString = condb
.Open()
End With
End Sub
End Module