Structural/ conceptual advice on working with custom functions for SQL database from C#

I'm wondering if any seasoned C#/ SQL developers could give me a nudge in the right direction as I get started with a new project. I'm coming from an MS Access/ VBA world, and I'm trying to develop a market- data system with C#, SQL server express back= end. My main question is what is the best way to work with custom functions? In access/ vba, it was simple to write udfs, and then nest them as deeply as I needed to continue creating new functions. So in C#/SQL, sho开发者_开发技巧uld I have a static functions class in C#? Or pass @parameters to SQL? It doesn't seem as straightforward to me to work with the database from my application. As always thanks in advance...

for a start, you might follow a very common pattern, which is:

you can create your sql functions in the sql-server database. keep in mind, these don't DO things to your application -- they simply do things to data (such as conversions, math, etc.)

you can access your database from your application using database stored procedures, which accept parameters. Stored procedures are what you would use, for example, to return a data set to your application, or any other DML.

finally, you can have non-data functions / subs in your application layer. Ex.: you might have a send-email function, get_data function that calls on a database stored procedure, etc.

lookup the keywords for more information on what they are / how to use them.

stored procedure
udf, user defined function
tsql
sql-server

After adding the stored procedure to your Database table you can simply call it in C# like this:

using System.Data.SqlClient;

private void DoSomething(string storedProcedure, string tableName)
{
 using (var conn = new SqlConnection(ConnectionString))
 {
  conn.Open();
  var cmd = new SqlCommand(storedProcedure, conn) { CommandType = CommandType.StoredProcedure };
  cmd.Parameters.Add(new SqlParameter("@tablename", SqlDbType.NVarChar, 80));
  cmd.Parameters["@tablename"].Value = tableName;
  cmd.ExecuteNonQuery();
 }
}