sqlConnect

Stefan Groenen 21 Reputation points
2021-03-04T13:18:35.163+00:00

Hello all. I'm new in C# and have use always Visual Basic so much different isn't it. I'm try to make an desktop app that connect to my SQL server. I have a good book that guide me. SO I must put using System.Data.sqlClient. I can use sqqlConnection. This works in .net framework. Now I want to go to .net 5.0 and use that same but now het don't recognise my sqlconnection anymore. So if someone can tell me what I must ad as using so I can use sqlconnection again with his function I would be happy. Or best way I stay in .net framework

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,278 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,605 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.4K Reputation points
    2021-03-04T14:51:05.543+00:00

    In order to use SQL client classes, try this method: right-click the project in Solution Explorer, select “Manage NuGet Packages…”, “Browse” tab, type “System.Data.SqlClient”, find and install it. Make sure that your code file contains using System.Data.SqlClient.


3 additional answers

Sort by: Most helpful
  1. Stefan Groenen 21 Reputation points
    2021-03-04T15:17:59.723+00:00

    Whell it is installed because if I make on same system in visual studio a desktop program with .net framework 4.7 I can use it when I upt in my file cose using System.Data.SqlClient. So it is onmy computer but on .net 5 it is not working that is the strange thing.


  2. Karen Payne MVP 35,381 Reputation points
    2021-03-04T15:31:56.577+00:00

    Viorel-1 has provided you with what to do to get those classes working via this NuGet package. Here is a code sample that uses new C# features along with a pre-defined time-out if a connection fails.

    using System;  
    using System.Data;  
      
    namespace WorkingWithSqlServer.Classes  
    {  
        public class DataTableResults  
        {  
            /// <summary>  
            /// Holds data read from a database.  
            /// </summary>  
            public DataTable DataTable { get; set; }  
            /// <summary>  
            /// Set when there is a connection to the server failure  
            /// </summary>  
            public bool ConnectionFailed { get; set; }  
            /// <summary>  
            /// General exception message  
            /// </summary>  
            public string ExceptionMessage { get; set; }  
            /// <summary>  
            /// General exception if not connection failure  
            /// </summary>  
            public Exception GeneralException { get; set; }  
            /// <summary>  
            /// Is there an exception thrown  
            /// </summary>  
            public bool HasException => ConnectionFailed || GeneralException != null;  
        }  
    }  
    

    Data operations

    using System;  
    using System.Data;  
    using System.Data.SqlClient;  
    using System.Threading;  
    using System.Threading.Tasks;  
      
    namespace WorkingWithSqlServer.Classes  
    {  
        public class DataOperations  
        {  
            private static string _connectionString =  
                "Data Source=.\\sqlexpress;" +   
                "Initial Catalog=NorthWind2020;" +   
                "Integrated Security=True";  
      
      
            public static async Task<DataTableResults> ReadProductsTask(CancellationToken ct)  
            {  
                var result = new DataTableResults() { DataTable = new DataTable() };  
      
                return await Task.Run(async () =>  
                {  
                    await using var cn = new SqlConnection(_connectionString);  
                    await using var cmd = new SqlCommand() { Connection = cn };  
                    cmd.CommandText = SelectStatement();  
      
                    try  
                    {  
                        await cn.OpenAsync(ct);  
                    }  
                    catch (TaskCanceledException tce)  
                    {  
                        result.ConnectionFailed = true;  
                        result.ExceptionMessage = "Connection Failed";  
                        return result;  
                    }  
                    catch (Exception ex)  
                    {  
                        result.GeneralException = ex;  
                        return result;  
                    }  
      
                    result.DataTable.Load(await cmd.ExecuteReaderAsync(ct));  
      
                    return result;  
      
                });  
      
            }  
      
            private static string SelectStatement()  
            {  
                return "SELECT P.ProductID, P.ProductName, P.SupplierID, S.CompanyName, P.CategoryID, " +  
                       "C.CategoryName, P.QuantityPerUnit, P.UnitPrice, P.UnitsInStock, P.UnitsOnOrder, " +  
                       "P.ReorderLevel, P.Discontinued, P.DiscontinuedDate " +  
                       "FROM  Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID " +  
                       "INNER JOIN Suppliers AS S ON P.SupplierID = S.SupplierID";  
            }  
      
        }  
      
    }  
    

    Form code

    using System;  
    using System.Threading;  
    using System.Threading.Tasks;  
    using System.Windows.Forms;  
    using WorkingWithSqlServer.Classes;  
      
    namespace WorkingWithSqlServer  
    {  
        public partial class Form1 : Form  
        {  
            private const int TimeOutSeconds = 5;  
            private CancellationTokenSource _cancellationTokenSource = new(TimeSpan.FromSeconds(TimeOutSeconds));  
            public Form1()  
            {  
                InitializeComponent();  
                Shown += OnShown;  
            }  
      
            private async void OnShown(object? sender, EventArgs e)  
            {  
                await LoadData(true);  
            }  
      
            private async Task LoadData(bool firstTime = false)  
            {  
                if (!firstTime)  
                {  
                    if (_cancellationTokenSource.IsCancellationRequested)  
                    {  
                        _cancellationTokenSource.Dispose();  
                        _cancellationTokenSource = new CancellationTokenSource(TimeSpan.FromSeconds(TimeOutSeconds));  
                    }  
                }  
      
                var dataResults = await DataOperations.ReadProductsTask(_cancellationTokenSource.Token);  
      
                if (dataResults.HasException)  
                {  
                    MessageBox.Show(dataResults.ConnectionFailed ?   
                        @"Connection failed" :   
                        dataResults.GeneralException.Message);  
                }  
                else  
                {  
                    dataGridView1.DataSource = dataResults.DataTable;  
                }  
            }  
        }  
    }  
    

    Project file

    Double click on the project name in solution explorer, make sure after installing the NuGet package this is what your project file looks like.

    <Project Sdk="Microsoft.NET.Sdk">  
      
    	<PropertyGroup>  
    		<OutputType>WinExe</OutputType>  
    		<LangVersion>9.0</LangVersion>  
    		<TargetFramework>net5.0-windows</TargetFramework>  
    		<UseWindowsForms>true</UseWindowsForms>  
    	</PropertyGroup>  
      
    	<ItemGroup>  
    	  <PackageReference Include="System.Data.SqlClient" Version="4.8.2" />  
    	</ItemGroup>  
      
    </Project>  
    
    0 comments No comments

  3. Duane Arnold 3,216 Reputation points
    2021-03-04T17:31:33.47+00:00

    I don't think you can except that a .NET Framework solution prior to 5 is going to work if do not look at the migration of the solution to .Net 5

    https://learn.microsoft.com/en-us/dotnet/architecture/modernize-desktop/example-migration

    0 comments No comments