How to run a SQL query with windows forms application ?

Question

Hello there guys....

Sam Hobbs helped me stablish a Database connection on the previous thread http://www.c-sharpcorner.com/Forums/Thread/143677/C-Sharp-sql-connection-class.aspx..

I think now I have a database connection :D Thanks alot Sam.

Now that I have a connection, I have 3 questions... (1 is solved)

SOLVED 1)In which section of the code (in the previous thread(code provided by Sam)) Where would the "MessageBox.Show("Connected");" be??? (SOLVED)

2)Also, how would I be able to display a query in the datagrid ? .. I would like to select * from pinfo where fname =thiago

3)How would I be able to set Label1.Text = connected ?  I want to have the option between putting the code in the class, or on the main code ... but seems like I don't know how to tell the code how to find somethign in class, and class how to find something in main code... :'(

... If I put the code in the class, it says it cant find label 1, if I try to go by myConnection.state, it won't find it from the main code

then, after I acomplish this, I will need to Update the Fname, by code, and also, by typing in the datagrid. then How would I make it autosave ? how would I save by clicking on a button ? ...

 just want to be flexible..

Thanks a lot

By the way, the name of the datagrid is datagrid1... Thanks

Answer 1

<3 thanks man !!! don't be sorry.. I should be sorry for bothering..

I will take a look at that link right away !!

Thanks man ... I was having an error about not having KEY TABLE or something...

My database has a table with 3 columns, i didnt add a key column or anything...

Answer 2

I am sorry that I did not notice that this question is asking about Windows Forms applications. It really helps to state that explicitly in the question; you can put it in the subject too but people that might help might not see it in the subject.

Please read my article Database Table Update in a DataGridView without Writing Code. I think you will really learn that "there are MANY MANY ways to get it done" and some of the other ways will save you much time (muita hora?).

Answer 3

YAY !!!!!!!! Fixed  !!!!

This is how I did it :


DBC CLASS:
------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace Database2
{
    class DBC
    {  
        SqlConnection myConnection;
     
        public DBC(string pass_word, string _account)
        {
           
            try
            {        
                myConnection = new SqlConnection("user id=sa;"+"password="+pass_word+";"+"server=98.118.57.8;"+"database="+_account+";"+"connection timeout=30");
                //"Trusted_Connection=yes;" +
                          }
            catch //(Exception ex)


            {
                 MessageBox.Show("");
            }
        }


       
        public void connect()
        {  
            myConnection.Open();
        }


        // TEST SELECT ... WORKS !!!! AHAHAHAH


        public DataTable query()
        {
            DataTable table = new DataTable();
            SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from dbo.Pinfo", myConnection);
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
            dataAdapter.Fill(table);
            return table;
        }
        // TEST UPDATE !! WORKS !!!! AHAHUAHUA


        public DataTable query2()
        {
            DataTable table = new DataTable();
            SqlDataAdapter dataAdapter = new SqlDataAdapter("UPDATE Pinfo SET Fname='Student'", myConnection);
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
            dataAdapter.Fill(table);
            return table;
        }




        public void Close()
        {
            myConnection.Close();
        }


        internal string State()
       {
           return myConnection.State.ToString();
       }
           
    }


}




Form1
---------------------------------------------------------

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace Database2

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        

        DBC db;

        public void button1_Click(object sender, EventArgs e)

        {

            

           try

            {

                //_password();

                db = new DBC(TB_password.Text, TB_database.Text);

               db.connect();

                LBL_status.Text = "Connected !!!!!!!!!";

                DataTable table = db.query();

                dataGridView1.DataSource = table;

            }

            catch (Exception ex)

            {

                LBL_status.Text = ex.Message;

            }

           

        }

        private void button2_Click(object sender, EventArgs e)

        {

            DataTable table = db.query2();

            dataGridView1.DataSource = table;            

        }

        private void BTN_Q2_Click(object sender, EventArgs e)

        {

            DataTable table = db.query();

            dataGridView1.DataSource = table;

        }

        public void _password()

        {

           // DBC obj = new DBC();

         //   obj.pass_word = TB_password.Text;

        }

        private void button1_Click_1(object sender, EventArgs e)

        {

        }

       

        

      //  public void _password()

      //  {

            //DBC obj = new DBC(TB_password.Text);

           

            //obj.pass_word = TB_password.Text;            

        //}

    

     //   private void button1_Click_1(object sender, EventArgs e)

   //     {

            

           // LBL_status.Text = obj.pass_word;

       // }

    }

}

----SOLVED-----

I was able to make a string for the Password, and a string for the Account :D
Thanks you guys for all the replies...

I am aware that there are MANY MANY ways to get it done, and the code above probably isn't the best...
BUT HEY ! IT WORKED :D :D :D :D

I am very happy right now...

Once again, Thanks all ...

Answer 4

I tried this too,

When I debug, the pass_word strin = null, even though textbox has the password... 

:'( ...

I am stuck

YOu want to try project ???
The connection string should be valid with the current IP, 

here's the project http://98.118.57.8:88/Database2.rar

I don't know why it doesn't work, I have zero errors ... :'(

Answer 5

did u call the _password() method?...if not then
try this:

public void button1_Click(object sender, EventArgs e)
{

try
{
  _password();
   db = new DBC();
   db.connect();
   LBL_status.Text = "Connected !!!!!!!!!";

}

Answer 6

Hey brothers, thanks for the reply... It didn't work... I put all the code, and got ZERO errors... This is how I did everything... below is what I did with the class: I put the code you gave me, + I modified the connection string. Below is how it became.

------------------------------------------------------------

 public String pass_word { get; set; }
        public DBC()
        {
            try
            {
                myConnection = new SqlConnection("user id=sa;"+"password="+pass_word+";"+"server=98.118.57.8;"+"database=Account;"+"connection timeout=30");
                //"Trusted_Connection=yes;" +
                          }
            catch //(Exception ex)
            {
                              MessageBox.Show("");
            }

------------------------------------------------------------

Then, This is my main code:
       
        public void _password()
        {
            DBC obj = new DBC();           
            obj.pass_word = TB_password.Text;            
        }

------------------------------------------------------------

It seems like the password which I typed in the textbox, did not get transfered to the connection string "myConnection = new SqlConnection("user id=sa;"+"password="+pass_word+";"+"server=98.118.57.8;"+"database=Account;"+"connection timeout=30");"

Did I do something wrong ?

Answer 7

Andile Choko's code is good. It is using a property, so that makes it look a little strange. The part that says "{get;set;}" is probably confusing; it is the part that makes pass_word a property instead of an object.

Answer 8

try the foolowing in yo DBC class:

//make your class is public(that is, public class DBC)

//put the following line above the "public DBC()"

public String  pass_word{get;set;}


In the Form.cs :
public void _password()
{
DBC obj = new DBC ();
obj.pass_word = TextBox1.Text;

}
Please check the checkbox if it is correct(for points)

Answer 9

Please get a book about C#; your questions are answered in any good book about C# and probably even in bad books about C#. It is wasteful for people to write a lengthy explanation here when there are even better explanations in books.

Classes have membrs. For beginners, there are three types of members of classes; methods, other objects and properties. We will ignore properties also for now. A string is an object that can be a member of a class. By default, members of a class cannot be used outside the class. If you want to use a member outside a class, you just need to make the member public. That applies to both methods and to other objects in a class.

Look at my code for the DBC class; I have the line:

internal string State()

The "internal" is the same as "public". So in the Main I have:

db.State()

That calls the State method, which returns a string.

There is much more to explain and I hope you can get a book to help you. Try downloading and reading the book in Programming C# for Beginners, which is the first item listed in the "Learn Visual C# Programming" section of the Learn C# Tutorials page; look at that page for much more material.

Answer 10

Thanks for the reply brother, I was able to make it work !!!

This is my Class :  (file name DBC.cs)

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace Database2

{

    class DBC

    {   

        SqlConnection myConnection;

        public DBC()

        {

            try

            {

                myConnection = new SqlConnection("user id=sa;" +

                    "password=t12457859;server=98.118.57.8;" +

                    "database=Account; " +

                    "connection timeout=30");

                //"Trusted_Connection=yes;" +

                          }

            catch //(Exception ex)

            {

                              MessageBox.Show("");

            }

        }

       

        public void connect()

        {   

            myConnection.Open();

        }

        // TEST SELECT ... WORKS !!!! AHAHAHAH

        public DataTable query()

        {

            DataTable table = new DataTable();

            SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from dbo.Pinfo", myConnection);

            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

            dataAdapter.Fill(table);

            return table;

        }

        // TEST UPDATE !! WORKS !!!! AHAHUAHUA

        public DataTable query2()

        {

            DataTable table = new DataTable();

            SqlDataAdapter dataAdapter = new SqlDataAdapter("UPDATE Pinfo SET Fname='Student'", myConnection);

            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

            dataAdapter.Fill(table);

            return table;

        }

        public void Close()

        {

            myConnection.Close();

        }

        internal string State()

        {

            return myConnection.State.ToString();

        }

    

    }

}

This is my main code (Form1)

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace Database2

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        DBC db;

        public void button1_Click(object sender, EventArgs e)

        {

            

            try

            {

                db = new DBC();

                db.connect();

                LBL_status.Text = "Connected !!!!!!!!!";

            }

            catch (Exception ex)

            {

                LBL_status.Text = ex.Message;

            }

            DataTable table = db.query();

            dataGridView1.DataSource = table;

        }

        private void button2_Click(object sender, EventArgs e)

        {

            DataTable table = db.query2();

            dataGridView1.DataSource = table;            

        }

        private void BTN_Q2_Click(object sender, EventArgs e)

        {

            DataTable table = db.query();

            dataGridView1.DataSource = table;

        }

        public void _password()

        {

            

        

        }

    }

}

Now I am stuck !!! ... it works 100% ...
But, the next step is, How do I share a string that's inside the DBC class, in form 1 ??

This is my wish on DBC class:

string password =textBox1.Text

But, since textBox1.Text is part of my main code, my class can not have access to textBox1.Text...

How do I make my program able to do this ?... I want to make the Textbox on form one, be the password input, ... 

I want my class to recognize the password string as textBox1.Text

How do I do that ???

Answer 11

1. put it on formLoad


2. Try this:

using System.Data;

SqlConnection myConnection;

SqlCommand myCommand;

SqlAdapter adapt;

myConnection = New SqlConnection("Your connectionString");

myCommand = New SqlCommand("Your select statement", myConnection);

myConnection.Open();

myCommand.ExecuteNonQuery();

DataSet ds = new DataSet();

adapt.Fill = (ds,"pinfo");

adapt.Dispose();

GridView1.Datasource = ds;

GridView.DataBind();

//hope it helps;

  

Answer 12

--------------EDIT------------
This is how I solved question 1 ... This worked.. But I have to put this code in the DBC class.. How would I make this work by putting it in the main code (outside of DBC class)??



I Think this here might work for the CONNECTED status thing...

 if (myConnection.State == ConnectionState.Open)
            {
                MessageBox.Show("Connected");
            }
            else
            {
            }