asked on

Call Stored Procedure from C#

I want to make a Stored Procedure but I am not sure how to call it from C#. I am new at a company and I am trying to understand their way of doing things and there isn't anyone who can answer my questions. It doesn't look like they use Stored Procedures so I can't copy the process on calling it. It looks like they do everything in one query string but I need to change it.

Here is the line of code to send the SQL string to the DB:
ArrayList dl = new ArrayList();
string sSQL = "SELECT * FROM Contacts";

Cm.ex(Cm.pldt, sSQL, null, null, null, null, dl, null, null, null, true);

Here is the definition for Cm:

namespace BaseClassLibrary
{
    public class Cm
    {
        public static string cp;
        public static string plc;
        public static string pld;
        public static string pldt;
        public static string plw;

        public Cm();

        public static string ex(string cmm, string fc, string cmt, Hashtable ch, string ot, Ob rob, ArrayList al, Ts gts, Dl gdl, Label rl, bool q);
    }
}

Open in new window

I hope someone can help and if I need to provide more information please let me know.

Thank you

ste5an

As you're new, don't try rebuilt and reengineer their software without understanding why it was built that way.
When " isn't anyone who can answer my questions" means that you're sitting alone in front of that code, then talk with your boss first. And make clear, that the code is not easy to read, cause it seems to be undocumented. And that it doesn't follows common best practices. For example using abbreviations instead of explain words for variables, members and methods.

While stored procedures are great from SQL Server perspective, I would stuff this in the backlog. Cause there seems to be much more to do in the ASP.NET code.

For your concrete problem: What does Ex() return? How is it further processed? What kind of ASP.NET architecture is used (plain, MVC. etc.)?

ASKER CERTIFIED SOLUTION

Ioannis Paraskevopoulos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jacques Bourgeois (James Burger)

These variables might mean something to you, but they mean nothing for any other programmer. Same thing for your method. Even more so for the class name. What the heck is a Cm?

Suppose that I ask you a question and ask you to help me with the following:

public Xv tom (string ab, string cd, string ef)

Can you help me?

Give proper names to your variables. Such sort names makes code very hard to understand. Think about the guy who will come after you and will have to work on your code. He will be completely loss with such variable names.

-----

Why do you use static variables everywhere? Do you know what static means? When you do not know what a word means in the language that you use, look for its meaning. Otherwise, do not use it.

-----

There are many different ways to call stored procedures, depending on what you intend to do.

Here is one where Visual Studio build a lot of the stuff for you: https://msdn.microsoft.com/en-us/library/d7125bke.aspx

If all you need is prepare a list or an array (which you seem to be looking for), then the following might be better: https://msdn.microsoft.com/en-us/library/haa3afyz%28v=vs.80%29.aspx

Jacque Scott

ASKER

The previous programmer at this company did not document well or give well meaning names. I have never created the connection string. It was always there before I started to work on a project. This is why I am having so many problems.

Jacque Scott

ASKER

Thank you. This is exactly what I needed and it worked. I always thought you have to call a Stored Procedure a special way. It turns out you don't.

Ioannis Paraskevopoulos

Hi,

Good to know this worked for you.

Please keep in mind that the fact that it worked, doesn't also mean it is right.

Jacques' comment could indicate one or two of the right ways. Another could be using Entity Framework, and its mechanism of calling a stored procedure.

By what you have given us, it is obvious, that the way you use the code, is that you give a string to a method, that will internally execute the string. We have no idea of what that method really does, or how it tries to execute that string. My comment was that if you would feed that method with a correct string that would call an sp, it would probably do it (and thankfully, it did).

If I was in your place, i would try to figure out how that method does its work, and make sure it complies with best practices. Again, Jacques' comment, has some useful links. Those links also provide the right way of passing parameters to your sql string. I didn't see any use of parameters on your case, so i did not mention them.

There are many ways to do a thing, such as calling an SP, and in time, Microsoft team has introduced several of them. I really prefer going through Entity Framewor, but you need to be somewhat familiar with it, and of course if the project you are working on is not using it, it may be quite heavy to change technology.

Giannis