Thread: Database Access

  1. #1
    Registered User
    Join Date
    Nov 2011
    Posts
    15

    Database Access

    I am new to C# and to Visual Studio 2011.

    I am trying to learn C# and many things, and for example I am trying to make a login form, which on login attempt will make a connections to a database and then verify the user and password in order to access the remainder of the program. Here are my questions:

    1- Because I have multiple form, should I have some sort of main class opening database connection once and leave it open until the end, or should it be open and closed on each form on event time.

    2- I am simply learning, and I am using Access as my database. I am wondering how to connect to it, using 2011 access, hard to understand clearly what MSDN is trying to say, they don't have much tutorial on beta.

    Thanks in advance

  2. #2
    Android geek@02's Avatar
    Join Date
    Mar 2004
    Location
    Kurunegala Colony, Sri Lanka, Sri Lanka
    Posts
    470
    Here's how i connect to the Access database in my program:

    Code:
    ....
    OleDbConnection m_cnADONetConnection = new OleDbConnection(); // Create new connection object
    public OleDbDataAdapter m_daDataAdapter;
    OleDbCommandBuilder m_cbCommandBuilder;
    public DataTable m_dtMovies = new DataTable(); // New data table to fill data using the data adapter
    public int m_rowPosition = 0;
    ....
    
    private void Form1_Load(object sender, EventArgs e)
    {
      m_cnADONetConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\PROGRAMMING\C#\Learning\Examples\MovieLib\DBExample2
      \db.accdb"; // The connection string with Provider and data source info
      m_cnADONetConnection.Open(); // Establish connection to data source
      m_daDataAdapter = new OleDbDataAdapter("Select * From Movies", m_cnADONetConnection); // A Data adapter is needed to execute queries.
      m_cbCommandBuilder = new OleDbCommandBuilder(m_daDataAdapter);
      m_daDataAdapter.Fill(m_dtMovies); // Fill table
      this.ShowCurrentRecord(); // Load data into controls
    ....
    }
    
    public void ShowCurrentRecord()
    {
    // Load the data into controls if the source is not empty
    if (m_dtMovies.Rows.Count == 0)
    {
      textBoxActor.Text = "";
      textBoxRackNo.Text = "";
      textBoxSerial.Text = "";
      textBoxTitle.Text = "";
    
       return;
    }
    textBoxActor.Text = m_dtMovies.Rows[m_rowPosition]["Stars"].ToString();
    textBoxRackNo.Text = m_dtMovies.Rows[m_rowPosition]["RackNum"].ToString();
    textBoxSerial.Text = m_dtMovies.Rows[m_rowPosition]["Serial"].ToString();
    textBoxTitle.Text = m_dtMovies.Rows[m_rowPosition]["Title"].ToString();
    textBoxGenre.Text = m_dtMovies.Rows[m_rowPosition]["Genre"].ToString();
    }
    GameJolt: https://gamejolt.com/@KasunL
    Game Development Youtube:
    https://is.gd/XyhYoP
    Amateur IT Blog: http://everything-geeky.blogspot.com/



    (and, sorry for my amateur English)

  3. #3
    Registered User
    Join Date
    Nov 2011
    Posts
    15
    Where should i post this code??? in the constructor??
    OleDbConnection m_cnADONetConnection = new OleDbConnection();// Create new connection object public OleDbDataAdapter m_daDataAdapter;
    OleDbCommandBuilder m_cbCommandBuilder;
    public DataTable m_dtMovies = new DataTable(); // New data table to fill data using the data adapter
    public int m_rowPosition = 0;

  4. #4
    Registered User
    Join Date
    Mar 2009
    Location
    england
    Posts
    209
    You might want to learn the basics of C# before worrying about things like connecting to databases. Variables prefixed by an access modifier would never be found in a constructor.

  5. #5
    Registered User
    Join Date
    Nov 2011
    Posts
    15
    Hmm yeah never mind about the constructor. Here the question I have currently I am making a login access form and the code I have his has follow:
    Code:
    // Creates a new instance of a person
                person Person;
                Person = new person(txtUser.Text,txtPass.Text,"",1);
    
    
    
    
                OleDbConnection connect = new OleDbConnection();
                connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jean\Desktop\Jessenger.accdb;User Id=admin;Password=;";
                connect.Open();
    
    
                string query = "SELECT * FROM Member WHERE userID = " + person.Username + " AND userPassword = " + person.Password;
                OleDbDataAdapter adapt = new OleDbDataAdapter(query, connect);
                OleDbCommandBuilder commandbuilder = new OleDbCommandBuilder(adapt);
                DataTable dtTable = new DataTable();
                adapt.Fill(dtTable);
         
    
    
                    /*
                    if ()
                    {
                        JessengerList ChatList = new JessengerList();
                        ChatList.Show();
                        this.Visible = false;
                        ChatList.Show();
                    }
                    */
                connect.Close();
    The only questions I really have is if its empty because user mistake or something, I am not familiar with Datable, but if its empty, I have an issues stating its empty(obviously), but why would it give me a warning. Should I try to catch an exception or something in this matter? Also i am wondering if on every form i am opening, should i open a new connection all the time ( cause they are required) or should i somehow determine globably ( i am thinking somekind of class) and wait until user ends the session??

  6. #6
    Registered User
    Join Date
    Nov 2011
    Posts
    15
    Here what I have for code;

    Code:
    private void btnLogin_Click(object sender, EventArgs e)
            {
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jean\Desktop\Jessenger.accdb;User Id=admin;Password=;";
                string queryString = "SELECT * FROM Member WHERE UserID = " + person.Username + " AND UserPassword = " + person.Password + ";";
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    OleDbCommand command = connection.CreateCommand();
                    command.CommandText = queryString;
                    try
                    {
                        
                        connection.Open();
                        OleDbDataReader reader = command.ExecuteReader();
                        
                        while (reader.Read())
                        {
                            txtUser.Text = "In";
                            //txtUser.Text = reader[0].ToString();
                            /*
                            if (reader["UserID"].ToString() == person.Username)
                            {
                                JessengerList ChatList = new JessengerList();
                                ChatList.Show();
                                this.Visible = false;
                                ChatList.Show();
                            }*/
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
    However after executeReader it seem to be freezing help on why??

  7. #7
    Registered User VirtualAce's Avatar
    Join Date
    Aug 2001
    Posts
    9,607
    Do not leave the connection to the database open and do not leave transactions open as this will place the database in a locked and potentially unusable state for other users. Usually when saving you create a database transaction in a using block and commit on success. The transaction object should handle the rest but this depends on the framework you are using as well as the version of .NET you are using.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. How to add a database from access in c++
    By m_abdelghani in forum Windows Programming
    Replies: 7
    Last Post: 03-24-2005, 08:19 AM
  2. C and database access
    By afisher in forum Linux Programming
    Replies: 4
    Last Post: 09-30-2004, 02:33 AM
  3. Mfc Dao Access Database
    By LISANANA in forum Windows Programming
    Replies: 1
    Last Post: 07-03-2003, 01:18 PM
  4. DataBase access
    By itld in forum C++ Programming
    Replies: 2
    Last Post: 12-29-2001, 08:25 PM
  5. DataBase Access
    By itld in forum Linux Programming
    Replies: 0
    Last Post: 12-29-2001, 11:40 AM