{"id":1974,"date":"2024-10-31T18:18:03","date_gmt":"2024-10-31T16:18:03","guid":{"rendered":"https:\/\/epicmarketing.co.il\/notebook\/?p=1974"},"modified":"2024-10-31T18:30:45","modified_gmt":"2024-10-31T16:30:45","slug":"database-interaction-using-ado-net-and-stored-procedures","status":"publish","type":"post","link":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/","title":{"rendered":"Database Interaction Using ADO.NET and Stored Procedures"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Learning Objectives<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Understand how to use <strong>ADO.NET<\/strong> for database interaction in .NET Core.<\/li>\n\n\n\n<li>Learn to perform <strong>CRUD operations<\/strong> with <strong>stored procedures<\/strong> in SQL Server.<\/li>\n\n\n\n<li>Learn best practices for <strong>SQL connection management<\/strong> and working with <strong>commands and parameters<\/strong>.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">ADO.NET Basics<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What is ADO.NET?<\/strong> <strong>ADO.NET<\/strong> is a data access technology that allows .NET applications to interact with databases. It provides the means to connect, execute commands, and retrieve results from databases such as SQL Server.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>In this chapter, we'll use <code>System.Data.SqlClient<\/code> and <code>Microsoft.Data.SqlClient<\/code> to interact with SQL Server.<\/li>\n\n\n\n<li>ADO.NET is <strong>lightweight<\/strong> and <strong>high-performance<\/strong>, and it's a good alternative to Entity Framework (EF) for those who prefer direct database interaction.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Setting Up SQL Connection<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">To connect to a SQL Server database, we need to use <code>SqlConnection<\/code>:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\nusing System.Data.SqlClient;\n\nstring connectionString = &quot;Your_Connection_String_Here&quot;;\nusing (SqlConnection connection = new SqlConnection(connectionString))\n{\n    connection.Open();\n    \/\/ Execute database commands\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Real-Life Example<\/strong>: In an e-commerce application, use <code>SqlConnection<\/code> to fetch product details directly from the database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Working with Stored Procedures<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What are Stored Procedures?<\/strong> A <strong>stored procedure<\/strong> is a prepared SQL code that can be saved and reused. Instead of sending raw SQL commands, you call a stored procedure, which can help reduce SQL injection risks and improve maintainability.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Creating a Stored Procedure in SQL Server<\/strong>:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE PROCEDURE GetProductById\n    @ProductId INT\nAS\nBEGIN\n    SELECT * FROM Products WHERE Id = @ProductId;\nEND\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Calling a Stored Procedure Using ADO.NET<\/strong>: We use <code>SqlCommand<\/code> to execute stored procedures.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\nusing (SqlConnection connection = new SqlConnection(connectionString))\n{\n    connection.Open();\n    using (SqlCommand command = new SqlCommand(&quot;GetProductById&quot;, connection))\n    {\n        command.CommandType = CommandType.StoredProcedure;\n        command.Parameters.AddWithValue(&quot;@ProductId&quot;, 1);\n\n        using (SqlDataReader reader = command.ExecuteReader())\n        {\n            while (reader.Read())\n            {\n                Console.WriteLine($&quot;Product Name: {reader&#x5B;&quot;Name&quot;]}, Price: {reader&#x5B;&quot;Price&quot;]}&quot;);\n            }\n        }\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Real-Life Example<\/strong>: Use stored procedures in a financial application to execute transactions safely without exposing raw SQL to the application.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">SQL Connection Management<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Managing Connections and Commands<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Always <strong>open<\/strong> a connection right before executing a command and <strong>close<\/strong> it immediately afterward.<\/li>\n\n\n\n<li>Use <strong><code>using<\/code> statements<\/strong> to ensure connections are closed automatically, even in case of an error.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example with Best Practices<\/strong>:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\nusing (SqlConnection connection = new SqlConnection(connectionString))\n{\n    try\n    {\n        connection.Open();\n        using (SqlCommand command = new SqlCommand(&quot;AddNewProduct&quot;, connection))\n        {\n            command.CommandType = CommandType.StoredProcedure;\n            command.Parameters.AddWithValue(&quot;@Name&quot;, &quot;New Product&quot;);\n            command.Parameters.AddWithValue(&quot;@Price&quot;, 100.0m);\n            command.ExecuteNonQuery();\n        }\n    }\n    catch (Exception ex)\n    {\n        Console.WriteLine($&quot;An error occurred: {ex.Message}&quot;);\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Real-Life Example<\/strong>: In a user management system, use connection management best practices to create new user accounts and ensure the connection is properly closed.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Examples<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Simple Example: Fetching Data with a Stored Procedure<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a stored procedure to get a product by ID and call it using ADO.NET.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Code Snippet<\/strong>:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\nusing (SqlConnection connection = new SqlConnection(connectionString))\n{\n    connection.Open();\n    using (SqlCommand command = new SqlCommand(&quot;GetProductById&quot;, connection))\n    {\n        command.CommandType = CommandType.StoredProcedure;\n        command.Parameters.AddWithValue(&quot;@ProductId&quot;, 1);\n        using (SqlDataReader reader = command.ExecuteReader())\n        {\n            while (reader.Read())\n            {\n                Console.WriteLine($&quot;Product: {reader&#x5B;&quot;Name&quot;]}, Price: {reader&#x5B;&quot;Price&quot;]}&quot;);\n            }\n        }\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Real-Life Example<\/strong>: Fetch product information from an e-commerce database to display to customers.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Simple Example: Adding Data Using a Stored Procedure<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a stored procedure for adding a new product and call it from your code.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Code Snippet<\/strong>:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\nusing (SqlConnection connection = new SqlConnection(connectionString))\n{\n    connection.Open();\n    using (SqlCommand command = new SqlCommand(&quot;AddNewProduct&quot;, connection))\n    {\n        command.CommandType = CommandType.StoredProcedure;\n        command.Parameters.AddWithValue(&quot;@Name&quot;, &quot;Tablet&quot;);\n        command.Parameters.AddWithValue(&quot;@Price&quot;, 300.0m);\n        command.ExecuteNonQuery();\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Real-Life Example<\/strong>: In an inventory management system, add new products to the database.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Simple Example: Updating Data Using a Stored Procedure<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a stored procedure for updating a product's price and call it from your code.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Code Snippet<\/strong>:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\nusing (SqlConnection connection = new SqlConnection(connectionString))\n{\n    connection.Open();\n    using (SqlCommand command = new SqlCommand(&quot;UpdateProductPrice&quot;, connection))\n    {\n        command.CommandType = CommandType.StoredProcedure;\n        command.Parameters.AddWithValue(&quot;@ProductId&quot;, 1);\n        command.Parameters.AddWithValue(&quot;@Price&quot;, 1200.0m);\n        command.ExecuteNonQuery();\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Real-Life Example<\/strong>: In a financial application, adjust the price of a product during a promotional campaign.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Key Takeaways<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>ADO.NET<\/strong> allows you to directly interact with a SQL Server database, providing a high degree of control.<\/li>\n\n\n\n<li><strong>Stored procedures<\/strong> are used for executing database commands more efficiently and securely.<\/li>\n\n\n\n<li><strong>Connection management<\/strong> is crucial to ensure that database resources are used correctly and avoid potential issues like connection leaks.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Practical Questions<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>How can you use a stored procedure in ADO.NET to fetch data from a SQL Server database?<\/li>\n\n\n\n<li>What are the best practices for managing SQL connections in ADO.NET?<\/li>\n\n\n\n<li>How can you use <code>SqlCommand<\/code> to execute a stored procedure that updates a record in a database?<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">New Concepts in .NET 8<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>New in .NET 8<\/strong>:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Improved integration for ADO.NET to support <strong>Native AOT Compilation<\/strong>, allowing you to create smaller and faster executables. This is particularly useful when deploying APIs to cloud environments where cold-start performance is crucial.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Real-World Application<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">These concepts are highly applicable in building real-world, scalable APIs where direct database access with high performance is required. You could use ADO.NET to interact directly with your database in scenarios where EF's abstraction is not necessary or might introduce performance overhead, such as:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>E-commerce systems<\/strong>: Directly retrieving product information.<\/li>\n\n\n\n<li><strong>User management systems<\/strong>: Safely storing user credentials.<\/li>\n\n\n\n<li><strong>Financial applications<\/strong>: Executing secure transactions via stored procedures.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">e-commerce scenario example<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This example demonstrates an <strong>e-commerce scenario<\/strong> where we have a product API that handles fetching product details. It follows a layered approach:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Controller<\/strong>: Manages the incoming HTTP request and returns a response.<\/li>\n\n\n\n<li><strong>Service (Business Logic Layer)<\/strong>: Contains the business logic, including calling the database repository.<\/li>\n\n\n\n<li><strong>Repository (Data Access Layer)<\/strong>: Uses ADO.NET to interact with the SQL Server database.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Step-by-Step Full Example<\/strong><\/h2>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Step 1: Create the Product Model<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Define a <code>Product<\/code> model that will be used to represent the product data.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\npublic class Product\n{\n    public int Id { get; set; }\n    public string Name { get; set; }\n    public decimal Price { get; set; }\n}\n<\/pre><\/div>\n\n\n<h4 class=\"wp-block-heading\"><strong>Step 2: Create the Repository Layer for Database Interaction<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>repository<\/strong> will handle the direct interaction with the database using <strong>ADO.NET<\/strong>.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\nusing System.Data.SqlClient;\n\npublic interface IProductRepository\n{\n    Product GetProductById(int productId);\n}\n\npublic class ProductRepository : IProductRepository\n{\n    private readonly string _connectionString;\n\n    public ProductRepository(string connectionString)\n    {\n        _connectionString = connectionString;\n    }\n\n    public Product GetProductById(int productId)\n    {\n        Product product = null;\n\n        using (SqlConnection connection = new SqlConnection(_connectionString))\n        {\n            connection.Open();\n            using (SqlCommand command = new SqlCommand(&quot;GetProductById&quot;, connection))\n            {\n                command.CommandType = System.Data.CommandType.StoredProcedure;\n                command.Parameters.AddWithValue(&quot;@ProductId&quot;, productId);\n\n                using (SqlDataReader reader = command.ExecuteReader())\n                {\n                    if (reader.Read())\n                    {\n                        product = new Product\n                        {\n                            Id = (int)reader&#x5B;&quot;Id&quot;],\n                            Name = reader&#x5B;&quot;Name&quot;].ToString(),\n                            Price = (decimal)reader&#x5B;&quot;Price&quot;]\n                        };\n                    }\n                }\n            }\n        }\n\n        return product;\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Stored Procedure<\/strong>: We are calling a stored procedure named <code>GetProductById<\/code> to get the product details.<strong>Connection Management<\/strong>: The connection is managed using a <code>using<\/code> statement to ensure it is closed properly.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Step 3: Create the Service Layer for Business Logic<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>service layer<\/strong> will handle any business logic required for product retrieval.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\npublic interface IProductService\n{\n    Product GetProductById(int productId);\n}\n\npublic class ProductService : IProductService\n{\n    private readonly IProductRepository _productRepository;\n\n    public ProductService(IProductRepository productRepository)\n    {\n        _productRepository = productRepository;\n    }\n\n    public Product GetProductById(int productId)\n    {\n        \/\/ Add any additional business logic here if needed\n        return _productRepository.GetProductById(productId);\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">The service simply calls the repository to get the product but can also have additional business logic if needed.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Step 4: Create the Product Controller<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>controller<\/strong> will handle the HTTP request and interact with the service to get the product data.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\nusing Microsoft.AspNetCore.Mvc;\n\n&#x5B;ApiController]\n&#x5B;Route(&quot;api\/&#x5B;controller]&quot;)]\npublic class ProductController : ControllerBase\n{\n    private readonly IProductService _productService;\n\n    public ProductController(IProductService productService)\n    {\n        _productService = productService;\n    }\n\n    &#x5B;HttpGet(&quot;{id}&quot;)]\n    public IActionResult GetProduct(int id)\n    {\n        var product = _productService.GetProductById(id);\n        if (product == null)\n        {\n            return NotFound(&quot;Product not found&quot;);\n        }\n        return Ok(product);\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Routing<\/strong>: The controller route is defined as <code>api\/product<\/code>, and the action route is <code>\/{id}<\/code>, allowing clients to call <code>GET \/api\/product\/{id}<\/code>.<strong><code>IActionResult<\/code><\/strong>: Returns either <code>200 OK<\/code> with the product data or <code>404 Not Found<\/code> if the product doesn\u2019t exist.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Step 5: Configure Dependency Injection in <code>Program.cs<\/code><\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Add the necessary services to the <strong>dependency injection container<\/strong> and configure the application.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\nvar builder = WebApplication.CreateBuilder(args);\n\n\/\/ Add services to the container.\nbuilder.Services.AddControllers();\n\n\/\/ Add connection string from appsettings.json or environment variable\nstring connectionString = builder.Configuration.GetConnectionString(&quot;DefaultConnection&quot;);\n\n\/\/ Register repository and service for dependency injection\nbuilder.Services.AddScoped&lt;IProductRepository&gt;(provider =&gt; new ProductRepository(connectionString));\nbuilder.Services.AddScoped&lt;IProductService, ProductService&gt;();\n\nvar app = builder.Build();\n\n\/\/ Configure the HTTP request pipeline.\napp.UseRouting();\napp.UseAuthorization();\n\napp.UseEndpoints(endpoints =&gt;\n{\n    endpoints.MapControllers();\n});\n\napp.Run();\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>Connection String<\/strong>: The connection string is retrieved from the configuration and passed to the repository.<strong>DI Registration<\/strong>:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>ProductRepository<\/code> and <code>ProductService<\/code> are registered with the DI container so they can be injected into the controller.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Step 6: SQL Stored Procedure Example<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Ensure you have a <strong>stored procedure<\/strong> in your SQL Server database to retrieve product details by ID.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nCREATE PROCEDURE GetProductById\n    @ProductId INT\nAS\nBEGIN\n    SELECT Id, Name, Price FROM Products WHERE Id = @ProductId;\nEND\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\"><strong>Summary<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This example follows an <strong>end-to-end flow<\/strong>:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Controller<\/strong> (<code>ProductController<\/code>):\n<ul class=\"wp-block-list\">\n<li>Receives a request (<code>GET \/api\/product\/{id}<\/code>) and calls the service layer.<\/li>\n\n\n\n<li>Returns <code>200 OK<\/code> with product data or <code>404 Not Found<\/code> if the product does not exist.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Service Layer<\/strong> (<code>ProductService<\/code>):\n<ul class=\"wp-block-list\">\n<li>Acts as a bridge between the controller and repository.<\/li>\n\n\n\n<li>Contains business logic (in this case, it\u2019s simple, but it can be expanded).<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Repository Layer<\/strong> (<code>ProductRepository<\/code>):\n<ul class=\"wp-block-list\">\n<li>Connects to SQL Server using ADO.NET.<\/li>\n\n\n\n<li>Executes the stored procedure <code>GetProductById<\/code> to fetch the product from the database.<\/li>\n\n\n\n<li>Manages SQL connections, commands, and parameters safely.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Real-Life Use Case<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine you have an <strong>e-commerce<\/strong> application:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A client wants to <strong>fetch product details<\/strong> by providing a product ID.<\/li>\n\n\n\n<li>They call your endpoint (<code>GET \/api\/product\/{id}<\/code>).<\/li>\n\n\n\n<li>The controller sends the request to the business layer (<code>ProductService<\/code>).<\/li>\n\n\n\n<li>The business layer calls the data access layer (<code>ProductRepository<\/code>), which queries the <strong>SQL Server<\/strong> database via a <strong>stored procedure<\/strong>.<\/li>\n\n\n\n<li>The response is returned through the controller as a JSON object to the client.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">This pattern follows <strong>separation of concerns<\/strong>, where:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Controllers are responsible for handling HTTP requests and responses.<\/li>\n\n\n\n<li>The service layer deals with business logic.<\/li>\n\n\n\n<li>The repository handles data access.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">This approach is suitable for real-world projects where scalability, maintainability, and reusability are key considerations.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learning Objectives ADO.NET Basics What is ADO.NET? ADO.NET is a data access technology that allows .NET applications to interact with databases. It provides the means to connect, execute commands, and retrieve results from databases such as SQL Server. Setting Up SQL Connection To connect to a SQL Server database, we need to use SqlConnection: Real-Life [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ocean_post_layout":"","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"","ocean_second_sidebar":"","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"","ocean_custom_header_template":"","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"","ocean_menu_typo_font_family":"","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"ocean_post_oembed":"","ocean_post_self_hosted_media":"","ocean_post_video_embed":"","ocean_link_format":"","ocean_link_format_target":"self","ocean_quote_format":"","ocean_quote_format_link":"post","ocean_gallery_link_images":"on","ocean_gallery_id":[],"footnotes":""},"categories":[79],"tags":[],"class_list":["post-1974","post","type-post","status-publish","format-standard","hentry","category-dotnet-8","entry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Database Interaction Using ADO.NET and Stored Procedures - Code Notebook<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/\" \/>\n<meta property=\"og:locale\" content=\"he_IL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Database Interaction Using ADO.NET and Stored Procedures - Code Notebook\" \/>\n<meta property=\"og:description\" content=\"Learning Objectives ADO.NET Basics What is ADO.NET? ADO.NET is a data access technology that allows .NET applications to interact with databases. It provides the means to connect, execute commands, and retrieve results from databases such as SQL Server. Setting Up SQL Connection To connect to a SQL Server database, we need to use SqlConnection: Real-Life [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/\" \/>\n<meta property=\"og:site_name\" content=\"Code Notebook\" \/>\n<meta property=\"article:published_time\" content=\"2024-10-31T16:18:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-10-31T16:30:45+00:00\" \/>\n<meta name=\"author\" content=\"kerendanino\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u05e0\u05db\u05ea\u05d1 \u05e2\u05dc \u05d9\u05d3\" \/>\n\t<meta name=\"twitter:data1\" content=\"kerendanino\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u05d6\u05de\u05df \u05e7\u05e8\u05d9\u05d0\u05d4 \u05de\u05d5\u05e2\u05e8\u05da\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 \u05d3\u05e7\u05d5\u05ea\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/database-interaction-using-ado-net-and-stored-procedures\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/database-interaction-using-ado-net-and-stored-procedures\\\/\"},\"author\":{\"name\":\"kerendanino\",\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/#\\\/schema\\\/person\\\/195dfc625818eadda7903d456890e24c\"},\"headline\":\"Database Interaction Using ADO.NET and Stored Procedures\",\"datePublished\":\"2024-10-31T16:18:03+00:00\",\"dateModified\":\"2024-10-31T16:30:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/database-interaction-using-ado-net-and-stored-procedures\\\/\"},\"wordCount\":1106,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/#organization\"},\"articleSection\":[\"Dotnet 8\"],\"inLanguage\":\"he-IL\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/database-interaction-using-ado-net-and-stored-procedures\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/database-interaction-using-ado-net-and-stored-procedures\\\/\",\"url\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/database-interaction-using-ado-net-and-stored-procedures\\\/\",\"name\":\"Database Interaction Using ADO.NET and Stored Procedures - Code Notebook\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/#website\"},\"datePublished\":\"2024-10-31T16:18:03+00:00\",\"dateModified\":\"2024-10-31T16:30:45+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/database-interaction-using-ado-net-and-stored-procedures\\\/#breadcrumb\"},\"inLanguage\":\"he-IL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/database-interaction-using-ado-net-and-stored-procedures\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/database-interaction-using-ado-net-and-stored-procedures\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database Interaction Using ADO.NET and Stored Procedures\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/#website\",\"url\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/\",\"name\":\"Code Notebook\",\"description\":\"Easy coding\",\"publisher\":{\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"he-IL\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/#organization\",\"name\":\"Code Notebook\",\"url\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"he-IL\",\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/wp-content\\\/uploads\\\/2023\\\/07\\\/logo-epic-marketing-05.png\",\"contentUrl\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/wp-content\\\/uploads\\\/2023\\\/07\\\/logo-epic-marketing-05.png\",\"width\":3626,\"height\":1942,\"caption\":\"Code Notebook\"},\"image\":{\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/#\\\/schema\\\/person\\\/195dfc625818eadda7903d456890e24c\",\"name\":\"kerendanino\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"he-IL\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/285cc9389c66aa46da1e26a474b1e90e9efaf3fa21f1b928cbd63ce5f0e89c63?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/285cc9389c66aa46da1e26a474b1e90e9efaf3fa21f1b928cbd63ce5f0e89c63?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/285cc9389c66aa46da1e26a474b1e90e9efaf3fa21f1b928cbd63ce5f0e89c63?s=96&d=mm&r=g\",\"caption\":\"kerendanino\"},\"url\":\"https:\\\/\\\/epicmarketing.co.il\\\/notebook\\\/author\\\/kerendanino\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Database Interaction Using ADO.NET and Stored Procedures - Code Notebook","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/","og_locale":"he_IL","og_type":"article","og_title":"Database Interaction Using ADO.NET and Stored Procedures - Code Notebook","og_description":"Learning Objectives ADO.NET Basics What is ADO.NET? ADO.NET is a data access technology that allows .NET applications to interact with databases. It provides the means to connect, execute commands, and retrieve results from databases such as SQL Server. Setting Up SQL Connection To connect to a SQL Server database, we need to use SqlConnection: Real-Life [&hellip;]","og_url":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/","og_site_name":"Code Notebook","article_published_time":"2024-10-31T16:18:03+00:00","article_modified_time":"2024-10-31T16:30:45+00:00","author":"kerendanino","twitter_card":"summary_large_image","twitter_misc":{"\u05e0\u05db\u05ea\u05d1 \u05e2\u05dc \u05d9\u05d3":"kerendanino","\u05d6\u05de\u05df \u05e7\u05e8\u05d9\u05d0\u05d4 \u05de\u05d5\u05e2\u05e8\u05da":"6 \u05d3\u05e7\u05d5\u05ea"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/#article","isPartOf":{"@id":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/"},"author":{"name":"kerendanino","@id":"https:\/\/epicmarketing.co.il\/notebook\/#\/schema\/person\/195dfc625818eadda7903d456890e24c"},"headline":"Database Interaction Using ADO.NET and Stored Procedures","datePublished":"2024-10-31T16:18:03+00:00","dateModified":"2024-10-31T16:30:45+00:00","mainEntityOfPage":{"@id":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/"},"wordCount":1106,"commentCount":0,"publisher":{"@id":"https:\/\/epicmarketing.co.il\/notebook\/#organization"},"articleSection":["Dotnet 8"],"inLanguage":"he-IL","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/","url":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/","name":"Database Interaction Using ADO.NET and Stored Procedures - Code Notebook","isPartOf":{"@id":"https:\/\/epicmarketing.co.il\/notebook\/#website"},"datePublished":"2024-10-31T16:18:03+00:00","dateModified":"2024-10-31T16:30:45+00:00","breadcrumb":{"@id":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/#breadcrumb"},"inLanguage":"he-IL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/epicmarketing.co.il\/notebook\/database-interaction-using-ado-net-and-stored-procedures\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/epicmarketing.co.il\/notebook\/"},{"@type":"ListItem","position":2,"name":"Database Interaction Using ADO.NET and Stored Procedures"}]},{"@type":"WebSite","@id":"https:\/\/epicmarketing.co.il\/notebook\/#website","url":"https:\/\/epicmarketing.co.il\/notebook\/","name":"Code Notebook","description":"Easy coding","publisher":{"@id":"https:\/\/epicmarketing.co.il\/notebook\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/epicmarketing.co.il\/notebook\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"he-IL"},{"@type":"Organization","@id":"https:\/\/epicmarketing.co.il\/notebook\/#organization","name":"Code Notebook","url":"https:\/\/epicmarketing.co.il\/notebook\/","logo":{"@type":"ImageObject","inLanguage":"he-IL","@id":"https:\/\/epicmarketing.co.il\/notebook\/#\/schema\/logo\/image\/","url":"https:\/\/epicmarketing.co.il\/notebook\/wp-content\/uploads\/2023\/07\/logo-epic-marketing-05.png","contentUrl":"https:\/\/epicmarketing.co.il\/notebook\/wp-content\/uploads\/2023\/07\/logo-epic-marketing-05.png","width":3626,"height":1942,"caption":"Code Notebook"},"image":{"@id":"https:\/\/epicmarketing.co.il\/notebook\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/epicmarketing.co.il\/notebook\/#\/schema\/person\/195dfc625818eadda7903d456890e24c","name":"kerendanino","image":{"@type":"ImageObject","inLanguage":"he-IL","@id":"https:\/\/secure.gravatar.com\/avatar\/285cc9389c66aa46da1e26a474b1e90e9efaf3fa21f1b928cbd63ce5f0e89c63?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/285cc9389c66aa46da1e26a474b1e90e9efaf3fa21f1b928cbd63ce5f0e89c63?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/285cc9389c66aa46da1e26a474b1e90e9efaf3fa21f1b928cbd63ce5f0e89c63?s=96&d=mm&r=g","caption":"kerendanino"},"url":"https:\/\/epicmarketing.co.il\/notebook\/author\/kerendanino\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/posts\/1974","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/comments?post=1974"}],"version-history":[{"count":2,"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/posts\/1974\/revisions"}],"predecessor-version":[{"id":1979,"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/posts\/1974\/revisions\/1979"}],"wp:attachment":[{"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/media?parent=1974"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/categories?post=1974"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/epicmarketing.co.il\/notebook\/wp-json\/wp\/v2\/tags?post=1974"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}