Subscribe to Subscribers
Talk to Webmaster Tony John

Online Members

baskar
More...

Resources » Code Snippets » ADO.NET

Access any Stored Procedure with or without parameter


Posted Date:     Category: ADO.NET    
Author: Member Level: Silver    Points: 10



 


Common Stored Procedure for insert,update delete

Hi! here i am writting a single method by which you can access any stored procedure with and without parameter.

SqlCommandBuilder automatically derive the parameters .
and list of parameters are supplied to stored procedure.

here is the code to execute the stored procedure and return the dataset.


class DataAcess
{
SqlConnection con;
//spName:name of stored procedure
//spParams:parameters required for stored procedure.
public DataSet ExecuteDataSet(string spName, object[] spParams)
{
try
{
SqlCommand sqlcom = GetSqlCommand(spName, spParams);
SqlDataAdapter sqlda = new SqlDataAdapter(sqlcom);
DataSet dsetResult = new DataSet();
sqlda.Fill(dsetResult);
return dsetResult;
}
finally
{
CloseConnection();
}
}

private SqlCommand GetSqlCommand(string spName, object[] spParam)
{
SqlCommand com = new SqlCommand(spName, GetConnection());
com.CommandType = CommandType.StoredProcedure;
//SqlCommandBuilder automatically derive the parameters
SqlCommandBuilder.DeriveParameters(com);
com.Parameters.RemoveAt(0);
if (spParam != null || spParam.Length != 0)
{
for (int index = 0; index < spParam.Length; index++)
{
//value will be set
if (spParam[index] != null)
com.Parameters[index].Value = spParam[index];
else
com.Parameters[index].Value = DBNull.Value;
}
}
return com;
}

//read the connection string from web.config(web) /app.config(window)
private SqlConnection GetConnection()
{
con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["MyConn"]);
con.Open();
return con;
}
private void CloseConnection()
{
con.Close();
}
}



//you have to just call the ExecuteDataSet from your business logic.

private void DisplayMachine(string machineName)
{
DataAcess dataAcess = new DataAcess();
//parameters required for procedure,if no parameter then pass Null
object[] spparam = { machineName };
DataSet ds = dataAcess.ExecuteDataSet("Usp_DisplayMachine",spparam);
}



Regards,
Shital Umare





Did you like this resource? Share it with your friends and show your love!


Responses to "Access any Stored Procedure with or without parameter"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Next Resource: Use of SQL stored procedure in ADO.NET code
    Previous Resource: Data Adapters
    Return to Resources
    Post New Resource
    Category: ADO.NET


    Post resources and earn money!
     
    More Resources
    Popular Tags   Tag posting guidelines   Search Tags  
    Stored procedure  .  Access stored procedure with or without parameter  .  ADO.Net  .  Fill dataset  .  Access connection string from config  .  
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
    2005 - 2012 All Rights Reserved.
    .NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
    Articles, tutorials and all other content offered here is for educational purpose only.
    We are not associated with Microsoft or its partners.