public MainPage()
{
InitializeComponent();
BindingContext = new DBViewModel();
//conn = DependencyService.Get<ISqlite>().GetConnection();
//conn.CreateTable<Student>();
}
public ActionResult HttpIncludeDB(string db_type, string user, string pass, string server, int port, string allias)
{
DBViewModel model = new DBViewModel(db_type, user, pass, server, "tcp/ip", port, allias);
var jsonIDB = JsonConvert.SerializeObject(model);
string x = client.includeDB(jsonIDB);
return(RedirectToAction("IncluirDB", new { x = x }));
}
public IHttpActionResult QueryData([FromBody] DBViewModel dto)
{
var repos = new DBRepository();
try {
msgResponse = Json(repos.GetQueryToDatatable(dto));
}
catch (Exception ex) {
msgResponse = InternalServerError(ex);
}
return(msgResponse);
}
/// <summary>
/// Invoked when the application is launched normally by the end user. Other entry points
/// will be used such as when the application is launched to open a specific file.
/// </summary>
/// <param name="e">Details about the launch request and process.</param>
protected override void OnLaunched(LaunchActivatedEventArgs e)
{
Frame rootFrame = Window.Current.Content as Frame;
MainDbViewModel = new DBViewModel();
// Do not repeat app initialization when the Window already has content,
// just ensure that the window is active
if (rootFrame == null)
{
// Create a Frame to act as the navigation context and navigate to the first page
rootFrame = new Frame();
rootFrame.NavigationFailed += OnNavigationFailed;
if (e.PreviousExecutionState == ApplicationExecutionState.Terminated)
{
//TODO: Load state from previously suspended application
}
// Place the frame in the current Window
Window.Current.Content = rootFrame;
}
if (e.PrelaunchActivated == false)
{
if (rootFrame.Content == null)
{
// When the navigation stack isn't restored navigate to the first page,
// configuring the new page by passing required information as a navigation
// parameter
if (ApiInformation.IsApiContractPresent("Windows.Phone.PhoneContract", 1, 0))
{
StatusBar statusBar = Windows.UI.ViewManagement.StatusBar.GetForCurrentView();
statusBar.HideAsync();
}
rootFrame.Navigate(
SecurityService.GetLastLoggedInUser() == null ? typeof(View.LoginView) : typeof(View.MainPage),
e.Arguments);
}
// Ensure the current window is active
Window.Current.Activate();
}
}
public DataTable GetQueryToDatatable_Old(DBViewModel dto)
{
List <string> list = new List <string>();
var tb = new DataTable();
// Open connection to the database
string conString = string.Format("server={0};uid={1};pwd={2}; database=master", dto.Server, dto.User, dto.Password);
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand(dto.Query, con))
{
using (IDataReader dr = cmd.ExecuteReader())
{
tb.Load(dr);
}
}
}
return(tb);
}
public DataTable GetQueryToDatatable(DBViewModel dto)
{
List <string> list = new List <string>();
var tb = new DataTable();
// Open connection to the database
string conString = string.Format("{0}", BIServer);
try
{
if (dto.Query.ToLower().IndexOf("insert") > -1 ||
dto.Query.ToLower().IndexOf("update") > -1 ||
dto.Query.ToLower().IndexOf("delete") > -1)
{
throw new Exception("Invalid Query cannot used insert update delete!!");
}
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand(dto.Query, con))
{
using (IDataReader dr = cmd.ExecuteReader())
{
tb.Load(dr);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Query Error:" + ex.Message);
}
return(tb);
}
public List <Databases> GetDatabaseList_Old(DBViewModel dto)
{
List <Databases> dbList = new List <Databases>();
Databases db = null;
List <TableAll> tableAll = new List <TableAll>();
List <ColumnAll> columnAll = new List <ColumnAll>();
// Open connection to the database
string conString = string.Format("server={0};uid={1};pwd={2}; database={3}", dto.Server, dto.User, dto.Password, dbConnectionStr);
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand("GetAllDatabase", con))
{
cmd.CommandType = CommandType.StoredProcedure;
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
db = new Databases();
db.Database = dr[0].ToString();
dbList.Add(db);
}
dr.NextResult();
while (dr.Read())
{
tableAll.Add(new TableAll()
{
Database = dr[0].ToString(), Table = dr[1].ToString() + '.' + dr[2].ToString()
});
}
dr.NextResult();
while (dr.Read())
{
columnAll.Add(new ColumnAll()
{
Database = dr[0].ToString(), Table = dr[1].ToString() + '.' + dr[2].ToString(), Column = dr[3].ToString()
});
}
}
}
/* from q in pois
* select
* new
* {
* OBJECT_ID = q.OBJECTID,
* NAME = q.NAME_T,
* X = q.X,
* Y = q.Y,
* CHANGWAT_NAME = q.PROV_NAME_T,
* TUMBON_NAME = q.TUMB_NAME_T,
* AMPHUR_NAME = q.AMPH_NAME_T,
* TUMBON_CODE = q.TUMB_CODE,
* }
* );
*/
foreach (Databases tempDB in dbList)
{
tempDB.Tables = tableAll.FindAll(o => o.Database == tempDB.Database);
foreach (TableAll tempTable in tempDB.Tables)
{
tempTable.Columns = columnAll.FindAll(o => o.Database == tempTable.Database && o.Table == tempTable.Table);
}
}
}
return(dbList);
}
