How to run a SQL query with windows forms application ?

thiago costa

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

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

thiago costa15y ago
<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 Hobbs15y ago
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 costa15y ago
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 costa15y ago
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 Choko15y ago
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 costa15y ago
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 Hobbs15y ago
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 Choko15y ago
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 Hobbs15y ago
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 costa15y ago
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 Choko15y ago
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 costa15y ago
--------------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
{
}
0