Data Access FAQ RSS

• • Reply
  

  Allen Chen –...

  All-Star

  40943 Points

  4949 Posts

  Data Access FAQ

  Dec 18, 2008 08:25 AM|LINK

  1.    General

  1.1     How do I set the parameter values of an ObjectDataSource control in code?

  1.2     What is the difference between DataSet and DataReader?

  1.3     How do I update the primary key in an ObjectDataSource control when it is used with the GridView control?

  1.4     How do I call a parameterized stored procedure by using ADO.NET?

  1.5     Why can’t ObjectDataSource find the type specified in the TypeName property?

  1.6     How do I update all the changes in a DataTable or DataSet object?     

  1.7     How do I get the return value when calling a stored procedure in a typed DataSet object?

  1.8     What is SQL injection and how do I prevent it?

  1.9     Why do I get the error "Timeout expired" when I try to connect to a database?

  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."?

  1.11  How do I speed up slow queries in the database?

  2.   LINQ

  2.1     How do I implement a transaction in LINQ?

  2.2     How do I create a left outer join in LINQ?

  2.3     What is the difference between List<T> and IQueryable<T>?

  2.4     How do I implement a SQL-type LIKE operation in LINQ?

  2.5     How do I query a DataTable object by using LINQ?

   

  <div style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 1pt; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; BORDER-TOP: medium none; BORDER-RIGHT: medium none; PADDING-TOP: 0cm" class=Section1 mce_style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 1pt; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; BORDER-TOP: medium none; BORDER-RIGHT: medium none; PADDING-TOP: 0cm">

   

   

  </div>

  
  

  1      General

  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, the DataReader 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 the 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 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. A CommandBuilder object helps generate all the changes in a DataTable 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 the TableAdapter instance; you can see this value in the design of a typed DataSet 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 SqlConnection object.

  ·         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 the SqlCommand 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 the Contains, 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 IEnumerable interface. To query a DataTable object, first call the AsEnumerable method 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"
                     };
  
  

  
  

  Sincerely,
  Allen Chen
  Microsoft Online Community Support
  
  Please remember to mark the replies as answers if they help and unmark them if they provide no help.
  • Edited by Allen Chen – MSFT on Dec 18, 2008 08:47 AM
  • Edited by Allen Chen – MSFT on Dec 18, 2008 08:54 AM
  • Edited by Allen Chen – MSFT on Dec 18, 2008 09:09 AM
  • Edited by Allen Chen – MSFT on Dec 19, 2008 10:25 AM
  • Edited by Allen Chen – MSFT on Dec 19, 2008 10:32 AM
  • Edited by JoeStagner on Feb 16, 2009 01:00 PM
  • Unlocked by Allen Chen – MSFT on Apr 08, 2009 01:49 AM
  • Locked by Allen Chen – MSFT on Apr 08, 2009 01:49 AM
  • Edited by Allen Chen – MSFT on Apr 08, 2009 07:18 AM
  • Edited by Allen Chen – MSFT on Apr 08, 2009 07:21 AM
  • Edited by Allen Chen – MSFT on Apr 08, 2009 08:25 AM
  • Edited by mbanavige on Apr 08, 2009 01:44 PM
  • Edited by mbanavige on Apr 08, 2009 01:45 PM
  • Edited by mbanavige on Apr 08, 2009 01:49 PM
  • Edited by tmorton on Jul 16, 2010 08:44 PM