Call store procedure in Kinetic Function not working

I am using function in Kinetic 2022.2 to call a SQL Store Procedure using the System.Data.Sqlclient. I can’t get it to work anymore. It is works fine in Epicor 10.2.700.xx. Is anyone know another way to call Store Procedure using Kinetic Function?

Let me guess, Connection String ?

If that’s what it is I can help.

If not, give us some more information. Have you verified the procedure exists?
How are you calling it, etc ?

I would convert the stored procedure into a function.

1 Like

Remember that starting with 2022.1, the Server code is based on .NET 6 and System.Data.SQLClient has been replaced with Microsoft.Data.SQLClient.

Check your favorite search engine or AI for more information.

3 Likes

I am using a connection string to SQL Client. I am getting error when Check Syntax. I am getting the CS1069 Error;

System.Drawing.Bitmap CS1069 The type name ‘SqlConnection’ could not be found in the namespace ‘System.Data.SqlClient’. This type has been forwarded to assembly ‘System.Data.SqlClient, Version=0.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ Consider adding a reference to that assembly.
System.Drawing.Bitmap CS1069 The type name ‘SqlConnection’ could not be found in the namespace ‘System.Data.SqlClient’. This type has been forwarded to assembly ‘System.Data.SqlClient, Version=0.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ Consider adding a reference to that assembly.
System.Drawing.Bitmap CS1069 The type name ‘SqlCommand’ could not be found in the namespace ‘System.Data.SqlClient’. This type has been forwarded to assembly ‘System.Data.SqlClient, Version=0.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ Consider adding a reference to that assembly.
System.Drawing.Bitmap CS1069 The type name ‘SqlCommand’ could not be found in the namespace ‘System.Data.SqlClient’. This type has been forwarded to assembly ‘System.Data.SqlClient, Version=0.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ Consider adding a reference to that assembly.

Ok that’s different errors than what I was expecting. Look at @Rich answer.

I can’t find the Microsoft.Data.SQLClient as Epicor Assembly in the function, but I found this: Microsoft.SqlServer.ConnectionInfo .

Here is my connection String:
using (sqlCon=new SqlConnection(SqlconString))
{
sqlCon.Open();
SqlCommand sql_cmnd = new SqlCommand(“Name”, sqlCon);
sql_cmnd.CommandType = CommandType.StoredProcedure;
sql_cmnd.ExecuteNonQuery();
sqlCon.Close();

}

I helped @xvhelp privately because I thought he was doing something a bit different.

Turns out he was not. He was calling a stored procedure in another database and just
wanted to patch up his code to call it.

Here is what we ended up with, since we just wanted to keep what he had going,
without moving to a new library.

//using System.Reflection

Assembly System_Data_SqlClient = Assembly.Load("System.Data.SqlClient");

Type typeSqlCommand    = System_Data_SqlClient.GetType("System.Data.SqlClient.SqlCommand");
Type typeSqlConnection = System_Data_SqlClient.GetType("System.Data.SqlClient.SqlConnection");

dynamic conn = Activator.CreateInstance(typeSqlConnection, "Data Source=MYDBServer;Initial Catalog=MYDB; User ID=MYID;Password=MYPASSWORD");

conn.Open();

string cmdText = "my_stored_proc"; //sql commands

dynamic command = Activator.CreateInstance(typeSqlCommand, new object[]{ cmdText, conn });

command.CommandType = System.Data.CommandType.StoredProcedure;

command.ExecuteNonQuery();

conn.Close();

Edit: How did those damn fancy quotes get in there? :dumpster_fire:

1 Like

Thanks for this, helped me out heaps today.

might worth wrapping the Sql connection and command in a using block…?

//using System.Reflection

Assembly System_Data_SqlClient = Assembly.Load("System.Data.SqlClient");

Type typeSqlCommand    = System_Data_SqlClient.GetType("System.Data.SqlClient.SqlCommand");
Type typeSqlConnection = System_Data_SqlClient.GetType("System.Data.SqlClient.SqlConnection");

using (dynamic conn = Activator.CreateInstance(typeSqlConnection, "Data Source=MYDBServer;Initial Catalog=MYDB; User ID=MYID;Password=MYPASSWORD") )
{

  conn.Open();
  
  string cmdText = "my_stored_proc"; //sql commands
  
  using (dynamic command = Activator.CreateInstance(typeSqlCommand, new object[]{ cmdText, conn }) )
  {
    command.CommandType = System.Data.CommandType.StoredProcedure; 
    command.ExecuteNonQuery();
  }
  
  conn.Close();
}
3 Likes

I never even looked. Is that disposable?

Is there any way to make this works using SqlDataReader? I manage to INSERT / UPDATE easily but if I want to read the entire row, the SqlDataReader instance doesn’t have a constructor so I can’t create a dynamic variable for it

The only way to read a value that I found was ExecuteScalar() but it only return the first column. For now, i need to return multiple columns. I know that I can probably put all data separated in a column but I’m trying to use the right way to do this and I’m wondering if it’s possible

Probably, but before I revisit this, please explain exactly what you are doing and why?

The reason I ask, is there may be alternatives that are better, and / or less painful.

Honestly, I’m not sure why I thought it was the best approach lol. I simply create an external datasource and then an external BAQ this morning and everything went smooth and way easier to use

I guess I like to have a solution to everything in coding but I understand the limitation / security problems about direct SQL queries

1 Like

@klincecum @josecgomez Do you have any deeper insight you can share to help us understand why Reflection is all of the sudden necessary here? The “type forwarding” errors make it sound like something is being redirected to another assembly but maybe that is a red herring. Just seems weird that referencing the assembly is not good enough anymore, now we need to load the whole thing in and do reflection? Is this situation related to the “migration” to Microsoft.Data.SQLClient? Or would you call it more of a .NET “bug/quirk” that randomly presented itself in System.Data.SQLClient dll?

Also, @Rich , in v2022.2.10 I do see System.Data.SQLClient in server assemblies but I don’t see Microsoft.Data.SQLClient. Has something with this changed since your comment in March?

Hopefully this line of questioning makes sense; a lot of this is new to me.

It is because of the change to .NET 6+ (NET 8) the DLL’s changed and they are no longer available in the current scope so reflection just allows you to pull the DLL from the GAC and use it.

1 Like

not GAC - just runtime subfolder for specific platform. But the idea is correct.

3 Likes

Thank you, starting to make more sense. So it’s just some DLLs that changed / aren’t available in the current scope, and SQLClient happened to be one of them? Should we be worried about needing to use reflection when referencing other assemblies? Or is @Olga about to tell us that we have nothing to worry about if we’re not doing anything dumb like trying to use System.Data.SqlClient?

It is only for assemblies that have different MS implemetation on differen platforms.
System.Data.SqlClient is one of them.

2 Likes

can you also let me know once stored procedure is being called, then how to access its data?

The sample code listed calls ExecuteNonQuery() which is intended for database calls that do not return any data.

For a query that returns data, which I assume is what you need, there are other options, ExecuteReader() and ExecuteScalar(). If you are looking to return a single value then ExecuteScalar is what you want otherwise ExecuteReader().

MS Docs:
ExecuteNonQuery()
ExecuteScalar()
ExecuteReader()

here is a stackOverFlow link on how you might get the data back from a call to ExecuteReader

Brett