|
i'm writing a little app that works with MySQL database. my app connects to MySQL server (for now it's a localhost) and works with it. it should do all the basic things like reading, adding, inserting, updating, deleting... i just started learning C#, and having little difficulties with it.
my app is used by 4 people, when one of them adds new data to database i want it to reflect in every client app that's running.
code that adds data to listView:
private void Form1_Load(object sender, EventArgs e)
{
string myConnectionString = "SERVER=localhost;" + "DATABASE=database;" + "UID=root;" + "PASSWORD=123123;";
MySqlConnection conn;
conn = new MySqlConnection();
conn.ConnectionString = myConnectionString;
try
{
conn.Open();
string sql = "SELECT * FROM table";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ListViewItem item = new ListViewItem("" + rdr[0]);
item.SubItems.Add("" + rdr[1]);
item.SubItems.Add("" + rdr[2]);
item.SubItems.Add("" + rdr[3]);
item.SubItems.Add("" + rdr[4]);
item.SubItems.Add("" + rdr[5]);
item.SubItems.Add("" + rdr[6]);
item.SubItems.Add("" + rdr[7]);
item.SubItems.Add("" + rdr[8]);
item.SubItems.Add("" + rdr[9]);
item.SubItems.Add("" + rdr[10]);
listView1.Items.Add(item);
}
rdr.Close();
}
catch (MySqlException ex)
so that's what happening when the form loads. probably not the best... guide me here too pls...
and that's how i add new data:
private void addButton_Click(object sender, EventArgs e)
{
string myConnectionString = "SERVER=localhost; DATABASE=database; UID=root; PASSWORD=123123;charset=utf8";
MySqlConnection conn;
conn = new MySqlConnection();
conn.ConnectionString = myConnectionString;
try
{
conn.Open();
string sql = "INSERT INTO table VALUES ('', '" + textBox1.Text + "', '" +textBox2.Text + "', '" + textBox3.Text + "', '0', '0', '0', '0', '0', '0', '0')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
so how should i let every client app know that the new data was added and update the listView? thanks
|
|
|
|
|
A couple of things to point out first.
string myConnectionString = "SERVER=localhost;" + "DATABASE=database;" + "UID=root;" + "PASSWORD=123123;";
There is no need for the string concatenation here. You can have it one string as you are doing in your button event. You should also have this string defined in one place, say a CONST at the top of your file. That way you avoid issues of changing the string in multiple location and possibly forgetting one.
string sql = "INSERT INTO table VALUES ('', '" + textBox1.Text + "', '" +textBox2.Text + "', '" + textBox3.Text + "', '0', '0', '0', '0', '0', '0', '0')";
Never, ever, ever, accept un-validated user input and place it in your sql command. You are asking for a SQL injection attack. Yes, this app is only a small local instance, but learn to do it correctly.
As for the notification. Since you are using mySql it doesn't have the capabilities that SQL Server has so you will have to poll the database occasionally to check for updates. Or develop a complex method of shared communication between the instances of the app.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
thanks mark for correcting me  about connection string, sql injection and all that... i know, i just testing my app... wanna put all the things to work, and then build it all again with everything ready. it's like a prototype app i was thinking to make some kind of "preferences page", where user would be able to set server, database, uid, and password them self, but i can't figure out how to encrypt my settings file so no sensitive information would ever be seen... any ideas?
about "mySql doesn't have the capabilities that SQL Server has" - what you mean? i will have a server and database. i choose MySQL because i worked with it before, but if there are some other BETTER methods of keeping information in database please let me know. so what's SQL server? what do i need? windows server 2010? or linux?
|
|
|
|
|
There is no such thing as Windows Server 2010. Linux? Then you are writing the app in c++ not c#. SQLCE is for small devices, such as handhelds, not for client server apps and has been depreciated. It seems you are just casting about with no thought.
Encrypting and Decrypting Configuration Sections[^]
SqlDependency Class[^]
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Mark Nischalke wrote: Linux? Then you are writing the app in c++ not c#.
Not necessarily - see Mono[^] - it may not be perfect, but it runs .NET(ish) under unix of all varieties.
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
|
|
|
|
|
True enough but it is limited in scope. Last I looked they were still working on some .net 2.0 features and had no plans for 3.5 or above
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
That's why I said ".NET(ish)" - last time I used it they had implemented some of 2.0 quite well, and some very badly or not at all. Given it's open source I guess we have to take it or leave it. I don't really think it would hurt MS to port it properly though, might even be an advantage to them in the long run. Won't happen, but a boy can dream...
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
|
|
|
|
|
I agree with Mark - except I would use a application settings file for the connection string, rather than a constant. That way, you don't have to change the software when you do go to multi-user, just the app.settigns file. You can also test your software on an off-line database while the production one is untouched.
As for the SQL Injection - Mark is spot on. Look at the MySqlCommand.AddWithValue method for details. It makes life a lot simpler than string concatenation.
ListViewItem item = new ListViewItem("" + rdr[0]);
item.SubItems.Add("" + rdr[1]);
item.SubItems.Add("" + rdr[2]);
It doesn't work because DateReader returns an object - you have to cast it to an appropriate data type. For example
ListViewItem item = new ListViewItem((string) rdr[0]);
item.SubItems.Add((int) rdr[1]);
item.SubItems.Add((float) rdr[2]);
By adding the empty string, you are forcing an implicit string conversion via the default ToString implementation. This may not be what you want in all cases...
When it comes to updates, and distributing them, I am afraid you will have to poll for them as Mark sugested. Use a timer, so you aren't looking all the time, and probably set a table with a last update column. Then you only have to read a single row in order to tell if you are up to date. Nasty, but better than reading an entire table.
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
|
|
|
|
|
thanks for reply. i forgot about casting things... i knew about it since C++... there are something similar... well know i know
about updates... tell me then what is the best to have (server, database) for my app to work?
|
|
|
|
|
i just found something called SqlCE, it's a small database, and it can hold whole database in one single file up to 4Gb in size... the .sdf file can be encrypted with 128-bit encryption for data security so that's how i could keep sensitive info (login, pwd) safe. what you think?
|
|
|
|
|
Regretably, not a good idea - SQLCe is a good DB system, but it is only single user. You will have huge problems if you try to have multiple users access it. Worse, these problems may be intermittent.
If you want multi-user access, then stick with one of the big boys (SQL, MySql)
It is unusual to store passwords, mostly you would store a hash of the password instead - MD5 is common but should not be used for new projects as it is considered "broken". Try a google for SHA-2 which is .NET supported. The reasoning for this is that it is "impossible" to reverse the hash and work out a typeable password that matches the stored hash value.
Note: "Impossible" is why MD5 is considered broken - it has been shown that you can reverse it.
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
|
|
|
|
|
i haven't said it properly... i DO have SQL Server 2008, SQLce - it's just a little DB for my app settings! nothing else, just settings. it could keep my passwords in it. that's the only reason i want to use it. but now i still have some doubts.
imagine i open some sort of properties window in my app. it has 2 text fields in it. "login" and "password". it should tell what's what in it, login can be seen untouched, but the password field will be kind of "**********". if i will keep SHA1 version of my password in my settings file (xml) then it's great, SHA1 version can't be used by anyone. but what will be sent to the server upon request? SHA1 version? well isn't it going to be wrong password? help me please to figure it out, i'm lost.
i DO want to change some settings FROM my app, but i DON'T want them (settings) to be seen anywhere outside of my app... so what to do?
i know about encrypting, but for it to be decrypted i need a password that i used to encrypt. right? well i have just one choice - is to hardcode it in my app, as a string value i guess? isn't true that anyone who knows what disassembler is can read my hardcoded password to decrypt the whole settings file? again, i'm lost 
ps: since i'm running SQL server 2008 is there any advantages i have already to update my listview?
added: exploring SqlDependency Class
modified on Monday, September 6, 2010 9:37 AM
|
|
|
|
|
Can anyone suggest .NET based open source Rule Engine?
|
|
|
|
|
This[^] is one. I have not used it though.
You can try out workflow foundation if that fits your need.
|
|
|
|
|
Hello everyone,
I have a control update problem where I am unable to update a progress bar during a loop.
I have a winform object in C# which contains a progress bar. The program interfaces with a C++ DLL through the use of delegates. I have a simple dumb loop on the C++ side (which simply calls back to update the progress bar value). The loop is as follows:
HANDLE handle = CreateProgressDialog();
for (int i = 0; i < 100000; ++i)
{
int val = i%100;
SetProgressValue(handle, val+1);
}
On the C# side, it is as follows for the progress bar update function.
public void SetProgressValue(IntPtr handle, int value)
{
pdt.SetProgressValue(value);
pdt.Refresh();
}
Now, the update does not happen frequently enough and I never see the progress bar move beyond the value of 10. The progress bar moves fast as expected but it does not update all the times (and has a strange affinity for update when the progress value is low!). When I debug and step through, everything is fine. I am guessing this is because there is plenty of time to update...
Also, I even tried calls like System.Windows.Forms.Application.DoEvents() instead of update() but with no change.
I should mention that everything is called from the same thread (the loop and the SetProgressValue() calls).
When I use something like Thread.Sleep(100) at the end of SetProgressValue() function, it behaves better. However, I was wondering if there was a better way to achieve this.
I would appreciate any help you can give me.
Thanks,
Keith
|
|
|
|
|
I suspect that your problem is caused by trying to access the progressbar from a different thread.
Take a look at this[^] for one solution.
If you search the CP articles for InvokeRequired, you will find several good articles on the subject.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hello,
Everything is accessed from the main thread. InvokeRequired is false. I will look into other options.
Thanks for your reply.
Keith
|
|
|
|
|
Here's[^] one of my articles that may be helpful.
|
|
|
|
|
This looks promising. I will look into it and see how I can change it for my needs.
Thanks!
Keith
|
|
|
|
|
hi
i have my C# program that work with sql-server 2008 Enterprise Edition
i have 40 users that connect to this database.
i dont close the connection.
how many open connection is recommended to leave ? or is it better to close connection ?
thank's in advance
|
|
|
|
|
Close them - they are a finite resource. At present it works, but suppose they add another 10 users in six moths time. Will it work then? Or will you have to drop the top-priority task you are working on then and fit long nights into retro fixing your software? I know which I would prefer.
Do it right the first time: then you hopefully won't get any nasty shocks.
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
|
|
|
|
|
|
Always dispose the connection once the work is done. In case of ODP.Net, close and Dispose. Never leave a connection open.
|
|
|
|
|
I close and open it again later -- why dispose?
|
|
|
|
|
Hello,
I wrote my own installer in C#. It's all finished, except I'm having trouble with file type association, specifically file icons. I made my installer extract .ico embedded resource from its own .exe into the installation directory and set the registry key's DefaultIcon value to the icon file's path. (e.g C:\Program Files\Moonlite Map Studio\FileIcons\gx.ico,0), however in Windows Explorer only the .txt file icon shows up, so something must obviously be wrong.
What is the normal method to do it, and would I be able to do it programmatically?
Thanks,
Theo
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
