[RESOLVED] [REOPEN] Datareader to datatable

[shakti5385]

HI all
I want to change the datareader to datatable.

I want to make datatable to readonly format.
Thanks

[shakti5385]

sqlDataReader = SqlCommand.ExecuteReader();
                             DataTable dataTable;
                dataTable.Load(sqlDataReader);

[Hack]

Moved from the CodeBank

[shakti5385]

Moved from the CodeBank

Thanks HacK for moving this thread.

[jmcilhinney]

Can I just point out that the code you posted will throw a NullReferenceException. You are calling the Load method of a DataTable you haven't yet created.

[shakti5385]

Sir you are here to guide US,
I am posting then whole function here please check it out!
Thanks

public DataTable ReturnDataTableRead(string queryString)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection = ReturnNewConnection();
            SqlCommand SqlCommand = new SqlCommand(queryString, sqlConnection);
            SqlDataReader sqlDataReader;
            try
            {
                sqlConnection.Open();
                sqlDataReader = SqlCommand.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(sqlDataReader);
                return dataTable;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                
                sqlConnection.Close();
            }
        }

[wey97]

Sir you are here to guide US,
I am posting then whole function here please check it out!
Thanks

public DataTable ReturnDataTableRead(string queryString)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection = ReturnNewConnection();
            SqlCommand SqlCommand = new SqlCommand(queryString, sqlConnection);
            SqlDataReader sqlDataReader;
            try
            {
                sqlConnection.Open();
                sqlDataReader = SqlCommand.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(sqlDataReader);
                return dataTable;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                
                sqlConnection.Close();
            }
        }

Since ReturnNewConnection() returns a SqlConnection object, there is no need to initialize a new SqlConnection object before the function call.

'using' blocks drastically simplify your code and automatically handle object disposal.

public DataTable ReturnDataTableRead(string queryString)
{
    DataTable dataTable = null;
 
    using (SqlConnection sqlConnection = ReturnNewConnection())
    {
        using (SqlCommand SqlCommand = new SqlCommand(queryString, sqlConnection))
        {
            using (SqlDataReader sqlDataReader = SqlCommand.ExecuteReader())
            {
                dataTable = new DataTable();
                dataTable.Load(sqlDataReader);
            }
        }
    }
    return dataTable;
}

[shakti5385]

Thanks for the good advice , but in the case of the data reader what shoud I do?
because a datareader required an open connection.
just look the code of the data reader, here connection is not closed yet

public SqlDataReader ReturnDataReader(string queryString)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection = ReturnNewConnection();
            SqlCommand SqlCommand = new SqlCommand(queryString,sqlConnection);
            SqlDataReader sqlDataReader;
            try
            {
                sqlConnection.Open();
                sqlDataReader = SqlCommand.ExecuteReader();
                return sqlDataReader;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                //what to do , how use using Here
            }
        }

EDIT
One more thing in your code where are you opening the connection?

Because using my fucntion I am returning a connection object but it is not opened

public SqlConnection ReturnNewConnection()
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                sqlConnection.ConnectionString = _ConnectionString;
                return sqlConnection;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

[jmcilhinney]

try
{
    using (Thing myThing = new Thing())
    {
        // ...
    }
}
catch
{
}

or you can put the exception handler inside the using block. Which is better will depend on the situation, although in many cases it may not matter.

[shakti5385]

Ok thats fine

But please look this code
and tell me that where we are opening the connection?

is the using block open the connection directly?

public DataTable ReturnDataTableRead(string queryString)
        {
            try
            {
                DataTable dataTable = null;
                using (SqlConnection sqlConnection = ReturnNewConnection())
                {
                    using (SqlCommand SqlCommand = new SqlCommand(queryString, sqlConnection))
                    {
                        using (SqlDataReader sqlDataReader = SqlCommand.ExecuteReader())
                        {
                            dataTable = new DataTable();
                            dataTable.Load(sqlDataReader);
                        }
                    }
                }
                return dataTable;
            }
            catch
            {
                throw;
            }
        }
 
 public SqlConnection ReturnNewConnection()
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                sqlConnection.ConnectionString = _ConnectionString;
                 return sqlConnection;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

[jmcilhinney]

Well, it's not my code but presumably the ReturnNewConnection method opens the connection before returning it. The connection is disposed at the end of the 'using' block so it gets closed then.

[shakti5385]

is that you want to say?

public SqlConnection ReturnNewConnection()
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                sqlConnection.ConnectionString = _ConnectionString;
                sqlConnection.Open();
                return sqlConnection;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                sqlConnection.Dispose();
            }
        }

But above code will not work for the data reader because , a datareader required an open connection

Please guide me sir

[jmcilhinney]

Who said to dispose the connection inside the ReturnNewConnection method? Of course you can't do that because the whole point of that method is to return a usable connection.

[shakti5385]

So that is the code is it??

public SqlConnection ReturnNewConnection()
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                sqlConnection.ConnectionString = _ConnectionString;
                sqlConnection.Open();
                return sqlConnection;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

[jmcilhinney]

1. Why are you catching an exception when all you do is throw it again?
2. If you want to rethrow a caught exception you just use the 'throw' key word on its own, without specifying the exception. Otherwise you lose the call stack information.

[shakti5385]

Ok sir check this is it correct?

private SqlConnection ReturnNewConnection()
    {
        SqlConnection sqlConnection = new SqlConnection();
        try
        {
            sqlConnection.ConnectionString = _ConnectionString;
            sqlConnection.Open();
            return sqlConnection;
        }
        catch 
        {
            throw ;
        }
    }

[jmcilhinney]

Again, why do you have any exception handling at all if you're not going to do anything about it? If you're simply going to rethrow the exception then what is the point of catching the exception at all? Unless you plan to add something meaningful to your 'catch' block you should get rid of the exception handling altogether.

[shakti5385]

Sir I am not getting Please if possible redefine then code.

[jmcilhinney]

Am I speaking another language? Remove the exception handler, i.e. the try...catch block.

[shakti5385]

Ok sir

private SqlConnection ReturnNewConnection()
    {
        SqlConnection sqlConnection = new SqlConnection();
        sqlConnection.ConnectionString = _ConnectionString;
        sqlConnection.Open();
        return sqlConnection;
    }

[jmcilhinney]

Perfect. Not that hard, was it?

[shakti5385]

Perfect. Not that hard, was it?

Yes not hard
Try try until not get the success