//Takes in a properly formatted string and inserts it into the database.
public int insertString(string rowString)
{
//Processing row
CSVDataStruct tempRow = new CSVDataStruct();
//creates a stream
var stream = new MemoryStream();
var bytes = System.Text.Encoding.Default.GetBytes(rowString);
stream.Write(bytes, 0, bytes.Length);
stream.Seek(0, SeekOrigin.Begin);
TextFieldParser parser = new TextFieldParser(stream);
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
string[] fields = parser.ReadFields();
tempRow.company_name = fields[0];
tempRow.domain = fields[1];
tempRow.ccm_companySize = fields[2];
tempRow.ccm_industry = fields[3];
tempRow.category_name = fields[4];
tempRow.topic_id = fields[5];
tempRow.topic_name = fields[6];
tempRow.composite_score = fields[7];
tempRow.country = fields[8];
tempRow.metro_area = fields[9];
tempRow.domain_origin = fields[10];
tempRow.cdt = fields[11];
insertObject(tempRow);
return(0);
}
//This function reads in a CSV file and returns a string of all the entries
public string readCSV(string inFile)
{
CSVDataStruct tempRow = new CSVDataStruct();
string outstring = "";
using (TextFieldParser parser = new TextFieldParser(@inFile))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
while (!parser.EndOfData)
{
//Processing row
string[] fields = parser.ReadFields();
tempRow.company_name = fields[0];
tempRow.domain = fields[1];
tempRow.ccm_companySize = fields[2];
tempRow.ccm_industry = fields[3];
tempRow.category_name = fields[4];
tempRow.topic_id = fields[5];
tempRow.topic_name = fields[6];
tempRow.composite_score = fields[7];
tempRow.country = fields[8];
tempRow.metro_area = fields[9];
tempRow.domain_origin = fields[10];
tempRow.cdt = fields[11];
insertObject(tempRow);
}
}
return(outstring);
}
//inserts a CSV object into a database.
public string insertObject(CSVDataStruct insertRow)
{
string outMessage = "";
string companyNumber;
var insertCommandCompany = "INSERT INTO Company (CompanyName, Domain, CompanySize, Industry) Values(@CompanyName, @Domain, @CompanySize, @Industry)";
var insertCommandTopic = "INSERT INTO Topic (TopicID,TopicName, Category) Values(@TopicID, @TopicName , @TopicCategory)";
var insertCommandSurge = "INSERT INTO Surge (TopicID, CompanyID, Date, Score, Country, MetroArea, DomainOrigin) Values(@TopicID, @CompanyID , @Date, @Score, @Country, @MetroArea, @DomainOrigin)";
var checkCommandCompany = "SELECT * FROM Company WHERE CompanyName = @CompanyName";
var checkCommandTopic = "SELECT * FROM Topic WHERE TopicID = @TopicID";
var checkCommandSurge = "SELECT * FROM Surge Where TopicID = @TopicID AND CompanyID = @CompanyID AND Date = @Date";
//creates connection class instance
var dbCon = MVC5App.Models.DBConnection.Instance();
dbCon.DatabaseName = "YourDatabase";
//trys to connect once
if (dbCon.IsConnect())
{
//based on how the code is written, the connection can fail.
//This attempts to retry connection if intially failed.
int tryConnection = 0;
while (dbCon.Connection.State != System.Data.ConnectionState.Open && tryConnection < 10)
{
dbCon.IsConnect();
tryConnection++;
}
//company check
var cmd = new MySqlCommand(checkCommandCompany, dbCon.Connection);
cmd.Parameters.AddWithValue("@CompanyName", insertRow.company_name);
var reader = cmd.ExecuteReader();
if (!reader.Read())
{
reader.Close();
cmd = new MySqlCommand(insertCommandCompany, dbCon.Connection);
cmd.Parameters.AddWithValue("@CompanyName", insertRow.company_name);
cmd.Parameters.AddWithValue("@Domain", insertRow.domain);
cmd.Parameters.AddWithValue("@CompanySize", insertRow.ccm_companySize);
cmd.Parameters.AddWithValue("@Industry", insertRow.ccm_industry);
cmd.ExecuteNonQuery();
}
else
{
reader.Close();
}
//we need the company number for the surge query
cmd = new MySqlCommand(checkCommandCompany, dbCon.Connection);
cmd.Parameters.AddWithValue("@CompanyName", insertRow.company_name);
reader = cmd.ExecuteReader();
reader.Read();
companyNumber = reader.GetString(0);
reader.Close();
//topic check
cmd = new MySqlCommand(checkCommandTopic, dbCon.Connection);
cmd.Parameters.AddWithValue("@TopicID", insertRow.topic_id);
reader = cmd.ExecuteReader();
if (!reader.Read())
{
reader.Close();
cmd = new MySqlCommand(insertCommandTopic, dbCon.Connection);
cmd.Parameters.AddWithValue("@TopicID", insertRow.topic_id);
cmd.Parameters.AddWithValue("@TopicName", insertRow.topic_name);
cmd.Parameters.AddWithValue("@TopicCategory", insertRow.category_name);
cmd.ExecuteNonQuery();
}
else
{
reader.Close();
}
//surge check
//topic check
cmd = new MySqlCommand(checkCommandSurge, dbCon.Connection);
cmd.Parameters.AddWithValue("@CompanyID", companyNumber);
cmd.Parameters.AddWithValue("@TopicID", insertRow.topic_id);
cmd.Parameters.AddWithValue("@Date", insertRow.cdt);
reader = cmd.ExecuteReader();
if (!reader.Read())
{
reader.Close();
cmd = new MySqlCommand(insertCommandSurge, dbCon.Connection);
cmd.Parameters.AddWithValue("@TopicID", insertRow.topic_id);
cmd.Parameters.AddWithValue("@CompanyID", companyNumber);
cmd.Parameters.AddWithValue("@Date", insertRow.cdt);
cmd.Parameters.AddWithValue("@Score", insertRow.composite_score);
cmd.Parameters.AddWithValue("@Country", insertRow.country);
cmd.Parameters.AddWithValue("@MetroArea", insertRow.metro_area);
cmd.Parameters.AddWithValue("@DomainOrigin", insertRow.domain_origin);
cmd.ExecuteNonQuery();
}
else
{
reader.Close();
}
dbCon.Close();
}
return(outMessage);
}