public void nonQuery(string query, Dictionary <string, string> parameters)
{
connection = null;
try
{
connection = new SqlConnection(connectionString);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command = new SqlCommand(query, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (KeyValuePair <string, string> item in parameters)
{
command.Parameters.Add(new SqlParameter(item.Key, item.Value));
}
command.ExecuteNonQuery();
}
catch (SqlException se)
{
FileHandlerTxt FHandler = new FileHandlerTxt();
switch (se.Number)
{
case 21:
FHandler.appendDataToTextFile(new List <string> {
string.Format("Error code 21 : Fatal SQL problem occured : {0} on {1}", se.Message, DateTime.UtcNow.ToLongDateString())
});
break;
case 53:
FHandler.appendDataToTextFile(new List <string> {
string.Format("Error code 53 : Error establishing a database connection : {0} on {1}", se.Message, DateTime.UtcNow.ToLongDateString())
});
break;
default:
FHandler.appendDataToTextFile(new List <string> {
string.Format("SQLException {0} on {1}", se.Message, DateTime.UtcNow.ToLongDateString())
});
break;
}
}
catch (Exception e)
{
FileHandlerTxt FHandler = new FileHandlerTxt();
FHandler.appendDataToTextFile(new List <string> {
string.Format("Exception {0} on {1}", e.Message, DateTime.UtcNow.ToLongDateString())
});
}
finally
{
connection.Close();
}
}
public void appendDataToTextFile(List <string> DataToAppend)
{
try
{
if (File.Exists(this.txtFilePath) == false)
{
CreateFileIfNotExists(this.txtFilePath);
}
stream = new FileStream(this.txtFilePath, FileMode.Append, FileAccess.Write);
writer = new StreamWriter(stream);
foreach (string item in DataToAppend)
{
writer.WriteLine(item);
writer.Flush();
}
}
catch (FileNotFoundException)
{
FileHandlerTxt handleError = new FileHandlerTxt();
handleError.appendDataToTextFile(new List <string>()
{
string.Format("The magical information capsule {0} could not be found, {1}", this.txtFilePath, DateTime.UtcNow.ToShortDateString())
});
}
catch (DirectoryNotFoundException)
{
FileHandlerTxt handleError = new FileHandlerTxt();
handleError.appendDataToTextFile(new List <string>()
{
string.Format("The directory of magical information capsule {0} could not be found, {1}", this.txtFilePath, DateTime.UtcNow.ToShortDateString())
});
}
catch (IOException)
{
FileHandlerTxt handleError = new FileHandlerTxt();
handleError.appendDataToTextFile(new List <string>()
{
string.Format("A magic disruption occured: {0} ({1})", this.txtFilePath, DateTime.UtcNow.ToShortDateString())
});
}
finally
{
writer.Close();
stream.Close();
}
}
public List <string> getRawDataFromTextFile()
{
List <string> rawData = new List <string>();
try
{
stream = new FileStream(this.txtFilePath, FileMode.Open, FileAccess.Read);
reader = new StreamReader(stream);
while (!reader.EndOfStream)
{
rawData.Add(reader.ReadLine());
}
}
catch (FileNotFoundException)
{
FileHandlerTxt handleError = new FileHandlerTxt();
handleError.appendDataToTextFile(new List <string>()
{
string.Format("The magical information capsule {0} could not be found, {1}", this.txtFilePath, DateTime.UtcNow.ToShortDateString())
});
}
catch (DirectoryNotFoundException)
{
FileHandlerTxt handleError = new FileHandlerTxt();
handleError.appendDataToTextFile(new List <string>()
{
string.Format("The directory of magical information capsule {0} could not be found, {1}", this.txtFilePath, DateTime.UtcNow.ToShortDateString())
});
}
catch (IOException)
{
FileHandlerTxt handleError = new FileHandlerTxt();
handleError.appendDataToTextFile(new List <string>()
{
string.Format("A magic disruption occured: {0} ({1})", this.txtFilePath, DateTime.UtcNow.ToShortDateString())
});
}
finally
{
reader.Close();
stream.Close();
}
return(rawData);
}
public DataTable dynamicQueries <T>(string queryType, object objectClass, KeyValuePair <string, string[, ]> table)
{
//return dalk n Datatable. As dit insert, delete, of update is, return null
DataTable returnTable = null;
try
{
StringBuilder queryString = new StringBuilder();
queryString.Append("SELECT ");
for (int i = 0; i < table.Value.GetLength(0); i++)
{
if (i != table.Value.GetLength(0) - 1)
{
queryString.Append(table.Value[i, 0] + ", ");
}
else
{
queryString.Append(table.Value[i, 0] + " ");
}
}
queryString.Append("FROM ");
queryString.Append(table.Key);
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
// Create the DbCommand.
DbCommand command = factory.CreateCommand();
command.CommandText = queryString.ToString();
command.Connection = connection;
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
if (queryType == "SELECT")
{
// Fill the DataTable.
returnTable = new DataTable();
adapter.Fill(returnTable);
return(returnTable);
}
if (queryType != "SELECT")
{
// Create the DbCommandBuilder.
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
// Cast it to itself
T spesificClass = (T)Convert.ChangeType(objectClass, typeof(T));
if (queryType == "INSERT")
{
// Get the insert commands.
adapter.InsertCommand = builder.GetInsertCommand();
System.Diagnostics.Debug.WriteLine(adapter.InsertCommand.CommandText);
// Fill the DataTable.
returnTable = new DataTable();
adapter.Fill(returnTable);
// Insert a new row.
DataRow newRow = returnTable.NewRow();
for (int i = 0; i < table.Value.GetLength(0); i++)
{
//PropertyInfo info = spesificClass.GetType().GetProperty(table.Value[i,1]);
//newRow[table.Value[i, 0]] = info.GetValue(spesificClass);
string dbColumn = table.Value[i, 0];
string propName = table.Value[i, 1];
var value = spesificClass.GetType().GetProperty(propName).GetValue(spesificClass);
newRow[dbColumn] = value;
}
returnTable.Rows.Add(newRow);
adapter.Update(returnTable);
return(returnTable);
}
else
if (queryType == "UPDATE")
{
// Get the update commands.
adapter.UpdateCommand = builder.GetUpdateCommand();
System.Diagnostics.Debug.WriteLine(adapter.UpdateCommand.CommandText);
// Fill the DataTable.
returnTable = new DataTable();
adapter.Fill(returnTable);
// Edit an existing row.
//DataRow[] editRow = returnTable.Select("CustomerID = 'XYZZZ'");
//editRow[0]["CompanyName"] = "XYZ Corporation";
// Update the row based on GUID
DataRow[] editRow = returnTable.Select(string.Format("globalUniqueID = '{0}'", spesificClass.GetType().GetProperty("GUID").GetValue(spesificClass)));
//editRow[0]["CompanyName"] = "XYZ Corporation";
for (int i = 0; i < table.Value.GetLength(0); i++)
{
editRow[0][table.Value[i, 0]] = spesificClass.GetType().GetProperty(table.Value[i, 1]).GetValue(spesificClass);
}
adapter.Update(returnTable);
return(returnTable);
}
else
if (queryType == "DELETE")
{
// Get the delete commands.
adapter.DeleteCommand = builder.GetDeleteCommand();
System.Diagnostics.Debug.WriteLine(adapter.DeleteCommand.CommandText);
// Fill the DataTable.
returnTable = new DataTable();
adapter.Fill(returnTable);
// Delete a row.
DataRow[] deleteRow = returnTable.Select(string.Format("globalUniqueID = '{0}'", spesificClass.GetType().GetProperty("GUID").GetValue(spesificClass)));
foreach (DataRow row in deleteRow)
{
row.Delete();
}
adapter.Update(returnTable);
return(returnTable);
}
}
}
catch (Exception e)
{
FileHandlerTxt FHandler = new FileHandlerTxt();
FHandler.appendDataToTextFile(new List <string> {
string.Format("Exception {0} on {1}", e.Message, DateTime.UtcNow.ToLongDateString())
});
}
return(returnTable);
}
