如何用C#将表单数组数据同步存入双数据库表？

Hey there! Let's get your array data saved into the Prop_detail table while inserting the main form data into tdcProduct1. The key points here are ensuring data consistency with transactions and properly parameterizing your queries to avoid SQL injection. Here's how to modify your existing SaveFrmDetails method:

Step-by-Step Explanation & Modified Code #

1. Use a Database Transaction: This ensures that if either the main table insert or any detail table insert fails, all changes are rolled back—no partial data saves!
2. Loop Through Your WireDimDetails Array: For each item in the array, construct the Tdc_property value (combining sizeMin, sizeMax, tolMin, tolMax as per your table structure) and insert it into Prop_detail.
3. Keep Queries Parameterized: Never concatenate values directly into SQL strings—use parameters to stay safe.

Here's the updated C# code:

[WebMethod]
public static void SaveFrmDetails(User user)
{
    string connectionString = ConfigurationManager.ConnectionStrings["conndbprodnew"].ConnectionString;
    using (OracleConnection con = new OracleConnection(connectionString))
    {
        con.Open();
        // Start a transaction to ensure atomicity
        using (OracleTransaction transaction = con.BeginTransaction())
        {
            try
            {
                // 1. Insert into tdcProduct1 table
                using (OracleCommand cmdMain = new OracleCommand(
                    "INSERT INTO TDC_PRODUCT1(PRODUCT_ID, TDC_NO, REVISION) VALUES (:PRODUCT_ID, :TDC_NO, :REVISION)",
                    con, transaction))
                {
                    cmdMain.CommandType = CommandType.Text;
                    cmdMain.Parameters.AddWithValue(":PRODUCT_ID", user.PRODUCT_ID);
                    cmdMain.Parameters.AddWithValue(":TDC_NO", user.TDC_NO);
                    cmdMain.Parameters.AddWithValue(":REVISION", user.REVISION);
                    cmdMain.ExecuteNonQuery();
                }

                // 2. Insert each WireDimDetail into Prop_detail table
                string insertDetailSql = "INSERT INTO Prop_detail(Tdc_no, Tdc_property) VALUES (:Tdc_no, :Tdc_property)";
                using (OracleCommand cmdDetail = new OracleCommand(insertDetailSql, con, transaction))
                {
                    // Add reusable parameters to avoid re-creating them in the loop
                    cmdDetail.Parameters.Add(":Tdc_no", OracleDbType.Varchar2);
                    cmdDetail.Parameters.Add(":Tdc_property", OracleDbType.Varchar2);

                    foreach (WireDimDetail wireDimDetail in user.WireDimDetails)
                    {
                        // Combine the four values into a single string for Tdc_property (adjust delimiter if needed)
                        string tdcProperty = $"{wireDimDetail.SizeMin}|{wireDimDetail.SizeMax}|{wireDimDetail.TolMin}|{wireDimDetail.TolMax}";
                        
                        // Set parameter values
                        cmdDetail.Parameters[":Tdc_no"].Value = user.TDC_NO;
                        cmdDetail.Parameters[":Tdc_property"].Value = tdcProperty;
                        
                        // Execute the insert for this detail item
                        cmdDetail.ExecuteNonQuery();
                    }
                }

                // Commit the transaction only if all inserts succeed
                transaction.Commit();
            }
            catch (Exception ex)
            {
                // Rollback on any error
                transaction.Rollback();
                // Re-throw the error so the frontend gets it
                throw new Exception("Error saving data: " + ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
    }
}

Key Notes: #

• Transaction Handling: The OracleTransaction wraps both inserts—if anything goes wrong, Rollback() ensures no data is left in either table.
• Tdc_property Format: I used a pipe (|) as a delimiter to combine the four size/tolerance values. You can change this to another separator (like comma, semicolon) if that works better for your future data retrieval needs.
• Reusable Parameters: For the detail insert, we create parameters once and reuse them in the loop—this is more efficient than creating new parameters each time.
• Error Handling: The try-catch block catches any exceptions, rolls back the transaction, and sends an error message to the frontend.

Quick Check on Your Models: #

Make sure your User class properly includes the WireDimDetails list, like this (in case you haven't defined it yet):

public class User
{
    public int PRODUCT_ID { get; set; }
    public string TDC_NO { get; set; }
    public string REVISION { get; set; }
    public List<WireDimDetail> WireDimDetails { get; set; }
}

public class WireDimDetail
{
    public string SizeMin { get; set; }
    public string SizeMax { get; set; }
    public string TolMin { get; set; }
    public string TolMax { get; set; }
}

That should do it! Your frontend code looks good—it's already sending the complete user object including the WireDimDetails array, so no changes needed there.

内容的提问来源于stack exchange，提问作者hari