public static string Difference(DataProvider from, DataProvider to, string dbNameFrom, string dbNameTo)
        {
            DatabaseName dname1 = new DatabaseName(from, dbNameFrom);
            DatabaseName dname2 = new DatabaseName(to, dbNameTo);

            string[] names = MetaDatabase.GetTableNames(dname1);

            StringBuilder builder = new StringBuilder();
            foreach (string tableName in names)
            {
                TableName tname1 = new TableName(dname1, tableName);
                TableName tname2 = new TableName(dname2, tableName);

                string[] primaryKeys = InformationSchema.PrimaryKeySchema(tname1).ToArray<string>(0);
                if (primaryKeys.Length == 0)
                    continue;

                if (MetaDatabase.TableExists(tname2))
                {
                    builder.Append(TableCompare.Difference(tname1, tname2, tableName, primaryKeys));
                }
                else
                {
                    builder.Append(TableCompare.Rows(tableName, from));
                }

                builder.AppendLine();
            }

            return builder.ToString();
        }

 public MatchReferencePredicate(DataProvider provider, Type entityType, string propertyName, Type referencedEntityType, Guid referencedEntityID, string mirrorPropertyName)
 {
     ReferencedEntityID = referencedEntityID;
     PropertyName = propertyName;
     ReferencedEntityType = referencedEntityType;
     MirrorPropertyName = mirrorPropertyName;
 }

        public static DbConnectionStringBuilder CreateConnectionStringBuilder(DataProvider dataProvider)
        {
            DbConnectionStringBuilder dbConnectionStringBuilder;

            switch (dataProvider)
            {
                case DataProvider.Odbc:
                    dbConnectionStringBuilder = new OdbcConnectionStringBuilder();
                    break;

                case DataProvider.OleDB:
                    dbConnectionStringBuilder = new OleDbConnectionStringBuilder();
                    break;

                case DataProvider.SqlServer:
                    dbConnectionStringBuilder = new SqlConnectionStringBuilder();
                    break;

                default:
                    dbConnectionStringBuilder = null;
                    break;
            }

            return dbConnectionStringBuilder;
        }

        public static DbConnection CreateConnection(DataProvider dataProvider)
        {
            DbConnection dbConnection;

            switch (dataProvider)
            {
                case DataProvider.Odbc:
                    dbConnection = new OdbcConnection();
                    break;

                case DataProvider.OleDB:
                    dbConnection = new OleDbConnection();
                    break;

                case DataProvider.SqlServer:
                    dbConnection = new SqlConnection();
                    break;

                default:
                    dbConnection = null;
                    break;
            }

            return dbConnection;
        }

        public MyConnectionSql(DataProvider dataProvider)
            : base(dataProvider)
        {
            SqlConnection conn = new SqlConnection(@"server = .\sqlexpress; integrated security = true;");

            try
            {
                // Abrir la conexión
                conn.Open();

                // Detalles de la conexión
                this.DetallesConexion(conn);

            }
            catch (SqlException ex)
            {
                // Desplegar la excepción e el error
                Console.WriteLine("Error: " + ex.Message + ex.StackTrace);
            }
            finally
            {
                // Cerrar la conexión
                conn.Close();
                Console.WriteLine("Conexión cerrada.");
            }
        }

        public MyConnectionOdbc(DataProvider dataProvider)
            : base(dataProvider)
        {
            // Crear conexión
            OdbcConnection conn = new OdbcConnection(@"provider = sqlodbc;
                data source = .\sqlexpress; trusted connection = yes;");

            try
            {
                // Abrir conexión
                conn.Open();
                Console.WriteLine("Conexión establecida.");

                // Detalles de la conexión
                this.DetallesConexion(conn);
            }
            catch (OdbcException ex)
            {
                // Desplegar excepción o error
                Console.WriteLine("Error: " + ex.Message + ex.StackTrace);
            }
            finally
            {
                //Cerrar la conexión
                conn.Close();
            }
        }

        public MyConnectionOleDb(DataProvider dataProvider)
            : base(dataProvider)
        {
            // Crear conexión
            OleDbConnection conn = new OleDbConnection(@"provider = sqloledb;
                data source = .\sqlexpress; integrated security = sspi;");

            try
            {
                // Abrir conexión
                conn.Open();
                Console.WriteLine("Conexión establecida.");

                // Detalles de la conexión
                this.DetallesConexion(conn);
            }
            catch (OleDbException ex)
            {
                // Desplegar excepción o error
                Console.WriteLine("Error: " + ex.Message + ex.StackTrace);
            }
            finally
            {
                //Cerrar la conexión
                conn.Close();
            }
        }

 private void textBox1_TextChanged(object sender, System.EventArgs e)
 {
     using (var dataProvider = new DataProvider())
     {
        
     }
 }

        // Called before the action in the inherited controller is executed, allowing certain members to be set
        protected override void OnActionExecuting(ActionExecutingContext ctx)
        {
            base.OnActionExecuting(ctx);

            this.db = HengeApplication.DataProvider;
            this.globals = HengeApplication.Globals;

            // If the user has logged in then add their name to the view data
            if (this.User.Identity.IsAuthenticated)
            {
                this.user					= this.db.Get<User>(x => x.Name == this.User.Identity.Name);
                this.avatar					= Session["Avatar"] as Avatar;
                if(this.avatar != null && this.avatar.User != this.user) {
                    this.avatar = null;
                }
                this.cache					= Session["Cache"] as Cache;

                this.ViewData["User"] 		= this.User.Identity.Name;
                this.ViewData["Character"]	= (this.avatar != null) ? string.Format("{0} of {1}", this.avatar.Name, this.user.Clan) : null;
            }
            else
            {
                this.user 	= null;
                this.avatar	= null;
            }
        }

		private void AddRecord(DataProvider dataProvider, EmployeeZonesDataSet ds, RubezhDAL.DataClasses.PassJournal record, EmployeeZonesReportFilter filter, bool isEnter, Dictionary<Guid, string> zoneMap)
		{
			if (record.EmployeeUID == null)
				return;
			var dataRow = ds.Data.NewDataRow();
			var employee = dataProvider.GetEmployee(record.EmployeeUID.Value);
			dataRow.Employee = employee.Name;
			dataRow.Orgnisation = employee.Organisation;
			dataRow.Department = employee.Department;
			dataRow.Position = employee.Position;
			dataRow.Zone = zoneMap.ContainsKey(record.ZoneUID) ? zoneMap[record.ZoneUID] : null;
			dataRow.EnterDateTime = record.EnterTime;
			if (record.ExitTime.HasValue)
			{
				dataRow.ExitDateTime = record.ExitTime.Value;
				dataRow.Period = dataRow.ExitDateTime - dataRow.EnterDateTime;
			}
			else
			{
				dataRow.ExitDateTime = filter.ReportDateTime;
				dataRow.Period = filter.ReportDateTime - dataRow.EnterDateTime;
			}

			if (!filter.IsEmployee)
			{
				var escortUID = employee.Item.EscortUID;
				if (escortUID.HasValue)
				{
					var escort = dataProvider.GetEmployee(escortUID.Value);
					dataRow.Escort = escort.Name;
				}
			}
			ds.Data.Rows.Add(dataRow);
		}

        protected override void OnCreate(Bundle bundle)
        {
            base.OnCreate(bundle);

            dataProvider = new DataProvider(XDocument.Load(Assembly.GetExecutingAssembly().GetManifestResourceStream("Lines.xml")), XDocument.Load(Assembly.GetExecutingAssembly().GetManifestResourceStream("Stations.xml")), XDocument.Load(Assembly.GetExecutingAssembly().GetManifestResourceStream("StationLines.xml")));

            // Set our view from the "main" layout resource
            SetContentView(Resource.Layout.Main);

            spinnerLine = FindViewById<Spinner>(Resource.Id.spinnerLine);
            spinnerStationFrom = FindViewById<Spinner>(Resource.Id.spinnerStationFrom);
            spinnerStationTo = FindViewById<Spinner>(Resource.Id.spinnerStationTo);

            Button buttonSwap = FindViewById<Button>(Resource.Id.buttonSwap);
            buttonSwap.Click += new EventHandler(swapDirection);

            Button buttonSave = FindViewById<Button>(Resource.Id.buttonSave);
            buttonSave.Click += new EventHandler(SaveSettings);

            Button buttonSearch = FindViewById<Button>(Resource.Id.buttonSearch);
            buttonSearch.Click += new EventHandler(lookup);

            SetUpLine();
            SetUpStationFrom();
            SetUpStationTo();
        }

        public static IDbTransaction GetTransaction(DataProvider providerType, IDbConnection connection)
        {
            IDbConnection iDbConnection = null;
            IDbTransaction iDbTransaction = null;

            try
            {
                iDbConnection = connection;

                if (iDbConnection != null)
                {
                    if (iDbConnection.State != ConnectionState.Open)
                        iDbConnection.Open();

                    iDbTransaction = iDbConnection.BeginTransaction();
                }
            }

            catch (Exception ex)
            {
                iDbConnection = null;
                iDbTransaction = null;

                throw ex;
            }

            return iDbTransaction;
        }

        public static DbCommand CreateCommand(DataProvider dataProvider)
        {
            DbCommand dbCommand;

            switch (dataProvider)
            {
                case DataProvider.Odbc:
                    dbCommand = new OdbcCommand();
                    break;

                case DataProvider.OleDB:
                    dbCommand = new OleDbCommand();
                    break;

                case DataProvider.SqlServer:
                    dbCommand = new SqlCommand();
                    break;

                default:
                    dbCommand = null;
                    break;
            }

            return dbCommand;
        }

 /// <summary>
 /// Tạo id - khóa chính
 /// </summary>
 /// <param name="strFormat">Chuỗi định dạng</param>
 /// <param name="pTable">Bảng muốn tạo khóa chính</param>
 /// <returns>Khóa chính</returns>
 public string CreateId(string strFormat, string pTable)
 {
     string id;
     DataProvider dp = new DataProvider();
     DataTable dt = new DataTable();
     dt = GetAll(pTable);
     int rowCount = dt.Rows.Count + 1;
     string row;
     if (rowCount < 10)
     {
         row = "000" + rowCount.ToString();
     }
     else if (rowCount >= 10 && rowCount < 100)
     {
         row = "00" + rowCount.ToString();
     }
     else if (rowCount >= 100 && rowCount < 1000)
     {
         row = "0" + rowCount.ToString();
     }
     else
         row = rowCount.ToString();
     id = strFormat.ToString() + row.ToString();
     return id;
 }

		private static IEnumerable<OrganisationBaseObjectInfo<Position>> GetPosition(DataProvider dataProvider, PositionsReportFilter filter)
		{
			var organisationUID = Guid.Empty;
			var organisations = dataProvider.Organisations.Where(org => filter.User == null || filter.User.IsAdm || org.Value.Item.UserUIDs.Any(y => y == filter.User.UID));
			if (filter.Organisations.IsEmpty())
			{
				if (filter.IsDefault)
					organisationUID = organisations.FirstOrDefault().Key;
			}
			else
			{
				organisationUID = organisations.FirstOrDefault(org => org.Key == filter.Organisations.FirstOrDefault()).Key;
			}

			IEnumerable<OrganisationBaseObjectInfo<Position>> positions = null;
			if (organisationUID != Guid.Empty)
			{
				positions = dataProvider.Positions.Values.Where(item => item.OrganisationUID == organisationUID);

				if (!filter.UseArchive)
					positions = positions.Where(item => !item.IsDeleted);
				if (!filter.Positions.IsEmpty())
					positions = positions.Where(item => filter.Positions.Contains(item.UID));
			}
			return positions != null ? positions : new List<OrganisationBaseObjectInfo<Position>>();
		}

		protected override DataSet CreateDataSet(DataProvider dataProvider)
		{
			var filter = GetFilter<DepartmentsReportFilter>();
			var databaseService = new RubezhDAL.DataClasses.DbService();
			dataProvider.LoadCache();
			var departments = GetDepartments(dataProvider, filter);
			var uids = departments.Select(item => item.UID).ToList();
			var employees = dataProvider.GetEmployees(departments.Where(item => item.Item.ChiefUID != Guid.Empty).Select(item => item.Item.ChiefUID));
			var ds = new DepartmentsDataSet();
			departments.ForEach(department =>
			{
				var row = ds.Data.NewDataRow();
				row.Organisation = department.Organisation;
				row.Department = department.Name;
				row.Phone = department.Item.Phone;
				row.Chief = employees.Where(item => item.UID == department.Item.ChiefUID).Select(item => item.Name).FirstOrDefault();
				row.ParentDepartment = dataProvider.Departments.ContainsKey(department.Item.ParentDepartmentUID) ?
					dataProvider.Departments[department.Item.ParentDepartmentUID].Name : string.Empty;
				row.Description = department.Item.Description;
				row.IsArchive = department.IsDeleted;
				var parents = GetParents(dataProvider, department);
				row.Level = parents.Count;
				row.Tag = string.Join("/", parents.Select(item => item.UID));
				ds.Data.AddDataRow(row);
			});
			return ds;
		}

 public ActionResult Index()
 {
     var provider = new DataProvider();
     ViewBag.Actions = provider.GetCurrentAcions();
     ViewBag.HotPropositions = provider.GetHotPropositions();
     return View();
 }

 public static IDbCommand GetCommand(string sql, CommandType cmdType, int timeout
         , DbParameter[] parameters, DataProvider provider)
 {
     IDbCommand command = GetCommand(provider);
     if (parameters != null)
     {
         for (int index = 0; index < parameters.Length; index++)
         {
             DbParameter parameter = parameters[index];
             if (parameter == null)
                 continue;
             IDbDataParameter param = GetParameter(parameter.Name
                 , parameter.Direction
                 , parameter.Value
                 , parameter.DataType
                 , parameter.SourceColumn
                 , (short)parameter.Size, provider);
             command.Parameters.Add(param);
         }
     }
     command.CommandType = cmdType;
     command.CommandText = sql;
     command.CommandTimeout = timeout;
     return command;
 }

        public ActionResult Index(string id, string priceOrder)
        {
            if (string.IsNullOrEmpty(id))
                id = _userInfo.LastSelectedCategory;
            else
                _userInfo.LastSelectedCategory = id;

            var dataProvider = new DataProvider();
            var selectors = dataProvider.GetSelectors();

            ViewBag.CategorySelectors = selectors[0];
            ViewBag.EventSelectors = selectors[1];
            var bouquetes = dataProvider.GetBouquetsByCategory(id);
            if (!string.IsNullOrEmpty(priceOrder))
            {
                if (priceOrder.ToLower() == "lacne")
                    bouquetes = bouquetes.OrderBy(_ => _.Sizes.Min(size => size.Price)).ToList();
                else if (priceOrder.ToLower() == "drahe")
                    bouquetes = bouquetes.OrderByDescending(_ => _.Sizes.Max(size => size.Price)).ToList();
            }
            ViewBag.Bouquetes = bouquetes;
            ViewBag.CurrentCategory = id;
            ViewBag.CurrentOrder = priceOrder;

            return View();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            dataProvider = new DataProvider(XDocument.Load("http://trainstrains.blob.core.windows.net/xml/Lines.xml"), XDocument.Load("http://trainstrains.blob.core.windows.net/xml/Stations.xml"), XDocument.Load("http://trainstrains.blob.core.windows.net/xml/StationLines.xml"));

            lineSelection.Value = Request.Form ["DropDownListLines"];
            fromSelection.Value = Request.Form ["DropDownListFrom"];
            toSelection.Value = Request.Form ["DropDownListTo"];
        }