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
数据库行列转换以及c#数据库基本操作
最新推荐文章于 2024-08-15 22:03:42 发布