C#和SQL Server2000数据库连接,查询语句总结

  using System.Data;

 using System.Data.SqlClient;

private SqlConnection myconn;

private SqlCommand mycomm;

一.单表

try
   {

    //插入记录
    this.myconn=new SqlConnection();
    this.myconn.ConnectionString= "workstation id=3BB9B810500D4AC;packet size=4096;integrated security=SSPI;data sou" + "rce=3BB9B810500D4AC;persist security info=False;initial catalog=Maintenance";
    this.myconn.Open();

    string insertString = string.Format(
     "INSERT INTO salary VALUES ('{0}','{1}','{2}',{3},{4},'{5}')"
     ,number++,workerName,time,salary,prize,details);
     
    this.mycomm=new SqlCommand();
    this.mycomm.CommandText=insertString;
    this.mycomm.Connection=this.myconn;

    this.mycomm.ExecuteNonQuery();

    MessageBox.Show(this,"员工工资的信息录入成功!","提示信息",
     MessageBoxButtons.OK,MessageBoxIcon.Information);

    this.txtSalary.Text="";
    this.txtPrize.Text="";
    this.txtDetails.Text="";
   }
   catch
   {
    MessageBox.Show(this,"员工工资的信息保存失败!","提示信息",
     MessageBoxButtons.OK,MessageBoxIcon.Information);
   }
   finally
   {
    this.myconn.Close();
   } 

//修改记录

   try
   {
    this.myconn=new SqlConnection();
    this.myconn.ConnectionString= "workstation id=3BB9B810500D4AC;packet size=4096;integrated security=SSPI;data sou" +
     "rce=3BB9B810500D4AC;persist security info=False;initial catalog=Maintenance";
    this.myconn.Open();

    string updateString="UPDATE salary SET 基本工资="+salary+",奖金="+prize+",备注='"+details+"' WHERE 员工姓名='"+workerName+"' AND 年月份='"+time+"'";
     
    this.mycomm=new SqlCommand();
    this.mycomm.CommandText=updateString;
    this.mycomm.Connection=this.myconn;

    this.mycomm.ExecuteNonQuery();

    MessageBox.Show(this,"员工工资的信息保存成功!","提示信息",
     MessageBoxButtons.OK,MessageBoxIcon.Information);

    this.btnSaveUpdate.Enabled=false;

    this.txtSalary.Text="";
    this.txtPrize.Text="";
    this.txtDetails.Text="";
   }
   catch
   {
    MessageBox.Show(this,"员工工资的信息保存失败!","提示信息",
     MessageBoxButtons.OK,MessageBoxIcon.Information);
   }
   finally
   {
    this.myconn.Close();
   }

    //删除信息
    if(result==DialogResult.OK)
    {
     try
     {
      string deleteString=String.Format("DELETE FROM worker_archives WHERE 员工姓名='{0}'",workerName);
      
      this.myConn=new SqlConnection();
      this.myConn.ConnectionString= "workstation id=3BB9B810500D4AC;packet size=4096;integrated security=SSPI;data sou" +
       "rce=3BB9B810500D4AC;persist security info=False;initial catalog=Maintenance";
      this.myConn.Open();

      myComm=new SqlCommand();
      myComm.Connection=this.myConn;
      myComm.CommandText=deleteString;
      myComm.ExecuteNonQuery();

      this.rbBoy.Checked=false;
      this.rbGirl.Checked=false;
      this.txtAge.Text="";
      this.txtAddress.Text="";
      this.txtPhone.Text="";
      this.txtMobilePhone.Text="";
      this.txtStatus.Text="";
      this.txtEmail.Text="";
     }
     finally
     {
      this.myConn.Close();
     }

 

二.多表

   //多表查询
   string cmdText="SELECT RealName,Role_Name,Role_Description FROM Staff "
    +"INNER JOIN StaffRole ON Staff.Staff_ID=StaffRole.Staff_ID "
    +"INNER JOIN Role ON Role.Role_ID=StaffRole.Role_ID "
    +"WHERE Staff.Staff_Name="
    +"'"+sUserName+"'";

   SqlConnection myConn=new SqlConnection(STRCONNECTION);
   SqlCommand myComm=new SqlCommand(cmdText,myConn);

   myConn.Open();
   SqlDataReader dr=myComm.ExecuteReader();
   string result="真实姓名为:";
   if(dr.Read())
   {
    result+=dr["RealName"].ToString();
    result+="<br>用户角色名为:";
    result+=dr["Role_Name"].ToString();
    result+="<br>角色描述为:";
    result+=dr["Role_Description"].ToString();
    result+="<br>";
   }
   else
   {
    result+="<br>用户角色为:";
    result+="<br>角色描述为:";
   }
   dr.Close();
   myConn.Close();