/// <summary>
//// Execute stored procedures (return to record set)
/// </summary>
/// <param name="procName">Process Name</param>
/// <param name="hashtable">Incoming parameter table</param>
/// <returns>Return to record set</returns>
public DataSet ExecProcedure(string procName, hashtable)
{
ds = new DataSet();
= procName;
= ;
ide = ();
while (())
{
p = new ((), );
(p);
}
try
{
da = new (com);
(ds);
();
}
catch (Exception ee)
{
throw new Exception(());
}
finally
{
();
}
return ds;
}
#endregion
#region Data Operation
/// <summary>
//// Statistics the total number of records in a certain table
/// </summary>
/// <param name="KeyField">Primary key/index key</param>
/// <param name="TableName">Database.Username.Tablename</param>
/// <param name="Condition">Query Conditions</param>
/// <returns>Return the total number of records</returns>
public int GetRecordCount(string keyField, string tableName, string condition)
{
int RecordCount = 0;
string sql = "select count(" + keyField + ") as count from " + tableName + " " + condition;
ds = GetDataSet(sql);
if ([0]. > 0)
{
RecordCount =Convert.ToInt32([0].Rows[0][0]);
}
();
();
return RecordCount;
}
/// <summary>
//// Statistics the total number of records in a certain table
/// </summary>
/// <param name="Field">Repeatable fields</param>
/// <param name="tableName">Database.Username.Table Name</param>
/// <param name="condition">Query conditions</param>
/// <param name="flag">Is the field primary key</param>
/// <returns>Return the total number of records</returns>
public int GetRecordCount(string Field, string tableName, string condition, bool flag)
{
int RecordCount = 0;
if (flag)
{
RecordCount = GetRecordCount(Field, tableName, condition);
}
else
{
string sql = "select count(distinct(" + Field + ")) as count from " + tableName + " " + condition;
ds = GetDataSet(sql);
if ([0]. > 0)
{
RecordCount = Convert.ToInt32([0].Rows[0][0]);
}
();
();
}
return RecordCount;
}
/// <summary>
//// Statistics the total number of pages in a certain table
/// </summary>
/// <param name="keyField">Primary key/index key</param>
/// <param name="tableName">table name</param>
/// <param name="condition">Query conditions</param>
/// <param name="pageSize">Page Width</param>
/// <param name="RecordCount">Total record count</param>
/// <returns>Return to the total number of pages</returns>
public int GetPageCount(string keyField, string tableName, string condition, int pageSize, int RecordCount)
{
int PageCount = 0;
PageCount = (RecordCount % pageSize) > 0 ? (RecordCount / pageSize) + 1 : RecordCount / pageSize;
if (PageCount < 1) PageCount = 1;
return PageCount;
}
/// <summary>
//// Statistics the total number of pages in a certain table
/// </summary>
/// <param name="keyField">Primary key/index key</param>
/// <param name="tableName">table name</param>
/// <param name="condition">Query conditions</param>
/// <param name="pageSize">Page Width</param>
/// <returns>Return to the total number of pages</returns>
public int GetPageCount(string keyField, string tableName, string condition, int pageSize, ref int RecordCount)
{
RecordCount = GetRecordCount(keyField, tableName, condition);
return GetPageCount(keyField, tableName, condition, pageSize, RecordCount);
}
/// <summary>
//// Statistics the total number of pages in a certain table
/// </summary>
/// <param name="Field">Repeatable fields</param>
/// <param name="tableName">table name</param>
/// <param name="condition">Query conditions</param>
/// <param name="pageSize">Page Width</param>
/// <param name="flag">Is the primary key</param>
/// <returns>Return to the total number of pages</returns>
public int GetPageCount(string Field, string tableName, string condition, ref int RecordCount, int pageSize, bool flag)
{
RecordCount = GetRecordCount(Field, tableName, condition, flag);
return GetPageCount(Field, tableName, condition, pageSize, ref RecordCount);
}
#endregion
#region pagination function
/// <summary>
/// Construct paging query SQL statement
/// </summary>
/// <param name="KeyField">Primary key</param>
/// <param name="FieldStr">All fields that need to be queried (field1,field2...)</param>
/// <param name="TableName">Library name.Owner.Table name</param>
/// <param name="Condition">Query Condition 1(where ...)</param>
/// <param name="Condition2">Query Condition 2(order by ...)</param>
/// <param name="CurrentPage">Current page number</param>
/// <param name="PageSize">Page Width</param>
/// <returns>SQL statement</returns>
public static string JoinPageSQL(string KeyField, string FieldStr, string TableName, string Condition, string Condition2, int CurrentPage, int PageSize)
{
string sql = null;
if (CurrentPage == 1)
{
sql = "select top " + CurrentPage * PageSize + " " + FieldStr + " from " + TableName + " " + Condition + " " + Condition2 + " ";
}
else
{
sql = "select * from (";
sql += "select top " + CurrentPage * PageSize + " " + FieldStr + " from " + TableName + " " + Condition + " " + Condition2 + ") a ";
sql += "where " + KeyField + " not in (";
sql += "select top " + (CurrentPage - 1) * PageSize + " " + KeyField + " from " + TableName + " " + Condition + " " + Condition2 + ")";
}
return sql;
}
/// <summary>
/// Construct paging query SQL statement
/// </summary>
/// <param name="Field">Field Name (non-primary key)</param>
/// <param name="TableName">Library name.Owner.Table name</param>
/// <param name="Condition">Query Condition 1(where ...)</param>
/// <param name="Condition2">Query Condition 2(order by ...)</param>
/// <param name="CurrentPage">Current page number</param>
/// <param name="PageSize">Page Width</param>
/// <returns>SQL statement</returns>
public static string JoinPageSQL(string Field, string TableName, string Condition, string Condition2, int CurrentPage, int PageSize)
{
string sql = null;
if (CurrentPage == 1)
{
sql = "select top " + CurrentPage * PageSize + " " + Field + " from " + TableName + " " + Condition + " " + Condition2 + " group by " + Field;
}
else
{
sql = "select * from (";
sql += "select top " + CurrentPage * PageSize + " " + Field + " from " + TableName + " " + Condition + " " + Condition2 + " group by " + Field + " ) a ";
sql += "where " + Field + " not in (";
sql += "select top " + (CurrentPage - 1) * PageSize + " " + Field + " from " + TableName + " " + Condition + " " + Condition2 + " group by " + Field + ")";
}
return sql;
}
/// <summary>
//// Page pagination display function
/// </summary>
/// <param name="Parameters">parameter string (a=1&b=2...)</param>
/// <param name="RecordCount">Total record count</param>
/// <param name="PageSize">Page Width</param>
/// <param name="CurrentPage">Current page number</param>
/// <param name="ShowJump">Whether the jump input box and buttons are displayed</param>
/// <param name="Style">Style (1: Previous page next page..., 2:1234...)</param>
/// <returns></returns>
public static string Paging(string Parameters, int RecordCount, int PageCount, int PageSize, int CurrentPage, bool ShowJump, int Style)
{
string str;
if (RecordCount <= PageSize) return "";
if (Parameters != "") Parameters += "&";
if (CurrentPage < 1) CurrentPage = 1;
if (CurrentPage > PageCount) CurrentPage = PageCount;
str = "<table align='center' width=\"100%\"><tr><td align=\"center\">";
str += "Total "+ RecordCount + " RecordCount + " Record Page: " + CurrentPage + "/" + PageCount + "Page ";
str += PageSize + "Bit/Page ";
if (Style == 1)
{
if (CurrentPage == 1)
str += "<font color=\"#999999\">Home Previous Page</font> ";
else
{
str += "<a href='?" + Parameters + "page=1' class=\"link\">Home</a> ";
str += "<a href='?" + Parameters + "page=" + (CurrentPage - 1) +"' class=\"link\">Previous Page</a> ";
}
if (CurrentPage == PageCount )
{
str += "<font color=\"#999999\">Next page Last page</font> ";
}
else
{
str += "<a href='?" + Parameters + "page=" + (CurrentPage + 1) +"' class=\"link\">Next Page</a> ";
str += "<a href='?" + Parameters + "page=" + PageCount + "' class=\"link\">Last Page</a> ";
}
}
else if (Style == 2)
{
int NumberSize = 10;
int PageNumber = (CurrentPage - 1) / NumberSize;
if (PageNumber * NumberSize > 0)
str += "<a href='?" + Parameters + "page=" + PageNumber * NumberSize + "' title=Previous ten pages >[<<]</a> ";
int i;
for (i = PageNumber * NumberSize + 1; i <= (PageNumber + 1) * NumberSize; i++)
{
if (i == CurrentPage)
str += "<strong><font color=#ff0000>[" + i + "]</font></strong> ";
else
str += "<a href='?" + Parameters + "page=" + i + "'>[" + i + "]</a> ";
if (i == PageCount) break;
}
if (i < RecordCount) str += "<a href='?" + Parameters + "page=" + i + "' title=Next ten pages>[>>]</a> ";
}
if (ShowJump)
{
str += "";
}
str += "</td></tr></table>";
return str;
}
#endregion
}
}
Previous page12Read the full text