Visual Basic

public abstract class DalBase
{
    private static string connectionString;

    protected DalBase()
    {
    }

    /// <summary>
    /// Builds the command object with an appropriate connection and sets
    /// the stored procedure name.
    /// </summary>
    /// <param name="storedProcedureName">The name of the stored 
    /// procedure</param>
    /// <returns>The command object</returns>
    protected SqlCommand BuildCommand(string storedProcedureName)
    {
        // Create a connection to the database.
        SqlConnection connection = new SqlConnection(connectionString);

        // Create the command object - The named stored procedure
        // will be called when needed.
        SqlCommand result = new SqlCommand(storedProcedureName, 
            connection);
        result.CommandType = CommandType.StoredProcedure;
        return result;
    }

    /// <summary>
    /// Builds a DataAdapter that can be used for retrieving the results 
    /// of queries
    /// </summary>
    /// <param name="storedProcedureName">The name of the stored 
    /// procedure</param>
    /// <returns>A data adapter</returns>
    protected SqlDataAdapter BuildBasicQuery(string storedProcedureName)
    {
        SqlCommand cmd = BuildCommand(storedProcedureName);

        // Set up the data adapter to use the command already setup.
        SqlDataAdapter result = new SqlDataAdapter(cmd);
        return result;
    }
}
And the derived class looks like this:

public class InvoiceDal : DalBase
{

    /// <summary>
    /// A private field to hold the only instance of this class
    /// </summary>
    private static InvoiceDal instance;

    /// <summary>
    /// A private constructor so that the only thing that can 
    /// construct this class is itself
    /// </summary>
    private InvoiceDal()
    {
        connectionString = 
            ConfigurationManager.AppSettings["InvoiceConnectionString"];
    }

    /// <summary>
    /// A public property that retrieves the only instance of the class
    /// </summary>
    public static InvoiceDal Instance
    {
        get
        {
            if (instance == null)
                instance = new InvoiceDal();
            return instance;
        }
    }

    /// <summary>
    /// A sample public method. There are no parameters, it simply calls
    /// a stored procedure that retrieves all the products
    /// </summary>
    /// <returns>A DataTable containing the product data</returns>
    public DataTable GetAllProducts()
    {
        SqlDataAdapter dataAdapter = BuildBasicQuery("GetAllProducts");

        // Get the result set from the database and return it
        DataTable result = new DataTable();
        dataAdapter.Fill(result);
        return result;
    }

    /// <summary>
    /// A sample public method. It takes one parameter which is passed
    /// to the database
    /// </summary>
    /// <param name="invoiceNumber">A number which identifies the
    /// invoice</param>
    /// <returns>A dataset containing the details of the required
    /// invoice</returns>
    public DataSet GetInvoice(int invoiceNumber)
    {
        SqlDataAdapter dataAdapter = BuildBasicQuery("GetInvoice");
        dataAdapter.SelectCommand.Parameters.AddWithValue(
            "@invoiceNumber", invoiceNumber);

        DataSet result = new DataSet();
        dataAdapter.Fill(result);
        return result;
    }

    /// <summary>
    /// A sample public method. Creates an invoice in the database and 
    /// returns the invoice number to the calling code.
    /// </summary>
    /// <param name="customerId">The id of the customer</param>
    /// <param name="billingAddressId">The id of the billing 
    /// address</param>
    /// <param name="date">The date of the invoice</param>
    /// <returns>The invoice number</returns>
    public int CreateInvoice(int customerId, int billingAddressId, 
        DateTime date)
    {
        SqlCommand cmd = BuildCommand("CreateInvoice");
        cmd.Parameters.AddWithValue("@customerId", customerId);
        cmd.Parameters.AddWithValue("@billingAddressId", 
            billingAddressId);
        cmd.Parameters.AddWithValue("@date", date);

        cmd.Connection.Open();
        int result = (int)cmd.ExecuteScalar();
        cmd.Connection.Close();
        return result;
    }

}

Public Class Dal

    Dim connectionString As String

    ' <summary>
    'The static initialiser will be called the first time any
    ' method on the class is called. This ensures that the
    ' connection string is available.
    ' </summary>
    Shared Sub Dal()

   DIm   connectionString as String  =  ConfigurationManager.AppSettings["ConnectionString"]
    End Sub

<br />
   DIm   connectionString as String  =  ConfigurationManager.AppSettings["ConnectionString"]<br />

<br />
using System.Collections.Generic;<br />

Private Function BuildCommand(ByVal storedProcedureName As String)

        ' Create a connection to the database.
        Dim connection = New SqlConnection(connectionString)

        ' Create the command object - The named stored procedure
        ' will be called when needed.
        Dim result As New SqlCommand(storedProcedureName, connection)
        result.CommandType = CommandType.StoredProcedure
        Return result


    End Function

<br />
<br />

' <summary>
     ' Builds a DataAdapter that can be used for retrieving the
     ' results of queries
     ' </summary>
     ' <param name="storedProcedureName">The name of the stored
     ' procedure</param>
     ' <returns>A data adapter</returns>
     Private Function BuildBasicQuery(ByVal storedProcedureName As String)

         Dim cmd As New SqlCommand(BuildCommand(storedProcedureName))

         'Set up the data adapter to use the command already setup.
         Dim result As New SqlDataAdapter(cmd)

         Return result
     End Function

'<summary>
       ' A sample public method. There are no parameters, it simply
       ' calls a stored procedure that retrieves all the products
       ' </summary>
       ' <returns>A DataTable containing the product data</returns>
       Public Function GetAllProducts()

           Dim DataAdapter As New SqlDataAdapter(BuildBasicQuery("GetAllProducts"))
           'Get the result set from the database and return it
           Dim result As New DataTable
           DataAdapter.Fill(result)
           Return result
       End Function

'  <summary>
    ' A sample public method. It takes one parameter which is
    'passed to the database.
    ' </summary>
    ' <param name="invoiceNumber">A number which identifies the
    ' invoice</param>
    ' <returns>A dataset containing the details of the required
    ' invoice</returns>
    Public Function GetInvoice(ByVal invoiceNumber As Integer)

        Dim DataAdapter As New SqlDataAdapter(BuildBasicQuery("GetInvoice"))
        DataAdapter.SelectCommand.Parameters.AddWithValue( _
            "@invoiceNumber", invoiceNumber)

        Dim result As New DataSet
        DataAdapter.Fill(result)
        Return result
    End Function

<br />
<br />
            Dim DataAdapter As New SqlDataAdapter(BuildBasicQuery("GetInvoice" _<br />
              , "@invoiceNumber", invoiceNumber))<br />

Public Function CreateInvoice(ByVal customerId As Integer, _
   ByVal billingAddressId As Integer, ByVal DateTime As Date)

       Dim cmd As New SqlCommand(BuildCommand("CreateInvoice"))
       cmd.Parameters.AddWithValue("@customerId", customerId)
       cmd.Parameters.AddWithValue("@billingAddressId")
           billingAddressId)
       cmd.Parameters.AddWithValue("@date",date)

       cmd.Connection.Open()
       dim result as Integer  = (integer).cmd.ExecuteScalar()
       cmd.Connection.Close()
       Return result
   End Function

<br />
            int result = (int)cmd.ExecuteScalar()

Vuyiswamb wrote:

On this one i need your help, i got Stucked. the Code Complained in the Following line.


Dim DataAdapter As New SqlDataAdapter(BuildBasicQuery("GetInvoice" _
, "@invoiceNumber", invoiceNumber))

Dim dataAdapter As SqlDataAdapter = BuildBasicQuery("GetInvoice")

Vuyiswamb wrote:

The Compiler Complained about All the lines in this Function.

Vuyiswamb wrote:

Dim cmd As New SqlCommand(BuildCommand("CreateInvoice"))

Vuyiswamb wrote:

cmd.Parameters.AddWithValue("@billingAddressId") billingAddressId)

Vuyiswamb wrote:

int result = (int)cmd.ExecuteScalar()
i Think i you can confirm my Code, i will try to understand.

    public int CreateInvoice(int customerId, int billingAddressId, 
        DateTime date)
    {
        SqlCommand cmd = BuildCommand("CreateInvoice");
        cmd.Parameters.AddWithValue("@customerId", customerId);
        cmd.Parameters.AddWithValue("@billingAddressId", 
            billingAddressId);
        cmd.Parameters.AddWithValue("@date", date);

        cmd.Connection.Open();
        int result = (int)cmd.ExecuteScalar();
        cmd.Connection.Close();
        return result;
    }

}

Vuyiswamb wrote:

i had a problem with the Connection string

DIm connectionString as String = ConfigurationManager.AppSettings["ConnectionString"]

The IDE tells me that "Identifier Expected"
The First one is the namespaces that needs to be imported

Imports System.Configuration

Vuyiswamb wrote:

using System.Collections.Generic;

This line it does not recognise the Generic. if i remove it "Generic" it does not Complain.

Vuyiswamb wrote:

The Second Function i coverted it like this

Private Function BuildCommand(ByVal storedProcedureName As String)
    ' Create a connection to the database.
    Dim connection As New SqlConnection(connectionString)
    ' Create the command object - The named stored procedure
    ' will be called when needed.
    Dim result As New SqlCommand(storedProcedureName, connection)
    result.CommandType = CommandType.StoredProcedure
    Return result
End Function

Vuyiswamb wrote:

My Following Function is like this

' Builds a DataAdapter that can be used for retrieving the
' results of queries
Private Function BuildBasicQuery(ByVal storedProcedureName As String)
    Dim cmd As SqlCommand = BuildCommand(storedProcedureName)
    ' Set up the data adapter to use the command already setup.
    Dim result As New SqlDataAdapter(cmd)
    Return result
End Function

Vuyiswamb wrote:

On the Last one, i was Completely lost

Public Function CreateInvoice(ByVal customerId As Integer, _ ByVal billingAddressId As Integer, ByVal DateTime As Date) Dim cmd As New SqlCommand(BuildCommand("CreateInvoice")) cmd.Parameters.AddWithValue("@customerId", customerId) cmd.Parameters.AddWithValue("@billingAddressId") billingAddressId) cmd.Parameters.AddWithValue("@date",date) cmd.Connection.Open() dim result as Integer = (integer).cmd.ExecuteScalar() cmd.Connection.Close() Return result End Function


The Compiler Complained about All the lines in this Function. the other thing is that i did not understand the Following line in the last function

Public Function CreateInvoice(ByVal customerId As Integer, _
        ByVal billingAddressId As Integer, ByVal ByVal Date As DateTime) As Integer
    Dim cmd As SqlCommand = BuildCommand("CreateInvoice")
    cmd.Parameters.AddWithValue("@customerId", customerId)
    cmd.Parameters.AddWithValue("@billingAddressId", billingAddressId)
    cmd.Parameters.AddWithValue("@date", date)
    cmd.Connection.Open()
    Dim result as Integer = CInt(cmd.ExecuteScalar())
    cmd.Connection.Close()
    Return result
End Function