|
I use MySql in combination with C# as well. To answer the question from Cristian why using MySql instead of SQLServer: to get the best of both worlds, and have a rich user interface with a windows forms application, combined with a database that can be reached over the internet.
First of all, use the MySQL .net components provided by MySql, and create a reference to it in your projects. Don't forget the using statement:
using MySql.Data.MySqlClient;
I then use a number of standard functions like:
public static bool RunSQL(string sql, string ConnectString)
{
MySqlConnection ConnMySQL;
MySqlCommand cmdMySql = new MySqlCommand();
ConnMySQL = new MySqlConnection(ConnectString);
cmdMySql.Connection = ConnMySQL;
try
{
ConnMySQL.Open();
//Execute SQL
cmdMySql.CommandText = sql;
cmdMySql.ExecuteNonQuery();
}
catch (Exception e)
{
System.Windows.Forms.Clipboard.SetText(sql);
if (e.Message.ToString().IndexOf("Duplicate entry") < 0)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
return false;
}
finally
{
ConnMySQL.Close();
}
return true;
}
The connectstring is something like:
public static string ConnectString()
{
string Connectstring = @"server=yourservername;uid=yourusername;pwd=yourpassword;database=yourdatabasename;";
return Connectstring;
}
I hope this will get you on the right track
|
|
|
|
|
Hey i am now going to use Mysql to connect instead of ole db as i have been told that it is quicker...
How do i actually incoporate the mysql connection example into my program?
Do i need to put this code in every time that i need to connect to the database? or do i just put it at the top of the code?
Also how do i get the mysql.data.mysqlclient import as when i put it in my code i get the error:
"The type or namespace 'MySql' could not be found (are you missing a using directive or an assembly reference?"
Do i need to download the dll? and if so in which folder too?
|
|
|
|
|
If you want to create more applications connected to a MySQL database in the future, it is best to create a separate project in your current solution.
Download the dll from MySQL, the folder is not important. In the new project rightclick on references and choose "add new reference". Browse to the downloaded dll and create the reference.
Create a new class in your new MySQL project and make the functions you need.
In your original project add a reference to your new MySQL project, and you can use the functions in your project.
This sounds easy but will still be a lot of work, if you need further help please state your level of experience with MySQL and SQL.
Regards,
|
|
|
|
|
Hey i have managed to get the mysql reference working and can now use the mysql.dll...
I have tried to alter my code using somebody elses code as a basis... My problem at the moment is that i get the error messages : "The name textBOx3 does not exist in the current context" and also "The name MySqlConn does not exist in the current context" Why is this suddenly occuring?
I am still unsure how to go about authorising the login e.eg opening up screen 2 when the username and password are found in the database?
(I'm very new to c# and programming in general)
Thanks in advance,
C~Rose
Code Below:
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.OleDb;
using MySql.Data.MySqlClient;
namespace _Consultants
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void textBox2_TextChanged(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
string uname;
string pword;
try
{
uname = textBox1.Text;
pword = textBox2.Text;
{
string connectionString = "Data Source=localhost" + ";Database=_consultants" + ";User ID=root" + ";Password=password;";
MySqlConnection MySqlConn = new MySqlConnection(connectionString);
string strSQL = ("SELECT (*) FROM user where username = ' " + textBox1.Text + " 'and password=' " + textBox2.Text + " ' ");
MySqlCommand mysqlCommand = new MySqlCommand(strSQL, MySqlConn);
MySqlConn.Open();
String strResult = String.Empty;
strResult = (String)mysqlCommand.ExecuteScalar();
MySqlConn.Close();
if (strResult.Length == 0)
{
textBoxt3.Text = "INCORRECT USER/PASS!";
//could redirect to register page
}
else
{
textBox3.Text = "YOU ARE LOGGED IN!";
//set loggin in sessions variables
}
}
}
catch (Exception ex)
{
MessageBox.Show("Wrong Input Entered", "Window Information" + ex.Message.ToString(),
MessageBoxButtons.OK, MessageBoxIcon.Information);
MySqlConn.Close();
}
}
private void textBox3_TextChanged(object sender, EventArgs e)
{
}
}
}
|
|
|
|
|
The messages mean exactly what they say. C# is case sensitive which means textBox3 is something else than textBOx3.
I also found this line:
textBoxt3.Text = "INCORRECT USER/PASS!";
watch the t after textBox and before the 3!
I can't find the error with MySqlConn, but it must be the same thing. Check the line with the error.
Regards,
|
|
|
|
|
Thank you for the tip, i am learning something new about c# with every message that you send.
Back to the problem, i did stuble across another person that was trying to get the login working and he said he managed to do so in the end so i have borrowed his code and adapted it....
It seems to almost correct except for when i click on the login button it waits a while and then says "you have an error in your SQL Syntax check the ,manual that" is this to do with the connection string of the actual SQL query? I feel that if i can correct this then it should work as it is the only erro that comes up when run? As always your help will be greatly appreciated.
C~Rose
Link to borrowed code http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/764cdca8-8464-497e-ac15-daab9d748724/br mode="hold" />My Code Below:
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.OleDb;
using MySql.Data.MySqlClient;
namespace _Consultants
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void textBox2_TextChanged(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
string uname = textBox1.Text;
string pword = textBox2.Text;
string connectionString = "Data Source=localhost" + ";Database=_consultants" + ";User ID=root" + ";Password=password;";
MySqlConnection MySqlConn = new MySqlConnection(connectionString);
string strSQL = ("SELECT (*) FROM user WHERE username = ' " + textBox1.Text + " 'AND password=' " + textBox2.Text + " ' ");
MySqlCommand mysqlCommand = new MySqlCommand(strSQL, MySqlConn);
try
{
MySqlConn.Open();
MySqlDataReader mysqlReader = mysqlCommand.ExecuteReader();
while (mysqlReader.Read())
{
string strusername;
string strpassword;
strusername = (mysqlReader["username"].ToString());
strpassword = (mysqlReader["password"].ToString());
if (strusername == uname && pword == strpassword)
{
textBox3.Text = "INCORRECT USER/PASS!";
}
else
{
textBox3.Text = "YOU ARE LOGGED IN!";
}
}
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Wrong Input Entered", "Window Information" + ex.Message.ToString(),
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void textBox3_TextChanged(object sender, EventArgs e)
{
}
}
}
|
|
|
|
|
MySql isn't always very helpfull in it's error messages. When something like this occurs you can best make a messagebox with the sql.
I'm not sure about the () in Select (*), I always use Select *. Definately wrong is the absence of a space before -AND-
Your code:
textBox1.Text + " 'AND password=
Must be
textBox1.Text + " ' AND password=
It's subtle but the space is important.
Rob
|
|
|
|
|
Ok i have ironed out those errors now and when i run and click the login button the hello displays in textbox3.text so i am guessing that everything works up until that point?
But other than the hello appearing nothing else happens...it just does nothing (does not go to the exception and bring up error mesasge either).
In the lines : string strusername;
string strpassword;
strusername = (mysqlReader["username"].ToString());
strpassword = (mysqlReader["password"].ToString());
are "username" and "password" meant to be the name of the tables in my database? because that is what i have done.
My Updated code below:
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.OleDb;
using MySql.Data.MySqlClient;
namespace Deroche_Consultants
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void textBox2_TextChanged(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
string uname = textBox1.Text;
string pword = textBox2.Text;
string connectionString = "Data Source=localhost" + "; Database=deroche_consultants" + "; User ID=root" + "; Password=password;";
MySqlConnection MySqlConn = new MySqlConnection(connectionString);
string strSQL = ("SELECT * FROM user WHERE username= ' " + textBox1.Text + " ' AND password= ' " + textBox2.Text + " ' ");
MySqlCommand mysqlCommand = new MySqlCommand(strSQL, MySqlConn);
MySqlDataReader mysqlReader = null;
textBox3.Text = "hello";
try
{
MySqlConn.Open();
mysqlReader = mysqlCommand.ExecuteReader();
while (mysqlReader.Read())
{
string strusername;
string strpassword;
strusername = (mysqlReader["username"].ToString());
strpassword = (mysqlReader["password"].ToString());
if (strusername == uname && pword == strpassword)
{
main_screen mss = new main_screen();
mss.Show();
}
else
{
textBox3.Text = "YOU ARE LOGGED IN!";
}
}
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Wrong Input Entered", "Window Information" + ex.Message.ToString(),
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void textBox3_TextChanged(object sender, EventArgs e)
{
}
private void label3_Click(object sender, EventArgs e)
{
}
}
}
|
|
|
|
|
Nothing else happening is exactly what you coded, so it works. Perhaps you want to prove it by changing textbox3 to something that's from the database.
Suppose you have a fullname field in your database (and that's yes to your other question), you extend the code with:
string strfullname = (mysqlReader["fullname"].ToString());
and:
textBox3.Text = "congratulations " + strfullname + ", you are now logged in!";
The first steps are the most difficult, and you've taken them already so keep on experimenting 
Rob
|
|
|
|
|
Hey, i was refering to this section of my code:
while (mysqlReader.Read())
{
string strusername;
string strpassword;
strusername = (mysqlReader["username"].ToString());
strpassword = (mysqlReader["password"].ToString());
if (strusername == uname && pword == strpassword)
{
main_screen mss = new main_screen();
mss.Show();
}
else
{
textBox3.Text = "YOU ARE LOGGED IN!";
}
}
isn't the if statement proving that the username and password exists? (in response to; "youve programmed it to do nothing")
isn't the main screen form supposed to open if there is a match between username and password?
and the if else statement textBox3.Text is supposed to show "you are now logged in"?
Neither of these are happening which means that somewhere the code is not working.
Merry Christmas by the way and thanks again for your comments!
|
|
|
|
|
I'm sorry, I didn't look right.
I think there are no records that meet the criteria.
My books says to use a data reader for performance reasons, but I've found that a data reader can even take longer than a dataset, so I always use a data set (perhaps if I make a million records application the datareader comes into play again).
The advantage of the dataset is you can check it in debug mode.
I have a standard function:
public static DataTable dtAlgemeen(string sql, string NaamRetourTabel)
{
MySqlConnection ConnMySQL;
MySqlCommand cmdMySql = new MySqlCommand();
ConnMySQL = new MySqlConnection(ConnectString());
cmdMySql.Connection = ConnMySQL;
try
{
ConnMySQL.Open();
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = new MySqlCommand(sql, ConnMySQL);
MySqlCommandBuilder sqlbuilder = new MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, NaamRetourTabel );
DataTable dt = ds.Tables[NaamRetourTabel];
return dt;
}
catch (Exception e)
{
System.Windows.Forms.Clipboard.SetText(sql);
System.Windows.Forms.MessageBox.Show("Foutmelding:" + e.Message + Convert.ToChar(13) + e.GetType().ToString() + Convert.ToChar(13) + "SQL is niet uitgevoerd, SQL text gekopieerd naar clipboard");
return null;
}
finally
{
ConnMySQL.Close();
}
}
then I code in my main routine
DataTable dtUsers = dtAlgemeen("Select * from users", "users");
If you put a break on the next line you can see the actual data extracted from the database by clicking on the magnifying glass that appears when you hold the mouse pointer over the dtUsers word.
If the data is satisfactory, you can adjust your code to select only the record with the necessary data
string sql = "Select * from users where username = '" + this.TextBox1.Text + "' And password = '" + this.TextBox2.Text + "'";
dtUsers = dtAlgemeen(sql, "users");
if (dtUsers.Rows.Count ==0)
{
MessageBox.Show("Username does not exist, or Password invalid");
}
else
{
MessageBox.Show("You are now logged in");
//do your other things
}
Use the debugger to check your code line by line to see if the expected result occurs!
Rob
|
|
|
|
|
The documentation of ReaderWriterLockSlim.EnterUpgradeableReadLock says:
"A thread in upgradeable mode can downgrade to read mode or upgrade to write mode."
How do I downgrade the lock to a read lock? The documentation does't tell...
|
|
|
|
|
To upgrade to write mode call EnterWriteLock and to downgrade, call ExitWriteLock
|
|
|
|
|
Thanks, but I meant to downgrade from UpgradeableReadLock to simple ReadLock. I don't need WriteLock. The rationale is that since only one thread can acquire UpgradeableReadMode, I want to downgrade it when I know it's not needed so that another thread can acquire it.
|
|
|
|
|
Well, the below code will answer it
class Program
{
static ReaderWriterLockSlim rwSlim = new ReaderWriterLockSlim(LockRecursionPolicy.SupportsRecursion);
static void Main(string[] args) {
rwSlim.EnterUpgradeableReadLock();
Console.WriteLine(rwSlim.IsUpgradeableReadLockHeld);
rwSlim.EnterReadLock();
Console.WriteLine(rwSlim.IsReadLockHeld);
rwSlim.ExitUpgradeableReadLock();
Console.WriteLine(rwSlim.IsUpgradeableReadLockHeld);
rwSlim.ExitReadLock();
Console.WriteLine(rwSlim.IsReadLockHeld);
}
}You need to supply LockRecursionPolicy.SupportsRecursion to indicate that you need recursive locks.
|
|
|
|
|
Hi,
Am working in Asp.net using C#.I have 4 textboxes and 1 button in my application.After the user enters values in 4 textboxes he will click the button.On button click the values in textbox should be written in XML format....Usually to insert values in database we will use INSERT query..But instead of saving in Database i should write the values in textbox directly to XML.
S it possible to write the values in Textbox directly to xml???
Can u help me in doing this....Thanx in advance..
Regards,
Samson
|
|
|
|
|
Yes, that is possible. Try this little horrorcode;
string asXML = @"<codinghorrorxml>" + Environment.NewLine;
asXML += @"<textbox1>" + textBox1.Text + @"</textbox1>" + Environment.NewLine;
asXML += @"<textbox2>" + textBox2.Text + @"</textbox2>" + Environment.NewLine;
asXML += @"<textbox3>" + textBox3.Text + @"</textbox3>" + Environment.NewLine;
asXML += @"</codinghorrorxml>";
You'll also need to write this string to disk, I presume. There's a StreamWriter somewhere in System.IO.
I are troll 
|
|
|
|
|
hi,
I don know how to use streamwriter and save the xml...
Please give me some idea over there...
Thanks in advance
Regards,
Samson
|
|
|
|
|
There's an example here.
Good luck
I are troll
|
|
|
|
|
Hi,
I created the code and run it but it's not creating a new xml file... I pasted my code her... please let me know what to correct it
string asXML = @"" + Environment.NewLine;
asXML += @"" + txtid.Text + @"" + Environment.NewLine;
asXML += @"" + txtname.Text + @"" + Environment.NewLine;
asXML += @"" + txtage.Text + @"" + Environment.NewLine;
asXML += @"";
FileStream test= new FileStream("test.xml", FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter sw = new StreamWriter(test);
sw.Write("Hello file system world!");
sw.Close();
test.Close();
It's not creating the test.xml file... Please correct it...
Thanks in advance
Regards,
Samson
|
|
|
|
|
Here i had change the code as
sw.write(asXML);
i got the xml file,but it's not writing the value from textbox.
I'm Getting the folloeing error.
XML document must have a top level element. Error processing resource 'file:///C:/Documents and Settings/sbritto/Desktop/test.xml
Please correct it...
Thanks in advance
With Regards,
Samson
|
|
|
|
|
you're not writing XML. Stop flailing about in the dark, abandon the path of ignorance, and actually do some work to establish that the code you enter stands some semblance of a chance of working.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
samsonx wrote: Please correct it...
Well, I'd recommend you do some reading on XML, and possibly C#. You either are hoping for some sort of magic to happen that has nothing to do with any code that you've written, or you plain have no idea. You've not written any nodes here, this would only work if you magically happened to type the right values into your textboxes to create XML. Either way, as the original guy said, this is horrible code. XmlTextWriter is the class you should be using. I've confirmed that there's tons of info and examples on the web, easily found with your search engine of choice.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
Christian Graus wrote: Either way, as the original guy said, this is horrible code.
I'm having fun here 
I are troll
|
|
|
|
|
is it possible to have a click event that takes three paramenter i:e
private void MyButton_Click(object sender, EventArgs e,int xyz)
{
}
if yes then how?
thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
