OrderSystem/Database.cs at main · Wesley273/OrderSystem · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem
{
    class DataBase
    {
        private SqlConnection connection;  //创建连接对象
        #region   打开数据库连接
        private void Open()
        {
            if (connection == null)
            {
                connection = new SqlConnection("Server=localhost;Trusted_Connection=SSPI;database=order_system");
            }
            if (connection.State == ConnectionState.Closed)
                connection.Open();
        }
        #endregion

        #region  关闭连接
        public void Close()
        {
            if (connection != null)
                connection.Close();
        }
        #endregion

        #region 释放数据库连接资源
        public void Dispose()
        {
            if (connection != null)
            {
                connection.Dispose();
                connection = null;
            }
        }
        #endregion

        #region   传入参数并且转换为SqlParameter类型
        public SqlParameter GetSqlParameter(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            SqlParameter parameter;
            if (Size > 0)
                parameter = new SqlParameter(ParamName, DbType, Size);
            else
                parameter = new SqlParameter(ParamName, DbType);
            if (!(Value == null))
                parameter.Value = Value;
            return parameter;
        }
        #endregion

        #region   执行参数命令文本(无数据库中数据返回)
        public void RunQuery(string query, SqlParameter[] parameter)
        {
            SqlCommand command = CreateCommand(query, parameter);
            command.ExecuteNonQuery();
            this.Close();
        }

        #endregion

        #region   执行参数命令文本(有返回值)
        public DataSet GetViews(string query, SqlParameter[] parameter, string table)
        {
            SqlDataAdapter adapter = CreateDataAdaper(query, parameter);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, table);
            Close();
            return dataSet;
        }
        public DataSet GetViews(string query, string table)
        {
            SqlDataAdapter adapter = CreateDataAdaper(query, null);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, table);
            this.Close();
            return dataSet;
        }

        #endregion

        #region 将命令文本添加到SqlDataAdapter
        private SqlDataAdapter CreateDataAdaper(string query, SqlParameter[] parameter)
        {
            this.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            adapter.SelectCommand.CommandType = CommandType.Text;
            if (parameter != null)
            {
                foreach (SqlParameter param in parameter)
                    adapter.SelectCommand.Parameters.Add(param);
            }
            adapter.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return adapter;
        }
        #endregion

        #region   将命令文本添加到SqlCommand
        private SqlCommand CreateCommand(string query, SqlParameter[] parameter)
        {
            this.Open();
            SqlCommand command = new SqlCommand(query, connection);
            command.CommandType = CommandType.Text;
            if (parameter != null)
            {
                foreach (SqlParameter param in parameter)
                    command.Parameters.Add(param);
            }
            return command;
        }
        #endregion
    }
}