helper类实现增删改查，关键字(Case，sql声明变量，If语句，while)，子查询，分页查询
ADO.NET复习(一)
先复习几个关键词
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
select ID,username,
case
when id=1
then N'牛逼'
else N'不牛逼'
end
as 卧槽
from UserInfo
|
INULL函数,但是最好不用
1
| select EditorComment,ISNULL(EditorComment,'11111') as '牛逼' FROM [book_shop3].[dbo].[Books]
|
sql声明变量
1
2
3
| declare @I int
set @I=1
print @I
|
给表计算总行数然后赋值
1
2
3
| declare @num int
set @num=(select COUNT(1) from UserInfo)
print @num
|
select 可以给变量赋值，把上面的set换成select也行
IF语句
1
2
3
4
5
6
7
8
9
10
| declare @num int
select @num=(select COUNT(1) from UserInfo)
if(@num<5)
begin
print '小于5'
end
else
begin
print '大于5'
end
|
while的用法跟if差不多
1
2
3
4
5
6
7
| declare @num int
select @num=(select COUNT(1) from UserInfo)
while(@num<100)
begin
print @num
set @num=@num+1
end
|
子查询
最简单的子查询
1
2
| SELECT * from
(select ID from Userinfo where id<5) as T
|
第二种
1
2
3
4
5
| select * from UserInfo where ID<(select count(*) from Userinfo)
select* from ActionGroupActionInfo where ActionInfo_ID =
(select top 1 ActionInfo_ID as 权限数量 from ActionGroupActionInfo where ActionInfo_ID=8
)
|
分页查询
第一种，跳过多少取多少(—-以每页3个数据为例子，跳过多少(index-1)*3，拿多少3)，in代表从集合里面
1
2
3
| select top 3 * from [dbo].[ActionGroupActionInfo] where [ActionGroup_ID] not in (
select top((3-1)*3) [ActionGroup_ID] from [dbo].[ActionGroupActionInfo] order by [ActionGroup_ID]
)
|
开窗函数over，比如说select * ,avg(Username)，这样的查询时错的，因为前面查询的是列明，你后面的只是一个数，这两者不同等，
第二种,ROW_NUMBER函数，从1开始，为每一条分组记录返回一个数字。
1
| select * from (select *,ROW_NUMBER() over(order by ID) as num from UserInfo)as T where T.num between ((4-1)*3+1) and (4*3)
|
一个简单的增删改查helper类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
| public class SqlHelper
{
#region 返回连接字符串
public static string GetSqlConnectionString()
{
return ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
}
#endregion
#region 封装一个执行sql 返回受影响的行数
public static int ExcuteNonQuery(string sqlText,params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
{
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
#endregion
#region 执行sql。返回 查询结果中的 第一行第一列的值
public static object ExcuteScalar(string sqlText, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
{
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
public static T ExcuteScalar<T>(string sqlText, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
{
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(parameters);
return (T)cmd.ExecuteScalar();
}
}
}
#endregion
#region 执行sql 返回一个DataTable
public static DataTable ExcuteDataTable(string sqlText, params SqlParameter[] parameters)
{
#region MyRegion
#endregion
return ExcuteDataTable(sqlText, CommandType.Text, parameters);
}
#endregion
#region 执行sql 脚本，返回一个SqlDataReader
public static SqlDataReader ExucteReader(string sqlText, params SqlParameter[] parameters)
{
SqlConnection conn =new SqlConnection(GetSqlConnectionString());
SqlCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion
#region 提供一个 可以 执行存储过程的方法。
public static DataTable ExcuteDataTable(string sqlText,CommandType commandType, params SqlParameter[] parameters)
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sqlText, GetSqlConnectionString()))
{
DataTable dt =new DataTable();
adapter.SelectCommand.Parameters.AddRange(parameters);
adapter.SelectCommand.CommandType = commandType;
adapter.Fill(dt);
return dt;
}
}
#endregion
}
|