How to get data from stored procedure in C#.net desktop application?

Question

2.33/5 (3 votes)

See more:

.NET4.5

I want to get data from required stored procedure. sp will return me a table having 4 columns.
how to get data i tried following code

C#

var connectionString = ConfigurationManager.ConnectionStrings["cse"].ConnectionString;
           using (SqlConnection conn = new SqlConnection(connectionString))
           using (SqlCommand cmd = conn.CreateCommand())
           {
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.CommandText = "TimeSummary_ForInvoice";

               cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = "2014-05-31"; // dtFrom.Value;
               cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = "2014-06-01";// dtTo.Value;
               cmd.Parameters.Add("@Company", SqlDbType.Int).Value = 2;
               cmd.Parameters.Add("@InvoiceStartNo",SqlDbType.Int).Value=1;
               conn.Open();
               var result = cmd.ExecuteReader();
           }

help will be appreciable

Posted 7-Aug-14 0:27am

shivani 2013

Add a Solution

3 solutions

Solution 1

i hope this will help you.

Storing and Retrieving Images from SQL Server Using Strored Procedures and C#.net[^]

Posted 7-Aug-14 0:48am

Thilina Chandima

Solution 2

It should work fine, it will return you a Reader, then you can iterate through that reader for your data.
Or you can fill a Dataset with the results provided by the stored procedure.

Posted 7-Aug-14 0:49am

Abdul Hafeez Munawar

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)

Raul Iloc · Accepted Answer · 2014-08-07T00:50:00

Solution 3

1.Your code is using to many var, and using statement.

2.After you execute the reader you have to use a while block to read all data from the reader for each row.

3.Better is to use try-catch-finally block like in the next example:

C#

List<ErpPriceColumn > resultList = new List<ErpPriceColumn>();
            IDataReader reader = null;
            SqlConnection dbConnection = null;
            //
            try
            {
                dbConnection = new  SqlConnection(connectionString);   
                IDbCommand dbCommand = new SqlCommand();
                dbCommand.Connection = dbConnection;
                dbCommand.CommandType = CommandType.StoredProcedure;
                dbCommand.CommandText = "TimeSummary_ForInvoice";
                //
                dbConnection.Open();
                //
                reader = dbCommand.ExecuteReader();
                //
                while (reader.Read())
                {
                    ErpPriceColumn erpEntity = new ErpPriceColumn();
                    erpEntity.ID = (int)reader["ID"];
                    erpEntity.Comment = (string)reader["Comment"];
                    erpEntity.BeforeTaxPrice = (0 == (byte)reader["BeforeTaxPrice"]);
                    erpEntity.UpdateDate = (DateTime)reader["UpdateDate"];
                    //
                    resultList.Add(erpEntity);
                }
            }
            catch (SqlException exception)
            {
                throw new MyExceptionClass(exception.Message, exception); //This is good to have!
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                //
                dbConnection.Close();
            }
            //
            return resultList.ToArray();

Posted 7-Aug-14 0:50am

Raul Iloc

Updated 7-Aug-14 0:52am

v3

Comments

shivani 2013 7-Aug-14 7:33am

I tried ur way but facing issue while(reader.read()) -- there is no data present and is skipping all code with in it..through sp i am returning table with multiple records.

Raul Iloc 7-Aug-14 7:40am

This could be generated by two facts:
1.Your SP do not generate any results for the current parameters, so you should test your SP in the SQL by using the same paramteres;
OR
2.You made an error in your code.

shivani 2013 7-Aug-14 7:48am

sp is returning result with two records..

updated code is

public List<invoice> GetData()
{
var connectionString = ConfigurationManager.ConnectionStrings["csTcube"].ConnectionString;
List<invoice> resultList = new List<invoice>();
SqlConnection dbConnection = null;
SqlDataReader reader = null;

try
{
dbConnection = new SqlConnection(connectionString);
// IDbCommand dbCommand = new SqlCommand();

SqlCommand dbCommand = new SqlCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "TimeSummary_ForInvoice";

dbCommand.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = "2014-05-31"; // dtFrom.Value;
dbCommand.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = "2014-06-01";// dtTo.Value;
dbCommand.Parameters.Add("@Company", SqlDbType.Int).Value = 2;
dbCommand.Parameters.Add("@InvoiceStartNo",SqlDbType.Int).Value=1;

SqlDataAdapter adp = new SqlDataAdapter(dbCommand);
DataTable dt = new DataTable();

dbConnection.Open();
//
reader = dbCommand.ExecuteReader();

//adp.Fill(dt);
//if (dt.Rows.Count > 0)

//
while (reader.Read())
{
Invoice invoiceEntity = new Invoice();
invoiceEntity.Rowno=(int)reader["Rowno"];
invoiceEntity.ProjectName = (string)reader["ProjectName"];
invoiceEntity.EmployeeRoleTypeName = (string)reader["EmployeeRoleTypeName"];
invoiceEntity.WorkHours = (decimal)reader["WorkHours"];
invoiceEntity.InvoiceNo = (string)reader["@Invoice"];
invoiceEntity.CurrentDate=(DateTime)reader["CurrentDate"];

resultList.Add(invoiceEntity);
}
}
catch (SqlException exception)
{
// throw new MyExceptionClass(exception.Message, exception); //This is good to have!
}
finally
{
if (reader != null)
reader.Close();

dbConnection.Close();
}
//
return resultList.ToList();

}

Raul Iloc 7-Aug-14 7:59am

1.You could comment the code with SqlDataAdapter.
2.It seems that you have a problem with the DateTime parameters values that must be of type DateTime and not string.

shivani 2013 7-Aug-14 7:59am

hey i achieved it trogh datatable..thanks for structure and will query you again ...if face issue...thanks

Raul Iloc 7-Aug-14 8:00am

Welcome, I am glad that I could help you!