Solved

How to save code for data access in a central place

Posted on 2014-10-15
6
245 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 29

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 29

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

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!
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

840 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