Using SQL stored procedures in C# WinForm appllication

Home
»
General
»
Using SQL stored procedures in C# WinForm appllication

AuthorQuestion

Neven Draskovic

closed

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.

Report a Spam

Our Recommended Solutions

[ + ]

AuthorReply

Satyapriya Nayak

0
0

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

Vidya Vrat Agarwal

0
0

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

Neven Draskovic

0
0

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)

Neven Draskovic

0
0

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.

Vidya Vrat Agarwal

0
0

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

SEARCH ANSWERS

Nevron Diagram

Our recommended forum posts

HOT QUESTIONS

SPONSORED BY

Get the industry leading .NET Charting component

.NET Charting component with full set of charting and gauge types.