Data Access FAQ (一)

前些日子，写了ASP.NET Data Access的FAQ，现在贴出来和大家一起分享，希望对大家有帮助! 这里是第一部分： 打包下载 Table of Contents General How can I set the parameter values of ObjectDataSource in code-behind? Comparing DataSet and DataReader

前些日子，写了ASP.NET Data Access的FAQ，现在贴出来和大家一起分享，希望对大家有帮助! 这里是第一部分：

打包下载

Table of Contents

General

How can I set the parameter values of ObjectDataSource in code-behind?

Comparing DataSet and DataReader.

How to update the primary key in ObjectDataSource when used with GridView.

How to call a parameterized stored procedure via ADO.NET.

Why ObjectDataSource couldn’t find the type specified in TypeName property.

How to update all the changes in DataTable/DataSet.

How can I get the return value when calling stored procedure in typed DataSet?

What’s SQL injection? How to avoid that?

Why I can’t connect to my database and I also get the error ‘connection out of time’?

Failed to enable constraints in typed DataSet.

How to handle slow querying in a database.

How to select distinct rows in a DataTable.

LINQ

How can I implement a transaction in LINQ?

How can I use left join in LINQ.

What’s the difference between List and IQueryable?

How to implement ‘Like’ operation in LINQ just like in SQL script?

How to query a DataTable using LINQ?

 

 

General

How can I set the parameter values of ObjectDataSource in code-behind?

A: Suppose that you want to set the parameters in the Select method of ObjectDataSource. You can handle the Selecting event of ObjectDataSource to set the parameters. For example:

protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)

{

    e.InputParameters["accountID"] = 5;

}

Comparing DataSet and DataReader.

A: DataSet is a collection of DataTables and relations between the tables. It is used to hold tables with data, you can select data from tables or create views and access child rows. Also DataSet provides you with rich features like saving data to XML and loading data from XML. DataReader is an object that is used to iterate through a result set that was queried from a server and reads one row per move. If you want forward-only access to the results then DataReader is the best option because it is the most efficient method in this scenario.

Related link:

http://www.windowsitlibrary.com/Content/1205/06/files/Table3.html

How to update the primary key in ObjectDataSource when used with GridView.

A: If you need to update the primary key in Business Logic Layer (BLL), you need to specify DataKeyNames property in GridView control and OldValuesParameterFormatString property in ObjectDataSource 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 Business Logic Layer is:

[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update, true)]

public void UpdateDec(int original_id, int id, decimal value)

How to call a parameterized stored procedure via ADO.NET.

A: When calling a parameterized stored procedure via ADO.NET, you need to set the CommandType property to ‘StoredProcedure’, and correctly set the Direction property when passing parameters into the command. The example below demonstrates 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();

Response.Write("
OutPut Value:" + uName.Value);

Response.Write("
Return Value:" + ret.Value);

Why ObjectDataSource couldn’t find the type specified in TypeName property.

A: Please check your project’s references and ensure the source code or assembly that contains the type is in the right location. The type specified in TypeName property of ObjectDataSource should be in Bin, App_Code or GAC. If your type can’t be loaded from assemblies in the related directories, a runtime exception will be thrown.

P.S You can also use Fusion log viewer tool to see the binding failures.

Related link: http://blogs.msdn.com/suzcook/archive/2003/05/29/57120.aspx

How to update all the changes in DataTable/DataSet.

A: Usually,you can update all the changes in DataTable/DataSet to the database with the help of CommandBuilder object. A CommandBuilder object will help to generate all the changes in DataTable/DataSet to SQL statements which will be executed by the Command object.

To update all the changes to SQL Server, we can write the code as shown below:

da.Fill(ds, "tab1");

SqlCommandBuilder cb = new SqlCommandBuilder(da);

 

ds.Tables[0].Rows[0]["username"] = "Modified Name";

da.UpdateCommand = cb.GetUpdateCommand();

da.Update(ds); 

How can I get the return value when calling stored procedure in typed DataSet?

A: As we know, the stored procedure is called as a method of a typed DataSet. But when we return a value from a stored procedure, we can’t get it via the form of ‘(int)da.CallSP(xx,xx);’. If you want to get the return value from a stored procedure, you need to write a new method in the partial class of TableAdapters as shown below – the input parameter is the index of the method in the TableAdapter which can be easily seen in the designer of typed DataSet.

partial class UsersTableAdapter

{

     public object GetReturnValue(int commandIndex)

     {

         return this.CommandCollection[commandIndex].Parameters[0].Value;

     }

}

Then you can call this method to get the return value:

da.CallSP(xx, xx);

int returnValue = int.Parse(da.GetReturnValue(2).ToString());

What’s SQL injection? How to avoid that?

A: When you use a string query to build a SQL statement with input values from end-user, it is easy to have a SQL injection attack. For example, we have the following SQL statement 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, some dangerous SQL commands such as “’; DROP TABLE …” might be executed.

To avoid SQL injection, we can use command parameters in SQL query – this will be efficient to validate the user. The single quotes will be filtered.

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 --";

Why I can’t connect to my database and I also get the error ‘connection out of time’?

A: The time-out value of SqlConnection or SqlCommand object might be too small.

The default time out of SqlConnection is 15 seconds. You can set this time longer via these two ways.

·         Set the ConnectionTimeout property of SqlConnection object.

·         Set the Connect Timeout property in the connection string.

The default time out of the SqlCommand is 30 seconds. You can also set this time longer.

·         Set the CommandTimeout property of SqlCommand object.

Failed to enable constraints in typed DataSet.

A: The problem was caused by a discrepancy between the defined max size of a data column in your project's XSD and the size in database. The error is because one of the columns in the database is larger than the one in XSD / table adapter. You can try to update the table schema in XSD or modify the maximum size of a data column manually.

How to handle slow querying in a database.

A: There may be several possible causes, here are some general suggestions:

·         Is your table very large?  Is it possible to split it into several smaller tables?

·         If you don't use all the fields, you could just return the required fields when constructing your SQL statements. Also, you may need to optimize your SQL statements when querying.

·         You may want to index some frequently used fields in database. It'll save you time when querying.

How to select distinct rows in a DataTable.

A: As we know, we can use Select method to filter a DataTable based on certain fields. However, it doesn't help to select distinct rows in a DataTable. Therefore, we have no way to select distinct rows directly.

After a hard research on MSDN, I found a solution here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3633630&SiteID=1

First, create a DataView for your table, and apply any filtering and sorting in that dataview.  If you have no filtering or sorting, you can just use originalTable.DefaultView.

Second, call ToTable() on the dataview.  ToTable() has an overload which lets you specify whether or not to return only distinct values, and a params string[] argument to specify which columns you want in the new table.  If you specify true as the first argument, only rows unique within the columns that you specify in the params argument will be returned.  If you want all of the columns from the original table, you can simply specify the boolean argument "true"; if you don't specify any columns, all columns are returned.

Thus, the easiest way to return a new table that has only the unique values from the original table is as follows:

DataTable distinctTable = originalTable.DefaultView.ToTable( /*distinct*/ true);