Get @@IDENTITY Value,that's OK!
而且最好能使用一个事务来控制
而且最好能使用一个事务来控制
解决方案 »
- 请问怎么才能让判断循环起来啊 郁闷
- 淘宝网修饰店铺添加模块功能的实现
- ASP.NET2.0中,为何选择一个控件后,属性栏中没有自动更新为被选择的控件的属性
- 地址栏只显示IP地址(所有的页面)
- ==== [求助]在ASP.NET中使用模态网页窗体时出现的问题? ====
- 大家有没有遇到过DataGrid导出Excel后,DataGrid不能用的情况
- 发现新网asp.net服务存在漏洞
- 无法将类型“string”隐式转换“PeopleManage.Model.Users
- sql查询问题~,请大家进来帮忙啦
- 关于模式对话框的问题。
- 标计符?
- 如何把在数据库搜到的数据添到事先设定好的相应的word表格当中!!请教应该如何实现!!!
//主表
CustomerDataset.CUSTRow row = customerDS.CUST.NewCUSTRow(); //初始值
row.CUST_ID = "";//key
row.BALANCE = 0;
row.CREATED_DATE = DateTime.Now;
row.CUST_STATUS = "E"; row.CUST_TYPE = dropCustType.SelectedItem.Value;
row.DOMAIN_NAME = txtDomainName.Text;
row.CUST_NAME = txtCustName.Text;
row.CUST_IDENTITY = txtCustIdentity.Text;
row.CUST_ADDRESS = txtCustAddress.Text;
row.CONTACT_NAME = txtContactName.Text;
row.CONTACT_PHONE = txtContactPhone.Text;
row.BANK_ID = dropBank.SelectedItem.Value;
row.BANK_ACCOUNT = txtAccount.Text;
customerDS.CUST.AddCUSTRow(row);
Session["cnc.Hoten.Boss.CustinfoWeb.CustReg.CustReg.CustDS"]=customerDS;
//从
CustomerDataset customerDS =(CustomerDataset)Session["cnc.Hoten.Boss.CustinfoWeb.CustReg.CustReg.CustDS"];
string userID=txtUserID.Text;
string commonName=txtCommonName.Text;
string userPassword=txtUserPassword.Text;
string idValue="";
GetIdentity("CUST_ID",out idValue);
CustomerDataset.CUSTRow cRow = (CustomerDataset.CUSTRow)customerDS.CUST.Rows[0];
cRow.CUST_ID = idValue;
CustomerDataset.USERSRow row = customerDS.USERS.NewUSERSRow();
row.CUST_ID=idValue;
row.USER_ID=userID;
row.USER_PASSWORD=userPassword;
row.COMMON_NAME=commonName;
customerDS.USERS.AddUSERSRow(row);
//主从一起更新
CreateNewCustUser(ref customerDS);
新插入的纪录identity流水号肯定是最大的,你可以用select max(identity字段名)...来取最大值,肯定就是新加的纪录。
string GetIdentity(string idName,out string idValue)
{
idValue="";
DbComponent com=new DbComponent();
OleDbTransaction trans=null;
try
{
com.dbCnc.Open();
trans=com.dbCnc.BeginTransaction();
OleDbCommand oleDbCommand1 = new OleDbCommand();
oleDbCommand1.Connection = com.dbCnc;
oleDbCommand1.Transaction = trans;
oleDbCommand1.CommandText = "UPDATE [IDENTITY] SET ID_VALUE = CONVERT(VARCHAR,CONVERT(BIGINT,ID_VALUE) + 1) WHERE ID_NAME = ? ; SELECT ID_VALUE FROM [IDENTITY] WHERE ID_NAME = ?";
oleDbCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID_NAME", System.Data.OleDb.OleDbType.VarChar, 50, "ID_NAME"));
oleDbCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID_NAME2", System.Data.OleDb.OleDbType.VarChar, 50, "ID_NAME2"));
oleDbCommand1.Parameters["ID_NAME"].Value = idName;
oleDbCommand1.Parameters["ID_NAME2"].Value = idName; OleDbDataReader dr = oleDbCommand1.ExecuteReader();
if(dr.Read())
{
Int64 icust_id = Int64.Parse(dr.GetString(0)) - 1;
idValue = string.Format("{0:d10}", icust_id);
dr.Close();
}
else
{
dr.Close(); OleDbCommand oleDbCommand2 = new OleDbCommand();
oleDbCommand2.Connection = com.dbCnc;
oleDbCommand2.Transaction = trans; oleDbCommand2.CommandText = "INSERT INTO [IDENTITY](ID_NAME,ID_VALUE) VALUES(?,?); INSERT INTO [IDENTITY](ID_NAME,ID_VALUE) VALUES(?,?)";
oleDbCommand2.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID_NAME", System.Data.OleDb.OleDbType.VarChar, 50, "ID_NAME"));
oleDbCommand2.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID_VALUE", System.Data.OleDb.OleDbType.VarChar, 100, "ID_VALUE"));
oleDbCommand2.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID_NAME2", System.Data.OleDb.OleDbType.VarChar, 50, "ID_NAME2"));
oleDbCommand2.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID_VALUE2", System.Data.OleDb.OleDbType.VarChar, 100, "ID_VALUE2"));
oleDbCommand2.Parameters["ID_NAME"].Value = idName;
oleDbCommand2.Parameters["ID_VALUE"].Value = "0000000001";
oleDbCommand2.Parameters["ID_NAME2"].Value = idName;
oleDbCommand2.Parameters["ID_VALUE2"].Value = "0000000002"; idValue = "0000000001";
oleDbCommand2.ExecuteNonQuery();
}
trans.Commit();
}
catch(Exception e)
{
if(trans!=null)
{
trans.Rollback();
}
return e.Message;
}
finally
{
com.dbCnc.Close();
}
return "OK";
}
string CreateNewCustUser(ref CustomerDataset customerDS)
{
DbComponent com = new DbComponent();
OleDbTransaction trans=null;
try
{
com.dbCnc.Open();
//使用事务处理
trans=com.dbCnc.BeginTransaction();
com.daCust.InsertCommand.Transaction=trans;
com.daCust.UpdateCommand.Transaction=trans;
com.daUser.InsertCommand.Transaction=trans;
com.daUser.UpdateCommand.Transaction=trans;
com.daCust.Update(customerDS.CUST);
com.daUser.Update(customerDS.USERS);
trans.Commit();
}
catch (OleDbException e)
{
//如果未能实现两个表同时修改
if(trans!=null)
{
trans.Rollback();
// customerDS.Clear();
}
//如果存在相同主键的记录,返回"EXIST"
if (e.ErrorCode == -2147217873)
return "EXIST";
else
return e.Message;
}
catch(Exception e)
{
return e.Message;
}
finally
{
com.dbCnc.Close();
}
return "OK";
}
BEGIN TRAN T1
Insert into fcsUserDivision (IsObsolete) values (@IsObsolete)
select @UserDivisionalID = max(UserDivisionalID) from fcsUserDivision
Insert into fcsUserDivisionDetails (UserDivisionalID, LangCode, UserDivision, UserDepartment, UserSection) values (@UserDivisionalID, @LangCode, @UserDivision, @UserDepartment, @UserSection)
COMMIT TRAN T1
fcsUserDivisionDetails是从表
UserDivisionalID 是流水号主键
你可以看看是不是有用!
你的办法也不行,你是在添加后,又把最大值取出来,这时有可能最大值不是你添加过了记录,因为可能有其他用户又往表中添加过新的记录了,除非你锁定数据库。
你的办法也需要锁定数据库,不然也会导致加入相同值的记录
BEGIN TRAN T1
INSERT INTO table(...) VALUES (...)
SELECT @@IDENTITY AS 'Identity'
COMMIT TRAN T1
你可以把Identity作为参数返回就可以了