How do I execute a stored procedure from sql server for a generic program?

Question

0.00/5 (No votes)

See more:

Hi, I am new to C#, I will kindly appreciate your help:)

How do I execute a stored procedure from sql server and let it return tables to the program and generates number of csv file required based on the number of data tables return from the Stored procedure?

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.CommandText = "Test";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();

            reader = cmd.ExecuteReader();

            sqlConnection1.Close();
        }
    }
}

The current errors I received:

Error: The name 'CommandType' does not exist in the current context

Do I have to add any codes to the App.config? With the current execution codes, am I able to generate csv files with it and how do I go about generating csv files based on the codes without the need of any user input?

Posted 31-Jul-15 21:59pm

Member 11878313

Updated 1-Aug-15 2:25am

v5

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-31T22:08:00

Solution 1

In order to fix the errors, you need to add

C#

using System.Data.SqlClient;

or you need to specify the namespace for the classes, for example

C#

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection("Your Connection String");

Also ensure that System.Data assembly is included in your project references.

Posted 31-Jul-15 22:08pm

Wendelius

Updated 31-Jul-15 22:09pm

v2

Comments

CPallini 1-Aug-15 4:15am

5.

Member 11878313 1-Aug-15 4:32am

Thanks for solving most of my errors:) However do you have any idea on an how to generate all csv files and save into a common file directory?

Wendelius 1-Aug-15 6:19am

There are quite a lot of articles concerning writing a CSV but have a look at C# - CSV Import Export[^]. It looks like a really nice utility to learn from.

Wendelius 1-Aug-15 6:26am

Thanks :)

DamithSL · Accepted Answer · 2015-07-31T22:39:00

1. Include using statements for system.Data and System.Data.SqlClient as below

C#

using System.Data;
using System.Data.SqlClient;

2. Fill Data Set using stored procedure

C#

using(SqlConnection conn = new SqlConnection("ConnectionString"))
{
        SqlCommand sqlComm = new SqlCommand("StoredProcedureName", conn);
        sqlComm.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter da = new SqlDataAdapter(sqlComm);
        DataSet ds = new DataSet();
        da.Fill(ds);
 }

3. Now you have data set called with multiple tables, export each DataTable to CSV file using Writing a DataTable to a CSV File[^]