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.
Note :- Sample code is provided in folder “WindowsSqlClientCommand”. There are two stored procedures created in same database “Employees” which was created for the previous question.
CREATE PROCEDURE SelectByEmployee @FirstName nvarchar(200) AS
Select FirstName from Employees where FirstName like @FirstName + '%'
CREATE PROCEDURE SelectEmployee AS
Select FirstName from Employees
If txtEmployeeName.Text.Length = 0 Then
objCommand = New SqlCommand(“SelectEmployee”)
Else
objCommand = New SqlCommand(“SelectByEmployee”)
objCommand.Parameters.Add(“@FirstName”, Data.SqlDbType.NVarChar, 200)
objCommand.Parameters.Item(“@FirstName”).Value = txtEmployeeName.Text.Trim()
End If
In the above sample, not much has been changed only that the SQL is moved to the stored procedures. There are two stored procedures one is “Select Employee” which selects all the employees and the other is “SelectByEmployee” which returns employee name starting with a specific character. As you can see to provide parameters to the stored procedures, we are using the parameter object of the command object. In such question interviewer expects two simple answers one is that we use command object to execute stored procedures and the parameter object to provide parameter to the stored procedure. Above sample is provided only for getting the actual feel of it. Be short be nice and get a job.
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 where in 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.
What are the steps involved to fill a dataset?
Twist: - How can we use data adapter to fill a dataset?
Sample code is provided in “WindowsDataSetSample” folder in CD.”LoadData” has all the implementation of connecting and loading to dataset. This dataset is finally bind to a List Box. Below is the sample code.
Private Sub LoadData()
Dim strConnectionString As String
strConnectionString = AppSettings.Item(“ConnectionString”)
Dim objConn As New SqlConnection(strConnectionString)
objConn.Open()
Dim objCommand As New SqlCommand(“Select FirstName from Employees”)
objCommand.Connection = objConn
Dim objDataAdapter As New SqlDataAdapter()
objDataAdapter.SelectCommand = objCommand
Dim objDataSet As New DataSet
End Sub
In such type of questions interviewer is looking from practical angle, that have you worked with dataset and datadapters. Let me try to explain the above code first and then we move to what steps should be told during interview.
Dim objConn As New SqlConnection(strConnectionString)
objConn.Open()
First step is to open the connection. Again, note the connection string is loaded from config file.
Dim objCommand As New SqlCommand(“Select FirstName from Employees”)
objCommand.Connection = objConn
Second step is to create a command object with appropriate SQL and set the connection object to this command.
Dim objDataAdapter As New SqlDataAdapter()
objDataAdapter.SelectCommand = objCommand
Third steps is to create the Adapter object and pass the command object to the adapter object.
objDataAdapter.Fill(objDataSet)
Fourth step is to load the dataset using the “Fill” method of the data adapter.
lstData.DataSource = objDataSet.Tables(0).DefaultView
lstData.DisplayMember = “FirstName”
lstData.ValueMember = “FirstName”
Fifth step is to bind to the loaded dataset with the GUI. At this moment sample has list box as the UI. Binding of the UI is done by using Default View of the dataset. Just to revise every dataset has tables and every table has views. In this sample, we have only loaded one table i.e. Employees table so we are referring that with an index of zero.
Just say all the five steps during interview and you will see the smile on the interviewer’s face and appointment letter in your hand.
What are the various methods provided by the dataset object to generate XML?
Note:- XML is one of the most important leap between classic ADO and ADO.NET. So this question is normally asked more generally how can we convert any data to XML format. Best answer is convert in to dataset and use the below methods.
• ReadXML
Read’s a XML document in to Dataset.
• GetXML
This is a function, which returns the string containing XML document.
• Writexml
This writes a XML data to disk.
How can we save all data from dataset?
Dataset has “Accept Changes” method, which commits all the changes since last time “Accept changes” has been executed.
Note :- This book does not have any sample of Acceptchanges. We leave that to readers as homework sample. But yes from interview aspect that will be enough.
How can we check that some changes have been made to dataset since it was loaded?
Twist: - How can we cancel all changes done in dataset?
Twist: - How do we get values, which are changed, in a dataset?
For tracking down changes, Dataset has two methods, which comes as rescue “Get Changes “and “Has Changes”.
Get Changes
Returns dataset, which are changed since it, was loaded, or since Accept changes was executed.
Has Changes
Or abandon all changes since the dataset was loaded use “Reject Changes This property indicates that has any changes been made since the dataset was loaded or accept changes method was executed.
Note:- One of the most misunderstood things about these properties is that it tracks the changes of actual database. That is a fundamental mistake; actually the changes are related to only changes with dataset and have nothing to with changes happening in actual database. As dataset are disconnected and do not know anything about the changes happening in actual database.
No comments:
Post a Comment