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

thiago costa · Answer

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...

Sam Hobbs · Answer

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?).

thiago costa · Answer

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 ...

thiago costa · Answer

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 ... :'(

Andile Choko · Answer

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 !!!!!!!!!; }

thiago costa · Answer

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 ?

Sam Hobbs · Answer

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.

Andile Choko · Answer

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)

Sam Hobbs · Answer

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.

thiago costa · Answer

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 ???

Andile Choko · Answer

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 = (dspinfo); adapt.Dispose(); GridView1.Datasource = ds; GridView.DataBind(); //hope it helps;

thiago costa · Answer

--------------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 { }