Call store procedure in Kinetic Function not working

Kinetic 202X
1

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?

2

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 ?

3

I would convert the stored procedure into a function.

1 Like
4

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
5

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.
6

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

7

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();

}

8

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();
1 Like
9

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();
}
1 Like
10

I never even looked. Is that disposable?