DataAdapter.Fill(DataTable) Not Working

Question

Never any exception thrown. Locally: - Doesn't repro at a breakpoint even with same inputs. - Doesn't repro in SSMS - the SP always returns all the rows.

 

After my last deployment, it worked fine on 3 out of 4 calls. The  last one repeatedly fails (on a background thread looping to re-execute every minute)

I suspect it is related to Comp Gov change from:  

 System.Data.SqlClient

to this:  

 Microsoft.Data.SqlClient.

I'd like to try returning to System.Data.SqlClient. I think most versions are deprecated as unsecure, but maybe 4.86 is okay? But when I install 4.86, it complains that it needs this DLL:   

System.Data.SqlClient.SNI

and I don't know where to get it, nor even how to run it, as it seems to be a non-Nuget DLL.

	public static oResult fillDataTable(string query, List<SqlParameter> parameters, CommandType commandType, object[] values, int timeoutInSeconds = 60){
		oResult result = new oResult { dt = new DataTable() };
		Microsoft.Data.SqlClient.SqlDataAdapter da = new Microsoft.Data.SqlClient.SqlDataAdapter(query, cnString);
		da.SelectCommand.Connection.AccessToken = CommonStatics.DBAccessToken;
		da.SelectCommand.Parameters.Clear();
		da.SelectCommand.CommandTimeout = timeoutInSeconds;
		da.SelectCommand.CommandText = query.Trim();
		da.SelectCommand.CommandType = commandType;
		if (parameters != null) da.SelectCommand.Parameters.AddRange(parameters.ToArray());
		int rowCount = 0;
		DateTime startTime = DateTime.Now;
		DataTable dt = result.dt;
		try
		{
			dt.BeginLoadData();
			da.Fill(dt);
			rowCount = dt.Rows.Count;
			dt.EndLoadData();
		}
		catch (Exception ex)
		{
			throw new Exception($"An error occured executing the query: '{query}', with param values: '{valuesAsString(values)}'. THE ERROR IS: {ex}.");
		}
		return result;
	}

Answer 1

Hi @JAL,

What project are you working on?

Does the problem occur locally, or only after deployment, or both?

But when I install 4.86, it complains that it needs this DLL: System.Data.SqlClient.SNI

System.Data.SqlClient has a dependency runtime.native.System.Data.SqlClient.sni. But it is not needed in .NET Framework.

I think you'd be better off using blocks.

https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/statements/using?redirectedfrom=MSDN

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread