How do we use stored procedure in ADO.NET and how do we provide parameters to the stored procedures?
ADO.NET provides the SqlCommand object, which provides the functionality of executing stored procedures. In the command type we need to provide the command type as stored procedure as shown in the below code snippet.
SqlCommand objCommand = new SqlCommand("sp_Insert", objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
How can we force the connection object to close after my data reader is closed?Command method Execute reader takes a parameter called as Command Behavior wherein we can specify saying close connection automatically after the Data reader is close.
PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.CloseConnection)
I want to force the data reader to return only schema of the data store rather than data.
PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.SchemaOnly)
How can we fine-tune the command object when we are expecting a single row?Again, CommandBehaviour enumeration provides two values Single Result and Single Row. If you are expecting a single value then pass “CommandBehaviour.SingleResult” and the query is optimized accordingly, if you are expecting single row then pass “CommandBehaviour.SingleRow” and query is optimized according to single row.
Which is the best place to store connection string in .NET projects?Config files are the best places to store connection strings. If it is a web-based application “Web.config” file will be used and if it is a windows application “App.config” files will be used.
See following video on Dataset is a disconnect while datareader is connected: -
Click and see here for more
training on ADO.NET.