Calling a Stored Procedure from Entity Framework

Dear readers,

In the previous posts on entity framework, we saw some basic tutorials on programming using Entity Framework. This post is about calling a stored procedure using Entity Framework. I assume that you have idea in creating .edmx and write stored procedures in SQL server.

1) Write a stored procedure and execute it in your data base, In my case I have written a simple SP to delete the records with odd Numbered Primary Keys from a table.

Stored Procedure

2) Then Go to your application and open the .edmx file>> right click and select Update Model from database.

Update Model From Database

3) An update wizard will open. In which you can select the stored procedure that you would like to execute through Entity Framework >> Click finish.

Add the SP from Update Model Wizard

4) Now open the model browser by right clicking the .edmx file and selecting Model browser. You will now be able to see the your stored procedure under (Second red box in model browser) yourmodel.store tree view. You will be able to see another parent tree just above that in the name of your model, traverse to Entity Container >> Function imports (first red box in the model browser).

Open the Model Browser

5) Right click Function Imports and select “Add Function import“. The below wizard will open in which you need to give a name to sp and then select the SP that you want to call from your code. Provide the return parameters. if any(In my case I am not using any return type in my SP). Click “ok“.

Open the Add Function Import Wizard

6) Go to the fuction from where you want to execute it and call the ExecuteFunction method by passing the params as string array, if any. In the code snippet below, I have called my “DeleteOddAddress” SP.

Call the Execute function from the code

The result for this execution returns 0 records since I do not have any odd numbered address ids in my table.

Result Console

Hope you find this useful. Happy Learning Folks!!!

Export to Excel (.xlsx) using Open xml in C#

Dear readers, This post is about implementing and reusing the Export to Excel functionality using Open XML. Following the steps given below will guide you through the same. Every methods are supported by the comments for better understanding. Do write for clarifications, if any. 1) Add the below Class, “ExportToExcelUtility” to the project where you need to implement the functionality. (in the presentation layer).

public sealed class ExportToExcelUtility

{

/// <summary>

/// Convert the list to Data table.

/// </summary>

/// <param name=”items”>List</param>

/// <returns>DataTable</returns>

public static DataTable ToDataTable<T>(List<T> items)

{

DataTable dataTable = new DataTable(typeof(T).Name);

PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

foreach (PropertyInfo prop in Props)

{

dataTable.Columns.Add(prop.Name);//Setting column names

}

foreach (T item in items)

{

var values = new object[Props.Length];

for (int i = 0; i < Props.Length; i++)

{

values[i] = Props[i].GetValue(item, null);//Insert values.

}

dataTable.Rows.Add(values);

}

return dataTable;

}

/// <summary>

/// Call this method from the Page providing the desired information

/// </summary>

/// <param name=”dataTable”>The records to be written in excel</param>

/// <param name=”excelFilename”>Name of the file</param>

/// <param name=”sheetName”>Name of the sheet</param>

/// <param name=”filters”>Search key and value based on which the datatable is generated</param>

/// <param name=”columnSize”>column name and size</param>

/// <returns></returns>

public static bool CreateExcelDocument(DataTable dataTable, string excelFilename, string sheetName, Dictionary<string, string> filters, Dictionary<string,   int> columnSize)

{

try

{

using (SpreadsheetDocument objExcelDoc = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))

{

int cellSize;

WorkbookPart wbp = objExcelDoc.AddWorkbookPart();

WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();

Workbook wb = new Workbook();

FileVersion fv = new FileVersion();

fv.ApplicationName = “Microsoft Office Excel”;

Worksheet workSheet = new Worksheet();

WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();

wbsp.Stylesheet = CreateStylesheet();

wbsp.Stylesheet.Save();

Columns columns = new Columns();

for (int i = 1; i <= columnSize.Count(); i++)

{

columnSize.TryGetValue(columnSize.Keys.ElementAt(i – 1).ToString(), out cellSize);

columns.Append(CreateColumnData(Convert.ToUInt32(i), Convert.ToUInt32(i), cellSize));

}

workSheet.Append(columns);

SheetData sheetData = new SheetData();

for (UInt32 i = 2; i <= 1 + filters.Count(); i++)

{

sheetData.Append(CreateFilters(i, filters));

}

sheetData.Append(CreateColumnHeader(Convert.ToUInt32(filters.Count() + 3), columnSize));

UInt32 index = Convert.ToUInt32(filters.Count() + 4);

foreach (DataRow dr in dataTable.Rows)

{

sheetData.Append(CreateContent(index, dr, columnSize.Count()));

index++;

}

workSheet.Append(sheetData);

wsp.Worksheet = workSheet;

Sheets sheets = new Sheets();

Sheet sheet = new Sheet();

sheet.Name = sheetName;

sheet.SheetId = 1;

sheet.Id = wbp.GetIdOfPart(wsp);

sheets.Append(sheet);

wb.Append(fv);

wb.Append(sheets);

objExcelDoc.WorkbookPart.Workbook = wb;

objExcelDoc.WorkbookPart.Workbook.Save();

objExcelDoc.Close();

}

}

catch (Exception ex)

{

throw;

}

return true;

}

/// <summary>

/// Create column for storing data by passing the start column index, end column index and column width

/// </summary>

/// <param name=”StartColumnIndex”>start column index</param>

/// <param name=”EndColumnIndex”>end column index</param>

/// <param name=”ColumnWidth”>width of each column</param>

/// <returns>column</returns>

private static Column CreateColumnData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)

{

Column column;

column = new Column();

column.Min = StartColumnIndex;

column.Max = EndColumnIndex;

column.Width = ColumnWidth;

column.CustomWidth = true;

return column;

}

/// <summary>

/// Writes the row to the excel by reading each datarow from the datatable

/// </summary>

/// <param name=”index”>row index</param>

/// <param name=”dr”>data row</param>

/// <param name=”columns”>number of columns</param>

/// <returns></returns>

private static Row CreateContent(UInt32 index, DataRow dr, int columns)

{

Row objRow = new Row();

Cell objCell;

try

{

objRow.RowIndex = index;

for (int i = 65; i < 65 + columns; i++)

{

objCell = new Cell();

objCell.StyleIndex = 5;

objCell.DataType = CellValues.String;

objCell.CellReference = (char)i + index.ToString();

objCell.CellValue = new CellValue(dr.ItemArray[i - 65].ToString());

objRow.Append(objCell);

}

}

catch (Exception ex)

{

throw;

}

return objRow;

}

/// <summary>

/// Defines the Style sheet for the excel.

/// </summary>

/// <returns>Stylesheet</returns>

private static Stylesheet CreateStylesheet()

{

Stylesheet ss = new Stylesheet();

Fonts fts = new Fonts();

DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();

FontName ftn = new FontName();

ftn.Val = StringValue.FromString(“Calibri”);

DocumentFormat.OpenXml.Spreadsheet.FontSize ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();

ftsz.Val = DoubleValue.FromDouble(11);

ft.FontName = ftn;

ft.FontSize = ftsz;

fts.Append(ft);

ft = new DocumentFormat.OpenXml.Spreadsheet.Font();

ftn = new FontName();

ftn.Val = StringValue.FromString(“Palatino Linotype”);

ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();

ftsz.Val = DoubleValue.FromDouble(18);

ft.FontName = ftn;

ft.FontSize = ftsz;

fts.Append(ft);

fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);

Fills fills = new Fills();

Fill fill;

PatternFill patternFill;

fill = new Fill();

patternFill = new PatternFill();

patternFill.PatternType = PatternValues.None;

fill.PatternFill = patternFill;

fills.Append(fill);

fill = new Fill();

patternFill = new PatternFill();

patternFill.PatternType = PatternValues.Gray125;

fill.PatternFill = patternFill;

fills.Append(fill);

fill = new Fill();

patternFill = new PatternFill();

patternFill.PatternType = PatternValues.Solid;

patternFill.ForegroundColor = new ForegroundColor();

patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString(“CDCDCD”);

patternFill.BackgroundColor = new BackgroundColor();

patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;

fill.PatternFill = patternFill;

fills.Append(fill);

fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);

Borders borders = new Borders();

Border border = new Border();

border.LeftBorder = new LeftBorder();

border.RightBorder = new RightBorder();

border.TopBorder = new TopBorder();

border.BottomBorder = new BottomBorder();

border.DiagonalBorder = new DiagonalBorder();

borders.Append(border);

border = new Border();

border.LeftBorder = new LeftBorder();

border.LeftBorder.Style = BorderStyleValues.Thin;

border.RightBorder = new RightBorder();

border.RightBorder.Style = BorderStyleValues.Thin;

border.TopBorder = new TopBorder();

border.TopBorder.Style = BorderStyleValues.Thin;

border.BottomBorder = new BottomBorder();

border.BottomBorder.Style = BorderStyleValues.Thin;

border.DiagonalBorder = new DiagonalBorder();

borders.Append(border);

borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);

CellStyleFormats csfs = new CellStyleFormats();

CellFormat cf = new CellFormat();

cf.NumberFormatId = 0;

cf.FontId = 0;

cf.FillId = 0;

cf.BorderId = 0;

csfs.Append(cf);

csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);

uint iExcelIndex = 164;

NumberingFormats nfs = new NumberingFormats();

CellFormats cfs = new CellFormats();

NumberingFormat nfForcedText = new NumberingFormat();

nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);

nfForcedText.FormatCode = StringValue.FromString(“@”);

nfs.Append(nfForcedText);

cf = new CellFormat();

cf.FontId = 0;

cf.FillId = 0;

cf.BorderId = 0;

cf.FormatId = 0;

cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);

cfs.Append(cf);

cf = new CellFormat();

cf.FontId = 0;

cf.FillId = 0;

cf.BorderId = 1;

cf.FormatId = 0;

cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);

cfs.Append(cf);

cf = new CellFormat();

cf.FontId = 0;

cf.FillId = 0;

cf.BorderId = 0;

cf.FormatId = 0;

cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);

cfs.Append(cf);

cf = new CellFormat();

cf.NumberFormatId = nfForcedText.NumberFormatId;

cf.FontId = 0;

cf.FillId = 0;

cf.BorderId = 0;

cf.FormatId = 0;

cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);

cfs.Append(cf);

cf = new CellFormat();

cf.NumberFormatId = nfForcedText.NumberFormatId;

cf.FontId = 1;

cf.FillId = 0;

cf.BorderId = 0;

cf.FormatId = 0;

cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);

cfs.Append(cf);

cf = new CellFormat();

cf.FontId = 0;

cf.FillId = 0;

cf.BorderId = 1;

cf.FormatId = 0;

cfs.Append(cf);

cf = new CellFormat();

cf.FontId = 0;

cf.FillId = 2;

cf.BorderId = 1;

cf.FormatId = 0;

cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);

cfs.Append(cf);

cf = new CellFormat();

cf.NumberFormatId = nfForcedText.NumberFormatId;

cf.FontId = 0;

cf.FillId = 2;

cf.BorderId = 1;

cf.FormatId = 0;

cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);

cfs.Append(cf);

ss.Append(nfs);

ss.Append(fts);

ss.Append(fills);

ss.Append(borders);

ss.Append(csfs);

ss.Append(cfs);

CellStyles css = new CellStyles();

CellStyle cs = new CellStyle();

cs.Name = StringValue.FromString(“Normal”);

cs.FormatId = 0;

cs.BuiltinId = 0;

css.Append(cs);

css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);

ss.Append(css);

DifferentialFormats dfs = new DifferentialFormats();

dfs.Count = 0;

ss.Append(dfs);

TableStyles tss = new TableStyles();

tss.Count = 0;

tss.DefaultTableStyle = StringValue.FromString(“TableStyleMedium9″);

tss.DefaultPivotStyle = StringValue.FromString(“PivotStyleLight16″);

ss.Append(tss);

return ss;

}

/// <summary>

/// Create the header row provided with the row index and header list with each column size

/// </summary>

/// <param name=”index”>row index</param>

/// <param name=”headerList”>header name and column width</param>

/// <returns></returns>

private static Row CreateColumnHeader(UInt32 index, Dictionary<string, int> headerList)

{

Row objRow = new Row();

objRow.RowIndex = index;

Cell objCell;

for (int i = 0; i < headerList.Count(); i++)

{

objCell = new Cell();

objCell.DataType = CellValues.String;

objCell.StyleIndex = 6;

objCell.CellReference = Convert.ToChar(65 + i) + index.ToString();

objCell.CellValue = new CellValue(headerList.Keys.ElementAt(i).ToString());

objRow.Append(objCell);

}

return objRow;

}

/// <summary>

/// Creating the filters based on which data row is generated. For creating header templates

/// </summary>

/// <param name=”index”>Row index</param>

/// <param name=”filters”>Search key and its respective value</param>

/// <returns></returns>

private static Row CreateFilters(UInt32 index, Dictionary<string, string> filters)

{

Row objRow = new Row();

try

{

objRow.RowIndex = index;

Cell objcell;

objcell = new Cell();

objcell.DataType = CellValues.String;

objcell.StyleIndex = 6;

objcell.CellReference = “A” + index.ToString();

objcell.CellValue = new CellValue(filters.Keys.ElementAt(Convert.ToInt32(index – 2)).ToString());

objRow.Append(objcell);

objcell = new Cell();

objcell.DataType = CellValues.String;

objcell.StyleIndex = 5;

objcell.CellReference = “B” + index.ToString();

objcell.CellValue = new CellValue(Convert.ToString(filters.ElementAt(Convert.ToInt32(index – 2)).Value));

objRow.Append(objcell);

}

catch (Exception ex)

{

throw;

}

return objRow;

}

}

2) Add the following namespaces to the page where you need to implement export to excel functionality

using System.Web.UI;

using System.Web.UI.WebControls;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml;

using System.Data;

using DocumentFormat.OpenXml.Packaging;

using System.Reflection;

using System.IO;

3) In the event that requires to perform export to excel, call the CreateExcelDocument by providing the datatable generated, filename, sheetname, search filters as key value pair, column names and each column size as key value pairs.

protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)

{

try

{

Dictionary<string, string> filters = new Dictionary<string, string>();

//filters.Add(“Search Key”,”Search Value”); to add the filters for generating the template rows

Dictionary<string, int> Columns = new Dictionary<string, int>();

//Columns.Add(“Column name”,size in integer); to add the column name and its respective size

DataTable DataList = new DataTable();

DataList = ExportToExcelUtility.ToDataTable(call the method that returns The list to be written in Excel);

string date = DateTime.Now.ToString(“ddMMMyyHHmmtt”);

string filename = System.IO.Path.GetTempPath() + (Session["FileName"].ToString()) + DateTime.Now.ToString(“ddMMMyyHHmmtt”) + “.xlsx”;

if (ExportToExcelUtility.CreateExcelDocument(DataList, filename, date, filters, Columns))

{

Response.ClearContent();

FileStream objFileStream = new FileStream(filename, FileMode.Open, FileAccess.Read);

byte[] data1 = new byte[objFileStream.Length];

objFileStream.Read(data1, 0, data1.Length);

Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;

Response.AddHeader(“Content-Disposition”, string.Format(“attachment; filename={0}”, filename));

Response.BinaryWrite(data1);

}

}

catch (Exception ex)

{

}

Response.End();

}

Note: Make sure you have added open xml dll to the project. The open xml dll can be downloaded from
https://docs.google.com/file/d/0B915XE_zbo18d0xudGVzYW11dXc/edit?usp=sharing

Happy Learning Folks!!!

How to set the focus to the master page controls

While web development using ASP.NET, we often required to set the focus of your page pointing to particular controls. We can use Page.SetFocus(Control) or Page.SetFocus(“Contol.ClientID”) to do bring the focus to the respective control after an operation.

Let us consider a scenario of implementing custom pagination controls, set to display 50 records in each page. When you press the previous or the next button, the focus will be pointing to the end of the page. In order to improve the user-friendliness, we can include Page.SetFocus(Control) after binding the grid. Note that the Control here need to be replaced by any controls that are present in to top of your pages.

When it comes to the master pages, the above method will not give us the required result, rather it will ignore the Master page and focus will be displaying the first line of your child page. In order to set the focus to the MasterPage, an additional piece of code need to add, as shown below:

Consider I have a log out label of id “lbtnLogout” in the top of the master page.In order to set the focus to the beginning of the page, I need to make use of the topmost control in the Master Page. The code for the same is :

LinkButton userName = (LinkButton)Master.FindControl(“lbtnLogout”); 

//use the find control in-built method to fetch the lbtnLogout control’s client ID in Master Page

Page.SetFocus(userName.ClientID);

 

This will set the focus to the master page controls. Hope it Helps! Happy Learning folks!!!

Filters in LINQ

LINQ plays an important role in application development using Entity Framework. In the past posts, we saw how to make use of Entity Framework to make the basic insert, update, delete and display operations and about some frequent errors.

 

LINQ, Language Integrated Query, uses similar querying syntax like SQL with some minor differences. The LINQs can be written in two ways, as queries and as Lambda expressions. Major difference between LINQ and SQL is, the former is used to query the objects(xml, SQL, entities) whereas the latter is used to query the relational database(tables).With this small introduction to LINQ, let us see how to do LINQ querying.

 

In this post, I will be covering various filters in LINQ that during displaying Records.

 

Filters ———- Description

 

Contains —— “Where in” in SQL

StartsWith—– “Like a%” in SQL

Single——— “Select Top 1″ in SQL

Skip(n)——– Skips first n records from the list

Take(n)——– Takes first n records from the list

Distinct——- Removes duplicate records from the list

First ——— First element in the List

Last ———- Last element in the list

FirstOrDefault- First element or the default value from the list

LastOrDefault– Last element or the default value from the list

 

Find below the sample code implementing the above filters on the AdventureWorks db(This can be downloaded from the link
http://msftdbprodsamples.codeplex.com/releases/view/93587
).

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace EntityFrameworksFilters

{

    class Program

    {

        static void Main(string[] args)

        {

            using (AdventureWorksModelContainer context = new AdventureWorksModelContainer())

            {

                        //using Contains

                Console.WriteLine(“The currencies that contains the name Dollar in it”);

                var Records = from e in context.Currencies

                              where e.Name.Contains(“Dollar”)

                              select e.Name;

                foreach (string currencyName in Records)

                {

                    Console.WriteLine(currencyName);

                }

                Console.WriteLine(“Enter a key to view the first 10 currencies in the list”);

                Console.ReadKey();

//using Take

                var First10 = (from e in context.Currencies

                              select e.Name).Take(10);

                foreach (string currencyName in First10)

                {

                    Console.WriteLine(currencyName);

                }

                        //Using Skip

                Console.WriteLine(“Enter a key to view the second 10 currencies in the list”);

                Console.ReadKey();

                var Second10 = (from e in context.Currencies

                               select e).OrderBy(e=>e.Name).Skip(10).Take(10);

                foreach (var currencyName in Second10)

                {

                    Console.WriteLine(currencyName.Name);

                }

                        //Using StartsWith

                Console.WriteLine(“Enter a key to view the currencies starting with the letter ‘A’ in the list”);

                Console.ReadKey();

                var StartsWith = (from e in context.Currencies

                                  where e.Name.StartsWith(“A”)

                                  select e);

                foreach (var currencyName in StartsWith)

                {

                    Console.WriteLine(currencyName.Name);

                }

                        //Using Distinct

                Console.WriteLine(“Enter a key to view the Distinct list of currencies in the List”);

                Console.ReadKey();

                var DistinctList = (from e in context.Currencies

                                    select e).Distinct();

                foreach (var currencyName in DistinctList)

                {

                    Console.WriteLine(currencyName.Name);

                }

                Console.ReadKey();

            }

        }

    }

}

 

The project is available for download in the link: 
https://docs.google.com/file/d/0B915XE_zbo18Rlpaa2R6ejlQTzA/edit?usp=sharing

Message: Unable to update the EntitySet – because it has a DefiningQuery and no element exist

Reason: 1) This error often occur due to the absence of primary key in the respective table, or 2)the identity column is not set to true and hence there is no option to set the auto-increment value 3) due to unupdated model file after adding the primary key to the table.

Solutions:
1) This error often occur due to the absence of primary key in the respective table where you are trying to perform an insert operation:

Check your database to see if the table has the primary key is set. If not, add the primary key by “opening the Design view >> right click and set as primary key >>  click save” or by executing the following query.
ALTER TABLE <TABLE_NAME>
ADD CONSTRAINT <CONSTRAINT_NAME> PRIMARY KEY(<COLUMN_NAME>)

Set primary key

2) The identity column is not set to true and hence there is no option to set the auto-increment value:

If the table contains the primary key already, go to the design view and check the column properties of the primary key. If Identity specification is set as No as shown in figure below, change it to Yes and then make sure the auto-increment option below that row is set and the value for the same is 1.

Column Properties

3) Due to unupdated model file after adding the primary key to the table

The third step must be followed if any change is made to the table using above steps, so that your change will be reflected in your Model.

Now, Go to the Visual Studio >> Open your solution with the project having the issue >> Open the Entity Framework Model Designer(.edmx) >> Right click the designer>> Select the model corresponding to the table to click update model from database. Now save and run the solution.

Update Model From Database

Hope this helps. Happy Learning!!!

Update in Entity Framework

Hi friends,

In the previous post, we saw about insert, delete and displaying records using Entity Framework. This week let us see how to update a record in Entity framework.

In order to perform update we need to, 1) Fetch the object using a LINQ, 2) Assign the values to be updated, 3) Save the changes to persist the values in db.


using (SampleDatabaseEntities context = new SampleDatabaseEntities())
{
int i = 1;
Dictionary recordDict = new Dictionary();
var CountryTable = from offices in context.Offices
join countries in context.Countries on offices.CountryId equals countries.Id
select new
{
CountryName = countries.Name,
Location = offices.Name
};
foreach (var ent in CountryTable.ToList())
{
Console.WriteLine(i + ")\t" + ent.CountryName + "-----" + ent.Location);
recordDict[i] = ent.CountryName + "-" + ent.Location;
i++;
}
Console.WriteLine("Enter the element to be updated from the choices 1 to {0}", i);
int c = Int32.Parse(Console.ReadLine());
if (c < i)
{
string country = recordDict[c].Split('-').ElementAt(0);
string location = recordDict[c].Split('-').ElementAt(1);
Office obj = (from ent in context.Offices
join con in context.Countries on ent.CountryId equals con.Id
where con.Name == country && ent.Name == location
select ent).FirstOrDefault(); //Fetch the record using the framework's class object
Console.WriteLine("Enter the new name to be updated instead of {0}", obj.Name);
obj.Name = Console.ReadLine(); //Update the record by assigning the value to the desired property
context.SaveChanges(); //Save the changes in the database
}
else
{
Console.WriteLine("Invalid Choice");
}


Whenever we perform updation inside the loops/while writing batch updation logics, it is recommended to use “context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);” to perform updates.

int i=1;
foreach(var obj in ListElements)
{
obj.CountryID = i;
context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified); //requires namespace using System.Data.Objects; and using System.Data;
context.SaveChanges();
i++;
}

Hope it helps. Happy Learning!

Beginners Tutorial on Entity Framework : A Console Application

Let us start with the Introduction to Entity Framework. Entity Framework is one of the ADO.NET approaches, which is used for communication between the application and the database. It is a Open Source project.

Entity framework is widely used with Microsoft .NET applications, that provides database connectivity using a model class (whose object is often referred as context) that forms the abstraction of database objects as classes and properties. There are two approaches, viz Creating model from database and creating database from models. Advantage of entity framework is, it reduces the dependency of database admin and makes the most of the code to handle database operations using the Model Designer. Entity framework often make use of LINQ to query the database objects. Care should be taken while using LINQ which may affect the performance, in case of the use of improper LINQs (Language INtegrated Query) .

Now let us see how to create a console application using Entity Framework.

Steps to create the Model from Database:

1. Create a new console application.
2. Right click the project and click “add new item” >> Select “Data” from the left side of the wizard >> Select “ADO.NET Entity Model” >> Name your Entity Model >> click “Add”.
3. A “Entity Data Model Wizard” will appear.

• Select “Generate from Database” >> Click “Next”
• Create the connection to the database. Click “Next”.
• Select “Tables” >> Click “Finish”

Note: The steps are illustrated in below. Follow the presentation for more clarification.

Step 1:  Create a new Console Application

Step 2: Add a item “ADO.NET Entity Model to the Project”

Step 3: Run the “Entity Data Model Wizard”

After running the wizard go to Program.cs in your console application.
Follow the steps to create, delete and display the database records, as demonstrated below
1 )Add the required .dlls to the header

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Objects;

using System.Data;

2) The code to insert record into db

In the Main method of program.cs add the following code.

using (SampleDatabaseEntities context = new SampleDatabaseEntities())

{

Console.WriteLine(“Enter a Country Name”);

string country = Console.ReadLine(); //read the country name to be stored

Country con = new Country(); //Create an object to the Country Entity

con.Name = country;

context.AddToCountries(con); //Equivalent to inset query

context.SaveChanges();

}

3) The Code to display the records from the database

using (SampleDatabaseEntities context = new SampleDatabaseEntities())

{

int i = 1;

var CountryTable = from offices in context.Offices

join countries in context.Countries on offices.CountryId equals countries.Id

select new

{

CountryName = countries.Name,

Location = offices.Name

};

foreach (var ent in CountryTable.ToList())

{

Console.WriteLine(i + “\t” + ent.CountryName + “—–” + ent.Location);

i++;

}

}

4) The Code to delete a record from database

using (SampleDatabaseEntities context = new SampleDatabaseEntities())

{

int i=1;

var locations = from loc in context.Offices

select loc.Name;

Console.WriteLine(“Enter the Location Name to be deleted from the list”);

foreach (string loc in locations)

{

Console.WriteLine(i+”)”+loc);

i++;

}

string locationDel = Console.ReadLine().ToUpper();

Office record = (from loc in context.Offices

where loc.Name == locationDel.ToUpper()

select loc).FirstOrDefault();   //Fetch the object to be deleted

context.DeleteObject(record); // the object to be deleted

context.SaveChanges();

}

Happy Learning!