LINQ Insert Example

Here is the lINQ example I got it to work. It was fairly simple. We needed to Use the "InsertOnSubmit" method. Also to get the foreign key we just needed to point to the first person object. LINQ understands the relationships and gets the key for us.

Remember you have to make a connection to the database. You can use an existing one if you have it. Then you add a LINQ to SQL to the project and drag the tables from the connection in the Server Explorer onto the LINQ designer window.

Here is the XAML file. It may be somewhat different from what we did in class, because I recreated it at home:

<Window x:Class="ClientApForm.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="615" Width="536">
<Grid Height="563">
<Label Content="New Application" Height="41" HorizontalAlignment="Left" Margin="23,26,0,0" Name="label1" VerticalAlignment="Top" Width="200" FontSize="24" Foreground="#FF576B9A" />
<Label Content="Last Name" Height="28" HorizontalAlignment="Left" Margin="29,89,0,0" Name="label2" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="119,93,0,0" Name="txtLastName" VerticalAlignment="Top" Width="120" Background="BlanchedAlmond"/>
<Label Content="First Name" Height="28" HorizontalAlignment="Left" Margin="29,136,0,0" Name="label3" VerticalAlignment="Top" />
<TextBox Background="BlanchedAlmond" Height="23" HorizontalAlignment="Left" Margin="119,140,0,0" Name="txtFirstName" VerticalAlignment="Top" Width="120" />
<Label Content="Street" Height="28" HorizontalAlignment="Left" Margin="29,178,0,0" Name="label4" VerticalAlignment="Top" />
<TextBox Background="BlanchedAlmond" Height="23" HorizontalAlignment="Left" Margin="119,182,0,0" Name="txtStreet" VerticalAlignment="Top" Width="194" />
<Label Content="City" Height="28" HorizontalAlignment="Left" Margin="29,221,0,0" Name="label5" VerticalAlignment="Top" />
<TextBox Background="BlanchedAlmond" Height="23" HorizontalAlignment="Left" Margin="119,225,0,0" Name="txtCity" VerticalAlignment="Top" Width="194" />
<Label Content="State" Height="28" HorizontalAlignment="Left" Margin="29,260,0,0" Name="label6" VerticalAlignment="Top" />
<TextBox Background="BlanchedAlmond" Height="23" HorizontalAlignment="Left" Margin="119,264,0,0" Name="txtState" VerticalAlignment="Top" Width="38" />
<Label Content="Zip Code" Height="28" HorizontalAlignment="Left" Margin="29,302,0,0" Name="label7" VerticalAlignment="Top" />
<TextBox Background="BlanchedAlmond" Height="23" HorizontalAlignment="Left" Margin="119,306,0,0" Name="txtZip" VerticalAlignment="Top" Width="70" />
<Label Content="home Phone" Height="28" HorizontalAlignment="Left" Margin="29,342,0,0" Name="label8" VerticalAlignment="Top" />
<TextBox Background="BlanchedAlmond" Height="23" HorizontalAlignment="Left" Margin="119,346,0,0" Name="txtHomePhone" VerticalAlignment="Top" Width="120" />
<Label Content="Email" Height="28" HorizontalAlignment="Left" Margin="29,384,0,0" Name="label9" VerticalAlignment="Top" />
<TextBox Background="BlanchedAlmond" Height="23" HorizontalAlignment="Left" Margin="119,388,0,0" Name="txtEmail" VerticalAlignment="Top" Width="237" />
<ComboBox Height="23" HorizontalAlignment="Left" Margin="119,436,0,0" Name="cboService" VerticalAlignment="Top" Width="120" />
<Label Content="Service" Height="28" HorizontalAlignment="Left" Margin="28,436,0,0" Name="label10" VerticalAlignment="Top" />
<Label Content="Request" Height="28" HorizontalAlignment="Left" Margin="29,481,0,0" Name="label11" VerticalAlignment="Top" />
<TextBox Background="BlanchedAlmond" Height="23" HorizontalAlignment="Left" Margin="119,485,0,0" Name="txtRequest" VerticalAlignment="Top" Width="84" />
<Button Content="Submit" Height="23" HorizontalAlignment="Left" Margin="31,528,0,0" Name="button1" VerticalAlignment="Top" Width="75" Click="button1_Click" />
</Grid>
</Window>

Now here is the C# code. I have commented it some:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace ClientApForm
{
/// <summary>
/// This project uses LINQ to retrive the data from the database
/// and fill a combo box and then to insert data into the database
/// from an xaml form
/// Steve Conger, 8/6/2010
/// </summary>
public partial class MainWindow : Window
{
//I declare this at class level to give it
//class scope
//unlike on Thursday I used the default name
DataClasses1DataContext dc = new DataClasses1DataContext();

public MainWindow() //constructor
{
//this must run before anything else
//it creates the form
InitializeComponent();

FillCombo(); //call the fillCombo Method
}

private void FillCombo()
{
//this metho fills the cboService Box
//use the proxy classes create by link
//to get the serviceName and servicekey
//from the Service tale
var service = from s in dc.Services
select new { s.ServiceName, s.ServiceKey };

//assign the results to the combobox
//display the ServiceName
//but store the serviceKey
cboService.ItemsSource = service.ToList();
cboService.DisplayMemberPath = "ServiceName";
cboService.SelectedValuePath = "ServiceKey";
cboService.SelectedIndex = 0; //move to top of list
}

private void SubmitClient()
{
//this method insert into the various
//tables required to record a new client

//create a new person object
//assign the values from the textboxes
//to the properties of the person
Person p = new Person();
p.LastName = txtLastName.Text;
p.FirstName = txtFirstName.Text;

//pass the person object to the
//datacontext to hold it for
//insert when the submit is called
dc.Persons.InsertOnSubmit(p);


//Do the same for all the other
//tables required
PersonAddress pa = new PersonAddress();
pa.Street = txtStreet.Text;
pa.City = txtCity.Text;
pa.State = txtState.Text;
pa.Zip = txtZip.Text;
pa.Person = p; //get the personKey of the new Person

dc.PersonAddresses.InsertOnSubmit(pa);

PersonContact phone = new PersonContact();
phone.ContactInfo = txtHomePhone.Text;
phone.ContactTypeKey = 1;
phone.Person = p;

dc.PersonContacts.InsertOnSubmit(phone);

PersonContact email = new PersonContact();
email.ContactInfo = txtEmail.Text;
email.ContactTypeKey = 6;
email.Person = p;

dc.PersonContacts.InsertOnSubmit(email);

ServiceGrant sg = new ServiceGrant();
sg.EmployeeKey = 1;
sg.GrantDate = DateTime.Now;
sg.ServiceKey = int.Parse(cboService.SelectedValue.ToString());
sg.GrantAmount = decimal.Parse(txtRequest.Text);
sg.Person = p;

dc.ServiceGrants.InsertOnSubmit(sg);

//submit all the changes to the database

dc.SubmitChanges();

}

private void button1_Click(object sender, RoutedEventArgs e)
{
SubmitClient(); //call the submit client method
//show a message if success
MessageBox.Show("Your request has been submitted");
}

}
}