SqlPerfomanceHandler - выполнение удаленных запросов к СУБД
Этот код я набросал для замеров производительности SQL-серверов. Особо не изголялся - взял свою существующую обертку для выполнения запросов в PostgreSQL (которая у меня существует и работает во многих проектах) и склонировал ее для MySQL и MS SQL.
Таким образом код состоит из трех совершенно одинаковых классов:
1: 'Server=127.0.0.1;Port=5432;Database=Disk;User Id=postgres;Password=12345;ConnectionLifetime=0;
2:
3: ''' <summary>
4: ''' Внешний интерфейс, обращение
5: ''' Dim RDR As Npgsql.NpgsqlDataReader = PG.ExecRDR(CMD1)
6: ''' </summary>
7: Public Class SQL_Postgres
8:
9: Dim _PG As Postgres
10: Public ReadOnly Property PG() As Postgres
11: Get
12: Return _PG
13: End Get
14: End Property
15:
16: Dim _PG_CN As Npgsql.NpgsqlConnection
17: Public ReadOnly Property PG_CN() As Npgsql.NpgsqlConnection
18: Get
19: Return _PG_CN
20: End Get
21: End Property
22:
23: Dim _ConnectionString As String
24: Public ReadOnly Property ConnectionString() As String
25: Get
26: Return _ConnectionString
27: End Get
28: End Property
29:
30: Public Sub New(ByVal ConnectionString As String)
31: _ConnectionString = ConnectionString
32: Try
33: _PG = New Postgres
34: _PG_CN = _PG.Open(_ConnectionString)
35: Catch ex As Exception
36: Throw New Exception(ex.Message)
37: End Try
38: End Sub
39:
40: Public Sub CheckConnect()
41: Try
42: If _PG_CN Is Nothing Then
43: _PG = New Postgres
44: _PG_CN = _PG.Open(_ConnectionString)
45: Else
46: If PG_CN.FullState = Data.ConnectionState.Open Then
47: 'отлично, работаем дальше
48: ElseIf PG_CN.FullState = Data.ConnectionState.Closed Then
49: _PG_CN = _PG.Open(_ConnectionString)
50: ElseIf PG_CN.FullState = Data.ConnectionState.Broken Then
51: Throw New Exception("ConnectionState=Broken")
52: ElseIf PG_CN.FullState = Data.ConnectionState.Connecting Then
53: Throw New Exception("ConnectionState=Connecting")
54: ElseIf PG_CN.FullState = Data.ConnectionState.Executing Then
55: Throw New Exception("ConnectionState=Executing")
56: ElseIf PG_CN.FullState = Data.ConnectionState.Fetching Then
57: Throw New Exception("ConnectionState=Fetching")
58: End If
59: End If
60: Catch ex As Exception
61: Throw New Exception(ex.Message)
62: End Try
63: End Sub
64:
65: Public Sub Close()
66: Try
67: If _PG IsNot Nothing Then
68: _PG.Close()
69: End If
70: Catch ex As Exception
71: 'молча
72: End Try
73: End Sub
74: End Class
75:
76:
77: ''' <summary>
78: ''' Внутренний класс
79: ''' </summary>
80: Public Class Postgres
81:
82: Dim CN1 As Npgsql.NpgsqlConnection
83: Dim CMD1 As Npgsql.NpgsqlCommand
84: Dim RDR1 As Npgsql.NpgsqlDataReader
85:
86: Public Function Open(ByVal ConnectionString As String) As Npgsql.NpgsqlConnection
87: Try
88: If CN1 Is Nothing Then
89: CN1 = New Npgsql.NpgsqlConnection(ConnectionString)
90: CN1.Open()
91: Else
92: If CN1.FullState = Data.ConnectionState.Open Then
93: 'отлично, работаем дальше
94: ElseIf CN1.FullState = Data.ConnectionState.Closed Then
95: CN1.Open()
96: ElseIf CN1.FullState = Data.ConnectionState.Broken Then
97: Throw New Exception("ConnectionState=Broken")
98: ElseIf CN1.FullState = Data.ConnectionState.Connecting Then
99: Throw New Exception("ConnectionState=Connecting")
100: ElseIf CN1.FullState = Data.ConnectionState.Executing Then
101: Throw New Exception("ConnectionState=Executing")
102: ElseIf CN1.FullState = Data.ConnectionState.Fetching Then
103: Throw New Exception("ConnectionState=Fetching")
104: End If
105: End If
106: Return CN1
107: Catch ex As Exception
108: Throw New Exception(ex.Message)
109: End Try
110: End Function
111:
112: Public Function ExecScalar(ByVal CMD As String) As Integer
113: If CMD1 Is Nothing Then
114: CMD1 = New Npgsql.NpgsqlCommand(CMD, CN1)
115: Else
116: 'команда уже есть - есть ли в ней открытый ридер
117: If RDR1 IsNot Nothing Then
118: RDR1.Close()
119: End If
120: 'теперь новая команда
121: CMD1.CommandText = CMD
122: End If
123: RDR1 = CMD1.ExecuteReader
124: If RDR1.Read Then
125: Return RDR1(0)
126: End If
127: End Function
128:
129: Public Function ExecRDR(ByVal CMD As String) As Npgsql.NpgsqlDataReader
130: If CMD1 Is Nothing Then
131: CMD1 = New Npgsql.NpgsqlCommand(CMD, CN1)
132: Else
133: 'команда уже есть - есть ли в ней открытый ридер
134: If RDR1 IsNot Nothing Then
135: RDR1.Close()
136: End If
137: 'теперь новая команда
138: CMD1.CommandText = CMD
139: End If
140: RDR1 = CMD1.ExecuteReader
141: Return RDR1
142: End Function
143:
144: Public Sub Close()
145: If CN1 IsNot Nothing Then
146: CN1.Close()
147: End If
148: End Sub
149: End Class
1: Imports Microsoft.VisualBasic
2:
3: 'Server=10.10.10.2;Port=3308;Database=criminal;User ID=criminal;;Password=12345;Max Pool Size=500;Connect Timeout=2;
4:
5: ''' <summary>
6: ''' Внешний интерфейс, обращение
7: ''' Dim RDR As MySql.Data.MySqlClient.MySqlDataReader = MySQL2.ExecRDR(CMD1)
8: ''' </summary>
9: Public Class MySQL1
10:
11: Dim _MySQL As MySQL2
12: Public ReadOnly Property MySQL() As MySQL2
13: Get
14: Return _MySQL
15: End Get
16: End Property
17:
18: Dim _MySQL_CN As MySql.Data.MySqlClient.MySqlConnection
19: Public ReadOnly Property MySQL_CN() As MySql.Data.MySqlClient.MySqlConnection
20: Get
21: Return _MySQL_CN
22: End Get
23: End Property
24:
25: Dim _ConnectionString As String
26: Public ReadOnly Property ConnectionString() As String
27: Get
28: Return _ConnectionString
29: End Get
30: End Property
31:
32: Public Sub New(ByVal ConnectionString As String)
33: _ConnectionString = ConnectionString
34: Try
35: _MySQL = New MySQL2
36: _MySQL_CN = _MySQL.Open(_ConnectionString)
37: Catch ex As Exception
38: Throw New Exception(ex.Message)
39: End Try
40: End Sub
41:
42: Public Sub CheckConnect()
43: Try
44: If _MySQL_CN Is Nothing Then
45: _MySQL = New MySQL2
46: _MySQL_CN = _MySQL.Open(_ConnectionString)
47: Else
48: If _MySQL_CN.State = Data.ConnectionState.Open Then
49: 'отлично, работаем дальше
50: ElseIf _MySQL_CN.State = Data.ConnectionState.Closed Then
51: _MySQL_CN.Open()
52: ElseIf _MySQL_CN.State = Data.ConnectionState.Broken Then
53: Throw New Exception("ConnectionState=Broken")
54: ElseIf _MySQL_CN.State = Data.ConnectionState.Connecting Then
55: Throw New Exception("ConnectionState=Connecting")
56: ElseIf _MySQL_CN.State = Data.ConnectionState.Executing Then
57: Throw New Exception("ConnectionState=Executing")
58: ElseIf _MySQL_CN.State = Data.ConnectionState.Fetching Then
59: Throw New Exception("ConnectionState=Fetching")
60: End If
61: End If
62: Catch ex As Exception
63: Throw New Exception(ex.Message)
64: End Try
65: End Sub
66:
67: Public Sub Close()
68: Try
69: If _MySQL_CN IsNot Nothing Then
70: _MySQL_CN.Close()
71: End If
72: Catch ex As Exception
73: 'молча
74: End Try
75: End Sub
76: End Class
77:
78:
79: Public Class MySQL2
80:
81: Dim CN1 As MySql.Data.MySqlClient.MySqlConnection
82: Dim CMD1 As MySql.Data.MySqlClient.MySqlCommand
83: Dim RDR1 As MySql.Data.MySqlClient.MySqlDataReader
84:
85: Public Function Open(ByVal ConnectionString As String) As MySql.Data.MySqlClient.MySqlConnection
86: Try
87: If CN1 Is Nothing Then
88: CN1 = New MySql.Data.MySqlClient.MySqlConnection(ConnectionString)
89: CN1.Open()
90: Else
91: If CN1.State = Data.ConnectionState.Open Then
92: 'отлично, работаем дальше
93: ElseIf CN1.State = Data.ConnectionState.Closed Then
94: CN1.Open()
95: ElseIf CN1.State = Data.ConnectionState.Broken Then
96: Throw New Exception("ConnectionState=Broken")
97: ElseIf CN1.State = Data.ConnectionState.Connecting Then
98: Throw New Exception("ConnectionState=Connecting")
99: ElseIf CN1.State = Data.ConnectionState.Executing Then
100: Throw New Exception("ConnectionState=Executing")
101: ElseIf CN1.State = Data.ConnectionState.Fetching Then
102: Throw New Exception("ConnectionState=Fetching")
103: End If
104: End If
105: Return CN1
106: Catch ex As Exception
107: Throw New Exception(ex.Message)
108: End Try
109: End Function
110:
111:
112: Public Function ExecRDR(ByVal CMD As String) As MySql.Data.MySqlClient.MySqlDataReader
113: If CMD1 Is Nothing Then
114: CMD1 = New MySql.Data.MySqlClient.MySqlCommand(CMD, CN1)
115: Else
116: 'команда уже есть - есть ли в ней открытый ридер
117: If RDR1 IsNot Nothing Then
118: RDR1.Close()
119: End If
120: 'теперь новая команда
121: CMD1.CommandText = CMD
122: End If
123: RDR1 = CMD1.ExecuteReader
124: Return RDR1
125: End Function
126:
127: Public Sub Close()
128: If CN1 IsNot Nothing Then
129: CN1.Close()
130: End If
131: End Sub
132: End Class
133:
1: Imports Microsoft.VisualBasic
2:
3: 'Server=10.10.10.2;Port=3308;Database=criminal;User ID=criminal;;Password=12345;Max Pool Size=500;Connect Timeout=2;
4:
5: ''' <summary>
6: ''' Внешний интерфейс, обращение
7: ''' ''' Dim RDR As MData.SqlClient.SqlDataReader = SQL2.ExecRDR(CMD1)
8: ''' </summary>
9: Public Class SQL1
10:
11: Dim _SQL As SQL2
12: Public ReadOnly Property SQL() As SQL2
13: Get
14: Return _SQL
15: End Get
16: End Property
17:
18: Dim _SQL_CN As Data.SqlClient.SqlConnection
19: Public ReadOnly Property SQL_CN() As Data.SqlClient.SqlConnection
20: Get
21: Return _SQL_CN
22: End Get
23: End Property
24:
25: Dim _ConnectionString As String
26: Public ReadOnly Property ConnectionString() As String
27: Get
28: Return _ConnectionString
29: End Get
30: End Property
31:
32: Public Sub New(ByVal ConnectionString As String)
33: _ConnectionString = ConnectionString
34: Try
35: _SQL = New SQL2
36: _SQL_CN = _SQL.Open(_ConnectionString)
37: Catch ex As Exception
38: Throw New Exception(ex.Message)
39: End Try
40: End Sub
41:
42: Public Sub CheckConnect()
43: Try
44: If _SQL_CN Is Nothing Then
45: _SQL = New SQL2
46: _SQL_CN = _SQL.Open(_ConnectionString)
47: Else
48: If _SQL_CN.State = Data.ConnectionState.Open Then
49: 'отлично, работаем дальше
50: ElseIf _SQL_CN.State = Data.ConnectionState.Closed Then
51: _SQL_CN.Open()
52: ElseIf _SQL_CN.State = Data.ConnectionState.Broken Then
53: Throw New Exception("ConnectionState=Broken")
54: ElseIf _SQL_CN.State = Data.ConnectionState.Connecting Then
55: Throw New Exception("ConnectionState=Connecting")
56: ElseIf _SQL_CN.State = Data.ConnectionState.Executing Then
57: Throw New Exception("ConnectionState=Executing")
58: ElseIf _SQL_CN.State = Data.ConnectionState.Fetching Then
59: Throw New Exception("ConnectionState=Fetching")
60: End If
61: End If
62: Catch ex As Exception
63: Throw New Exception(ex.Message)
64: End Try
65: End Sub
66:
67: Public Sub Close()
68: Try
69: If _SQL_CN IsNot Nothing Then
70: _SQL_CN.Close()
71: End If
72: Catch ex As Exception
73: 'молча
74: End Try
75: End Sub
76: End Class
77:
78:
79: Public Class SQL2
80:
81: Dim CN1 As Data.SqlClient.SqlConnection
82: Dim CMD1 As Data.SqlClient.SqlCommand
83: Dim RDR1 As Data.SqlClient.SqlDataReader
84:
85: Public Function Open(ByVal ConnectionString As String) As Data.SqlClient.SqlConnection
86: Try
87: If CN1 Is Nothing Then
88: CN1 = New Data.SqlClient.SqlConnection(ConnectionString)
89: CN1.Open()
90: Else
91: If CN1.State = Data.ConnectionState.Open Then
92: 'отлично, работаем дальше
93: ElseIf CN1.State = Data.ConnectionState.Closed Then
94: CN1.Open()
95: ElseIf CN1.State = Data.ConnectionState.Broken Then
96: Throw New Exception("ConnectionState=Broken")
97: ElseIf CN1.State = Data.ConnectionState.Connecting Then
98: Throw New Exception("ConnectionState=Connecting")
99: ElseIf CN1.State = Data.ConnectionState.Executing Then
100: Throw New Exception("ConnectionState=Executing")
101: ElseIf CN1.State = Data.ConnectionState.Fetching Then
102: Throw New Exception("ConnectionState=Fetching")
103: End If
104: End If
105: Return CN1
106: Catch ex As Exception
107: Throw New Exception(ex.Message)
108: End Try
109: End Function
110:
111:
112: Public Function ExecRDR(ByVal CMD As String) As Data.SqlClient.SqlDataReader
113: If CMD1 Is Nothing Then
114: CMD1 = New Data.SqlClient.SqlCommand(CMD, CN1)
115: Else
116: 'команда уже есть - есть ли в ней открытый ридер
117: If RDR1 IsNot Nothing Then
118: RDR1.Close()
119: End If
120: 'теперь новая команда
121: CMD1.CommandText = CMD
122: End If
123: RDR1 = CMD1.ExecuteReader
124: Return RDR1
125: End Function
126:
127: Public Sub Close()
128: If CN1 IsNot Nothing Then
129: CN1.Close()
130: End If
131: End Sub
132: End Class
133:
И трех совершенно одинаковых хандлеров:
1: <%@ WebHandler Language="VB" Class="GetFromPostgreSQL" %>
2:
3: Imports System
4: Imports System.Web
5:
6: 'http://localhost:3164/SqlPerfomanceHandler/GetFromPostgreSQL.ashx?SQL=select%20*%20from%20partyresult;
7:
8: Public Class GetFromPostgreSQL : Implements IHttpHandler, IRequiresSessionState
9:
10: Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
11: Try
12: If context.Request.QueryString("SQL") Is Nothing Then
13: context.Response.ContentType = "text/plain"
14: context.Response.Write("SQL request not found")
15: Exit Sub
16: End If
17: If context.Request.QueryString("SQL") = "" Then
18: context.Response.ContentType = "text/plain"
19: context.Response.Write("SQL request not found")
20: Exit Sub
21: End If
22: '
23: PG_Safe_Connection(context)
24: Dim DT1 As Data.DataTable = GetPostgreSQLRequest(context.Request.QueryString("SQL"))
25: '
26: Dim ResponseStream As New Text.StringBuilder
27: For i As Integer = 0 To DT1.Rows.Count - 1
28: For j As Integer = 0 To DT1.Columns.Count - 1
29: If Not IsDBNull(DT1.Rows(i)(j)) Then
30: ResponseStream.Append(DT1.Rows(i)(j))
31: End If
32: ResponseStream.Append(";")
33: Next
34: ResponseStream.Append(vbCrLf)
35: Next
36: '
37: context.Response.ContentType = "text/plain"
38: context.Response.Charset = "utf-8"
39: context.Response.Write(ResponseStream.ToString)
40: '
41: 'разгрузка базы в виде CSV-атачмента для Exel
42: 'context.Response.ContentType = "text/csv" '"attachment"
43: 'context.Response.Charset = "utf-8"
44: 'context.Response.AppendHeader("content-disposition", "attachment; filename=" & ResponseFileName)
45: 'context.Response.Write(ResponseStream.ToString)
46: '
47: Catch ex As Exception
48: context.Response.ContentType = "text/plain"
49: context.Response.Write(ex.Message)
50: End Try
51:
52:
53: End Sub
54:
55: Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
56: Get
57: Return False
58: End Get
59: End Property
60:
61: Function GetPostgreSQLRequest(ByVal SQL_CMD As String) As Data.DataTable
62: Dim RET1 As New Data.DataTable
63: Dim RDR1 As Npgsql.NpgsqlDataReader = PG1.PG.ExecRDR(SQL_CMD)
64: If RDR1.HasRows Then
65: RET1.Load(RDR1)
66: RDR1.Close()
67: End If
68: Return RET1
69: End Function
70:
71:
72: Dim PG1 As SQL_Postgres
73:
74: Sub PG_Safe_Connection(ByVal context As HttpContext)
75: If context.Session("PG1") IsNot Nothing Then
76: PG1 = context.Session("PG1")
77: Else
78: PG1 = New SQL_Postgres(System.Configuration.ConfigurationManager.ConnectionStrings("PostgreSQL_ConnectionStrings").ConnectionString)
79: context.Session("PG1") = PG1
80: End If
81: PG1.CheckConnect()
82: End Sub
83:
84: End Class
1: <%@ WebHandler Language="VB" Class="GetFromMSSQL" %>
2:
3: Imports System
4: Imports System.Web
5:
6: 'http://localhost:3164/SqlPerfomanceHandler/GetFromMSSQL.ashx?SQL=SELECT%20*%20FROM%20%20partyresult;
7:
8: Public Class GetFromMSSQL : Implements IHttpHandler, IRequiresSessionState
9:
10: Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
11: Try
12: If context.Request.QueryString("SQL") Is Nothing Then
13: context.Response.ContentType = "text/plain"
14: context.Response.Write("SQL request not found")
15: Exit Sub
16: End If
17: If context.Request.QueryString("SQL") = "" Then
18: context.Response.ContentType = "text/plain"
19: context.Response.Write("SQL request not found")
20: Exit Sub
21: End If
22: '
23: MSSQL_Safe_Connection(context)
24: Dim DT1 As Data.DataTable = GetPostgreSQLRequest(context.Request.QueryString("SQL"))
25: '
26: Dim ResponseStream As New Text.StringBuilder
27: For i As Integer = 0 To DT1.Rows.Count - 1
28: For j As Integer = 0 To DT1.Columns.Count - 1
29: If Not IsDBNull(DT1.Rows(i)(j)) Then
30: ResponseStream.Append(DT1.Rows(i)(j))
31: End If
32: ResponseStream.Append(";")
33: Next
34: ResponseStream.Append(vbCrLf)
35: Next
36: '
37: context.Response.ContentType = "text/plain"
38: context.Response.Charset = "utf-8"
39: context.Response.Write(ResponseStream.ToString)
40:
41:
42: Catch ex As Exception
43: context.Response.ContentType = "text/plain"
44: context.Response.Write(ex.Message)
45: End Try
46: End Sub
47:
48: Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
49: Get
50: Return False
51: End Get
52: End Property
53:
54:
55: Function GetPostgreSQLRequest(ByVal SQL_CMD As String) As Data.DataTable
56: Dim RET1 As New Data.DataTable
57: Dim RDR1 As Data.SqlClient.SqlDataReader = MSSQL11.SQL.ExecRDR(SQL_CMD)
58: If RDR1.HasRows Then
59: RET1.Load(RDR1)
60: RDR1.Close()
61: End If
62: Return RET1
63: End Function
64:
65:
66: Dim MSSQL11 As SQL1
67:
68: Sub MSSQL_Safe_Connection(ByVal context As HttpContext)
69: If context.Session("MSSQL1") IsNot Nothing Then
70: MSSQL11 = context.Session("MSSQL1")
71: Else
72: MSSQL11 = New SQL1(System.Configuration.ConfigurationManager.ConnectionStrings("MSSQL_ConnectionStrings").ConnectionString)
73: context.Session("MSSQL1") = MSSQL11
74: End If
75: MSSQL11.CheckConnect()
76: End Sub
77:
78:
79: End Class
Comments (
)
)
Link to this page:
//www.vb-net.com/CompareSQLPerfomance/SqlPerfomanceHandler.htm
|
|