private string sqlAddOrderDetails(clsUploadData.Data objData)
{
string strSQL = "Insert into OrderDetails(Orders_Number, PartID,Description,Qty)";
strSQL = strSQL + " Values('" + objData.OrderNumber + "','" + objData.ItemNumber + "','" + objData.ItemDescription + "','" + objData.Qty + "')";
return(strSQL);
}
private string sqlAddOrder(clsUploadData.Data objData)
{
string strSQL = "Insert into Orders(Number,Email,Telephone,ShipMethod,ShippingAccount,AES, SubTotal)";
strSQL = strSQL + " Values('" + objData.OrderNumber + "','" + objData.CustEmailAddr + "','" + objData.TelephoneNbr + "','" + objData.ShipMethod + "','" + objData.ShippingAccountNo + "','" + objData.aesNumber + "','" + objData.Subtotal + "')";
return(strSQL);
}
private string sqlAddBillTo(clsUploadData.Data objData)
{
string strSQL = "Insert into BillTo(Orders_Number,Contact,Address1,Address2,Address3,City,State,Zip,Country)";
strSQL = strSQL + " Values('" + objData.OrderNumber + "','" + objData.BillToContact + "','" + objData.BillToLine1 + "','" + objData.BillToLine2 + "','" + objData.BillToLine3 + "'";
strSQL = strSQL + ",'" + objData.BillToCity + "','" + objData.BillToState + "','" + objData.BillToPostalCode + "'";
strSQL = strSQL + ",'" + objData.BillToCountry + "')";
return(strSQL);
}
/* both parameters should be required and placed in a constructor. Preferably, two required properties */
public int addToDB(clsUploadData.Data objData)
{
int intRows = 0;
SQLiteCommand sqlCommand = new SQLiteCommand(sqlConnection);
SQLiteTransaction objTransaction;
try
{
if (strOrderNumber != objData.OrderNumber)
{
objTransaction = sqlConnection.BeginTransaction();
//Adding Order Data
sqlCommand.CommandText = sqlAddOrder(objData);
intRows = sqlCommand.ExecuteNonQuery();
//Adding ShipTo Info
sqlCommand.CommandText = sqlAddShipTo(objData);
intRows = sqlCommand.ExecuteNonQuery();
//Adding BillTo Info
sqlCommand.CommandText = sqlAddBillTo(objData);
intRows = sqlCommand.ExecuteNonQuery();
//Adding OrderDetails Info
sqlCommand.CommandText = sqlAddOrderDetails(objData);
intRows = sqlCommand.ExecuteNonQuery();
objTransaction.Commit();
strOrderNumber = objData.OrderNumber;
}
else
{
sqlCommand.CommandText = sqlAddOrderDetails(objData);
sqlCommand.ExecuteNonQuery();
}
}
catch (SQLiteException E)
{
switch (E.Message)
{
case "Abort due to constraint violation\r\ncolumn Number is not unique":
strError = "Order has already been added to DB";
break;
default:
strError = E.Message;
break;
}
}
finally
{
sqlCommand.Dispose();
}
return(intRows);
}