ADO.net Interview Questions and answers

1.1     How do I set the parameter values of an ObjectDataSource control in code? [top]
Suppose that you want to set the parameters in the Select method of an ObjectDataSource control. You can handle the Selecting event to set the parameters. For example:
protected void ObjectDataSource1_Selecting(object sender,
ObjectDataSourceSelectingEventArgs e)
{
e.InputParameters["accountID"] = 5;
}

1.2     What is the difference between DataSet and DataReader?[top]
A DataSet object is a collection of DataTable objects and includes information about relationships between the tables. A DataSet object is used to select data from tables or to create views and access child rows. In addition, DataSet provides you with rich features like saving data to XML files and loading data from XML files. DataReader is an object that is used to iterate a result set from a query; it reads one row at a time. If you want forward-only access to the results, theDataReader object is the best option because it is the most efficient method in this scenario.
Also, DataSet is used for a disconnected case while the DataReader needs to keep connection to a database.

Related link:
DataReader and DataSet
 
1.3     How do I update the primary key in an ObjectDataSource control when it is used with the GridView control? [top]
If you need to update the primary key in the Business Logic Layer (BLL), you must specify the DataKeyNames property in the GridView control and the OldValuesParameterFormatString property in theObjectDataSource control.
For example:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="SelectDecs"
TypeName="Job.Code.bll"
UpdateMethod="UpdateDec">
<UpdateParameters>
<asp:Parameter Name="original_id" Type="Int32" />
<asp:Parameter Name="id" Type="Int32" />
<asp:Parameter Name="value" Type="Decimal" />
</UpdateParameters>
</asp:ObjectDataSource>
The signature of the update method in the Business Logic Layer is:
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update, true)]
public void UpdateDec(int original_id, int id, decimal value)

1.4     How do I call a parameterized stored procedure by using ADO.NET? [top]
When you call a parameterized stored procedure by using ADO.NET, you need to set the CommandType property of the command object to "StoredProcedure", and you must set the Direction property appropriately for each parameter. The following example shows how to call a parameterized stored procedure:
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter id = cmd.Parameters.Add("@id", SqlDbType.Int, 4);
id.Direction = ParameterDirection.Input;
SqlParameter uName = cmd.Parameters.Add("@uName",SqlDbType.Char,20);
uName.Direction = ParameterDirection.Output;
SqlParameter ret = cmd.Parameters.Add("@Return_value",SqlDbType.Int,4);
ret.Direction = ParameterDirection.ReturnValue;

id.Value = 2;
conn.Open();
cmd.ExecuteNonQuery();
Label1.Text = uName.Value;
Label2.Text = ret.Value;
1.5     Why can’t ObjectDataSource find the type specified in the TypeName property? [top]
Check the project’s references and make sure that the source code or assembly that contains the type is in the right location. The type specified in the TypeName property of the ObjectDataSource control should be in the Bin or App_Code folders, or in the GAC. If the type cannot be loaded from assemblies in the related directories, a run-time exception is thrown. The TypeName format should be composed of namespace and the type name. For example:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="SelectDecs"
TypeName="Job.Code.Test" >
Note:   You can use the Fusion log viewer tool to examine binding failures.

Related link
"Debugging Assembly Loading Failures", blog entry by Suzanne Cook

1.6     How do I Update all the changes in a DataTable or DataSet object? [top]
You can send all updates in a DataTable or DataSet object to the database with the help of the CommandBuilder object. ACommandBuilder object helps generate all the changes in aDataTable or DataSet object into SQL statements that can be executed by the Command object.
To update all the changes to SQL Server, use codes like the following example:
da.Fill(ds, "tab1");
SqlCommandBuilder cb = new SqlCommandBuilder(da);
ds.Tables[0].Rows[0]["username"] = "Modified Name";
da.UpdateCommand = cb.GetUpdateCommand();
da.Update(ds);

1.7     How do I get the return value when calling a stored procedure in a typed DataSet object? [top]
A stored procedure is called as a method of a typed DataSet object. However, when you return a value from a stored procedure, you cannot get it directly. If you want to get the return value from a stored procedure, you must write a new method in the partial class of the generated TableAdapter object, like that shown in the following example. The input parameter is the index of the method in theTableAdapter instance; you can see this value in the design of a typedDataSet object.
partial class UsersTableAdapter
{
public object GetReturnValue(int commandIndex)
{
return this.CommandCollection[commandIndex].Parameters[0].Value;
}
}
You can then call the method to get the return value as shown in the following example:
da.CallSP(xx, xx);
int returnValue = int.Parse(da.GetReturnValue(2).ToString());

1.8     What is SQL injection and how do I prevent it? [top]
A SQL injection attack lets malicious users alter SQL commands or execute arbitrary SQL commands. SQL injection attacks typically replace or add to queries that are already being legitimately executed as part of an application. When you use string concatenation to create a SQL statement with input values from an end user, it becomes possible to launch a SQL injection attack. For example, the following SQL statement might be used to verify a user’s password:
sql = "select * from UserInfo where password='" + password + "'";
A malicious user can use the following input to bypass the password check:
password = "' or 1=1 --";
Even worse, the malicious user might use the command delimiter (; ) to add SQL commands to the user input, as in this example:
; DROP TABLE
To help avoid SQL injection, use command parameters in SQL queries, which help validate user input. The single quotation marks will be represented in an alternate format to pass validation. This example will not cause SQL injection although the password is not valid:
cmd = new SqlCommand("select * from UserInfo where password=@ password", conn);
SqlParameter pwd = cmd.Parameters.Add("@password", SqlDbType.Char, 20);
pwd.Direction = ParameterDirection.Input;
pwd.Value = "' or 1=1 --";
1.9     Why do I get the error "Timeout expired " when I try to connect to a database? [top]
The time-out value for a SqlConnection or SqlCommand object might be too small. The default time-out value for the SqlConnection class is 15 seconds. You can set this to a longer time in these ways:
·         Set the ConnectionTimeout property of the SqlConnectionobject.
·         Set the Connect Timeout property in the connection string.
The default time-out value for the SqlCommand class is 30 seconds. To change this, set the CommandTimeout property of theSqlCommand object.

1.10     Why do I get the error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."? [top]
This problem is caused by a discrepancy between the defined maximum size of a data column in a project's XSD schema and the column size in database. The error occurs when one of the columns in the database is larger than the corresponding column size as defined in the XSD schema. To resolve the problem, update the table in the XSD schema or modify the maximum size of a data column in the database.

1.11     How do I speed up slow queries in the database? [top]
Slow queries can have several causes. Here are some general suggestions:
·         Is the table very large? If so, is it possible to split it into smaller tables?
·         Specify only the data fields that you need when you construct SQL statements.
·         Add indexes to the most commonly used fields.

2      LINQ
2.1     How do I implement a transaction in LINQ? [top]
Use the TransactionScope class in LINQ to implement an implicit transaction. The following example shows how to create a transaction in LINQ:
using (TransactionScope scope = new TransactionScope())
{
try
{
// ...
ctx.SubmitChanges();
// ...
ctx.SubmitChanges();
}
catch (Exception ex)
{
Response.Write("An error has occurred -- transaction
will automatically be rolled backed.");
}

scope.Complete();
}
You must reference the System.Transactions assembly and add the namespace System.Transactions to the project. You must also make sure that the Distributed Transaction Coordinator Service Windows Service is running.
Related link
TransactionScope Class

2.2     How do I create a left outer join in LINQ? [top]
Use the keywords join and into to implement a left outer join in LINQ, as shown in the following example:
var sel = from u in ctx.Tags
join p in ctx.ArticlesTags on u.TagID equals p.TagID into UP
from p in UP.DefaultIfEmpty()
select new
{
UT = u.TagID,
UT1 = u.Text,
UT2 = p.Info
};
 
2.3     What is the difference between List<T> and IQueryable<T>?[top]
You can return LINQ query results as List<T> or IQueryable<T>. There are some differences between these two types.
List<T> will create a new list object in memory immediately in order to persist data. If there are any associations in this table, the related information will be null. IQueryable<T> will not retrieve the data until you iterate over the data source by using foreach, DataBind, ToList, and so on. If there are associations in the table, the related information will not be null and can be used. The following example shows the differences between the two classes.
// Return List<T> will fail when referring to related UserInfos object.
List users = res.ToList();
var ss = users.Where(p => p.UserInfos.ID != 3);

// Return IQueryable<T> will be successful.
IQueryable users = res.AsQueryable();
var ss = users.Where(p => p.UserInfos.ID != 3);

2.4     How do I implement a SQL-type LIKE operation in LINQ? [top]
You can create LIKE functionality in two ways. You can use theContains, StartsWith, or EndsWith methods, as shown in the following example:
var dd = from p in ctx.Users
where p.email.Contains("xx") || p.userName.StartsWith("xx") ||
p.userName.EndsWith("xx")
select p;
You can also use the SqlMethods class, which contains a Like method that has the same semantics as the equivalent SQL operation. The following example shows how to use this class:
var dd = (from p in ctx.Users
where SqlMethods.Like(p.userName, "%Jiang%") &&
SqlMethods.Like(p.email,"%WWW%")
orderby p.accountID
select p).Take(10);

2.5     How do I query a DataTable object by using LINQ? [top]
LINQ can query any data source that implements the IEnumerableinterface. To query a DataTable object, first call the AsEnumerablemethod of the DataTable class. You can then use LINQ to query the data. The following example shows how:
var nostr = from u in dt.AsEnumerable()
where u.Field("m").ToString().ToUpper().StartsWith("3")
select new
{
MONEY = u.Field("m"),
TIME = u.Field("t"),
EXT = "Extra Column"
};