本篇接C# SQL封装（三）
在其功能上继续扩展 改操作 ， 让我们开始吧。。。。。
一 ： 构建T-SQL语句（在SQL_Structure.cs中添加一个方法 UPDATE_T_SQL ， 用于构建update的T-SQL）
|
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
|
/// <summary>
/// 更新一条数据的T-SQL构成（以id号来删除）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model">原始model</param>
/// <param name="model_change">更改后的model</param>
/// <returns></returns>
public
static
string
UPDATE_T_SQL<T>(T model , T model_change)
where
T : BaseModel
{
Type model_type = model.GetType();
Type model_change_type = model_change.GetType();
if
(model_type.Name == model_change_type.Name)
{
string
base_update =
@"update [{0}] set {1} where {2} = @{3}"
;
string
[] arr = model_type.Name.Split(
new
char
[] {
'.'
});
string
table_name = arr[arr.Length - 1];
//获得表的名称
PropertyInfo[] p_intos = model_type.GetProperties();
PropertyInfo item =
null
;
PropertyInfo item_change =
null
;
string
item_value =
string
.Empty;
string
item_change_value =
string
.Empty;
StringBuilder update_set =
null
;
for
(
int
i = 0; i < p_intos.Length; i++)
{
item = p_intos[i];
item_change = model_change_type.GetProperty(item.Name);
item_value = item.GetValue(model,
null
).ToString();
item_change_value = item_change.GetValue(model_change,
null
).ToString();
if
(item.Name ==
"id"
)
{
//比较id值是否一致
if
(item_value != item_change_value)
{
throw
new
Exception(
string
.Format(
"model类 id : {0} 与model_change类id:{1} 不一致,请确保是同一条数据"
,
item_value,
item_change_value
)
);
}
}
else
{
if
(item_value != item_change_value)
{
if
(update_set ==
null
) update_set =
new
StringBuilder();
update_set.Append(
string
.Format(
@" {0} = @{1},"
, item.Name , item.Name )
);
}
}
}
if
(update_set !=
null
)
{
return
string
.Format( base_update,
table_name,
update_set.ToString().Substring(0, update_set.ToString().Length - 1),
"id"
,
"id"
);
}
else
{
return
string
.Empty;
//不存在更新
}
}
else
{
throw
new
Exception(
string
.Format(
"model类 : {0} 与model_change类 :{1} 不匹配"
,
model_type.Name ,
model_change_type.Name
)
);
}
}
|
二 ： 构建SqlParameter参数 （在SqlParameter_Structure.as中添加方法UPDATE_T_SQL）
|
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
|
/// <summary>
/// 构建T-SQL参数 UPDATE
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model">原model</param>
/// <param name="model_change">更改后model</param>
/// <returns></returns>
public
static
SqlParameter[] UPDATE_T_SQL<T>(T model, T model_change)
where
T : BaseModel
{
Type model_type = model.GetType();
Type model_change_type = model_change.GetType();
if
(model_type.Name == model_change_type.Name)
{
PropertyInfo[] p_intos = model_type.GetProperties();
PropertyInfo item =
null
;
PropertyInfo item_change =
null
;
string
item_value =
string
.Empty;
string
item_change_value =
string
.Empty;
List<SqlParameter> sql_param =
null
;
SqlParameter cell =
null
;
bool
is_change_model =
false
;
//是否有字段进行了更改
for
(
int
i = 0; i < p_intos.Length; i++)
{
item = p_intos[i];
item_change = model_change_type.GetProperty(item.Name);
item_value = item.GetValue(model,
null
).ToString();
item_change_value = item_change.GetValue(model_change,
null
).ToString();
if
(item.Name ==
"id"
)
{
if
(item_value != item_change_value)
{
throw
new
Exception(
string
.Format(
"model类 id : {0} 与model_change类id:{1} 不一致,请确保是同一条数据"
,
item_value,
item_change_value
)
);
}
else
{
if
(sql_param ==
null
) sql_param =
new
List<SqlParameter>();
cell =
new
SqlParameter(item.Name, SqlParameter_Structure.Get_SqlDbType_SqlType(item.PropertyType));
cell.Value = item.GetValue(model,
null
);
sql_param.Add(cell);
}
}
else
{
if
(item_value != item_change_value)
{
if
(!is_change_model) is_change_model =
true
;
if
(sql_param ==
null
) sql_param =
new
List<SqlParameter>();
cell =
new
SqlParameter(item_change.Name, SqlParameter_Structure.Get_SqlDbType_SqlType(item_change.PropertyType));
cell.Value = item_change.GetValue(model_change,
null
);
//加入改变的值
sql_param.Add(cell);
}
}
}
if
(is_change_model)
{
return
sql_param.ToArray<SqlParameter>();
}
else
{
return
null
;
//没有任何字段更改
}
}
else
{
throw
new
Exception(
string
.Format(
"model类 : {0} 与model_change类 :{1} 不匹配"
,
model_type.Name,
model_change_type.Name
)
);
}
}
|
测试：
测试代码
//------------------------模拟一条重数据库得到的数据 begin ---------------------------------------
User kayer_ori = new User();
kayer_ori.id = 3;
kayer_ori.name = "kayer";
kayer_ori.sex = 1;
kayer_ori.lv = 1;
kayer_ori.username = "Kayer";
kayer_ori.userpwd = "123";
//------------------------模拟一条重数据库得到的数据 end -----------------------------------------
//拷贝一条kayer_ori数据 （ 可以用原型模式 ， 这里我直接使用笨办法 ）
User kayer_change_copy = new User();
kayer_change_copy.id = kayer_ori.id;
kayer_change_copy.name = kayer_ori.name;
kayer_change_copy.sex = kayer_ori.sex;
kayer_change_copy.lv = kayer_ori.lv;
kayer_change_copy.username = kayer_ori.username;
kayer_change_copy.userpwd = kayer_ori.userpwd;
// --- 改变数据 ---
kayer_change_copy.name = "Aonaufly";
kayer_change_copy.lv = 4;
string sql_update = SQL_Structure.UPDATE_T_SQL<User>(kayer_ori, kayer_change_copy);
SqlParameter[] update_p = SqlParameter_Structure.UPDATE_T_SQL<User>(kayer_ori, kayer_change_copy);
int i = SqlHelper.ExecteNonQuery(CommandType.Text, sql_update, update_p);
if (i > 0)
{
Console.WriteLine("执行操作成功");
}
else
{
Console.WriteLine("执行操作失败");
}
结果 :
在数据库中：
未完待续 ..........................
扫一扫
2690
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
