conndata.cs

用C#与SQL2005写的一个简单的图书管理系统.主要功能:图书及学生信息的添加修改与删除 图书的借出与归还 图书信息的模糊查询.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace BooksM.MyClass
{
    class ConnData
    {
        private static SqlConnection mySqlConnect;
        public SqlConnection ConnectDB()
        {
            string myConnectionString = (@"
                                         Data Source=WWW-D17C54796EB\SQLEXPRESS;"
                                  + "Persist Security Info=False;"
                                  + "Integrated Security=SSPI;"
                                  + "Initial Catalog=BooksM ");
             mySqlConnect = new SqlConnection(myConnectionString);
            try
            {
                mySqlConnect.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
            return mySqlConnect;
        }
//        private static void OpenConnection()
//        {
//            string myConnectionString = (@"
//                                         Data Source=WWW-D17C54796EB\SQLEXPRESS;"
//                                  + "Persist Security Info=False;"
//                                  + "Integrated Security=SSPI;"
//                                  + "Initial Catalog=BooksM ");
//            mySqlConnect = new SqlConnection(myConnectionString);
//                mySqlConnect.Open();
//        }
        private static void CloseConnection()
        {
            if (mySqlConnect != null)
            {
                mySqlConnect.Close();
                mySqlConnect.Dispose();
            }
        }
      public DataSet MyDataSet(string mySQL_Str)
      {
          SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(mySQL_Str, ConnectDB());
          DataSet myDS = new DataSet();
          mySqlDataAdapter.Fill(myDS);
          return myDS;
      }
      public void ShowDataGrid(ref string sqlStr,ref DataGridView myDataGridView)
      {
          DataSet myDataSetShow = this.MyDataSet(sqlStr);
          myDataGridView.DataSource = myDataSetShow.Tables[0].DefaultView;
      }
        public SqlDataAdapter myStoredProcedureDS(string myStoredProcedure)
      {
         SqlDataAdapter da = new SqlDataAdapter();
          da.SelectCommand = new SqlCommand();
          da.SelectCommand.Connection = ConnectDB();
          da.SelectCommand.CommandText = myStoredProcedure;
          da.SelectCommand.CommandType = CommandType.StoredProcedure;
          return da;
      }
        public bool Command(string CommandText)
        {
            bool state;
            ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(CommandText, mySqlConnect);
                cmd.ExecuteNonQuery();
                state = true;
            }
            catch (Exception ex)
            {
                state = false;
                MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);                
            }
            CloseConnection();
            return state;
        }
        public  DataSet GetData(string strCm)
        {
            ConnectDB();
            DataSet ds = new DataSet();
            SqlCommand sqlCommand = new SqlCommand();
            SqlDataAdapter sqlAdapter = new SqlDataAdapter();
            try
            {
                sqlCommand.CommandText = strCm;
                sqlCommand.Connection = mySqlConnect;
                sqlAdapter.SelectCommand = sqlCommand;
                sqlAdapter.Fill(ds);
                CloseConnection();
            }
            finally
            {
                CloseConnection();

            }

            return ds;
        }

    }
}