How to use stored procedure

[Zohaib]

I have a piece of code
string sel="select * from ableco";
SqlCommand command = new SqlCommand("storedprocedureName", con);
command.CommandType = CommandType.StoredProcedure;

if I do not want to use connection and use a code like this
DbCommand selectCommand = Token.Instance.Database.GetSqlStringCommand(sel)

My question is how i mentioned the stored procedure in this code.

[mazhar]

Here is the example code

Code: Select all

System.Data.Common.DbCommand dbCommand = Token.Instance.Database.GetStoredProcCommand("NameOfYourStoredProceedure");
        Token.Instance.Database.AddInParameter(dbCommand, "@value1", System.Data.DbType.Int32, 2331);
        
        //For Insert/Update/Delte you can use execute non-query like
        int affectedRows = Token.Instance.Database.ExecuteNonQuery(dbCommand);
        
        //For Query that returns only a single value
        Object returnedValue = Token.Instance.Database.ExecuteScalar(dbCommand);
        
        //For query that returns a tabular structure
        System.Data.DataSet resultSet = Token.Instance.Database.ExecuteDataSet(dbCommand);
        
        //For quer where you want a connected read only reqader
        using (System.Data.IDataReader dr = Token.Instance.Database.ExecuteReader(dbCommand))
        {
            while (dr.Read())
            { 
                //You can access every row of your returned data here
                int name = AlwaysConvert.ToString(dr["Name"]);
            }
            dr.Close();
        }

[Zohaib]

string size = "select p1 from ac_CloudSettings where Description='Size'";
CommerceBuilder.Data.Database database = Token.Instance.Database;
using (System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(size))
{
System.Data.DataSet dsDiscountedProducts = (System.Data.DataSet)Token.Instance.Database.ExecuteDataSet(selectCommand);
System.Data.DataTable table = dsDiscountedProducts.Tables[0];
CloudSize = Convert.ToInt32(table.Rows[0][0].ToString());
}

I am writting above lines to do fetch dataset from the database.My question is whether is it right way to do things or not?

[mazhar]

Its OK but about a complete information about AbleCommerce DAL and best practices you need to read following topics
http://wiki.ablecommerce.com/index.php/ ... cess_Layer
http://wiki.ablecommerce.com/index.php/Custom_Queries

There is also some code templates available in forums to generate data access code. You can get these templates from this thread
viewtopic.php?f=47&t=9530