Brunofsv10
Membro
Boa noite,
Estou a ter um problema na Criação de tabelas na data access layer devido ao método criado aqui na DB.
O programa não está a passar os parms do dicionário a != (diferente) de null, logo não cria um novo dictionary,
Não estou a conseguir resolver este problema, agradeço uma ajuda.
public bool NonQuery(string query, Dictionary<string, object> parms =null)
{
bool res = false;
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = getConnection();
myCommand.CommandText=query;
if (parms != null)
{
parms = new Dictionary<string, object);
}
return res;
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace Farmacia_DataAbstrationLayer
{
public class DB : IDisposable
{
protected SqlConnection conn;
protected SqlCommand cmd;
protected SqlDataReader dr;
protected SqlDataAdapter myAdapter;
private bool isDisposed = false;
private static DB db = null; // padrão singleton, garante que
// no máx só há uma instância desta classe
private DB(string file)
{
conn = new SqlConnection(file);
var query = @"PRAGMA foreign_keys = ON";
this.NonQuery(query, null);
}
public static DB getDB(string file)
{
if (db == null)
{
db = new DB(file);
}
return db;
}
public bool NonQuery(string query, Dictionary<string, object> parms =null)
{
bool res = false;
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = getConnection();
myCommand.CommandText=query;
if (parms != null)
{
parms = new Dictionary<string, object);
}
return res;
}
public DB()
{
myAdapter = new SqlDataAdapter();
var cb = new SqlConnectionStringBuilder
{
DataSource = "daisqldatabase.database.windows.net",
UserID = "grupo_dai",
Password = "Bruno1234",
InitialCatalog = "DAI"
};
}
/// <method>
/// Open Database Connection if Closed or Broken
/// </method>
private SqlConnection getConnection()
{
if (conn.State == ConnectionState.Closed || conn.State ==
ConnectionState.Broken)
{
conn.Open();
}
return conn;
}
public DataTable executeSelectQuery(String _query, SqlParameter[] sqlParameter =null )
{
SqlCommand myCommand = new SqlCommand();
DataTable dataTable = new DataTable();
dataTable = null;
DataSet ds = new DataSet();
try
{
myCommand.Connection = getConnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myCommand.ExecuteNonQuery();
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(ds);
dataTable = ds.Tables[0];
}
catch (SqlException e)
{
Console.WriteLine("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
return null;
}
finally
{
}
return dataTable;
}
/// <method>
/// Insert Query
/// </method>
public bool executeInsertQuery(String _query, SqlParameter[] sqlParameter )
{
SqlCommand myCommand = new SqlCommand();
try
{ if (sqlParameter != null )
{
myCommand.Connection = getConnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myAdapter.InsertCommand = myCommand;
myCommand.ExecuteNonQuery();
}
else { Console.WriteLine("não criado"); }
}
catch (SqlException e)
{
Console.WriteLine("Error - Connection.executeInsertQuery - Query: " + _query + " \nException: \n" + e.StackTrace.ToString());
return false;
}
finally
{
}
return true;
}
/// <method>
/// Update Query
/// </method>
public bool executeUpdateQuery(String _query, SqlParameter[] sqlParameter)
{
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.Connection = getConnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myAdapter.UpdateCommand = myCommand;
myCommand.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.Write("Error - Connection.executeUpdateQuery - Query: " + _query + " \n Exception: " + e.StackTrace.ToString());
return false;
}
finally
{
}
return true;
}
protected void Dispose(bool disposing)
{
if (isDisposed) return;
if (disposing)
{
if (conn != null)
{
conn.Dispose();
}
}
conn = null;
isDisposed = true;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
~DB()
{
Dispose(false);
}
}
}
Estou a ter um problema na Criação de tabelas na data access layer devido ao método criado aqui na DB.
O programa não está a passar os parms do dicionário a != (diferente) de null, logo não cria um novo dictionary,
Não estou a conseguir resolver este problema, agradeço uma ajuda.
public bool NonQuery(string query, Dictionary<string, object> parms =null)
{
bool res = false;
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = getConnection();
myCommand.CommandText=query;
if (parms != null)
{
parms = new Dictionary<string, object);
}
return res;
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace Farmacia_DataAbstrationLayer
{
public class DB : IDisposable
{
protected SqlConnection conn;
protected SqlCommand cmd;
protected SqlDataReader dr;
protected SqlDataAdapter myAdapter;
private bool isDisposed = false;
private static DB db = null; // padrão singleton, garante que
// no máx só há uma instância desta classe
private DB(string file)
{
conn = new SqlConnection(file);
var query = @"PRAGMA foreign_keys = ON";
this.NonQuery(query, null);
}
public static DB getDB(string file)
{
if (db == null)
{
db = new DB(file);
}
return db;
}
public bool NonQuery(string query, Dictionary<string, object> parms =null)
{
bool res = false;
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = getConnection();
myCommand.CommandText=query;
if (parms != null)
{
parms = new Dictionary<string, object);
}
return res;
}
public DB()
{
myAdapter = new SqlDataAdapter();
var cb = new SqlConnectionStringBuilder
{
DataSource = "daisqldatabase.database.windows.net",
UserID = "grupo_dai",
Password = "Bruno1234",
InitialCatalog = "DAI"
};
}
/// <method>
/// Open Database Connection if Closed or Broken
/// </method>
private SqlConnection getConnection()
{
if (conn.State == ConnectionState.Closed || conn.State ==
ConnectionState.Broken)
{
conn.Open();
}
return conn;
}
public DataTable executeSelectQuery(String _query, SqlParameter[] sqlParameter =null )
{
SqlCommand myCommand = new SqlCommand();
DataTable dataTable = new DataTable();
dataTable = null;
DataSet ds = new DataSet();
try
{
myCommand.Connection = getConnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myCommand.ExecuteNonQuery();
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(ds);
dataTable = ds.Tables[0];
}
catch (SqlException e)
{
Console.WriteLine("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
return null;
}
finally
{
}
return dataTable;
}
/// <method>
/// Insert Query
/// </method>
public bool executeInsertQuery(String _query, SqlParameter[] sqlParameter )
{
SqlCommand myCommand = new SqlCommand();
try
{ if (sqlParameter != null )
{
myCommand.Connection = getConnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myAdapter.InsertCommand = myCommand;
myCommand.ExecuteNonQuery();
}
else { Console.WriteLine("não criado"); }
}
catch (SqlException e)
{
Console.WriteLine("Error - Connection.executeInsertQuery - Query: " + _query + " \nException: \n" + e.StackTrace.ToString());
return false;
}
finally
{
}
return true;
}
/// <method>
/// Update Query
/// </method>
public bool executeUpdateQuery(String _query, SqlParameter[] sqlParameter)
{
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.Connection = getConnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myAdapter.UpdateCommand = myCommand;
myCommand.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.Write("Error - Connection.executeUpdateQuery - Query: " + _query + " \n Exception: " + e.StackTrace.ToString());
return false;
}
finally
{
}
return true;
}
protected void Dispose(bool disposing)
{
if (isDisposed) return;
if (disposing)
{
if (conn != null)
{
conn.Dispose();
}
}
conn = null;
isDisposed = true;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
~DB()
{
Dispose(false);
}
}
}