{"id":78936,"date":"2026-11-10T19:43:03","date_gmt":"2026-11-10T19:43:03","guid":{"rendered":"http:\/\/bangla.sitestree.com\/?p=78936"},"modified":"2026-11-10T19:55:35","modified_gmt":"2026-11-10T19:55:35","slug":"rewritten-sql-server-69886","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=78936","title":{"rendered":"C# and SQL Server: From Foreign Keys to Reusable Database Commands"},"content":{"rendered":"<p>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.<\/p>\n<h2>Design the schema first<\/h2>\n<p>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.<\/p>\n<h2>A stored procedure with output<\/h2>\n<pre><code>CREATE OR ALTER PROCEDURE dbo.CountCustomerOrders\n @CustomerID int,\n @OrderCount int OUTPUT\nAS\nBEGIN\n SET NOCOUNT ON;\n SELECT @OrderCount = COUNT(*)\n FROM dbo.Orders\n WHERE CustomerID = @CustomerID;\nEND;<\/code><\/pre>\n<h2>Call queries with parameters<\/h2>\n<p>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.<\/p>\n<h2>Transaction boundaries<\/h2>\n<p>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.<\/p>\n<h2>Exercise<\/h2>\n<p>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.<\/p>\n<hr\/>\n<p><strong>AI, sources and reuse:<\/strong> This is an AI-generated rewrite informed by <a href=\"https:\/\/bangla.sitestree.com\/?p=69886\">the original Open Education post (#69886)<\/a>, 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. <strong>AI can make mistakes.<\/strong> Verify version support, code correctness, and security practices against Microsoft documentation, and test with nonproduction data first.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[20,182],"tags":[466],"class_list":["post-78936","post","type-post","status-publish","format-standard","hentry","category-information-technology","category---blog","tag-sql-server","item-wrap"],"jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":78933,"url":"http:\/\/bangla.sitestree.com\/?p=78933","url_meta":{"origin":78936,"position":0},"title":"Using C# with SQL Server: Relationships, Procedures, and Safe Parameters","author":"Sayed","date":"November 10, 2026","format":false,"excerpt":"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.","rel":"","context":"In &quot;Information Technology&quot;","block_context":{"text":"Information Technology","link":"http:\/\/bangla.sitestree.com\/?cat=20"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":78924,"url":"http:\/\/bangla.sitestree.com\/?p=78924","url_meta":{"origin":78936,"position":1},"title":"SQL Server Dynamic SQL, Procedures, Cursors, and Injection: Concepts and Examples","author":"Sayed","date":"November 10, 2026","format":false,"excerpt":"Stored procedures, dynamic SQL, and cursors solve different problems. Good T-SQL design starts with ordinary set-based queries and introduces these features only when they are warranted.","rel":"","context":"In &quot;Information Technology&quot;","block_context":{"text":"Information Technology","link":"http:\/\/bangla.sitestree.com\/?cat=20"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":78949,"url":"http:\/\/bangla.sitestree.com\/?p=78949","url_meta":{"origin":78936,"position":2},"title":"SQL and SQL Server Interview Questions: Concepts with Short Answers","author":"Sayed","date":"November 10, 2026","format":false,"excerpt":"A good SQL Server interview review connects fundamental relational principles to practical database development, administration, and troubleshooting.","rel":"","context":"In &quot;Information Technology&quot;","block_context":{"text":"Information Technology","link":"http:\/\/bangla.sitestree.com\/?cat=20"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":69886,"url":"http:\/\/bangla.sitestree.com\/?p=69886","url_meta":{"origin":78936,"position":3},"title":"Random C# and MS SQl Server #19","author":"Author-Check- Article-or-Video","date":"August 21, 2021","format":false,"excerpt":"Foreign Key in Table Declaration CREATE TABLE ORDERS ( ID integer primary key, Order_Date datetime, Customer_ID integer references CUSTOMER(ID), Amount double); ALTER TABLE ORDERS ADD FOREIGN KEY (customer_id) REFERENCES CUSTOMER(ID); Stored Procedure Example CREATE PROCEDURE [dbo].[procedure_name] @param1 VARCHAR(100) ,@param2 VARCHAR(200) OUTPUT AS BEGIN DECLARE @param3 VARCHAR(100) SET @param3 = '\u2026","rel":"","context":"In &quot;C# - Misc&quot;","block_context":{"text":"C# - Misc","link":"http:\/\/bangla.sitestree.com\/?cat=1973"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":70287,"url":"http:\/\/bangla.sitestree.com\/?p=70287","url_meta":{"origin":78936,"position":4},"title":"Random C# and MS SQl Server #.Net Web Applications","author":"Author-Check- Article-or-Video","date":"August 31, 2021","format":false,"excerpt":"Brought from: http:\/\/salearningschool.com\/displayArticle.php?table=Articles&articleID=1321&title=Random%20C#%20and%20MS%20SQl%20Server Foreign Key in Table Declaration CREATE TABLE ORDERS ( ID integer primary key, Order_Date datetime, Customer_ID integer references CUSTOMER(ID), Amount double ); ALTER TABLE ORDERS ADD FOREIGN KEY (customer_id) REFERENCES CUSTOMER(ID); Stored Procedure Example CREATE PROCEDURE [dbo].[procedure_name] @param1 VARCHAR(100) ,@param2 VARCHAR(200) OUTPUT AS BEGIN DECLARE @param3 VARCHAR(100)\u2026","rel":"","context":"In &quot;C# - Misc&quot;","block_context":{"text":"C# - Misc","link":"http:\/\/bangla.sitestree.com\/?cat=1973"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":78941,"url":"http:\/\/bangla.sitestree.com\/?p=78941","url_meta":{"origin":78936,"position":5},"title":"SQL Server Indexes Explained: Clustered, Nonclustered, and Composite Keys","author":"Sayed","date":"November 10, 2026","format":false,"excerpt":"An index helps the SQL Server query optimizer find rows efficiently, but every index carries maintenance costs. Good index design begins with real workloads rather than indexing every column.","rel":"","context":"In &quot;Information Technology&quot;","block_context":{"text":"Information Technology","link":"http:\/\/bangla.sitestree.com\/?cat=20"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/78936","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=78936"}],"version-history":[{"count":1,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/78936\/revisions"}],"predecessor-version":[{"id":78998,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/78936\/revisions\/78998"}],"wp:attachment":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=78936"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=78936"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=78936"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}