Of Stack

C operations database summary of vs2005+sql2005

  • 2020-05-07 20:14:49
  • OfStack

Development tools: Microsoft Visual Studio 2005
Database :Microsoft SQL Server 2005
Note: the database established here is Demo, there is a student table Student, for the sake of convenience, I only add two fields :studentnum and studentname.
1. SQL statement: 

 
--create database Demo 
use Demo 

create table Student 
( 
studentnum char(14) primary key, 
studentname varchar(30) not null 
) 
insert into Student values('20041000010201',' Make public ')

2. Code:
1. Introduce namespace :using System. Data. SqlClient;
2. Define connection string, connection object and command object:
private String connectionstr;
private SqlConnection connection;
private SqlCommand command;
Initializes concatenation string, concatenation object, and command object in constructor

(1) initialize the connection string:
Methods (1) connectionstr = "server = localhost; uid = sa; pwd = 123456; database = Demo ";
Way (2) connectionstr = "server = 127.0.0.1"; Integrade Security = SSPI; database = Demo ";
SIMS is the name of the database I want to connect to. uid and pwd in (1) are your login and password to the database
Note: this connection is to connect to a local database, if you want to connect to a database on another machine in the LAN, you can "server=localhost;" Change to "server= IP;" 
 
//  Connection string: String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=product.mdb"; 
//  Establish a connection: OleDbConnection connection = new OleDbConnection(connectionString); 
//  use OleDbCommand Class to perform Sql Statement:  
// OleDbCommand cmd = new OleDbCommand(sql, connection); 
// connection.Open(); 
// cmd.ExecuteNonQuery(); 
#endregion 

#region  Concatenation string  
//string strcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ Program book software \c# The program code \access Database operation \addressList.mdb"; // An absolute path  
// string strcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Environment.CurrentDirectory+"\\addressList.mdb"; // Relative paths


(2) initialize the connection object
connection = new SqlConnection(connectionstr);
(3) initialize the command object
command =new SqlCommand();
command .Connection =connection ;
4. Manipulate the data in the database
(1) query the data in the database
Method 1: 
 
string snum=tBstudentnum .Text .Trim (); 
string str = "select * from Student where studentnum='" + snum + "'"; 
command .CommandText =str; 
connection.Open(); 
if (command.ExecuteScalar() == null) 
{ 
MessageBox.Show(" The student number you entered does not exist! ", " error ", MessageBoxButtons.OK,MessageBoxIcon.Error); 
} 
else 
{ 
SqlDataReader sdr = command.ExecuteReader(); 
while (sdr.Read()) 
{ 
tBstudentnum .Text = sdr["studentnum"].ToString(); 
tBstudentname.Text = sdr["studentname"].ToString(); 
} 
sdr.Close(); 
} 
connection.Close();

Method 2: 
 
string snum=tBstudentnum .Text .Trim (); 
string str = "select * from Student where studentnum='" + snum + "'"; 
command .CommandText =str; 
connection.Open(); 
if (command.ExecuteScalar() == null) 
{ 
MessageBox.Show(" The student number you entered does not exist! ", " error ", MessageBoxButtons.OK,MessageBoxIcon.Error); 

} 
else 
{ 
SqlDataAdapter sda = new SqlDataAdapter(str,connection ); 
DataSet ds = new DataSet(); 
sda.Fill(ds, "Student"); 
DataTable dt = ds.Tables["Student"]; 
tBstudentnum.Text = dt.Rows[0]["studentnum"].ToString(); 
tBstudentname.Text = dt.Rows[0]["studentname"].ToString(); 
} 
connection.Close();

(2) add data to the database
Method 1: 
 
string snum = tBstudentnum.Text.Trim (); 
string sname = tBstudentname.Text.Trim(); 
if (snum == "" || sname == "") 
{ 
MessageBox.Show(" Student student number or name cannot be empty! ", " error ", MessageBoxButtons.OK, 
MessageBoxIcon.Error); 
} 
else 
{ 
string insertstr="insert into Student values('"+snum +"','"+sname +"')"; 
command.CommandText = insertstr; 
connection.Open(); 
command.ExecuteNonQuery(); 
MessageBox.Show(" Students add success! ", " prompt ", MessageBoxButtons.OK, 
MessageBoxIcon.Information); 
connection.Close(); 
}

Method 2: 
 
string str = "select * from Student"; 
string insertstr = "insert into Student values('" + snum + "','" + sname + "')"; 
SqlDataAdapter sda = new SqlDataAdapter(str, connection); 
DataSet ds = new DataSet(); 
sda.Fill(ds, "Student"); 
DataTable dt = ds.Tables["Student"]; 
DataRow dr = dt.NewRow(); 
dr["studentnum"] = snum; 
dr["studentname"] = sname; 
dt.Rows.Add(dr); 
sda.InsertCommand = new SqlCommand(insertstr, connection); 
sda.Update(ds, "Student"); 
MessageBox.Show(" Students add success! ", " prompt ", MessageBoxButtons.OK, 
MessageBoxIcon.Information);

(3) modify the data in the database
Method 1: 
 
string snum = tBstudentnum.Text.Trim(); 
string sname = tBstudentname.Text.Trim(); 
if (snum == "" || sname == "") 
{ 
MessageBox.Show(" Student student number or name cannot be empty! ", " error ", MessageBoxButtons.OK, 
MessageBoxIcon.Error); 
} 
else 
{ 
string modifystr = "update Student set studentname='" + sname + 
"' where studentnum='" + snum + "'"; 
command.CommandText = modifystr; 
connection.Open(); 
command.ExecuteNonQuery(); 
MessageBox.Show(" Student's name modified successfully! ", " prompt ", MessageBoxButtons.OK, 
MessageBoxIcon.Information ); 
connection.Close();

Method 2: 
 
string snum = tBstudentnum.Text.Trim(); 
string sname = tBstudentname.Text.Trim(); 
if (snum == "" || sname == "") 
{ 
MessageBox.Show(" Student student number or name cannot be empty! ", " error ", MessageBoxButtons.OK, 
MessageBoxIcon.Error); 
} 
else 
{ 
string str = "select * from Student where studentnum='" + snum + "'"; ; 
string updatestr = "update Student set studentname='" + sname + 
"' where studentnum='" + snum + "'"; 
SqlDataAdapter sda = new SqlDataAdapter(str, connection); 
DataSet ds = new DataSet(); 
sda.Fill(ds, "Student"); 
DataTable dt = ds.Tables["Student"]; 
dt.Rows[0]["studentname"] = sname; 
sda.UpdateCommand = new SqlCommand(updatestr , connection); 
sda.Update(ds, "Student"); 
MessageBox.Show(" Student name modified successfully! ", " prompt ", MessageBoxButtons.OK, 
MessageBoxIcon.Information); 
}

(4) delete the data in the database
Method 1: 
 
string snum = tBstudentnum.Text.Trim(); 
if (snum == "") 
{ 
MessageBox.Show(" Student student number cannot be empty! ", " error ", MessageBoxButtons.OK, 
MessageBoxIcon.Error); 
} 
else 
{ 
string str = "select * from Student where studentnum='" + snum + "'"; 
string deletestr = "delete from Student where studentnum='" + snum + "'"; 
command.CommandText =str ; 
connection.Open(); 
if (command.ExecuteScalar() == null) 
{ 
MessageBox.Show(" The student corresponding to this student number does not exist! ", " error ", MessageBoxButtons.OK, MessageBoxIcon.Error); 
} 
else 
{ 
command.CommandText = deletestr; 
command.ExecuteNonQuery(); 
MessageBox.Show(" Student information deleted successfully! ", " prompt ", MessageBoxButtons.OK, 
MessageBoxIcon.Information); 
} 
connection.Close();

Party 2: 
 
string str = "select * from Student where studentnum='" + snum + "'"; 
string deletestr = "delete from Student where studentnum='" + snum + "'"; 
SqlDataAdapter sda = new SqlDataAdapter(str, connection); 
DataSet ds = new DataSet(); 
sda.Fill(ds, "Student"); 
DataTable dt = ds.Tables["Student"]; 
if (dt.Rows.Count > 0) 
{ 
dt.Rows[0].Delete(); 
sda.DeleteCommand = new SqlCommand(deletestr, connection); 
sda.Update(ds, "Student"); 
MessageBox.Show(" Student information deleted successfully! ", " prompt ", MessageBoxButtons.OK, 
MessageBoxIcon.Information); 
} 
else 
{ 
MessageBox.Show(" The student corresponding to this student number does not exist! ", " error ", MessageBoxButtons.OK, MessageBoxIcon.Error); 
}

Related articles: