ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决c# - Proper way of getting a data from an Access Database

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

I'm a bit confused of how to get a data from an access database. Is it proper to gather it first in a List then get those data from your List OR it is okay to just directly get it in you database ?

My codes work perfectly fine, but I wanna know if there is a better way to do this?? :

 private void button3_Click(object sender, EventArgs e)
    {
        OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\redgabanan\Desktop\Gabanan_Red_dbaseCon\Red_Database.accdb");
        connection.Open();
        OleDbDataReader reader = null;
        OleDbCommand command = new OleDbCommand("SELECT * from  Users WHERE LastName='"+textBox8.Text+"'", connection);
        reader = command.ExecuteReader();
        listBox1.Items.Clear();

        while (reader.Read())
        {

            listBox1.Items.Add(reader[1].ToString()+","+reader[2].ToString());
        }

        connection.Close();

*I'm getting my records directly from a database then display it in a listbox.

c# winforms
asked Mar 1 '13 at 0:54 Red Gabanan 81 1 1 8      Brace yourself for unhandled exception. –  kiran Feb 27 '14 at 7:14

 | 

4 Answers
4

解决方法

One thing that is sticking out like a sore thumb is the SQLInjection and to use Parameterised queries, eg:

OleDbCommand command = new OleDbCommand("SELECT * from  Users WHERE LastName='@1'", connection);

command.Parameters.AddWithValue("@1", textBox8.Text)

What your doing is perfectly acceptable, although you would generally be better off to use a SQL Database.

Edit: Here is how you seperate your business logic from the GUI:

Class BusLogic
{
 public List<string> ListboxItems = new List<string>();
 public void PopulateListBoxItems(string userName)
 {
  string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\redgabanan\Desktop\Gabanan_Red_dbaseCon\Red_Database.accdb";
  using (OleDbConnection connection = new OleDbConnection(connString))
  {
        connection.Open();
        OleDbDataReader reader = null;
        OleDbCommand command = new OleDbCommand("SELECT * from  Users WHERE LastName='@1'", connection);            
        command.Parameters.AddWithValue("@1", userName)
        reader = command.ExecuteReader();    
        while (reader.Read())
        {
            ListboxItems.Add(reader[1].ToString()+","+reader[2].ToString());
        }    
   }
 }    
}

GUI

private void button3_Click(object sender, EventArgs e)
{        
      var busLogic = new BusLogic();
      busLogic.PopulateListBoxItems(textBox8.Text);          
      \\listBox1.Items.Clear();
      ListboxItems.DataSource = busLogic.ListboxItems;
}

edited Jan 28 '15 at 12:18 user1 5,021 2 27 69 answered Mar 1 '13 at 1:02 Jeremy Thompson 29.1k 9 75 130      right, that is very vulnerable to SQL injection, better use parameters AddwithValue :) –  Pyromancer Mar 1 '13 at 1:05 2   Thanks. That's all i wanna know that my codes are fine. I used Parametised queries now. –  Red Gabanan Mar 1 '13 at 1:13      If your not having any errors, that's fine :) –  Pyromancer Mar 1 '13 at 1:15      Adding some exception handling mechanism will be good.As there is no guarantee that there will be a access database with hard coded table name with read access permission. –  kiran Feb 27 '14 at 7:12

 | 

I would say the answer is "yes" to both.

What you're doing now is perfectly acceptable for simple cases. Just be aware that it doesn't "scale" very well. That is, loading 10 or 20 items is fine. But what happens if it becomes 10 thousand or a million?

In that case you want to look at using a Model-View-Controller (MVC) architecture. That's a topic in itself, but basically you decouple the listbox (the "view") from the data (the "model").

See this site for a C#-centric MVC discussion

In between what you're doing now and a full-blown MVC architecture, you may simply want to do as you suggest - load the list first then add them to the list box. That gains you nothing if you just load it once, but if the list is loaded "all over the place", you can save the database IO overhead each time by just accessing it once.

The fact that you thought to ask the question indicates you're on the right track.


answered Mar 1 '13 at 1:05 Mark Stevens 2,048 6 7      Thanks.Appreciated much. –  Red Gabanan Mar 1 '13 at 1:18

 | 

Although your code works without any problem, I suggest you to perform some exception handling as in this example, since both OleDbConnection.Open() and OleDbCommand.ExecuteReader() might throw an InvalidOperationException.

It is also common to wrap the connection with a using statement, so in the end connection.close() is called automatically, but this is just a personal preference.


answered Mar 1 '13 at 1:10 A. Rodas 12.8k 4 32 51      +1 Thanks for this. I've included code demonstrating this in my answer –  Jeremy Thompson Mar 1 '13 at 1:13      Thanks for this. –  Red Gabanan Mar 1 '13 at 1:17

 | 

You can maybe separate your data access functions in different classes or create generic functions to retrieve records.


answered Mar 1 '13 at 0:59 Gian Acuna 1,073 8 23      so you mean,my work is okay ? –  Red Gabanan Mar 1 '13 at 1:05      +1 Thanks for this. I've included code demonstrating this in my answer –  Jeremy Thompson Mar 1 '13 at 1:14

 | 


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。