DataTable dynamicQueries <T>(QueryType 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);
ErrorHandler.ErrorHandle error = ErrorHandler.ErrorHandle.getInstance();
error.log(queryString.ToString());
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionStringSHS;
// Create the DbCommand.
DbCommand command = factory.CreateCommand();
command.CommandText = queryString.ToString();
command.Connection = connection;
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
if (queryType == QueryType.SELECT)
{
// Fill the DataTable.
returnTable = new DataTable();
adapter.Fill(returnTable);
return(returnTable);
}
if (queryType != 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 == 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);
// Fill the DataTable.
//returnTable = new DataTable();
//adapter.Fill(returnTable);
// Insert a new row.
DataRow newRow = returnTable.NewRow();
// newRow["CustomerID"] = "XYZZZ";
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 == 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("guid = '{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 == 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("guid = '{0}'", spesificClass.GetType().GetProperty("GUID").GetValue(spesificClass)));
foreach (DataRow row in deleteRow)
{
row.Delete();
}
adapter.Update(returnTable);
return(returnTable);
}
}
}
catch (Exception ex)
{
ErrorHandler.ErrorHandle error = ErrorHandler.ErrorHandle.getInstance();
error.handle(ex, true, false, ex.ToString());
}
return(returnTable);
}
DataTable dynamicSelectQuery <T>(object objectClass, object[] selectParameters = null)
{
//return dalk n Datatable. As dit insert, delete, of update is, return null
DataTable returnTable = new DataTable();
try
{
// Cast it to itself
T spesificClass = (T)Convert.ChangeType(objectClass, typeof(T));
string tableName = (string)spesificClass.GetType().GetProperty("TableName").GetValue(spesificClass);
SortedDictionary <string, string> valuePair = (SortedDictionary <string, string>)spesificClass.GetType().GetProperty("TableDataInfo").GetValue(spesificClass);
StringBuilder queryString = new StringBuilder();
queryString.Append("SELECT ");
for (int i = 0; i < valuePair.Count; i++)
{
if (i != valuePair.Count - 1)
{
queryString.Append(valuePair.Keys.ElementAt(i) + ", ");
}
else
{
queryString.Append(valuePair.Keys.ElementAt(i) + " ");
}
}
queryString.Append("FROM ");
queryString.Append(tableName);
// providerName = "System.Data.SqlClient"
// connectionStringSHS = "Data Source=192.168.2.222;Initial Catalog=SmartHomeSystem;User ID=applicationLogin;Password=BelgiumCampus123."
//connectionStringSHS = ConfigurationManager.ConnectionStrings["default"].ConnectionString);
ErrorHandler.ErrorHandle error = ErrorHandler.ErrorHandle.getInstance();
error.log(queryString.ToString());
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionStringSHS;
using (connection)
{
// Create the DbCommand.
DbCommand command = factory.CreateCommand();
command.CommandText = queryString.ToString();
//command.CommandText = "SELECT * FROM tblProduct";
command.Connection = connection;
connection.Open();
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
// Fill the DataTable.
adapter.Fill(returnTable);
}
return(returnTable);
}
catch (Exception ex)
{
ErrorHandler.ErrorHandle error = ErrorHandler.ErrorHandle.getInstance();
error.handle(ex, true, false, ex.ToString());
}
return(returnTable);
}