Select the table name on the left to see the associated Class Object, created using SQLDatalayer. Notice in the screen image the Execution time of 0:0:1 (1) second to script 13 tables, scripting a total of 72 Stored procedures. Total lines of all the Stored Procedures: 1516 and a total of 3026 lines in the Class Objects. SQLServer Class is used by the Data layer Class Objects created. |
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:18 PM
Imports System.Data.SqlClient
Public Class [Categories]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetCategories() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetCategories")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetCategories_CategoryID(ByVal [CategoryID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CategoryID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [CategoryID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetCategories_CategoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertCategories(ByVal [CategoryName] As String, _
ByVal [Description] As System.Text.StringBuilder, _
ByVal [Picture] As System.Drawing.Image) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CategoryID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Output
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CategoryName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [CategoryName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Description"
Parm.SqlDbType = SqlDbType.ntext
Parm.Direction = ParameterDirection.Input
parm.Value = [Description].ToString
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Picture"
Parm.SqlDbType = SqlDbType.image
Parm.Direction = ParameterDirection.Input
parm.Value = [Picture]
objParms.Add(Parm)
Try
objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertCategories",objCommand)
Return objParms("@CategoryID").Value
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateCategories_CategoryID(ByVal [CategoryID] As Integer, _
ByVal [CategoryName] As String, _
ByVal [Description] As System.Text.StringBuilder, _
ByVal [Picture] As System.Drawing.Image) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CategoryID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [CategoryID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CategoryName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [CategoryName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Description"
Parm.SqlDbType = SqlDbType.ntext
Parm.Direction = ParameterDirection.Input
parm.Value = [Description].ToString
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Picture"
Parm.SqlDbType = SqlDbType.image
Parm.Direction = ParameterDirection.Input
parm.Value = [Picture]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateCategories_CategoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteCategories_CategoryID(ByVal [CategoryID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CategoryID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [CategoryID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteCategories_CategoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:18 PM
Imports System.Data.SqlClient
Public Class [CustomerCustomerDemo]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetCustomerCustomerDemo() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetCustomerCustomerDemo")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetCustomerCustomerDemo_CustomerID(ByVal [CustomerID] As String) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetCustomerCustomerDemo_CustomerID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetCustomerCustomerDemo_CustomerTypeID(ByVal [CustomerTypeID] As String) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerTypeID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerTypeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetCustomerCustomerDemo_CustomerTypeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertCustomerCustomerDemo(ByVal [CustomerID] As String, _
ByVal [CustomerTypeID] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CustomerTypeID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerTypeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertCustomerCustomerDemo",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateCustomerCustomerDemo_CustomerID_CustomerTypeID(ByVal [CustomerID] As String, _
ByVal [CustomerTypeID] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CustomerTypeID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerTypeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateCustomerCustomerDemo_CustomerID_CustomerTypeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteCustomerCustomerDemo_CustomerID_CustomerTypeID(ByVal [CustomerID] As String, _
ByVal [CustomerTypeID] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CustomerTypeID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerTypeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteCustomerCustomerDemo_CustomerID_CustomerTypeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:18 PM
Imports System.Data.SqlClient
Public Class [CustomerDemographics]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetCustomerDemographics() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetCustomerDemographics")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetCustomerDemographics_CustomerTypeID(ByVal [CustomerTypeID] As String) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerTypeID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerTypeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetCustomerDemographics_CustomerTypeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertCustomerDemographics(ByVal [CustomerTypeID] As String, _
ByVal [CustomerDesc] As System.Text.StringBuilder) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerTypeID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerTypeID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CustomerDesc"
Parm.SqlDbType = SqlDbType.ntext
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerDesc].ToString
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertCustomerDemographics",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateCustomerDemographics_CustomerTypeID(ByVal [CustomerTypeID] As String, _
ByVal [CustomerDesc] As System.Text.StringBuilder) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerTypeID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerTypeID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CustomerDesc"
Parm.SqlDbType = SqlDbType.ntext
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerDesc].ToString
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateCustomerDemographics_CustomerTypeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteCustomerDemographics_CustomerTypeID(ByVal [CustomerTypeID] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerTypeID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerTypeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteCustomerDemographics_CustomerTypeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:18 PM
Imports System.Data.SqlClient
Public Class [Customers]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetCustomers() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetCustomers")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetCustomers_CustomerID(ByVal [CustomerID] As String) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetCustomers_CustomerID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertCustomers(ByVal [CustomerID] As String, _
ByVal [CompanyName] As String, _
ByVal [ContactName] As String, _
ByVal [ContactTitle] As String, _
ByVal [Address] As String, _
ByVal [City] As String, _
ByVal [Region] As String, _
ByVal [PostalCode] As String, _
ByVal [Country] As String, _
ByVal [Phone] As String, _
ByVal [Fax] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CompanyName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [CompanyName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ContactName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [ContactName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ContactTitle"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [ContactTitle]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Address"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 120
Parm.Direction = ParameterDirection.Input
parm.Value = [Address]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@City"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [City]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Region"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Region]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@PostalCode"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [PostalCode]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Country"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Country]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Phone"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Phone]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Fax"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Fax]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertCustomers",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateCustomers_CustomerID(ByVal [CustomerID] As String, _
ByVal [CompanyName] As String, _
ByVal [ContactName] As String, _
ByVal [ContactTitle] As String, _
ByVal [Address] As String, _
ByVal [City] As String, _
ByVal [Region] As String, _
ByVal [PostalCode] As String, _
ByVal [Country] As String, _
ByVal [Phone] As String, _
ByVal [Fax] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CompanyName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [CompanyName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ContactName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [ContactName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ContactTitle"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [ContactTitle]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Address"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 120
Parm.Direction = ParameterDirection.Input
parm.Value = [Address]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@City"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [City]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Region"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Region]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@PostalCode"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [PostalCode]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Country"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Country]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Phone"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Phone]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Fax"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Fax]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateCustomers_CustomerID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteCustomers_CustomerID(ByVal [CustomerID] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteCustomers_CustomerID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:18 PM
Imports System.Data.SqlClient
Public Class [Employees]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetEmployees() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetEmployees")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetEmployees_EmployeeID(ByVal [EmployeeID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetEmployees_EmployeeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetEmployees_ReportsTo(ByVal [ReportsTo] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ReportsTo"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ReportsTo]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetEmployees_ReportsTo",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertEmployees(ByVal [LastName] As String, _
ByVal [FirstName] As String, _
ByVal [Title] As String, _
ByVal [TitleOfCourtesy] As String, _
ByVal [BirthDate] As Datetime, _
ByVal [HireDate] As Datetime, _
ByVal [Address] As String, _
ByVal [City] As String, _
ByVal [Region] As String, _
ByVal [PostalCode] As String, _
ByVal [Country] As String, _
ByVal [HomePhone] As String, _
ByVal [Extension] As String, _
ByVal [Photo] As System.Drawing.Image, _
ByVal [Notes] As System.Text.StringBuilder, _
ByVal [ReportsTo] As Integer, _
ByVal [PhotoPath] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Output
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@LastName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [LastName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@FirstName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [FirstName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Title"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [Title]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@TitleOfCourtesy"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 50
Parm.Direction = ParameterDirection.Input
parm.Value = [TitleOfCourtesy]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@BirthDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [BirthDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@HireDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [HireDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Address"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 120
Parm.Direction = ParameterDirection.Input
parm.Value = [Address]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@City"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [City]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Region"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Region]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@PostalCode"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [PostalCode]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Country"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Country]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@HomePhone"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [HomePhone]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Extension"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 8
Parm.Direction = ParameterDirection.Input
parm.Value = [Extension]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Photo"
Parm.SqlDbType = SqlDbType.image
Parm.Direction = ParameterDirection.Input
parm.Value = [Photo]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Notes"
Parm.SqlDbType = SqlDbType.ntext
Parm.Direction = ParameterDirection.Input
parm.Value = [Notes].ToString
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ReportsTo"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ReportsTo]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@PhotoPath"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 510
Parm.Direction = ParameterDirection.Input
parm.Value = [PhotoPath]
objParms.Add(Parm)
Try
objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertEmployees",objCommand)
Return objParms("@EmployeeID").Value
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateEmployees_EmployeeID(ByVal [EmployeeID] As Integer, _
ByVal [LastName] As String, _
ByVal [FirstName] As String, _
ByVal [Title] As String, _
ByVal [TitleOfCourtesy] As String, _
ByVal [BirthDate] As Datetime, _
ByVal [HireDate] As Datetime, _
ByVal [Address] As String, _
ByVal [City] As String, _
ByVal [Region] As String, _
ByVal [PostalCode] As String, _
ByVal [Country] As String, _
ByVal [HomePhone] As String, _
ByVal [Extension] As String, _
ByVal [Photo] As System.Drawing.Image, _
ByVal [Notes] As System.Text.StringBuilder, _
ByVal [ReportsTo] As Integer, _
ByVal [PhotoPath] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@LastName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [LastName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@FirstName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [FirstName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Title"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [Title]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@TitleOfCourtesy"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 50
Parm.Direction = ParameterDirection.Input
parm.Value = [TitleOfCourtesy]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@BirthDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [BirthDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@HireDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [HireDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Address"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 120
Parm.Direction = ParameterDirection.Input
parm.Value = [Address]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@City"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [City]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Region"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Region]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@PostalCode"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [PostalCode]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Country"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Country]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@HomePhone"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [HomePhone]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Extension"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 8
Parm.Direction = ParameterDirection.Input
parm.Value = [Extension]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Photo"
Parm.SqlDbType = SqlDbType.image
Parm.Direction = ParameterDirection.Input
parm.Value = [Photo]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Notes"
Parm.SqlDbType = SqlDbType.ntext
Parm.Direction = ParameterDirection.Input
parm.Value = [Notes].ToString
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ReportsTo"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ReportsTo]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@PhotoPath"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 510
Parm.Direction = ParameterDirection.Input
parm.Value = [PhotoPath]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateEmployees_EmployeeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteEmployees_EmployeeID(ByVal [EmployeeID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteEmployees_EmployeeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:18 PM
Imports System.Data.SqlClient
Public Class [EmployeeTerritories]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetEmployeeTerritories() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetEmployeeTerritories")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetEmployeeTerritories_EmployeeID(ByVal [EmployeeID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetEmployeeTerritories_EmployeeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetEmployeeTerritories_TerritoryID(ByVal [TerritoryID] As String) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryID"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetEmployeeTerritories_TerritoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertEmployeeTerritories(ByVal [EmployeeID] As Integer, _
ByVal [TerritoryID] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryID"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertEmployeeTerritories",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateEmployeeTerritories_EmployeeID_TerritoryID(ByVal [EmployeeID] As Integer, _
ByVal [TerritoryID] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryID"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateEmployeeTerritories_EmployeeID_TerritoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteEmployeeTerritories_EmployeeID_TerritoryID(ByVal [EmployeeID] As Integer, _
ByVal [TerritoryID] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryID"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteEmployeeTerritories_EmployeeID_TerritoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:18 PM
Imports System.Data.SqlClient
Public Class [Order Details]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetOrder Details() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetOrder Details")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetOrder Details_OrderID(ByVal [OrderID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@OrderID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [OrderID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetOrder Details_OrderID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetOrder Details_ProductID(ByVal [ProductID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ProductID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ProductID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetOrder Details_ProductID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertOrder Details(ByVal [OrderID] As Integer, _
ByVal [ProductID] As Integer, _
ByVal [UnitPrice] As Decimal, _
ByVal [Quantity] As Integer, _
ByVal [Discount] As Int64) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@OrderID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [OrderID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ProductID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ProductID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@UnitPrice"
Parm.SqlDbType = SqlDbType.money
Parm.Direction = ParameterDirection.Input
parm.Value = [UnitPrice]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Quantity"
Parm.SqlDbType = SqlDbType.smallint
Parm.Direction = ParameterDirection.Input
parm.Value = [Quantity]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Discount"
Parm.SqlDbType = SqlDbType.real
Parm.Direction = ParameterDirection.Input
parm.Value = [Discount]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertOrder Details",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateOrder Details_OrderID_ProductID(ByVal [OrderID] As Integer, _
ByVal [ProductID] As Integer, _
ByVal [UnitPrice] As Decimal, _
ByVal [Quantity] As Integer, _
ByVal [Discount] As Int64) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@OrderID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [OrderID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ProductID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ProductID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@UnitPrice"
Parm.SqlDbType = SqlDbType.money
Parm.Direction = ParameterDirection.Input
parm.Value = [UnitPrice]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Quantity"
Parm.SqlDbType = SqlDbType.smallint
Parm.Direction = ParameterDirection.Input
parm.Value = [Quantity]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Discount"
Parm.SqlDbType = SqlDbType.real
Parm.Direction = ParameterDirection.Input
parm.Value = [Discount]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateOrder Details_OrderID_ProductID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteOrder Details_OrderID_ProductID(ByVal [OrderID] As Integer, _
ByVal [ProductID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@OrderID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [OrderID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ProductID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ProductID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteOrder Details_OrderID_ProductID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:18 PM
Imports System.Data.SqlClient
Public Class [Orders]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetOrders() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetOrders")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetOrders_OrderID(ByVal [OrderID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@OrderID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [OrderID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetOrders_OrderID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetOrders_CustomerID(ByVal [CustomerID] As String) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetOrders_CustomerID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetOrders_EmployeeID(ByVal [EmployeeID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetOrders_EmployeeID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetOrders_ShipVia(ByVal [ShipVia] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ShipVia"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipVia]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetOrders_ShipVia",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertOrders(ByVal [CustomerID] As String, _
ByVal [EmployeeID] As Integer, _
ByVal [OrderDate] As Datetime, _
ByVal [RequiredDate] As Datetime, _
ByVal [ShippedDate] As Datetime, _
ByVal [ShipVia] As Integer, _
ByVal [Freight] As Decimal, _
ByVal [ShipName] As String, _
ByVal [ShipAddress] As String, _
ByVal [ShipCity] As String, _
ByVal [ShipRegion] As String, _
ByVal [ShipPostalCode] As String, _
ByVal [ShipCountry] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@OrderID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Output
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@OrderDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [OrderDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@RequiredDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [RequiredDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShippedDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [ShippedDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipVia"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipVia]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Freight"
Parm.SqlDbType = SqlDbType.money
Parm.Direction = ParameterDirection.Input
parm.Value = [Freight]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipAddress"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 120
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipAddress]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipCity"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipCity]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipRegion"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipRegion]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipPostalCode"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipPostalCode]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipCountry"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipCountry]
objParms.Add(Parm)
Try
objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertOrders",objCommand)
Return objParms("@OrderID").Value
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateOrders_OrderID(ByVal [OrderID] As Integer, _
ByVal [CustomerID] As String, _
ByVal [EmployeeID] As Integer, _
ByVal [OrderDate] As Datetime, _
ByVal [RequiredDate] As Datetime, _
ByVal [ShippedDate] As Datetime, _
ByVal [ShipVia] As Integer, _
ByVal [Freight] As Decimal, _
ByVal [ShipName] As String, _
ByVal [ShipAddress] As String, _
ByVal [ShipCity] As String, _
ByVal [ShipRegion] As String, _
ByVal [ShipPostalCode] As String, _
ByVal [ShipCountry] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@OrderID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [OrderID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CustomerID"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 10
Parm.Direction = ParameterDirection.Input
parm.Value = [CustomerID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@EmployeeID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [EmployeeID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@OrderDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [OrderDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@RequiredDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [RequiredDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShippedDate"
Parm.SqlDbType = SqlDbType.datetime
Parm.Direction = ParameterDirection.Input
parm.Value = [ShippedDate]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipVia"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipVia]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Freight"
Parm.SqlDbType = SqlDbType.money
Parm.Direction = ParameterDirection.Input
parm.Value = [Freight]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipAddress"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 120
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipAddress]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipCity"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipCity]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipRegion"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipRegion]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipPostalCode"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipPostalCode]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ShipCountry"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipCountry]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateOrders_OrderID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteOrders_OrderID(ByVal [OrderID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@OrderID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [OrderID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteOrders_OrderID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:19 PM
Imports System.Data.SqlClient
Public Class [Products]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetProducts() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetProducts")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetProducts_ProductID(ByVal [ProductID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ProductID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ProductID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetProducts_ProductID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetProducts_CategoryID(ByVal [CategoryID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@CategoryID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [CategoryID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetProducts_CategoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetProducts_SupplierID(ByVal [SupplierID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@SupplierID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [SupplierID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetProducts_SupplierID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertProducts(ByVal [ProductName] As String, _
ByVal [SupplierID] As Integer, _
ByVal [CategoryID] As Integer, _
ByVal [QuantityPerUnit] As String, _
ByVal [UnitPrice] As Decimal, _
ByVal [UnitsInStock] As Integer, _
ByVal [UnitsOnOrder] As Integer, _
ByVal [ReorderLevel] As Integer, _
ByVal [Discontinued] As Boolean) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ProductID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Output
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ProductName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [ProductName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@SupplierID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [SupplierID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CategoryID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [CategoryID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@QuantityPerUnit"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [QuantityPerUnit]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@UnitPrice"
Parm.SqlDbType = SqlDbType.money
Parm.Direction = ParameterDirection.Input
parm.Value = [UnitPrice]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@UnitsInStock"
Parm.SqlDbType = SqlDbType.smallint
Parm.Direction = ParameterDirection.Input
parm.Value = [UnitsInStock]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@UnitsOnOrder"
Parm.SqlDbType = SqlDbType.smallint
Parm.Direction = ParameterDirection.Input
parm.Value = [UnitsOnOrder]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ReorderLevel"
Parm.SqlDbType = SqlDbType.smallint
Parm.Direction = ParameterDirection.Input
parm.Value = [ReorderLevel]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Discontinued"
Parm.SqlDbType = SqlDbType.bit
Parm.Direction = ParameterDirection.Input
parm.Value = [Discontinued]
objParms.Add(Parm)
Try
objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertProducts",objCommand)
Return objParms("@ProductID").Value
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateProducts_ProductID(ByVal [ProductID] As Integer, _
ByVal [ProductName] As String, _
ByVal [SupplierID] As Integer, _
ByVal [CategoryID] As Integer, _
ByVal [QuantityPerUnit] As String, _
ByVal [UnitPrice] As Decimal, _
ByVal [UnitsInStock] As Integer, _
ByVal [UnitsOnOrder] As Integer, _
ByVal [ReorderLevel] As Integer, _
ByVal [Discontinued] As Boolean) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ProductID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ProductID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ProductName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [ProductName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@SupplierID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [SupplierID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CategoryID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [CategoryID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@QuantityPerUnit"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [QuantityPerUnit]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@UnitPrice"
Parm.SqlDbType = SqlDbType.money
Parm.Direction = ParameterDirection.Input
parm.Value = [UnitPrice]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@UnitsInStock"
Parm.SqlDbType = SqlDbType.smallint
Parm.Direction = ParameterDirection.Input
parm.Value = [UnitsInStock]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@UnitsOnOrder"
Parm.SqlDbType = SqlDbType.smallint
Parm.Direction = ParameterDirection.Input
parm.Value = [UnitsOnOrder]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ReorderLevel"
Parm.SqlDbType = SqlDbType.smallint
Parm.Direction = ParameterDirection.Input
parm.Value = [ReorderLevel]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Discontinued"
Parm.SqlDbType = SqlDbType.bit
Parm.Direction = ParameterDirection.Input
parm.Value = [Discontinued]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateProducts_ProductID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteProducts_ProductID(ByVal [ProductID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ProductID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ProductID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteProducts_ProductID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:19 PM
Imports System.Data.SqlClient
Public Class [Region]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetRegion() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetRegion")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetRegion_RegionID(ByVal [RegionID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@RegionID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [RegionID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetRegion_RegionID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertRegion(ByVal [RegionID] As Integer, _
ByVal [RegionDescription] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@RegionID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [RegionID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@RegionDescription"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 100
Parm.Direction = ParameterDirection.Input
parm.Value = [RegionDescription]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertRegion",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateRegion_RegionID(ByVal [RegionID] As Integer, _
ByVal [RegionDescription] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@RegionID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [RegionID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@RegionDescription"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 100
Parm.Direction = ParameterDirection.Input
parm.Value = [RegionDescription]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateRegion_RegionID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteRegion_RegionID(ByVal [RegionID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@RegionID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [RegionID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteRegion_RegionID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:19 PM
Imports System.Data.SqlClient
Public Class [Shippers]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetShippers() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetShippers")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetShippers_ShipperID(ByVal [ShipperID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ShipperID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipperID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetShippers_ShipperID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertShippers(ByVal [CompanyName] As String, _
ByVal [Phone] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ShipperID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Output
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CompanyName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [CompanyName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Phone"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Phone]
objParms.Add(Parm)
Try
objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertShippers",objCommand)
Return objParms("@ShipperID").Value
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateShippers_ShipperID(ByVal [ShipperID] As Integer, _
ByVal [CompanyName] As String, _
ByVal [Phone] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ShipperID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipperID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CompanyName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [CompanyName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Phone"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Phone]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateShippers_ShipperID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteShippers_ShipperID(ByVal [ShipperID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@ShipperID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [ShipperID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteShippers_ShipperID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:19 PM
Imports System.Data.SqlClient
Public Class [Suppliers]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetSuppliers() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetSuppliers")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetSuppliers_SupplierID(ByVal [SupplierID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@SupplierID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [SupplierID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetSuppliers_SupplierID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertSuppliers(ByVal [CompanyName] As String, _
ByVal [ContactName] As String, _
ByVal [ContactTitle] As String, _
ByVal [Address] As String, _
ByVal [City] As String, _
ByVal [Region] As String, _
ByVal [PostalCode] As String, _
ByVal [Country] As String, _
ByVal [Phone] As String, _
ByVal [Fax] As String, _
ByVal [HomePage] As System.Text.StringBuilder) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@SupplierID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Output
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CompanyName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [CompanyName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ContactName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [ContactName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ContactTitle"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [ContactTitle]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Address"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 120
Parm.Direction = ParameterDirection.Input
parm.Value = [Address]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@City"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [City]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Region"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Region]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@PostalCode"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [PostalCode]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Country"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Country]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Phone"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Phone]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Fax"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Fax]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@HomePage"
Parm.SqlDbType = SqlDbType.ntext
Parm.Direction = ParameterDirection.Input
parm.Value = [HomePage].ToString
objParms.Add(Parm)
Try
objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertSuppliers",objCommand)
Return objParms("@SupplierID").Value
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateSuppliers_SupplierID(ByVal [SupplierID] As Integer, _
ByVal [CompanyName] As String, _
ByVal [ContactName] As String, _
ByVal [ContactTitle] As String, _
ByVal [Address] As String, _
ByVal [City] As String, _
ByVal [Region] As String, _
ByVal [PostalCode] As String, _
ByVal [Country] As String, _
ByVal [Phone] As String, _
ByVal [Fax] As String, _
ByVal [HomePage] As System.Text.StringBuilder) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@SupplierID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [SupplierID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@CompanyName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 80
Parm.Direction = ParameterDirection.Input
parm.Value = [CompanyName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ContactName"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [ContactName]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@ContactTitle"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 60
Parm.Direction = ParameterDirection.Input
parm.Value = [ContactTitle]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Address"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 120
Parm.Direction = ParameterDirection.Input
parm.Value = [Address]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@City"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [City]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Region"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Region]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@PostalCode"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 20
Parm.Direction = ParameterDirection.Input
parm.Value = [PostalCode]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Country"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 30
Parm.Direction = ParameterDirection.Input
parm.Value = [Country]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Phone"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Phone]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@Fax"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 48
Parm.Direction = ParameterDirection.Input
parm.Value = [Fax]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@HomePage"
Parm.SqlDbType = SqlDbType.ntext
Parm.Direction = ParameterDirection.Input
parm.Value = [HomePage].ToString
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateSuppliers_SupplierID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteSuppliers_SupplierID(ByVal [SupplierID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@SupplierID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [SupplierID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteSuppliers_SupplierID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'Scripted using SQLDataLayer http://www.SQLDataLayer.com/ 2/6/2006 4:53:19 PM
Imports System.Data.SqlClient
Public Class [Territories]
Private objSQLServer As New SQLserver
Private objCommand As New SqlCommand
Private Parm As SqlParameter
Private objParms As SqlParameterCollection = CType(objCommand.Parameters, SqlParameterCollection)
#Region "Select"
Public Overloads Function GetTerritories() AS DataTable
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetTerritories")
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetTerritories_TerritoryID(ByVal [TerritoryID] As String) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryID"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetTerritories_TerritoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
Public Overloads Function GetTerritories_RegionID(ByVal [RegionID] As Integer) AS DataTable
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@RegionID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [RegionID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteDataTable_StoredProcedure("fsp_GetTerritories_RegionID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Insert"
Public Overloads Function InsertTerritories(ByVal [TerritoryID] As String, _
ByVal [TerritoryDescription] As String, _
ByVal [RegionID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryID"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryDescription"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 100
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryDescription]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@RegionID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [RegionID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_InsertTerritories",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Update"
Public Overloads Function UpdateTerritories_TerritoryID(ByVal [TerritoryID] As String, _
ByVal [TerritoryDescription] As String, _
ByVal [RegionID] As Integer) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryID"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryID]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryDescription"
Parm.SqlDbType = SqlDbType.nchar
Parm.Size = 100
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryDescription]
objParms.Add(Parm)
Parm = New SqlParameter
Parm.ParameterName = "@RegionID"
Parm.SqlDbType = SqlDbType.int
Parm.Direction = ParameterDirection.Input
parm.Value = [RegionID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_UpdateTerritories_TerritoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
#Region "Delete"
Public Overloads Function DeleteTerritories_TerritoryID(ByVal [TerritoryID] As String) AS VariantType
objParms.Clear()
Parm = New SqlParameter
Parm.ParameterName = "@TerritoryID"
Parm.SqlDbType = SqlDbType.nvarchar
Parm.Size = 40
Parm.Direction = ParameterDirection.Input
parm.Value = [TerritoryID]
objParms.Add(Parm)
Try
Return objSQLServer.ExecuteNonQuery_StoredProcedure("fsp_DeleteTerritories_TerritoryID",objCommand)
Catch ex As System.Exception
Throw ex
End Try
End Function
#End Region
End Class
|
'//************************************************************************************
'// Copyright (C) 2006 - JTN Development
'//
'// Code by: R. Barnes
'// Comments by: R. Barnes
'// Created: 07, 2005
'// http://www.sqldatalayer.com
'//************************************************************************************
'// Modifications: None - Original Release 1.0
'//************************************************************************************
Imports System.Data.SqlClient
Public Class SQLServer
#Region "Members"
Private m_objConnection As SqlConnection
Private m_strConnectionString As String
#End Region
#Region "Properties"
Protected ReadOnly Property ConnectionString() As String
Get
m_strConnectionString = System.Configuration.ConfigurationSettings.AppSettings("DatabaseConnection")
Return m_strConnectionString
End Get
End Property
#End Region
#Region "Methods"
Public Sub InitializeConnection()
'create the connection to the database
m_objConnection = New SqlConnection
m_objConnection.ConnectionString = ConnectionString
End Sub
#End Region
#Region "ExecuteDataSet"
Public Overloads Function ExecuteDataSet_StoredProcedure(ByVal strStoredProcedure As String, ByVal strTable As String, ByVal ParamArray aParameters() As SqlParameter) As DataSet
Try
'open the connection
InitializeConnection()
'establish the command
Dim objCommand As New SqlCommand(strStoredProcedure, m_objConnection)
objCommand.CommandType = CommandType.StoredProcedure
'check for sqlparameters and add them to the command
Dim objParameter As SqlParameter
For Each objParameter In aParameters
objCommand.Parameters.Add(objParameter)
Next
'setup the data adapter
Dim objDataAdapter As New SqlDataAdapter(objCommand)
'setup and populate the dataset
Dim objDataSet As New DataSet
objDataAdapter.Fill(objDataSet, strTable)
Return objDataSet
Catch ex As System.Exception
Throw ex
Finally
m_objConnection.Close()
m_objConnection = Nothing
End Try
End Function
Public Overloads Function ExecuteDataSet_StoredProcedure(ByVal strStoredProcedure As String, ByVal strTable As String) As DataSet
Try
'open the connection
InitializeConnection()
'establish the command
Dim objCommand As New SqlCommand(strStoredProcedure, m_objConnection)
objCommand.CommandType = CommandType.StoredProcedure
'setup the data adapter
Dim objDataAdapter As New SqlDataAdapter(objCommand)
'setup and populate the dataset
Dim objDataSet As New DataSet
objDataAdapter.Fill(objDataSet, strTable)
Return objDataSet
Catch ex As System.Exception
Throw ex
Finally
m_objConnection.Close()
m_objConnection = Nothing
End Try
End Function
#End Region
#Region "ExecuteDataTable"
Public Overloads Function ExecuteDataTable_StoredProcedure(ByVal strStoredProcedure As String, ByVal ParamArray aParameters() As SqlParameter) As DataTable
Try
'open the connection
InitializeConnection()
'establish the command
Dim objCommand As New SqlCommand(strStoredProcedure, m_objConnection)
objCommand.CommandType = CommandType.StoredProcedure
'check for sqlparameters and add them to the command
Dim objParameter As SqlParameter
For Each objParameter In aParameters
objCommand.Parameters.Add(objParameter)
Next
'setup the data adapter
Dim objDataAdapter As New SqlDataAdapter(objCommand)
'setup and populate the dataset
Dim objDataTable As New DataTable
objDataAdapter.Fill(objDataTable)
Return objDataTable
Catch ex As System.Exception
Throw ex
Finally
m_objConnection.Close()
m_objConnection = Nothing
End Try
End Function
Public Overloads Function ExecuteDataTable_StoredProcedure(ByVal strStoredProcedure As String, ByVal objCommand As SqlCommand) As DataTable
Try
'open the connection
InitializeConnection()
m_objConnection.Open()
'establish the command
objCommand.Connection = m_objConnection
objCommand.CommandText = strStoredProcedure
objCommand.CommandType = CommandType.StoredProcedure
'setup the data adapter
Dim objDataAdapter As New SqlDataAdapter(objCommand)
'setup and populate the dataset
Dim objDataTable As New DataTable
objDataAdapter.Fill(objDataTable)
Return objDataTable
Catch ex As System.Exception
Throw ex
Finally
m_objConnection.Close()
m_objConnection = Nothing
End Try
End Function
Public Overloads Function ExecuteDataTable_StoredProcedure(ByVal strStoredProcedure As String) As DataTable
Try
'open the connection
InitializeConnection()
'establish the command
Dim objCommand As New SqlCommand(strStoredProcedure, m_objConnection)
objCommand.CommandType = CommandType.StoredProcedure
'setup the data adapter
Dim objDataAdapter As New SqlDataAdapter(objCommand)
'setup and populate the dataset
Dim objDataTable As New DataTable
objDataAdapter.Fill(objDataTable)
Return objDataTable
Catch ex As System.Exception
Throw ex
Finally
m_objConnection.Close()
m_objConnection = Nothing
End Try
End Function
#End Region
#Region "ExecuteDataReader"
Public Function ExecuteDataReader_StoredProcedure(ByVal strStoredProcedure As String, ByVal strTable As String, ByVal ParamArray aParameters() As SqlParameter) As SqlDataReader
Try
'open the connection
InitializeConnection()
'establish the command
Dim objCommand As New SqlCommand(strStoredProcedure, m_objConnection)
objCommand.CommandType = CommandType.StoredProcedure
'check for sqlparameters and add them to the command
Dim objParameter As SqlParameter
For Each objParameter In aParameters
objCommand.Parameters.Add(objParameter)
Next
'setup and populate the datareader
Dim objDataReader As SqlDataReader
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return objDataReader
Catch ex As System.Exception
Throw ex
Finally
'objConnection.Dispose()
End Try
End Function
#End Region
#Region "ExecuteNonQuery"
Public Function ExecuteNonQuery_StoredProcedure(ByVal strStoredProcedure As String, ByVal ParamArray aParameters() As SqlParameter) As VariantType
Try
'open the connection
InitializeConnection()
m_objConnection.Open()
'establish the command
Dim objCommand As New SqlCommand(strStoredProcedure, m_objConnection)
objCommand.CommandType = CommandType.StoredProcedure
'check for sqlparameters and add them to the command
Dim objParameter As SqlParameter
For Each objParameter In aParameters
objCommand.Parameters.Add(objParameter)
Next
Return objCommand.ExecuteNonQuery()
Catch ex As System.Exception
Throw ex
Finally
m_objConnection.Close()
m_objConnection = Nothing
End Try
End Function
Public Function ExecuteNonQuery_StoredProcedure(ByVal strStoredProcedure As String, ByVal objCommand As SqlCommand) As VariantType
Try
'open the connection
InitializeConnection()
m_objConnection.Open()
'establish the command
objCommand.Connection = m_objConnection
objCommand.CommandText = strStoredProcedure
objCommand.CommandType = CommandType.StoredProcedure
Return objCommand.ExecuteNonQuery()
Catch ex As System.Exception
Throw ex
Finally
m_objConnection.Close()
m_objConnection = Nothing
End Try
End Function
#End Region
#Region "ExecuteScalar"
Public Overloads Function ExecuteScalar_StoredProcedure(ByVal strStoredProcedure As String, ByVal ParamArray aParameters() As SqlParameter) As VariantType
Try
'open the connection
InitializeConnection()
m_objConnection.Open()
'establish the command
Dim objCommand As New SqlCommand(strStoredProcedure, m_objConnection)
objCommand.CommandType = CommandType.StoredProcedure
'check for sqlparameters and add them to the command
Dim objParameter As SqlParameter
For Each objParameter In aParameters
objCommand.Parameters.Add(objParameter)
Next
Return objCommand.ExecuteScalar
Catch ex As System.Exception
Throw ex
Finally
m_objConnection.Close()
m_objConnection = Nothing
End Try
End Function
#End Region
End Class
|