Call stored procedure in C#, LINQ from MySQL database

tombradshaw · Post by **tombradshaw** » Tue 19 May 2009 12:29

Hi,

I have a basic test db in MySQL (see below), and I have created a datacontext but I keep getting errors when I try and call the stored procedure like
DataContext1 dc = new DataContext1();
Test1Result res = dc.Test1().FirstOrDefault();

but I get the error:
System.NullReferenceException was unhandled
Message="Object reference not set to an instance of an object."

Am I calling the procedure correctly? what if the procedure returns several rows? Is it possible to call stored procedures?

Thanks
Tom

CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`thename` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

DELIMITER $$

CREATE PROCEDURE `test1`()
BEGIN
select thename from table1;
END */$$
DELIMITER ;

I have created a datacontext which links to the

AndreyR · Post by **AndreyR** » Wed 20 May 2009 11:23

This problem is already fixed in the current 5.20.29 build of dotConnect for MySQL. I succeeded in running the similar example.

tombradshaw · Post by **tombradshaw** » Wed 20 May 2009 14:35

ok? I only downloaded dotConnect for MySQL on tuesday, so I would be using that version. I will try it again.

Thanks
Tom

tombradshaw · Post by **tombradshaw** » Wed 20 May 2009 15:20

Hi,
I have tried this again on a different pc, with new latest install of MySQL and the latest download of the dotConnect for MySQL (downloaded today, dotConnect for MySQL 5.20 Professional Trial, 5.20.29)

I created the table and stored proc as above and run the C# code (visual studio 2008):

dvDataContext1 dc2 = new dvDataContext1();
List res2 = dc2.Test1().ToList();
foreach (Test1Result t in res2)
MessageBox.Show(t.Thename);

The crash occurs on the "IExecuteResult result = " line of the auto code:

[Function(Name=@"test.test1")]
public System.Data.Linq.ISingleResult Test1()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((System.Data.Linq.ISingleResult)(result.ReturnValue));
}

The full exception is:
System.NullReferenceException was unhandled
Message="Object reference not set to an instance of an object."
Source="Devart.Data.Linq"
StackTrace:
at Devart.Data.Linq.Provider.Query.bk.b(ar A_0, MethodCallExpression A_1, MetaFunction A_2)
at Devart.Data.Linq.Provider.Query.bk.a(MethodCallExpression A_0)
at Devart.Data.Linq.Provider.Query.bk.b(MethodCallExpression A_0)
at Devart.Data.Linq.Provider.Query.bk.j(Expression A_0)
at Devart.Data.Linq.Provider.Query.bk.i(Expression A_0)
at Devart.Data.Linq.Provider.DataProvider.a(Expression A_0)
at Devart.Data.Linq.Provider.DataProvider.h(Expression A_0)
at Devart.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
at WindowsFormsApplication4.dvDataContext1.Test1() in C:\Users\Tom\Desktop\Lysanda\Development\Prototypes\WindowsFormsApplication4\WindowsFormsApplication4\dvDataContext1.Designer.cs:line 76
at WindowsFormsApplication4.Form1.button1_Click(Object sender, EventArgs e) in C:\Users\Tom\Desktop\Lysanda\Development\Prototypes\WindowsFormsApplication4\WindowsFormsApplication4\Form1.cs:line 22
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at WindowsFormsApplication4.Program.Main() in C:\Users\Tom\Desktop\Lysanda\Development\Prototypes\WindowsFormsApplication4\WindowsFormsApplication4\Program.cs:line 18
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

Not sure what is wrong?
I can send you the project and sql to create the database.

Thanks
Tom

AndreyR · Post by **AndreyR** » Thu 21 May 2009 11:47

Thank you for the additional information, we have found the problem.
As a temporary workaround, try to replace the Test1Result data type with the Table1 data type in the procedure's definition
in the Designer.cs file. This should help.

tombradshaw · Post by **tombradshaw** » Fri 22 May 2009 13:41

Hi,
tried the workaround, it worked but only if the stored procedure returned a table1 i.e.
SELECT * FROM table1;

but would it work for other queries? which you cant use a table as a result? e.g.
SELECT thename, CONCAT("mr ", thename) FROM table1

When do you think you are going to release a fixed version?

Thanks
Tom

AndreyR · Post by **AndreyR** » Mon 25 May 2009 13:07

I will let you know as soon as the problem is fixed.

tombradshaw · Post by **tombradshaw** » Tue 09 Jun 2009 10:49

Hi,
How is the fix going?
I have managed to work arround the stored procedure results by using a table which is created to have the same structure as the stored proc result.
This does have one issue, it seems that repeated calls to a stored procedure always return the first calls result, (within the same datacontext).

Thanks
Tom

Shalex · Post by **Shalex** » Thu 11 Jun 2009 09:55

We are working on this issue.

it seems that repeated calls to a stored procedure always return the first calls result, (within the same datacontext).

Could you please post here the code that illustrates the problem?