public void RemoveTags(string removeString, string blogID)
{
List <string> tagNames = removeString.Split(',').ToList();
List <string> tagIDs = new List <string>();
string SQLQuery;
for (int i = 0; i <= tagNames.Count - 1; i++)
{
var tag = _context.Tags.Single(m => m.Title == tagNames[i]);
tagIDs.Add(tag.TagID);
}
using (SqlConnection con = new SqlConnection(GetConString.ConString()))
using (SqlCommand cmd = con.CreateCommand())
{
con.Open();
for (int i = 0; i < tagNames.Count; i++)
{
SQLQuery = "DELETE FROM BlogTags WHERE TagID = @tagID" + i + " AND BlogID = @blogID" + i;
cmd.CommandText = SQLQuery;
cmd.Parameters.AddWithValue("@tagID" + i, tagIDs[i]);
cmd.Parameters.AddWithValue("@blogID" + i, blogID);
cmd.ExecuteNonQuery();
}
}
}
public void CreateTag(string tagNameString)
{
List <string> tagNames = tagNameString.Split(',').ToList();
string paramString;
string SQLQuery;
string id;
tagNames = tagNames.ConvertAll(d => d.Substring(0, 1).ToUpper() + d.Substring(1));
for (int i = tagNames.Count - 1; i >= 0; i--)
{
if (_context.Tags.Any(m => m.Title == tagNames[i]))
{
tagNames.Remove(tagNames[i]);
}
;
}
if (tagNames.Count == 0)
{
return;
}
else if (tagNames.Count == 1)
{
paramString = "(@tagID1, @Title1)";
}
else
{
paramString = "(@tagID1, @Title1)";
for (int i = 2; i <= tagNames.Count; i++)
{
paramString = paramString + ", (@TagID" + i + ", @Title" + i + ")";
}
}
SQLQuery = "INSERT INTO Tags(TagID, Title) VALUES " + paramString;
using (SqlConnection con = new SqlConnection(GetConString.ConString()))
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = SQLQuery;
for (int i = 1; i <= tagNames.Count; i++)
{
id = Guid.NewGuid().ToString();
cmd.Parameters.AddWithValue("@TagID" + i, id);
cmd.Parameters.AddWithValue("@Title" + i, tagNames[i - 1]);
}
con.Open();
cmd.ExecuteNonQuery();
}
}
public void PairTags(string tagNameString, string blogID)
{
List <string> tagNames = tagNameString.Split(',').ToList();
List <string> tagIDs = new List <string>();
string paramString;
string SQLQuery;
tagNames = tagNames.ConvertAll(d => d.Substring(0, 1).ToUpper() + d.Substring(1));
for (int i = 0; i <= tagNames.Count - 1; i++)
{
var tag = _context.Tags.Single(m => m.Title == tagNames[i]);
tagIDs.Add(tag.TagID);
}
if (tagNames.Count == 1)
{
paramString = "(@tagID1, @BlogID1)";
}
else
{
paramString = "(@tagID1, @BlogID1)";
for (int i = 2; i <= tagNames.Count; i++)
{
paramString = paramString + ", (@TagID" + i + ", @BlogID" + i + ")";
}
}
SQLQuery = "INSERT INTO BlogTags(TagID, BlogID) VALUES " + paramString;
using (SqlConnection con = new SqlConnection(GetConString.ConString()))
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = SQLQuery;
for (int i = 1; i <= tagNames.Count; i++)
{
;
cmd.Parameters.AddWithValue("@TagID" + i, tagIDs[i - 1]);
cmd.Parameters.AddWithValue("@BlogID" + i, blogID);
}
con.Open();
cmd.ExecuteNonQuery();
}
}
public void GetUserInfo(string username)
{
SqlConnection con = new SqlConnection(GetConString.ConString());
//string query = "SELECT FirstName, Id FROM AspNetUsers WHERE UserName = "******"SELECT FirstName, Id FROM AspNetUsers WHERE UserName = @UserName";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add(new SqlParameter("@UserName", System.Data.SqlDbType.NVarChar));
cmd.Parameters["@UserName"].Value = username;
SqlDataReader rdr = cmd.ExecuteReader();
con.Open();
LoggedUserModel user = new LoggedUserModel
{
Email = username,
CreatorFirstName = rdr["FirstName"].ToString(),
CreatorID = rdr["Id"].ToString()
};
}
public JsonResult AddContact(AgentData agent)
{
SqlConnection con = new SqlConnection(GetConString.ConString());
SqlCommand cmd = new SqlCommand();
cmd = new SqlCommand("InsertData", con); // Stored Procedure to add data
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FName", agent.FName);
cmd.Parameters.AddWithValue("@Lname", agent.LName);
cmd.Parameters.AddWithValue("@Email", agent.Email);
cmd.Parameters.AddWithValue("@Phone", agent.Phone);
cmd.Parameters.AddWithValue("@Comment", agent.Comment);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
string data = "Enquiry Send Successfully,Melissa will get back to you very soon.";
return(Json(data)); // return json string to ajax cal
}
public async Task <IActionResult> OnPostAsync(string returnUrl = null)
{
returnUrl = returnUrl ?? Url.Content("~/");
ExternalLogins = (await _signInManager.GetExternalAuthenticationSchemesAsync()).ToList();
if (ModelState.IsValid)
{
var user = new IdentityUser {
UserName = Input.Email, Email = Input.Email
};
var result = await _userManager.CreateAsync(user, Input.Password);
if (result.Succeeded)
{
if (Input.Checked == "true")
{
int id = 0;
NpgsqlConnection con = new NpgsqlConnection(GetConString.ConString());
NpgsqlCommand com = new NpgsqlCommand();
con.Open();
com.Connection = con;
com.CommandText = "SELECT companyid FROM public.company ORDER BY \"companyid\" ASC";
dr = com.ExecuteReader();
while (dr.Read())
{
id = (int)dr["companyid"] + 1;
}
con.Close();
String query = "INSERT INTO public.company(companyid, name, email, phoneno, weblink) values ('" + id + "','" + Input.CompanyName + "','" + Input.CompanyEmail + "','" + Input.CompanyPhone + "','" + Input.CompanyWebsite + "')";
String query1 = "INSERT INTO public.\"UserCompany\"(\"CompanyID\", \"UserID\") values ('" + id + "','" + user.Id + "')";
String query2 = "INSERT INTO public.\"AspNetUserRoles\"(\"UserId\", \"RoleId\") values ('" + user.Id + "','" + 1 + "')";
NpgsqlCommand cmd = new NpgsqlCommand(query, con);
NpgsqlCommand cmd1 = new NpgsqlCommand(query1, con);
NpgsqlCommand cmd2 = new NpgsqlCommand(query2, con);
con.Open();
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
con.Close();
}
_logger.LogInformation("User created a new account with password.");
var code = await _userManager.GenerateEmailConfirmationTokenAsync(user);
code = WebEncoders.Base64UrlEncode(Encoding.UTF8.GetBytes(code));
var callbackUrl = Url.Page(
"/Account/ConfirmEmail",
pageHandler: null,
values: new { area = "Identity", userId = user.Id, code = code, returnUrl = returnUrl },
protocol: Request.Scheme);
await _emailSender.SendEmailAsync(Input.Email, "Confirm your email",
$"Please confirm your account by <a href='{HtmlEncoder.Default.Encode(callbackUrl)}'>clicking here</a>.");
if (_userManager.Options.SignIn.RequireConfirmedAccount)
{
return(RedirectToPage("RegisterConfirmation", new { email = Input.Email, returnUrl = returnUrl }));
}
else
{
await _signInManager.SignInAsync(user, isPersistent : false);
return(LocalRedirect(returnUrl));
}
}
foreach (var error in result.Errors)
{
ModelState.AddModelError(string.Empty, error.Description);
}
}
// If we got this far, something failed, redisplay form
return(Page());
}
public HomeController(ILogger <HomeController> logger)
{
_logger = logger;
con.ConnectionString = GetConString.ConString();
}