Create common class for Database connection using C# and VB.Net in ASP.Net

Last Reply 5 months ago By pandeyism

Posted 5 months ago

in class file for CRUD i need to write lots of repetive code like i write comments on below code please advice

    public void EmailSetup()
    {
        string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;   // this line can be go dbconnection file
        using (SqlConnection con = new SqlConnection(constr)) // this line can be go dbconnection file
        {
            using (SqlCommand cmd = new SqlCommand("sp_Helper", con))  // this line can be go dbconnection file  and it can receive storeprocedure name
            {
                cmd.CommandType = CommandType.StoredProcedure; // this line can be go dbconnection file
		//so i need to only pass paraemter in each class  file by giving storeprocedure name
                cmd.Parameters.AddWithValue("@Action", "EmailSetup");
		//this whole section can go into dbconnection class file
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    smtp = sdr["smtp"].ToString();
                    bcc = sdr["bcc"].ToString();
                    emailfrom = sdr["emailfrom"].ToString(); 
                    password = sdr["password"].ToString();
                    port = Convert.ToInt32(sdr["port"]);
                }
                con.Close();
            }
        }
    }

 

You are viewing reply posted by: pandeyism 5 months ago.
Posted 5 months ago

Hi nauna,

Refer below sample.

HTML

<asp:Label ID="lblName" runat="server" />

Namespaces

C#

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

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

CS.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
    ConnectionClass connection = new ConnectionClass();
    string name = "";
    string query = "get_CustomersData";
    SqlCommand cmd = connection.getCommand(query);
    cmd.Parameters.AddWithValue("@CustomerId", 1);
    cmd.Connection.Open();
    SqlDataReader dr = cmd.ExecuteReader();
    if (dr.Read())
    {
        name = dr["Name"].ToString();
    }
    cmd.Connection.Close();
    lblName.Text = "The Name is : <b>" + name + "</b>";
}

ConnectionClass.cs

public class ConnectionClass
{
    public SqlCommand getCommand(string query)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.CommandType = CommandType.StoredProcedure;
        return cmd;
    }
}

VB.Net

VB.aspx.vb

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim connection As ConnectionClassVB = New ConnectionClassVB()
    Dim name As String = ""
    Dim query As String = "get_CustomersData"
    Dim cmd As SqlCommand = connection.getCommand(query)
    cmd.Parameters.AddWithValue("@CustomerId", 1)
    cmd.Connection.Open()
    Dim dr As SqlDataReader = cmd.ExecuteReader()

    If dr.Read() Then
        name = dr("Name").ToString()
    End If

    cmd.Connection.Close()
    lblName.Text = "The Name is : <b>" & name & "</b>"
End Sub

ConnectionClassVB.vb

Public Class ConnectionClassVB
    Public Function getCommand(ByVal query As String) As SqlCommand
        Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Dim cmd As SqlCommand = New SqlCommand(query, con)
        cmd.CommandType = CommandType.StoredProcedure
        Return cmd
    End Function
End Class

Output

The Name is : John Hammond