اين کد آماده هست فقط بايد جهت اضافه ، ويرايش ، حذف و اضافه ،نام جدول و فيلدها و مقدار آنها و شرط مورد نظر را به اين دستورات بدهيد تا خودکار عمل کند.
کانکشن استرينگ را مطابق فايل خود عوض کنيد.
#Region "SQL Declaration"
Public da As OleDbDataAdapter
Public ds As New DataSet
Public cnn As OleDbConnection
Public CnnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Files\Nodedb.fnb"
Public Where As Contition
Dim ra As Integer
Dim DG As DataGridView = FrmMain.DG
#End Region
#Region "SqlCodes"
''' <summary>
''' اضافه کردن يک رکورد به بانک
''' </summary>
''' <param name="Filds">نام فيلدها: بين آنها ويرگول(،) بگذاريد</param>
''' <param name="Valus">مقادير (مقدار1،مقدار2 ،...)به ترتيب نام فيلدها</param>
''' <remarks></remarks>
Public Sub AddRow(ByVal Table As String, ByVal Filds As String, ByVal Valus As String)
cnn.Open()
Dim sqlstr As String = "Insert INTO " & Table & " (" & Filds & ")VALUES (" & Valus & ")"
Dim cmd As New OleDbCommand(sqlstr, cnn)
ra = cmd.ExecuteNonQuery()
cnn.Close()
End Sub
''' <summary>
''' فراخواني مقاديري از ديتابيس
''' </summary>
''' <param name="table">نام حدول</param>
''' <param name="Fields">نام فيلدهايي که بار شوند. (*) براي بارگزاري همه</param>
''' <param name="where">شرطي که اعمال مي شود مثلا Id=35</param>
''' <remarks>
''' پس از پايان عمليات، تمام نتايج در ديتاگريدي به نام ريخته مي شود به نام
''' DG</remarks>
Public Sub GetRows(ByVal table As String, ByVal Fields As String, Optional ByVal where As String = "")
Dim cnn As OleDbConnection
Dim sql As String
If where = "" Then
sql = "Select " & Fields & " from " & table
Else
sql = "Select " & Fields & " from " & table & " Where " & where
End If
cnn = New OleDbConnection(CnnStr)
cnn.Open()
da = New OleDbDataAdapter(sql, cnn)
ds.Clear()
da.Fill(ds, table)
DG.DataSource = ds.Tables(table)
cnn.Close()
Debug.Print(DG.Rows.Count)
End Sub
Public Sub SearchRows(ByVal table As String, ByVal Fields As String)
SearchRows(table, Fields, "")
End Sub
Public Sub SearchRows(ByVal table As String, ByVal Fields As String, ByVal DG As DataGridView)
SearchRows(table, Fields, "", DG)
End Sub
Public Sub SearchRows(ByVal table As String, ByVal Fields As String, ByVal where As String)
Dim sqlstr As String
If where = "" Then
sqlstr = "Select " & Fields & " from " & table
Else
sqlstr = "Select " & Fields & " from " & table & " Where " & where
End If
If cnn.State = ConnectionState.Open Then cnn.Close()
cnn.ConnectionString = CnnStr.Replace(AppPath & "\", "")
cnn.Open()
da = New OleDbDataAdapter(sqlstr, cnn)
ds.Clear()
da.Fill(ds, table)
DG.DataSource = ds.Tables(table)
cnn.Close()
' Debug.Print(DGV.Rows.Count)
End Sub
Public Sub SearchRows(ByVal table As String, ByVal Fields As String, ByVal where As String, ByVal DGV As DataGridView)
Dim sqlstr As String
If where = "" Then
sqlstr = "Select " & Fields & " from " & table
Else
sqlstr = "Select " & Fields & " from " & table & " Where " & where
End If
If cnn.State = ConnectionState.Open Then cnn.Close()
cnn.ConnectionString = CnnStr.Replace(AppPath & "\", "")
cnn.Open()
da = New OleDbDataAdapter(sqlstr, cnn)
ds.Clear()
da.Fill(ds, table)
DG.DataSource = ds.Tables(table)
cnn.Close()
' Debug.Print(DGV.Rows.Count)
End Sub
''' <summary>
''' به روزرساني جدول و آپديت آن
''' </summary>
''' <param name="table">نام جدولي که بايد آپديت شود.</param>
''' <param name="Fields">آرايه اي از نام فيلدها</param>
''' <param name="values">مقادير مطابق با نام فيلدها(براي مقادير متني کاراکتر 'را نيز به دوطرف آن بيفزاييد مثل 'نام'و غيره</param>
''' <param name="Where">شرط مورد نظر</param>
''' <remarks></remarks>
Public Sub UpdateRow(ByVal table As String, ByVal Fields() As String, ByVal values() As Object, ByVal Where As String)
Dim fld As String = ""
For i As Short = 0 To Fields.Length - 1
fld &= Fields(i) & "=" & values(i) & " ,"
Next
'' fld &= ",,"
' fld = fld.Replace("''", "")
fld &= "|\|"
fld = fld.Replace(",|\|", "")
cnn.Open()
Dim sqlstr As String = "Update " & table & " Set " & fld & " Where " & Where
' sqlstr = "Update Matn Set [Caption]='كتاب' ,[string]='<html dir=rtl><head>',[Tag]='ق',User=False ,Folder=140 ,Rate=3 Where Id=599"
Dim cmd As New OleDbCommand(sqlstr, cnn)
ra = cmd.ExecuteNonQuery()
cnn.Close()
End Sub
Public Sub UpdateRow(ByVal table As String, ByVal Fields As String, ByVal values As Object, ByVal Where As String)
Dim fld As String = ""
' For i As Short = 0 To Fields.Length - 1
fld = Fields & "=" & values & ""
'Next
'Dim con As New OleDb.OleDbConnection
'cnn.ConnectionString = CnnStr
cnn.Open()
' If cnn.State = 0 Then cnn.Open()
Dim sql As String = "Update " & table & " Set " & fld & " Where " & Where
Dim cmn As New OleDbCommand(sql, cnn)
cmn.ExecuteNonQuery()
cnn.Close()
End Sub
''' <summary>
''' حذف يک رکورد از جدول
''' </summary>
''' <param name="table">نام جدولي که يک رديف از آن حذف مي شود</param>
''' <param name="Where">شرط مورد نظر مثل
''' Id=35</param>
''' <remarks></remarks>
Public Sub DeleteRows(ByVal table As String, ByVal Where As String)
cnn.Open()
Dim sql As String = "Delete From " & table & " Where " & Where
Dim cmd As New OleDbCommand(sql, cnn)
ra = cmd.ExecuteNonQuery()
cnn.Close()
End Sub
Public Function GetMax(ByVal Table As String, ByVal fieldName As String) As Integer
Dim sqlstr As String = "Select Max ([" & fieldName & "]) from " & Table
' cnn = New OleDbConnection()
If cnn.State = 1 Then cnn.Close()
cnn.ConnectionString = CnnStr.Replace(Application.StartupPath & "\", "")
cnn.Open()
Dim cmd As New OleDbCommand(sqlstr, cnn)
Dim U As Integer = cmd.ExecuteScalar
'da = New OleDbDataAdapter(sql, cnn)
Debug.Print(DG.Rows.Count)
Return U
End Function
Public Function GetRowsCount(ByVal table As String) As Integer
cnn.Open()
Dim sqlStr As String = "Select Count(*) From " & table
Dim cmd As New OleDbCommand(sqlStr, cnn)
ra = cmd.ExecuteScalar
Return ra
cnn.Close()
End Function
Public Function GetRowsCount(ByVal table As String, ByVal Where As String) As Integer
cnn.Open()
Dim sqlStr As String = "Select Count(*) From " & table & " Where " & Where
Dim cmd As New OleDbCommand(sqlStr, cnn)
ra = cmd.ExecuteScalar
Return ra
cnn.Close()
End Function
Public Function GetOne(ByVal table As String, ByVal Field As String, ByVal Where As String) As Object
If Field.Contains(",") Then
MsgBox("شما فقط بايد يک فيلد وارد کنيد", MsgBoxStyle.Critical)
Return Nothing
Exit Function
End If
cnn.Open()
Dim sqlStr As String = "Select " & Field & " From " & table & " Where " & Where
Dim cmd As New OleDbCommand(sqlStr, cnn)
Dim g As OleDbDataReader = cmd.ExecuteScalar
Return g
cnn.Close()
End Function
#End Region