Using stored procedures is one way, and has been in widespread use for many years.
A more modern way to interact with SQL Server databases from C# (or any .NET language) is to use Entity Framework. The advantage of Entity Framework is that it provides a higher level of abstraction.
To quote from Microsoft (http://msdn.microsoft.com/en-us/library/bb399572.aspx ):
The ADO.NET Entity Framework enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits:
- Applications can work in terms of a more application-centric
conceptual model, including types with inheritance, complex members,
- Applications are freed from hard-coded dependencies on a particular
data engine or storage schema.
- Mappings between the conceptual model and the storage-specific schema
can change without changing the application code.
- Developers can work with a consistent application object model that
can be mapped to various storage schemas, possibly implemented in
different database management systems.
- Multiple conceptual models can be mapped to a single storage schema.
- Language-integrated query (LINQ) support provides compile-time syntax
validation for queries against a conceptual model.
The use of an ORM vs Stored Procedures involves tradeoffs, particularly in terms of security and where the logic resides.
The "classic" approach to development with SQL Server is to have the application logic reside in stored procedures and programs only given security rights to execute stored procedures, not update tables directly. The concept here is that stored procedures are the business logic layer for the application(s). While the theory is sound, it has tended to fall out of favor for various reasons, being replaced by implementing the business logic in a programming language like C# or VB. Good applications are still implemented with a tiered approach, including separation of concerns etc. but are more likely to follow a pattern like MVC.
One downside of implementing logic in the ORM rather than the database is ease of debugging and testing data integrity rules by those responsible for the database (DA or DBA). Take the classic example of transferring money from your checking to savings account, it is important that this be done as an atomic unit of work, in other words sandwiched in a transaction. If this sort of transfer is only allowed to be done through a stored procedure it is relatively easy for the DA and auditors to QA the stored procedure.
If on the other hand this is done via an ORM like Entity Framework and in production it is discovered that on rare occasions money is taken from checking but not put into savings debugging may be far more complex, particularly if multiple programs are potentially involved. This would most likely be an edge case, perhaps involving peculiar hardware issues that need to occur in a particular sequence etc. How does one test for this?