LINQ to SQL in c# .Net

Hello folks, for a change in the week end i started reading .Net, as a programmer i should appreciate both the legendary languages(Java, .Net) some have i was attracted to the beauty of one of the .Net’s feature called LINQ.
so here is a small post where i will demonstrate how to link up our front end with database using LINQ(Language Integrated Query). This is my simple attempt to write a .Net post(apologies if anything is wrong and please give your precious feedback)

prior to create our application let us create the database table as follows

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[Details]    Script Date: 04/27/2014 20:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Details](
	[id] [int] NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Age] [int] NULL,
 CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

after creating the database from the visual designer of the VisualStudio please create the following screen.
FrontEnd

inside the “CSharp” class behind this UI double click the submit button it will create the following method

private void SaveDetailsButton_Click(object sender, EventArgs e){
    // code to save the details and retrieve the details using LINQ
}

now its time to create the LINQ class inside the project create a file called “LINQ to SQL” and drag and drop the table in this file and it looks as below.
dbml_file

Once this is done the VisualStudio automatically create the following code


//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:2.0.50727.4984
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace SampleApplication
{
	using System.Data.Linq;
	using System.Data.Linq.Mapping;
	using System.Data;
	using System.Collections.Generic;
	using System.Reflection;
	using System.Linq;
	using System.Linq.Expressions;
	using System.ComponentModel;
	using System;
	
	
	[System.Data.Linq.Mapping.DatabaseAttribute(Name="TestDatabase")]
	public partial class DetailsClassDataContext : System.Data.Linq.DataContext
	{
		
		private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
		
    #region Extensibility Method Definitions
    partial void OnCreated();
    partial void InsertDetail(Detail instance);
    partial void UpdateDetail(Detail instance);
    partial void DeleteDetail(Detail instance);
    #endregion
		
		public DetailsClassDataContext() : 
				base(global::SampleApplication.Properties.Settings.Default.TestDatabaseConnectionString, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(string connection) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(System.Data.IDbConnection connection) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public System.Data.Linq.Table<Detail> Details
		{
			get
			{
				return this.GetTable<Detail>();
			}
		}
	}
	
	[Table(Name="dbo.Details")]
	public partial class Detail : INotifyPropertyChanging, INotifyPropertyChanged
	{
		
		private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
		
		private int _id;
		
		private string _FirstName;
		
		private string _LastName;
		
		private System.Nullable<int> _Age;
		
    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnidChanging(int value);
    partial void OnidChanged();
    partial void OnFirstNameChanging(string value);
    partial void OnFirstNameChanged();
    partial void OnLastNameChanging(string value);
    partial void OnLastNameChanged();
    partial void OnAgeChanging(System.Nullable<int> value);
    partial void OnAgeChanged();
    #endregion
		
		public Detail()
		{
			OnCreated();
		}
		
		[Column(Storage="_id", DbType="Int NOT NULL", IsPrimaryKey=true)]
		public int id
		{
			get
			{
				return this._id;
			}
			set
			{
				if ((this._id != value))
				{
					this.OnidChanging(value);
					this.SendPropertyChanging();
					this._id = value;
					this.SendPropertyChanged("id");
					this.OnidChanged();
				}
			}
		}
		
		[Column(Storage="_FirstName", DbType="VarChar(50)")]
		public string FirstName
		{
			get
			{
				return this._FirstName;
			}
			set
			{
				if ((this._FirstName != value))
				{
					this.OnFirstNameChanging(value);
					this.SendPropertyChanging();
					this._FirstName = value;
					this.SendPropertyChanged("FirstName");
					this.OnFirstNameChanged();
				}
			}
		}
		
		[Column(Storage="_LastName", DbType="VarChar(50)")]
		public string LastName
		{
			get
			{
				return this._LastName;
			}
			set
			{
				if ((this._LastName != value))
				{
					this.OnLastNameChanging(value);
					this.SendPropertyChanging();
					this._LastName = value;
					this.SendPropertyChanged("LastName");
					this.OnLastNameChanged();
				}
			}
		}
		
		[Column(Storage="_Age", DbType="Int")]
		public System.Nullable<int> Age
		{
			get
			{
				return this._Age;
			}
			set
			{
				if ((this._Age != value))
				{
					this.OnAgeChanging(value);
					this.SendPropertyChanging();
					this._Age = value;
					this.SendPropertyChanged("Age");
					this.OnAgeChanged();
				}
			}
		}
		
		public event PropertyChangingEventHandler PropertyChanging;
		
		public event PropertyChangedEventHandler PropertyChanged;
		
		protected virtual void SendPropertyChanging()
		{
			if ((this.PropertyChanging != null))
			{
				this.PropertyChanging(this, emptyChangingEventArgs);
			}
		}
		
		protected virtual void SendPropertyChanged(String propertyName)
		{
			if ((this.PropertyChanged != null))
			{
				this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
			}
		}
	}
}

now copy and paste the following code snippet into the button handling method as shown below.

 DetailsClassDataContext detailsClassDataContext = new DetailsClassDataContext();

            Detail detail = new Detail();
            detail.id = Int32.Parse(IdtextBox.Text.ToString());
            detail.FirstName = firstNametextBox.Text.ToString();
            detail.LastName = lastNametextBox.Text.ToString();
            detail.Age = Int32.Parse(agetextBox.Text.ToString());
            try
            {
                detailsClassDataContext.Details.InsertOnSubmit(detail);
                detailsClassDataContext.SubmitChanges();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

           
            var data = from a in detailsClassDataContext.Details select a;
            dataGridView1.DataSource = data;

Also in the Form_load method please write the following code as below. this helps to query the database at the application load time and populate the DataGrid.

     private void Form1_Load(object sender, EventArgs e)
        {
            DetailsClassDataContext detailsClassDataContext = new DetailsClassDataContext();
            var data = from a in detailsClassDataContext.Details select a;
            dataGridView1.DataSource = data;
        }

Finally aour application is ready to use and this looks as below.
FinalScreen

Happy Coding in LINQ to SQL with C# 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s