SQL数据库操作

本文主要提供SQL数据库操作方法，包括连接、读取、插入、更新、删除等操作，以及基本指令格式；仅供学习！

以下为程序源码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb; // <- for database methods
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
    //===================================================================
    //==================//         SQL数据库操作      //=================//
    //===================================================================
 public class SqlDbObj
    {
        public SqlConnection m_DbConnect;
        public string strServerName = "";
        public string strDBName = "";
        public string strUserName = "";
        public string strPassword = "";
        public bool bUseWindowsLogin = true;


        public string GetSelectCmd(string TableName, bool bSelectAll = true, string KeyName = "", string strNum = "50", bool bIncrease = false)
        {
            string strCmd = "";
            try
            {
                if (bSelectAll)
                    strCmd = "SELECT * FROM  " + TableName;
                else
                {
                    if (bIncrease)
                        strCmd = string.Format("SELECT TOP {0}* FROM {1}", strNum, TableName);
                    else
                        strCmd = string.Format("SELECT TOP {0}* FROM {1} order by {2} desc", strNum, TableName, KeyName);
                }
            }
            catch
            {

            }
            return strCmd;
        }

        //  public  string connectString = "Data Source=10.136.24.34\\MYSQL;initial Catalog=PCControl;User ID=sa;password=sa123456;";
        //  public static string connectString = "Server=10.136.24.34\\MYSQL;database=PCControl;Integrated Security = true;";

        /// <summary> 连接成功，返回空字符，连接失败：返回错误信息 </summary>
        public string OpenDataBase(string ServerName, string DBName, string UserName = "", string Password = "", bool UseWindowsLogin = true)
        {
            try
            {
                string connectString = "";
                if (UseWindowsLogin)
                {
                    connectString = @"Server=" + ServerName
                                  + ";Integrated Security = true"
                                  + ";database=" + DBName + ";";
                }
                else
                {
                    connectString = @"Data Source=" + ServerName
                                  + ";initial Catalog=" + DBName
                                  + ";User ID=" + UserName
                                  + ";password=" + Password + ";";
                }
                m_DbConnect = new SqlConnection(connectString);
                m_DbConnect.Open();

                strServerName = ServerName;
                strDBName = DBName;
                strUserName = UserName;
                strPassword = Password;
                bUseWindowsLogin = UseWindowsLogin;
                return "";
            }
            catch (Exception ex)
            {
                return "Error info:" + ex.Message;
            }
        }

        //关闭数据库
        public void CloseDataBase()
        {
            try
            {
                m_DbConnect.Close();
            }
            catch
            { }
        }

        public bool GetConnectState()
        {
            if (m_DbConnect == null || m_DbConnect.State == ConnectionState.Closed)
                return false;
            else
                return true;
        }

        void CheckConnect()
        {
            if (m_DbConnect == null || m_DbConnect.State == ConnectionState.Closed)
            {
                OpenDataBase(strServerName, strDBName, strUserName, strPassword, bUseWindowsLogin);
            }
        }

        /// <summary>插入指令（格式化插入） 
        /// "INSERT INTO 表名(字段1，字段2，...,字段n) VALUES(内容1，内容2，...，内容n)" 
        /// 注意 字符串要加单引号''
        /// </summary>
        public string InsertCommand(string SQLInsertString)
        {
            try
            {
                if (!GetConnectState())
                    return "Not connected";
                SqlCommand m_SqlCmd = new SqlCommand();
                m_SqlCmd.Connection = m_DbConnect;
                m_SqlCmd.CommandType = CommandType.Text;
                m_SqlCmd.CommandText = SQLInsertString;

                int response = -1;
                response = m_SqlCmd.ExecuteNonQuery();
                if (response >= 1)
                    return "";
                else
                    return "Insert failed";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }


        /// <summary>删除指令  
        /// "DELETE FROM 表名 where 字段 = " + "内容" + ""; 
        /// 注意 字符串要加单引号''
        /// </summary>
        public bool DeleteCommand(string SQLDeleteString)
        {
            try
            {
                if (!GetConnectState())
                    return false;
                SqlCommand m_SqlCmd = new SqlCommand();
                m_SqlCmd.Connection = m_DbConnect;
                m_SqlCmd.CommandType = CommandType.Text;
                m_SqlCmd.CommandText = SQLDeleteString;                
                m_SqlCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }


        /// <summary> 更新指令  
        /// "UPDATE 表名 SET 字段1 =内容1, 字段2=内容2,...,字段n=内容n WHERE 查找字段=查找内容";
        /// 注意 字符串要加单引号''
        /// </summary>
        public bool UpdataCommand(string SQLUpdateString)
        {
            try
            {
                if (!GetConnectState())
                    return false;
                SqlCommand m_SqlCmd = new SqlCommand();
                m_SqlCmd.Connection = m_DbConnect;
                m_SqlCmd.CommandType = CommandType.Text;
                m_SqlCmd.CommandText = SQLUpdateString;               
                int response = m_SqlCmd.ExecuteNonQuery();
                if (response >= 1)
                {
                    MessageBox.Show("Update successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return true;
                }
                else
                {
                    MessageBox.Show("更新失败，没有该项目");
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movieType WHERE movietype.typeID = movie.typeID";
        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.title LIKE '" + title + "%'";
        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.typeID = " + type + "";
        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.MovieYear BETWEEN " + yr1 + " AND " + yr2 + "";
        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND Previewed ='" + previewed + "'";

        /// <summary> 选择指令  
        /// "SELECT 字段1, 字段2,...,字段n FROM 表名1,表名2 WHERE 查找字段=查找内容";
        /// 注意 字符串要加单引号''
        /// </summary>
        public int SelectCommand(string SQLSelectString)
        {
            if (!GetConnectState())
                return 0;
            SqlCommand m_SqlCmd = new SqlCommand();
            m_SqlCmd.Connection = m_DbConnect;
            m_SqlCmd.CommandType = CommandType.Text;
            m_SqlCmd.CommandText = SQLSelectString;

            SqlDataReader sqlReader = m_SqlCmd.ExecuteReader();
            while (sqlReader.Read())
            {
                //if (textBox_find.Text == sqlReader["Map"].ToString())
                //{
                //    listBox1.Items.Add(Convert.ToString(sqlReader["Map"] + " ->  " + sqlReader["Program"]));
                //}
            }
            int SS = sqlReader.VisibleFieldCount;
            sqlReader.Close();

            DataSet dataSet = new DataSet();            
            SqlDataAdapter dataAdapter = new SqlDataAdapter(m_SqlCmd);
            dataAdapter.Fill(dataSet);
            return SS;
        }

        /// <summary> 选择指令,返回DataSet
        /// "SELECT 字段1, 字段2,...,字段n FROM 表名1,表名2 WHERE 查找字段=查找内容";
        /// 注意 字符串要加单引号''
        /// </summary>
        public DataSet SelectCommand_DS(string SQLSelectString, string strTableName = "DataSet1")
        {
            DataSet dataSet = new DataSet();
            try
            {
                if (!GetConnectState())
                    return null;
                SqlCommand m_SqlCmd = new SqlCommand();
                m_SqlCmd.Connection = m_DbConnect;
                m_SqlCmd.CommandType = CommandType.Text;
                m_SqlCmd.CommandText = SQLSelectString;
                if (m_SqlCmd.Connection == null)
                    return null;

                SqlDataAdapter dataAdapter = new SqlDataAdapter(m_SqlCmd);
                dataAdapter.Fill(dataSet, strTableName);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dataSet;
        }

        /// <summary> 选择指令,返回DataSet
        /// "SELECT 字段1, 字段2,...,字段n FROM 表名1,表名2 WHERE 查找字段=查找内容";
        /// 注意 字符串要加单引号''
        /// </summary>
        public DataTable SelectCommand_DT(string SQLSelectString, string strTableName = "DataTable1")
        {
            DataTable dataTable = new DataTable();
            try
            {
                if (!GetConnectState())
                    return null;
                SqlCommand m_SqlCmd = new SqlCommand();
                m_SqlCmd.Connection = m_DbConnect;
                m_SqlCmd.CommandType = CommandType.Text;
                m_SqlCmd.CommandText = SQLSelectString;
                if (m_SqlCmd.Connection == null)
                    return null;

                SqlDataAdapter dataAdapter = new SqlDataAdapter(m_SqlCmd);
                dataAdapter.Fill(dataTable);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dataTable;
        }
    }