如何从C＃.net桌面应用程序中的存储过程中获取数据？ [英] How to get data from stored procedure in C#.net desktop application?

问题描述

我想从所需的存储过程中获取数据。 sp将返回一个有4列的表。

如何获取我试用的数据代码

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

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

推荐答案

1 。您的代码用于许多 var ，而使用语句。



2.执行阅读器后，你必须使用而块来阅读阅读器中的所有数据对于每一行。



3.Better是使用try-catch-finally块，如下例所示：

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:

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();

我希望这对您有所帮助。



使用Strored从SQL Server存储和检索图像程序和C＃.net [ ^ ]

i hope this will help you.

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

它应该可以正常工作，它会返回一个Reader，然后你可以通过读取器遍历你的数据。

或者您可以使用存储过程提供的结果填充数据集。

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.

这篇关于如何从C＃.net桌面应用程序中的存储过程中获取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！