Ehsan Tavakoli

Writing About .NET Programming and other stuff

Archive for the ‘TableAdapter’ Category

Three common ways to Connect to database in ASP.NET

with one comment

There are numbers of ways for building database driven websites using ASP.NET. In this post I’m going to show you three common ways of doing that. Please note these three are not the best ways for retrieving data from database and present it on the web. However, you may find one or coup of this ways easy, fast or even flexible for your specific project. In next articles I’m going to develop a small project using 3-layer architecture. But before that, I’m going to show other ways.

The Spaghetti Model

Oh yeah, the old fashion way for dealing with any kind of algorithm or database or even interface design.  To be honest, it still works well. Depends on what do you want to get. Or how much time do you have. Or simply about how much you care! No just joking! Here is sample page: retrieving information from [tblProduct] in [dbShop]. Present those two field on page and you can update them! Easy and straight forward.

using System;
using System.Configuration;
using System.Data.SqlClient;

namespace ecom
{
    public partial class spaghetti : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                string sql = @"SELECT Title, Price FROM tblProduct WHERE ID=1";

                using (
                    SqlConnection myConnection =
                        new SqlConnection(ConfigurationManager.ConnectionStrings["NLayer"].ConnectionString))
                {
                    using (SqlCommand myCommand = new SqlCommand(sql, myConnection))
                    {
                        myConnection.Open();
                        using (SqlDataReader myReader = myCommand.ExecuteReader())
                        {
                            if (myReader.Read())
                            {
                                txtTitle.Text = myReader.GetString(0).ToString();
                                txtPrice.Text = myReader.GetValue((1)).ToString();

                            }
                            myReader.Close();
                        }
                        myConnection.Close();
                    }
                }

        }
        }

        protected void submit_Click(object sender, EventArgs e)
        {
            string sqlBase = @"UPDATE tblProduct SET Title='{0}', Price='{1}' WHERE ID=1";
            using (SqlConnection myConnection =
                new SqlConnection(ConfigurationManager.ConnectionStrings["NLayer"].ConnectionString))
            {
                string sql = string.Format(sqlBase, txtTitle.Text, txtPrice.Text);
                SqlCommand myCommand = new SqlCommand(sql, myConnection);
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }
    }
}

But there is number of issues:

  1. SQL injection is a piece of cake for this code. However, you can put another method there to handle SQL Injection. Otherwise, you can loss your data in a snap!
  2. This code is hard to write and remember. And it’s hard to modify any fields in DB or add new field.
  3. You cannot use this code for other scenarios.



Using SqlDataSource Control

It’s another common ways to connect to database is using SqlDataSource. There are easy to configure and create CRUD (Create, Read, Update and Delete) procedures in a glance. Then you can easily assign them to control like the GrideView or ForView.

The problem is they make huge amount of codes on your page. And really you don’t know how they manage the transaction. Of course they put all things in XML files but it’s kind of hard to manage and edit these files. And it will become more complicated if you want to add your own function for conversation or validation of data.

Here is an example of using SqlDataSource Control for previous scenario.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="sqldatasource.aspx.cs" Inherits="ecom.sqldatasource" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:SqlDataSource ID="SqlDataSource1"
            ConnectionString="<%$ ConnectionStrings:dbEcomConnectionString %>"
            SelectCommand="SELECT * FROM [tblProduct]" runat="server"></asp:SqlDataSource>

    </div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataKeyNames="ID" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
                ReadOnly="True" SortExpression="ID" />
            <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
            <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
        </Columns>
    </asp:GridView>
    </form>
</body>
</html>

Using TableAdapters
Another alternative way for working with database is using TableAdapters. They can be made by Visual Studio and all you need it’s just put your query statements and using the tools. You can access to business object by using classes that inherit form System.Data.DataTable and System.Data.DataRow by using method like AcceptChanges.
There is valuable article about using TableAdapters by Scott Mitchell’s here

In next few posts I’m going to build a simple website using 3-layer architecture..

*This article is highly adopted from here

Written by Ehsan Tavakoli

May 24, 2010 at 2:33 am