数据库行列转换以及c#数据库基本操作

http://blog.csdn.net/kankankankan2222/article/details/7456432  //数据库网页
  com.CommandText = "exec [dbo].[demo_tests] -2";  调用存储过程语句
c#调用数据库方法
try
            {
                SqlConnection con = new SqlConnection();

                // con.ConnectionString = "server=505-03;database=ttt;user=sa;pwd=123";
                con.ConnectionString = "server=.;database=demo;uid=zzw;pwd=123456";
                con.Open();

                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "select * from demos";//拼接sql语句
                SqlDataReader dr = com.ExecuteReader();//执行SQL语句
                
                List<String> list = new List<String>();
                while(dr.Read()){
                    list.Add(dr.GetString(1));
                }
                dr.Close();//关闭执行
                con.Close();//关闭数据库
                return list;
            }catch(Exception e){
                return null;
            }




分页查询
select * from (
       select *,ROW_NUMBER() OVER (ORDER BY FlightsDetailID) as rank from tbl_FlightsDetail
)  as t where t.rank between 3000001 and 3000010


行列转换--静态转换
方法一
select [Name],
sum(case [Class] when 'dfff' then '1' else 0 end) as 'dfff',
sum(case [Class] when 'aaa' then '1' else 0 end) as 'aaa',
sum(case [Class] when '对对对' then '1' else 0 end) as '对对对',
sum(case [Class] when 'sss' then '1' else 0 end) as 'sss'
from demo_table 
GROUP BY Class,Name

方法二
select * from (
select [Name],[Class] FROM demo_table
) as p PIVOT (
count([Name]) for [Name] IN ([asd],[双射手],[as],[去去去],[对对对])
) as pvt ORDER BY pvt.[Class];
GO


游标运用
DECLARE @id INT
DECLARE @name VARCHAR(20)
DECLARE @class INT
DECLARE cursors CURSOR for SELECT * FROM demo_table
OPEN cursors
FETCH NEXT FROM cursors INTO @id,@name,@class
WHILE (@@fetch_status=0)
BEGIN
if @class=500
BEGIN
select * FROM demos where id = @class
END
fetch next from cursors into @id,@name,@class
END
CLOSE cursors

临时表
ALTER PROCEDURE [dbo].[demo_sos]
@id int,
@name VARCHAR(20)
AS

BEGIN
DECLARE @s INT
set @s=1

CREATE table #css(
id int,
name VARCHAR(20)
)

INSERT into #css values(1,'sssssss')
	select * from #css WHERE id=@s
END