Solved

How to save code for data access in a central place

Posted on 2014-10-15
6
232 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Exception in Log4Net 1 19
Data Saving 5 36
Enable copy and pasting even when textbox is disabled 2 16
Getfiles in vb.net 28 22
There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
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 goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now