各路英雄。下面的代码来自petshop4.0，有谁能把这段代码生成的sql语句转成存储过程？
public class Order : IOrder { //Static constants
private const string SQL_INSERT_ORDER = "Declare @ID int; Declare @ERR int; INSERT INTO Orders VALUES(@UserId, @Date, @ShipAddress1, @ShipAddress2, @ShipCity, @ShipState, @ShipZip, @ShipCountry, @BillAddress1, @BillAddress2, @BillCity, @BillState, @BillZip, @BillCountry, 'UPS', @Total, @BillFirstName, @BillLastName, @ShipFirstName, @ShipLastName, @AuthorizationNumber, 'US_en'); SELECT @ID=@@IDENTITY; INSERT INTO OrderStatus VALUES(@ID, @ID, GetDate(), 'P'); SELECT @ERR=@@ERROR;";
private const string SQL_INSERT_ITEM = "INSERT INTO LineItem VALUES( ";
private const string SQL_SELECT_ORDER = "SELECT o.OrderDate, o.UserId, o.CardType, o.CreditCard, o.ExprDate, o.BillToFirstName, o.BillToLastName, o.BillAddr1, o.BillAddr2, o.BillCity, o.BillState, BillZip, o.BillCountry, o.ShipToFirstName, o.ShipToLastName, o.ShipAddr1, o.ShipAddr2, o.ShipCity, o.ShipState, o.ShipZip, o.ShipCountry, o.TotalPrice, l.ItemId, l.LineNum, l.Quantity, l.UnitPrice FROM Orders as o, lineitem as l WHERE o.OrderId = @OrderId AND o.orderid = l.orderid";
private const string PARM_USER_ID = "@UserId";
private const string PARM_DATE = "@Date";
private const string PARM_SHIP_ADDRESS1 = "@ShipAddress1";
private const string PARM_SHIP_ADDRESS2 = "@ShipAddress2";
private const string PARM_SHIP_CITY = "@ShipCity";
private const string PARM_SHIP_STATE = "@ShipState";
private const string PARM_SHIP_ZIP = "@ShipZip";
private const string PARM_SHIP_COUNTRY = "@ShipCountry";
private const string PARM_BILL_ADDRESS1 = "@BillAddress1";
private const string PARM_BILL_ADDRESS2 = "@BillAddress2";
private const string PARM_BILL_CITY = "@BillCity";
private const string PARM_BILL_STATE = "@BillState";
private const string PARM_BILL_ZIP = "@BillZip";
private const string PARM_BILL_COUNTRY = "@BillCountry";
private const string PARM_TOTAL = "@Total";
private const string PARM_BILL_FIRST_NAME = "@BillFirstName";
private const string PARM_BILL_LAST_NAME = "@BillLastName";
private const string PARM_SHIP_FIRST_NAME = "@ShipFirstName";
private const string PARM_SHIP_LAST_NAME = "@ShipLastName";
private const string PARM_AUTHORIZATION_NUMBER = "@AuthorizationNumber";
private const string PARM_ORDER_ID = "@OrderId";
private const string PARM_LINE_NUMBER = "@LineNumber";
private const string PARM_ITEM_ID = "@ItemId";
private const string PARM_QUANTITY = "@Quantity";
private const string PARM_PRICE = "@Price"; public void Insert(OrderInfo order) {
StringBuilder strSQL = new StringBuilder(); // Get each commands parameter arrays
SqlParameter[] orderParms = GetOrderParameters(); SqlCommand cmd = new SqlCommand(); // Set up the parameters
orderParms[0].Value = order.UserId;
orderParms[1].Value = order.Date;
orderParms[2].Value = order.ShippingAddress.Address1;
orderParms[3].Value = order.ShippingAddress.Address2;
orderParms[4].Value = order.ShippingAddress.City;
orderParms[5].Value = order.ShippingAddress.State;
orderParms[6].Value = order.ShippingAddress.Zip;
orderParms[7].Value = order.ShippingAddress.Country;
orderParms[8].Value = order.BillingAddress.Address1;
orderParms[9].Value = order.BillingAddress.Address2;
orderParms[10].Value = order.BillingAddress.City;
orderParms[11].Value = order.BillingAddress.State;
orderParms[12].Value = order.BillingAddress.Zip;
orderParms[13].Value = order.BillingAddress.Country;
orderParms[14].Value = order.OrderTotal;
orderParms[15].Value = order.BillingAddress.FirstName;
orderParms[16].Value = order.BillingAddress.LastName;
orderParms[17].Value = order.ShippingAddress.FirstName;
orderParms[18].Value = order.ShippingAddress.LastName;
orderParms[19].Value = order.AuthorizationNumber.Value; foreach (SqlParameter parm in orderParms)
cmd.Parameters.Add(parm); // Create the connection to the database
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringOrderDistributedTransaction)) { // Insert the order status
strSQL.Append(SQL_INSERT_ORDER);
SqlParameter[] itemParms;
// For each line item, insert an orderline record
//构造生成多条Insert语句，一次性送到数据库；
int i = 0;
foreach (LineItemInfo item in order.LineItems) {
strSQL.Append(SQL_INSERT_ITEM).Append(" @ID").Append(", @LineNumber").Append(i).Append(", @ItemId").Append(i).Append(", @Quantity").Append(i).Append(", @Price").Append(i).Append("); SELECT @ERR=@ERR+@@ERROR;"); //Get the cached parameters
itemParms = GetItemParameters(i); itemParms[0].Value = item.Line;
itemParms[1].Value = item.ItemId;
itemParms[2].Value = item.Quantity;
itemParms[3].Value = item.Price;
//Bind each parameter
foreach (SqlParameter parm in itemParms)
cmd.Parameters.Add(parm);
i++;
} conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL.Append("SELECT @ID, @ERR").ToString(); // Read the output of the query, should return error count
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
// Read the returned @ERR
rdr.Read();
// If the error count is not zero throw an exception
if (rdr.GetInt32(1) != 0)
throw new ApplicationException("DATA INTEGRITY ERROR ON ORDER INSERT - ROLLBACK ISSUED");
}
//Clear the parameters
cmd.Parameters.Clear();
}
}
public class Order : IOrder { //Static constants
private const string SQL_INSERT_ORDER = "Declare @ID int; Declare @ERR int; INSERT INTO Orders VALUES(@UserId, @Date, @ShipAddress1, @ShipAddress2, @ShipCity, @ShipState, @ShipZip, @ShipCountry, @BillAddress1, @BillAddress2, @BillCity, @BillState, @BillZip, @BillCountry, 'UPS', @Total, @BillFirstName, @BillLastName, @ShipFirstName, @ShipLastName, @AuthorizationNumber, 'US_en'); SELECT @ID=@@IDENTITY; INSERT INTO OrderStatus VALUES(@ID, @ID, GetDate(), 'P'); SELECT @ERR=@@ERROR;";
private const string SQL_INSERT_ITEM = "INSERT INTO LineItem VALUES( ";
private const string SQL_SELECT_ORDER = "SELECT o.OrderDate, o.UserId, o.CardType, o.CreditCard, o.ExprDate, o.BillToFirstName, o.BillToLastName, o.BillAddr1, o.BillAddr2, o.BillCity, o.BillState, BillZip, o.BillCountry, o.ShipToFirstName, o.ShipToLastName, o.ShipAddr1, o.ShipAddr2, o.ShipCity, o.ShipState, o.ShipZip, o.ShipCountry, o.TotalPrice, l.ItemId, l.LineNum, l.Quantity, l.UnitPrice FROM Orders as o, lineitem as l WHERE o.OrderId = @OrderId AND o.orderid = l.orderid";
private const string PARM_USER_ID = "@UserId";
private const string PARM_DATE = "@Date";
private const string PARM_SHIP_ADDRESS1 = "@ShipAddress1";
private const string PARM_SHIP_ADDRESS2 = "@ShipAddress2";
private const string PARM_SHIP_CITY = "@ShipCity";
private const string PARM_SHIP_STATE = "@ShipState";
private const string PARM_SHIP_ZIP = "@ShipZip";
private const string PARM_SHIP_COUNTRY = "@ShipCountry";
private const string PARM_BILL_ADDRESS1 = "@BillAddress1";
private const string PARM_BILL_ADDRESS2 = "@BillAddress2";
private const string PARM_BILL_CITY = "@BillCity";
private const string PARM_BILL_STATE = "@BillState";
private const string PARM_BILL_ZIP = "@BillZip";
private const string PARM_BILL_COUNTRY = "@BillCountry";
private const string PARM_TOTAL = "@Total";
private const string PARM_BILL_FIRST_NAME = "@BillFirstName";
private const string PARM_BILL_LAST_NAME = "@BillLastName";
private const string PARM_SHIP_FIRST_NAME = "@ShipFirstName";
private const string PARM_SHIP_LAST_NAME = "@ShipLastName";
private const string PARM_AUTHORIZATION_NUMBER = "@AuthorizationNumber";
private const string PARM_ORDER_ID = "@OrderId";
private const string PARM_LINE_NUMBER = "@LineNumber";
private const string PARM_ITEM_ID = "@ItemId";
private const string PARM_QUANTITY = "@Quantity";
private const string PARM_PRICE = "@Price"; public void Insert(OrderInfo order) {
StringBuilder strSQL = new StringBuilder(); // Get each commands parameter arrays
SqlParameter[] orderParms = GetOrderParameters(); SqlCommand cmd = new SqlCommand(); // Set up the parameters
orderParms[0].Value = order.UserId;
orderParms[1].Value = order.Date;
orderParms[2].Value = order.ShippingAddress.Address1;
orderParms[3].Value = order.ShippingAddress.Address2;
orderParms[4].Value = order.ShippingAddress.City;
orderParms[5].Value = order.ShippingAddress.State;
orderParms[6].Value = order.ShippingAddress.Zip;
orderParms[7].Value = order.ShippingAddress.Country;
orderParms[8].Value = order.BillingAddress.Address1;
orderParms[9].Value = order.BillingAddress.Address2;
orderParms[10].Value = order.BillingAddress.City;
orderParms[11].Value = order.BillingAddress.State;
orderParms[12].Value = order.BillingAddress.Zip;
orderParms[13].Value = order.BillingAddress.Country;
orderParms[14].Value = order.OrderTotal;
orderParms[15].Value = order.BillingAddress.FirstName;
orderParms[16].Value = order.BillingAddress.LastName;
orderParms[17].Value = order.ShippingAddress.FirstName;
orderParms[18].Value = order.ShippingAddress.LastName;
orderParms[19].Value = order.AuthorizationNumber.Value; foreach (SqlParameter parm in orderParms)
cmd.Parameters.Add(parm); // Create the connection to the database
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringOrderDistributedTransaction)) { // Insert the order status
strSQL.Append(SQL_INSERT_ORDER);
SqlParameter[] itemParms;
// For each line item, insert an orderline record
//构造生成多条Insert语句，一次性送到数据库；
int i = 0;
foreach (LineItemInfo item in order.LineItems) {
strSQL.Append(SQL_INSERT_ITEM).Append(" @ID").Append(", @LineNumber").Append(i).Append(", @ItemId").Append(i).Append(", @Quantity").Append(i).Append(", @Price").Append(i).Append("); SELECT @ERR=@ERR+@@ERROR;"); //Get the cached parameters
itemParms = GetItemParameters(i); itemParms[0].Value = item.Line;
itemParms[1].Value = item.ItemId;
itemParms[2].Value = item.Quantity;
itemParms[3].Value = item.Price;
//Bind each parameter
foreach (SqlParameter parm in itemParms)
cmd.Parameters.Add(parm);
i++;
} conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL.Append("SELECT @ID, @ERR").ToString(); // Read the output of the query, should return error count
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
// Read the returned @ERR
rdr.Read();
// If the error count is not zero throw an exception
if (rdr.GetInt32(1) != 0)
throw new ApplicationException("DATA INTEGRITY ERROR ON ORDER INSERT - ROLLBACK ISSUED");
}
//Clear the parameters
cmd.Parameters.Clear();
}
}
解决方案 »
- 如果实现在线看电影,听音乐的功能.....
- 在一个WebService中是否能访问控制器
- C#从一个页面到另一个页面
- 请问高手，ReadEntityBody这个方法倒底是从哪里读取http请求的？[仅剩的50分]
- treeview显示树形表时，节点重复出现两次（在线等）
- 在线调查系统的取值问题?or你有更好的思路?
- 各位高手进业帮帮忙,我明天要用的,谢谢! ^_^
- datalist嵌套，子控件的内容不显示，断点调试，子控件里面有内容，可就是不显示，请高手帮忙！！
- asp编程：ADODB.Recordset 错误 '800a0bb9'
- 谁有crystal reports的电子书。
- 加密\解密---封装的动态链接库[dll]
- 问个关于url重写的问题
strSQL.Append(SQL_INSERT_ITEM).Append(" @ID").Append(", @LineNumber").Append(i).Append(", @ItemId").Append(i).Append(", @Quantity").Append(i).Append(", @Price").Append(i).Append("); SELECT @ERR=@ERR+@@ERROR;");主要是存储过程都是手动一个一个的给变量赋值，这个也是，
只是看起来不好看罢了。