Home Programming Databinding in C# with LINQ

Databinding in C# with LINQ

0
117

So, as I stated in an earlier post, I’ve been working on C# and making a project management application. It’s been rocky, but it is my first. And now C# 3.0 with .NET 3.5 and LINQ have come out and it’s a whole new kettle of fish. So, in my continuing effort to make life simpler for others, here’s what I’ve learned about accessing data in C# and LINQ so far.

First, be sure you have your using statement at the top of the source page:

using System.Linq;

Also, be sure to do the following before you start writing the code:

  1. Right Click your project – select Add Item – Select Data – Add LINQ to SQL Classes
  2. Add the tables you want to work with to your data class
  3. Add your relationships
  4. After you finish your relationships, LINQ will create a projectDataClasses.dbml file with is the Context

Now on to code writing.I added the following, you may name it differently depending on the name of your .dbml file:

private projectDataClasses1DataContext lqMyDb = new projectDataClasses1DataContext();

This will create an instance of ProjectDatabaseContext that is your starting point. It will go right above public Form1().Now, you can use a datagrid which is what I did and add some buttons and stuff and we’ll start yanking data out of our database.Here’s the button for the Refresh button. It’ll load the data and display it on the data grid view:

private void btnRefresh_Click(object sender, EventArgs e)
{
var Companies = from p in lqMyDb.Companies select p;
dgvProjectData.DataSource = Companies;
}

var Customers this means i am going to create a new object of an unknown type named Customers. var is a new keyword in C# only for Linq. So linq will see the var and make Customers the right type for you.

After the = this is a functional syntax p is an arbituary name you can call that anything you want. The important is you are pullling data from something then you are telling it what object to pull from in your case you are saying Customers lqMyDb.Customers

And since you are not filtering any of the data you are selecting everything from p (p is a reference don’t worry about it just learn the pattern).

So what gets pulled from the LINQ query gets dumped into the Customers object which you defined as var Customers.

Mow that u have the data u need to tell the grid that this is its datasource! Once the grid know this is its datasource it will be able to show the results of that linq query!

Something to remember: when you mapped your LINQ tables if you had any that were plural it automatically made the LING object names singular. Keep that in mind.

So, after this I added a button and named it btNew. Here is the code for that button:

private void addButton_Click(object sender, EventArgs e)
{
Company curCompany = new Company();
curCompany.CompanyName = tbUpdate.Text;
curCompany.CompanyID = new Guid(System.Guid.NewGuid().ToString(“D”));
lqMyDb.Companies.InsertOnSubmit(curCompany);
lqMyDb.SubmitChanges();
btnRefresh_Click(null, null);
}

The first line is making a instance of the linq company object from my dbml file. The last two lines will tell LINQ to write my data to my new object in the database.

After the first line, I need to set the object’s field values. So, I begin by adding a line feed after Company curCompany = new Company(); Think of it like this, LINQ takes your database, looks at the fields and makes a object for the database and properties for each field. Properties are just variables that hold data. So, to set the values, you do this: curCompany then type a period and look for the field names in the list.

One thing to note: LINQ knows what data types your fields are in the Company table. So make sure you enter the right types. LINQ looks at the tables that you added to it. It then made a class representation of it. Classes are blueprints used to stamp out copies or objects of the class.

So company table was read by LINQ and it made a C# Class of the table and it made a property for each field in the table.

In order for you to work with th class you need to take the class (the bluprint) and make a object something tangible that you can work with from the class. this is called creating a instance of the class. an instance of the class is called an object so the first line Company curCompany = new Company(); says ok use the Company (class) and make a curCompany object of Type Company class.

On the next line in your code after u set your CompanyName you have this:

curCompany.CompanyID = new Guid(System.Guid.NewGuid().ToString(“D”));

This will create the new GUID for your primary key. You are using Primary Keys right?

So, In summary:

  1. In order to make a new row in the table u had to make a new instance object of Company that was the first line.
  2. Then you set all the values u want to write to the new line in the table by setting the properties that match the field names in the new object.
  3. Now, you need to tell link that when you tell it to submit changes that for Companies you want it to do inserts. New = Insert and you are telling it to take the new objects that you made and insert them into the table. That what the InsertOnSubmit() does.
  4. Finally, you need to tell LINQ that you are ready for it to do the submits to the database with this line: lqMyDb.SubmitChanges();

Now on to doing Updates. I added a text box and button I called tbUpdate and btUpdate respectively. Here’s the code:

private void btUpdate_Click(object sender, EventArgs e)
{
if (dgvProjectData.SelectedRows.Count > 0)

{
try
{
var curCompany = GetRecord();

curCompany.CompanyName = tbUpdate.Text;
lqMyDb.SubmitChanges();
}

catch (Exception ex)
{
MessageBox.Show(this, (“The following occred while trying to update:” + ex.Message), “Update Error…”);
}
}
else
{
MessageBox.Show(this, “You did not select a row to update”, “Update Error…”);
}

}

So, this is fairly simple: Basically we’re telling LINQ to look in the Company table (called companies in LINQ) and pull all records whose CompanyName field value is FOO. Then only return the very first record found that was pulled. they are not really pulled there is a lot more going on in the backend. The fist record is put assigned to curCompany then we’re updating the CompanyName property to the text entered in the tbUpdate Textbox. Finally we submit the changes. SubmitChanges will process all Updates, Inserts, Deletets that we tell LINQ to process.

And there you have it. Once I get delete taken care of I’ll update this blog post with that information. Many thanks goes to John Padilla from Scofield for his assistance with this project and his time and efforts into schooling me.