Having a MAJOR brain fart here...

Guys I'm sorry to be asking such a routine question...

I'm having trouble figuring out how to make this function dynamic enough to handle multiple insert statements.

1public int Add()23{45string SQL;67SQL ="INSERT INTO [BuildingInterior] (PropertyID, CeilingHeight, " +89"LoadingDocks, PassengerElevators, FreightElevators, PassengerEscalators, " +1011"FireSprinklersID, SecurityCameras, SmokeDetection, FireAlarms, " +1213"GasDetection, SecureAccess, HeatTypeID, AirConditioningID, " +1415"AirExchange, InternetAccessID, InteriorDescription) " +1617"VALUES ( @.PropertyID, @.CeilingHeight, " +1819"@.LoadingDocks, @.PassengerElevators, @.FreightElevators, @.PassengerEscalators, " +2021"@.FireSprinklersID, @.SecurityCameras, @.SmokeDetection, @.FireAlarms, " +2223"@.GasDetection, @.SecureAccess, @.HeatTypeID, @.AirConditioningID, " +2425"@.AirExchange, @.InternetAccessID, @.InteriorDescription)";2627PropertyDB myConnection =new PropertyDB();2829SqlConnection conn = myConnection.GetOpenConnection();3031SqlCommand cmd =new SqlCommand(SQL, conn);3233cmd.Parameters.Add("@.PropertyID", SqlDbType.Int).Value = PropertyID;3435cmd.Parameters.Add("@.CeilingHeight", SqlDbType.NVarChar, 50).Value = CeilingHeight;3637cmd.Parameters.Add("@.LoadingDocks", SqlDbType.NVarChar, 50).Value = LoadingDocks;3839cmd.Parameters.Add("@.PassengerElevators", SqlDbType.NVarChar, 50).Value = PassengerElevators;4041cmd.Parameters.Add("@.FreightElevators", SqlDbType.NVarChar, 50).Value = FreightElevators;4243cmd.Parameters.Add("@.PassengerEscalators", SqlDbType.NVarChar, 50).Value = PassengerEscalators;4445cmd.Parameters.Add("@.FireSprinklersID", SqlDbType.Int).Value = FireSprinklersID;4647cmd.Parameters.Add("@.SecurityCameras", SqlDbType.NVarChar, 50).Value = SecurityCameras;4849cmd.Parameters.Add("@.SecurityAlarms", SqlDbType.NVarChar, 50).Value = SecurityAlarms;5051cmd.Parameters.Add("@.SmokeDetection", SqlDbType.NVarChar, 50).Value = SmokeDetection;5253cmd.Parameters.Add("@.FireAlarms", SqlDbType.NVarChar, 50).Value = FireAlarms;5455cmd.Parameters.Add("@.GasDetection", SqlDbType.NVarChar, 50).Value = GasDetection;5657cmd.Parameters.Add("@.SecureAccess", SqlDbType.NVarChar, 50).Value = SecureAccess;5859cmd.Parameters.Add("@.HeatTypeID", SqlDbType.Int).Value = HeatTypeID;6061cmd.Parameters.Add("@.AirConditioningID", SqlDbType.Int).Value = AirConditioningID;6263cmd.Parameters.Add("@.AirExchange", SqlDbType.NVarChar, 50).Value = AirExchange;6465cmd.Parameters.Add("@.InternetAccessID", SqlDbType.Int).Value = InternetAccessID;6667cmd.Parameters.Add("@.InteriorDescription", SqlDbType.NVarChar, 50).Value = InteriorDescription;6869cmd.ExecuteNonQuery();7071cmd.CommandText ="SELECT @.@.IDENTITY";7273this.BuildingInteriorID = Int32.Parse(cmd.ExecuteScalar().ToString());7475conn.Close();7677return this.BuildingInteriorID;7879}80

Should I just pass an array of column names and use the AddWithValues SqlCommand method while looping through the array?

Any comments are greatly welcomed.

Hi eterry,

As far as I can see there is no better way to assign value by iterating through each column like this.

But I think there is one thing that you can improve in your code. You can put SELECT SCOPE_IDENTITY() (use SCOPE_IDENTITY() instead of @.@.IDENTITY in SQL Server)at the end of the INSERT statement. Seperate them with a ";", like

INSERT INTO ......; SELECT SCOPE_IDENTITY()

Then you can use ExecuteScalar to have the 2 statement executed at once. This will have 2 advantages.

1. Save a roundtrip to the server and gain better performance.
2. Prevent the concurrency issues. In your code, if 2 users do this together, there is possibility that they will get wrong identity if one's execution is interrupted by the other.

HTH. If this does not answer you question, please feel free to mark the post as Not Answered and post your reply. Thanks!

|||

Thanks for the tip Kevin.

I was hoping that there would be a better way of doing, but it is what it is.