public P_fxrates[] GetLatestfxrates()
        {
            var db = new DataClasses1DataContext();
            CultureInfo culture;
            culture = new CultureInfo("en-Us", false);
            var fxrates_update = GetfxratesLastUpdate();
            string[] dt;
            int? upd;
            dt = fxrates_update[0].Day.Split('/');
            upd = Convert.ToInt32(fxrates_update[0].Update);
            int yyyy, mm, dd;
            yyyy = Convert.ToInt32(dt[2]);
            mm = Convert.ToInt32(dt[1]);
            dd = Convert.ToInt32(dt[0]);
            var datetime = new DateTime(yyyy, mm, dd);
            var query = from temp in db.sp_Getfxrates(datetime, upd)
                        select new P_fxrates
                        {
                            ID = temp.ID.Value.ToString(),
                            Description = temp.Description,
                            BuyingRates = temp.BuyingRates,
                            SellingRates = temp.SellingRates,
                            SightBill = temp.SightBill,
                            Family = temp.Family.Trim(),
                            FamilyLong = temp.FamilyLong,
                            Bill_DD_TT = temp.Bill_DD_TT,
                            TT = temp.TT,
                            Update = temp.Update.Value.ToString(),
                            Ddate = temp.Date.Value.ToString("d/MM/yyyy", culture),
                            DTime = temp.Time
                        };

            return query.ToArray();
        }

 // Update Account <acc> in database
 public static bool update(Account acc)
 {
     using (DataClasses1DataContext database = new DataClasses1DataContext(Globals.connectionString))
     {
         var query = from a in database.Accounts
                     // Assuming the accountNumber is enough of an identifier.
                     // Additional 'where' clauses can be added if necessary.
                     where (a.AccountID == acc.AccountID)
                     select a;
         // It seems to me that a single account renders the foreach unnecessary. However, I can't
         // find another way to get the variable 'a' from 'query'.
         foreach (var a in query)
         {
             a.AccountAddress = acc.AccountAddress;
             a.AccountFirstName = acc.AccountFirstName;
             a.AccountLastName = acc.AccountLastName;
             a.AccountNum = acc.AccountNum;
             a.AccountPhoneNum = acc.AccountPhoneNum;
             a.AccountRoutingNum = acc.AccountRoutingNum;
             a.BankID = acc.BankID;
         }
         try
         {
             database.SubmitChanges();
             return true;
         }
         catch (Exception e)
         {
             return false;
         }
     }
 }

 // Update Store <store>
 public static bool update(Store store)
 {
     using (DataClasses1DataContext database = new DataClasses1DataContext(Globals.connectionString))
     {
         var query = from a in database.Stores
                     where (a.StoreID == store.StoreID)
                     select a;
         foreach (var a in query)
         {
             a.StoreNum = store.StoreNum;
             a.StoreName = store.StoreName;
             a.StoreAddress = store.StoreAddress;
             a.StoreServiceCharge = store.StoreServiceCharge;
         }
         try
         {
             database.SubmitChanges();
             return true;
         }
         catch (Exception e)
         {
             return false;
         }
     }
 }

        private void button1_Click_1(object sender, EventArgs e)
        {
            DoctorEdit docEdit = new DoctorEdit();

            if (!String.IsNullOrEmpty(doctorBox.Text))
            {
                DataClasses1DataContext editContext = new DataClasses1DataContext();

                var doctor = (from doc in editContext.Doctors
                              where doc.doctorName == doctorBox.Text
                              select doc).First();

                docEdit.oldDoctorName.Text = doctor.doctorName;
                docEdit.oldInitials.Text = doctor.doctorID;
                docEdit.oldGloves.Text = doctor.gloveSize.ToString();
                docEdit.newDoctorName.Text = doctor.doctorName;
                docEdit.newInitials.Text = doctor.doctorID;
                docEdit.newGloves.Text = doctor.gloveSize.ToString();

                docEdit.ShowDialog(this);

                if (docEdit.DialogResult == DialogResult.OK)
                {
                    Form1.DoctorsList.Clear();
                    Form1.GetDoctors();
                    loadDoctors();
                }
            }
            else
            {
                MessageBox.Show("Please select a doctor from the list", "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            categoryName = Request["category"];
            productName = Request["product"];
            using (var db = new DataClasses1DataContext())
            {
                if (categoryName == "野菜類")
                {
                    var q = from x in db.YasaiKensa
                            where x.食品カテゴリ == categoryName.Normalize() && x.野菜品名 == productName.Normalize()
                            orderby x.採取日D descending
                            select x;
                    list = q.ToList();
                }
                else
                {
                    var q = from x in db.YasaiKensa
                            where x.食品カテゴリ == categoryName.Normalize() && x.品目 == productName.Normalize()
                            orderby x.採取日D descending
                            select x;
                    list = q.ToList();
                }

                bqByDayImage.ImageUrl = String.Format("ProductChartImage.ashx?category={0}&product={1}", HttpUtility.UrlEncode(categoryName), HttpUtility.UrlEncode(productName));
                prefImage.ImageUrl = list.PreparePrefURL(300,300);
            }
            Title = String.Format("{0}({1}) | 食品の放射能検査データ", productName, categoryName);
        }

 public static void AddCommoditypopularityByID(Guid id)
 {
     DataClasses1DataContext dd = new DataClasses1DataContext();
     //在存储过程中加1了？？？
     //dc.updatePopularityCommodity(id);
     dd.updatePopularityCommodity(id);
 }

        private void calcButton_Click(object sender, EventArgs e)
        {
            try
            {
                //Get input data from text boxes
                int minStock = Convert.ToInt32(minStockBox.Text);
                int maxStock = Convert.ToInt32(maxStockBox.Text);
                int day = Convert.ToInt32(daysBox.Text);
                int endDay = Convert.ToInt32(endDayBox.Text);

                //testing of input data
                if (minStock < 0 || maxStock < 0 || endDay < 1 || day < 1 || maxStock <= minStock)
                    throw new Exception("Invalid data");

                //Calc Purchase table and parse function return
                DataClasses1DataContext dc = new DataClasses1DataContext();
                switch (dc.CalcMRP(1, endDay, minStock, day, maxStock))
                {
                    case 1:
                        throw new Exception("StOck overflow");
                    case 2:
                        throw new Exception("Very little stock");
                }
                this.purchaseTableAdapter.Fill(this.assyPartDS.Purchase);
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        public JsonResult habitacionesdisponibles(string fechai, string fechaf)
        {
            DataClasses1DataContext db = new DataClasses1DataContext();

            string fii = fechai.Substring(3, 3) + fechai.Substring(0, 3) + fechai.Substring(6, 4);
            string fnn = fechaf.Substring(3, 3) + fechaf.Substring(0, 3) + fechaf.Substring(6, 4);
            DateTime fi = DateTime.Parse(fii), fn = DateTime.Parse(fnn);

            var numero = (db.habitacions.Where(h => db.ocupado(h.numero, fi, fn).Value).Select(h => h.numero.ToString())).ToArray();
            var tipo = (db.habitacions.Where(h => db.ocupado(h.numero, fi, fn).Value).Select(h => h.tip_hab.tipo)).ToArray();
            var precio = (db.habitacions.Where(h => db.ocupado(h.numero, fi, fn).Value).Select(h => (Convert.ToInt32(h.precio)).ToString())).ToArray();
            var q = db.habitacions.Where(h => db.ocupado(h.numero, fi, fn).Value).Select(h => h.numero);
            var ress = q.ToArray();
            var rr = Json(ress);
            int t = numero.Count();
            string[,] cadena = new string[t, 3];
            for (int i = 0; i < t; i++)
            {
                cadena[i, 0] = numero[i];
                cadena[i, 1] = tipo[i];
                cadena[i, 2] = precio[i];
            }

            var cad = Json(cadena);
            return cad;
        }

        public bool FindNewImages(Product product)
        {
            d = new DataClasses1DataContext();
            bannedHosts = d.BannedImagesSites.Select(t => t.BannedHost).ToList();
            bannedImages = d.BannedImages.Select(t => t.BannedImage1).ToList();
            potentialImages.Clear();

            var productName = product.ProductFullName;
            foreach (var c in chars)
            {
                productName = productName.Replace(c, " ");
            }

            var searchURL = "http://ajax.googleapis.com/ajax/services/search/images?v=1.0&q=" + productName;
            var findResult = PageEncoding.GetPage(searchURL, null, null, ip, port);
            if (findResult == null || findResult.Contains("Bot or Human"))
            {
                return false;
            }

            // выбираем самую большую
            GetImagesList(findResult);
            foreach (var potentialImage in potentialImages.OrderByDescending(t => t.ContentLength).ToList())
            {
                var result = GetImage(potentialImage);
                if (result)
                {
                    //imageHost = potentialImage.ResponseUri.Host;
                    imageHost = potentialImage.ResponseUri.AbsoluteUri.Replace("www.", "");
                    return true;
                }
            }
            return false;
        }

        public ActionResult Details(int id)
        {
            DataClasses1DataContext db = new DataClasses1DataContext();

            OrderAndServies orderservies = new OrderAndServies();
            List<ListServiesModif> listServies = new List<ListServiesModif>();

            /*
            List<list_add_services> servies = new List<list_add_services>();

            //var order = db.Orders.Where(item => item.Id_order == id);
              var order = db.ExecuteQuery<Order>(@"SELECT * FROM Orders WHERE Id_order = " + id + ";");
              Order ddd = order.First();
            */

            var ttt = db.ExecuteQuery<OrderModific>(@"SELECT [Orders].[Id_order], [Orders].[date], [Orders].[begin], [Orders].[end], [Client].[FIO] FROM [Orders], [Client] WHERE Orders.Id_order = '" + id + "'  AND Orders.id_client = Client.Id_client;").ToList<OrderModific>().First();

            //listServies = db.ExecuteQuery<ListServiesModif>(@"SELECT CAST([list_add_services].[Id_list_add_services] AS NVARCHAR(100)),	CAST([list_add_services].[id_order] AS NVARCHAR(100)),	CAST([type_servies].[name] AS NVARCHAR(100)) FROM [list_add_services], [type_servies], [Orders] WHERE [list_add_services].[id_servies] = [type_servies].[Id_servies] AND [list_add_services].[id_order] = '" + id + "'GROUP BY 	CAST([list_add_services].[Id_list_add_services] AS NVARCHAR(100)), CAST([list_add_services].[Id_order] AS NVARCHAR(100)), CAST([type_servies].[name] AS NVARCHAR(100));").ToList<ListServiesModif>();

            //listServies = db.ExecuteQuery<ListServiesModif>(@"SELECT CAST([list_add_services].[Id_list_add_services] AS NVARCHAR(100)),	CAST([list_add_services].[id_order] AS NVARCHAR(100)),	CAST([type_servies].[name] AS NVARCHAR(100)) FROM [list_add_services], [type_servies], [Orders] WHERE [list_add_services].[id_servies] = [type_servies].[Id_servies] AND [list_add_services].[id_order] = '" + id + "'GROUP BY 	CAST([list_add_services].[Id_list_add_services] AS NVARCHAR(100)), CAST([list_add_services].[Id_order] AS NVARCHAR(100)), CAST([type_servies].[name] AS NVARCHAR(100));").ToList<ListServiesModif>();

            //listServies = db.ExecuteQuery<ListServiesModif>(@"SELECT [list_add_services].[Id_list_add_services], [list_add_services].[id_order],	CAST([type_servies].[name] AS NVARCHAR(100)) AS 'name' FROM [list_add_services], [type_servies], [Orders] WHERE [list_add_services].[id_servies] = [type_servies].[Id_servies] AND [list_add_services].[id_order] = '" + id + "' GROUP BY [list_add_services].[Id_list_add_services],	[list_add_services].[Id_order],	CAST([type_servies].[name] AS NVARCHAR(100));").ToList<ListServiesModif>();

            /*
            orderservies.order = ttt;
            orderservies.ListServirecs = listServies;
            */
            return View(orderservies);
        }

        private void btn_deletar_Click(object sender, EventArgs e)
        {
            try
            {

                // metodo para pega index  dgw_funcionario.CurrentCell.RowIndex.ToString()
                int selecionado = dgw_funcionario.CurrentCell.RowIndex;

                int idfunc;

                idfunc = int.Parse(dgw_funcionario.Rows[selecionado].Cells["id_funcionario"].Value.ToString());

                DataClasses1DataContext db = new DataClasses1DataContext();

                var funci = from funcionario in db.tblfuncionarios
                            where funcionario.id_funcionario.Equals(idfunc)
                            select funcionario;

                tblfuncionario fun = funci.Single();

                db.tblfuncionarios.DeleteOnSubmit(fun);
                db.SubmitChanges();

                MessageBox.Show("Deletado com sucesso");

                Funcionarios.PreecherGridview(dgw_funcionario);

            }
            catch
            {
                MessageBox.Show("Ocorreu um erro!");
            }
        }

        public void formFuncionario_Load(object sender, EventArgs e)
        {
            DataClasses1DataContext  db = new DataClasses1DataContext();
            var func = from funcionario in db.tblfuncionarios
                       select funcionario;

            dgw_funcionario.DataSource = func ;

            dgw_funcionario.Columns["id_funcionario"].HeaderText = "ID";
            dgw_funcionario.Columns["nome"].HeaderText = "Nome ";
            dgw_funcionario.Columns["sobrenome"].HeaderText = "Sobrenome";
            dgw_funcionario.Columns["cpf"].HeaderText = "CPF";
            dgw_funcionario.Columns["rg"].HeaderText = "RG";
            dgw_funcionario.Columns["ctps"].HeaderText = "CTPS";
            dgw_funcionario.Columns["data_admissao"].HeaderText = "Data Admissão";
            dgw_funcionario.Columns["renumeracao_mensal"].HeaderText = "Renumeração Mensal";
            dgw_funcionario.Columns["funcao"].HeaderText = "Função";
            dgw_funcionario.Columns["endere"].HeaderText = "Endereço";
            dgw_funcionario.Columns["bairro"].HeaderText = "Bairro";
            dgw_funcionario.Columns["email"].HeaderText = "E-Mail";
            dgw_funcionario.Columns["obs"].HeaderText = "Observação";
            dgw_funcionario.Columns["sexo"].HeaderText = "Sexo";
            // colocando nome nas coluna do datagrid

               // barra rodape

            toolStripStatusLabel1.Text = dgw_funcionario.RowCount + " Funcionários Cadastrado.";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if(!String.IsNullOrWhiteSpace(textBox1.Text) &&
                    !String.IsNullOrWhiteSpace(textBox2.Text) &&
                    !String.IsNullOrWhiteSpace(textBox3.Text))
                {
                    DataClasses1DataContext doctorContext= new DataClasses1DataContext();
                    Doctor doctor = new Doctor();
                    doctor.doctorName = textBox1.Text;
                    doctor.doctorID = textBox2.Text;
                    doctor.gloveSize = Decimal.Parse(textBox3.Text);
                    doctorContext.Doctors.InsertOnSubmit(doctor);

                    doctorContext.SubmitChanges();
                    textBox1.Text = "";
                    textBox2.Text = "";
                    textBox3.Text = "";
                    Form1.GetDoctors();
                    loadDoctors();
                }
                else
                {
                    MessageBox.Show("Cannot be empty...", "Error",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

            }
            catch
            {
                MessageBox.Show("Something went wrong, does the doctor already exist? Please try again...", "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public ActionResult upload(ArchivoFile datos)
        {
            string rutafisica = Server.MapPath("~/csv");
            string rt = rutafisica + @"\" + datos.archivo.FileName;
            datos.archivo.SaveAs(rutafisica + @"\" + datos.archivo.FileName);
            DataClasses1DataContext db = new DataClasses1DataContext();
            archivo ar = new archivo()
            {
                ruta_fisica = rutafisica + @"\" + datos.archivo.FileName,
                fecha = DateTime.Now
            };
            db.archivos.InsertOnSubmit(ar);
            db.SubmitChanges();
            CsvReader csv = new CsvReader(new StreamReader(rt), true);
            //int total = csv.FieldCount;
            //string[] headers = csv.GetFieldHeaders;
            List<reservasC> listares = new List<reservasC>();
            while (csv.ReadNextRecord())
            {
                reservasC res = new reservasC()
                {
                    nombreCliente = csv[0],
                    numHab = csv[1],
                    fechaI = csv[2],
                    fechaF = csv[3]

                };
                listares.Add(res);
            }
            ViewBag.lista = listares;
            return View();
        }

 // Update Check <check>
 public static bool update(Check check)
 {
     using (DataClasses1DataContext database = new DataClasses1DataContext(Globals.connectionString))
     {
         var query = from a in database.Checks
                     where (a.CheckID == check.CheckID)
                     select a;
         foreach (var a in query)
         {
             a.CheckAmount = check.CheckAmount;
             a.CheckAmountOwed = check.CheckAmountOwed;
             a.CheckCashierID = check.CheckCashierID;
             a.CheckDate = check.CheckDate;
             a.CheckDeleted = check.CheckDeleted;
             a.CheckNum = check.CheckNum;
             a.CheckPaidDate = check.CheckPaidDate;
         }
         try
         {
             database.SubmitChanges();
             return true;
         }
         catch (Exception e)
         {
             return false;
         }
     }
 }

 // Delete Bank <bank> from database
 public static bool delete(Bank bank)
 {
     using (DataClasses1DataContext database = new DataClasses1DataContext(Globals.connectionString))
     {
         var query = from a in database.Banks
                     where (a.BankID == bank.BankID)
                     select a;
         // It seems to me that a single account renders the foreach unnecessary. However, I can't
         // find another way to get the variable 'a' from 'query'.
         foreach (var a in query)
         {
             database.Banks.DeleteOnSubmit(a);
             try
             {
                 database.SubmitChanges();
                 return true;
             }
             catch (Exception e)
             {
                 return false;
             }
         }
         return false;
     }
 }

        public ActionResult Index(ReservaHab model)
        {
            DataClasses1DataContext db = new DataClasses1DataContext();
            reserva reserr = new reserva();
            System.Guid id = System.Guid.Parse((System.Web.Security.Membership.GetUser(User.Identity.Name).ProviderUserKey).ToString());
            reserr.idusuario = id;
            var cliiii = 0;
            if (model.tipocli2 == 1)
            {
                db.personas.Where(a => (a.cliente.nombre + " " + a.apellido).ToString() == model.buscarpersona.ToString()).Select(a => a.id).First();
            }
            if (model.tipocli2 == 2)
            {
                db.empresas.Where(a => a.cliente.nombre.ToString() == model.buscarempresa.ToString()).Select(a => a.id).First();
            }
            if (model.tipocli2 == 3)
            {
                db.agencias.Where(a => a.cliente.nombre.ToString() == model.buscaragencia.ToString()).Select(a => a.id).First();
            }
            reserr.idcli = cliiii;

            reserr.idHab = model.idhab;
            string ini = model.Fecha_I.Substring(3, 3) + model.Fecha_I.Substring(0, 3) + model.Fecha_I.Substring(6, 4);
            string fin = model.Fecha_F.Substring(3, 3) + model.Fecha_F.Substring(0, 3) + model.Fecha_F.Substring(6, 4);
            reserr.fecha_ini = DateTime.Parse(ini);
            reserr.fecha_fin = DateTime.Parse(fin);
            reserr.estado = "pendiente";
            var ppago = db.habitacions.Where(a => a.numero == model.idhab).Select(a => a.precio).First();
            reserr.pago = ppago * model.CantD;
            db.reservas.InsertOnSubmit(reserr);
            db.SubmitChanges();
            return View();
        }

        public static void FlowStatics()
        {
            System.IO.StreamWriter sw = new System.IO.StreamWriter("log.txt", false);
            DataClasses1DataContext mydb = new DataClasses1DataContext(common.connString);
            var message = mydb.LA_update1.Select(e => e.ip_version_MsgType).Distinct();
            Dictionary<string, int> myDic = new Dictionary<string, int>();
            foreach (string m in message)
            {
                myDic.Add(m, 0);
                Console.Write(m + "--------------------");
                Console.WriteLine(0);

                sw.Write(m + "--------------------");
                sw.WriteLine(0);

            }
            sw.Flush();

            List<string> startmessage = new List<string>();
            startmessage.Add("DTAP MM.Location Updating Request");
            startmessage.Add("DTAP MM.CM Service Request");
            startmessage.Add("DTAP RR.Paging Response");
            startmessage.Add("BSSMAP.Handover Request");


            foreach (var start in startmessage)
            {
                Dictionary<string, int> newDic = new Dictionary<string, int>();
                foreach (KeyValuePair<string, int> pair in myDic)
                    newDic.Add(pair.Key, 0);

                var a = from p in mydb.LA_update1
                        where p.ip_version_MsgType == start
                        select p.opcdpcsccp;

                foreach (var b in a)
                {
                    foreach (KeyValuePair<string, int> kvp in myDic)
                    {
                        var c = mydb.LA_update1.Where(e => e.opcdpcsccp == b).Where(e => e.ip_version_MsgType == kvp.Key).FirstOrDefault();
                        if (c != null)
                            newDic[kvp.Key] = newDic[kvp.Key] + 1;
                    }
                }

                foreach (var m in newDic.OrderByDescending(e => e.Value))
                {
                    Console.Write(m.Key+"--------------------");
                    Console.WriteLine(m.Value);

                    sw.Write(m.Key + "--------------------");
                    sw.WriteLine(m.Value);
                }

                sw.Flush();
                
            }
            sw.Close();
        }

 protected void Page_Load(object sender, EventArgs e)
 {
     using (var db = new DataClasses1DataContext())
     {
         SRList =  db.ShippingRestriction.ToList();
         SRProducts=  db.ShippingRestrictedProduct.ToList();
         SRCities = db.ShippingRestrictedCity.ToList();
     }
 }

 protected void Page_Load(object sender, EventArgs e)
 {
     //if (Page.IsPostBack == false)
     {
         using (var db = new DataClasses1DataContext())
         {
             list = db.Temp_YasaiKensa.ToList();
         }
     }
 }