Lexa » 02.11.2007 (Пт) 18:54
Lexa » 04.11.2007 (Вс) 18:54
Thomas » 05.11.2007 (Пн) 12:47
Поясню более подробно.При создании класса просто выгребать эти данные из БД, в чем проблема то?
class Artikel
{
#region Declaration.
private AccessDB m_ArtikelDb;
private string m_Code;
private string m_Categorie;
private string m_Leveranciersreferentie;
private string m_CodeLeverancier;
private string m_Naam;
private string m_Beschrijving;
private float m_Aankoopprijs;
private float m_Verkoopprijs;
private int m_BtwCode;
private float m_BtwPercentage;
private int m_Voorraad;
private int m_MinVoorraad;
private DateTime m_DatumEersteAankoop;
private int m_AantalMaandenGarantie;
#endregion
#region Constructors.
/// <summary>
/// Default constructor.
/// </summary>
public Artikel()
{
m_ArtikelDb = new AccessDB();
}
/// <summary>
///
/// </summary>
/// <param name="ArtCode">Het is value van ArtikelCode.</param>
/// <remarks></remarks>
public Artikel(string ArtCode)
{
m_Code = ArtCode;
m_ArtikelDb = new AccessDB();
FillData();
}
#endregion
#region Methodes.
private void FillData()
{
string sql = "SELECT dbo.[tblArtikels_(verkoop)].*, dbo.tblBTWcodes.[BTW-percentage] FROM dbo.[tblArtikels_(verkoop)] INNER JOIN";
sql += " dbo.tblBTWcodes ON dbo.[tblArtikels_(verkoop)].[BTW-code artikel] = dbo.tblBTWcodes.[BTW-code]";
sql += " WHERE [Code artikel] = '" + m_Code + "';";
m_ArtikelDb.FillDataSet("artikel", sql);
m_Categorie = m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Categorie"].ToString();
m_Leveranciersreferentie = m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Leveranciersreferentie artikel"].ToString();
m_CodeLeverancier = m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Code leverancier"].ToString();
m_Naam = m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Naam artikel"].ToString();
m_Beschrijving = m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Beschrijving artikel"].ToString();
m_Aankoopprijs = float.Parse(m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Aankoopprijs artikel"].ToString());
m_Verkoopprijs = float.Parse(m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Verkoopprijs artikel"].ToString());
m_BtwCode = Convert.ToInt32(m_ArtikelDb.DS.Tables["artikel"].Rows[0]["BTW-code artikel"]);
m_BtwPercentage = float.Parse(m_ArtikelDb.DS.Tables["artikel"].Rows[0]["BTW-percentage"].ToString());
m_Voorraad = Convert.ToInt32(m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Voorraad"]);
m_MinVoorraad = Convert.ToInt32(m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Minimale voorraad"]);
m_DatumEersteAankoop = Convert.ToDateTime(m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Datum eerste aankoop"]);
if(m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Aantal maanden garantie"]!=DBNull.Value)
m_AantalMaandenGarantie = Convert.ToInt32(m_ArtikelDb.DS.Tables["artikel"].Rows[0]["Aantal maanden garantie"]);
}
public void Update()
{
string sql = "UPDATE [tblArtikels_(verkoop)] SET ";
sql += "Categorie = '" + m_Categorie + "', ";
sql += "[Leveranciersreferentie artikel] = '" + m_Leveranciersreferentie + "', ";
sql += "[Code leverancier] = '" + m_CodeLeverancier + "', ";
sql += "[Naam artikel] = '" + m_Naam + "', ";
sql += "[Beschrijving artikel] = '" + m_Beschrijving + "', ";
sql += "[Aankoopprijs artikel] = " + m_Aankoopprijs + ", ";
sql += "[Verkoopprijs artikel] = " + m_Verkoopprijs + ", ";
sql += "[BTW-code artikel] = " + m_BtwCode + ", ";
sql += "[Voorraad] = " + m_Voorraad + ", ";
sql += "[Minimale voorraad] = " + m_MinVoorraad + ", ";
sql += "[Datum eerste aankoop]= '" + m_DatumEersteAankoop.ToString("MM/d/yyyy") + "'";
if (m_AantalMaandenGarantie != 0)
sql += ", [Aantal maanden garantie] = " + m_AantalMaandenGarantie;
else
sql += ", [Aantal maanden garantie]= NULL";
sql += " WHERE [Code artikel] = '" + m_Code + "';";
System.Windows.Forms.MessageBox.Show(sql);
m_ArtikelDb.DoNonQuery(sql);
}
public void Insert()
{
string sql = "INSERT INTO [tblArtikels_(verkoop)] ([Code artikel], Categorie, [Leveranciersreferentie artikel],";
sql += " [Code leverancier], [Naam artikel], [Beschrijving artikel], [Aankoopprijs artikel],";
sql += " [Verkoopprijs artikel], [BTW-code artikel], [Voorraad], [Minimale voorraad],";
sql += " [Datum eerste aankoop]";
if (m_AantalMaandenGarantie != 0)
sql += ", [aantal maanden garantie])";
else
sql += ")";
sql += " VALUES ('" + m_Code + "', '" + m_Categorie + "', '" + m_Leveranciersreferentie + "', '" + m_CodeLeverancier + "', '";
sql += m_Naam + "', '" + m_Beschrijving + "', " + m_Aankoopprijs + ", " + m_Verkoopprijs + ", ";
sql += m_BtwCode + ", " + m_Voorraad + ", " + m_MinVoorraad + ", '" + m_DatumEersteAankoop.ToString("MM/d/yyyy") + "'";
if (m_AantalMaandenGarantie != 0)
sql += "' " + m_AantalMaandenGarantie + ")";
else
sql += ")";
m_ArtikelDb.DoNonQuery(sql);
}
#endregion
#region Properties.
public string Code
{
get { return m_Code; }
set { m_Code = value; }
}
public string Categorie
{
get { return m_Categorie; }
set { m_Categorie = value; }
}
public string Leveranciersreferentie
{
get { return m_Leveranciersreferentie; }
set { m_Leveranciersreferentie = value; }
}
public string CodeLeverancier
{
get { return m_CodeLeverancier; }
set { m_CodeLeverancier = value; }
}
public string Naam
{
get { return m_Naam; }
set { m_Naam = value; }
}
public string Beschrijving
{
get { return m_Beschrijving; }
set { m_Beschrijving = value; }
}
public float Aankoopprijs
{
get { return m_Aankoopprijs; }
set { m_Aankoopprijs = value; }
}
public float Verkooppeijs
{
get { return m_Verkoopprijs; }
set { m_Verkoopprijs = value; }
}
public int BtwCode
{
get { return m_BtwCode; }
set { m_BtwCode = value; }
}
public float BtwPercentage
{
get { return m_BtwPercentage; }
set { m_BtwPercentage = value; }
}
public int Voorraad
{
get { return m_Voorraad; }
set { m_Voorraad = value; }
}
public int Minvoorraad
{
get { return m_MinVoorraad; }
set { m_MinVoorraad = value; }
}
public DateTime DatumEersteAankoop
{
get { return m_DatumEersteAankoop; }
set { m_DatumEersteAankoop = value; }
}
public int AantalMaandenGarantie
{
get { return m_AantalMaandenGarantie; }
set { m_AantalMaandenGarantie = value; }
}
#endregion
}
в какую сторону копать.
hCORe » 05.11.2007 (Пн) 15:32
Thomas » 05.11.2007 (Пн) 15:52
Ramzes » 05.11.2007 (Пн) 16:17
Thomas писал(а):Lexa
Привет.
В принципе, ответ на вопрос уже прозвучал.При создании класса просто выгребать эти данные из БД, в чем проблема то?
Lexa » 07.11.2007 (Ср) 17:21
Public MustInherit Class clsATCDataObject
Protected Enum SQLCommandType
Undefined = 0
SelectCm = 1
UpdateCm = 2
InsertCm = 3
DeleteCm = 4
End Enum
Protected Enum ObjectState
Unchanged = 0
Modified = 1
NewObject = 2
End Enum
' Начальные значения для генерации строк запросов
Private lvar_select As String = "SELECT [TableName] FROM [Table] WHERE [IDColumn] = [ID];"
Private lvar_update As String = "UPDATE [Table] SET [TableName] WHERE [IDColumn] = [ID];"
Private lvar_delete As String = "DELETE * FROM [Table] WHERE [IDColumn] = [ID];"
Private lvar_insert As String = "INSERT INTO [Table]([IDColumn],[TableName]) VALUES ([ID],[Values]);"
Private Shared lvar_connection As OleDb.OleDbConnection
Private lvar_objConnection As New OleDb.OleDbConnection(OLEDBConnStr)
Private lcm As New OleDb.OleDbCommand
Private lvar_idcolumn As String
Private lvar_tablename As String
Private lvar_state As ObjectState
' Для генерации запросов
Protected lvar_id As Long
Protected WithEvents lvar_prop As New clsProperties
Public WithEvents lvErrorList As classErrorTable
Public lvar_errMsg As String = "" ' Сообщение об ошибке
' Разрешения
Public AllowSave As Boolean = True
Public AllowDelete As Boolean = True
Public AllowInsert As Boolean = True
#Region "События"
Public Event ObjectUpdated(ByVal ID As Long)
Public Event LoadComplete(ByVal ID As Long)
Public Event ObjectRemoved(ByVal ID As Long)
Public Event ObjectInserted(ByVal ID As Long)
Public Event PropertySet(ByVal strProperty As String)
Public Event PropertyGet(ByVal strProperty As String)
Protected Event ErrorMessageChanged As EventHandler
#End Region
' Создание класса
Public Sub New(Optional ByVal ID As Long = 0)
lvar_id = ID
Me.lvErrorList = New classErrorTable
Me.SetProperties()
Me.LoadObject()
End Sub
#Region "Свойства SQL Комманды"
' SQL Комманды
Protected Property SelectCommand() As String
Get
Return Me.lvar_select
End Get
Set(ByVal Value As String)
Me.lvar_select = Value
End Set
End Property
Protected Property UpdateCommand() As String
Get
Return Me.lvar_update
End Get
Set(ByVal Value As String)
Me.lvar_update = Value
End Set
End Property
Protected Property DeleteCommand() As String
Get
Return Me.lvar_delete
End Get
Set(ByVal Value As String)
Me.lvar_delete = Value
End Set
End Property
Protected Property InsertCommand() As String
Get
Return Me.lvar_insert
End Get
Set(ByVal Value As String)
Me.lvar_insert = Value
End Set
End Property
#End Region
' Описание ошибки
Public ReadOnly Property ErrorMessage() As String
Get
Return lvar_errMsg
End Get
End Property
Public Property ErrorList() As classErrorTable
Get
Return Me.lvErrorList
End Get
Set(ByVal value As classErrorTable)
Me.lvErrorList = value
RaiseEvent ErrorListChanged(Me, New EventArgs)
End Set
End Property
Public Event ErrorListChanged As EventHandler
Protected Property State() As ObjectState
Get
Return lvar_state
End Get
Set(ByVal Value As ObjectState)
lvar_state = Value
End Set
End Property
Public Overridable ReadOnly Property ID() As Long
Get
Return CLng(lvar_id)
End Get
End Property
Protected Property Properties() As clsProperties
Get
Return lvar_prop
End Get
Set(ByVal Value As clsProperties)
lvar_prop = Value
End Set
End Property
Protected Property IDColumn() As String
Get
Return lvar_idcolumn
End Get
Set(ByVal Value As String)
lvar_idcolumn = Value
End Set
End Property
Protected Property TableName() As String
Get
Return lvar_tablename
End Get
Set(ByVal Value As String)
lvar_tablename = Value
End Set
End Property
Friend Function LoadObject() As Short
Dim ret As Object = Nothing ' результат из функции
If Me.TableName = "" OrElse Me.IDColumn = "" Then
MessageBox.Show("Системная ошибка" & vbCrLf & "Ввседите TableName и IDColumn")
Me.Finalize()
End If
Dim SQL As String = lvar_select
SQL = SQL.Replace("[Table]", Me.TableName)
SQL = SQL.Replace("[IDColumn]", Me.IDColumn)
SQL = SQL.Replace("[ID]", Me.ID)
' Создать строку свойств Select
Dim PRPS() As String = {""}
Dim ca As Short = 0
Dim tp As Short = Me.lvar_prop.Values.Count - 1
ReDim PRPS(0)
Dim ic As Short = 0
For Each pr As clsProperty In Me.lvar_prop.Values
PRPS(ca) &= pr.TableName & " & '#' & "
If ic = 30 Or ic = 60 Then
ca = ca + 1
ReDim Preserve PRPS(ca)
End If
ic += 1
Next
For i As Short = 0 To PRPS.Length - 1
PRPS(i) = PRPS(i).TrimEnd("&", "'", "#", " ")
Next
Dim GSQL(PRPS.GetUpperBound(0)) As String
For i As Short = 0 To PRPS.GetUpperBound(0)
GSQL(i) = SQL.Replace("[TableName]", PRPS(i))
Next
' Открываем соединение
Me.lvar_objConnection.Open()
lcm = New OleDb.OleDbCommand(SQL.Replace("[TableName]", "ID"), Me.lvar_objConnection)
ret = lcm.ExecuteScalar
If ret Is Nothing Then
' Новая запись
For Each prop As clsProperty In Me.lvar_prop.Values
Me.Properties(prop.TableName).Value = Nothing
Next
Else
ret = ""
For Each str As String In GSQL
lcm.CommandText = str
ret &= lcm.ExecuteScalar & "#"
Next
ret = CType(ret, String).Substring(0, CType(ret, String).Length - 1)
Dim objArr() As String
Dim i As Integer = 0
objArr = CStr(ret).Split("#")
For Each prop As clsProperty In Me.Properties.Values
prop.Value = objArr(i)
i += 1
Next
End If
Me.lvar_objConnection.Close()
' Если новый объект
If lvar_id = 0 Then
lvar_id = modDReader.GetAutoIncrementSeed(Me.lvar_tablename, Me.lvar_idcolumn)
Me.State = ObjectState.NewObject
Else
Me.State = ObjectState.Unchanged
End If
RaiseEvent LoadComplete(lvar_id)
End Function
Friend Function LoadObject2() As Short
Dim ret As Object ' результат из функции
If Me.TableName = "" OrElse Me.IDColumn = "" Then
MessageBox.Show("Системная ошибка" & vbCrLf & "Ввседите TableName и IDColumn")
Me.Finalize()
End If
Dim SQL As String = lvar_select
SQL = SQL.Replace("[Table]", Me.TableName)
SQL = SQL.Replace("[IDColumn]", Me.IDColumn)
SQL = SQL.Replace("[ID]", Me.ID)
' Создать строку свойств Select
Dim PRPS As String = ""
For Each prop As clsProperty In Me.lvar_prop.Values
PRPS &= prop.TableName & " & '#' & "
Next
PRPS = PRPS.TrimEnd("&", "'", "#", " ")
SQL = SQL.Replace("[TableName]", PRPS)
' Открываем соединение
Me.lvar_objConnection.Open()
Try
lcm = New OleDb.OleDbCommand(SQL, Me.lvar_objConnection)
ret = lcm.ExecuteScalar
If ret Is Nothing Then
' Новая запись
For Each prop As clsProperty In Me.lvar_prop.Values
Me.Properties(prop.TableName).Value = Nothing
Next
Else
Dim objArr() As String
Dim i As Integer = 0
objArr = CStr(ret).Split("#")
For Each prop As clsProperty In Me.Properties.Values
prop.Value = objArr(i)
i += 1
Next
End If
Me.lvar_objConnection.Close()
' Если новый объект
If lvar_id = 0 Then
lvar_id = modDReader.GetAutoIncrementSeed(Me.lvar_tablename, Me.lvar_idcolumn)
Me.State = ObjectState.NewObject
Else
Me.State = ObjectState.Unchanged
End If
RaiseEvent LoadComplete(lvar_id)
Catch ex As Exception
MessageBox.Show(ex.Message & vbCrLf & lcm.CommandText)
End Try
End Function
' Сохранение объекта
Public Function SaveObject(Optional ByVal ShowSaveMessage As Boolean = True) As Short
'If Me.AllowSave = False Then Throw New Exception("Запрещено сохранение/добавление объекта")
lcm.Connection = pCN
lcm.CommandText = "SELECT [IDColumn] FROM [Table] WHERE [IDColumn] = [ID];"
lcm.CommandText = lcm.CommandText.Replace("[IDColumn]", Me.lvar_idcolumn)
lcm.CommandText = lcm.CommandText.Replace("[Table]", Me.lvar_tablename)
lcm.CommandText = lcm.CommandText.Replace("[ID]", Me.lvar_id)
If lcm.ExecuteScalar Is Nothing Then Me.InsertObject() Else Me.UpdateObject()
Me.AfterPrimariSave()
Me.State = ObjectState.Unchanged
If ShowSaveMessage Then MessageBox.Show("Объект сохранён")
End Function
Protected Overridable Function AfterPrimariSave() As Short
Return 0
End Function
Protected Overridable Function DeleteSubObjects() As Short
' удаление подчинённых объектов
' выполняется перед удалением объекта
' для удаления всех связанных таблиц
' 0 - удаление подчинённых объектов прошло успешно
' -1 - не удалось удалить выбранные объекты
Return 0
End Function
Protected Overridable Function CheckObjectSaveProperties() As Short
' Функция проверки свойств объекта при InsertObject и UpdateObject
' 0 - всё впорядке или другое значение
' -1 - не впорядке
Return 0
End Function
Protected Overridable Function CheckObjectDeleteProperties() As Short
' Функция проверки свойств объекта при DeleteObject
' 0 - всё впорядке или другое значение
' -1 - не впорядке
End Function
Protected MustOverride Function SetProperties() As Short
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
#Region "Процедуры обновления"
' Обновление объекта
Private Function UpdateObject() As Short
If Me.CheckObjectSaveProperties() = -1 Then Return -1
lcm.Connection = pCN
Dim SQL As String = Me.lvar_update
Dim SQLUpdCol As String = ""
For Each prop As clsProperty In lvar_prop.Values
SQLUpdCol &= prop.TableName & "=" & prop.GetDBString & ","
Next
SQLUpdCol = SQLUpdCol.TrimEnd(",")
SQL = SQL.Replace("[Table]", Me.TableName)
SQL = SQL.Replace("[IDColumn]", Me.IDColumn)
SQL = SQL.Replace("[ID]", Me.ID)
SQL = SQL.Replace("[TableName]", SQLUpdCol)
lcm.CommandText = SQL
Try
lcm.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -1
End Try
RaiseEvent ObjectUpdated(Me.ID)
End Function
' Добавление объекта
Private Function InsertObject() As Short
If Me.CheckObjectSaveProperties() = -1 Then Return -1
lcm.Connection = pCN
Dim SQL As String = Me.lvar_insert
Dim SQLInsCol As String = ""
Dim SQLInsVal As String = ""
For Each prop As clsProperty In lvar_prop.Values
SQLInsCol &= prop.TableName & ","
SQLInsVal &= prop.GetDBString & ","
Next
SQLInsCol = SQLInsCol.TrimEnd(",")
SQLInsVal = SQLInsVal.TrimEnd(",")
SQL = SQL.Replace("[Table]", Me.TableName)
SQL = SQL.Replace("[IDColumn]", Me.IDColumn)
SQL = SQL.Replace("[ID]", Me.ID)
SQL = SQL.Replace("[TableName]", SQLInsCol)
SQL = SQL.Replace("[Values]", SQLInsVal)
lcm.CommandText = SQL
Try
lcm.ExecuteNonQuery()
RaiseEvent ObjectInserted(Me.ID)
Return 0
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -1
End Try
End Function
' Удаление объекта
Public Function DeleteObject(Optional ByVal boolShowConfirmDialog As Boolean = True) As Short
If Me.CheckObjectDeleteProperties = -1 Then Return -1
If Me.DeleteSubObjects <> 0 Then Exit Function
If boolShowConfirmDialog Then
If MessageBox.Show("Удалить выбранную запись", "", MessageBoxButtons.YesNo, _
MessageBoxIcon.Question, MessageBoxDefaultButton.Button2, MessageBoxOptions.DefaultDesktopOnly) _
= DialogResult.No Then
Return -1
End If
End If
lcm.Connection = pCN
Dim SQL As String = Me.lvar_delete
SQL = SQL.Replace("[IDColumn]", Me.IDColumn)
SQL = SQL.Replace("[ID]", Me.ID)
SQL = SQL.Replace("[Table]", Me.TableName)
lcm.CommandText = SQL
Try
lcm.ExecuteNonQuery()
RaiseEvent ObjectRemoved(Me.ID)
Me.Finalize()
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -1
End Try
Return 0
End Function
#End Region
Protected Sub SetErrorMessage(ByVal ErrMsg As String, Optional ByVal ASave As Boolean = False)
Me.lvar_errMsg = ErrMsg
Me.AllowSave = ASave
Try
RaiseEvent ErrorMessageChanged(Me, New EventArgs)
Catch ex As Exception
MsgBox(ex)
End Try
End Sub
End Class
' Внутреннее задание - единичная запись
Public Class classTask
Inherits clsATCDataObject
' Установка свойств
Protected Overrides Function SetProperties() As Short
Me.IDColumn = "ID"
Me.TableName = "Tasks"
With Me.Properties
.Add("task", GetType(String))
.Add("priority", GetType(Byte))
.Add("get_date", GetType(Date))
.Add("to_date", GetType(Date))
.Add("result", GetType(String))
.Add("completed", GetType(CheckState))
.Add("to_user", GetType(Long))
.Add("by_user", GetType(Long))
End With
End Function
Public Sub New(Optional ByVal ID As Long = 0)
MyBase.New(ID)
' Предустановки
If ID = 0 Then
Me.Priority = atcEnTaskPriority.Middle
Me.Text = "[введите задание]"
Me.GetDate = Now.ToShortDateString
Me.Completed = CheckState.Unchecked
Me.ByUserID = AppProperties.CurrentUser.ID
End If
End Sub
Public Property Priority() As atcEnTaskPriority
Get
Return Me.Properties("priority").Value
End Get
Set(ByVal Value As atcEnTaskPriority)
Me.Properties("priority").Value = Value
RaiseEvent PriorityChanged(Me, New EventArgs)
RaiseEvent isValidChanged(Me, New EventArgs)
End Set
End Property
Public Event PriorityChanged As eventhandler
...
Сейчас этот форум просматривают: Google-бот и гости: 110