Need help with Sqldatareader
-
Hi im new to Both C# and sql and exept from that my problem is: I´ve managed to connect to a mssql database (express edition) from my C# program and I have the sqldata reader to read my question to listboxes in my windowsForm. Ive understood from "Beginners guide to accessing SQL Server through C#" that once you got your connection running and the reader has read the table in the database You can rephrase your question without reading directly to the database. If that is correct, Is it possible to establish a connection to the sql database in "program.cs" and get your reader to read the whole table and then let different windowsForms make sql questions to that reader? Im hoping to gain less questions to the DB and its supposed to get the table every five minutes or something. I would be very happy if anyone could help me in this matter Here is an example of code in my Form:
public partial class BinMonitor : Form { private SqlConnection conn; private SqlCommand cmd; private SqlDataReader reader; string Sql; public BinMonitor() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { } // Gets the actual time and returns to a textbox private void timer1_Tick(object sender, EventArgs e) { textBox1.Text = DateTime.Now.ToLongTimeString(); } //gets values from DB once a minute private void timer2_Tick(object sender, EventArgs e) { listBox1.Items.Clear(); listBox2.Items.Clear(); listBox3.Items.Clear(); string connStr = "server=DBserver; user id=user; password=pwd; database=flights;Trusted_Connection=yes;"; SqlConnection conn = new SqlConnection(connStr); conn.Open(); reader = null; cmd = new SqlCommand("SELECT * FROM BinMonitor Where Departure !<" + "'" + textBox1.Text + "'" + "order by Departure ASC", conn); reader = cmd.ExecuteReader(); reader.Read(); while (reader.Read()) { listBox1.Items.Add(reader.GetString(0)); listBox2.Items.Add(reader.GetString(1)); listBox3.Items.Add(reader.GetString(2)); } } } }Lost in space -
Hi im new to Both C# and sql and exept from that my problem is: I´ve managed to connect to a mssql database (express edition) from my C# program and I have the sqldata reader to read my question to listboxes in my windowsForm. Ive understood from "Beginners guide to accessing SQL Server through C#" that once you got your connection running and the reader has read the table in the database You can rephrase your question without reading directly to the database. If that is correct, Is it possible to establish a connection to the sql database in "program.cs" and get your reader to read the whole table and then let different windowsForms make sql questions to that reader? Im hoping to gain less questions to the DB and its supposed to get the table every five minutes or something. I would be very happy if anyone could help me in this matter Here is an example of code in my Form:
public partial class BinMonitor : Form { private SqlConnection conn; private SqlCommand cmd; private SqlDataReader reader; string Sql; public BinMonitor() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { } // Gets the actual time and returns to a textbox private void timer1_Tick(object sender, EventArgs e) { textBox1.Text = DateTime.Now.ToLongTimeString(); } //gets values from DB once a minute private void timer2_Tick(object sender, EventArgs e) { listBox1.Items.Clear(); listBox2.Items.Clear(); listBox3.Items.Clear(); string connStr = "server=DBserver; user id=user; password=pwd; database=flights;Trusted_Connection=yes;"; SqlConnection conn = new SqlConnection(connStr); conn.Open(); reader = null; cmd = new SqlCommand("SELECT * FROM BinMonitor Where Departure !<" + "'" + textBox1.Text + "'" + "order by Departure ASC", conn); reader = cmd.ExecuteReader(); reader.Read(); while (reader.Read()) { listBox1.Items.Add(reader.GetString(0)); listBox2.Items.Add(reader.GetString(1)); listBox3.Items.Add(reader.GetString(2)); } } } }Lost in spaceI am very happy to help you, I would not know that you want to express the link MYSQL or other intention. I hope you can more simple expression of what you mean. MSN : chenqi1000y@163.com
-
I am very happy to help you, I would not know that you want to express the link MYSQL or other intention. I hope you can more simple expression of what you mean. MSN : chenqi1000y@163.com
Thank you for helping! I was hoping to get an answer if its possible to make the sqlreader work for several Forms at the same time, and if so how?
-
Hi im new to Both C# and sql and exept from that my problem is: I´ve managed to connect to a mssql database (express edition) from my C# program and I have the sqldata reader to read my question to listboxes in my windowsForm. Ive understood from "Beginners guide to accessing SQL Server through C#" that once you got your connection running and the reader has read the table in the database You can rephrase your question without reading directly to the database. If that is correct, Is it possible to establish a connection to the sql database in "program.cs" and get your reader to read the whole table and then let different windowsForms make sql questions to that reader? Im hoping to gain less questions to the DB and its supposed to get the table every five minutes or something. I would be very happy if anyone could help me in this matter Here is an example of code in my Form:
public partial class BinMonitor : Form { private SqlConnection conn; private SqlCommand cmd; private SqlDataReader reader; string Sql; public BinMonitor() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { } // Gets the actual time and returns to a textbox private void timer1_Tick(object sender, EventArgs e) { textBox1.Text = DateTime.Now.ToLongTimeString(); } //gets values from DB once a minute private void timer2_Tick(object sender, EventArgs e) { listBox1.Items.Clear(); listBox2.Items.Clear(); listBox3.Items.Clear(); string connStr = "server=DBserver; user id=user; password=pwd; database=flights;Trusted_Connection=yes;"; SqlConnection conn = new SqlConnection(connStr); conn.Open(); reader = null; cmd = new SqlCommand("SELECT * FROM BinMonitor Where Departure !<" + "'" + textBox1.Text + "'" + "order by Departure ASC", conn); reader = cmd.ExecuteReader(); reader.Read(); while (reader.Read()) { listBox1.Items.Add(reader.GetString(0)); listBox2.Items.Add(reader.GetString(1)); listBox3.Items.Add(reader.GetString(2)); } } } }Lost in spaceerikhjerpe wrote:
If that is correct, Is it possible to establish a connection to the sql database in "program.cs" and get your reader to read the whole table and then let different windowsForms make sql questions to that reader?
No. A reader is a one-time-forward-only view of the data that is being retrieved from the database. Once a row has been read from the reader you cannot go back and read it again. You may wish to look into DataAdapters in order to Fill a DataSet with the data which you can store in a central location and allow the forms to access.
erikhjerpe wrote:
Here is an example of code in my Form
It is unwise to access data dirctly from a form.
erikhjerpe wrote:
cmd = new SqlCommand("SELECT * FROM BinMonitor Where Departure !<" + "'" + textBox1.Text + "'" + "order by Departure ASC", conn); reader
This code is susceptable to SQL Injection Attacks. Please read up on SQL Injection Attacks and Tips on How to Prevent Them[^]
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website
-
erikhjerpe wrote:
If that is correct, Is it possible to establish a connection to the sql database in "program.cs" and get your reader to read the whole table and then let different windowsForms make sql questions to that reader?
No. A reader is a one-time-forward-only view of the data that is being retrieved from the database. Once a row has been read from the reader you cannot go back and read it again. You may wish to look into DataAdapters in order to Fill a DataSet with the data which you can store in a central location and allow the forms to access.
erikhjerpe wrote:
Here is an example of code in my Form
It is unwise to access data dirctly from a form.
erikhjerpe wrote:
cmd = new SqlCommand("SELECT * FROM BinMonitor Where Departure !<" + "'" + textBox1.Text + "'" + "order by Departure ASC", conn); reader
This code is susceptable to SQL Injection Attacks. Please read up on SQL Injection Attacks and Tips on How to Prevent Them[^]
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website
Thank you very much for the tips, I certainly will look into DataAdapters, have a vague ideea that thats what I´m looking for. About Injection attacks Im´not afraid since the application is supposed for a closed network and without any input from users other than to load a form. (Trying to build a system of info displays). Anyway thanks a lot for your answers
-
Thank you very much for the tips, I certainly will look into DataAdapters, have a vague ideea that thats what I´m looking for. About Injection attacks Im´not afraid since the application is supposed for a closed network and without any input from users other than to load a form. (Trying to build a system of info displays). Anyway thanks a lot for your answers
erikhjerpe wrote:
About Injection attacks Im´not afraid since the application is supposed for a closed network and without any input from users other than to load a form
Statistically most system breaches are inside jobs.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website
-
erikhjerpe wrote:
About Injection attacks Im´not afraid since the application is supposed for a closed network and without any input from users other than to load a form
Statistically most system breaches are inside jobs.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website
You´re right, at the moment I´m taking my first trembling steps in programming, and it´s probably a good Idea to learn from the start to think about security. Thanks
-
You´re right, at the moment I´m taking my first trembling steps in programming, and it´s probably a good Idea to learn from the start to think about security. Thanks
We all have to start somewhere. Just a few tips of my own and I apologize if any are repeats. Think stored procedures. That will solve the Sql injection attack problem by itself. At the very least use parameterized queries. This will solve more than just security issues. Imagine the headache of having to debug your app at 2am because somebody entered "Bob O'Malley" into a textbox? put data connections, data readers, and commands into using statements. Especially the connections. Again, you don't want to be figuring out why your server came to a screeching halt at 2am because no connections ever closed. DataSets are great, you can also use the reader to load that data into a collection of objects, close your connections, then pass the collection around anywhere you need it. I know some of this may seem advanced if you're just getting started, but remember this is where you want to be going. It's never to early to start doing it right. Oh, one more thing that I just noticed: you don't need a priming read on your datareader. This will actually cause you to throw away the first row of data. HTH.
J
Make the logo bigger