فئة لتسهيل التعامل مع خادم SQL Server، اسند قيمة نص الاتصال الى المتغير ConnectionString قبل استدعاء طرق الفئة: Basic: #Region "SQLHelper Class" ' SQLHelper ver 2.3 Public Class SQLHelper Public Shared ConnectionString As String Public Shared Function ExecuteSQLCommand(ByVal sql As String) As Integer Dim cmd As SqlClient.SqlCommand = GetCommandObject(sql) Dim count As Integer = cmd.ExecuteNonQuery() cmd.Connection.Close() Return count End Function Public Overloads Shared Function GetDataSet(ByVal sql As String, Optional ByVal schemaOnly As Boolean = False) As DataSet Dim cmd As SqlClient.SqlCommand = GetCommandObject(sql) Dim da As New SqlClient.SqlDataAdapter Dim ds As New DataSet Try da.SelectCommand = cmd If schemaOnly Then da.FillSchema(ds, SchemaType.Source) Else da.Fill(ds) Catch Throw Finally If cmd IsNot Nothing AndAlso cmd.Connection IsNot Nothing Then cmd.Connection.Close() End Try Return ds End Function Public Overloads Shared Function GetDataSet(ByVal cmd As SqlClient.SqlCommand) As DataSet Dim da As New SqlClient.SqlDataAdapter Dim ds As New DataSet Try da.SelectCommand = cmd da.Fill(ds) Catch Throw Finally If cmd IsNot Nothing AndAlso cmd.Connection IsNot Nothing Then cmd.Connection.Close() End Try Return ds End Function Public Overloads Shared Function GetDataReader(ByVal sql As String) As SqlClient.SqlDataReader Return GetDataReader(sql, CommandBehavior.CloseConnection) End Function Public Overloads Shared Function GetDataReader(ByVal sql As String, ByVal commandBehavior As CommandBehavior) As SqlClient.SqlDataReader Dim cmd As SqlClient.SqlCommand = GetCommandObject(sql) Return cmd.ExecuteReader(commandBehavior) End Function Public Shared Function GetQuickField(ByVal sql As String) As Object Dim cmd As SqlClient.SqlCommand = GetCommandObject(sql) Return cmd.ExecuteScalar() End Function Public Shared Function GetCommandObject(ByVal sql As String) As SqlClient.SqlCommand Dim cmd As New SqlClient.SqlCommand(sql) cmd.Connection = New SqlClient.SqlConnection(ConnectionString) cmd.Connection.Open() Return cmd End Function Friend Shared Function GetPagedSQL(ByVal pageNum As Integer, ByVal pageSize As Integer, ByVal pKey As String, ByVal tableName As String, ByVal selectedFields As String, ByVal ordering As String, Optional ByVal whereCluase As String = "", Optional ByVal joinStatment As String = "") As String Dim sql As String If whereCluase <> String.Empty Then whereCluase = " WHERE " & whereCluase If pageNum <= 1 Then : pageNum = 1 ' First Page. This is good for optimization If joinStatment = String.Empty Then sql = String.Format(" SELECT TOP {0} {1} FROM [{2}] {3}", pageSize, selectedFields, tableName, whereCluase) Else sql = String.Format(" SELECT TOP {0} {1} FROM [{2}] {3} {4}", pageSize, selectedFields, tableName, joinStatment, whereCluase) End If Else If joinStatment = String.Empty Then sql = String.Format(" SELECT TOP {0} {1} FROM [{2}] {3}", pageSize, selectedFields, tableName, whereCluase) Else sql = String.Format(" SELECT TOP {0} {1} FROM [{2}] {3} {4}", pageSize, selectedFields, tableName, joinStatment, whereCluase) End If If whereCluase = String.Empty Then sql &= String.Format(" WHERE [{0}] NOT IN ", pKey) Else sql &= String.Format(" AND [{0}] NOT IN ", pKey) End If sql &= String.Format(" (SELECT TOP {0} [{1}] FROM [{2}] {3} ORDER BY {4})", (pageNum - 1) * pageSize, pKey, tableName, whereCluase, ordering) End If sql &= String.Format(" ORDER BY {0}", ordering) Return sql End Function End Class #End Region C#: using System; using System.Data; using System.Data.SqlClient; // SQLHelper ver 2.3 public class SQLHelper { public static string ConnectionString; public static int ExecuteSQLCommand(string sql) { SqlCommand cmd = GetCommandObject(sql); int count = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return count; } public static DataSet GetDataSet(string sql, bool schemaOnly) { SqlCommand cmd = GetCommandObject(sql); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); try { da.SelectCommand = cmd; if (schemaOnly) da.FillSchema(ds, SchemaType.Source); else da.Fill(ds); } catch { throw; } finally { if (!((cmd == null)) && !((cmd.Connection == null))) cmd.Connection.Close(); } return ds; } public static DataSet GetDataSet(SqlCommand cmd) { SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); try { da.SelectCommand = cmd; da.Fill(ds); } catch { throw; } finally { if (!((cmd == null)) && !((cmd.Connection == null))) cmd.Connection.Close(); } return ds; } public static SqlDataReader GetDataReader(string sql) { return GetDataReader(sql, CommandBehavior.CloseConnection); } public static SqlDataReader GetDataReader(string sql, CommandBehavior commandBehavior) { SqlCommand cmd = GetCommandObject(sql); return cmd.ExecuteReader(commandBehavior); } public static object GetQuickField(string sql) { SqlCommand cmd = GetCommandObject(sql); return cmd.ExecuteScalar(); } public static SqlCommand GetCommandObject(string sql) { SqlCommand cmd = new SqlCommand(sql); cmd.Connection = new SqlConnection(ConnectionString); cmd.Connection.Open(); return cmd; } internal static string GetPagedSQL(int pageNum, int pageSize, string pKey, string tableName, string selectedFields, string ordering, string whereCluase, string joinStatment) { string sql; if (whereCluase != string.Empty) whereCluase = " WHERE " + whereCluase; if (pageNum <= 1) { pageNum = 1; if (joinStatment == string.Empty) sql = string.Format(" SELECT TOP {0} {1} FROM [{2}] {3}", pageSize, selectedFields, tableName, whereCluase); else sql = string.Format(" SELECT TOP {0} {1} FROM [{2}] {3} {4}", pageSize, selectedFields, tableName, joinStatment, whereCluase); } else { if (joinStatment == string.Empty) sql = string.Format(" SELECT TOP {0} {1} FROM [{2}] {3}", pageSize, selectedFields, tableName, whereCluase); else sql = string.Format(" SELECT TOP {0} {1} FROM [{2}] {3} {4}", pageSize, selectedFields, tableName, joinStatment, whereCluase); if (whereCluase == string.Empty) sql += string.Format(" WHERE [{0}] NOT IN ", pKey); else sql += string.Format(" AND [{0}] NOT IN ", pKey); sql += string.Format(" (SELECT TOP {0} [{1}] FROM [{2}] {3} ORDER BY {4})", (pageNum - 1) * pageSize, pKey, tableName, whereCluase, ordering); } sql += string.Format(" ORDER BY {0}", ordering); return sql; } }