开发过程中，Insus.NET经常性遇上一对多的数据关系，并需要存入数据中。就以下图为例，一个拉号可以对应多台机器。下图有三个铵钮，分别为[分配机器], [取消分配]和[查看分配]。
想存储这些分配数据，需要在数据库创建一个表存储。
LaHaoJiQi SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Insus.NET -- Create date: 2012-09-13 -- Description: 存储拉号与机器分配记录 -- ============================================= CREATE TABLE [ dbo ]. [ LaHaoJiQi ]( [ LaHaoId ] [ tinyint ] NULL, [ JiQi_nbr ] [ smallint ] NULL, [ IsEnable ] [ bit ] NULL ) ON [ PRIMARY ] GO
写一个存储过程，处理分配与取消分配，数据记录插入与更新，存储过程代码示例，有详细备注。
usp_LaHaoJiQi_Distribution SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Insus.NET -- Create date: 2012-09-13 -- Description: 拉号与机器分配或取消 -- ============================================= ALTER PROCEDURE [ dbo ]. [ usp_LaHaoJiQi_Distribution ] ( @LaHaoId TINYINT, -- 用户选择的拉号(只有一个) @jIqiCollections NVARCHAR( MAX), -- 选择的机器，有可能多台机器，前台处理的结果，如1,4,7,9,12 @IsEnable BIT -- 告诉系统，是分配还是取消分配，如果值为1是分配，那0为取消分配。 ) AS -- 创建一个临时表,用来存储选择的机器。 IF OBJECT_ID ( ' #jq ') IS NOT NULL DROP TABLE #jq CREATE TABLE #jq ( [ ID ] INT, [ JiQi_nbr ] SMALLINT ) -- 把字符串的机器拆分，然后插入刚才创建的临时表中,下面有一个函数，可以参考URL链接。
-- INSERT INTO #jq SELECT [ ID ], [ KeyWord ] FROM [ dbo ]. [ udf_SplitStringToTable ]( @jIqiCollections, ' , ') DECLARE @r INT = 1, @r1 INT SELECT @r1 = MAX( [ ID ]) FROM #jq -- 获取最大的记录ID,将为下面的记录循环准备 WHILE @r <= @r1 -- 开始循环 BEGIN DECLARE @JiQi_nbr SMALLINT IF EXISTS ( SELECT TOP 1 1 FROM #jq WHERE [ ID ] = @r) BEGIN -- 获取机器ID SELECT @JiQi_nbr = [ JiQi_nbr ] FROM #jq WHERE [ ID ] = @r IF @IsEnable = 1 -- 是分配动作 BEGIN -- 如果此机器已经存在分配表中。 IF EXISTS ( SELECT TOP 1 1 FROM [ dbo ]. [ LaHaoJiQi ] WHERE [ JiQi_nbr ] = @JiQi_nbr) BEGIN -- 判断此机器是否已经被分配。IsEnable为1是分配，为0没有被分配。 IF EXISTS ( SELECT TOP 1 1 FROM [ dbo ]. [ LaHaoJiQi ] WHERE [ JiQi_nbr ] = @JiQi_nbr AND [ IsEnable ] = 0) -- 更新拉号和分配状态 UPDATE [ dbo ]. [ LaHaoJiQi ] SET [ LaHaoId ] = @LaHaoId, [ IsEnable ] = @IsEnable WHERE [ JiQi_nbr ] = @JiQi_nbr ELSE -- 如果已经被分配，只更新IsEnable的值，变为分配。 UPDATE [ dbo ]. [ LaHaoJiQi ] SET [ IsEnable ] = @IsEnable WHERE [ JiQi_nbr ] = @JiQi_nbr END ELSE -- 如果此机器不存在分配表中，把记录插入表中。 INSERT INTO [ dbo ]. [ LaHaoJiQi ] ( [ LaHaoId ], [ JiQi_nbr ], [ IsEnable ]) VALUES ( @LaHaoId, @JiQi_nbr, @IsEnable) END -- 取消分配动作 IF @IsEnable = 0 AND EXISTS ( SELECT TOP 1 1 FROM [ dbo ]. [ LaHaoJiQi ] WHERE [ LaHaoId ] = @LaHaoId AND [ JiQi_nbr ] = @JiQi_nbr AND [ IsEnable ] = 1) UPDATE [ dbo ]. [ LaHaoJiQi ] SET [ IsEnable ] = @IsEnable WHERE [ JiQi_nbr ] = @JiQi_nbr END SET @r = @r + 1 END -- 结束循环
处理前与数据库之间传输，也就是逻辑层的方法：
View Code public void DistributionJiQi() { Parameter[] parameter = { new Parameter ( " @LaHaoId ",SqlDbType.TinyInt, 1,_LaHaoId), new Parameter ( " @jIqiCollections ",SqlDbType.NVarChar,- 1,_JiQiCollections), new Parameter ( " @IsEnable ",SqlDbType.Bit, 1,_IsEnable) }; objBusinessBase.ExecuteProcedure( " usp_LaHaoJiQi_Distribution ", parameter); }
然后，在页面中，两个铵钮事件，两个铵钮事件写法其本一样，只是稍微不一样。
View Code // 分配动作 protected void ButtonDistributionJiQi_Click( object sender, EventArgs e) { string jqPrimarykey = GetCheckBoxListSelectedValue( this.CheckBoxListJiQi); try { objLaHaoJiQi.LaHaoId = ConvertData.ToByte( this.RadioButtonListLaHao.SelectedItem.Value); objLaHaoJiQi.JiQiCollections = jqPrimarykey; objLaHaoJiQi.IsEnable = true; // 设为true objLaHaoJiQi.DistributionJiQi(); Data_Binding(); objInsusJsUtility.JsAlert( " 机器成功分配。 "); } catch (Exception ex) { objInsusJsUtility.JsAlert(ex.Message); } } // 取消分配动作 protected void ButtonCancelDistributionJiQi_Click( object sender, EventArgs e) { string jqPrimarykey = GetCheckBoxListSelectedValue( this.CheckBoxListJiQi); try { objLaHaoJiQi.LaHaoId = ConvertData.ToByte( this.RadioButtonListLaHao.SelectedItem.Value); objLaHaoJiQi.JiQiCollections = jqPrimarykey; objLaHaoJiQi.IsEnable = false; // 设为false objLaHaoJiQi.DistributionJiQi(); Data_Binding(); objInsusJsUtility.JsAlert( " 机器成功取消分配。 "); } catch (Exception ex) { objInsusJsUtility.JsAlert(ex.Message); } }
细心的用户，也许会发现上面的代码，一个方法GetCheckBoxListSelectedValue()，就是获取CheckBoxList的选择选项的值。它的代码是这样子的：
View Code protected string GetCheckBoxListSelectedValue(CheckBoxList checkBoxList) { string selectedValue = string.Empty; foreach (ListItem li in checkBoxList.Items) { if (li.Selected) { selectedValue = selectedValue + " , " + li.Value; } } if (selectedValue.Length > 0) { selectedValue = selectedValue.Substring( 1); } return selectedValue; }