SQL多表插入事务处理
浏览数：86 /
时间：2015年06月12日
新建两个需统一事务处理的数据表
--学生信息表 CREATE TABLE [dbo].[Student]( [Id] [int] NOT NULL, [Name] [varchar](50) NOT NULL, [Age] [int] NOT NULL, [Address] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --学生成绩表 CREATE TABLE [dbo].[Result]( [Id] [int] IDENTITY(1,1) NOT NULL, [StudentId] [int] NOT NULL, [Subject] [varchar](50) NOT NULL, [Score] [int] NOT NULL, CONSTRAINT [PK_Result] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
代码中定义相应的实体类
public class Student { /// <summary> ///编号 /// </summary> public int StudentId { get; set; } /// <summary> /// 姓名 /// </summary> public string Name { get; set; } /// <summary> /// 年龄 /// </summary> public int Age { get; set; } /// <summary> /// 地址 /// </summary> public string Address { get; set; } } public class Result { /// <summary> /// 学生编号 /// </summary> public int StudentId { get; set; } /// <summary> /// 科目 /// </summary> public string Subject { get; set; } /// <summary> /// 得分 /// </summary> public int Score { get; set; } }
定义一个把实体转换成XML的通用方法
/// <summary> /// 把实体对象转换成Xml /// </summary> /// <typeparam name="T"></typeparam> /// <param name="model"></param> /// <returns></returns> protected string GetSerializer<T>(T model) where T : new() { StringBuilder sb = new StringBuilder(); XmlSerializer x = new XmlSerializer(typeof(T)); StringWriter sw = new StringWriter(sb); x.Serialize(sw, model); return ProcessXmlStr(sb.ToString()); } /// <summary> /// 处理Xml中的特殊字符 /// </summary> /// <param name="xml"></param> /// <returns></returns> public string ProcessXmlStr(string xml) { string xmlHead = "<?xml version=\"1.0\" encoding=\"utf-16\"?>"; string strSpace = "\r\n"; string strXlnsxsi = " xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\""; string strXlnsxsd = "xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\""; string strNil = "xsi:nil=\"true\""; return xml.Replace(xmlHead, "").Replace(strSpace, "").Replace(strXlnsxsi, "").Replace(strXlnsxsd, "") .Replace("> <", "><").Replace(strNil, "").Replace("<", "<").Replace(">", ">"); }
把实体转换成Xml格式
/// <summary> /// 获取单个对象sql执行脚本 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="parameterValue">参数值</param> /// <param name="procName">存储过程名称</param> /// <param name="procIndex">存储过程执行顺序</param> /// <param name="parameterName">参数名称</param> /// <returns></returns> protected string GetSingleExcuteSql<T>(T parameterValue, string procName, int procIndex, string parameterName) where T : new() { StringBuilder sb = new StringBuilder(); sb.Append(string.Format("<SP Name=\"{0}\" ProcIndex=\"{1}\" ParameterName=\"{2}\" ParameterValue=\"{3}\"></SP>", procName, procIndex, parameterName, parameterValue != null ? GetSerializer(parameterValue) : "")); return sb.ToString(); } /// <summary> /// 获取sql执行脚本 /// </summary> /// <param name="student">学生信息</param> /// <param name="resultList">学生成绩</param> /// <returns></returns> protected string GetExcuteSql(Student student, List<Result> resultList) { StringBuilder sb = new StringBuilder(); sb.Append(GetSingleExcuteSql<Student>(student, "Proc_Insert_Student", (int)SqlExcuteIndex.CurrentStep, "StudentXml")); sb.Append(GetSingleExcuteSql<List<Result>>(resultList, "Proc_Insert_Result", (int)SqlExcuteIndex.CurrentStep + 1, "ResultXml")); return sb.ToString(); }
组装Xml，并统一执行
/// <summary> /// 使用事务进行存储 /// </summary> /// <returns></returns> public bool ExcuteTransaction() { return new TestDAL().ProcessOverall("<SPList>"+GetExcuteSql(student,resultList)+"</SPList>"); } /// <summary> /// 事件统一执行 /// </summary> /// <param name="allInfo"></param> /// <returns></returns> public bool ProcessOverall(string allInfo) { bool result = false; SqlParameter param = new SqlParameter("@AllInfo", allInfo); result = Excute("proc_flow_ProcessOverall", param, CommandType.StoredProcedure); return result; }
数据库创建XML处理函数
-- ============================================= -- Author: Casper -- Create date: 2014/09/30 -- Description: 预处理xml字符串，替换特殊字符 -- ============================================= CREATE FUNCTION [dbo].[func_sys_PreProcessXmlStr] ( @xmlStr NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@xmlStr , ‘&gt;‘,‘>‘) , ‘&amp;gt;‘,‘>‘) , ‘&lt;‘,‘<‘) , ‘&amp;lt;‘, ‘<‘) , ‘<‘, ‘<‘) , ‘>‘, ‘>‘) , ‘0001-01-01T00:00:00‘, ‘‘) END
数据库创建单表保存存储过程
-- ============================================= -- Author: Casper -- Create date: 2014-09-30 -- Description: 学生成绩保存 -- ============================================= CREATE PROCEDURE [dbo].[Proc_Insert_Result] @ResultXml NVARCHAR(MAX) AS BEGIN IF @ResultXml <> ‘‘ BEGIN --DECLARE @ResultXml NVARCHAR(MAX)=‘<ArrayOfResult ><Result> <StudentId>1</StudentId> <Subject>语文</Subject> <Score>80</Score></Result><Result> <StudentId>1</StudentId> <Subject>数学</Subject> <Score>60</Score></Result></ArrayOfResult>‘ DECLARE @a INT SET @ResultXml = dbo.func_sys_PreProcessXmlStr(@ResultXml) --替换XML特殊字符 EXEC sp_xml_preparedocument @a OUTPUT, @ResultXml SELECT * INTO #ResultInfo FROM OPENXML(@a,‘ArrayOfResult/Result‘,2) WITH ( StudentId INT, [Subject] VARCHAR(50), Score INT ) SELECT * FROM #ResultInfo IF EXISTS ( SELECT * --判断新增记录是否存在,如果存在则修改，否则插入 FROM dbo.Result WHERE Id = ( SELECT Id FROM #ResultInfo ) ) BEGIN UPDATE a SET StudentId = b.StudentId , [Subject] = b.[Subject] , Score = b.Score FROM dbo.Result a INNER JOIN #ResultInfo b ON a.Id = b.Id END ELSE BEGIN INSERT INTO dbo.Result ( StudentId , Subject , Score ) SELECT StudentId , Subject , Score FROM #ResultInfo END END DROP TABLE #ResultInfo END -- ============================================= -- Author: Casper -- Create date: 2014-09-30 -- Description: 学生成绩信息保存 -- ============================================= CREATE PROCEDURE [dbo].[Proc_Insert_Result] @ResultXml NVARCHAR(MAX) AS BEGIN IF @ResultXml <> ‘‘ BEGIN --DECLARE @ResultXml NVARCHAR(MAX)=‘<ArrayOfResult ><Result> <StudentId>1</StudentId> <Subject>语文</Subject> <Score>80</Score></Result><Result> <StudentId>1</StudentId> <Subject>数学</Subject> <Score>60</Score></Result></ArrayOfResult>‘ DECLARE @a INT SET @ResultXml = dbo.func_sys_PreProcessXmlStr(@ResultXml) --替换XML特殊字符 EXEC sp_xml_preparedocument @a OUTPUT, @ResultXml SELECT * INTO #ResultInfo FROM OPENXML(@a,‘ArrayOfResult/Result‘,2) WITH ( Id INT, StudentId INT, [Subject] VARCHAR(50), Score INT ) IF EXISTS ( SELECT * --判断新增记录是否存在,如果存在则修改，否则插入 FROM dbo.Result WHERE Id = ( SELECT Id FROM #ResultInfo ) ) BEGIN UPDATE a SET StudentId = b.StudentId , [Subject] = b.[Subject] , Score = b.Score FROM dbo.Result a INNER JOIN #ResultInfo b ON a.Id = b.Id END ELSE BEGIN INSERT INTO dbo.Result ( StudentId , Subject , Score ) SELECT StudentId , [Subject] , Score FROM #ResultInfo END END END
最后创建统一事务处理存储过程
---- ============================================= ---- Author: Caper ---- Create date: 2014-09-30 ---- Description: 统一事务处理 CREATE PROCEDURE [dbo].[proc_flow_ProcessOverall] @AllInfo NVARCHAR(MAX) AS BEGIN --DECLARE @a INT --DECLARE @AllInfo NVARCHAR(MAX) --SET @AllInfo = ‘<SPList><SP Name="Proc_Insert_Student" ProcIndex="1" ParameterName="StudentXml" ParameterValue="<Student ><StudentId>1</StudentId><Name>Johh</Name><Age>18</Age><Address>中国上海</Address></Student>"></SP><SP Name="Proc_Insert_Result" ProcIndex="2" ParameterName="ResultXml" ParameterValue="<ArrayOfResult ><Result> <StudentId>1</StudentId> <Subject>语文</Subject> <Score>80</Score></Result><Result> <StudentId>1</StudentId> <Subject>数学</Subject> <Score>60</Score></Result></ArrayOfResult>"></SP></SPList>‘ EXEC sp_xml_preparedocument @a OUTPUT, @AllInfo SELECT * INTO #temp FROM OPENXML (@a, ‘SPList/SP‘,1) WITH ( Name VARCHAR(500), ProcIndex INT, ParameterName VARCHAR(100), ParameterValue NVARCHAR(MAX) ) SELECT * FROM #temp SELECT DISTINCT ProcIndex , ‘ exec ‘ + NAME + ( STUFF(( SELECT ‘,‘ + ‘ @‘ + ParameterName + ‘=‘‘‘ + ParameterValue + ‘‘‘‘ FROM #temp WHERE NAME = t1.name FOR XML PATH(‘‘) ), 1, 1, ‘‘) ) AS ProcStr INTO #TEMP1 FROM #temp t1 SELECT * FROM #TEMP1 --按存储过程执行顺序排序 SELECT * INTO #TEMP2 FROM #TEMP1 ORDER BY ProcIndex SELECT * FROM #TEMP2 -- --执行存储过程 DECLARE @sql NVARCHAR(MAX) SELECT @sql = ‘ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET XACT_ABORT ON BEGIN TRANSACTION FlowProcessOverall ‘ + STUFF(( SELECT ‘;‘ + ProcStr FROM #TEMP2 FOR XML PATH(‘‘) ), 1, 1, ‘‘) + ‘ COMMIT TRANSACTION FlowProcessOverall ‘ SELECT @sql EXECUTE sp_executesql @sql DROP TABLE #temp ,#TEMP1 ,#TEMP2 END
国庆快乐
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
