请问.net 事务中如何获得自增值
主表：T_Authority_LoginUser子表： T_Authority_UserRole
关联： T_Authority_LoginUser.F_UserID =T_Authority_UserRole.FK_UserID
/// <summary>
/// 增加一条数据,及其子表数据
/// </summary>
public int Add(Model.T_Authority_LoginUser model, string[] ArrayRoles)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_Authority_LoginUser(");
strSql.Append("F_UserName,F_Password,FK_EmployeeID,F_LastLoginTime,F_Disabled,F_CreateTime)");
strSql.Append(" values (");
strSql.Append ("@F_UserName,@F_Password,@FK_EmployeeID,@F_LastLoginTime,@F_Disabled,@F_CreateTime)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@F_UserName", SqlDbType.VarChar,50),
new SqlParameter("@F_Password", SqlDbType.Char,50),
new SqlParameter("@FK_EmployeeID", SqlDbType.Int,4),
new SqlParameter("@F_LastLoginTime", SqlDbType.DateTime),
new SqlParameter("@F_Disabled", SqlDbType.Bit,1),
new SqlParameter("@F_CreateTime", SqlDbType.DateTime),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.F_UserName;
parameters[1].Value = model.F_Password;
parameters[2].Value = model.FK_EmployeeID;
parameters[3].Value = model.F_LastLoginTime;
parameters[4].Value = model.F_Disabled;
parameters[5].Value = model.F_CreateTime;
parameters[6].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
//同时插入表T_Authority_UserRole
StringBuilder strSql2;
foreach (string strRolesID in ArrayRoles)
{
strSql2 = new StringBuilder();
strSql2.Append("insert into T_Authority_UserRole(");
strSql2.Append("FK_UserID,FK_RolesID)");
strSql2.Append(" values (");
strSql2.Append("@FK_UserID,@FK_RolesID)");
strSql2.Append(";select @@IDENTITY");
SqlParameter[] parameters2 = {
new SqlParameter("@FK_UserID", SqlDbType.Int,4),
new SqlParameter("@FK_RolesID", SqlDbType.Int,4)};
parameters2[0].Value = (int)parameters[6].Value;------------就是这里我如何获得主表自增的值呢
parameters2[1].Value = Convert.ToInt16(strRolesID);
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
}
//DbHelperSQL.ExecuteSqlTran(sqllist);
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[6].Value;
}
--------------------编程问答-------------------- MS SQL Server ? insert之后， select @@IDENTITY就可以得到自增值 --------------------编程问答-------------------- @@IDENTITY 和 SCOPE_IDENTITY
插入后返回这个值 --------------------编程问答-------------------- 用 select SCOPE_IDENTITY() 獲得 --------------------编程问答-------------------- 但是我是在事务过程中，就是在我标明红字的地方，如何填写呢？？、在下实在弄不明白。
--------------------编程问答-------------------- 大家帮帮忙吧，你们说的，我应该怎么写呢，在红色标记的部分，谢谢大家了， --------------------编程问答-------------------- 怎么没有人，帮忙解决一下啊！纠结中，纠结我很久了 --------------------编程问答-------------------- 循环执行object obj = cmd.ExecuteScalar();
然后返回一个ArrayList回来 --------------------编程问答--------------------
+1 --------------------编程问答-------------------- 插从表可以考虑用触发器啊哈哈，看例子：
当消费记录表CardDealRecordConsume插入数据时，触发TrgChargeInsert触发器向
短信内容表SmsMessage插入相关信息
--------------------编程问答-------------------- +1
GO
IF EXISTS( SELECT name FROM sysobjects WHERE name='TrgChargeInsert' AND type='TR')
DROP TRIGGER TrgChargeInsert
GO
RAISERROR ('CREATE TRIGGER: TrgChargeInsert',0,1) WITH NOWAIT
GO
CREATE TRIGGER TrgChargeInsert ON CardDealRecordConsume FOR INSERT
AS
IF @@rowcount = 0
RETURN
SET NOCOUNT ON
DECLARE @CustomerID INT, @MobilePhone VARCHAR(20), @DealAmount Money, @ArisesTime DATETIME, @BookID INT,@PhysicalCode CHAR(10),@CardID INT,@SendMessage VARCHAR(255)
IF EXISTS(SELECT * FROM Inserted where BookID in(101,102,103,104,105))
BEGIN
SELECT @CustomerID=CustomerID,@DealAmount=DealAmount,@ArisesTime=ArisesTime,@PhysicalCode=PhysicalCode,@CardID=CardID FROM Inserted where BookID in(101,102,103,104,105)
SELECT @MobilePhone = MobilePhone
FROM VwCustomer WHERE PhysicalCode = @PhysicalCode
IF @MobilePhone IS NOT NULL
BEGIN
SET @SendMessage = '您好！您的校园卡于 '
+ DATENAME(YEAR, @ArisesTime) + '年' + DATENAME(MONTH, @ArisesTime) + '月' + DATENAME(DAY, @ArisesTime) + '日 ' + DATENAME(HOUR, @ArisesTime) + '时' + DATENAME(MINUTE, @ArisesTime) + '分' + ' 充值了 ' + CAST(@DealAmount AS VARCHAR(8)) + ' 元'
INSERT INTO SmsMessage(ArisesTime, PhysicalCode, CardID, CustomerID, MobilePhone, SendMessage)
VALUES(@ArisesTime, @PhysicalCode, @CardID, @CustomerID, @MobilePhone, @SendMessage)
END
END
--------------------编程问答-------------------- 能不能不用触发器呢！
对于楼上说的用
循环执行object obj = cmd.ExecuteScalar();
然后返回一个ArrayList回来
我在这里如何解决呢
new SqlParameter("@FK_UserID", SqlDbType.Int,4),
new SqlParameter("@FK_RolesID", SqlDbType.Int,4)};
parameters2[0].Value = (int)parameters[6].Value;--------就是这里我如何获得主表自增的值呢
parameters2[1].Value = Convert.ToInt16(strRolesID);
--------------------编程问答-------------------- 个人觉得触发器最简单了。你在cs代码事务里操作反而困难重重哪
--------------------编程问答--------------------
插入数据后还要获取这个记录的自增值？特坑爹的问题。以前大家都是用一个存储过程实现sequence，或者使用uuid了，不用那个自增值了，
--------------------编程问答--------------------
楼主结贴率令人心寒啊
--------------------编程问答--------------------
public string T_WriteXML(string year, string v_name,string value, string note,string note1) //三级栏目新增数据 v_name 获取id用 value 分值{
string name = NewName(v_name, 3, year);
//初始化XML文档操作类
XmlDocument myDoc = new XmlDocument();
//加载XML文件
string xmlPath = System.Web.HttpContext.Current.Server.MapPath("config\\" + year + "\\T_xml.xml");
myDoc.Load(xmlPath);
XmlText text = myDoc.CreateTextNode((note + "|" + note1).ToString());
XmlNode newElem = myDoc.CreateNode("element", "item", "");
newElem.AppendChild(text);
XmlElement xe = (XmlElement)newElem;
xe.SetAttribute("name", name);
xe.SetAttribute("value", value);
xe.SetAttribute("fraction", value);
XmlElement root = myDoc.DocumentElement;
root.AppendChild(newElem);
//保存
myDoc.Save(xmlPath);
return name;
} --------------------编程问答-------------------- public string T_WriteXML(string year, string v_name,string value, string note,string note1) //三级栏目新增数据 v_name 获取id用 value 分值
{
string name = NewName(v_name, 3, year);
//初始化XML文档操作类
XmlDocument myDoc = new XmlDocument();
//加载XML文件
string xmlPath = System.Web.HttpContext.Current.Server.MapPath("config\\" + year + "\\T_xml.xml");
myDoc.Load(xmlPath);
XmlText text = myDoc.CreateTextNode((note + "|" + note1).ToString());
XmlNode newElem = myDoc.CreateNode("element", "item", "");
newElem.AppendChild(text);
XmlElement xe = (XmlElement)newElem;
xe.SetAttribute("name", name);
xe.SetAttribute("value", value);
xe.SetAttribute("fraction", value);
XmlElement root = myDoc.DocumentElement;
root.AppendChild(newElem);
//保存
myDoc.Save(xmlPath);
return name;
} --------------------编程问答-------------------- 插入自动递增的值
public string NewName(string value, int type, string year) ///--2011-8-23--shixin--获取插入的需要自动递增的值///
{
if (type == 1)
{
XmlDocument myDoc = new XmlDocument();
//加载XML文件
string xmlPath = System.Web.HttpContext.Current.Server.MapPath("config\\" + year + "\\F_xml.xml");
myDoc.Load(xmlPath);
XmlNodeList nodeList = myDoc.DocumentElement.ChildNodes;
int MaxName = 0;
foreach (XmlNode node in nodeList)
{
if (Convert.ToInt32(node.Attributes["name"].Value) > MaxName)
MaxName = Convert.ToInt32(node.Attributes["name"].Value);
}
return (MaxName + 1).ToString();
--------------------编程问答-------------------- 学习学习
补充：.NET技术 , C#
