C# Database.DataClasses1DataContext类代码示例

        /// <summary>
        /// Loescht eine Auftragsposition und ggf. die Amtlichen Gebuehren dazu
        /// </summary>
        /// <param name="dbContext">DB Kontext</param>
        /// <param name="orderItemId">AuftragspositionID</param>
        public static void RemoveOrderItem(DataClasses1DataContext dbContext, int orderItemId)
        {
            var orderItemToDelete = dbContext.OrderItem.FirstOrDefault(q => q.Id == orderItemId);
            if (orderItemToDelete != null)
            {

                if (orderItemToDelete.Status > (int)OrderItemStatusTypes.Open)
                    throw new Exception("Der Auftragsstatus ist nicht mehr Offen, löschen nicht möglich");
                if (orderItemToDelete.Order.DocketList != null)
                    throw new Exception("Laufzettel wurde bereits erstellt, löschen nicht möglich");
                if (orderItemToDelete.Order.PackingList != null)
                    throw new Exception("Lieferschein wurde bereits erstellt, löschen nicht möglich");

                var itemsAnzahl = dbContext.OrderItem.Count(q => q.Id != orderItemId && q.SuperOrderItemId != orderItemId && q.OrderNumber == orderItemToDelete.OrderNumber);
                if (itemsAnzahl == 0)
                    throw new Exception("Mind. eine Position muss pro Auftrag verfügbar sein");

                var hasChildItems = dbContext.OrderItem.FirstOrDefault(q => q.SuperOrderItemId == orderItemToDelete.Id);
                dbContext.OrderItem.DeleteOnSubmit(hasChildItems);

                if (orderItemToDelete.SuperOrderItemId.HasValue == true)
                {
                    RemoveOrderItem(dbContext, orderItemToDelete.SuperOrderItemId.Value);
                }

                dbContext.OrderItem.DeleteOnSubmit(orderItemToDelete);
                dbContext.WriteLogItem("Auftragsposition " + orderItemToDelete.ProductName + " mit der Auftragsnummer " + orderItemToDelete.Order.OrderNumber + " wurde gelöscht.",
                    LogTypes.DELETE, orderItemToDelete.Id, "OrderItem");
            }
        }

        /// <summary>
        /// Gibt alle Herstellern by HSN zurück
        /// </summary>
        /// <param name="hsn">HSN des Herstellers.</param>
        /// <param name="dbContext">Datenbankkontext für die Transaktion.</param>
        /// <returns>Herstellers</returns>
        public static string GetMakeByHSN(string hsn)
        {
            string hsnResult = string.Empty;
            bool atLeastOneHSNFound = false;

            DataClasses1DataContext dbContext = new DataClasses1DataContext();
            var hsnQuery = from make in dbContext.Make
                           where make.HSN == hsn
                           select make.Name;

            foreach (string hsnName in hsnQuery)
            {
                atLeastOneHSNFound = true;
                hsnResult += hsnName + " ";
            }

            hsnResult = "Hersteller: " + hsnResult;

            if (atLeastOneHSNFound == false)
            {
                hsnResult = "Wir haben kein Hersteller mit dem Nummer " + hsn + " gefunden";
            }

            return hsnResult;
        }

 /// <summary>
 /// Erstellt einen neuen Rechnungslauf Datensatz
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 protected void GenerateInvoiceRunButton_Click(object sender, EventArgs e)
 {
     InvoiceRunError.Text="";
     using (DataClasses1DataContext dbContext = new DataClasses1DataContext())
     {
         try
         {
             Guid? customerId=null;
             Guid? invoiceType = null;
              if(CustomerDropDownList.SelectedValue != string.Empty)customerId=new Guid(CustomerDropDownList.SelectedValue);
              if (RechnungsTypComboBox.SelectedValue != string.Empty && RechnungsTypComboBox.Enabled==true) invoiceType = new Guid(RechnungsTypComboBox.SelectedValue);
              InvoiceRunReport run = new InvoiceRunReport()
              {
                  Id = Guid.NewGuid(),
                  CustomerId = customerId,
                  InvoiceTypeId = invoiceType,
                  CreateDate = DateTime.Now
              };
              dbContext.InvoiceRunReport.InsertOnSubmit(run);
              dbContext.SubmitChanges();
              RadGridInvoiceRun.MasterTableView.ClearChildEditItems();
              RadGridInvoiceRun.MasterTableView.ClearEditItems();
              RadGridInvoiceRun.Rebind();
         }
         catch (Exception ex)
         {
             InvoiceRunError.Visible = true;
             InvoiceRunError.Text = "Rechnungslauf Fehler " + ex.Message;
             dbContext.WriteLogItem("Rechnungslauf Error " + ex.Message, LogTypes.ERROR, "Rechnungslauf");
         }
     }
 }

 protected void ChangeSaveBtn_Click(object sender, EventArgs e)
 {
     DataClasses1DataContext dbContext = new DataClasses1DataContext(Int32.Parse(Session["CurrentUserId"].ToString())); // hier kommt die Loggingid
     try
     {
         if (txbNewPassword.Text == txbRepeatPWD.Text)
         {
             var thisUser = dbContext.User.SingleOrDefault(q => q.Id == Int32.Parse(Session["CurrentUserId"].ToString()));
             if (thisUser != null)
             {
                 thisUser.ChangePassword(txbNewPassword.Text, txbOldPWD.Text, dbContext);
                 dbContext.SubmitChanges();
                 RadWindowManagerChangePassword.RadAlert("Das Passwort wurde erfolgreich geändert", 380, 180, "Info", "");
             }
             else
             {
                 Response.Redirect("../login.aspx");
             }
         }
         else
         {
             FailureText.Text = "Die neuen Passwörter stimmen nicht überein";
         }
     }
     catch (Exception ex)
     {
         FailureText.Text = ex.Message;
         dbContext.WriteLogItem("ChangePassowrd Error:  " + ex.Message, LogTypes.ERROR, "User");
     }
 }

        public Kundendaten GetCustomerProductInformation(string username, string password, string InternalId)
        {
            Kundendaten knd = new Kundendaten();
            List<Guid> allowedCustomers = new List<Guid>();
            try
            {
                allowedCustomers = CheckLogin.CheckUser(username, password, InternalId);
                if (allowedCustomers.Count > 0)
                {
                    using (DataClasses1DataContext dbContext = new DataClasses1DataContext(new Guid(ConfigurationManager.AppSettings["currentUser"])))
                    {
                        dbContext.WriteLogItem("Zugriff von der IP Adresse: " + this.Context.Request.UserHostAddress + "Webservice GetCustomerInformation()", LogTypes.INFO);
                        ValidateOrderType.GetCutomerInformation(out knd, allowedCustomers, dbContext);
                    }
                }
                else
                {
                    throw new Exception("Für Ihre Daten konnten keine Kunden gefunden werden!");
                }
            }
            catch(Exception ex)
            {
                throw new Exception("Fehler beim verabeiten der Daten, bitte wiederholen Sie den Vorgang" + Environment.NewLine + "Fehlermeldung: " + ex.Message);
            }

            return knd;
        }

        /// <summary>
        /// Erstelle zu der jeweiligen Rechnung ein neues Erlöskonto
        /// </summary>
        /// <param name="dbContext">DB Kontext</param>
        /// <param name="inv">Rechnungsobjekt</param>
        public static void CreateAccounts(DataClasses1DataContext dbContext, Invoice inv)
        {
            List<_Accounts> acc = null;
             acc = Accounts.generateAccountNumber(dbContext,inv.Id).ToList();

             if (acc != null && acc.Count() == inv.InvoiceItem.Count)
             {
                 foreach (var thisItems in acc)
                 {
                     var myAccount = new InvoiceItemAccountItem
                     {
                         InvoiceItemId = thisItems.InvoiceItemId,
                         RevenueAccountText = thisItems.AccountNumber
                     };
                     var contains = dbContext.InvoiceItemAccountItem.FirstOrDefault(q => q.InvoiceItemId ==
                         thisItems.InvoiceItemId && q.RevenueAccountText == thisItems.AccountNumber.Trim());
                     if (contains != null)
                     {
                         contains.RevenueAccountText = thisItems.AccountNumber.Trim();
                     }
                     else
                     {
                         dbContext.InvoiceItemAccountItem.InsertOnSubmit(myAccount);
                     }
                     dbContext.SubmitChanges();
                 }
             }
             else
             {
                 throw new Exception("Die Rechnung konnte nicht gedruckt werden, da nicht alle Dienstleistungen ein Erlöskonto haben! Sie können die Erlöskonten im Reiter 'Rechnung erstellen' zuweisen und die Rechnung erneut drucken.");
             }
        }

 public static void CreateAccount(Guid? AccountNumber, Price _object, DataClasses1DataContext dbContext, bool withLocation = false)
 {
     if (AccountNumber != null)
        {
        IQueryable<PriceAccount> myAccount = null;
        if (withLocation == false)
        {
            myAccount = dbContext.PriceAccount.Where(q => q.PriceId == _object.Id && q.Price.LocationId == null);
        }
        else
        {
            myAccount = dbContext.PriceAccount.Where(q => q.PriceId == _object.Id && q.Price.LocationId == _object.LocationId);
        }
        if (myAccount.Count() > 0)
        {
            dbContext.PriceAccount.DeleteAllOnSubmit<PriceAccount>(myAccount);
            dbContext.SubmitChanges();
        }
        if (myAccount.Count() == 0)
        {
            var myNewAccount = new PriceAccount
            {
                PriceId = _object.Id,
                AccountId = AccountNumber.Value
            };
            dbContext.PriceAccount.InsertOnSubmit(myNewAccount);
            dbContext.SubmitChanges();
        }
        }
 }

 //Checked if amt.gebühr UND mind.eine Dienstleistung vorhanden ist
 protected bool CheckDienstleistungAndAmtGebuhr(Guid orderId)
 {
     bool DienstVorhanden = false;
     bool AmtGebuhVorhanden = false;
     using (DataClasses1DataContext dbContext = new DataClasses1DataContext(new Guid(Session["CurrentUserId"].ToString())))
     {
         var searchOrderQuery = dbContext.Order.SingleOrDefault(q => q.Id == orderId);
         if (searchOrderQuery != null)
         {
             foreach (OrderItem item in searchOrderQuery.OrderItem)
             {
                 if (item.IsAuthorativeCharge == true)
                 {
                     AmtGebuhVorhanden = true;
                 }
                 else if (item.IsAuthorativeCharge == false)
                 {
                     DienstVorhanden = true;
                 }
             }
         }
     }
     if (AmtGebuhVorhanden == true && DienstVorhanden == true)
         return true;
     else
         return false;
 }

 protected void RadGridAllChanges_DetailTableDataBind(object source, GridNeedDataSourceEventArgs e)
 {
     DataClasses1DataContext dbContext = new DataClasses1DataContext();
     try
     {
         RadGrid sender = source as RadGrid;
         Panel item = sender.Parent as Panel;
         Label lblReferenceId = item.FindControl("lblReferenceId") as Label;
         Label lblTableName = item.FindControl("lblTableName") as Label;
         DataTable t = new DataTable();
         if (lblReferenceId.Text != string.Empty || lblTableName.Text != string.Empty)
         {
             var tableColumns = dbContext.ChangeLogColumNames.SingleOrDefault(q => q.TableName == lblTableName.Text);
             string thisSelect = "";
             if (tableColumns != null)
             {
                 string idName = tableColumns.IdColumnName;
                 foreach (var col in tableColumns.ColumnNames.Split(';'))
                 {
                     if (thisSelect == string.Empty)
                     {
                         thisSelect = "[" + col + "]";
                     }
                     else
                     {
                         thisSelect += ",[" + col + "]";
                     }
                 }
                 dbContext.Connection.Close();
                 string myQuery = "";
                 if (thisSelect != string.Empty && idName != string.Empty && lblTableName.Text != string.Empty)
                 {
                     myQuery = "select " + thisSelect + " from [" + lblTableName.Text + "] where " + idName + " ='" + lblReferenceId.Text + "'";
                     using (SqlConnection c = new SqlConnection(dbContext.Connection.ConnectionString))
                     {
                         c.Open();
                         using (SqlDataAdapter a = new SqlDataAdapter(myQuery, c))
                         {
                             a.Fill(t);
                         }
                     }
                     sender.DataSource = t;
                 }
             }
             else
             {
                 setEmptyDataSet(((RadGrid)sender));
             }
         }
         else
         {
             setEmptyDataSet(((RadGrid)sender));
         }
     }
     catch (Exception ex)
     {
         dbContext.WriteLogItem("Fehler beim generieren des ChangeLogs Details. " + ex.Message, LogTypes.ERROR);
         dbContext.SubmitChanges();
     }
 }

 protected void AllChangesLinq_Selected(object sender, LinqDataSourceSelectEventArgs e)
 {
     DataClasses1DataContext con = new DataClasses1DataContext();
     var changes = from chang in con.ChangeLog
                   let tableName = chang.TableName
                   let status = chang.TableProperty
                   let allStates = con.OrderStatus
                   let myOrder = con.Order
                   let myOrderItem=con.OrderItem
                   let Ref = (!chang.ReferenceId.HasValue)?Guid.Empty: chang.ReferenceId.Value
                   where chang.TableName == "Order" || chang.TableName == "OrderItem"
                   select new OrderLogging
                   {
                       LogId = chang.Expr1,
                       TableName = tableName,
                       Name = chang.Name,
                       FirstName = chang.FirstName,
                       Login = chang.Login,
                       Type= tableName=="Order" ? "Auftrag":"Auftragsposition",
                       Date = chang.Date,
                       ReferenceId = (!chang.ReferenceId.HasValue)?Guid.Empty: chang.ReferenceId.Value,
                       TranslatedText = TranslatedText(status,chang.Text, allStates),
                       OrderNumber = tableName == "Order" ? myOrder.FirstOrDefault(q => q.Id == Ref).Ordernumber : myOrderItem.FirstOrDefault(q => q.Id == Ref).Order.Ordernumber
                   };
     changes = changes.OrderByDescending(q=>q.OrderNumber);
     e.Result = changes;
 }

 protected void bSaveClick(object sender, EventArgs e)
 {
     ResetErrorLabels();
     if (checkFields() == true)
     {
         DataClasses1DataContext dbContext = new DataClasses1DataContext(((Guid)Session["CurrentUserId"]));
         try
         {
             var createUser = KVSCommon.Database.User.CreateUser(txbUserLogin.Text, txbUserPassword1.Text, txbUserNachname.Text, txbUserVorname.Text, txbUserTitle.Text, dbContext);
             if (hasContactData() == true)
             {
                 var createContact = Contact.CreateContact(txbUserPhone.Text, txbUserFax.Text, txbUserMobile.Text, txbUserEmail.Text, dbContext);
                 createUser.Contact = createContact;
             }
             dbContext.SubmitChanges();
             RadWindowManagerCreateUser.RadAlert("Der Benutzer wurde erfolgreich angelegt!", 380, 180, "Info", "");
             txbUserLogin.Text=""; txbUserPassword1.Text=""; txbUserNachname.Text="";
             txbUserVorname.Text = ""; txbUserTitle.Text = "";
             txbUserPhone.Text = ""; txbUserFax.Text = ""; txbUserMobile.Text = ""; txbUserEmail.Text = "";
         }
         catch (Exception ex)
         {
             RadWindowManagerCreateUser.RadAlert(Server.HtmlEncode(ex.Message).RemoveLineEndings(), 380, 180, "Fehler", "");
             try
             {
                 dbContext.WriteLogItem("Create User Error " + ex.Message, LogTypes.ERROR, "User");
                 dbContext.SubmitChanges();
             }
             catch { }
         }
     }
 }

 protected void AllChangesLinq_Selected(object sender, LinqDataSourceSelectEventArgs e)
 {
     DataClasses1DataContext con = new DataClasses1DataContext();
     var changes = from chang in con.ChangeLog orderby chang.Expr1 descending
                   select chang;
     e.Result = changes;
 }

        // public void cmbCustomerProducts_OnLoad(object sender, EventArgs e)
        public void cmbCustomerProducts_OnLoad(RadComboBox Box)
        {
            using (DataClasses1DataContext dbContext = new DataClasses1DataContext())
            {
                try
                {
                    if (Session["editableProductId"] != null && !String.IsNullOrEmpty(Session["editableProductId"].ToString()))
                    {
                        string myProductId = Session["editableProductId"].ToString();
                        Box.Visible = true;

                        var myCustomers = from cust in dbContext.Customer
                                          join lCust in dbContext.LargeCustomer on cust.Id equals lCust.CustomerId
                                          orderby cust.Name ascending
                                          select new
                                          {
                                              CustomerId = cust.Id,
                                              CustomerName = String.IsNullOrEmpty(cust.MatchCode) ? cust.Name : cust.Name + "(" + cust.MatchCode + ")",
                                              IsChecked = dbContext.CustomerProduct.SingleOrDefault(q => q.ProductId == Int32.Parse(myProductId) && q.CustomerId == cust.Id) != null ? true : false
                                          };

                        Box.DataSource = myCustomers;
                        Box.DataBind();
                    }
                }
                catch (Exception ex)
                {
                    dbContext.WriteLogItem("Product Error " + ex.Message, LogTypes.ERROR, "Product");
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// Erstellt einen Laufkunden.
        /// </summary>
        /// <param name="name">Name des Kunden.</param>
        /// <param name="firstName">Vorname des Kunden.</param>
        /// <param name="title">Anrede.</param>
        /// <param name="gender">Geschlecht.</param>
        /// <param name="street">Straße.</param>
        /// <param name="streetnumber">Hausnummer.</param>
        /// <param name="zipcode">Postleitzahl.</param>
        /// <param name="city">Straße.</param>
        /// <param name="country">Land.</param>
        /// <param name="phone">Telefonnummer.</param>
        /// <param name="fax">Faxnummer.</param>
        /// <param name="mobilephone">Mobiltelefonnummer.</param>
        /// <param name="email">Emailaddresse.</param>
        /// <param name="vat">Mehrwersteuersatz für den Kunden.</param>
        /// <param name="termOfCredit">Zahlungsziel (in Tagen).</param>
        /// <param name="customerNumber">Kundennummer.</param>
        /// <param name="dbContext">Datenbankkontext für die Transaktion.</param>
        /// <returns>Den neuen Laufkunden.</returns>
        public static SmallCustomer CreateSmallCustomer(string name, string firstName, string title, string gender, string street, string streetnumber, string zipcode, string city, 
            string country, string phone, string fax, string mobilephone, string email, decimal vat, int? termOfCredit, string customerNumber, DataClasses1DataContext dbContext)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentNullException("Der Name darf nicht leer sein.");
            }

            var customer = Customer.CreateCustomer(name, street, streetnumber, zipcode, city, country, phone, fax, mobilephone, email, vat, termOfCredit, customerNumber);
            customer._dbContext = dbContext;
            var person = new Person()
            {
                FirstName = firstName,
                Name = name,
                Gender = gender,
                Title = title
            };

            var smallCustomer = new SmallCustomer()
            {
                Customer = customer,
                Person = person
            };

            dbContext.SmallCustomer.InsertOnSubmit(smallCustomer);
            dbContext.SubmitChanges();
            dbContext.WriteLogItem("Kunde " + firstName + " " + name + " wurde angelegt.", LogTypes.INSERT, customer.Id, "SmallCustomer");

            return smallCustomer;
        }

        protected void GetAllCustomerDataSource_Selecting(object sender, LinqDataSourceSelectEventArgs e)
        {
            DataClasses1DataContext dbContext = new DataClasses1DataContext();

            var query = from cust in dbContext.Customer
                        select cust;
            e.Result = query;
        }