// insert command handler
public bool DataInsert(int id, string efirst, string elast, int eid, string cfirst, string clast, int cid)
{
// initialize variables
bool flag = false;
int rowsAffected = 0;
// create a connection string for the database
//ConnectionStringSettings settings;
//settings = System.Configuration.ConfigurationManager.ConnectionStrings["DatabaseConnection"];
//string connectionString = settings.ConnectionString;
// create a connection object
//SqlConnection Connection1;
//Connection1 = new SqlConnection();
//Connection1.ConnectionString = connectionString;
// create db connection object
SqlConnection Connection1;
Connection1 = createDBConnection();
// open a connection to the table
Connection1.Open();
System.Diagnostics.Debug.WriteLine("connection opened to insert");
// create a command object for the query
using (SqlCommand command = new SqlCommand(
"INSERT INTO Table_test VALUES(@Id,@EFirst,@ELast,@EQID,@CFirst,@CLast,@CQID)", Connection1))
{
// create a parameterized query
command.Parameters.Add(new SqlParameter("Id", id));
command.Parameters.Add(new SqlParameter("EFirst", efirst));
command.Parameters.Add(new SqlParameter("ELast", elast));
command.Parameters.Add(new SqlParameter("EQID", eid));
command.Parameters.Add(new SqlParameter("CFirst", cfirst));
command.Parameters.Add(new SqlParameter("CLast", clast));
command.Parameters.Add(new SqlParameter("CQID", cid));
// use try block to fail gracefully
try
{
// execute the query
rowsAffected = command.ExecuteNonQuery();
Debug.WriteLine(rowsAffected);
// close the connection to the database
Connection1.Close();
Debug.WriteLine("connection closed");
// set flag to true to signify data was inserted
flag = true;
}
// catch the error to make it a learning experience instead of failure
catch (SqlException err)
{
// initialize variables
string errorMessage = "";
// create a readable error message from the sql exception(s)
for (int i = 0; i < err.Errors.Count; i++)
{
errorMessage = "Index #" + i + "\n" +
"Message: " + err.Errors[i].Message + "\n" +
"LineNumber: " + err.Errors[i].LineNumber + "\n" +
"Source: " + err.Errors[i].Source + "\n" +
"Procedure: " + err.Errors[i].Procedure + "\n";
}
// write the error message out to the debug console
Debug.WriteLine(errorMessage);
// close the connection to the database still and return 0
Connection1.Close();
// set flag to false to signify information was not inserted
flag = false;
}
}
// return the number of rows affected
return(flag);
}
public int DataSelect(string query)
{
// initialize variables
int rows = 0;
// create a connection string for the database
//ConnectionStringSettings settings;
//settings = System.Configuration.ConfigurationManager.ConnectionStrings["DatabaseConnection"];
//string connectionString = settings.ConnectionString;
// create a connection object
//SqlConnection Connection1;
//Connection1 = new SqlConnection();
//Connection1.ConnectionString = connectionString;
// create db connection object
SqlConnection Connection1;
Connection1 = createDBConnection();
// use try block to fail gracefully
try
{
// open a connection to the database
Connection1.Open();
Debug.WriteLine("connection opened to select");
// create an sqlcommand object with the query and database connection
SqlCommand command = new SqlCommand(query, Connection1);
// create a sql reader object with the sql command object
SqlDataReader reader = command.ExecuteReader();
// only read if there is valid information to be read
if (reader.HasRows)
{
// initialize variable to iterate over reader results
int i = 1;
// while the reader has rows
while (reader.Read())
{
// since there are 7 elements in each row of the table, bet we do not wanbt the first (i=0)
while (i < 7)
{
// output the information from the reader
Debug.WriteLine(reader[i]);
// increment i
i += 1;
}
}
}
// close the connection to the database
Connection1.Close();
Debug.WriteLine("connection closed");
// return the number of rows affected
return(rows);
}
// catch the error to make it a learning experience instead of failure
catch (SqlException err)
{
// initialize variables
string errorMessages = "";
// create a readable error message from the sql exception(s)
for (int i = 0; i < err.Errors.Count; i++)
{
errorMessages = "Index #" + i + "\n" +
"Message: " + err.Errors[i].Message + "\n" +
"LineNumber: " + err.Errors[i].LineNumber + "\n" +
"Source: " + err.Errors[i].Source + "\n" +
"Procedure: " + err.Errors[i].Procedure + "\n";
}
// write the error message out to the debug console
Debug.WriteLine(errorMessages);
// close the connection to the database
Connection1.Close();
}
// return the number of rows affected
return(rows);
}
public async Task InitializeConnection(IWriteToOutput writeToOutput, StringBuilder content, string messageConnection1 = null, string messageConnection2 = null)
{
bool service1IsNull = this.Service1 == null;
bool service2IsNull = this.Service2 == null;
{
var mess1 = messageConnection1;
if (string.IsNullOrEmpty(mess1))
{
mess1 = Properties.OutputStrings.ConnectingToCRM;
}
var mess2 = Connection1.GetConnectionDescription();
if (service1IsNull)
{
writeToOutput.WriteToOutput(null, mess1);
writeToOutput.WriteToOutput(null, mess2);
this.Service1 = await QuickConnection.ConnectAsync(Connection1);
}
var mess3 = string.Format(Properties.OutputStrings.CurrentServiceEndpointFormat1, this.Service1.CurrentServiceEndpoint);
if (service1IsNull)
{
writeToOutput.WriteToOutput(null, mess3);
}
if (content != null)
{
content.AppendLine(mess1);
content.AppendLine(mess2);
content.AppendLine(mess3);
}
}
if (service1IsNull)
{
writeToOutput.WriteToOutput(null, string.Empty);
}
if (content != null)
{
content.AppendLine();
}
{
var mess1 = messageConnection2;
if (string.IsNullOrEmpty(mess1))
{
mess1 = Properties.OutputStrings.ConnectingToCRM;
}
var mess2 = Connection2.GetConnectionDescription();
if (service2IsNull)
{
writeToOutput.WriteToOutput(null, mess1);
writeToOutput.WriteToOutput(null, mess2);
this.Service2 = await QuickConnection.ConnectAsync(Connection2);
}
var mess3 = string.Format(Properties.OutputStrings.CurrentServiceEndpointFormat1, this.Service2.CurrentServiceEndpoint);
if (service2IsNull)
{
writeToOutput.WriteToOutput(null, mess3);
}
if (content != null)
{
content.AppendLine(mess1);
content.AppendLine(mess2);
content.AppendLine(mess3);
}
}
if (content != null)
{
content.AppendLine();
}
}
