一直在项目中很少用到存储过程，虽说是能够高效执行sql，各种好处，但用得不熟练，总是写很长的sql也不会用存储过程替代，但这次不管sql写多长都不好实现，所以在同事的指导下还是用存储过程实现的，第一次体验存储过程的好处，所以记录下来，以便今后使用
使用存储过程原因：
实验管理员列表数据过多，需要支持分页，但数据源是多处拼凑出来的，不好直接实现分页，
所以使用存储过程，把查询出来的数据源新增到临时表，再进行分页就简便多了
原始sql:
1 DECLARE @ID NVARCHAR(100); 2 SET @ID ='00000000-0000-0000-0000-000000000001'; 3 WITH Test AS 4 ( 5 SELECT * FROM T_Sys_Domain where Enable=1 and DomainID='00000000-0000-0000-0000-000000000001' 6 UNION ALL 7 SELECT a.* FROM T_Sys_Domain a, Test b where a.Enable=1 and b.domainId = a.ParentID and a.InstructorShareToParent=1 8 ) 9 select * from 10 ( select d.DomainName Domian, 11 c.UserID,c.LoginName,c.FirstName,c.LastName,c.Email,c.CompanyName,c.CreateTime 12 from Test as d,dbo.T_Sys_User as c 13 where d.domainid=c.domainid and IsInstructor=1 and IsEnabled=1 and LoginName like '%%' ) t 14 union 15 select (select DomainName from T_Sys_Domain where DomainID=u.DomainID) Domian, 16 u.UserID,u.LoginName,u.FirstName,u.LastName,u.Email,u.CompanyName,u.CreateTime from dbo.T_Sys_User u where IsInstructor=1 and IsEnabled=1 17 and DomainID in( 18 select ShareDomainID from dbo.T_Sys_Share s where DomainID='00000000-0000-0000-0000-000000000001' and ShareType=1 19 and s.ShareDomainID in (select DomainID from T_Sys_Domain where Enable=1)) 20 and LoginName like '%%' 21 order by LoginName
用到的存储过程：
1 CREATE PROCEDURE [P_SelectInstructor] 2 @pageIndex INT, 3 @pageSize INT , 4 @SqlSelStr nvarchar(4000), 5 @counNum int out 6 AS 7 print @SqlSelStr; 8 begin 9 10 IF OBJECT_ID('tempdb..#tb') is not null 11 DROP TABLE #tb 12 13 /**/--创建临时表，字段跟显示的列表一样 14 create table #tb( 15 DomainName nvarchar(100), 16 UserID uniqueidentifier, 17 LoginName nvarchar(80), 18 FirstName nvarchar(80), 19 LastName nvarchar(80), 20 Email nvarchar(320), 21 CompanyName nvarchar(80), 22 CreateTime datetime 23 ) 24 25 /**/ 26 insert into #tb exec(@SqlSelStr) 27 begin 28 select @counNum = COUNT('1') From #tb; 29 with InstructorTable as 30 ( 31 select row_number() over(order by CreateTime desc) as num2, * from #tb 32 ) 33 select DomainName,UserID,LoginName,FirstName,LastName,Email,CompanyName,CreateTime 34 from InstructorTable 35 where num2 between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize 36 end 37 end
修改前的方法：
1 public DataTable GetInstructorList(Guid domainId, string name) 2 { 3 if (!string.IsNullOrEmpty(name)) 4 { 5 name = "'%/" + name + "%' ESCAPE '/'"; 6 } 7 else 8 { 9 name = "'%" + name + "%'"; 10 } 11 12 13 string sql = string.Format( 14 @"DECLARE @ID NVARCHAR(100); 15 SET @ID ='{0}'; 16 WITH Test AS 17 ( 18 SELECT * FROM T_Sys_Domain where Enable=1 and DomainID='{0}' 19 UNION ALL 20 SELECT a.* FROM T_Sys_Domain a, Test b where a.Enable=1 and b.domainId = a.ParentID and a.InstructorShareToParent=1 21 ) 22 select * from 23 ( select d.DomainName Domian, 24 c.UserID,c.LoginName,c.FirstName,c.LastName,c.Email,c.CompanyName,c.CreateTime 25 from Test as d,dbo.T_Sys_User as c 26 where d.domainid=c.domainid and IsInstructor=1 and IsEnabled=1 and LoginName like {1} ) t 27 union 28 select (select DomainName from T_Sys_Domain where DomainID=u.DomainID) Domian, 29 u.UserID,u.LoginName,u.FirstName,u.LastName,u.Email,u.CompanyName,u.CreateTime from dbo.T_Sys_User u where IsInstructor=1 and IsEnabled=1 30 and DomainID in( 31 select ShareDomainID from dbo.T_Sys_Share s where DomainID='{0}' and ShareType=1 32 and s.ShareDomainID in (select DomainID from T_Sys_Domain where Enable=1)) 33 and LoginName like {1} 34 order by LoginName ", domainId, name); 35 36 DataSet ds = null; 37 using (DBFactory dbfac = new DBFactory()) 38 { 39 ds = dbfac.ExecuteSelectSql(sql); 40 } 41 42 if (ds != null && ds.Tables.Count > 0) 43 { 44 return ds.Tables[0]; 45 } 46 else 47 { 48 return null; 49 } 50 }
修改后的方法：
1 public DataTable GetInstructorList(Guid domainId, string name, int pageSize,int pageIndex, out int count) 2 { 3 if (!string.IsNullOrEmpty(name)) 4 { 5 name = "'%/" + name + "%' ESCAPE '/'"; 6 } 7 else 8 { 9 name = "'%" + name + "%'"; 10 } 11 12 13 string sql = string.Format( 14 @"DECLARE @ID NVARCHAR(100); 15 SET @ID ='{0}'; 16 WITH Test AS 17 ( 18 SELECT * FROM T_Sys_Domain where Enable=1 and DomainID='{0}' 19 UNION ALL 20 SELECT a.* FROM T_Sys_Domain a, Test b where a.Enable=1 and b.domainId = a.ParentID and a.InstructorShareToParent=1 21 ) 22 select * from 23 ( select d.DomainName Domian, 24 c.UserID,c.LoginName,c.FirstName,c.LastName,c.Email,c.CompanyName,c.CreateTime 25 from Test as d,dbo.T_Sys_User as c 26 where d.domainid=c.domainid and IsInstructor=1 and IsEnabled=1 and LoginName like {1} ) t 27 union 28 select (select DomainName from T_Sys_Domain where DomainID=u.DomainID) Domian, 29 u.UserID,u.LoginName,u.FirstName,u.LastName,u.Email,u.CompanyName,u.CreateTime from dbo.T_Sys_User u where IsInstructor=1 and IsEnabled=1 30 and DomainID in( 31 select ShareDomainID from dbo.T_Sys_Share s where DomainID='{0}' and ShareType=1 32 and s.ShareDomainID in (select DomainID from T_Sys_Domain where Enable=1)) 33 and LoginName like {1} 34 order by LoginName ", domainId, name); 35 // ds = dbfac.ExecuteSelectSql(sql); 36 37 List<SqlParameter> param = new List<SqlParameter>(); 38 SqlParameter sqlstr = new SqlParameter(); 39 sqlstr.Direction = ParameterDirection.Input; 40 sqlstr.SqlDbType = SqlDbType.NVarChar; 41 sqlstr.Value = sql.ToString(); 42 sqlstr.ParameterName = "@SqlSelStr"; 43 44 param.Add(new SqlParameter("@pageIndex", pageIndex)); 45 param.Add(new SqlParameter("@pageSize", pageSize)); 46 47 SqlParameter output = new SqlParameter(); 48 output.ParameterName = "@counNum"; 49 output.SqlDbType = SqlDbType.Int; 50 output.Direction = ParameterDirection.Output; 51 param.Add(output); 52 param.Add(sqlstr); 53 DataSet ds = null; 54 using (DBFactory dbfac = new DBFactory()) 55 { 56 ds = dbfac.ExecuteSelectSql 57 ("exec [P_SelectInstructor] @pageIndex,@pageSize,@SqlSelStr,@counNum output", 58 param.ToArray()); 59 count = int.Parse(output.Value.ToString()); 60 } 61 62 if (ds != null && ds.Tables.Count > 0) 63 { 64 return ds.Tables[0]; 65 } 66 else 67 { 68 return null; 69 } 70 }
