Using SQL stored procedures in C# WinForm appllication

Neven Draskovic

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vidya Vrat Agarwal13y ago
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.
0
Neven Draskovic13y ago
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.
0
Neven Draskovic13y ago
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)
0
Vidya Vrat Agarwal13y ago
You should instead make that table a Global Temporary Table using ##, retry and it should work.
Thanks
0
Satyapriya Nayak13y ago
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!!!"; } } }
0