110,571
社区成员
发帖
与我相关
我的任务
分享C# 调用存储过程，添加主从表的问题。。。
现在程序是调用了金蝶提供的存储过程，但是金蝶的主从表是分开了两个存储过程实现的，所以现在就会出现问题。有的时候主表添加成功了，从表没添加成功。也不知道是并发了还是其他原因引起的错误。查了下本来想用 SqlTransaction ，也没实现。各位大神能否给提供点思路、代码任何idea ，不胜感激！！！
...全文
请发表友善的回复…
发表回复
a12345_asdfg 2017-05-07
- 打赏
- 举报
各位回复的有理有据，特别感谢，大家提出的意见和建议及方法，就是8楼说的那个意思，我自己后来好好研究了下已经实现了，一开始比较着急所以。。。再次感谢大家
xdashewan 2017-05-04
- 打赏
- 举报
你至少也得把你的transaction赋值给你的command吧，cm.Transaction = transaction;，问题是你后面没有使用command，用的是RunProcedure，这得你自己看了
a12345_asdfg 2017-05-04
- 打赏
- 举报
存储过程多记录可以先插进临时表，不用存储过程用SqlTransaction效果应该一样[/quote]
SqlTransaction 的例子有吗？能不能给看看这段调用的代码有什么问题吗，报错了也不能回滚
public ReturnResponse InsertOrder_Customer(InvoceOrder model)
{
ReturnResponse result = new ReturnResponse();
//开启事务
System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(connectionString);//这行是仿照我说的那个链接的
System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();//这行是仿照我说的那个链接的
cm.Connection = cnn;//这行是仿照我说的那个链接的
cnn.Open();//这行是仿照我说的那个链接的
System.Data.SqlClient.SqlTransaction trans = cnn.BeginTransaction(); //这行是仿照我说的那个链接的
try
{
var ordersn = CreateKingdeeSn(model.FTranType, model.FROB);
SqlParameter parReturn = new SqlParameter("@return", SqlDbType.Decimal);
parReturn.Direction = ParameterDirection.ReturnValue;
SqlParameter TotalPrice = new SqlParameter("@hanshuizongjine", SqlDbType.Decimal);
TotalPrice.Precision = 28;
TotalPrice.Scale = 10;
SqlParameter[] parameters = {
new SqlParameter("@FBillNo", SqlDbType.VarChar,100),
new SqlParameter("@Coustemor", SqlDbType.VarChar,50),
new SqlParameter("@CoustemorReceiveInfor", SqlDbType.VarChar,50),
new SqlParameter("@FDept", SqlDbType.VarChar,50),
new SqlParameter("@FEmp", SqlDbType.VarChar,50),
new SqlParameter("@FTranType", SqlDbType.VarChar,50),
new SqlParameter("@yewuriqi", SqlDbType.VarChar,50),
new SqlParameter("@supplier1", SqlDbType.VarChar,50),
new SqlParameter("@FROB", SqlDbType.VarChar,50),
new SqlParameter("@FFManager", SqlDbType.VarChar,50),
new SqlParameter("@FSManager", SqlDbType.VarChar,50),
new SqlParameter("@Rrmark", SqlDbType.VarChar,50),
new SqlParameter("@dinghuoren", SqlDbType.VarChar,50),
new SqlParameter("@dianhua", SqlDbType.VarChar,50),
new SqlParameter("@iskaipiao", SqlDbType.VarChar,50),
new SqlParameter("@caiwushunxuhao", SqlDbType.VarChar,50),
TotalPrice,
parReturn
};
parameters[0].Value = model.FBillNo;
parameters[1].Value = model.Customer;
parameters[2].Value = model.CoustemorReceiveInfor;
parameters[3].Value = model.FDept;
parameters[4].Value = model.FEmp;
parameters[5].Value = (int)KdOrderType.销售出库;// model.FTranType;
parameters[6].Value = model.BusinessDate;
parameters[7].Value = ""; //供应商
parameters[8].Value = model.FROB;
parameters[9].Value = model.FFManager;
parameters[10].Value = model.FSManager;
parameters[11].Value = model.Rrmark;
parameters[12].Value = model.dinghuoren;
parameters[13].Value = model.dianhua;
parameters[14].Value = model.iskaipiao;
parameters[15].Value = ordersn;// CreateKingdeeSn(model.FTranType, model.FROB);// model.caiwushunxuhao;
parameters[16].Value = model.totalprice;
DataSet ds = RunProcedure("AAInsertChuRuKudan", parameters, "icstockbill", 3000);
result = getErrorInfo((ErrorCode)Convert.ToInt32(parReturn.Value));
//日志
addLog(model.FBillNo + "|" + ordersn, model.FFManager, model.FTranType, result.ErrorCode, result.Msg, result.Success);
if (!result.Success)
{
return result;
}
//从表
foreach (var m in model.orderList)
{
//goods_sn = m.FNumber;
SqlParameter parReturn2 = new SqlParameter("@return", SqlDbType.Decimal);
parReturn2.Direction = ParameterDirection.ReturnValue;
SqlParameter FQty = new SqlParameter("@FQty", SqlDbType.Decimal);
FQty.Precision = 28;
FQty.Scale = 10;
SqlParameter FPrice = new SqlParameter("@FPrice", SqlDbType.Decimal);
FPrice.Precision = 28;
FPrice.Scale = 10;
SqlParameter FAmount = new SqlParameter("@FAmount", SqlDbType.Decimal);
FAmount.Precision = 28;
FAmount.Scale = 10;
SqlParameter TaxAmount = new SqlParameter("@Shuie", SqlDbType.Decimal);
TaxAmount.Precision = 28;
TaxAmount.Scale = 2;
SqlParameter[] parameters2 = {
new SqlParameter("@FNumber", SqlDbType.VarChar,100),
new SqlParameter("@UnitName", SqlDbType.VarChar,100),
FQty,
FPrice,
FAmount,
new SqlParameter("@FDCStock", SqlDbType.VarChar,50),
new SqlParameter("@FSCStock", SqlDbType.VarChar,50),
new SqlParameter("@FBillNo", SqlDbType.VarChar,50),
new SqlParameter("@Pihao", SqlDbType.VarChar,50),
new SqlParameter("@CaigouDate", SqlDbType.VarChar,50),
new SqlParameter("@Baozhiqi", SqlDbType.VarChar,50),
new SqlParameter("@Youxiaoqi", SqlDbType.VarChar,50),
new SqlParameter("@Dingdanhao", SqlDbType.VarChar,50),
new SqlParameter("@Rrmark", SqlDbType.VarChar,50),
TaxAmount,
parReturn2
};
parameters[0].Value = m.FNumber;
parameters[1].Value = m.UnitName.Trim();
parameters[2].Value = m.FQty;
parameters[3].Value = m.FPrice;
parameters[4].Value = m.FAmount;
parameters[5].Value = string.IsNullOrEmpty(m.FDCStock) ? "" : m.FDCStock.ToUpper(); //收货仓库
parameters[6].Value = string.IsNullOrEmpty(m.FSCStockID) ? "" : m.FSCStockID.ToUpper(); //发出仓库
parameters[7].Value = m.FBillNo;
parameters[8].Value = "";
parameters[9].Value = "";
parameters[10].Value = ""; // 保质期
parameters[11].Value = ""; //有效期
parameters[12].Value = m.orderNo;
parameters[13].Value = m.Rrmark;
parameters[14].Value = 0; //税额
//allgoods_sn += m.FNumber + ",";
DataSet ds2 = RunProcedure("AAInsertChuRuKudanEntry", parameters, "entry", 3000);
}
trans.Commit(); //提交事务
result.Success = true;
result.ErrorCode = (int)ErrorCode.成功;
result.Msg = ordersn;// "操作成功！";
return result;
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
result.Success = false;
result.ErrorCode = (int)ErrorCode.数据操作异常;
var Msg = "数据操作异常：" + ex.Message;
result.Msg = "系统正忙，请稍后再试";
addLog(model.FBillNo, model.FFManager, model.FTranType, result.ErrorCode, Msg, result.Success);
//判断主表是否已经存在数据，存在则删除
if (IsExistOrder(model.FBillNo, (int)KdOrderType.销售出库))
{
DeleteOrder(model.FBillNo);
}
return result;
}
}
堂风 2017-05-04
- 打赏
- 举报
static void TransLate()
{
string connString = "";
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
try
{
///第一个
SqlCommand cmd1 = new SqlCommand();
SqlParameter[] parameters1 =
{
new SqlParameter("@FBillNo", SqlDbType.VarChar,100),
new SqlParameter("@caiwushunxuhao", SqlDbType.VarChar,50)
};
parameters1[0].Value = "";
parameters1[16].Value = "";
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.CommandText = "";
cmd1.ExecuteNonQuery();
///第二个
SqlCommand cmd2 = new SqlCommand();
SqlParameter[] parameters2 =
{
new SqlParameter("@FBillNo", SqlDbType.VarChar,100),
new SqlParameter("@caiwushunxuhao", SqlDbType.VarChar,50)
};
parameters2[0].Value = "";
parameters2[16].Value = "";
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "";
cmd2.ExecuteNonQuery();
///第N个
///.........
///添加
cmd.Parameters.Add(cmd1);
cmd.Parameters.Add(cmd2);
///事务提交
tran.Commit();
}
catch (Exception ex)
{
///任何一个异常都回滚
tran.Rollback();
}
finally
{
///释放
tran.Dispose();
conn.Close();
}
}
}
wang_peng_yl 2017-05-04
- 打赏
- 举报
建议你自己行做个小小的程序，针对 BeginTransaction这块好好学习一下
等最基本的明白了，搞这个都不用在csdn上问了，自己全搞定
你现在弄代码一堆，出问题都不知道在哪找
很明显，你的cm跟本没用上
xdashewan 2017-05-03
- 打赏
- 举报
存储过程多记录可以先插进临时表，不用存储过程用SqlTransaction效果应该一样
a12345_asdfg 2017-05-03
- 打赏
- 举报
如果自己再写一个存储过程，参数如何设置？？从表是有多条数据的，求教。 SqlTransaction 的话，我写的直接就执行了
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}
正怒月神 2017-05-03
- 打赏
- 举报
1楼给的主意没毛病
xdashewan 2017-05-03
- 打赏
- 举报
自己再写个存储过程，用事务包装调用的两个，错了就一起回滚。SqlTransaction应该也是同样效果
