sqlctrl.cs

C#连接式访问数据库,最初级的数据库SQL 2005操作

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Collections;
namespace DataBase
{
    class SqlCtrl
    {

        private SqlConnection conn;
        public SqlCtrl(string server,string database,string uid,string pwd)
        { 
        
            try
            {
                string connString = "server=" + server.Trim() + ";database=" + database.Trim() + ";uid=" + uid.Trim() + ";pwd=" + pwd.Trim();
                conn = new SqlConnection(connString);
                conn.Open();

            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "失败");
            }
        }

        public void AddData(int id,string name,int age)
        {
         try
            {
                string sql = "INSERT INTO Person(pID,pName,pAge) VALUES('"+ id +"','" + name + "','" + age + "')";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
               
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message,"提示");
            }

        }

        public void DelData(int id)
        {

            try
            {
                string sql = "DELETE FROM Person WHERE pID='" +id+"'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "提示");
            }
        
        }

        public void ChangeData(string id, string name, string age)
        {
            string sql;
        try{
            if (name== "" && age != "")
            {
                sql = "UPDATE Person SET pAge='" + int.Parse(age.Trim()) + "'WHERE pID='" + int.Parse(id.Trim()) + "'";
            }
            else if (name != "" && age== "")
            {
                sql = "UPDATE Person SET pName='" + name.Trim() + "'WHERE pID='" + int.Parse(id.Trim()) + "'";
            }
            else if (name == "" && age== "")
            {
                MessageBox.Show("请输入内容", "提示");
                return;
            }
            else
            {
                sql = "UPDATE Person SET pName='" + name.Trim() + "',pAge='" + int.Parse(age.Trim()) + "'WHERE pID='" + int.Parse(id.Trim()) + "'";


            }
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "提示");
            }

        }

        public  ArrayList SelectData()
        {
            ArrayList readArray = null;
            try
            {
                readArray = new ArrayList();
                string sql = "SELECT * FROM Person";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                
                while (read.Read())
                {

                    readArray.Add(read["pID"].ToString() +" "+ read["pName"].ToString()+" " + read["pAge"].ToString());

                }

                read.Close();

            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "提示");
            }
            return readArray;
           
        }

        public void ShutData()
        {
            conn.Close();
        }
     

    
    }

}