Solved

How to save code for data access in a central place

Posted on 2014-10-15
6
253 Views
Last Modified: 2014-10-19
I am a novice C# programmer and need to learn where and how to store the data access code in a central place in a windows applications, developed using Visual Studio 2012.

I know that in ASP.Net I use the configuration file to place and access reference to the code. But how does this apply to a C# Windows application?

What I have been doing for a long time is to write almost the same code or almost the same code for data access over and over:
 Something like the method below with little variations (it could be  "void" instead of data type):

public DataTable GetMyData()
        {
            SqlConnection con;
            con = new SqlConnection("Server=myServer;;Database=myDatabase;integrated security=True");
            SqlCommand comm = new SqlCommand("Select id,Name from myTable order by LastName", con);
             SqlDataAdapter dataAdapter1 = new SqlDataAdapter(comm);
            DataSet ds1 = new DataSet();
            dataAdapter1.Fill(ds1, "tableNamef");
            return ds1.Tables["tablwNamw"];
        }

Please give example if possible.

Thanks
Adam
0
Comment
Question by:adamtrask
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 40383637
You may use as well a configuration file:

http://msdn.microsoft.com/en-us/library/ms243192.aspx

In some cases I just create a custom xml file with required entries and read it. Depends on a task.
0
 

Expert Comment

by:CHARU T
ID: 40385103
You can have it in the Busines Logic layer, or a generic function that'll take the sql as string parameter. And reuse the connection object as much as possible. What is the purpose you are trying to solve here exactly?
0
 
LVL 30

Accepted Solution

by:
anarki_jimbel earned 500 total points
ID: 40385231
As I understand the main purpose is to store connection data somewhere else but not have it hard-coded.

Just a side remark: Always use try-catch in data access.
Something like below. Or make a use of the operator "using" (google it)

        public DataTable GetMyData()
        {
            SqlConnection con=null;
            DataTable dt = null;

            try
            {
                con = new SqlConnection("Server=myServer;;Database=myDatabase;integrated security=True");
                SqlCommand comm = new SqlCommand("Select id,Name from myTable order by LastName", con);
                SqlDataAdapter dataAdapter1 = new SqlDataAdapter(comm);
                DataSet ds1 = new DataSet();
                dataAdapter1.Fill(ds1, "tableNamef");
                dt = ds1.Tables["tablwNamw"];
            }
            catch (Exception ex)
            {
                //...
            }
            finally
            {
                // release any resoutrces
                if (con != null)
                {
                    con.Close();
                }

                //...
            }

            return dt;
        }

Open in new window

0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Expert Comment

by:CHARU T
ID: 40385275
Connectonstrings should e stored in config file or in strings in the Resources. Check it out.
0
 

Author Comment

by:adamtrask
ID: 40386569
Thank you both...

Yes, the main purpose is  to avoid hard coding the code. I used it when I was experimenting with ASP.Net and learned to store the connection in a configuration file which was automatically created in each project. It looks like I need to learn to create one myself in C#.

I intend to use the example referred by expert Anarki but I need to check if i can use SqlServer instead of MS.Access. This is the plan for the weekend and I will see if I can figure it out.

P.S. Thank your for the try-catch remark anarki_jimbel. Yes, I will use it.
0
 

Author Closing Comment

by:adamtrask
ID: 40390357
Thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question