C# and SQL Server: From Foreign Keys to Reusable Database Commands

Database-backed .NET applications depend on both a correct schema and correctly parameterized application queries. This tutorial links foreign keys, stored procedures, and prepared SQL commands.

Design the schema first

A foreign key from Orders to Customers prevents an order from referencing a nonexistent customer, provided the constraint is enabled and the relationship is correctly modelled.

A stored procedure with output

CREATE OR ALTER PROCEDURE dbo.CountCustomerOrders
 @CustomerID int,
 @OrderCount int OUTPUT
AS
BEGIN
 SET NOCOUNT ON;
 SELECT @OrderCount = COUNT(*)
 FROM dbo.Orders
 WHERE CustomerID = @CustomerID;
END;

Call queries with parameters

Use Microsoft.Data.SqlClient and a SqlCommand; choose CommandType.StoredProcedure when invoking a procedure by name, and set the Direction property on output parameters. Prefer explicitly typed SqlParameter objects to ambiguous inferred types.

Transaction boundaries

When a business operation changes several tables, group the dependent writes into a transaction and commit only when each step succeeds. Catch errors, roll back, and avoid holding a transaction open while awaiting external services.

Exercise

Add Products and OrderLines with primary/foreign keys; create a procedure to return items from one order, and call it with a parameter from a small .NET console application.


AI, sources and reuse: This is an AI-generated rewrite informed by the original Open Education post (#69886), with additional explanations and examples. The new article text may be copied, adapted, and republished with or without a reference to this site. Third-party resources and trademarks keep their own rights. AI can make mistakes. Verify version support, code correctness, and security practices against Microsoft documentation, and test with nonproduction data first.

PHP Functions for SQL Server: Historical MSSQL APIs and Supported Replacements

A historic PHP API family exposed functions beginning with mssql_, including connection management, result fetching, stored-procedure execution, and metadata inspection. That API is no longer suitable for modern PHP applications.

Mapping old ideas to new APIs

Legacy mssql_connect/mssql_query/mssql_fetch_assoc correspond conceptually to sqlsrv_connect/sqlsrv_query/sqlsrv_fetch_array or PDO connection, prepare, execute, and fetch methods. They are not drop-in replacements: argument types, error handling, and driver installation differ.

PDO example

$pdo = new PDO(
    "sqlsrv:Server=localhost;Database=TrainingDB",
    getenv("DB_USER"),
    getenv("DB_PASSWORD"),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$stmt = $pdo->prepare(
    "SELECT ProductID, ProductName FROM dbo.Products WHERE ProductID = ?"
);
$stmt->execute([12]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

Driver considerations

Select a driver release compatible with your PHP runtime and Microsoft’s ODBC driver, and verify encryption and certificate configuration. Use UTF-8 consistently and test date/time and decimal conversions.

Migration checklist

Inventory all mssql_ calls, identify stored procedures and output parameters, replace query concatenation with parameters, add error handling, and run regression tests against a staging database.


AI, sources and reuse: This is an AI-generated rewrite informed by the original Open Education post (#70087), with additional explanations and examples. The new article text may be copied, adapted, and republished with or without a reference to this site. Third-party resources and trademarks keep their own rights. AI can make mistakes. Verify version support, code correctness, and security practices against Microsoft documentation, and test with nonproduction data first.

Calling SQL Server Stored Procedures from PHP: Modern Drivers and Examples

Early PHP applications commonly used mssql_connect, mssql_init, and mssql_bind. Those functions belonged to an obsolete extension and should not be copied into a new application.

Choose a supported driver

For PHP with Microsoft SQL Server, evaluate the Microsoft Drivers for PHP for SQL Server (SQLSRV or PDO_SQLSRV), matching PHP, operating system, and SQL Server versions.

Example: SQLSRV stored procedure call

$connection = sqlsrv_connect($server, [
  "Database" => "TrainingDB",
  "UID" => getenv("DB_USER"),
  "PWD" => getenv("DB_PASSWORD")
]);
if ($connection === false) { throw new RuntimeException("Connection failed"); }
$id = 17;
$params = [[$id, SQLSRV_PARAM_IN]];
$stmt = sqlsrv_query($connection, "{CALL dbo.GetCustomer(?)}", $params);
if ($stmt === false) { throw new RuntimeException("Query failed"); }
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    echo htmlspecialchars((string)$row["CustomerName"], ENT_QUOTES, "UTF-8");
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($connection);

Procedure contract

The example assumes dbo.GetCustomer accepts an integer parameter. For output parameters and result sets, consult the current driver documentation, since binding directions and buffer types matter.

Security essentials

Use least-privilege accounts, encrypted connections with valid certificates, and server-side input validation. Never return raw database error details to website visitors.


AI, sources and reuse: This is an AI-generated rewrite informed by the original Open Education post (#70089), with additional explanations and examples. The new article text may be copied, adapted, and republished with or without a reference to this site. Third-party resources and trademarks keep their own rights. AI can make mistakes. Verify version support, code correctness, and security practices against Microsoft documentation, and test with nonproduction data first.

Using C# with SQL Server: Relationships, Procedures, and Safe Parameters

C# applications typically access SQL Server through ADO.NET. A reliable design starts with proper relational constraints, calls stored procedures using typed parameters, and manages connections predictably.

Model keys and foreign keys

Define a primary key for each record and a foreign key for relationships. SQL Server does not use the DOUBLE column type in the way some other databases do; decimal(p,s), float, or money-like types have different trade-offs.

T-SQL schema example

CREATE TABLE dbo.Customers (
 CustomerID int NOT NULL PRIMARY KEY,
 CustomerName nvarchar(150) NOT NULL
);
CREATE TABLE dbo.Orders (
 OrderID int NOT NULL PRIMARY KEY,
 CustomerID int NOT NULL REFERENCES dbo.Customers(CustomerID),
 Amount decimal(12,2) NOT NULL CHECK (Amount >= 0)
);

A parameterized .NET call

using Microsoft.Data.SqlClient;
using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand(
    "SELECT OrderID, Amount FROM dbo.Orders WHERE CustomerID = @id",
    connection);
command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = 42;
connection.Open();
using var reader = command.ExecuteReader();
while (reader.Read())
    Console.WriteLine(reader.GetInt32(0));

Safer design

In modern projects prefer the supported Microsoft.Data.SqlClient provider, parameterize values, scope connections with using, and load secrets from secure configuration rather than source files. Explicitly verify types and lengths for production code.


AI, sources and reuse: This is an AI-generated rewrite informed by the original Open Education post (#70287), with additional explanations and examples. The new article text may be copied, adapted, and republished with or without a reference to this site. Third-party resources and trademarks keep their own rights. AI can make mistakes. Verify version support, code correctness, and security practices against Microsoft documentation, and test with nonproduction data first.

Adding Custom Code to SQL Server Reporting Services Reports

A paginated report can often meet requirements using report expressions and dataset queries. Custom code is an advanced option for reusable calculations or formatting that the standard expression language does not conveniently express.

Choose the simplest layer

Perform filtering and aggregations in the dataset when it makes results more accurate and reusable. Use report expressions for display logic. Resort to custom code only when the extra maintenance is justified.

Illustrative expression

=IIF(Fields!TotalAmount.Value >= 1000, "Large Order", "Standard Order")

Custom code considerations

Report Builder and older SSRS designers support custom Visual Basic code in some report contexts. Check the target report-server version and designer because supported assemblies and security restrictions vary. Do not embed credentials, filesystem assumptions, or untrusted input in report code.

Test the published version

Validate the same report in the designer and on the server. Render to PDF and Excel; check null values, culture-specific formatting, parameters, and export layout.

Version note

As of SQL Server 2025, Microsoft directs new on-premises reporting deployments to Power BI Report Server. Check current documentation for deployment and supported custom-code features.


About this article: This AI-generated educational rewrite was prepared from an existing Open Education article, with new explanations and illustrative examples where helpful. Read the earlier article (post 72827). The new text may be freely copied, edited, and reused with or without attribution to this site. Third-party material, brand names, and linked resources retain their own rights. AI can make mistakes: verify syntax, version support, security practices, and outcomes against official documentation before use, especially in production.

Managing Product Prices and Updates in SQL Server

Price changes require care because incorrect UPDATE statements can modify every row, break reporting, or overwrite valid historical prices. This tutorial outlines a controlled update workflow.

Find the correct records

Start with a SELECT that uses the exact same predicate as the planned UPDATE. Verify the key, currency, effective date, and expected row count.

Transaction example

BEGIN TRANSACTION;
UPDATE dbo.Products
SET UnitPrice = 49.99
WHERE ProductID = 101;
SELECT @@ROWCOUNT AS RowsChanged;
-- Review the change before choosing:
-- COMMIT TRANSACTION;
-- or ROLLBACK TRANSACTION;

Use constraints and parameters

The example assumes a Products table and is for a test database. In an application, pass ProductID and price as typed parameters. Add checks that prevent invalid negative or null prices if the domain requires them.

Protect history

For real businesses, consider a separate PriceHistory table with effective dates rather than overwriting the only recorded price. Capture actor, change reason, old and new values, and approval when required.

Deployment discipline

Test against a copy of the database, back up data according to policy, and never run a mass update in production without a verified WHERE clause and a rollback plan.


About this article: This AI-generated educational rewrite was prepared from an existing Open Education article, with new explanations and illustrative examples where helpful. Read the earlier article (post 73188). The new text may be freely copied, edited, and reused with or without attribution to this site. Third-party material, brand names, and linked resources retain their own rights. AI can make mistakes: verify syntax, version support, security practices, and outcomes against official documentation before use, especially in production.

Exploring SQL Server Database Objects for Product Management

A product-management application often uses tables for products, categories, inventory, and prices. Learning how to inspect SQL Server objects helps developers understand both the data model and the application’s behavior.

Browse with SSMS

Open Object Explorer, connect to the correct instance, expand Databases, then inspect Tables, Views, Programmability, and Security. Review column types, nullability, indexes, keys, and object dependencies.

Inspect metadata with T-SQL

SELECT s.name AS SchemaName, t.name AS TableName
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.name LIKE '%Product%'
ORDER BY s.name, t.name;

Read sample records safely

Use a read-only connection for exploration. Select explicit columns, apply TOP and ORDER BY for manageable results, and do not expose personal or confidential product data.

Understand relationships

Check primary and foreign keys before editing product prices or inventory. A product may be referenced by order lines or catalogue history, so deletion can break referential integrity.

Practice task

Sketch an ER diagram for Products, Categories, and Prices; list expected constraints; write a SELECT joining those tables; compare the model to the database metadata.


About this article: This AI-generated educational rewrite was prepared from an existing Open Education article, with new explanations and illustrative examples where helpful. Read the earlier article (post 73199). The new text may be freely copied, edited, and reused with or without attribution to this site. Third-party material, brand names, and linked resources retain their own rights. AI can make mistakes: verify syntax, version support, security practices, and outcomes against official documentation before use, especially in production.

Moving SQL Server Stored Procedures Between Databases Safely

Copying a stored procedure to another database requires more than transferring its text. Dependencies, schemas, permissions, compatibility, and deployment order also matter.

Inventory dependencies

Identify referenced tables, views, functions, types, and other procedures. Confirm the destination has an equivalent schema and that cross-database names are intentional.

Generate and review scripts

In SQL Server Management Studio, script the procedure as CREATE or ALTER, inspect the generated definition, and place the reviewed script in source control. Avoid moving secrets or environment-specific server names without review.

Example

USE TargetDatabase;
GO
CREATE OR ALTER PROCEDURE dbo.GetRecentOrders
    @Since date
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate
    FROM dbo.Orders
    WHERE OrderDate >= @Since;
END;
GO

Deployment checklist

Deploy required tables and types first, then functions and procedures. Check ownership, EXECUTE grants, SQL Server version and database compatibility level. Execute unit tests with nonproduction data and verify returned results.

Repeatability

Prefer a version-controlled database project or migration pipeline for ongoing changes. An ad-hoc copy is appropriate only when the destination and rollback plan are understood.


About this article: This AI-generated educational rewrite was prepared from an existing Open Education article, with new explanations and illustrative examples where helpful. Read the earlier article (post 74017). The new text may be freely copied, edited, and reused with or without attribution to this site. Third-party material, brand names, and linked resources retain their own rights. AI can make mistakes: verify syntax, version support, security practices, and outcomes against official documentation before use, especially in production.

Create and Deploy a Paginated SQL Server Report: A Step-by-Step Overview

Paginated reports are designed for precise, printable layouts such as invoices, statements, and operational summaries. A typical report combines a data source, a parameterized dataset, report layout, and deployment.

Prepare the data

Create a read-only reporting login or an appropriately constrained service identity. Write and test the dataset query separately before placing it in a report designer.

Sample dataset

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE OrderDate >= @StartDate
  AND OrderDate < DATEADD(day, 1, @EndDate)
ORDER BY OrderDate, OrderID;

Build the report

In Report Builder or a compatible development environment, create a shared or embedded data source, define StartDate and EndDate parameters, add a table or matrix, and configure grouping, sorting, page breaks, and headers.

Deploy and validate

Publish to a report server with the correct permissions and credentials. Test PDF rendering, empty datasets, large result sets, date boundaries, and subscriptions if those are enabled.

Version note

For SQL Server 2025, Microsoft consolidates on-premises reporting under Power BI Report Server. Earlier SSRS installations and guidance should be assessed for their target version.


About this article: This AI-generated educational rewrite was prepared from an existing Open Education article, with new explanations and illustrative examples where helpful. Read the earlier article (post 74135). The new text may be freely copied, edited, and reused with or without attribution to this site. Third-party material, brand names, and linked resources retain their own rights. AI can make mistakes: verify syntax, version support, security practices, and outcomes against official documentation before use, especially in production.

Data Mining with Excel and SQL Server: Legacy Tools and Modern Alternatives

Excel and SQL Server have been used together for extracting, cleaning, exploring, and modelling business data. It is important to distinguish current analytical workflows from historical SQL Server Analysis Services data-mining tools.

A sensible workflow

Load transaction data into a staging table, validate missing values and outliers, create a curated dataset with T-SQL, and export or connect that dataset to Excel, Power BI, Python, or another analytical tool.

Example analytical query

SELECT CustomerID,
       COUNT_BIG(*) AS OrderCount,
       SUM(OrderTotal) AS LifetimeRevenue
FROM dbo.Orders
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID;

Legacy compatibility

The original Analysis Services data-mining algorithms and related Excel add-ins are historical technologies. Microsoft says SSAS data mining was deprecated in SQL Server 2017 and discontinued in SQL Server 2022. Do not design a new system assuming that discontinued feature is available.

Current practice

Use SQL Server for reliable storage, data preparation, and governance; choose a separately supported analytics platform for forecasting, classification, clustering, and experiment tracking. Evaluate model accuracy and leakage independently of the database engine.

Documentation

Microsoft’s historical feature reference: https://learn.microsoft.com/en-us/analysis-services/data-mining/data-mining-ssas


About this article: This AI-generated educational rewrite was prepared from an existing Open Education article, with new explanations and illustrative examples where helpful. Read the earlier article (post 74166). The new text may be freely copied, edited, and reused with or without attribution to this site. Third-party material, brand names, and linked resources retain their own rights. AI can make mistakes: verify syntax, version support, security practices, and outcomes against official documentation before use, especially in production.