private void InsertData(Lead request) { // define INSERT query with parameters string insertLeadQuery = GetInsertLeadSQL(); string insertLeadParamQuery = GetInsertLeadParamSQL(); request.PopulateLeadRequestParmeterDictionary(); // create connection and command using (SqlConnection cn = new SqlConnection(_connectionString)) using (SqlCommand cmd = new SqlCommand(insertLeadQuery, cn)) { // define parameters and their values cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 255).Value = request.FirstName; cmd.Parameters.Add("@Lastname", SqlDbType.VarChar, 255).Value = request.Surname; cmd.Parameters.Add("@LeadId", SqlDbType.BigInt).Value = request.LeadId; cmd.Parameters.Add("@TrackingCode", SqlDbType.VarChar, 20).Value = "N/A"; cmd.Parameters.Add("@Email", SqlDbType.VarChar, 150).Value = request.EmailAddress; cmd.Parameters.Add("@ReceivedDateTime", SqlDbType.DateTime).Value = DateTime.Now; cmd.Parameters.Add("@ContactNumber", SqlDbType.VarChar, 20).Value = request.ContactNumber; cn.Open(); SqlTransaction sqlTran = cn.BeginTransaction(); cmd.Transaction = sqlTran; int rowsAffected = 0; try { rowsAffected += cmd.ExecuteNonQuery(); cmd.CommandText = insertLeadParamQuery; cmd.Parameters.Clear(); foreach (var item in request.LeadParameters) { cmd.Parameters.Add("@LeadParameterId", SqlDbType.VarChar, 150).Value = 10000000 + new Random().Next(); cmd.Parameters.Add("@LeadId", SqlDbType.BigInt).Value = request.LeadId; cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = item.Key; cmd.Parameters.Add("@Value", SqlDbType.VarChar, 200).Value = item.Value; rowsAffected += cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } sqlTran.Commit(); } catch (Exception ex) { sqlTran.Rollback(); } finally { cn.Close(); } } }