update sql server data database in c#

[terdie]

I think my thread title is a bit incomplete. coz these are my questions.I need answers to these questions for the completion of my thesis. I Know this is sort of a long message but im detailing the situation for better understanding of my questions.

And because these thread is pretty long and I have many questions,I hope I dont look like a lazy of something.Im doing my best but i still need help.

Im using Visual c# 2008 pro,sql server 2005.using a xp sp3 OS with 512 RAM

lets say i have a form with a button that inserts a data in my database. when i run my program,press the button,it says that i successfully added my data.when I check my database,I cant see the data I added.
Q.1.] Why is that so?Do I need to save the database first to "commit" my changes?

With the same form,but now i have a datagridview in my form. when i click the button that adds a data in my database,i need the dataGridView to automatically reflect my changes without clicking any other control.Ive been able to display the changes of a table in a dataGridView but only after 'refilling' the dataGridView.

Q.2.a] is there a function in sql server that 'refreshes' the database instantly/automatically?meaning is it instantly/automatically displays the changes made to a table?

Q.2.b] is there a function that instantly/automatically refresh a dataGridView (or any other control) that is connected to a updated table?

In manipulating my database i used the following:
SqlConnection = for connection
SqlDataAdapter = for SELECT queries
SqlCommand = for INSERT,UPDATE,DELETE queries
DataTable = to store my result table

Q.3]Am I doing this right? or there are better way to 'manipulate' the database?Ive read some codes that uses a dataSet instead of DataTable but I dont know the difference so I decided to ask.I hope you can help.

Lastly,My program must communicate with a minimum of 2 PCs with one server.Im Thinking of the database will be on the server only and all other PCs will only connect to it.like client/server connection.Is that the best way to do the job?If it is-
Q.4]how can i connect to the database from a PC(maybe a client) to another PC(a server) using C# and Sql server 2005. what hardware do i need?

I hope you understands my problem and reply to this thread any time. coz my thesis is due 2 weeks from now so I think I will tentatively forget sleeping for 2 weeks

For your answers,any help, or if you need more detail or a source code,please reply to this post.
I truly appreciate when you reach this part of my thread coz i know that you've read it.
thank you very much in what ever help you can give me.even by simply replying "GoodLuck" will help

thanks again and Godbless!

[jatin24]

Hi,

Well the database is updated instantly as soon as something is added/modified. I dont think there is any need to refresh it from the application end. However if you are using the 'Server Explorer' from Visual Studio, or SQL Management Tools, then u will need to refresh manually to view the updated data.

What you will need to do is, as soon as something is added, you will need to reload the data again to view the updated values. Lets say you are using dataGridView. You will need to clear and load the data again so that you can see the updated values. Why dont you post your code here so it will give a better idea of what exactly you are doing and where the problem may be.

Manipulating data in the database can be done by using different ways. One of the most secure ways i would say is by using Stored Procedures. Using SqlCommand is also fine but not as safe as Stored Procedures.

Did'nt quite understand Q4. Arnt you connecting to the database already in your application? you mentioned you are using SqlConnection, SqlCommand etc..

[terdie]

thanks jatin24 for reading my thread and for the reply

attached at the bottom of my reply is some of my codes

in ur reply:

"...However if you are using the 'Server Explorer' from Visual Studio, or SQL Management Tools, then u will need to refresh manually to view the updated data..."

-->I dont quite understand that.what do you mean, if im using 'Server Explorer' or SQL Management Tools?
-------------------------------------------------------------------------------

"...you will need to reload the data again to view the updated values..."

-->thats what im doing,im just hoping that there are more "instant/easier" to do that
-------------------------------------------------------------------------------

"...Manipulating data in the database can be done by using different ways. One of the most secure ways i would say is by using Stored Procedures. Using SqlCommand is also fine but not as safe as Stored Procedures..."

-->I have some basic knowlegde in SP,but in my forms,inputs on some textboxes are needed in my queries so I decided not to use stored proc..but do you recommend using Stored procedures?

"...Did'nt quite understand Q4. Arnt you connecting to the database already in your application? you mentioned you are using SqlConnection, SqlCommand etc...."

-->yes Im already connecting to a database but later I will need to use a minimum of 2 PCs,Im thinking that my application will use only one database(that is in 1 of the PCs) and the other PCs will then connect to that 'server PC'.
but I dont know how to do that.

These are some of my codes that I think is necessary

SqlConnection konek;
SqlDataAdapter sikwelDataAdapter = new SqlDataAdapter();
public DataTable tableResult = new DataTable();
SqlCommand query;
string connString=@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Kink0821.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

//My code in connection to database
konek = new SqlConnection(connString);
konek.Open();

//one of my code to search in my database
                    tableResult = new DataTable();
                    sikwelDataAdapter = new SqlDataAdapter("select * from Temp_custTable", konek);
                    sikwelDataAdapter.Fill(tableResult);
                    dataGridView1.DataSource = tableResult;
                    tableResult.Dispose();
                    tableResult = new DataTable();
                    sikwelDataAdapter = new SqlDataAdapter("select * from Temp_cakeTable", konek);
                    sikwelDataAdapter.Fill(tableResult);
                    dataGridView2.DataSource = tableResult;
[B]//this time,i used"Dispose()" & "tableResult = new DataTable()" twice.Is that necessary?am i Doing that right?[/B]

//in one of my button click event handler..
query = new SqlCommand("DELETE from Temp_cakeTable", konek);
query.CommandType = CommandType.Text;
query.ExecuteNonQuery();
dataGridView2.Refresh();
[B]//the dataGridView is not displaying the changes I made[/B]

thats it.If you think I didnt supply enough code I can attach my whole code(if thats possible here)
I hope my questions are clear.

any of you guys can also reply in this thread.any help will be appreciated.
thanks and Godbless!

[jatin24]

The 'server explorer' and the 'SQL management tools' are just some tools that can be used to connect to the database server, and you can browse the database (tables, stored procedures etc).

I prefer stored procedures, they are much better, faster and safer. you can pass parameters to a stored procedure, you can basically use it like a function. You can do much more if you use it.. You can read more about it here

http://databases.about.com/od/sqlserver/a/storedprocedure.htm

To connect to database, you will need to use something like a Connection String.
You need to setup the database on one machine. To access this database you will have to use connection string, where you supply the name of the server, the database name, the username and password to connect and then you perform the functions you want on that machine.

Once you've done that, doesnt matter which machine you install the application to, as long as that machine is in the network and can access the machine with the database, you will be fine.

Right click on your projet on Visual Studio, goto Properties.
Add a new row, Name= 'ConnectionString' or whatever you wanna call it. Type = 'string', Scope = 'Application' and Value will be something like this:
'server=DATABASE-SERVER;database=Db_Name;Username=username;Password=password'
You can now use this connection string anywhere in your application. A sample code for accessing the database and the connection string:

SqlConnection cn = new SqlConnection(MyProject.Properties.Settings.Default.ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText="your sql statement or the stored procedure name";

// if using a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// if using an sql command
cmd.CommandType = CommandType.Text;

cn.Open();
// if you are simply adding/modifying something on the database without returning anything
cn.ExecuteNonQuery();
cn.Close();

If your query is returning some values (SELECT etc), then you will have to use SqlDataReader.

string name, address, phone;
cmd.Open();
SqlDataReader dr = cmd.Execute Reader();

while (dr.Read())
{
//assuming Name, Address and Phone are the fields of a table.
name = dr["Name"].ToString();
address = dr["Address"].ToString();
phone = dr["Phone"].ToString();
}
cn.close();

[terdie]

Hey! thanks again for reading.truly appreciate it.

now i think I understand what you mean about the server explorer and management studio. and also Im thinking of using stored proc.Like you said,it is faster and safer coz as far as I know(and learned),It is on the database side not on the application or the C# side.right?

and what you share about connecting to database is great!.some of it I know,some I dont.but obviously I havnt try all of them.wah! Its a bit confusing but Ill figure it out this weekend. then nxt week hopefully my problem is solved.

I have a question though

"...Right click on your projet on Visual Studio, goto Properties.
Add a new row, Name= 'ConnectionString' or whatever you wanna call it. Type = 'string', Scope = 'Application' and Value will be something like this:
'server=DATABASE-SERVER;database=Db_Name;Username=username;Password=password'
You can now use this connection string anywhere in your application...."

my project can be seen on the solution explorer right?I right clicked my project,goto properties but there no part there that I can add a new row.
and for the server name,is it the ".\SQLEXPRESS" part of my connection string:"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Kink0821.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"?

P.S.: Ive found out that you also reply to my other post. Thanks!
Ill first try what you said there then see if it will solve my problem.

Thanks again and Godbless!

[jatin24]

No worries mate! we're all here to help each other out ;) !!!

Regarding your question, i think i missed out mentioning one thing!! my bad!!
Ok when you right click and select properties, you will see different kinds of settings on the left. Application, Build, Build Events etc... goto 'Settings'. Simply add that connection string in the first row and save it.

[mangesh_dhole12]

SqlConnection con = new SqlConnection("server=.;database=Emp;uid=sa;pwd=sa");
        con.Open();
        SqlCommand cmd = new SqlCommand("Update emp set=Emp_Id where Emp_Name,Emp_Address,Emp_Sal", con);
        //cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter ("@name",SqlDbType.NVarChar,50));
        cmd.Parameters["@name"].Value =TextBox2.Text;

        cmd.Parameters.Add(new SqlParameter("@add", SqlDbType.NVarChar,50));
        cmd.Parameters["@add"].Value = TextBox3.Text;

        cmd.Parameters.Add(new SqlParameter("@sal", SqlDbType.Money,8));
        cmd.Parameters["@sal"].Value = TextBox4.Text;
        cmd.ExecuteNonQuery();
        Response.Write("Record Updated");
        con.Close();