How to save code for data access in a central place

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
adamtraskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anarki_jimbelSenior DeveloperCommented:
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
CHARU TCommented:
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
anarki_jimbelSenior DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

CHARU TCommented:
Connectonstrings should e stored in config file or in strings in the Resources. Check it out.
0
adamtraskAuthor Commented:
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
adamtraskAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.