Entity layer:
Imports system.dataimports system.data.sqlclientimports global_@11- dTAMPORTS
Public Class DatabaseOperate Private Strsqlconn AS String Private Dbconn As SqlConnection
Public Sub New () 'constructor MyBase.New () Dim gp As Global_params = New Global_params Dim ci As CONFIG_INFO = gp.Get_Params ( "g_info") Dim StrServername, StrUser, StrPwd As String StrServername = ci.SERVER_NAME StrUser = ci. USER_ID strPwd = ci.PASSWORD StrSqlConn = "server =" & strServername & "; Initial Catalog = XJOADATA; User Id =" & strUser & "; Password =" & strPwd & ";" dbConn = New SqlConnection (StrSqlConn) End Sub
PUBLIC FUNCTION GETCONNECTION () AS SQLCONNECTION RETURN DBCONN END FUNCTION
Public Function Read (ByVal StrTableName As String, ByVal StrField As String, ByVal StrParameter As String, ByVal StrField2 As String, ByVal StrParameter2 As String) As DataView 'reads the table in the database field and StrParameter StrField value equal strTableName StrField2 field value is equal to StrParameter2 all records Dim DbComm As SqlDataAdapter Dim Ds As New DataSet Dim strSql As String strSql = "select * from" & strTableName & "where" & StrField & "= '" & StrParameter & "' and" & StrField2 & "= '" & StrParameter2 & " '" DbComm = New SqlDataAdapter (strSql, dbConn) DbConn.Open () DbComm.Fill (Ds, strTableName) DbConn.Close () Return ds.Tables (strTableName) .DefaultView End Function
Public Function Delete (ByVal DbConnTran As SqlConnection, ByVal Tx As SqlTransaction, ByVal StrTableName As String, ByVal StrField As String, ByVal StrParameter As String, ByVal StrField2 As String, ByVal StrParameter2 As String) As Integer 'read fields in the database table strTableName StrField equal StrParameter and field StrField2 equal StrParameter2 all records Dim DbComm As SqlCommand Dim strSql As StringStrSql = "delete from" & strTableName & "where" & StrField & "= '" & StrParameter & "' and" & StrField2 & " = '"& Strparameter2 &" "dbcomm = new SQLCommand (strsql, dbconntran, tx)
Dbcomm.executenonQuery ()
END FUNCTION
Public Function Insert (ByVal DbConnTran As SqlConnection, ByVal Tx As SqlTransaction, ByVal Dr As DataRow) As Integer 'data Dr in a transactional manner into the database Dim DbComm As SqlCommand Dim StrField, StrParameter, StrSql As String Dim Count As Integer Dim DataParam as Sqlparameter
Strfield = "" 'formatted fields and parameters in the SQL statement strparameter = "" for count = 0 to Dr.Table.Columns.count - 1 strfield = strfield & dr.table.columns (count) .columnname () & " , "Strparameter = strparameter &" @ "& dr.table.columns (count) .Columnname () &", "next strfield = left (strparameter = left (strparameter) - StrParameter = Left - 1)
STRSQL = "Insert INTO" & Dr.Table.TableName & "Values (" & StrParameter & ")"
Dbcomm = new sqlcommand (strsql, dbconntran, tx) for count = 0 to Dr.Table.columns.count - 1 'Give all parameters to SQL statements DataParam = New SqlParameter ("@" & Dr.Table.Columns (count ) .Columnname (), DR (count) dbcomm.parameters.add (dataparam) Next
DBComm.executenonQuery () 'Performs Insert Operation END FUNCTION
Public Function Update (ByVal DbConnTran As SqlConnection, ByVal Tx As SqlTransaction, ByVal Dr As DataRow) As Integer 'modify data in the database according to the ID Dim DbComm As SqlCommand Dim StrSql As String Dim Count As Integer Dim DataParam As SqlParameter
STRSQL = "" for count = 1 to Dr.Table.Columns.count - 1 strsql = strsql & dr.table.columns (count) .COLUMNNAME & "= @" & dr.table.columns (count) .columnname & " , "Next Strsql = Left (strsql, lin (strsql) - 1) strsql =" Update "& DR.Table.tablename &" Set "& strsql &" where "& dr.table.columns (0) .columnname &" = '"& DR (0) &"' "
Dbcomm = new SQLCOMMAND (strsql, dbconntran, tx)
For count = 1 to Dr.Table.Columns.count - 1 'Give all parameters in the SQL statement DataParam = New Sqlparameter ("@" & Dr.Table.Columns (count) .ColumnName (), DR (count)) DBcomm.Parameters.Add (DataParam) Next
DBcomm.executenonQuery () 'Performs a modification operation END FUNCTION
Public Function ReadSql (ByVal StrSql As String) As DataView Dim DbComm As SqlDataAdapter Dim Ds As New DataSet DbComm = New SqlDataAdapter (StrSql, DbConn) DbConn.Open () DbComm.Fill (Ds) DbConn.Close () Return Ds.Tables ( 0) .defaultView end functionend Class
