I've written the following C# code to insert rows (that a user enters from a web application) into a SQL database. How does this code look? Is there a better, more efficient way to accomplish the task?
public void UpdateDeviceStatus(string[] deviceId, byte[] status, string userId, string[] remarks, DateTime dateTurnin)
{
if (deviceId.Length != status.Length || status.Length != remarks.Length)
throw new ArgumentOutOfRangeException("Invalid arguments passed to UpdateDeviceStatus: deviceId, status, and remarks must contain the same number of entries.");
if (deviceId.Length == 0)
throw new ArgumentOutOfRangeException("UpdateDeviceStatus expects to update status for at least one deviceId, but an empty array was passed in.");
// Build the SQL statement
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@"INSERT INTO AT_Event_History(deviceId,parentCode,statusCode,remarks,userId,whenEntered) VALUES");
for (int i = 0; i < deviceId.Length; i++)
{
string values = string.Format("({0},0,{1},{2},{3},{4}),",
new string[] { "@deviceId" + i.ToString(), "@statusCode" + i.ToString(), "@remarks" + i.ToString(), "@userId", "@whenEntered" });
sbSql.Append(values);
}
string sql = sbSql.ToString();
sql = sql.TrimEnd(','); // remove the trailing comma ','
Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("AssetTrackConnection");
DbCommand command = db.GetSqlStringCommand(sql);
command.CommandType = CommandType.Text;
command.CommandText = sql;
// Add in parameters
db.AddInParameter(command, "@userId", DbType.AnsiString, userId);
db.AddInParameter(command, "@whenEntered", DbType.Date, dateTurnin);
for (int j = 0; j < deviceId.Length; j++)
{
db.AddInParameter(command, "@deviceId" + j.ToString(), DbType.Guid, new Guid(deviceId[j]));
db.AddInParameter(command, "@statusCode" + j.ToString(), DbType.Byte, status[j]);
db.AddInParameter(command, "@remarks" + j.ToString(), DbType.AnsiString, remarks[j]);
}
// Execute the statement.
db.ExecuteNonQuery(command);
}
As you can see, I am looping to add db parameters to hold the value for each row. I think that there may be a better way to do this, but I don't know how.
