/// <summary>
/// Gets table from database depending on recieved object.
/// </summary>
/// <param name="procName">Selected procedure from ProcedureNames enum.</param>
/// <returns>DataTable based on condition</returns>
public DataTable GetTable(ProcedureNames procName, string param = null)
{
DataTable dt = new DataTable();
string query = $"EXEC {CreateQuery(procName, param)};";
try
{
using SqlConnection conn = new SqlConnection(ConnHelper.ConnStr(connection_name));
using SqlDataAdapter sda = new SqlDataAdapter(query, conn);
//Fill the DataTable with records from Table.
sda.Fill(dt);
}
catch (SqlException e)
{
MessageBox.Show(
$"Provjerite vezu sa bazom podataka.\n {e.Message}",
"Greška",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
catch (Exception e)
{
MessageBox.Show(
$"Nepoznata greška kod dohvata županija, kontaktirajte podršku.\n {e.Message}",
"Greška",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
return(dt);
}
/// <summary>
/// Insert data in database. Name of table based on argument type.
/// </summary>
/// <param name="dbObject">Table name based on type name of object.</param>
/// <returns>Boolean, True if operation successful</returns>
public bool InsertData(IDbObject dbObject)
{
GenericPropertyFinder <IDbObject> property = new GenericPropertyFinder <IDbObject>();
IEnumerable <List <string> > obj = property.PrintTModelPropertyAndValue(dbObject);
GetTableName(dbObject);
string query = new DbQueryBuilder(obj, _table).BuildQuery(QueryType.Insert);
try
{
using SqlConnection conn = new SqlConnection(ConnHelper.ConnStr(connection_name));
using SqlCommand command = new SqlCommand(query, conn);
conn.Open();
command.ExecuteNonQuery();
command.Dispose();
conn.Close();
return(true);
}
catch (SqlException e)
{
MessageBox.Show(
$"Provjerite vezu sa bazom podataka.\n {e.Message}",
"Greška",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
return(false);
}
catch (Exception e)
{
MessageBox.Show(
$"Nepoznata greška kod brisanja podataka, kontaktirajte podršku.\n {e.Message}",
"Greška",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
return(false);
}
}
/// <summary>
/// Gets name of existing tables from database
/// </summary>
private void GetTableNames()
{
DataTable dt = new DataTable();
try
{
using SqlConnection conn = new SqlConnection(ConnHelper.ConnStr("KnjigovodstvoDb"));
string query = $"SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('{_tableName[0]}')";
using SqlDataAdapter sda = new SqlDataAdapter(query, conn);
//Fill the DataTable with records from Table.
sda.Fill(dt);
}
catch
{
}
foreach (DataRow row in dt.Rows)
{
_items.Add(row.ItemArray[0].ToString());
}
}
public DataTable ExecuteQuery(string query)
{
DataTable dt = new DataTable();
try
{
using SqlConnection conn = new SqlConnection(ConnHelper.ConnStr(connection_name));
using SqlDataAdapter sda = new SqlDataAdapter(query, conn);
//Fill the DataTable with records from Table.
sda.Fill(dt);
}
catch (SqlException e)
{
MessageBox.Show(
$"Provjerite vezu sa bazom podataka.\n {e.Message}",
"Greška",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
return(dt);
}