Archive for December, 2008

SQL Server Stored Procedures – Returning Multiple Tables to ADO.NET

December 29, 2008

how to use Multiple ResultSets with the ADO.NET SqlDataReader class. In John’s article he illustrates how you can execute multiple SQL select statements in batch and access all of the results of each SQL select statement with SqlDataReader.NextResult(). The MSDN library documentation for SqlDataReader.NextResult() indicates that it “advances the data reader to the next result, when reading the results of batch Transact-SQL statements.” You can also utilize SqlDataReader.NextResult() with SQL Server stored procedures.

In addition, you can access multiple ResultSets with SqlDataAdapter.Fill(), which is what this blog focuses on.

If a single stored procedure issues several SELECT statements, they are all returned to the caller by default. If we convert John’s example of running 4 queries in batch against the Northwind database to a stored procedure, we now have:

/* Test stored procedure. Returns 4 ResultSets */
CREATE PROCEDURE spSomeStoredProcedure AS
BEGIN

SELECT CategoryName FROM Categories ORDER BY CategoryName
SELECT Top 10 CompanyName FROM Customers ORDER BY CompanyName
SELECT LastName FROM Employees ORDER BY LastName

SELECT Top 10 ProductName FROM Products ORDER BY ProductName

END

GO

Using SqlDataAdapter.Fill() to access all of your returned tables in ADO.NET is really easy:

SqlConnection conn = new SqlConnection(connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(“exec spSomeStoredProc”, conn);
adapter.Fill(dataset);

// dataset.Tables[0] – refers to resultset obtained from first SQL query
// in stored procedure. dataset.Tables[1] – refers to resultset obtained

// from second SQL query. Etc.

The ordinal position of each DataTable in dataset.Tables matches the order that the queries are executed within the stored procedure. This technique can and should be used to minimize network roundtrips between your application server and database server (in addition to minimizing all of the marshalling and additional overhead required per remote database query execution).

Advertisements

Differrence between Convert.To…() and .To…()

December 4, 2008

Mostly problems comes while converting string data.
Always use Convert.ToString(strString) in place of strString.ToString() if your strString may contain NULL.
Because when you use strString.ToString(), it gives error if your strString contains NULL.

Adding “Select One” inside dropdown list

December 4, 2008

Q. How to add “Select One” item inside dropdown list while populating dropdownlist from database?
Q. First item selection problem – no event fire on select index changed but it works for second item.

Answer:
Below is the code to add “Select One” option as first item inside dropdown list.
Here BindDropDownList1() is the function to bind dropdown list from database.
private void BindDropDownList1()
{
string strConn = “server=(local);uid=UID;pwd=PWD;database=Northwind”;
string MySQL = “Select LastName from Employees”;
SQLConnection MyConn =new SQLConnection(strConn);
SQLDataReader objDR ;
SQLCommand Cmd =new SQLCommand(MySQL, MyConn);
MyConn.Open();
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection);
DropDownList.DataSource = objDR;
DropDownList.DataBind();
DropDownList.Items.Insert(“0”,new ListItem(“Select One”,”0″));
}

Now your dropdown list looks like:
dropdownlistboxwithselectoneoption

[Note: the last line DropDownList.Items.Insert(“0”,new ListItem(“Select One”,”0″)); add “Select One” item in your dropdown list and its index is “0”.
Now when the Page Load and dropdown list fill with all the data from database as well as with “Select One” item and “Select One” is selected as your first item.
Now the event is also fire on select index changed for the first item that comes from your database.
]

DropDownList losing data on page submission or on page postback

December 4, 2008

Q.”Why doesn’t my DropDownList keep it’s Selection?”
Q.”Why is the selectedindex for my (ASP.Net control) always turning up a -1?”

Answer:

The problem occurs due to not checking of Page Post Back on Page Load

private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
BindDropDownList1();
}
}

[Note: you may be missing if(!IsPostBack) line on page load.
!Page.IsPostBack read as Not Page is Page Back prevents on each subsequent loading of the page, the control doesn’t go through the redundant action of re-loading the control. It loads it only once, at the initial page load and the selection which was made by the end user is maintained throughout the post back.
BindDropDownList1() is the method created by You to fill your DropDownList box.]
Below is the BindDopDownList1 method that you have written in your page or in any other class.

private void BindDropDownList1()
{
string strConn = “server=(local);uid=UID;pwd=PWD;database=Northwind”;
string MySQL = “Select LastName from Employees”;
SQLConnection MyConn =new SQLConnection(strConn);
SQLDataReader objDR ;
SQLCommand Cmd =new SQLCommand(MySQL, MyConn);
MyConn.Open();
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection);
DropDownList.DataSource = objDR;
DropDownList.DataBind();
}

Differences between various .net framework

December 3, 2008

This picture below shows the various versions of the .NET framework and the similarities and differences between them:
.NET Framework

Why I have created this Blog????????

December 2, 2008

Hi,

As a Software Engineer I found there is several blogs and websites that helps us to code better way but to find suitable article and appropriate code and example is a very tough task on net.
We found many post or articles that doesn’t come to the point about our problem. Mostly make us more confusion about our questions and answers.I too fetch this type of problems.
So visiting several websites and blogs, I decided to start own blog that definitely solve your problem. Its not promise but as friend You can trust me.
Off course the blog that doesn’t contain all new articles but I am going to put those article that really mean. For this I am going to filter number of sites and blogs and by only testing , I will put those article in this blog.
Sounds good na!!!!!!!!!!!!
So why you are waiting……. Keep posting me about your issue and anything new you have done in your field and hope that will be really best to help other friends in their professional life.