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 = (ds,"pinfo"); 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 { }

How to run a SQL query with windows forms application ?

Roei Bar

Ken Barrett