Database

Re: Data Access Application Block

11-Jul-06 21:22

Hi,

I just have a couple of questions on the DAAB:

When using db.AddInParameter and the input parameter is an empty string, does it automatically add DBNull.Value to the coloumn? Or should I rather then create a method that checks for an empty string, if it is empty then add DBNull.Value to that coloumn (if it accepts NULL values).

How to get the returned value and throw an exception to the user? I usually have a @ReturnValue parameter, with ParameterDirection.ReturnValue. I was thinking of not doing it this way but rather something like:

using (dbCommand = db.GetStoredProcCommand(strSProc))
{
db.AddInParameter(dbCommand, "@CarName", DbType.String, objCar.Name);
// Rest of the parameters

IDataReader rdr = db.ExecuteReader(dbCommand);

if (rdr.Read())
{
// Do convertions here of what was returned from the stored procedure
}
}

Please can someone help advise.

Regards,
ma se

kumarprabhakar7412-Jul-06 0:25

kumarprabhakar74

12-Jul-06 0:25

String or any parameter except int accept Null value for empty parameter.

Kumar

The using block

Brendan Vogt11-Jul-06 21:12

11-Jul-06 21:12

I have the following using block (not all code is displayed):

try
{
using (dbCommand = db.GetStoredProcCommand("sp_GetAllCars"))
{
rdr = db.ExecuteReader(dbCommand);

while (rdr.Read())
{
Car objCar = new Car();
objCar.ID = rdr.GetInt32(0); // Required
objCar.Name = rdr.GetString(1); // Required

// Add Car instance to array
carList.Add(objCar);
}
// Close the reader object
rdr.Close();
}
}
catch (SqlException ex)
{
HandleSQLError(ex, "sp_GetAllCars");
}
finally
{
dbCommand.Dispose();
}

What does the using block close and dispose of, because I am thinking that I am closing uneccessary stuff. I always thought that the using block only closed this that were declared in the brackets like:

using (...only closed what inserted here...)
{
...
}

Please can someone comment.

Regards,
ma se

ekhorutomwen11-Jul-06 22:49

ekhorutomwen

11-Jul-06 22:49

Hi
I don't think you need the

ma se wrote:
dbCommand.Dispose();

dbCommand.Dispose(); in the finally block because the using block would have released the dbcommand

Brendan Vogt11-Jul-06 23:08

Re: The using block [modified]

11-Jul-06 23:08

Thanks I thought so, but what about db and rdr, must I still close rdr? Doesn't the using block take care of this?

ekhorutomwen11-Jul-06 23:30

ekhorutomwen

11-Jul-06 23:30

Hi i am not completely sure what you are trying to do but i would have implemented your code this way:

try
{
using (dbCommand = db.GetStoredProcCommand("sp_GetAllCars"))
{
rdr = dbCommand.ExecuteReader(); //db.ExecuteReader(dbCommand);

while (rdr.Read())
{
Car objCar = new Car();
objCar.ID = rdr.GetInt32(0); // Required
objCar.Name = rdr.GetString(1); // Required

// Add Car instance to array
carList.Add(objCar);
}
// Close the reader object
rdr.Close();
}
}
catch (SqlException ex)
{
HandleSQLError(ex, "sp_GetAllCars");
}
finally
{

}

you need to close rdr since it has exclusive access to the connection and without closing it, you can not use the underlying connection. at the end of the using, the dbCommand will be release. what i think you might need in the finally is to close the underlying connection. but that also depends on if you are not using the connection else where within the function after the finally block. i generally don't dispose my connections. but i use the Close() option since this will return the connection to the pool rather than releasing all its resources which happens with dispose().

-- modified at 5:31 Wednesday 12th July, 2006

Brendan Vogt11-Jul-06 23:47

11-Jul-06 23:47

What do you mean by the underlying connection? Is it the db? Do I have to close/ dispose of this db as well? If so where?

I defined db like this:
// Load the default database
Database db = DatabaseGlobals.GetDefaultDatabase;

Checking for an Existing record when Updating

Kevin McFarlane12-Jul-06 0:22

Kevin McFarlane

12-Jul-06 0:22

If you use a using for the datbase connection and for the data reader then you don't need the calls to close or dispose, e.g.,

using (SqlConnection cn = new SqlConnection(connectionString))
{
    cn.Open();

    string text = "spReadCustomers";

    SqlCommand command = new SqlCommand();
    command.Connection = cn;
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = text;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            int firstNameIdx = reader.GetOrdinal("FirstName");
            int lastNameIdx = reader.GetOrdinal("LastName");
            string firstName = reader.GetString(firstNameIdx);
            string lastName = reader.GetString(lastNameIdx);
            // ... Do stuff
        }
    }
}

Kevin

Brendan Vogt11-Jul-06 21:08

Re: Checking for an Existing record when Updating

11-Jul-06 21:08

Hi,

When updating a record in the database, for example, is it wise (or good practice) to always do a check to see if the record exists in the stored procedure?

Please can someone comment.

Regards,
ma se

kumarprabhakar7412-Jul-06 0:21

kumarprabhakar74

12-Jul-06 0:21

Checking the record in the procedure for update is always secure. It doesn't take any extra overhead in your program but it is additional as well as perfect checking fot data concurrency

Kumar

Update statement

angelagke11-Jul-06 18:23

angelagke

11-Jul-06 18:23

how to write update SQL statement in order to remove all underline in my data for each field, example:

"HIGH COST___" change to "HIGH COST" which is remove the underline for all data..

Thanks

angela

Dinuj Nath11-Jul-06 20:51

Dinuj Nath

11-Jul-06 20:51

Update table1 set col1 = replace(col1,'_','')

--
Don't take life seriously because you can't come out of it alive.
-Warren Miller
(From Monty²[^] bio)

angelagke11-Jul-06 21:37

angelagke

11-Jul-06 21:37

ok, i get it Smile | :)

Thanks a lot for help, Dinuj Nath Wink | ;)

angela

VK-Cadec12-Jul-06 10:01

VK-Cadec

12-Jul-06 10:01

How about::

Update table1 set col1 = 'HIGH COST'