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, our example or the read of a configuration table in the community program, we are to get data from the database according to certain conditions.
I. Database design:
ScoreSetting integral set the table Key: ScoreSettingID Field name Type Length Default Description ScoreSettingID int 4 0 ScoreSettingID (automatic number) FunctionID int 4 0 function ID OperationID int 4 0 Operation ID RoleTypeID int 4 0 identity ID BBSTypeID int 4 0 Group categories ID Score int 4 0 points BB INT 4 0 coins Buytype INT 4 0 Purchase Type FunctionState INT 4 0 0: No, 1: Yes, 2: Buy
Store procedure:
/ *****************************************
Function: Read function records according to certain conditions
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 stored procedure is dynamically created according to the class of the data layer, and then performs the SQL statement with the stored procedure with the system, and the advantage of executing the SQL statement with the system store is automatic escape character. . And the advantage of being dynamically created query statement is very large, this will save a lot of conditions, especially for those tables more, there are too many situations in one arrangement, while using stored procedures Dynamically create a SQL statement is basically consistent, here will give the reference initial value. If it is not equal to the initial value, the data layer class is passed, which is added to the corresponding conditional character. condition.
Second, the 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: /// integral set of collection classes: Kelvin Yu /// Creation Date: 2004-01-14 /// modified by: /// Modified: ///
Public Class ScoresettingCollection
{
#Region private member
Private arraylist members;
#ndregion
#REGION constructor
///
/// Constructor ///
Public scoresettingcollection ()
{
}
#ndregion
#Region Public property
///
/// Operation object number ///
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);
#ndregion
#Region Public Method
///
// / Point setting according to different conditions ///
/// function ID
/// Operation ID
/// Role ID
/// Table Type DI
/// points
/// coins
/// Purchase type
/// functional 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 description:
The code classification code 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: /// class of integration: Kelvin Yu /// Creation Date: 2004-01-14 /// modified by: /// modification 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;
///
/// Coin ////
Private int bb = -20000000;
///
/// Purchase Type ///
PRIVATE INT Buytype = -1;
///
/// function status ////
PRIVATE INT functionState = -1;
///
/ / / Do not update the score ///
#ndregion
#REGION public properties
///
/// Point Setup 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;
}
///
/// Coin ////
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- Have this function 2- Need to buy ///
Public int functionState
{
Get {Return FunctionState;} set {functionState = value;
}
#ndregion
#REGION constructor
Public scoresetting ()
{
}
///
/// Overload constructor ////
/// Point Setting ID
Public Scoresetting (INT ID)
{
THIS.ID = ID;
}
#ndregion
#Region Public Method
///
/ / / According to ID, you get the integral setting information ///
///
Success TRUE, failed False
Public bool getinfobyid ()
{
// code slightly
}
///
/// Add points set ///
///
Success TRUE, failed False
Public bool add ()
{
// code slightly
}
///
/// Programming points set ///
///
Successfully returns true, failed to return false
Public bool edit ()
{
// code slightly
}
///
/// Remove the integral setting ///
///
Successfully returns true, failed to return false
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.
Third, the representation layer (UI layer)
This layer is mainly read data. Basically, it will be filled in different server controls according to requirements.
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, and the data can be determined according to SSCCount. 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.
Author: rexsp reprint to: http: //www.cnblogs.com/rexsp
