What is the Effective way of Accessing sql database in asp.net

Question

In asp.net webform projects we create a connection channel where ever we need to access(insert,update,delete,select) the database which is like more or less as below mentioned.

My thought about this is can we create a class which stores all the queries and the connection string.is this way is correct ? if not what is the professional way ?

public bool ValidateFunc(string findText, string findField)//checking whether the mailid or username already exits
        {
            .. .. 
            .. ..
            string connstr = ConfigurationManager.ConnectionStrings["Connectionstr"].ConnectionString;
            SqlConnection Connection = new SqlConnection(connstr);
            string seletquery = @"SELECT * FROM Registration WHERE" + findField + "  = @Findt";
            SqlCommand Command = new SqlCommand(seletquery, Connection);
            Command.Parameters.AddWithValue("@Findt", findText);
            Connection.Open();
            SqlDataReader ad = Command.ExecuteReader();
            .. ..
            .. ..    
        }


public void InsertFunc()//inserting into database 
        {
            string connstr = ConfigurationManager.ConnectionStrings["Connectionstr"].ConnectionString;
            SqlConnection Connection = new SqlConnection(connstr);
            string insertq = @"insert into Registration values (@fname,@lname,@dob,@emailid,@uname)";
            SqlCommand Command = new SqlCommand(insertq, Connection);
            Connection.Open();
            Command.ExecuteNonQuery();
            Connection.Close();

        }

Community · Accepted Answer · 2017-05-23 11:50:08Z

2

I would not bundle all DB access into a single class. As your project grows, it will quickly become unmanageable.

One way to go that is widely accepted is to use the Repository pattern.

See this StackOverflow question for more information: Repository Pattern Step by Step Explanation

This writeup on MSDN is also pretty good: http://msdn.microsoft.com/en-us/library/ff649690.aspx

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Nov 13, 2013 at 13:34

rsbarro

27.4k9 gold badges73 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ram Over a year ago

Cant we use stored procedure?

rsbarro Over a year ago

Of course. Your Repository classes can uses whatever they want to access the database. The point is that the database implementation is hidden from the rest of your application. And if you use the Repository pattern with IoC you can also easily unit test your classes that use your Repository class by mocking out your Repositories. See this MSDN article for more info: msdn.microsoft.com/en-us/library/aa973811.aspx

Ram Over a year ago

I just came across 3 layer architecture. will it be a good one?

Collectives™ on Stack Overflow

What is the Effective way of Accessing sql database in asp.net

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related