C# WinForms stored procedure operation database example explanation

Operating databases through stored procedures in C# WinForms is an important means to improve application performance and security.

The following is a detailed analysis and complete example:

1. Stored process basics

Advantages：

Precompiled execution, better performance
Prevent SQL injection attacks
Reduce the amount of data transmitted on the network
Convenient to centralized management of business logic

Create an example（SQL Server）：

CREATE PROCEDURE sp_AddEmployee
    @Name NVARCHAR(50),
    @Age INT,
    @Email NVARCHAR(100),
    @NewID INT OUTPUT
AS
BEGIN
    INSERT INTO Employees (Name, Age, Email) 
    VALUES (@Name, @Age, @Email)
    
    SET @NewID = SCOPE_IDENTITY()
END

2. C# call process

1. Database connection configuration

using ;
using ;

string connectionString = "Server=.;Database=YourDB;Integrated Security=True;";

2. Execute stored procedures (addition, deletion and modification)

public bool AddEmployee(string name, int age, string email, out int newId)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        try
        {
            ();
            using (SqlCommand cmd = new SqlCommand("sp_AddEmployee", conn))
            {
                 = ;
                
                // Enter parameters                ("@Name", , 50).Value = name;
                ("@Age", ).Value = age;
                ("@Email", , 100).Value = email;
                
                // Output parameters                SqlParameter outputParam = new SqlParameter("@NewID", )
                {
                    Direction = 
                };
                (outputParam);

                ();
                
                newId = (int);
                return true;
            }
        }
        catch (SqlException ex)
        {
            ($"Database error: {}");
            newId = -1;
            return false;
        }
    }
}

3. Query data

public DataTable GetEmployees(int minAge)
{
    DataTable dt = new DataTable();
    
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand("sp_GetEmployees", conn))
        {
             = ;
            ("@MinAge", minAge);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            (dt);
        }
    }
    
    return dt;
}

III. Transaction processing

public bool UpdateEmployeeTransaction(int id, string newName)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        ();
        SqlTransaction transaction = ();
        
        try
        {
            using (SqlCommand cmd = new SqlCommand("sp_UpdateEmployee", conn, transaction))
            {
                ("@ID", id);
                ("@NewName", newName);
                ();
            }
            
            ();
            return true;
        }
        catch (Exception ex)
        {
            ();
            ($"Operation failed: {}");
            return false;
        }
    }
}

4. Key points to note

Parameter safety

Parameterized query must be used
Identify parameter types and lengths

Resource Management

using (SqlConnection conn = new SqlConnection(...))
using (SqlCommand cmd = new SqlCommand(...))
{
    // Automatically release resources}

Error handling

try 
{
    // Database operation}
catch (SqlException ex)
{
    // Handle database-specific errors    if ( == 547) // Foreign key constraint error}
catch (Exception ex)
{
    // General exception handling}

Performance optimization

Use SET NOCOUNT ON in stored procedures
Avoid complex calculations in stored procedures
Create index reasonably

V. Typical stored procedure types

Data operation：INSERT/UPDATE/DELETE
Query return: Single result set/multi result set
Pagination query: Use ROW_NUMBER() to implement
Business Processing: Multi-table operations containing transactions

The complete example can be used directly in actual projects, pay attention to the actual situation:

Modify the connection string
Adjust parameter type and length
Add specific business logic processing
Improve exception logging function

Implementing the data access layer through stored procedures makes WinForms applications easier to maintain while improving security and execution efficiency.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.