C # Stored procedure (draft)

1,try
{

Sqlleaveword Leav = new Sqlleaveword ();

Console.WriteLine ("Test public list<leavewordinfo> getleawordbyshop (int index, int number, int shopid)");

Leav. Getleawordbyshop (2,23,3);
Console.WriteLine (leav.pagenumbers);
Console.WriteLine ("Test Sqlmajor.delete suceess!");
}
catch (Appexception ex)
{
Console.WriteLine (ex. ToString ());
}

Public list<leavewordinfo> getleawordbyshop (int index, int number, int shopid)
{
Calling the search stored procedure
//@PageSize int = 20,--Paging size
//@CurrentPage int,--page
//@Columns varchar (1000) = ' * ',--fields to be obtained
//@TableName varchar (100)--table to be queried
//@Condition varchar (1000) = ',--query criteria without adding where keyword
@OrderColumn varchar (100) = ',--ordered field name (that is, order by column Asc/desc)
//@OrderType bit = 0,--sort type (0 is ascending, 1 is descending)
//@PkColumn varchar (50) = '--PRIMARY Key name
//@TotalCount int-------Total Output

Sqlparameter[] Paramsearch ={
New SqlParameter ("@PageSize", SqlDbType.Int),
New SqlParameter ("@CurrentPage", SqlDbType.Int),
New SqlParameter ("@Columns", sqldbtype.varchar,1000),
New SqlParameter ("@TableName", sqldbtype.varchar,100),
New SqlParameter ("@Condition", sqldbtype.varchar,1000),
New SqlParameter ("@OrderColumn", sqldbtype.varchar,100),
New SqlParameter ("@OrderType", Sqldbtype.bit),
New SqlParameter ("@PkColumn", sqldbtype.varchar,50),
            New SqlParameter ("@TotalCount", SqlDbType.Int)

           
        };
        paramsearch[0]. Value = number;
        paramsearch[1]. Value = index;
        paramsearch[2]. Value = "*";
        paramsearch[3]. Value = "Leaveword";
        paramsearch[4]. Value = "leaveword.shopid=" +SHOPID;
        paramsearch[5]. Value = "Leavedate";
        paramsearch[6]. Value = true;
        paramsearch[7]. Value = "Leavewordid";
        Paramsearch[8]. Direction = ParameterDirection.Output;
   

SqlDataReader SDR = Sqlhelper.executereader (sqlhelper.connectionstring, CommandType.StoredProcedure, "Search", Paramsearch);

List<leavewordinfo> leawords=new list<leavewordinfo> ();

PageNumbers = Sqlhelper.totalcount/number;
Assigning Values to Leawords
while (SDR. Read ())
{
Leavewordinfo Leaword = new Leavewordinfo ();

Leaword.leavewordid = Convert.ToInt32 (sdr["Leavewordid"). ToString ());
Leaword.account = sdr["Account"]. ToString ();
Leaword.leavedate = Convert.todatetime (sdr["Leavedate"). ToString ());
Leaword.shopid = Convert.ToInt32 (sdr["Shopid"). ToString ());
Leaword.message = sdr["message"]. ToString ();

Leawords.add (Leaword);

}
Sdr. Close ();

return leawords;
}

<summary>
Returns the message that ID number is Leavewordid
</summary>
<param name= "Leavewordid" ></param>
<returns>id Leavewordid Message </returns>
Public leavewordinfo GetInfo (int leavewordid)
{
Calling the search stored procedure
//@PageSize int = 20,--Paging size
@CurrentPage int,--the first few pages
//@Columns varchar (1000) = ' * ',--fields to be obtained
//@TableName varchar (100)--table to be queried
//@Condition varchar (1000) = ',--query criteria without adding where keyword
//@OrderColumn varchar (100) = ',--the sorted field name (that is, order by column Asc/desc)
//@OrderType bit = 0,--sort type (0 is ascending, 1 is descending)
//@PkColumn varchar (50) = '--primary Key name

Sqlparameter[] Paramsearch ={
New SqlParameter ("@PageSize", SqlDbType.Int),
New SqlParameter ("@CurrentPage", SqlDbType.Int),
New SqlParameter ("@Columns", sqldbtype.varchar,1000),
New SqlParameter ("@TableName", sqldbtype.varchar,100),
New SqlParameter ("@Condition", sqldbtype.varchar,1000),
New SqlParameter ("@OrderColumn", sqldbtype.varchar,100),
New SqlParameter ("@OrderType", Sqldbtype.bit),
New SqlParameter ("@PkColumn", sqldbtype.varchar,50)

};
Paramsearch[0]. Value = 20;
PARAMSEARCH[1]. Value = 0;
PARAMSEARCH[2]. Value = "*";
PARAMSEARCH[3]. Value = "Leaveword";
PARAMSEARCH[4]. Value = "leavewordid= '" + Leavewordid + "'";
PARAMSEARCH[5]. Value = "Leavedate";
PARAMSEARCH[6]. Value = true;
PARAMSEARCH[7]. Value = "Leavewordid";

SqlDataReader SDR = Sqlhelper.executereader (sqlhelper.connectionstring, CommandType.StoredProcedure, "Search", Paramsearch);
Leavewordinfo Leaword=null;

Assigning values to shops
while (SDR. Read ())
{
Leaword.leavewordid = Convert.ToInt32 (sdr["Leavewordid"). ToString ());
Leaword.account = sdr["Account"]. ToString ();
Leaword.leavedate = Convert.todatetime (sdr["Leavedate"). ToString ());
Leaword.shopid =convert.toint32 (sdr["Shopid"). ToString ());
Leaword.message = sdr["message"]. ToString ();

}

Sdr. Close ();

return Leaword;
}

--Create a stored procedure
ALTER PROCEDURE Search
@PageSize int = 20, paging size
@CurrentPage int,--the first few pages
@Columns varchar (1000) = ' * ',--fields to be obtained
@TableName varchar (100)--Tables to query
@Condition varchar (1000) = ',--query criteria without adding where keyword
@OrderColumn varchar (100) = ',--ordered field name (that is, order by column Asc/desc)
@OrderType bit = 0,--sort type (0 is ascending, 1 is descending)
@PkColumn varchar (50) = ',--primary Key name
@TotalCount int Output
As
Begin--The stored procedure begins
DECLARE @strTemp varchar (300)
DECLARE @strSql varchar (5000)--The last statement executed by the stored procedure
DECLARE @strOrderType varchar (1000)--Sort Type statement (ordered by column ASC or ORDER BY column DESC)
DECLARE @strRowCount varchar (1000)

BEGIN
IF @OrderType = 1--Descending
BEGIN
SET @strOrderType = ' ORDER BY ' + @OrderColumn + ' DESC '
SET @strTemp = ' < (SELECT min '
End
ELSE--Ascending
BEGIN
SET @strOrderType = ' ORDER BY ' + @OrderColumn + ' ASC '
SET @strTemp = ' > SELECT max '
End
-----------returns the total number of records
If @Condition!= '
Begin
Set @strRowCount = ' Select @TotalCount =count (' + @PkColumn + ') from ' + @TableName + ' where ' + @Condition
End
Else
Begin
Set @strRowCount = ' Select @TotalCount =count (' + @PkColumn + ') from ' + @TableName
End

EXEC sp_executesql @strRowCount, N ' @TotalCount int out ', @TotalCount out

IF @CurrentPage = 1--First page
BEGIN
IF @Condition!= '
SET @strSql = ' SELECT top ' +str (@PageSize) + ' + @Columns + ' from ' + @TableName +
' WHERE ' + @Condition + @strOrderType
ELSE
SET @strSql = ' SELECT top ' +str (@PageSize) + ' + @Columns + ' from ' + @TableName + @strOrderType
End

else IF (@CurrentPage >1)--Other pages
BEGIN
IF @Condition!= '
SET @strSql = ' SELECT top ' +str (@PageSize) + ' + @Columns + ' from ' + @TableName +
' WHERE ' + @Condition + ' and ' + @PkColumn + @strTemp + ' (' + @PkColumn + ') ' + ' from (SELECT Top ' +str ((@CurrentPage-1) * @PageSize) +
' + @PkColumn + ' from ' + @TableName + ' WHERE ' + @Condition + @strOrderType + ') as Tabtemp ' + @strOrderType
ELSE
SET @strSql = ' SELECT top ' +str (@PageSize) + ' + @Columns + ' from ' + @TableName +
' WHERE ' + @PkColumn + @strTemp + ' (' + @PkColumn + ') ' + ' from (SELECT Top ' +str ((@CurrentPage-1) * @PageSize) + ' + @PkColumn +
' From ' + @TableName + @strOrderType + ') as Tabtemp ' + @strOrderType

End
---non-paged query
ElSE
BEGIN
IF @Condition!= '
SET @strSql = ' SELECT ' + @Columns + ' from ' + @TableName + ' WHERE ' + @Condition + ' + ' + @strOrderType
ELSE
SET @strSql = ' SELECT ' + @Columns + ' from ' + @TableName + ' + @strOrderType
End

End
EXEC (@strSql)


End