Topic:   Create common class for Database connection using C# and VB.Net in ASP.Net
Oct 26, 2020 08:07 1 Replies 458 Views ANANYA

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

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();

        }

    }

}

Prev Next
Topic Replies (1)
  1. 1
    idnkx user

    RITA

    HTML

    1

    Namespaces

    C#

    1
    2
    3
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Data;
    VB.Net

    1
    2
    Imports System.Data.SqlClient
    Imports System.Data
    Code

    C#

    CS.aspx.cs

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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 : " + name + "";
    }
    ConnectionClass.cs

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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 : " & name & ""
    End Sub
    ConnectionClassVB.vb

    1
    2
    3
    4
    5
    6
    7
    8
    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

Leave a Reply
Guest User

Not sure what course is right for you?

Choose the right course for you.
Get the help of our experts and find a course that best suits your needs.


Let`s Connect