Query data by page in the gridview. when the data is empty, it is determined in the query statement.

Today, I was depressed for a long time. When I queried the data list, the database did not judge whether it was null. As a result, the query statement failed. I thought of a method that would be helpful to you too!

User table name: sysuser; alias: u

Employee table name: basicemployee: Give it an alias: E

Query User table data by user name, user type, and freeze or not:

Select U. *, E. Name as realname from

(Select * From sysuser where isdelete = 0

And (@ username is null or username = @ username)

And (@ usertype is null or usertype = @ usertype)

And (@ isuse is null or isuse = @ isuse) u left join basicemployee e on u. employeeid = E. employeeid

In this way, if @ username is null, @ username = NULL can be used in the background. If the text box has a value, that is, @ username data is not empty, @ username = value of this text box;

The following is an example:

1 In the Dal layer:
/// <Summary>
/// Use String concatenation to read paging data
/// </Summary>
/// <Param name = "pagesize"> page size </param>
/// <Param name = "pageindex"> current page number </param>
/// <Param name = "rowcount"> Number of data entries </param>
/// <Param name = "orderfield"> sorting field </param>
/// <Param name = "PRMS"> condition dictionary table </param>
/// <Returns> </returns>
Public dataset getpaging (INT pagesize, int pageindex, ref int rowcount, string orderfield, Dictionary <string, Object> PRMS)
{
DB dbutil = dB. getinstance ();
Stringbuilder sb = new stringbuilder ();
SB. append ("select U. *, E. name as realname from (select * From sysuser where isdelete = 0 and (@ username is null or username = @ username) and (@ usertype is null or usertype = @ usertype) and (@ isuse is null or isuse = @ isuse ))");
SB. append ("u left join basicemployee e on u. employeeid = E. employeeid ");
Database DB = This. createdatabase ();

// Obtain the number of data entries
Dbcommand success COUNT = dbutil. getdbcommanddatacount (dB, SB. tostring ());
DB. addinparameter (parameter count, "@ username", dbtype. String, PRMS ["@ username"]);
DB. addinparameter (parameter count, "@ usertype", dbtype. int32, PRMS ["@ usertype"]);
DB. addinparameter (parameter count, "@ isuse", dbtype. int32, PRMS ["@ isuse"]);
Rowcount = convert. toint32 (db. executescalar (distinct count ));
// Retrieve paging data
Dbcommand upload paging = dbutil. getdbcommandpaging (dB, SB. tostring (), pagesize, pageindex, rowcount, orderfield );
DB. addinparameter (partition paging, "@ username", dbtype. String, PRMS ["@ username"]);
DB. addinparameter (partition paging, "@ usertype", dbtype. int32, PRMS ["@ usertype"]);
DB. addinparameter (partition paging, "@ isuse", dbtype. int32, PRMS ["@ isuse"]);
// Execute the command to obtain data
Return dB. executedataset (grouping paging );
}

Bll:

/// <Summary>
/// Use String concatenation to read paging data
/// </Summary>
/// <Param name = "pagesize"> page size </param>
/// <Param name = "pageindex"> current page number </param>
/// <Param name = "rowcount"> Number of data entries </param>
/// <Param name = "orderfield"> sorting field </param>
/// <Param name = "PRMS"> condition dictionary table </param>
Public dataset getpaging (INT pagesize, int pageindex, ref int rowcount, string orderfield, Dictionary <string, Object> PRMS)
{
Return Dal. getpaging (pagesize, pageindex, ref rowcount, orderfield, PRMS );
}
Foreground call dictionary table data:

Public dictionary <string, Object> getpaginguser ()
{
Dic. Clear ();
String realname = this.txt realname. Text. Trim ();
String usertype = This. ddlusertype. selectedvalue;
String isuser = This. rblisuse. selectedvalue;
If (this.txt username. Text. Trim (). Length = 0)
{
Dic. Add ("@ username", null );
}
Else
{
Dic. Add ("@ username", this.txt username. Text. Trim ());
}
If (usertype = "-1 ")
{
Dic. Add ("@ usertype", null );
}
Else
{
Dic. Add ("@ usertype", usertype );
}
If (isuser = "-1 ")
{
Dic. Add ("@ isuse", null );
}
Else
{
Dic. Add ("@ isuse", isuser );
}
Return DIC;
}