You can access data in the following ways (assuming you are using .NET) - amongst others:
(1) using ADO.NET (and LINQ)
(2) using ODBC (not recommended if you can use other options)
(3) using T-SQL via ADO.NET
(3A) using T-SQL via LINQ
(4) using SQL Server CLR (C# code on the server side)
The debate between using (1) and (3) is an ongoing debate, however, if you don't have intentions to change your database to another one, it is fairly common to program the database layer in T-SQL. T-SQL is mostly used to do basic Create, Read, Update and Delete operations as well as triggers processing. Unlike plain ADO.NET, T-SQL may be more secure since it keeps your code SQL free.
Most ORM tools generate code for either (1) or (4). So your tool may affect your decision.
If you have complex logic and intensive calculation or if your require several calls to the database before your unit of work is completed, you could then use (4).
You may be interested to know more about this in the following link: