现有字典表(Dict)
字段 | 说明 |
Id | 标示 |
ItemKey | 键 |
ItemValue | 值 |
UpperId | 上层标示 |
//操作步骤:展开相关表,右击‘触发器’,新建即可
USE [sqlffwj]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[CheckKeyRepeated] ON [dbo].[Dict] for INSERTASif(select COUNT(*) from [Dict], inserted inobj where [Dict].ItemKey = inobj.ItemKey and [Dict].Id != inobj.Id) > 0BEGIN raiserror('已有相同键,不能插入',16,1) rollback tranEND
USE [sqlffwj]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[CheckDependence] ON [dbo].[Dict] for deleteASif(select COUNT(*) from [Dict], deleted delbj where [Dict].UpperId = delbj.Id) > 0BEGIN raiserror('有下层记录,不能删除',16,1) rollback tranEND
USE [sqlffwj]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[CheckDependence2] ON [dbo].[Dict] AFTER deleteASwhile(select COUNT(*) from [Dict] where UpperId != 0 and UpperId not in (select Id from [Dict])) > 0BEGIN delete from [Dict] where UpperId != 0 and UpperId not in (select Id from [Dict]) END
现有用户表(User)
字段 | 说明 |
Id | 标示 |
Name | 姓名 |
Age | 年龄 |
DeptId | 部门标示 |
部门表(Dept)
字段 | 说明 |
Id | 标示 |
Name | 名称 |
//操作步骤:展开数据库,再展开可编程性,右击‘存储过程’,新建即可
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetUsers]ASBEGIN select [User].Id '标示',[User].Name '姓名', [User].Age '年龄', [Dept].Name '部门' from [User] left join [Dept] on [User].DeptId = [Dept].IdENDGO
/* 调用 */exec GetUsers
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetUsersByDept]( @deptid int /* 部门标示 */ )ASBEGIN select [User].Id '标示',[User].Name '姓名', [User].Age '年龄', [Dept].Name '部门' from [User] left join [Dept] on [User].DeptId = [Dept].Id where [Dept].Id = @deptidENDGO
/* 调用 */exec GetUsersByDept 2
1、环境:VS2010+sql2008
2、新建edmx文件,引用两张表和存储过程
3、切换到‘模型浏览器’,‘添加函数导入’
4、在‘添加函数导入’面板,点击‘获取列信息’,获取到列信息后再点击‘创建新的复杂类型’,确定后就可以通过Func调用存储过程了
5、调用代码Demo
using (var context = new SqltestEntities()) { var result = context.GetUsersByDept(2); throw new Exception(result.Count().ToString()); }
新闻热点
疑难解答