12
Answers

How to run a SQL query with windows forms application ?

thiago costa

thiago costa

13y
22.5k
1
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

Answers (12)
0
thiago costa

thiago costa

NA 340 0 13y
<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...
0
Sam Hobbs

Sam Hobbs

NA 28.7k 1.3m 13y
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?).
0
thiago costa

thiago costa

NA 340 0 13y
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 ...


0
thiago costa

thiago costa

NA 340 0 13y
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 ... :'(
0
Andile Choko

Andile Choko

NA 53 47.6k 13y
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 !!!!!!!!!";

}


0
thiago costa

thiago costa

NA 340 0 13y
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 ?

0
Sam Hobbs

Sam Hobbs

NA 28.7k 1.3m 13y
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.
0
Andile Choko

Andile Choko

NA 53 47.6k 13y
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)
0
Sam Hobbs

Sam Hobbs

NA 28.7k 1.3m 13y
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.
0
thiago costa

thiago costa

NA 340 0 13y
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 ???



0
Andile Choko

Andile Choko

NA 53 47.6k 13y
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;

  
0
thiago costa

thiago costa

NA 340 0 13y
--------------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
            {
            }