public IActionResult DapperTransactionalInsert([FromBody] ProductsModel productsModel, int insert)
{
using (IDbConnection db = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
if (db.State != ConnectionState.Open)
{
db.Open();
}
//Transaction islemi yapiyorum Products ve Order Detail tablosunu kullanarak.
using (var transaction = db.BeginTransaction())
{
string sql = @"INSERT INTO dbo.products (ProductName, ProductID)
Values (@ProductName, @ProductID);";
var result = db.Execute(sql, new
{
ProductName = "Deneme",
ProductID = 80,
}, transaction);
throw new ArgumentNullException();
OrdersDetailModel ordersDetailModel = new OrdersDetailModel()
{
OrderId = 80,
ProductId = 80,
Quantity = 1500
};
sql = @"Insert into [products].[Order Details] (OrderId, ProductId, Quantity)
Values (@OrderId, @ProductId, @Quantity)";
result = db.Execute(sql, ordersDetailModel, transaction);
transaction.Commit();
}
}
return(Ok());
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(OrdersDetailModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update tb_OrdersDetail set ");
strSql.Append("OrdersID=@OrdersID,");
strSql.Append("ProductId=@ProductId,");
strSql.Append("Quantity=@Quantity,");
strSql.Append("States=@States");
strSql.Append(" where DetailID=@DetailID");
SqlParameter[] parameters =
{
new SqlParameter("@OrdersID", SqlDbType.Int, 4),
new SqlParameter("@ProductId", SqlDbType.Int, 4),
new SqlParameter("@Quantity", SqlDbType.Int, 4),
new SqlParameter("@States", SqlDbType.Int, 4),
new SqlParameter("@DetailID", SqlDbType.Int, 4)
};
parameters[0].Value = model.OrdersID;
parameters[1].Value = model.ProductId;
parameters[2].Value = model.Quantity;
parameters[3].Value = model.States;
parameters[4].Value = model.DetailID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public OrdersDetailModel DataRowToModel(DataRow row)
{
OrdersDetailModel model = new OrdersDetailModel();
if (row != null)
{
if (row["DetailID"] != null && row["DetailID"].ToString() != "")
{
model.DetailID = int.Parse(row["DetailID"].ToString());
}
if (row["OrdersID"] != null && row["OrdersID"].ToString() != "")
{
model.OrdersID = int.Parse(row["OrdersID"].ToString());
}
if (row["ProductId"] != null && row["ProductId"].ToString() != "")
{
model.ProductId = int.Parse(row["ProductId"].ToString());
}
if (row["Quantity"] != null && row["Quantity"].ToString() != "")
{
model.Quantity = int.Parse(row["Quantity"].ToString());
}
if (row["States"] != null && row["States"].ToString() != "")
{
model.States = int.Parse(row["States"].ToString());
}
}
return(model);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(OrdersDetailModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into tb_OrdersDetail(");
strSql.Append("OrdersID,ProductId,Quantity,States)");
strSql.Append(" values (");
strSql.Append("@OrdersID,@ProductId,@Quantity,@States)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@OrdersID", SqlDbType.Int, 4),
new SqlParameter("@ProductId", SqlDbType.Int, 4),
new SqlParameter("@Quantity", SqlDbType.Int, 4),
new SqlParameter("@States", SqlDbType.Int, 4)
};
parameters[0].Value = model.OrdersID;
parameters[1].Value = model.ProductId;
parameters[2].Value = model.Quantity;
parameters[3].Value = model.States;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public OrdersDetailModel GetModel(int DetailID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 DetailID,OrdersID,ProductId,Quantity,States from tb_OrdersDetail ");
strSql.Append(" where DetailID=@DetailID");
SqlParameter[] parameters =
{
new SqlParameter("@DetailID", SqlDbType.Int, 4)
};
parameters[0].Value = DetailID;
OrdersDetailModel model = new OrdersDetailModel();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
