1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

C# Get Info From Database

Discussion in 'C, C++, C#' started by jake3340, Oct 25, 2010.

  1. jake3340

    jake3340 Jr. VIP Jr. VIP Premium Member

    Joined:
    Nov 20, 2008
    Messages:
    1,368
    Likes Received:
    414
    Location:
    Pluto
    Okay so im having a bit of a problem finding out how to this properly. I set up a database on localhost using wamp and I need to connect via visual C# on a windows form application to the database and get info from the database and set it as a variable, for example

    1) Program connects to database.
    2) Program gets the very last row on the table.
    3) program gets info from all columbs and sets the info as variable.

    How can i get this done, does anyone have any example code please cause the ones im finding on the net is ones that connect to a database created via visual c# not the one in wamp.
     
  2. pyronaut

    pyronaut Executive VIP

    Joined:
    Dec 9, 2008
    Messages:
    1,229
    Likes Received:
    1,422
    What DB are you using? MSSQL or MYSQL? WAMP only does Mysql? You can download the free express version of MSSQL from Microsoft. It pretty much has everything included, just has limits on the size of the DB's.

    You should just be able to do something like this :

    Code:
    
    SqlConnection conn = new SqlConnection(
        "Data Source=(local);Initial Catalog=dbname;Integrated Security=true"); 
    conn.open();
    
    //This assumes you have a PK named "id"
    
    SQLCommand command = new SQLCommand("SELECT TOP 1 * FROM table_Name ORDER BY id DESC", conn);
    
    SQLDataReader reader = command.ExecuteReader();
    reader.read();
    
    //Reader has an indexer. So you can just use reader[0] for column 0. Or reader["columnname"];
    
    
     
    • Thanks Thanks x 1
  3. jake3340

    jake3340 Jr. VIP Jr. VIP Premium Member

    Joined:
    Nov 20, 2008
    Messages:
    1,368
    Likes Received:
    414
    Location:
    Pluto
    MMSQL is that the microsoft one ? I have that installed but cant get the hang of it. Wamp is much easier since it comes with phpmyadmin. But that code should work as well no ? Since the mysql database is running on localhost as well...
     
  4. jake3340

    jake3340 Jr. VIP Jr. VIP Premium Member

    Joined:
    Nov 20, 2008
    Messages:
    1,368
    Likes Received:
    414
    Location:
    Pluto
    Okay I played around with it and got it to work with some code I got off this site. Now the problem is I cant really understand how the reader function works.

    Code:
      MySqlConnection conn = new MySqlConnection(
        "SERVER=localhost;" +
    				"DATABASE=forumlogin;" +
    				"UID=user;" +
    				"PASSWORD=password;");
                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                MySqlDataReader Reader;
                //This assumes you have a PK named "id"
                command.CommandText = "select * from info";
                Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                    string thisrow = "";
                    for (int i = 0; i < Reader.FieldCount; i++)
                        thisrow += Reader.GetValue(i).ToString() + ",";
                    listBox1.Items.Add(thisrow);
                }
                conn.Close();
    Now how can I just grad column 1 set it to variable 1 column 2 set it to variable 2 etc...
     
  5. pyronaut

    pyronaut Executive VIP

    Joined:
    Dec 9, 2008
    Messages:
    1,229
    Likes Received:
    1,422
    The reader is just an array of the columns. Just an object with an indexer really.

    So each time you call reader.read(); it moves down one line. The same if you were reading a file line by line.

    Let me show you with some comments :

    Code:
    
    //Code up here calling the DB. 
    
    //Ok. what this does is each time it loops. It moves the reader object one place down. 
    //The function returns true if there was more rows and it could move down
    //false if it is out of rows. 
    while(reader.read())
    {
       int number = int.parse(reader["columnname"]);
    
    //This will read the column "columnname" and stick it in the variable. my memory is foggy, I haven't done raw ADO.net in a while, but taking a column from the reader object will always return an object. And then you can parse it or tostring it from there. 
    
    //Ok end of loop. Now it will increase the reader object row by one. 
    }
    
    
     
    • Thanks Thanks x 1
  6. trooper

    trooper Regular Member

    Joined:
    Jun 5, 2009
    Messages:
    207
    Likes Received:
    210
    Location:
    Front lines
    add a try/catch/finally block to prevent open DB conn if an error occurs
    while reading

    I also added a processor directive to output the error message
    (only if you are debugging)

    Code:
    
    MySqlConnection conn = new MySqlConnection(
        "SERVER=localhost;" +
                    "DATABASE=forumlogin;" +
                    "UID=user;" +
                    "PASSWORD=password;");
                try
                {
                    conn.Open();
                    MySqlCommand command = conn.CreateCommand();
                    MySqlDataReader Reader;
                    //This assumes you have a PK named "id"
                    command.CommandText = "select * from info";
                    Reader = command.ExecuteReader();
                    while (Reader.Read())
                    {
                        string thisrow = "";
                        for (int i = 0; i < Reader.FieldCount; i++)
                            thisrow += Reader.GetValue(i).ToString() + ",";
                        listBox1.Items.Add(thisrow);
                    }
                }
                catch(Exception ex)
                {
                    #if DUBUG
                    Console.WriteLine(ex.Message);
                    #endif
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
    

    HTH!
    Trooper
     
    • Thanks Thanks x 2
  7. jake3340

    jake3340 Jr. VIP Jr. VIP Premium Member

    Joined:
    Nov 20, 2008
    Messages:
    1,368
    Likes Received:
    414
    Location:
    Pluto
    Great I sorted it all out the database works like a charm. Now I have another question maybe someone can help me. I am trying to make a simple macro that just goes to a vbulletin forum and posts a reply to my thread. I want to use the web browser which you can put inside the form since I have no idea how to use httpwebresponse thing. How would I go around clicking the log in button on vbulletin since its ID is just "submit" and on most forums theres more than one button with the id "submit" on them(it ends up clicking a different button").
     
  8. pyronaut

    pyronaut Executive VIP

    Joined:
    Dec 9, 2008
    Messages:
    1,229
    Likes Received:
    1,422
    You can either use the mshtml object, the webbrowser object has methods that will autoclick buttons (Never used it however), or you can just use webbrowser.navigate('javascript:javascriptfunctiontoclickbutton();void();');

    Last one is easiest. Just work out how to do it in JS, then navigate to it.
     
    • Thanks Thanks x 1
  9. jake3340

    jake3340 Jr. VIP Jr. VIP Premium Member

    Joined:
    Nov 20, 2008
    Messages:
    1,368
    Likes Received:
    414
    Location:
    Pluto
    Doesnt javascript use the button id as well ? Wont I have the same problem...
     
  10. mibuyt

    mibuyt Newbie

    Joined:
    Mar 10, 2010
    Messages:
    9
    Likes Received:
    0
    use using () to manage db stuff, even better .. anyway you can use mysql, but i recommend you consider microsoft .. after all c# works best with microsoft products .. i really don't like the mysql db provider, has issues!
     
  11. Chris22

    Chris22 Regular Member

    Joined:
    Sep 29, 2010
    Messages:
    400
    Likes Received:
    1,059
    You should try Linq2Sql or Entity Framework, pretty powerful tools which make dealing with a database almost too easy.
     
  12. Freelanced

    Freelanced Newbie

    Joined:
    Mar 25, 2011
    Messages:
    16
    Likes Received:
    0
    Check out the System.Data.SqlClient namespace
     
  13. gamingstill

    gamingstill Newbie

    Joined:
    Sep 19, 2011
    Messages:
    15
    Likes Received:
    1

    please mention more clearly