Posted: Fri Sep 22, 2006 7:07 pm Post subject: کمک!
با سلام
حدودا یک هفته است که که در کار با پایگاه داده vb.net دچار مشکل شدم و هیچ کسی نتونسته کمکم کنه و کلی وقتم تلف شده ممکنه تو این بخش سوالم رو بپرسم؟
Joined: 27 Nov 2004 Posts: 1232 Location: www.parsx.com
Posted: Fri Sep 22, 2006 11:17 pm Post subject:
به جای سوال این کلاس رو تو پروژه اضافه کن
البته مال من نیست برای همین اسم فامیل روشه
وقتی این کلاس رو به پروژه اضافه کردی فقط کافیه دستورات زیر رو بنویسی
برو حال کن
' ************************************************************************************
' In the name of god
' This Class Help you to use ADO.Net 2.0
' Provider: Niloufar
' ************************************************************************************
Imports System
Imports System.Data
Public Class clsADO
'Public part
''' <summary>
''' This Constractor is for MsAccess DataBases
''' </summary>
''' <param name="strPathAndFileName">MsAccess DataBase full file name (Path\FileName)</param>
''' <remarks></remarks>
Public Sub New(ByVal strPathAndFileName As String)
DataBaseType = DataBaseTypes.MsAccess
strlocalPathAndFileName = strPathAndFileName
End Sub
''' <summary>
''' This constractor is for MsSqlServer DataBases
''' </summary>
''' <param name="strServerName">MsSqlServer Server Name</param>
''' <param name="strDataBaseName">DataBase Name</param>
''' <param name="strUserId">User Id</param>
''' <param name="strPassword">Password</param>
''' <remarks></remarks>
Public Sub New(ByVal strServerName As String, ByVal strDataBaseName As String, ByVal strUserId As String, ByVal strPassword As String)
DataBaseType = DataBaseTypes.MsSqlServer
strlocalServerName = strServerName
strlocalDataBaseName = strDataBaseName
strlocalUserId = strUserId
strlocalPassword = strPassword
End Sub
''' <summary>
''' This constractor is for Oracle DataBases
''' </summary>
''' <param name="strSID">Oracle System Identifier</param>
''' <param name="strUserId">User Id</param>
''' <param name="strPassword">Password</param>
''' <remarks></remarks>
Public Sub New(ByVal strSID As String, ByVal strUserId As String, ByVal strPassword As String)
DataBaseType = DataBaseTypes.Oracle
strlocalSID = strSID
strlocalUserId = strUserId
strlocalPassword = strPassword
End Sub
''' <summary>
''' Dispose this class
''' </summary>
''' <param name="Disposing">Set to TRUE if you want to dispose any objects used in this class and set them to nothing</param>
''' <remarks></remarks>
Public Overloads Sub Dispose(ByVal Disposing As Boolean)
If Not Me.Disposed Then
If Disposing Then
If Not objConnection Is Nothing Then
If objConnection.State <> ConnectionState.Closed Then
objConnection.Close()
End If
objConnection.Dispose()
objConnection = Nothing
End If
If Not objCommand Is Nothing Then
objCommand.Dispose()
objCommand = Nothing
End If
If Not objDataAdapter Is Nothing Then
objDataAdapter.Dispose()
objDataAdapter = Nothing
End If
Me.Disposed = True
End If
End If
End Sub
''' <summary>
''' Get a DataTable object by a text query
''' </summary>
''' <param name="strQuery">SELECT query that you want to fill DataTable by this query</param>
''' <returns>DataTable object filled by entered query</returns>
''' <remarks></remarks>
Public Function GetDataTableByQuery(ByVal strQuery As String) As DataTable
Select Case DataBaseType
Case DataBaseTypes.MsAccess
Call OpenMsAccessConnection(strlocalPathAndFileName)
Case DataBaseTypes.MsSqlServer
Call OpenMsSqlServerConnection(strlocalServerName, strlocalDataBaseName, strlocalUserId, strlocalPassword)
Case DataBaseTypes.Oracle
Call OpenOracleConnection(strlocalSID, strlocalUserId, strlocalPassword)
End Select
CommandType = Data.CommandType.Text
InitializeCommand(strQuery)
Try
objDataAdapter = New OleDb.OleDbDataAdapter(objCommand)
Dim objDataTable As New DataTable
objDataAdapter.Fill(objDataTable)
Return (objDataTable)
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Function
''' <summary>
''' Execute any text query (Select, Insert, Update, Delete) and return the number of rows affected by this query
''' </summary>
''' <param name="strQuery">Text query that you want to execute it</param>
''' <returns>The number of rows affected by entered query</returns>
''' <remarks></remarks>
Public Function ExecuteQuery(ByVal strQuery As String) As Integer
Select Case DataBaseType
Case DataBaseTypes.MsAccess
Call OpenMsAccessConnection(strlocalPathAndFileName)
Case DataBaseTypes.MsSqlServer
Call OpenMsSqlServerConnection(strlocalServerName, strlocalDataBaseName, strlocalUserId, strlocalPassword)
Case DataBaseTypes.Oracle
Call OpenOracleConnection(strlocalSID, strlocalUserId, strlocalPassword)
End Select
CommandType = Data.CommandType.Text
InitializeCommand(strQuery)
Try
ExecuteQuery = objCommand.ExecuteNonQuery
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Function
''' <summary>
''' You should call this sub befor getting DataTable or execute StoredProecedure for adding parameters of StoredProcedure
''' </summary>
''' <param name="SPName">StoredProcedure Name that you want Execute it</param>
''' <remarks></remarks>
Public Sub InitializeStoredProcedure(ByVal SPName As String)
Select Case DataBaseType
Case DataBaseTypes.MsAccess
Call OpenMsAccessConnection(strlocalPathAndFileName)
Case DataBaseTypes.MsSqlServer
Call OpenMsSqlServerConnection(strlocalServerName, strlocalDataBaseName, strlocalUserId, strlocalPassword)
Case DataBaseTypes.Oracle
Call OpenOracleConnection(strlocalSID, strlocalUserId, strlocalPassword)
End Select
CommandType = Data.CommandType.StoredProcedure
InitializeCommand(SPName)
End Sub
''' <summary>
''' Add a Parameter of StoredProcedure. Note: you should call the InitializeStoredProcedure sub befor using this sub
''' </summary>
''' <param name="Name">Parameter name</param>
''' <param name="Type">Parameter type</param>
''' <param name="Size">Parameter size</param>
''' <param name="Value">Parameter value</param>
''' <remarks></remarks>
Public Sub AddParameter(ByVal Name As String, ByVal Type As OleDb.OleDbType, ByVal Size As Integer, ByVal Value As Object)
Try
objCommand.Parameters.Add(Name, Type, Size).Value = Value
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Sub
''' <summary>
''' Get DataTable Object by Execute the StoredProcedure that you enter it in the InitializeStoredProcedure sub. Note: you should call the InitializeStoredProcedure sub befor using this function
''' </summary>
''' <returns>DataTable object filled by entered StoredProcedure</returns>
''' <remarks></remarks>
Public Function GetDataTableByStoredProcedure() As DataTable
Try
objDataAdapter = New OleDb.OleDbDataAdapter(objCommand)
Dim objDataTable As New DataTable
objDataAdapter.Fill(objDataTable)
Return (objDataTable)
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Function
''' <summary>
''' Execute the StoredProcedure that you enter it in the InitializeStoredProcedure sub. Note: you should call the InitializeStoredProcedure sub befor using this function
''' </summary>
''' <returns>The number of rows affected by entered StoredProcedure</returns>
''' <remarks></remarks>
Public Function ExecuteStoredProcedure() As Integer
Try
ExecuteStoredProcedure = objCommand.ExecuteNonQuery
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Function
'Private part
Private Enum DataBaseTypes As Byte
MsAccess = 1
MsSqlServer = 2
Oracle = 3
End Enum
Private CommandType As CommandType = CommandType.Text
Private Disposed As Boolean
Private DataBaseType As DataBaseTypes
Private strlocalPathAndFileName As String
Private strlocalServerName As String
Private strlocalDataBaseName As String
Private strlocalSID As String
Private strlocalUserId As String
Private strlocalPassword As String
Private objConnection As OleDb.OleDbConnection
Private objCommand As OleDb.OleDbCommand
Private objDataAdapter As OleDb.OleDbDataAdapter
Private Sub OpenMsAccessConnection(ByVal strPathAndFileName As String)
objConnection = New OleDb.OleDbConnection
objConnection.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;" & _
"Data Source=" & strPathAndFileName & ";"
Try
objConnection.Open()
Catch ex As OleDb.OleDbException 'Connection-Level Exceptions
Throw New Exception(ex.Message, ex.InnerException)
Catch ex As InvalidOperationException 'Trying to open a connection that is already opened
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
Private Sub OpenMsSqlServerConnection(ByVal strServerName As String, ByVal strDataBaseName As String, ByVal strUserId As String, ByVal strPassword As String)
objConnection = New OleDb.OleDbConnection
objConnection.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=" & strServerName & ";" & _
"DataBase=" & strDataBaseName & ";" & _
"User ID=" & strUserId & ";" & _
"Password=" & strPassword & ";"
Try
objConnection.Open()
Catch ex As OleDb.OleDbException 'Connection-Level Exceptions
Throw New Exception(ex.Message, ex.InnerException)
Catch ex As InvalidOperationException 'Trying to open a connection that is already opened
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
Private Sub OpenOracleConnection(ByVal strSID As String, ByVal strUserId As String, ByVal strPassword As String)
objConnection = New OleDb.OleDbConnection
objConnection.ConnectionString = "Provider=MSDAORA;" & _
"Data Source=" & strSID & ";" & _
"User ID=" & strUserId & ";" & _
"Password=" & strPassword & ";"
Try
objConnection.Open()
Catch ex As OleDb.OleDbException 'Connection-Level Exceptions
Throw New Exception(ex.Message, ex.InnerException)
Catch ex As InvalidOperationException 'Trying to open a connection that is already opened
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
Private Sub InitializeCommand(ByVal strQueryOrSPName As String)
Try
objCommand = New OleDb.OleDbCommand(strQueryOrSPName, objConnection)
objCommand.CommandType = CommandType
Catch ex As Exception
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Sub
End Class
Joined: 27 Nov 2004 Posts: 1232 Location: www.parsx.com
Posted: Fri Sep 22, 2006 11:42 pm Post subject:
برای اضافه کردن این کلاس به پروژه فقط کافیه از منوی Projectگزینه New Item رو بزنی بعد Class رو انتخاب کنی
کد داخل کلاس رو پاک کن جاش این کدها رو کپی پیست کن
در نهایت هم یک شی از نوع این کلاس می سازی
اگه دیتابیست Access هستش فقط کافیه آدرس فایل mdb.* رو بدونی و به جای آدرس زیر بزاری
Dim cls As New clsADO("C:\a.mdb")
بعد برای گرفتن گزارش می تونی از تابع استفاده کنی
Try
Dim da As DataTable
da = cls.GetDataTableByQuery("sel_ect * fr.om a")
MsgBox(da.Rows(0)(0).ToString)
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
برای اضافه کردن و یا بروز رسانی یا حتی حذف دیتا از دیتا بیس هم از تابع زیر استفاده کن
Try
cls.ExecuteQuery("ins_ert into a (id,name) values(1,'Parsx')")
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Posted: Sat Sep 23, 2006 1:35 pm Post subject: با سلام دوباره
با تشکر .
کلاس جالبيه اما اگه ممکنه يه نگاه به مشکل من بکن فکر کنم براحتي قابل حل باشه.
من يه فرم دارم که روي آن يک جعبه متن به نام textbox1 و يک ديتاگريد به نام datagrid1 قرار داره.(به اضافه يک دکمه)
بعد از مراحل مربوط به data connection و اتصال پايگاه داده به برنامه يه کنترل OledbDataAdapter به نام OledbDataAdapter1 به فرم اضافه کردم همان طور که ميدوني در يک مرحله از ويزارد بايد sql نويسي کنيم (در اينجا من ميخوام تنها سطر هايي در datagrid نمايش داده شود که مثلا فيلد phone از جدول table1 برابر textbox1.text باشه) اين کد ها رو نوشتم ولي جواب نميده:
من برنامه ام رو با قرار دادن يک کنترل oledbdataAdapter و طي کردن مراحل ويزارد آن درست کردم و کدي که گفتي در اون مرحله وارد کردم اما جواب نميده (البته دبل کوتيشن هاي ابتدا و انتها را برداشتم)
با اضافه کردن کنترل oledbdataAdapter تو يه برنامه کوچولو و طي کردن مراحل ويزارد مخصوصا مرحله sql نويسيش متوجه حرف من ميشي.
البته مي دونم اين يه روش آماتوريه ولي من نه روش انجام اون با کد نويسي رو بلدم و نه يه منبع که با زبان ساده اين مباحث رو توضيح داده باشه پيدا کردم.
اگه کمکم کني خيلي ممنون ميشم.
اگه از دات نت 2003 استفاده مي كني توي بخش كدها initial مقدار دهي datagrid بخش دستور sql رو مي توني پيدا كني و اونجا مي توني به راحتي textbox1.text رو مشخص كني ...
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Profile 
Log in to check your private messages | 
FAQ 
Search 
Memberlist 
Usergroups 
Log in
Register
