using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
namespace AndyQAD
{
public class ClassDB
{
static string sConnectionString = "server=(local);database=AndyQAD;uid=sa;pwd=sa";
SqlConnection cn1=new SqlConnection(sConnectionString);
SqlCommand cmd1 = new SqlCommand();
SqlDataAdapter da1 = new SqlDataAdapter();
public DataTable Table;
#region 错误提示
string msgError;
public string MsgError
{
get
{
return msgError;
}
}
#endregion
#region ExecuteScalar方法返回值
object returnValue;
public object ReturnValue
{
get
{
return returnValue;
}
}
#endregion
#region 记录总数属性RecordCount
int recordCount = 0;
public int RecordCount
{
get
{
return recordCount;
}
set
{
recordCount = value;
}
}
#endregion
#region 当前页面属性PageCurrent
int pageCurrent = 0;
public int PageCurrent
{
get
{
return pageCurrent;
}
set
{
pageCurrent = value;
}
}
#endregion
#region 页面总数属性PageCount
int pageCount = 0;
public int PageCount
{
get
{
return pageCount;
}
set
{
pageCount = value;
}
}
#endregion
#region 页面尺寸属性PageSize
int pageSize = 10;
public int PageSize
{
get
{
return pageSize;
}
set
{
pageSize = value;
}
}
#endregion
public ClassDB()
{
cmd1.Connection = cn1;
da1.SelectCommand = cmd1;
}
public bool ReadDataToTable(string cmdText)
{
bool isCorrect = true;
cmd1.CommandText = cmdText;
Table = new DataTable();
try
{
if (cn1.State.ToString() == "Closed")
{
cn1.Open();
}
da1.Fill(Table);
}
catch (Exception ex)
{
msgError = ex.Message;
isCorrect = false;
}
finally
{
if (cn1.State.ToString() == "Open")
{
cn1.Close();
}
}
return isCorrect;
}
public bool ReadDataToTable(string cmdText,string countText)
{
bool isCorrect = true;
cmd1.CommandText = cmdText;
Table = new DataTable();
try
{
if (cn1.State.ToString() == "Closed")
{
cn1.Open();
}
//读记录总数
cmd1.CommandText = countText;
recordCount = Convert.ToInt32(cmd1.ExecuteScalar());
//计算共有多少页
if ((recordCount % pageSize) == 0)
{
pageCount = recordCount / pageSize;
}
else
{
pageCount = recordCount / pageSize + 1;
}
cmd1.CommandText = cmdText;
if (pageCurrent > 0)
{
da1.Fill(0 + (pageCurrent - 1) * pageSize, pageSize, Table);
}
else
{
da1.Fill(0, pageSize, Table);
}
}
catch (Exception ex)
{
msgError = ex.Message;
isCorrect = false;
}
finally
{
if (cn1.State.ToString() == "Open")
{
cn1.Close();
}
}
return isCorrect;
}
public bool ExecuteScalar(string cmdText)
{
bool isCorrect = true;
cmd1.Parameters.Clear();
cmd1.CommandText = cmdText;
try
{
if (cn1.State.ToString() == "Closed")
{
cn1.Open();
}
returnValue = cmd1.ExecuteScalar();
}
catch (Exception ex)
{
msgError = ex.Message;
isCorrect = false;
}
finally
{
if (cn1.State.ToString() == "Open")
{
cn1.Close();
}
}
return isCorrect;
}
public bool ExecuteScalar(string sSqlCMD, string[] sParametersName, string[] sParametersValue)
{
bool isCorrect = true;
cmd1.Parameters.Clear();
cmd1.CommandText = sSqlCMD;
try
{
if (cn1.State.ToString() == "Closed")
{
cn1.Open();
}
for (int i1 = 0; i1 < sParametersName.Length; i1++)
{
cmd1.Parameters.Add(new SqlParameter(sParametersName[i1], sParametersValue[i1]));
}
returnValue = cmd1.ExecuteScalar();
}
catch (Exception ex)
{
msgError = ex.Message;
isCorrect = false;
}
finally
{
if (cn1.State.ToString() == "Open")
{
cn1.Close();
}
}
return isCorrect;
}
public bool ExecuteNonQuery(string sSqlCMD)
{
bool isCorrect = true;
cmd1.CommandText = sSqlCMD;
try
{
if (cn1.State.ToString() == "Closed")
{
cn1.Open();
}
cmd1.ExecuteNonQuery();
}
catch (Exception ex)
{
msgError = ex.Message;
isCorrect = false;
}
finally
{
if (cn1.State.ToString() == "Open")
{
cn1.Close();
}
}
return isCorrect;
}
//显示分页状态
public void ShowPageStatus(Button btnFirst, Button btnPrev, Button btnNext, Button btnLast, Label lblPage)
{
if (pageCurrent == 0 && pageCount > 0)
pageCurrent = 1;
if (pageCurrent > pageCount)
pageCurrent = pageCount;
lblPage.Text = "页次:" + pageCurrent.ToString() + "/" + pageCount.ToString();
if (pageCurrent <= 1)
{
btnFirst.Enabled = false;
btnPrev.Enabled = false;
}
else
{
btnFirst.Enabled = true;
btnPrev.Enabled = true;
}
if (pageCurrent == pageCount)
{
btnNext.Enabled = false;
btnLast.Enabled = false;
}
else
{
btnNext.Enabled = true;
btnLast.Enabled = true;
}
}
}
}