Forum guidelines
  • Home
  • »
  • General
  • »
  • Using SQL stored procedures in C# WinForm appllication
AuthorQuestion
Using SQL stored procedures in C# WinForm appllication
Posted on: 17 Dec 2012
Hey

I'm working on an application that generates certain reports based on data from my clients database. In this database, there are stored procedures whose purpose is to get the necessary data from the database (the queries are very complex and it would be very unpractical to use them as SQLCommands in C#). The problem I'm having is that I can't trigger the stored procedure from my application.
When I try to trigger it directly from code:

sp_AP.CommandText = "EXEC sp_AP"; // I've tried both with and without EXEC command
sp_AP.Parameters.Add(new SqlParameter("@parm1",parm1));
sp_AP.Connection = connection;
SDA.Fill(dtMyDataTable); //SDA is a SqlDataAdapter defined globally


When I execute this code I get the exception "No mapping exists from object type System.Data.DataRow[] to a known managed provider native type.".

When I try to create the new TableAdapter visually in my DataSet, I get the "Invalid object name '#table1'". This part is particularly confusing because when I execute the stored procedure directly on the database it works perfectly.

I've never used stored procedures in my applications before, so please follow any answer with code samples.
Thank you.

AuthorReply
Re: Using SQL stored procedures in C# WinForm appllication
Posted on: 17 Dec 2012  
using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; namespace Stored_procedure_to_insert { public partial class _Default : System.Web.UI.Page { string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlCommand com; SqlDataAdapter sqlda; DataSet ds; protected void btn_insert_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); com = new SqlCommand("insert1", con); com.CommandType = CommandType.StoredProcedure; com.Parameters.AddWithValue("@sid", txtid.Text); com.Parameters.AddWithValue("@sname", txtname.Text); com.Parameters.AddWithValue("@smarks", int.Parse(txtmarks.Text)); com.Parameters.AddWithValue("@saddress", txtaddress.Text); com.Parameters.AddWithValue("@year", txtyear.Text); com.ExecuteNonQuery(); con.Close(); lblmsg.Text = "Data entered successfully!!!"; } } }
If this post helps you mark it as answer
Thanks

Re: Using SQL stored procedures in C# WinForm appllication
Posted on: 17 Dec 2012  
You should instead make that table a Global Temporary Table using ##, retry and it should work.
Thanks

Vidya Vrat Agarwal www.MyPassionFor.net http://www.facebook.com/Mypassionfor.net
Re: Using SQL stored procedures in C# WinForm appllication
Posted on: 17 Dec 2012  
Mr. Agarwal, the stored procedures was created by my clients database administrator and I don't have the permission to alter the stored procedures (or the structure of the database for that matter)

Re: Using SQL stored procedures in C# WinForm appllication
Posted on: 17 Dec 2012  
I managed to solve this. The problem wasn't in the stored procedure, but in the parameters I passed. One of the parameters was a value from another DataTable, and I forgot to pass the value (I passed the DataRow). Thanks for your help anyway.


Re: Using SQL stored procedures in C# WinForm appllication
Posted on: 17 Dec 2012  
Sor first of all you are trying to access the temporary table from your client's DB, correct?

See this URL,
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/385e6778-a11d-4f90-a378-02e658776bd9/
and if the problem is similar like yours then you may need to do a small prototype at your end(trying # and ##) and if that works, you build a case for your client DBA to change the table names.

Many times it happen that we suggest changes to client schema in order to achieve business functioanlity, as at their design time they can't anticipate all the technical glitches.



Vidya Vrat Agarwal www.MyPassionFor.net http://www.facebook.com/Mypassionfor.net

SPONSORED BY

Custom Software Development
MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.