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();
}
}
}
