Programing Reference For .Net,Java,C

  'ClsSQLBuilder 

  'By YuHonglai 

  'Www.hahaIT.com 

  'Hahasoft@msn.com 

  'Note: Heavy ways to provide the specified table name, by default, is the introduction of the type o + "tbl_", the name of this type must be clsXXX form. 

  'Such as: 

  'Dim Rooms as new clsRooms 

  'SQLBuilder.Add (Rooms) 

  'ClsRooms this procedure will be converted into tbl_Rooms to operate a database table tbl_Rooms 

  'If the name of the database table name and does not have the above relationship, please use the Add (o, "TableName") forms of ways to display the specified database table to the operation of the name 

  Public Class SQLBuilder 

  'When we create the conditions where SQL statements are complex sentences, the constant use as a method FindCondition Select (HashTable) 

  'Key, for example: To generate where Birth <'  2000-4-4   'And Birth>'  1980-1-1   'Complex conditions, with the following methods: 

  'Dim h as new HashTable 

  'H.Add (ComplexSQL, "_Birth <'  2000-4-4   'And _Birth>'  1980-1-1   ' ") 

  'Note, Birth is the entity type attribute name, there must be a front underscore "_" 

  'Treatment, procedures will be used to replace the actual database column names corresponding _ Birth 

  Public Const ComplexSQL As String = "@ ComplexSQL" 

  'Root of the corresponding entities category generated SQL statements Insert … 

  'If the database table name and the corresponding attributes, keywords, it is automatically added value, (DB.XML document the value of a seed) 

  'Then the animal will be neglected, will not appear in the return of a SQL statement Insert … 

  Public Overloads Shared Function Add (ByVal o As Object) As String 

  Dim typeString As String = o.GetType.ToString 

  Dim i As Int16 

  I = typeString.IndexOf ( "cls") + 3 

  TypeString = "tbl_" & typeString.Substring (i, typeString.Length - i) 

  Return Add (o, typeString) 

  End Function 

  Public Overloads Shared Function Add (ByVal o As Object, ByVal TableName As String) As String 

Try

  Dim outSQL As String 

  Dim tmpString As String 

  OutSQL = "insert into [" & TableName & "] (" 

  TmpString = "" 

  Dim dsDB As New DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  Dim row As Data.DataRow 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item ( "seed") & "" = "0" Then 

  OutSQL = outSQL & row.Item ( "dbname") & "" 

  TmpString = tmpString & getS (row.Item ( "type")) & "@" & row.Item ( "dbname") & getS (row.Item ( "type")) & "" 

  End If 

Next

  OutSQL.Substring outSQL = (0, outSQL.Length - 1) 

  TmpString.Substring tmpString = (0, tmpString.Length - 1) 

  OutSQL = outSQL & ") values (" tmpString & & ")" 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item ( "seed") & "and" <> "" Then 

  'TmpString = o.GetType.InvokeMember (row.Item ( "name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object () ()) & "" 

  TmpString = CallByName (o, CType (row.Item ( "name"), String). Trim, CallType.Get) & "" 

  If tmpString = "True" Then 

  TmpString = "1" 

  ElseIf tmpString = "False" Then 

  TmpString = "0" 

  End If 

  OutSQL = outSQL.Replace ( "@" & row.Item ( "dbname"), tmpString) 

  End If 

Next

  Return outSQL.Trim 

  Catch ex As Exception 

  Throw ex 

  End Try 

  End Function 

  'Methods such as Add, keyword, not updated 

  'Keywords, but as the update …. …. where the conditions where there 

  Public Overloads Shared Function Update (ByVal o As Object) As String 

  Dim typeString As String = o.GetType.ToString 

  Dim i As Int16 

  I = typeString.IndexOf ( "cls") + 3 

  TypeString = "tbl_" & typeString.Substring (i, typeString.Length - i) 

  Return Update (o, typeString) 

  End Function 

  Public Overloads Shared Function Update (ByVal o As Object, ByVal TableName As String) As String 

Try

  Dim outString As String = "" 

  Dim tmpString As String 

  OutString = "update [" & TableName & "] set" 

  TmpString = "" 

  Dim whereString As String = "" 

  Dim dsDB As New DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  Dim row As Data.DataRow 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item (the "key") & "" = "1" Then 

  WhereString = whereString & row.Item ( "dbname") & "=" & getS (row.Item ( "type")) & "@" & row.Item ( "dbname") & getS (row.Item ( "type ")) &" and " 

Else

  TmpString = tmpString & row.Item ( "dbname") & "=" & getS (row.Item ( "type")) & "@" & row.Item ( "dbname") & getS (row.Item ( "type ")) &" " 

  End If 

Next

  If whereString.Trim = "" Then 

  Throw New Exception ( "must designate more than one primary key!") 

  End If 

  TmpString.Substring tmpString = (0, tmpString.Length - 1) 

  WhereString.Substring whereString = (0, whereString.Length - 4) 

  OutString tmpString & & = outString "where" & whereString 

  For Each row In dsDB.Tables (TableName). Rows 

  'TmpString = o.GetType.InvokeMember (row.Item ( "name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object () ()) & "" 

  TmpString = CallByName (o, CType (row.Item ( "name"), String). Trim, CallType.Get) & "" 

  If tmpString = "True" Then 

  TmpString = "1" 

  ElseIf tmpString = "False" Then 

  TmpString = "0" 

  End If 

  OutString = outString.Replace ( "@" & row.Item ( "dbname"), tmpString) 

Next

  Return outString.Trim 

  Catch ex As Exception 

  Throw ex 

  End Try 

  End Function 

  'The key attribute more targeted (and the database table of the corresponding keyword) delete the specified records 

  'Object other attributes will be ignored 

  Public Overloads Shared Function Delete (ByVal o As Object) As String 

  Dim typeString As String = o.GetType.ToString 

  Dim i As Int16 

  I = typeString.IndexOf ( "cls") + 3 

  TypeString = "tbl_" & typeString.Substring (i, typeString.Length - i) 

  Return Delete (o, typeString) 

  End Function 

  Public Overloads Shared Function Delete (ByVal o As Object, ByVal TableName As String) As String 

Try

  Dim outString As String = "" 

  Dim tmpString As String 

  OutString = "delete from [" & TableName & "] where" 

  Dim whereString As String = "" 

  Dim dsDB As New DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  Dim row As Data.DataRow 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item (the "key") & "" = "1" Then 

  WhereString = whereString & row.Item ( "dbname") & "=" & getS (row.Item ( "type")) & "@" & row.Item ( "dbname") & getS (row.Item ( "type ")) &" and " 

  End If 

Next

  If whereString.Trim = "" Then 

  Throw New Exception ( "must designate more than one primary key!") 

  End If 

  WhereString.Substring whereString = (0, whereString.Length - 4) 

  OutString = outString & tmpString & whereString 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item (the "key") & "" = "1" Then 

  'TmpString = o.GetType.InvokeMember (row.Item ( "name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object () ()) & "" 

  TmpString = CallByName (o, CType (row.Item ( "name"), String). Trim, CallType.Get) & "" 

  OutString = outString.Replace ( "@" & row.Item ( "dbname"), tmpString) 

  End If 

Next

  Return outString.Trim 

  Catch ex As Exception 

  Throw ex 

  End Try 

  End Function 

  'The key attribute more targeted (and the database table of the corresponding keywords) to determine the existence of the object in the database 

  'Object other attributes will be ignored 

  Public Overloads Shared Function Exists (ByVal o As Object) As String 

  Dim typeString As String = o.GetType.ToString 

  Dim i As Int16 

  I = typeString.IndexOf ( "cls") + 3 

  TypeString = "tbl_" & typeString.Substring (i, typeString.Length - i) 

  Return Exists (o, typeString) 

  End Function 

  Public Overloads Shared Function Exists (ByVal o As Object, ByVal TableName As String) As String 

Try

  Dim outString As String 

  OutString = "select count (*) from [" & TableName & "] where" 

  Dim tmpString As String 

  Dim whereString As String = "" 

  Dim dsDB As New DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  Dim row As Data.DataRow 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item (the "key") & "" = "1" Then 

  WhereString = whereString & row.Item ( "dbname") & "=" & getS (row.Item ( "type")) & "@" & row.Item ( "dbname") & getS (row.Item ( "type ")) &" and " 

  End If 

Next

  If whereString.Trim = "" Then 

  Throw New Exception ( "must designate more than one primary key!") 

  End If 

  WhereString.Substring whereString = (0, whereString.Length - 4) 

  OutString = outString & tmpString & whereString 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item (the "key") & "" = "1" Then 

  'TmpString = o.GetType.InvokeMember (row.Item ( "name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object () ()) & "" 

  TmpString = CallByName (o, CType (row.Item ( "name"), String). Trim, CallType.Get) & "" 

  OutString = outString.Replace ( "@" & row.Item ( "dbname"), tmpString) 

  End If 

Next

  Return outString.Trim 

  Catch ex As Exception 

  Throw ex 

  End Try 

  End Function 

  'First SQL statements generated 

  Public Overloads Shared Function First (ByVal o As Object) As String 

  Dim typeString As String = o.GetType.ToString 

  Dim i As Int16 

  I = typeString.IndexOf ( "cls") + 3 

  TypeString = "tbl_" & typeString.Substring (i, typeString.Length - i) 

  Return First (typeString) 

  End Function 

  Public Overloads Shared Function First (ByVal TableName As String) As String 

  Dim MoudleSQL As String 

  MoudleSQL = "select * from [TableName%%% key] where [%] in (select Min ([% key%]) from [% TableName%])" 

  Dim key As String 

  Dim dsDB As New DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  Dim row As Data.DataRow 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item (the "key") = "1" Then 

  Key = CType (row.Item ( "dbname"), String). Trim 

  Exit For 

  End If 

Next

  MoudleSQL = MoudleSQL.Replace ( "%% TableName" TableName) 

  MoudleSQL = MoudleSQL.Replace ( "%% key" key) 

  Return MoudleSQL 

  End Function 

  Public Overloads Shared Function Last (ByVal o As Object) As String 

  Dim typeString As String = o.GetType.ToString 

  Dim i As Int16 

  I = typeString.IndexOf ( "cls") + 3 

  TypeString = "tbl_" & typeString.Substring (i, typeString.Length - i) 

  Return Last (typeString) 

  End Function 

  Public Overloads Shared Function Last (ByVal TableName As String) As String 

  Dim MoudleSQL As String 

  MoudleSQL = "select * from [TableName%%% key] where [%] in (select Max ([% key%]) from [% TableName%])" 

  Dim key As String 

  Dim dsDB As New DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  Dim row As Data.DataRow 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item (the "key") = "1" Then 

  Key = CType (row.Item ( "dbname"), String). Trim 

  Exit For 

  End If 

Next

  MoudleSQL = MoudleSQL.Replace ( "%% TableName" TableName) 

  MoudleSQL = MoudleSQL.Replace ( "%% key" key) 

  Return MoudleSQL 

  End Function 

  Public Overloads Shared Function Previous (ByVal o As Object) As String 

  Dim typeString As String = o.GetType.ToString 

  Dim i As Int16 

  I = typeString.IndexOf ( "cls") + 3 

  TypeString = "tbl_" & typeString.Substring (i, typeString.Length - i) 

  Return Previous (o, typeString) 

  End Function 

  Public Overloads Shared Function Previous (ByVal o As Object, ByVal TableName As String) As String 

  Dim MoudleSQL As String 

  MoudleSQL = "select * from [TableName%%% key] where [%] in (select Max ([% key%]) from [%% TableName key] where [%%%] <keyValue%)" 

  Dim key As String 

  Dim propertyName As String 

  Dim propertyValue As String 

  Dim dsDB As New DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  Dim row As Data.DataRow 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item (the "key") = "1" Then 

  Key = CType (row.Item ( "dbname"), String). Trim 

  PropertyName = CType (row.Item ( "name"), String). Trim 

  Exit For 

  End If 

Next

  'PropertyValue = o.GetType.InvokeMember (propertyName, Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object () ()) & "" 

  PropertyValue = CallByName (o, propertyName, CallType.Get) & "" 

  MoudleSQL = MoudleSQL.Replace ( "%% TableName" TableName) 

  MoudleSQL = MoudleSQL.Replace ( "%% key" key) 

  MoudleSQL = MoudleSQL.Replace ( "%% keyValue" propertyValue) 

  Return MoudleSQL 

  End Function 

  Public Overloads Shared Function [Next] (ByVal o As Object) As String 

  Dim typeString As String = o.GetType.ToString 

  Dim i As Int16 

  I = typeString.IndexOf ( "cls") + 3 

  TypeString = "tbl_" & typeString.Substring (i, typeString.Length - i) 

  Return [Next] (o, typeString) 

  End Function 

  Public Overloads Shared Function [Next] (ByVal o As Object, ByVal TableName As String) As String 

  Dim MoudleSQL As String 

  MoudleSQL = "select * from [TableName%%% key] where [%] in (select Min ([% key%]) from [TableName%%% key] where [%]>% keyValue%)" 

  Dim key As String 

  Dim propertyName As String 

  Dim propertyValue As String 

  Dim dsDB As New DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  Dim row As Data.DataRow 

  For Each row In dsDB.Tables (TableName). Rows 

  If row.Item (the "key") = "1" Then 

  Key = CType (row.Item ( "dbname"), String). Trim 

  PropertyName = CType (row.Item ( "name"), String). Trim 

  Exit For 

  End If 

Next

  'PropertyValue = o.GetType.InvokeMember (propertyName, Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object () ()) & "" 

  PropertyValue = CallByName (o, propertyName, CallType.Get) & "" 

  MoudleSQL = MoudleSQL.Replace ( "%% TableName" TableName) 

  MoudleSQL = MoudleSQL.Replace ( "%% key" key) 

  MoudleSQL = MoudleSQL.Replace ( "%% keyValue" propertyValue) 

  Return MoudleSQL 

  End Function 

  'See the note public const ComplexSQL 

  Public Shared Function [Select] (ByVal FindCondition As Hashtable, ByVal TableName As String) As String 

  Dim outSQL As String 

  If FindCondition.Contains (ComplexSQL) Then 'Where dealing with complex types of clauses 

  OutSQL = "select * from [" & TableName & "] where" & FindCondition (ComplexSQL) 

  Dim row As Data.DataRow 

  Dim dsDB As New DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  For Each row In dsDB.Tables (TableName). Rows 

  OutSQL = outSQL.Replace ( "_" & CType (row.Item ( "name"), String). Trim, "[" & CType (row.Item ( "dbname"), String). Trim & "]") 

Next

Else

  OutSQL = "select * from [" & TableName & "] where" 

  Dim whereString As String = "" 

  Dim eachKey As Object 

  For Each eachKey In FindCondition.Keys 

  WhereString whereString & = CType (eachKey, String) & "=" & getS (getTypeByName (TableName, CType (eachKey, String))) & FindCondition (eachKey) & getS (getTypeByName (TableName, CType (eachKey, String))) & "and" 

Next

  If whereString.Length = 0 Then 

  WhereString = "0 = 0" 

Else

  WhereString.Substring whereString = (0, whereString.Length - 5) 

  End If 

  OutSQL = outSQL & whereString 

  End If 

  Return outSQL 

  End Function 

  'Back to the designated field (the name of the database table fields) data type name (VB data types) 

  Private Shared Function getTypeByName (ByVal TableName As String, ByVal n As String) As String 

  Dim outStr As String 

  Dim dsDB As New Data.DataSet 

  DsDB.ReadXml (clsPersistant.DBConfigPath) 

  Dim eachRow As DataRow 

  For Each eachRow In dsDB.Tables (TableName). Rows 

  If CType (eachRow.Item ( "dbname"), String). Trim.ToLower = n.Trim.ToLower Then 

  OutStr = CType (eachRow.Item ( "type"), String). Trim.ToLower 

  Exit For 

  End If 

Next

  Return outStr 

  End Function 

  'Root data types a name, to return to space or' SQL statements in the figures and whether the character was' enclose 

  Private Shared Function getS (ByVal t As String) As String 

  Dim outString As String 

  T = t.ToLower.Trim 

  If t = "single" Or t = "int16" Or t = "int32" Or t = "int64" Or t = "double" Or t = "byte" Then 

  OutString = "" 

  Return outString 

  ElseIf t = "date" Or t = "string" Then 

  OutString = " '" 

  Return outString 

  End If 

  End Function 

  End Class 

Releated Articles

• Popuklar Articles