#region -----------------------SQL数据库的操作方法,返回DataReader,DataSet,DataTable等对象---------------------
#region ------------执行SQL语句或存储过程,返回sqlCommand数据集---------------------
/// <summary>
/// 执行存储过程语句,返回sqlCommand类对象
/// </summary>
/// <param name="strProcName">存储过程名</param>
/// <returns>返回sqlCommand类对象</returns>
public SqlCommand getCommandProc(string strProcName)
{
SqlConnection myConn = GetConnection();//调用上面的链接字符串
SqlCommand myComm = new SqlCommand();
myComm.Connection = myConn;
myComm.CommandText = strProcName;
myComm.CommandType = CommandType.StoredProcedure;//存储过程的名字
return myComm;
}
/// <summary>
/// 执行SQL语句,返回sqlCommand类的对象
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回sqlCommand类对象</returns>
public SqlCommand getCommandStr(string sql)
{
SqlConnection myConn = GetConnection();
SqlCommand myComm = new SqlCommand();
myComm.Connection = myConn;
myComm.CommandText = sql;
return myComm;
}
#endregion
#region -----------执行SQL语句或存储过程,返回int或bool以作判断--------
/// <summary>
/// 执行SqlCommand返回受影响的行数
/// </summary>
/// <returns>执行SQL语句命令的SqlCommand对象</returns>
public void execNonQueryByComm(SqlCommand myCmd)
{
try
{
if (myCmd.Connection.State != ConnectionState.Open)//myCmd.Connection获取或者设置System.Data.SqlClient.SqlCommand的此实列用的,State状态
{
myCmd.Connection.Open(); //打开与数据库的连接
}
//使用SqlCommand对象的ExecuteNonQuery方法执行SQL语句,并返回受影响的行数
myCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (myCmd.Connection.State == ConnectionState.Open)
{
myCmd.Connection.Close(); //关闭与数据库的连接
myCmd.Dispose();//释放资源
}
}
}
/// <summary>
/// 执行SQL语句,并返回受影响的行数
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static int execNonQueryBySql(string strSql)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
int i = cmd.ExecuteNonQuery();
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句,并返回受影响的行数(待参数的SQL语句)
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static int execNonQueryBySqlparm(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
int i = cmd.ExecuteNonQuery();
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 返回bool值判断存储过程是否执行成功
/// </summary>
/// <param name="proName">存储过程名称</param>
/// <returns></returns>
public static bool getBoolByPro(string proName)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
bool perform = false;
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
perform = true;
return perform;
}
else
{
return perform;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 返回bool值判断存储过程是否执行成功
/// </summary>
/// <param name="proName">存储过程名称</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static bool getBoolByProparm(string proName, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
bool perform = false;
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
perform = true;
return perform;
}
else
{
return perform;
}
}
catch
{
return perform;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
#endregion
#region ------------执行SQL语句或存储过程,返回DataReader数据集---------------------
/// <summary>
/// 根据传入的SQL语句返回DataReader数据集.
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static SqlDataReader getDataReaderBySql(string strSql)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
//cmd.Dispose();
}
}
/// <summary>
/// 根据传入的SQL语句返回DataReader数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static SqlDataReader getDataReaderBySqlparm(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
}
}
/// <summary>
/// 说 明: 返回数据集的表的可读的DataReader
/// 返回值: 容纳数据源的数据表的容器DataReader
/// 参 数: sqlstr执行SQL语句命令的字符串
/// </summary>
public SqlDataReader getDrSqlstr(string sqlstr)
{
SqlCommand comm = GetCommandStr(sqlstr);
if (comm.Connection.State == ConnectionState.Closed)
{
comm.Connection.Open();
}
SqlDataReader dr = comm.ExecuteReader();
return dr;
}
/// <summary>
/// 执行存储过程返回一个DataReader对象
/// </summary>
/// <param name="com">sqlCommand类的对象</param>
/// <returns>返回一个DataReader对象</returns>
public SqlDataReader getDrProc(SqlCommand com)
{
SqlDataReader dr;
if (com.Connection.State == ConnectionState.Closed)
{
com.Connection.Open();
}
try
{
dr = com.ExecuteReader();
return dr;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 执行存储过程,并返回DataReader数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <returns></returns>
public static SqlDataReader getDataReaderByPro(string proName)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = proName;
cmd.Connection = conn;
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
}
}
/// <summary>
/// 执行存储过程并返回DataReader数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static SqlDataReader getDataReaderByProparm(string proName, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = proName;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
}
}
#endregion
#region ---------------执行SQL语句或存储过程,返回DataSet数据集-------------
/// <summary>
/// 执行SQL语句,并返回一个DataSet数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static DataSet getDataSetBySql(string strSql)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
sda.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句,并返回DataSet数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataSet getDataSetBySqlparm(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
sda.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行存储过程,并返回DataSet数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <returns></returns>
public static DataSet getDataSetByPro(string proName)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
sda.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行存储过程,并返回DataSet数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataSet getDataSetByProparm(string proName, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
cmd.Dispose();
sda.Dispose();
conn.Close();
conn.Dispose();
}
}
#endregion
#region ---------------执行SQL语句或存储过程,返回DataTable数据集-------------
/// <summary>
/// 说 明: 返回数据集的表的集合
/// 返回值: 数据源的数据表
/// 参 数: myCmd 执行SQL语句命令的SqlCommand对象,TableName返回的数据表名称
/// </summary>
public DataTable getDataSetByComm(SqlCommand myComm, string TableName)
{
SqlDataAdapter adapt;//声明一个SqlDataAdapter
DataSet ds = new DataSet();//实列化一个DataSet
try
{
if (myComm.Connection.State != ConnectionState.Open)//如果myComm没打开就打开它
{
myComm.Connection.Open();
}
adapt = new SqlDataAdapter(myComm);//给adapt赋值
adapt.Fill(ds, TableName);//填充adapt
return ds.Tables[TableName];//返回表
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (myComm.Connection.State == ConnectionState.Open)
{
myComm.Connection.Close();
myComm.Dispose();
}
}
}
/// <summary>
/// 执行SQL语句,并返回DataTable数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static DataTable getDataTableBySql(string strSql)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
dt.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句,并返回DataTable数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataTable getDataTableBySqlparm(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
sda.SelectCommand = cmd;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
dt.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL存储过程,并返回DataTalbe数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <returns></returns>
public static DataTable getDataTableByPro(string proName)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
dt.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL存储过程,并返回DataTable数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataTable getDataTableByProparm(string proName, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
cmd.Dispose();
dt.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句查询数据源,根据start与num将数据集分页并返回名称为info的DataTable
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="start">查询数据集的起始位置,从0开始</param>
/// <param name="num">查询数量</param>
/// <returns></returns>
public static DataTable getDataTableOfPage(string strSql, int start, int num)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(ds, start, num, "info");
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
cmd.Dispose();
ds.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句查询数据源,根据start与num将数据集分页并返回名称为info的DataTable
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="start">查询数据集的起始位置,从0开始</param>
/// <param name="num">查询数量</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataTable getDataTableOfPageparm(string strSql, int start, int num, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(ds, start, num, "info");
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
cmd.Dispose();
ds.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 返回查询结果DataTable前几条(公用方法)
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="num">前几条?</param>
/// <param name="filed">数据库过滤字段数组</param>
/// <param name="parametersList">参数名数组</param>
/// <param name="filetype">sqldbtype类型数组</param>
/// <param name="values">参数值数组</param>
/// <param name="isorder">正序还是倒序?正序为true</param>
/// <param name="orderValue">排序字段</param>
public static DataTable getInfo(string tablename, int num, string[] filed, string[] parametersList, SqlDbType[] filetype, string[] values, bool isorder, string orderValue)
{
StringBuilder result = new StringBuilder();
DataTable dt = new DataTable();
if (filed.Length == parametersList.Length && filed.Length == filetype.Length)
{
string order = string.Empty;
if (!"".Equals(orderValue))
{
order += " order by " + orderValue;
if (!isorder)
{
order += " desc";
}
}
string sql = "select top " + num + " * from " + tablename + " where ";
SqlParameter[] par = new SqlParameter[values.Length];
if (filed.Length > 0)
{
for (int i = 0; i < filed.Length; i++)
{
sql += filed[i] + " =@" + parametersList[i] + " and ";
}
for (int i = 0; i < values.Length; i++)
{
par[i] = new SqlParameter("@" + parametersList[i], filetype[i]);
par[i].Value = values[i];
}
sql = sql.Substring(0, sql.Length - 4);
sql += order;
}
else
{
sql += "1=1 ";
sql += order;
}
SqlConnection con = null;
DataSet ds = null;
SqlDataAdapter dp = null;
SqlCommand cmd = null;
try
{
con = new SqlConnection(_connString);
cmd = new SqlCommand(sql, con);
for (int i = 0; i < par.Length; i++)
{
cmd.Parameters.Add(par[i]);
}
dp = new SqlDataAdapter();
dp.SelectCommand = cmd;
ds = new DataSet();
dp.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return dt;
}
/// <summary>
/// 根据条件查询并且绑定 并返回数据表集
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="filed">数据库过滤字段数组</param>
/// <param name="conditions">查询逻辑条件</param>
/// <param name="parametersList">参数名数组</param>
/// <param name="filetype">sqldbtype类型数组</param>
/// <param name="values">参数值数组</param>
/// <param name="isorder">正序还是倒序?正序为true</param>
/// <param name="orderValue">排序字段</param>
/// <param name="dl">绑定容器:DataList GridView Repeater</param>
/// <param name="isPage">是否分页</param>
/// <param name="pageSize">分页条数</param>
/// <param name="pageIndex">页数</param>
/// <param name="yemian">跳转的页面</param>
/// <returns>数据表集</returns>
public static DataTable getDataTablePage(string tablename, string[] filed, string[] conditions, string[] parametersList, SqlDbType[] filetype, string[] values, bool isorder, string orderValue, bool isPage, int pageSize, int pageIndex, string yemian)
{
StringBuilder result = new StringBuilder();
DataTable dt = new DataTable();
if (filed.Length == parametersList.Length && filed.Length == filetype.Length && filed.Length == conditions.Length)
{
string order = string.Empty;
if (!"".Equals(orderValue))
{
order += " order by " + orderValue;
if (!isorder)
{
order += " desc";
}
}
string sql = "select * from " + tablename + " where ";
SqlParameter[] par = new SqlParameter[values.Length];
if (filed.Length > 0)
{
for (int i = 0; i < filed.Length; i++)
{
sql += filed[i] + conditions[i] + " @" + parametersList[i] + " and ";
}
for (int i = 0; i < values.Length; i++)
{
par[i] = new SqlParameter("@" + parametersList[i], filetype[i]);
par[i].Value = values[i];
}
sql = sql.Substring(0, sql.Length - 4);
sql += order;
}
else
{
sql += "1=1 ";
sql += order;
}
SqlConnection con = null;
DataSet ds = null;
SqlDataAdapter dp = null;
SqlCommand cmd = null;
try
{
con = new SqlConnection(_connString);
cmd = new SqlCommand(sql, con);
for (int i = 0; i < par.Length; i++)
{
cmd.Parameters.Add(par[i]);
}
dp = new SqlDataAdapter();
dp.SelectCommand = cmd;
ds = new DataSet();
if (isPage)
{
if (yemian.IndexOf("?") == -1)
{
yemian += "?";
}
else
{
yemian += "&";
}
int start = (pageIndex - 1) * pageSize;
int end = pageSize;
dp.Fill(ds, start, end, "内容表");
DataSet ds1 = new DataSet();
dp.Fill(ds1);
int total = ds1.Tables[0].Rows.Count;//总条数
int page_total = (total % pageSize) == 0 ? (total / pageSize) : (total / pageSize + 1);//总页数
int end_page = page_total;//最后一页
result.Append("<div style=\"height: 16px; padding-top: 5px; float: left; font-size:12px; font-family:Verdana;\">");
result.Append("共" + page_total + "页 | 当前第" + pageIndex + "页 |");
if (pageIndex > 1)
{
result.Append(" <a href=\"" + yemian + "pageIndex=1\" style=\" text-decoration:none\">首页</a> |");
}
else
{
result.Append(" 首页 |");
}
if (pageIndex > 1)
{
result.Append(" <a href=\"" + yemian + "pageIndex=" + (pageIndex - 1) + "\" style=\" text-decoration:none\">上一页</a> |");
}
else
{
result.Append(" 上一页 |");
}
if (pageIndex < end_page)
{
result.Append(" <a href=\"" + yemian + "pageIndex=" + (pageIndex + 1) + "\" style=\"text-decoration:none\">下一页</a> |");
}
else
{
result.Append(" 下一页 |");
}
if (pageIndex < page_total)
{
result.Append(" <a href=\"" + yemian + "pageIndex=" + page_total + "\" style=\" text-decoration:none\">尾页</a> ");
}
else
{
result.Append(" 尾页 ");
}
result.Append("<script type=\"text/javascript\">");
result.Append("function get1(){");
result.Append("var va=document.getElementById(\"name1\").value;");
result.Append(" if(va <=" + page_total + " && va >=1){");
result.Append("location.href=\"" + yemian + "pageIndex=\"+va;}");
result.Append("}</script>");
result.Append(" 转到 <input type=\"text\" style=\"width:30px; height:12px;\" id=\"name1\"/> 页 ");
result.Append("<a οnclick=\"get1();\" href=\"#\" style=\"text-decoration: none\">GO</a>");
result.Append("</div>");
}
else
{
dp.Fill(ds);
}
dt = ds.Tables[0];
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return dt;
}
/// <summary>
/// 根据条件查询分页 并返回数据表集
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="filed">数据库过滤字段数组</param>
/// <param name="parametersList">参数名数组</param>
/// <param name="filetype">sqldbtype类型数组</param>
/// <param name="values">参数值数组</param>
/// <param name="isorder">正序还是倒序?正序为true</param>
/// <param name="orderValue">排序字段</param>
/// <param name="isPage">是否分页</param>
/// <param name="pageSize">分页条数</param>
/// <param name="pageIndex">页数</param>
/// <returns>数据表集</returns>
public static DataTable getDataTableByPage(string tablename, string[] filed, string[] parametersList, SqlDbType[] filetype, string[] values, bool isorder, string orderValue, bool isPage, int pageSize, int pageIndex)
{
DataTable dt = new DataTable();
if (filed.Length == parametersList.Length && filed.Length == filetype.Length)
{
string order = string.Empty;
if (!"".Equals(orderValue))
{
order += " order by " + orderValue;
if (!isorder)
{
order += " desc";
}
}
string sql = "select * from " + tablename + " where ";
SqlParameter[] par = new SqlParameter[values.Length];
if (filed.Length > 0)
{
for (int i = 0; i < filed.Length; i++)
{
sql += filed[i] + " =@" + parametersList[i] + " and ";
}
for (int i = 0; i < values.Length; i++)
{
par[i] = new SqlParameter("@" + parametersList[i], filetype[i]);
par[i].Value = values[i];
}
sql = sql.Substring(0, sql.Length - 4);
sql += order;
}
else
{
sql += "1=1 ";
sql += order;
}
SqlConnection con = null;
DataSet ds = null;
SqlDataAdapter dp = null;
SqlCommand cmd = null;
try
{
con = new SqlConnection(_connString);
cmd = new SqlCommand(sql, con);
for (int i = 0; i < par.Length; i++)
{
cmd.Parameters.Add(par[i]);
}
dp = new SqlDataAdapter();
dp.SelectCommand = cmd;
ds = new DataSet();
if (isPage)
{
int start = (pageIndex - 1) * pageSize;
int end = pageSize;
dp.Fill(ds, start, end, "新闻表");
}
else
{
dp.Fill(ds);
}
dt = ds.Tables[0];
}
catch (Exception exx)
{
throw exx;
}
finally
{
con.Close();
}
}
return dt;
}
#endregion
#region ---------------执行SQL语句或存储过程,返回新插入行的ID数据集-------------
/// <summary>
/// 执行SQL,返回新插入行的ID
/// </summary>
/// <param name="strSql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static int getBySqlReturnID(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
int i = int.Parse(cmd.ExecuteScalar().ToString());
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 根据条件插入返回ID(公共方法)
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="filed">插入字段</param>
/// <param name="parametersList">插入参数名</param>
/// <param name="filetype">参数类型</param>
/// <param name="values">参数值</param>
/// <returns>返回ID</returns>
public static int insertreturnid(string tablename, string[] filed, string[] parametersList, SqlDbType[] filetype, string[] values)
{
int result = 0;
string sql = "insert into " + tablename;
SqlParameter[] par = new SqlParameter[values.Length];
if (filed.Length > 0)
{
sql += "(";
for (int i = 0; i < filed.Length; i++)
{
sql += filed[i] + " , ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ")";
sql += " values(";
for (int i = 0; i < parametersList.Length; i++)
{
sql += "@" + parametersList[i] + " , ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ") select @@identity as num";//@@identity全局变量或者插入新行的标示字段如ID
for (int i = 0; i < values.Length; i++)
{
if (values[i].ToLower().Contains("script") || values[i].Contains("iframe") || values[i].Contains("frame"))
{
HttpContext.Current.Response.Write("<script>alert('检测到有敏感字符,请重新输入!');</script>");
return 0;
}
par[i] = new SqlParameter("@" + parametersList[i], filetype[i]);
par[i].Value = values[i];
}
}
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader sd = null;
try
{
//con = new SqlConnection(_connString);
SqlConnection con = getSQLConnection();
con.Open();
cmd = new SqlCommand(sql, con);
for (int i = 0; i < par.Length; i++)
{
cmd.Parameters.Add(par[i]);
}
sd = cmd.ExecuteReader();
if (sd.Read())
{
result = int.Parse(sd[0].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return result;
}
#endregion
#region ---------------执行SQL语句或存储过程,返回数据集的第一行第一列-------------
/// <summary>
/// 执行SQL语句,返回数据集的第一行第一列
/// </summary>
/// <param name="strSql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static string getValueByFiled(string strSql, SqlParameter[] parms)
{
string result = "0";
try
{
DataTable dt = new DataTable();
dt = GlobalSystem.getDataTableBySql(strSql, parms);
result = dt.Rows[0][0].ToString();
}
catch (Exception E)
{
throw E;
}
return result;
}
/// <summary>
/// 返回object 无数组参数,第一行的第一列
/// </summary>
/// <param name="str">SQL语句</param>
/// <returns></returns>
public static object getExecuteScalar1(string str)
{
object i;
try
{
using (SqlConnection sqlConnection = new SqlConnection())
{
string DB = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
sqlConnection.ConnectionString = DB;
sqlConnection.Open();
SqlCommand SqlCommand = new SqlCommand(str, sqlConnection);
i = SqlCommand.ExecuteScalar();
sqlConnection.Close();
return i;
}
}
catch (Exception E)
{
throw E;
}
}
#endregion
#endregion
#region ------------执行SQL语句或存储过程,返回sqlCommand数据集---------------------
/// <summary>
/// 执行存储过程语句,返回sqlCommand类对象
/// </summary>
/// <param name="strProcName">存储过程名</param>
/// <returns>返回sqlCommand类对象</returns>
public SqlCommand getCommandProc(string strProcName)
{
SqlConnection myConn = GetConnection();//调用上面的链接字符串
SqlCommand myComm = new SqlCommand();
myComm.Connection = myConn;
myComm.CommandText = strProcName;
myComm.CommandType = CommandType.StoredProcedure;//存储过程的名字
return myComm;
}
/// <summary>
/// 执行SQL语句,返回sqlCommand类的对象
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回sqlCommand类对象</returns>
public SqlCommand getCommandStr(string sql)
{
SqlConnection myConn = GetConnection();
SqlCommand myComm = new SqlCommand();
myComm.Connection = myConn;
myComm.CommandText = sql;
return myComm;
}
#endregion
#region -----------执行SQL语句或存储过程,返回int或bool以作判断--------
/// <summary>
/// 执行SqlCommand返回受影响的行数
/// </summary>
/// <returns>执行SQL语句命令的SqlCommand对象</returns>
public void execNonQueryByComm(SqlCommand myCmd)
{
try
{
if (myCmd.Connection.State != ConnectionState.Open)//myCmd.Connection获取或者设置System.Data.SqlClient.SqlCommand的此实列用的,State状态
{
myCmd.Connection.Open(); //打开与数据库的连接
}
//使用SqlCommand对象的ExecuteNonQuery方法执行SQL语句,并返回受影响的行数
myCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (myCmd.Connection.State == ConnectionState.Open)
{
myCmd.Connection.Close(); //关闭与数据库的连接
myCmd.Dispose();//释放资源
}
}
}
/// <summary>
/// 执行SQL语句,并返回受影响的行数
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static int execNonQueryBySql(string strSql)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
int i = cmd.ExecuteNonQuery();
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句,并返回受影响的行数(待参数的SQL语句)
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static int execNonQueryBySqlparm(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
int i = cmd.ExecuteNonQuery();
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 返回bool值判断存储过程是否执行成功
/// </summary>
/// <param name="proName">存储过程名称</param>
/// <returns></returns>
public static bool getBoolByPro(string proName)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
bool perform = false;
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
perform = true;
return perform;
}
else
{
return perform;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 返回bool值判断存储过程是否执行成功
/// </summary>
/// <param name="proName">存储过程名称</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static bool getBoolByProparm(string proName, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
bool perform = false;
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
perform = true;
return perform;
}
else
{
return perform;
}
}
catch
{
return perform;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
#endregion
#region ------------执行SQL语句或存储过程,返回DataReader数据集---------------------
/// <summary>
/// 根据传入的SQL语句返回DataReader数据集.
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static SqlDataReader getDataReaderBySql(string strSql)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
//cmd.Dispose();
}
}
/// <summary>
/// 根据传入的SQL语句返回DataReader数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static SqlDataReader getDataReaderBySqlparm(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
}
}
/// <summary>
/// 说 明: 返回数据集的表的可读的DataReader
/// 返回值: 容纳数据源的数据表的容器DataReader
/// 参 数: sqlstr执行SQL语句命令的字符串
/// </summary>
public SqlDataReader getDrSqlstr(string sqlstr)
{
SqlCommand comm = GetCommandStr(sqlstr);
if (comm.Connection.State == ConnectionState.Closed)
{
comm.Connection.Open();
}
SqlDataReader dr = comm.ExecuteReader();
return dr;
}
/// <summary>
/// 执行存储过程返回一个DataReader对象
/// </summary>
/// <param name="com">sqlCommand类的对象</param>
/// <returns>返回一个DataReader对象</returns>
public SqlDataReader getDrProc(SqlCommand com)
{
SqlDataReader dr;
if (com.Connection.State == ConnectionState.Closed)
{
com.Connection.Open();
}
try
{
dr = com.ExecuteReader();
return dr;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 执行存储过程,并返回DataReader数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <returns></returns>
public static SqlDataReader getDataReaderByPro(string proName)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = proName;
cmd.Connection = conn;
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
}
}
/// <summary>
/// 执行存储过程并返回DataReader数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static SqlDataReader getDataReaderByProparm(string proName, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = proName;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
SqlDataReader sdr = cmd.ExecuteReader();
return sdr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
}
}
#endregion
#region ---------------执行SQL语句或存储过程,返回DataSet数据集-------------
/// <summary>
/// 执行SQL语句,并返回一个DataSet数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static DataSet getDataSetBySql(string strSql)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
sda.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句,并返回DataSet数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataSet getDataSetBySqlparm(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
sda.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行存储过程,并返回DataSet数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <returns></returns>
public static DataSet getDataSetByPro(string proName)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
sda.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行存储过程,并返回DataSet数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataSet getDataSetByProparm(string proName, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
cmd.Dispose();
sda.Dispose();
conn.Close();
conn.Dispose();
}
}
#endregion
#region ---------------执行SQL语句或存储过程,返回DataTable数据集-------------
/// <summary>
/// 说 明: 返回数据集的表的集合
/// 返回值: 数据源的数据表
/// 参 数: myCmd 执行SQL语句命令的SqlCommand对象,TableName返回的数据表名称
/// </summary>
public DataTable getDataSetByComm(SqlCommand myComm, string TableName)
{
SqlDataAdapter adapt;//声明一个SqlDataAdapter
DataSet ds = new DataSet();//实列化一个DataSet
try
{
if (myComm.Connection.State != ConnectionState.Open)//如果myComm没打开就打开它
{
myComm.Connection.Open();
}
adapt = new SqlDataAdapter(myComm);//给adapt赋值
adapt.Fill(ds, TableName);//填充adapt
return ds.Tables[TableName];//返回表
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (myComm.Connection.State == ConnectionState.Open)
{
myComm.Connection.Close();
myComm.Dispose();
}
}
}
/// <summary>
/// 执行SQL语句,并返回DataTable数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static DataTable getDataTableBySql(string strSql)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
dt.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句,并返回DataTable数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataTable getDataTableBySqlparm(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
sda.SelectCommand = cmd;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
dt.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL存储过程,并返回DataTalbe数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <returns></returns>
public static DataTable getDataTableByPro(string proName)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
dt.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL存储过程,并返回DataTable数据集
/// </summary>
/// <param name="proName">存储过程名</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataTable getDataTableByProparm(string proName, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
try
{
cmd.CommandText = proName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
cmd.Dispose();
dt.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句查询数据源,根据start与num将数据集分页并返回名称为info的DataTable
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="start">查询数据集的起始位置,从0开始</param>
/// <param name="num">查询数量</param>
/// <returns></returns>
public static DataTable getDataTableOfPage(string strSql, int start, int num)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(ds, start, num, "info");
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
cmd.Dispose();
ds.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行SQL语句查询数据源,根据start与num将数据集分页并返回名称为info的DataTable
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="start">查询数据集的起始位置,从0开始</param>
/// <param name="num">查询数量</param>
/// <param name="parms">SQL输入参数</param>
/// <returns></returns>
public static DataTable getDataTableOfPageparm(string strSql, int start, int num, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlDataAdapter sda = new SqlDataAdapter();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
sda.SelectCommand = cmd;
sda.Fill(ds, start, num, "info");
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
sda.Dispose();
cmd.Dispose();
ds.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 返回查询结果DataTable前几条(公用方法)
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="num">前几条?</param>
/// <param name="filed">数据库过滤字段数组</param>
/// <param name="parametersList">参数名数组</param>
/// <param name="filetype">sqldbtype类型数组</param>
/// <param name="values">参数值数组</param>
/// <param name="isorder">正序还是倒序?正序为true</param>
/// <param name="orderValue">排序字段</param>
public static DataTable getInfo(string tablename, int num, string[] filed, string[] parametersList, SqlDbType[] filetype, string[] values, bool isorder, string orderValue)
{
StringBuilder result = new StringBuilder();
DataTable dt = new DataTable();
if (filed.Length == parametersList.Length && filed.Length == filetype.Length)
{
string order = string.Empty;
if (!"".Equals(orderValue))
{
order += " order by " + orderValue;
if (!isorder)
{
order += " desc";
}
}
string sql = "select top " + num + " * from " + tablename + " where ";
SqlParameter[] par = new SqlParameter[values.Length];
if (filed.Length > 0)
{
for (int i = 0; i < filed.Length; i++)
{
sql += filed[i] + " =@" + parametersList[i] + " and ";
}
for (int i = 0; i < values.Length; i++)
{
par[i] = new SqlParameter("@" + parametersList[i], filetype[i]);
par[i].Value = values[i];
}
sql = sql.Substring(0, sql.Length - 4);
sql += order;
}
else
{
sql += "1=1 ";
sql += order;
}
SqlConnection con = null;
DataSet ds = null;
SqlDataAdapter dp = null;
SqlCommand cmd = null;
try
{
con = new SqlConnection(_connString);
cmd = new SqlCommand(sql, con);
for (int i = 0; i < par.Length; i++)
{
cmd.Parameters.Add(par[i]);
}
dp = new SqlDataAdapter();
dp.SelectCommand = cmd;
ds = new DataSet();
dp.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return dt;
}
/// <summary>
/// 根据条件查询并且绑定 并返回数据表集
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="filed">数据库过滤字段数组</param>
/// <param name="conditions">查询逻辑条件</param>
/// <param name="parametersList">参数名数组</param>
/// <param name="filetype">sqldbtype类型数组</param>
/// <param name="values">参数值数组</param>
/// <param name="isorder">正序还是倒序?正序为true</param>
/// <param name="orderValue">排序字段</param>
/// <param name="dl">绑定容器:DataList GridView Repeater</param>
/// <param name="isPage">是否分页</param>
/// <param name="pageSize">分页条数</param>
/// <param name="pageIndex">页数</param>
/// <param name="yemian">跳转的页面</param>
/// <returns>数据表集</returns>
public static DataTable getDataTablePage(string tablename, string[] filed, string[] conditions, string[] parametersList, SqlDbType[] filetype, string[] values, bool isorder, string orderValue, bool isPage, int pageSize, int pageIndex, string yemian)
{
StringBuilder result = new StringBuilder();
DataTable dt = new DataTable();
if (filed.Length == parametersList.Length && filed.Length == filetype.Length && filed.Length == conditions.Length)
{
string order = string.Empty;
if (!"".Equals(orderValue))
{
order += " order by " + orderValue;
if (!isorder)
{
order += " desc";
}
}
string sql = "select * from " + tablename + " where ";
SqlParameter[] par = new SqlParameter[values.Length];
if (filed.Length > 0)
{
for (int i = 0; i < filed.Length; i++)
{
sql += filed[i] + conditions[i] + " @" + parametersList[i] + " and ";
}
for (int i = 0; i < values.Length; i++)
{
par[i] = new SqlParameter("@" + parametersList[i], filetype[i]);
par[i].Value = values[i];
}
sql = sql.Substring(0, sql.Length - 4);
sql += order;
}
else
{
sql += "1=1 ";
sql += order;
}
SqlConnection con = null;
DataSet ds = null;
SqlDataAdapter dp = null;
SqlCommand cmd = null;
try
{
con = new SqlConnection(_connString);
cmd = new SqlCommand(sql, con);
for (int i = 0; i < par.Length; i++)
{
cmd.Parameters.Add(par[i]);
}
dp = new SqlDataAdapter();
dp.SelectCommand = cmd;
ds = new DataSet();
if (isPage)
{
if (yemian.IndexOf("?") == -1)
{
yemian += "?";
}
else
{
yemian += "&";
}
int start = (pageIndex - 1) * pageSize;
int end = pageSize;
dp.Fill(ds, start, end, "内容表");
DataSet ds1 = new DataSet();
dp.Fill(ds1);
int total = ds1.Tables[0].Rows.Count;//总条数
int page_total = (total % pageSize) == 0 ? (total / pageSize) : (total / pageSize + 1);//总页数
int end_page = page_total;//最后一页
result.Append("<div style=\"height: 16px; padding-top: 5px; float: left; font-size:12px; font-family:Verdana;\">");
result.Append("共" + page_total + "页 | 当前第" + pageIndex + "页 |");
if (pageIndex > 1)
{
result.Append(" <a href=\"" + yemian + "pageIndex=1\" style=\" text-decoration:none\">首页</a> |");
}
else
{
result.Append(" 首页 |");
}
if (pageIndex > 1)
{
result.Append(" <a href=\"" + yemian + "pageIndex=" + (pageIndex - 1) + "\" style=\" text-decoration:none\">上一页</a> |");
}
else
{
result.Append(" 上一页 |");
}
if (pageIndex < end_page)
{
result.Append(" <a href=\"" + yemian + "pageIndex=" + (pageIndex + 1) + "\" style=\"text-decoration:none\">下一页</a> |");
}
else
{
result.Append(" 下一页 |");
}
if (pageIndex < page_total)
{
result.Append(" <a href=\"" + yemian + "pageIndex=" + page_total + "\" style=\" text-decoration:none\">尾页</a> ");
}
else
{
result.Append(" 尾页 ");
}
result.Append("<script type=\"text/javascript\">");
result.Append("function get1(){");
result.Append("var va=document.getElementById(\"name1\").value;");
result.Append(" if(va <=" + page_total + " && va >=1){");
result.Append("location.href=\"" + yemian + "pageIndex=\"+va;}");
result.Append("}</script>");
result.Append(" 转到 <input type=\"text\" style=\"width:30px; height:12px;\" id=\"name1\"/> 页 ");
result.Append("<a οnclick=\"get1();\" href=\"#\" style=\"text-decoration: none\">GO</a>");
result.Append("</div>");
}
else
{
dp.Fill(ds);
}
dt = ds.Tables[0];
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return dt;
}
/// <summary>
/// 根据条件查询分页 并返回数据表集
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="filed">数据库过滤字段数组</param>
/// <param name="parametersList">参数名数组</param>
/// <param name="filetype">sqldbtype类型数组</param>
/// <param name="values">参数值数组</param>
/// <param name="isorder">正序还是倒序?正序为true</param>
/// <param name="orderValue">排序字段</param>
/// <param name="isPage">是否分页</param>
/// <param name="pageSize">分页条数</param>
/// <param name="pageIndex">页数</param>
/// <returns>数据表集</returns>
public static DataTable getDataTableByPage(string tablename, string[] filed, string[] parametersList, SqlDbType[] filetype, string[] values, bool isorder, string orderValue, bool isPage, int pageSize, int pageIndex)
{
DataTable dt = new DataTable();
if (filed.Length == parametersList.Length && filed.Length == filetype.Length)
{
string order = string.Empty;
if (!"".Equals(orderValue))
{
order += " order by " + orderValue;
if (!isorder)
{
order += " desc";
}
}
string sql = "select * from " + tablename + " where ";
SqlParameter[] par = new SqlParameter[values.Length];
if (filed.Length > 0)
{
for (int i = 0; i < filed.Length; i++)
{
sql += filed[i] + " =@" + parametersList[i] + " and ";
}
for (int i = 0; i < values.Length; i++)
{
par[i] = new SqlParameter("@" + parametersList[i], filetype[i]);
par[i].Value = values[i];
}
sql = sql.Substring(0, sql.Length - 4);
sql += order;
}
else
{
sql += "1=1 ";
sql += order;
}
SqlConnection con = null;
DataSet ds = null;
SqlDataAdapter dp = null;
SqlCommand cmd = null;
try
{
con = new SqlConnection(_connString);
cmd = new SqlCommand(sql, con);
for (int i = 0; i < par.Length; i++)
{
cmd.Parameters.Add(par[i]);
}
dp = new SqlDataAdapter();
dp.SelectCommand = cmd;
ds = new DataSet();
if (isPage)
{
int start = (pageIndex - 1) * pageSize;
int end = pageSize;
dp.Fill(ds, start, end, "新闻表");
}
else
{
dp.Fill(ds);
}
dt = ds.Tables[0];
}
catch (Exception exx)
{
throw exx;
}
finally
{
con.Close();
}
}
return dt;
}
#endregion
#region ---------------执行SQL语句或存储过程,返回新插入行的ID数据集-------------
/// <summary>
/// 执行SQL,返回新插入行的ID
/// </summary>
/// <param name="strSql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static int getBySqlReturnID(string strSql, SqlParameter[] parms)
{
SqlConnection conn = getSQLConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
foreach (SqlParameter par in parms)
{
cmd.Parameters.Add(par);
}
int i = int.Parse(cmd.ExecuteScalar().ToString());
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 根据条件插入返回ID(公共方法)
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="filed">插入字段</param>
/// <param name="parametersList">插入参数名</param>
/// <param name="filetype">参数类型</param>
/// <param name="values">参数值</param>
/// <returns>返回ID</returns>
public static int insertreturnid(string tablename, string[] filed, string[] parametersList, SqlDbType[] filetype, string[] values)
{
int result = 0;
string sql = "insert into " + tablename;
SqlParameter[] par = new SqlParameter[values.Length];
if (filed.Length > 0)
{
sql += "(";
for (int i = 0; i < filed.Length; i++)
{
sql += filed[i] + " , ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ")";
sql += " values(";
for (int i = 0; i < parametersList.Length; i++)
{
sql += "@" + parametersList[i] + " , ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ") select @@identity as num";//@@identity全局变量或者插入新行的标示字段如ID
for (int i = 0; i < values.Length; i++)
{
if (values[i].ToLower().Contains("script") || values[i].Contains("iframe") || values[i].Contains("frame"))
{
HttpContext.Current.Response.Write("<script>alert('检测到有敏感字符,请重新输入!');</script>");
return 0;
}
par[i] = new SqlParameter("@" + parametersList[i], filetype[i]);
par[i].Value = values[i];
}
}
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader sd = null;
try
{
//con = new SqlConnection(_connString);
SqlConnection con = getSQLConnection();
con.Open();
cmd = new SqlCommand(sql, con);
for (int i = 0; i < par.Length; i++)
{
cmd.Parameters.Add(par[i]);
}
sd = cmd.ExecuteReader();
if (sd.Read())
{
result = int.Parse(sd[0].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return result;
}
#endregion
#region ---------------执行SQL语句或存储过程,返回数据集的第一行第一列-------------
/// <summary>
/// 执行SQL语句,返回数据集的第一行第一列
/// </summary>
/// <param name="strSql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static string getValueByFiled(string strSql, SqlParameter[] parms)
{
string result = "0";
try
{
DataTable dt = new DataTable();
dt = GlobalSystem.getDataTableBySql(strSql, parms);
result = dt.Rows[0][0].ToString();
}
catch (Exception E)
{
throw E;
}
return result;
}
/// <summary>
/// 返回object 无数组参数,第一行的第一列
/// </summary>
/// <param name="str">SQL语句</param>
/// <returns></returns>
public static object getExecuteScalar1(string str)
{
object i;
try
{
using (SqlConnection sqlConnection = new SqlConnection())
{
string DB = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
sqlConnection.ConnectionString = DB;
sqlConnection.Open();
SqlCommand SqlCommand = new SqlCommand(str, sqlConnection);
i = SqlCommand.ExecuteScalar();
sqlConnection.Close();
return i;
}
}
catch (Exception E)
{
throw E;
}
}
#endregion
#endregion