In my previous post, I demonstrated how to use ADO.NET to connect to Teradata in your OLTP projects. In this sample, I am going to implement the same sample using ADO.NET Entity Model, Entity Framework and LINQ.
Connect to Teradata Database via .NET Data Provider
Prerequisites
- Entity Framework 5.x or any version above 3.5
- .NET Data Provider for Teradata 15.01.00.01 (http://downloads.teradata.com/download/connectivity/net-data-provider-for-teradata)
- Teradata Database 12.0 or later.
Step 1 Create ADO.NET Entity Data Model
In the wizard, create ‘TDSample.edmx’ through item ‘ADO.NET Entity Data Model’.
/project/visual_csharp/resources/BA293C61-AD33-57B9-9671-F3319F57D789.webp
Select Generate from Database:
/project/visual_csharp/resources/FCE23375-FDD4-5B30-8E57-A401E5265BA1.webp
Create a connection to Teradata database:
/project/visual_csharp/resources/8060DBBF-E5CC-5ED8-B6A8-9C463AE3F1EF.webp
Remember to set the option ‘Use X Views’ to false and set the Database to the database you are using. I also changed the timeout options as my local VM responds slowly.
The entity connection string looks like:
metadata=res://*/TDSample.csdl|res://*/TDSample.ssdl|res://*/TDSample.msl;provider=Teradata.Client.Provider;provider connection string="connection timeout=90;database=TD_MS_SAMPLE_DB;user id=dbc;data source=192.168.121.128;persist security info=True;use x views=False;restrict to default database=True;command timeout=90"
* Change the server and user id/password to yours.
Click next to choose the entity framework version.
/project/visual_csharp/resources/AE0F5D2B-52F8-5845-8572-D7C586982E02.webp
And then choose the right tables required.
/project/visual_csharp/resources/5D9B0A1D-62CE-570C-BA61-24557B6F4E68.webp
Click Finish button to close the wizard. In my sample, the model has two tables added.
/project/visual_csharp/resources/E0A97602-6480-588C-868F-BC044ABF9CB5.webp
Step 2 LINQ to Teradata
With the generated entity objects, it will be very straightforward to query from the database.
The sample code looks like:
static void Main(string[] args)
{
DisplayEmployeeInfoFromTeraData2();
Console.ReadLine();
}
private static void DisplayEmployeeInfoFromTeraData2()
{
var db = new TDSampleContainer();
var query = from emp in db.Employees.AsQueryable()
select new
{
EmployeeID = emp.EmployeeID,
EmployeeName = emp.EmployeeName,
EmployeeSalutation = emp.Gendar == "M" ? "Dear MR " + emp.EmployeeName :
"Dear MS " + emp.EmployeeName
};
foreach (var e in query)
{
Console.WriteLine("EmployeeID = {0}", e.EmployeeID);
Console.WriteLine("EmployeeName = {0}", e.EmployeeName);
Console.WriteLine("Employee Salutation = {0}", e.EmployeeSalutation);
Console.WriteLine();
}
Console.WriteLine("{0} Records", query.Count());
}
The result
/project/visual_csharp/resources/E1D79FD9-D2BE-5D43-9714-87CF2A8567E0.webp
Summary
With .NET Data Provider for Teradata, you can easily operate on the database with Linq. If you want to learn more about LINQ, please visit the following link: