Regarding minimizing SQL server calls

So I have this page that's got like MANY methods in it..some method is fetching user email..one is binding repeater, another is changing password and all. Now in each and every method some stored procedure is called and changes are made in DB.

So my page's Page_load event looks like this :-

    protected void Page_Load(object sender, EventArgs e)
    {

                BindCon();
                getemail();
                getadd();
                BindRepeater();

                getpic();


}

Now this way SQL server is being called like over and over which will affect performance. I thought may be I can create ONE stored Procedure that will execute ALL the stored Procedures being used in the methods above for once and not call SQL server over and over like its doing above. Now how do I create that Stored P and use it in my code?? need ideas...thnx

If you are getting all these things individually from one row in a table (account, email, age, say), you should definitely get them all at one time in a single row instead of a call for each column.

In addition, if you are getting things from separate tables which can be combined with joins to make a single row (account, number of message in inbox, account balance), you can do that in the SP and get a single row.

If the things are really separate entities (one row for user account, multiple rows for permissions), you can also return multiple result sets from your stored proc and use the ADO.NET feature which allows you to get next result set.

Note that in some cases with longer running SPs, it might be better to call all the SPs in parallel using async ADO.NET, since a single SP will effectively force the operations to run in serial.

In any case where your database is cutting across subsystems, you can be creating maintenance issues, because you may be creating a SP which crosses entity boundaries and will be affected by changes to either subsystem. Where possible have a cross-cutting SP call separate SPs in each entity domain to try to decouple it as much as possible. Even if it needs to be changed due to a future change, this should at least make the dependency clear.

you can run the SQL profiler, analyze what's repeating and reduce those that you don't normally need. Since you're using ASP.Net, you can take advantage of states like Session, Application and Cache to store data depending on the need.