生活靠自己
求别人不如求自己!
posts - 6,comments - 2,trackbacks - 0
CREATE PROCEDURE sp_page
@tblName 
varchar(255), 
@strGetFields 
varchar(1000) ,
@fldName 
varchar(255), 
@PageSize 
int , 
@PageIndex 
int , 
@doCount 
bit,
@OrderType 
bit , 
@strWhere 
varchar(1500
AS

declare @strSQL varchar(5000
declare @strTmp varchar(110
declare @strOrder varchar(400

if @doCount != 0
begin
if @strWhere !=""
set @strSQL = "select count(*as Total from [" + @tblName + "] where "+@strWhere
else
set @strSQL = "select count(*as Total from [" + @tblName + "]"
end 
else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end

if @PageIndex =0
begin
if @strWhere !=""

set @strSQL = "select top " + str(@PageSize) +"  "+@strGetFields+ " from [" + @tblName + "]  where " + @strWhere + " " + @strOrder
else

set @strSQL = "select top " + str(@PageSize) +"  "+@strGetFields+ "  from ["+ @tblName + "] "+ @strOrder

end
else
begin
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] where [" + @fldName + "]+ @strTmp + "(["+ @fldName +
 "
]from (select top " + str(@PageIndex*@PageSize) + " ["+ @fldName +
 "
] from [" + @tblName + "]+ @strOrder + ") as tblTmp)"+ @strOrder

if @strWhere !=""
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "
] where [" + @fldName + "]+ @strTmp + "(["
+ @fldName + "
]from (select top " + str(@PageIndex*@PageSize) + " ["
+ @fldName + "
] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end 

end 

exec (@strSQL)
GO


c#实现代码为:
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Threading; 
using System.Text;

namespace WindowsApplication4
{
    
/// <summary>
    
/// Form1 的摘要说明。
    
/// </summary>

    public class Form1 : System.Windows.Forms.Form
    
{
        
private System.Windows.Forms.Button button1;
        
private System.Windows.Forms.Label label1;
        
int threadindex=0;
        
        
        
        
/// <summary>
        
/// 必需的设计器变量。
        
/// </summary>

        private System.ComponentModel.Container components = null;

        
public Form1()
        
{
            
//
            
// Windows 窗体设计器支持所必需的
            
//
            InitializeComponent();

            
//
            
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
            
//
        }


        
/// <summary>
        
/// 清理所有正在使用的资源。
        
/// </summary>

        protected override void Dispose( bool disposing )
        
{
            
if( disposing )
            
{
                
if (components != null
                
{
                    components.Dispose();
                }

            }

            
base.Dispose( disposing );
        }


        
#region Windows 窗体设计器生成的代码
        
/// <summary>
        
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
        
/// 此方法的内容。
        
/// </summary>

        private void InitializeComponent()
        
{
            
this.button1 = new System.Windows.Forms.Button();
            
this.label1 = new System.Windows.Forms.Label();
            
this.SuspendLayout();
            
// 
            
// button1
            
// 
            this.button1.Location = new System.Drawing.Point(208368);
            
this.button1.Name = "button1";
            
this.button1.Size = new System.Drawing.Size(14440);
            
this.button1.TabIndex = 0;
            
this.button1.Text = "button1";
            
this.button1.Click += new System.EventHandler(this.button1_Click);
            
// 
            
// label1
            
// 
            this.label1.Location = new System.Drawing.Point(408);
            
this.label1.Name = "label1";
            
this.label1.Size = new System.Drawing.Size(496336);
            
this.label1.TabIndex = 1;
            
this.label1.Text = "label1";
            
// 
            
// Form1
            
// 
            this.AutoScaleBaseSize = new System.Drawing.Size(614);
            
this.ClientSize = new System.Drawing.Size(584422);
            
this.Controls.Add(this.label1);
            
this.Controls.Add(this.button1);
            
this.Name = "Form1";
            
this.Text = "Form1";
            
this.ResumeLayout(false);

        }

        
#endregion


        
/// <summary>
        
/// 应用程序的主入口点。
        
/// </summary>

        [STAThread]
        
static void Main() 
        
{
            Application.Run(
new Form1());
        }


        
private void button1_Click(object sender, System.EventArgs e)
        
{
            threadindex
=0;
            
for(int i=0;i<30;i++)
            
{
//                Thread myThread = new Thread(new ThreadStart(dosome));
//                myThread.IsBackground=true;
//                myThread.Name="dothread"+i.ToString();
//                myThread.Start();
                ThreadPool.QueueUserWorkItem(new WaitCallback(dosome));
            }

            
//dosome();
        }


        
public void dosome(Object obj)
        
{
            
            
            SqlConnection _connection
=new SqlConnection("server=192.168.0.8;uid=sa;pwd=;database=dbtest;persist security info=False;packet size=4096");

            


            SqlParameter[] sqlParameters1 
= new SqlParameter[]{
                                                                  
new SqlParameter("@tblName",SqlDbType.VarChar,255),
                                                                  
new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
                                                                  
new SqlParameter("@fldName",SqlDbType.VarChar,255),
                                                                  
new SqlParameter("@PageSize",SqlDbType.Int,4),
                                                                  
new SqlParameter("@PageIndex",SqlDbType.Int,4),
                                                                  
new SqlParameter("@doCount",SqlDbType.Bit),
                                                                  
new SqlParameter("@OrderType",SqlDbType.Bit),
                                                                  
new SqlParameter("@strWhere",SqlDbType.VarChar,1500)
            }
;
            sqlParameters1[
0].Value = "songs";
            sqlParameters1[
1].Value = "*";
            sqlParameters1[
2].Value = "ssongname";
            sqlParameters1[
3].Value = 10;
            sqlParameters1[
4].Value =threadindex;
            sqlParameters1[
5].Value =false;
            sqlParameters1[
6].Value = true;
            sqlParameters1[
7].Value =" SongNameLength=4 ";
            
            SqlDataReader dataread
=null;
            
try
            
{
                dataread
=RunProcedure("sp_page",sqlParameters1,_connection);
            }

            
catch (SqlException e)
            
{
                MessageBox.Show(e.ToString());
            }



            StringBuilder sb
=new StringBuilder();
            
if(dataread.Read())
            
{                
                
while(dataread.Read())
                
{                    
                    sb.Append(dataread[
"ssongname"]);
                    sb.Append(
"\n");
                }

            }

            dataread.Close();
            label1.Text
=String.Empty;
            label1.Text
=sb.ToString();        
                
            threadindex
++;
        
            
        }
        

        
private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters,SqlConnection _connection)
        
{
            SqlCommand sqlCommand 
= new SqlCommand(storedProcName, _connection);
            sqlCommand.CommandType 
= CommandType.StoredProcedure;
            IDataParameter[] iDataParameters 
= parameters;
            
for (int i = 0; i < (int)iDataParameters.Length; i++)
            
{
                SqlParameter sqlParameter 
= (SqlParameter)iDataParameters[i];
                sqlCommand.Parameters.Add(sqlParameter);
            }

            
return sqlCommand;
        }


        

        
protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters,SqlConnection _connection)
        
{
            _connection.Open();
            SqlCommand sqlCommand 
= BuildQueryCommand(storedProcName, parameters,_connection);
            sqlCommand.CommandType 
= CommandType.StoredProcedure;
            
return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }

        

        

    }

    
}

posted on 2005-06-30 16:30 MyRiverMan 阅读(230) 评论(0)  编辑 收藏 引用
只有注册用户登录后才能发表评论。