Introduction
Of course, ADO.NET is much simpler than ADO and OLEDB. I know is to open the Connection I can read the database through DataSet and DataReader. Of course, you can also recover data through your needs.
I realize that the DataSet is powerful and saves me, but I am still willing to choose my own way to deal with the database program. I like to use a class to call the method to update the data. Therefore, when I use a record in the database, I will use a record in the database. It is as simple as the instance of the item, setting its properties, and the update method of calling.
But I have to write old code, business object class, database update code and database read code. I didn't use the stored procedure to update the database, so I want to write a SQL statement without a business item. This work is particularly boring when I modify the database or business object.
My program
My solution is to start with a simple class. It is the less code to update the item. All I have to add the name, value, and classes of the field, and then generate the SQL statement. When I end this, I am intoxicated in the joy in a week ...
But when I started using SQLServer instead of Access, my emotions changed. I can't use a simple SQL statement to update my object, I have to use the store process. Unfortunately start ... I have to create a SQL statement To update the object. The boring work begins again ...
I noticed that I can write a simple class to generate parameters, just as the SQL statement is the same. Although this program can write less code, I still have to check whenever my plan is changed. My update code.
Then I have the idea of how to create a sustainable class in the database. Use properties to describe the table of the database so that attributes can be mapped to the fields in the table. So I can only modify the business object class.
For you easier to read, I divide this article into three parts. The first part shows the use of properties to describe a business class. The second part shows how I collect information, and finally I will show a complete solution.
I - Attributes
Attributes is used to describe accessories, classes, properties, methods, and fields. It is already in some places in the .NET architecture. But you can create your own properties.
I use Attributes to describe how a class is stored in the database. In a class, I will point out those attributes should be continuous, as the stored procedure updates the database. In order to describe the columns in the table, I am in the class The attribute uses attributes. Column can be a simple data field, unique key or foreign key.
How to create your own Attribute?
Quite easy, you can create a class inherited in system.attribute. As for the naming habits, you can add an Attribute suffix. When you create an Attribute, you have to know how the attribute is used. Should you use it? Properties, Or multiple definitions are allowed?
It's time to see some code. These Attributes are used to describe a business object class:
Using system;
Using system.data;
Namespace DAL
{
[AttributeUSAGE (AttributeTargets.property)]]
Public Class Basefieldattribute: Attribute: Attribute
{
String columnname;
Public Basefieldattribute (String ColumnName)
{
THIS.COLUMNNAME = ColumnName;
}
Public String ColumnName
{
Get {return columnname;}
Set {colornname = value;
}
}
[AttributeUSAGE (AttributeTargets.property)]]
Public Class DataFieldattribute: Basefieldattribute
{
DBTYPE DBTYPE = DBTYPE.STRING;
INT size = 0;
Public DataFieldattribute (String ColumnName): Base (ColumnName)
{
}
Public DBTYPE TYPE
{
Get {return dbtype;} set {dbtype = value;
}
Public int size
{
Get {returnide;}
SET {size = value;}
}
}
[AttributeUSAGE (AttributeTargets.property)]]
Public Class Keyfieldattribute: BasefieldAttribute
{
Public keyfieldattribute (string columnname): Base (ColumnName)
{
}
}
[AttributeUSAGE (AttributeTargets.property)]]
Public Class ForeignKeyfieldAttribute: BasefieldAttribute
{
Public ForeignKeyfieldAttribute (String ColumnName): Base (ColumnName)
{
}
}
[AttributeUSAGE (AttributeTargets.class | attributeTargets.struct)]
Public Class DataTableAttribute: Attribute
{
String Tablename;
String updatestoredProcedure = "";
Public DataTableAttribute (String Tablename)
{
THIS.TABLENAME = TABLENAME;
}
Public String TableName
{
Get {return Tablename;
Set {TABLENAME = Value;}
}
Public String UpdateStoredProcedure
{
Get {return UpdatestoredProcedure;
Set {updatestoredProcedure = Value;}
}
}
}
As you can see, there is an Attributeusage Attribute. It is to indicate how Attribute is used.
How do I use these Attribute to describe a class?
Suppose you have an application to save users and contact information. In OO design, we start with a person, then contact information is a Person plus address and information. Users are contact information plus purchase information. Of course users and others It is dependent. If I use these other articles, it is of course quite stupid. ^ _ ^
The code of this class is as follows:
Using system;
Using system.data;
Using dal;
Namespace TestApp
{
Public Class Person
{
String name = ""
INT AGE = 0;
INT ID = 0;
[Keyfield ("ID")]]]
Public Int ID
{
Get {return id;}
Set {id = value;}
}
[DataField ("Name", size = 50)]]]
Public String Name
{
Get {return name;}
Set {name = value;}
}
[DataField ("age")]]]]]
Public Int Age
{
Get {return age;}
Set {age = value;
}
Public override string toString ()
{
Return string.format ("{0}, {1} Years Old", Name, AGE;
}
[DataTable ("Contact", UpdateStoredProcedure = "sp_updatecontact")]]]]
Public Class Contact: Person
{
String phone = "";
String email = "";
String address = "";
String address2 = "";
String city = "";
String postalcode = "";
String stat = "";
String country = ""
[DataField ("Phone", size = 20)]
Public String Phone
{
Get {return;}
SET {Phone = Value;}
}
[DataField ("email", size = 80)]]
Public String email
{
Get {return email;}
SET {email = value;}
}
[DataField ("Address", size = 80)]
Public String Address
{
Get {return address;}
Set {address = value;
}
[DataField ("Address2", size = 80)]]
Public string address2
{
Get {return address2;}
Set {address2 = value;}
}
[DataField ("City", size = 50)]]
Public String City
{
Get {return city;}
Set {city = value;}
}
[DataField ("Postalcode", size = 20)]]
Public String Postalcode, PUBLIC STRING
{
Get {returno postalcode;}
Set {Postalcode = Value;
}
[DataField ("State", size = 4)]]
Public String State
{
Get {return.}
Set {state = value;}
}
[DataField ("Country", size = 50)]
Public String Country
{
Get {return country;}
Set {country = value;}
}
Public override string toString ()
{
Return string.format ("
}
}
Public Enum CustomerReranceShip {Family, Friend, Other};
[DATATABLE ("CustomerDependent", UpdateStoredProcedure = "sp_updatecustomerdepend")] Public Class CustomerDependent: Person
{
INT Customerid = 0;
CustomerReranceShip Relationship = CustomerReranceShip.FAMILY;
Protected customOMerDepend ()
{
}
Public CustomerDependent (int Customerid)
{
This.customerid = CustomerId;
}
[ForeignKeyfieldtattribute ("Customerid")]]]
Public Int Customerid
{
Get {returnj
SET {Customerid = Value;
}
[DataFieldTRibute ("Relationship")]]]
Public CustomerReranceShip Relationship
{
Get {return rellationship;}
Set {rateship = value;}
}
}
Public Enum Customerstatus {active, inactive};
[DataTable ("Customer", UpdateStoredProcedure = "sp_updatecustomer"]]
Public Class Basecustomer: Contact
{
CustomersTatus Status = CustomersTatus.Active;
Decimal totalpurchased = 0m;
INT NUMBEROFPURCHASES = 0;
Datetime dateregistered = datetime.now;
[DataField ("status")]]]]
Public Customerstatus Status
{
Get {return status;}
Set {status = value;}
}
[DataField ("TotalpurchaseD")]]]]]]]
Public Decimal Totalpurchased
{
Get {returnotalpurchased;
Set {Totalpurchased = value;
}
[DataField ("Numberofpurchases")]]]
Public int numberofpurchases
{
Get {return numberofpurchases;
SET {Numberofpurchases = value;
}
[DataField ("DateRegistered")]]]]]
Public DateTime DateRegistered
{
Get {return dateregistered;}
Set {dateregistered = value;
}
Public override string toString ()
{
Return string.format ("
"# {4} Purchases Spending a Total of $ {5}", ID,
Name,
COUNTRY,
DateRegistered,
Numberofpurchases,
Totalpurchased;
}
}
Public Class Customer: Basecustomer
{
ArrayList Dependents = NULL;
Public ArrayList Dependents
{
get
{
IF (dependents == null)
{
Dal Dal = new dal ();
Dependents = Dal.getCustomerDependents;
}
Return Dependents;
}
}
Public Customerdependnt Newdepend ()
{
Return New CustomerDependent (ID);
}
Public Decimal Purchasemedia
{
Get {return totalpurchased / numberofpurchases;
}
}
}
The Person class is a base class for all of our classes. It is the only class that does not use Attribute, thinking that he doesn't have to continuous. Only Contacts, CustomerDependents and Customer will exist in the database. But the Person class has defined some columns Some columns must have a column. For attribute IDs, it is an int key field, which is an automatic field in this example. The only difference is CustomerDependent :: CustomerID is an foreign key.
Create the following script for this class in the SQL analyzer:
IF exists (Select * from sysobjects where id = Object_id (n '[dbo]. [contact]')
And ObjectProperty (id, n'susertable ') = 1)
Drop Table [DBO]. [Contact]
Go
If EXISTS (Select * from sysobjects where id = Object_id (n '[dbo]. [Customer]')
And ObjectProperty (id, n'susertable ') = 1)
DROP TABLE [DBO]. [CUSTOMER]
Go
If EXISTS (Select * from sysobjects where id = Object_id (n '[dbo]. [CustomerDependent]')
And ObjectProperty (id, n'susertable ') = 1)
Drop Table [DBO]. [CustomerDependents]
Go
Create Table [DBO]. [Contact] (
[ID] [INT] Identity (1, 1) Not NULL,
[Name] [varchar] (50) Not null,
[age] [int] Not null,
[address] [varchar] (80) Not null,
[postalcode] [varchar] (20) Not null,
[Phone] [varchar] (20) Not null,
[email] [varchar] (80) Not null,
[address2] [varchar] (80) Not null,
[City] [varchar] (50) Not null,
[State] [varchar] (4) Not null,
[country] [varchar] (50) Not null) on [primary]
Go
CREATE TABLE [DBO]. [Customer] (
[ID] [INT] Identity (1, 1) Not NULL,
[Name] [varchar] (50) Not null,
[age] [int] Not null,
[address] [varchar] (80) Not null,
[postalcode] [varchar] (20) Not null,
[phone] [varchar] (50) Not null,
[email] [varchar] (80) Not null,
[address2] [varchar] (80) Not null,
[City] [varchar] (50) Not null,
[State] [varchar] (4) Not null,
[country] [varchar] (50) Not null,
[totalpurchased] [Money] Not null,
[Numberofpurchases] [int] Not null,
[dateRegistered] NOT NULL,
[status] NOT NULL
) On [primary]
Go
Create Table [DBO]. [CustomerDepend] (
[ID] [INT] Identity (1, 1) Not NULL,
[Name] [varchar] (50) Not null,
[Customerid] [INT] NOT NULL,
[RELATIONSHIP] [INT] NOT NULL,
[ag] NOT NULL
) On [primary]
Go
Next step
Next article I will show how to use reflections to help us collect the needs of information to update the database. I also create a simple tool to create a SQL script by creating a simple tool to create a simple tool in the assembly.
