Solved

How to save code for data access in a central place

Posted on 2014-10-15
6
249 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 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

680 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