Development tools: Microsoft Visual Studio 2005
Database: Microsoft SQL Server 2005
Description: This set up the database named demo, there is a student table student, for the convenience of operation, I only add two fields: Studentnum and Studentname.
One, SQL statements:
Copy Code code as follows:
--create Database Demo
Use Demo
CREATE TABLE Student
(
Studentnum char () primary key,
Studentname varchar () NOT NULL
)
INSERT into Student values (' 20041000010201 ', ' publicity ')
Second, code:
1. Introducing namespaces: Using System.Data.SqlClient;
2. Define connection string, connect object, Command object:
Private String ConnectionStr;
Private SqlConnection connection;
Private SqlCommand command;
3. Initialize the connection string in the constructor, connect the object, command object
(1) Initialization of the connection string:
Way ①connectionstr= "Server=localhost;uid=sa;pwd=123456;database=demo";
Way ②connectionstr= "server=127.0.0.1"; Integrade Security=sspi;database=demo ";
Where Sims is the name of the database that I want to connect to. (1) The UID and PWD are the logins and passwords of your login database
Note: This connection is connected to the local database, to connect to other machines in the LAN database, the way to ① the "server=localhost;" Instead, the IP of the server= database.
Copy Code code as follows:
Connection string: String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=product.mdb";
Establish connection: OleDbConnection connection = new OleDbConnection (connectionString);
Use the OleDbCommand class to execute the SQL statement:
OleDbCommand cmd = new OleDbCommand (sql, connection);
Connection. Open ();
Cmd. ExecuteNonQuery ();
#endregion
#region Connection string
String Strcon = @ "Provider=Microsoft.Jet.OLEDB.4.0;Data source=d:\ program book software \c# program code \access database Operation \addresslist.mdb"; Absolute path
String Strcon = @ "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" +environment.currentdirectory+ "\\addressList.mdb "; Relative path
(2) Initializing the Connection object
Connection = new SqlConnection (CONNECTIONSTR);
(3) Initialize Command object
Command =new SqlCommand ();
command. Connection =connection;
4. Manipulating data in a database
(1) Querying the data in the database
Method One:
Copy Code code as follows:
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 Two:
Copy Code code as follows:
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) Adding data to the database
Method One:
Copy Code code as follows:
String snum = TBstudentnum.Text.Trim ();
String sname = TBstudentname.Text.Trim ();
if (Snum = = "" | | sname = "")
{
MessageBox.Show ("Student study number or name cannot be empty!") "," The mistake, 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!") "," The Hint, "MessageBoxButtons.OK,
MessageBoxIcon.Information);
Connection. Close ();
}
Method Two:
Copy Code code as follows:
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!") "," The Hint, "MessageBoxButtons.OK,
MessageBoxIcon.Information);
(3) Modify the data in the database
Method One:
Copy Code code as follows:
String snum = TBstudentnum.Text.Trim ();
String sname = TBstudentname.Text.Trim ();
if (Snum = = "" | | sname = "")
{
MessageBox.Show ("Student study number or name cannot be empty!") "," The mistake, 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 is changed successfully!") "," The Hint, "MessageBoxButtons.OK,
MessageBoxIcon.Information);
Connection. Close ();
Method Two:
Copy Code code as follows:
String snum = TBstudentnum.Text.Trim ();
String sname = TBstudentname.Text.Trim ();
if (Snum = = "" | | sname = "")
{
MessageBox.Show ("Student study number or name cannot be empty!") "," The mistake, 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 revision success!") "," The Hint, "MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
(4) Delete data in the database
Method One:
Copy Code code as follows:
String snum = TBstudentnum.Text.Trim ();
if (Snum = "")
{
MessageBox.Show ("Student study number can not be empty!") "," The mistake, 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 ("This school number corresponds to the student does not exist!" "," Error ", MessageBoxButtons.OK, Messageboxicon.error);
}
Else
{
Command.commandtext = Deletestr;
Command. ExecuteNonQuery ();
MessageBox.Show ("Student's message deleted successfully!") "," The Hint, "MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
Connection. Close ();
Party II:
Copy Code code as follows:
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!") "," The Hint, "MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
Else
{
MessageBox.Show ("This school number corresponds to the student does not exist!" "," Error ", MessageBoxButtons.OK, Messageboxicon.error);
}