ASP.NET (C #) SQL Server three-layer architecture data access scheme
introduction:
Participate in the development of a large community program, and now, the experience of relevant development development is now summarized, and everyone will discuss it. This section mainly wants to explore a data reading program with you: The main benefit is the bottleneck problem that can solve the bottleneck of SQL Server throughput instead of DataSet directly from the database. Generally, small number of programs do not have problems, but when data is 100,000 million, the limits of database throughput will be apparent. The solution here is actually to divide the massive data information into a strip, and the bottleneck limit is solved with the cost of the library, in fact, the burden of the database server allows the web server. Gossip less, enter the topic,
I
Our examples or reads in a configuration table in the community program,
I
We are
root
Data is obtained from the database according to certain conditions.
I. Database design:
Scoresetting integral setting table
Key: scoresettingid
Field Name
Type
Length
DEFAULT
Description
ScoresettingID
int
4
0
ScoresettingID (Auto Number)
FunctionID
int
4
0
Function ID
OperationID
int
4
0
Operation ID
RoletypeID
int
4
0
Identity ID
BBStypeID
int
4
0
Forum Category ID
Score
int
4
0
Score
BB
int
4
0
Currency
Buytype
Int
4
0
Purchase type
FunctionState
int
4
0
0: No, 1: Yes, 2: Buy
Store procedure:
/ *****************************************
Features:
root
Read function record accordingly
Author: Rexsp
Creating Date: 2004-01-13
editor:
Modify date:
***************************************** /
Alter Procedure GetscoreSetting
(
@ScoreSettingID INT = -1, --- Setting ID
@FunctionID int = -1, --- Function ID
@OperationID int = -1, --- Operation ID
@RoletypeID int = -1, --- role type
@BBSTYPEID INT = -1, --- Type
@Score int = -1, --- Points
@BB INT = -1, --- Coin Arrangement
@BuyType Int = -1, --- Purchase Type 0: Not a Purchase Type 1: One - time purchase 2: Repeated Buy
@FunctionState int = -1 --- Functional status
)
AS
Set nocount on
Declare @STRSQL NVARCHAR (1000)
Set @strsql = 'select * from [scoresetting] where @ckscoresettingId = @ckscoresettingId' --- add keywords begin ---
IF @scoresettingId <> -1
Begin
Set @strsql = @strsql 'and scoresettingId = @ckscoresettingID'
End
IF @FunctionID <> -1
Begin
Set @strsql = @strsql 'and functionID = @ckfunctionID'
End
IF @operationid <> - 1
Begin
Set @STRSQL = @strsql 'and OperationID = @ckoperationID'
End
IF @roletypeId <> - 1
Begin
Set @strsql = @STRSQL 'and RoletypeId = @ckroletypeID'
End
IF @BBStypeId <> - 1
Begin
Set @strsql = @strsql 'and bbstypeid = @ckbbstypeID'
End
IF @score <> - 1
Begin
Set @strsql = @strsql 'and score = @CKSCORE'
End
IF @BB <> - 1
Begin
Set @strsql = @strsql 'and bb = @ckbb'
End
IF @BuyType <> - 1
Begin
Set @strsql = @strsql 'and buype = @ckbuytype'
End
IF @functionState <> - 1
Begin
Set @strsql = @strsql 'and functionState = @ckfunctionState'
End
--- Add where key word ---
--- Run SQL Begin ---
EXECUTE sp_executesql @strsql,
N '@ckscoresettingid Int,
@ackfunctionID INT,
@ckoperationid Int,
@CKROLETYPEID INT,
@ckbbstypeid int,
@CKSCORE INT,
@CKBB INT,
@CKBUYTYPE INT,
@ackfunctionState Int ',
@ ckscoresettingId = @ scoresettingID,
@ CKFunctionID = @ functionID,
@ckoperationid = @ OperationID, @ ckroletypeId = @roletypeID,
@ckbbstypeid = @bbstypeid,
@CKSCORE = @score,
@CKBB = @BB,
@CKBUYTYPE = @BuyType,
@ckfunctionState = @functionState
--- Run SQL END -----
One point description:
This memory process will
root
According to the class of the data layer, the query statement is transmitted, and the SQL statement is executed with the stored procedure with the system, and the advantage of executing the SQL statement with the system store is an automatic escape character. And the benefits of dynamically create a query statement are very big, this will save
I
We wrote a lot of conditions, especially for those tables, and there are too many cases that are arranged in combination, and the number of judgment numbers and fields that use stored procedures dynamically create SQL statements are basically consistent. This will give the enactment initial value, if not equal to the initial value, the data layer class is passed, so that the query condition of the corresponding conditional character will be added.
First, data layer class: using system;
Using system.collections;
Using system.data;
Using system.data.sqlclient;
Using town.data;
Using town.log;
Namespace town.com
{
///
/// Function: Point Settings Collection
/// Author: Rexsp /// Creation Date: 2004-01-14
/// editor:
/// Modified Date:
///
Public Class ScoresettingCollection
{
#Region private member
Private arraylist members;
#ndregion
#REGION constructor
///
/// Constructor
///
Public scoresettingcollection ()
{
}
#ndregion
#Region Public property
///
/// Operation object
///
Public Int Count
{
get
{
IF (MEMBERS! = NULL)
Return members.count;
Else
Return 0;
}
}
#ndregion
#REGION index
///
/// Index
///
Public scoresetting this [int index]
{
get
{
IF (MEMBERS! = NULL)
Return (ScoreSetting) (MEMBERS [INDEX]);
Else
Return NULL;
}
}
#ndregion
#Region Private method
///
/// Add Operation to OperationCollection Collection
///
Private void add (scoresetting scoresetting) {
IF (MEMBERS == NULL)
MEMBERS = New ArrayList ();
MEMBERS.ADD (Scoresetting);
}
#Region public method
///
///
root
Points set according to different conditions
///
/// Function ID
/// Operation ID
/// Role ID
/// Type DI
/// Points
/// coin
/// Purchase type
/// Function status
///
Public Bool GetSpecialInfo (int FunctionID, int OperationID, int RoletypeId, int bbstypeid, int score, int bb, int burstype, int functionstate)
{
SqlDataAdapter DataAdapter = NULL;
Database data = New Database ("Town");
#Region Creating a parameter
ArrayList Sqlparameterlist ();
IF (FunctionID! = - 1)
SqlParameterList.Add (Data.makeinParam ("@ functionid", sqldbtype.int, 4, functionID);
IF (OperationID! = - 1)
SqlparameterList.Add (Data.makeinParam ("@ OperationID", SqldbType.int, 4, OperationID);
IF (RoletypeID! = - 1)
SqlParameterList.Add (Data.makeinParam ("@ RoletypeID", SqldbType.int, 4, RoletypeId);
IF (bbstypeid! = - 1)
SqlparameterList.add (Data.MakeinParam ("@ bbstypeid", sqldbtype.int, 4, bbstypeid);
IF (score! = - 1)
SqlParameterList.Add (Data.makeinParam ("@ score", sqldbtype.int, 4, score);
IF (bb! = - 1)
SqlparameterList.add (Data.makeinParam ("@ bb", sqldbtype.int, 4, bb)); if (BuyType! = - 1)
SqlParameterList.Add (Data.makeinParam ("@ Buytype", SqldbType.int, 4, BuyType);
IF (functionState! = - 1)
SqlParameterList.Add (Data.makeinParam ("@ functionState", SqldbType.int, 4, functionstate);
Sqlparameter [] PRAMS = New Sqlparameter [sqlparameterlist.count];
For (int i = 0; i { PRAMS [I] = (SQLParameter) SQLParameterList [i]; } #ndregion Try { Data.Runproc ("Getscoresetting", Prams, Out DataAdapter; DataSet DataSet = New DataSet (); DataAdapter.Fill (Dataset, "Table"); DataAdapter.dispose (); IF (Dataset.Tables ["Table"]. rows.count == 0) { DataSet.clear (); Dataset.dispose (); Return False; } Else { Foreach (DataRow Dr in Dataset.tables ["Table"]. ROWS) { Scoresetting ss = new scoresetting (); SS.ID = int32.parse (DR ["scoresettingID"]. TOSTRING (). Trim ()); SS.FunctionID = Int32.Parse (DR ["FunctionID"]. TOSTRING (). Trim ()); SS.OPERATIONID = INT32.PARSE (DR ["OperationID"]. TOSTRING (). TRIM ()); Ss.RoletypeId = int32.parse (DR ["RoletypeID"]. TOSTRING (). Trim ()); SS.BBSTYPEID = INT32.PARSE (DR ["bbstypeid"]. TOSTRING (). TRIM ()); Ss.score = int32.parse (DR ["score"]. TOSTRING (). Trim ()); SS.BB = Int32.Parse (DR ["BB"]. TOSTRING (). Trim ()); Ss.buytype = int32.Parse (DR ["BuyType"]. TOSTRING (). Trim ()); SS.FunctionState = int32.parse (DR ["functionState"]. TOSTRING (). Trim ()); Add (ss); } DataSet.clear (); Dataset.dispose (); Return True; } } Catch (Exception EX) { Error.log ("Town", EX.TOSTRING ()); DataAdapter.dispose (); Return False; } Finally { Data.Close (); Data.Dispose (); // Release Database } } #ndregion } } One point explanation: The code class classification is divided into six pieces: private member, constructor, public property, index, private method, and public method. The index is established here, which is the necessary elements of the collection class. Then there is a private method that the role is to add objects to a collection, and the public method is a query method. In the above example, the parameter is passed, and it can also be transmitted with the properties. Here, it has been contemplated. If the transfer value is -1, it is considered that this variable does not work, and it is basically the same as the thoughts in the stored procedure. The scoresetting object in this example is another independent class, as follows: USING SYSTEM; Using system.data; Using system.data.sqlclient; Using town.data; Using town.log; Using system.collections; Namespace town.com { /// /// Function: Classification /// Author: Rexsp /// Creation Date: 2004-01-14 /// editor: /// Modified Date: /// Public class scoresetting { #Region private member /// /// score setting ID /// Private Int ID = -1; /// /// function ID /// Private int functionID = -1; /// /// Operation ID /// PRIVATE INT OPERATIONID = -1; /// /// Role Type ID /// Private int RoletypeId = -1; /// // 版 版 type ID /// Private int bbstypeId = -1; /// /// points /// Private int score = -2000000000; /// /// coins /// Private int bb = -20000000; /// /// Purchase type /// PRIVATE INT Buytype = -1; /// /// functional status /// PRIVATE INT functionState = -1; /// / / / Do not update the score /// #ndregion #REGION public properties /// /// Points set DI /// Public Int ID { Get {return id;} Set {id = value;} } /// /// function ID /// public int functionID { Get {returnif functionid; Set {functionId = value; } /// /// Operation ID /// Public int OperationID { Get {return OperationId; Set {OperationId = Value;} } /// /// Role type /// Public int RoletypeID { Get {return roletypeId;} Set {RoletypeId = Value;} } /// /// Type /// Public int bbstypeID { Get {return bbstypeid;} Set {bbstypeid = value; } /// /// points /// Public int score { Get {returnis score;} Set {score = value; } /// /// coins /// Public int bb { Get {return bb;} SET {BB = Value;} } /// /// Purchase type 0- is not a purchase type 1 - ahead purchase 2 - repeated purchase /// Public int burstype { Get {return buyiType;} Set {Buytype = Value;} } /// /// Purchase status: 0- Nobody features 1- There is this function 2- need to buy /// Public int functionState { Get {returnif functionState;} SET {functionState = value;} } #endregion #Region constructor Public scoresetting () { } /// /// overload constructor /// /// Points set ID Public Scoresetting (INT ID) { THIS.ID = ID; } #ndregion #Region Public Method /// /// root Id to get points set information /// /// Public bool getinfobyid () { // code slightly } /// /// Add points set /// /// Public bool add () { // code slightly } /// /// Programming points set /// /// Public bool edit () { // code slightly } /// /// Remove points settings /// /// Public Bool Remove () { // code slightly } } One point explanation: This class contains four parts, private members, constructor, public properties, public methods, and private members and database tables, and attributes correspond to private members. The constructor will be the key button of the initial data table, of course, can also overload the initial private member of the constructor. This class contains four ways, that is, four operations: read, write, delete, change. First, the layer indicating that the layer (UI layer) is mainly read data. Basically root The demand is filled in different server controls. Scoresettingcollection SSC = New ScoresettingCollection (); FunctionCollection funcc = new functioncollection (); Funcc.getinfobyfunctionName ("Edition Management"); INT functionID = funcc [0] .id; Ssc.getspecialinfo (FunctionID, 0, RoletypeId, BbstypeID, -1, -1, -1, -1); Int ssccount = ssc.count; The above code is the data reading process, root According to SSCCount, it can be determined that the data is obtained. The data recirculation of this strip is set up in the premise of the data, and then the data of this strip is set up and then bind the data. I am dizzy, I thought I could put it out, I don't want to have restrictions, really trouble, an article post six times. Hey! MSN: YUBO@x263.net has problems can be discussed together
