ADO.NET connection to SharePoint List causing memory leak

Question

I am connecting to a SharePoint list using the following code. The problem is that if I run a Command, the console application hangs and doesn't close. I can see that the application is still consuming memory. Without a Command, the debugger exits cleanly.

I am also looking for a way to connect to SharePoint with EntityFramework, or a more modern way that doesn't use the SharePoint API.

The sample uses several methods to connect to the same SharePoint list.

namespace TestADO
{
    using System.Data;
    using System.Data.OleDb;
    using System.Data.SqlClient;

    using ADODB;

    internal class Program
    {
        public static string ConnectionStringBase = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;Pooling=false;DATABASE=https://.../sites/...;LIST={xxxxxxx-ac0a-4cb3-8a32-ecbdxxxxxxxx}"; 
        public static string sql = "select top 10 * from [Call Distributions]";

        static void Main(string[] args)
        {
            ADONetImplementationWithAdapter();
            ADONetImplementation();
        }

        static void ADONetImplementationWithAdapter()
        {
            OleDbDataAdapter oledbAdapter;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringBase))
            {
                DataSet ds = new DataSet();

                conn.Open();

                oledbAdapter = new OleDbDataAdapter(sql, conn);
                oledbAdapter.Fill(ds);
                for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    Console.WriteLine(ds.Tables[0].Rows[i].ItemArray[0] + " -- " + ds.Tables[0].Rows[i].ItemArray[1]);
                }
                oledbAdapter.Dispose();
                conn.Close();
            }
        }

        static void ADONetImplementation()
        {
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringBase))
            {
                conn.Open();

                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine(reader.GetValue(0) + " - " + reader.GetValue(1) + " - " + reader.GetValue(2));
                    }
                    reader.Close();
                    cmd.Dispose();
                }

                conn.Close();
            }
        }

        static void ADOImplementation()
        {
            Connection connection = new Connection();
            connection.CursorLocation = CursorLocationEnum.adUseServer;
            connection.ConnectionString = ConnectionStringBase;
            connection.Open();

            object res;

            var rst = connection.Execute(sql, out res);

            while (!rst.EOF)
            {
                rst.MoveNext();
            }

            rst.Close();
            rst = null;

            //Recordset recordset = new Recordset();

            //recordset.Open(sql, connection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic);

            //recordset.Close();
            //recordset = null;

            connection.Close();
            connection = null;

        }
    }
}

Answer 1

Please try to use the following code to connect to SharePoint:

using Microsoft.SharePoint.Client;
using System;
using System.Linq;
using System.Security;
using System.Linq;
using System.Text;
 
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
string siteCollectionUrl = "https://globalsharepoint2020.sharepoint.com";
string userName = "[email protected]";
string password = "YourSPOPassword";
Program obj = new Program();
try
{
obj.ConnectToSharePointOnline(siteCollectionUrl, userName, password);
}
catch (Exception ex)
{
string msg = ex.Message.ToString();
 
}
 
}
 
public void ConnectToSharePointOnline(string siteCollUrl, string userName, string password)
{
 
//Namespace: It belongs to Microsoft.SharePoint.Client
ClientContext ctx = new ClientContext(siteCollUrl);
 
// Namespace: It belongs to System.Security
SecureString secureString = new SecureString();
password.ToList().ForEach(secureString.AppendChar);
 
// Namespace: It belongs to Microsoft.SharePoint.Client
ctx.Credentials = new SharePointOnlineCredentials(userName, secureString);
 
// Namespace: It belongs to Microsoft.SharePoint.Client
Site mySite = ctx.Site;
 
ctx.Load(mySite);
ctx.ExecuteQuery();
 
Console.WriteLine(mySite.Url.ToString());
}
}
}