using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;
namespace domain.UI.News.Controls
{
public partial class NewsShow : System.Web.UI.UserControl
{
private domain.SQLDB.DbAccess _db;
private string _outModel;
private string _inModel;
private string _columns;
private string _sql;
private string _split;
private string _firstModel;
private string _firstColumn;
public domain.SQLDB.DbAccess Db
{
set { this._db = value; }
get { return this._db; }
}
public string Sql
{
set { this._sql = value; }
get {
if (string.IsNullOrEmpty(_sql))
_sql = "";
return this._sql; }
}
public string OutModel
{
set { this._outModel = value; }
get {
if (string.IsNullOrEmpty(_outModel))
_outModel = "";
return this._outModel; }
}
public string InModel
{
set { this._inModel = value; }
get {
if (string.IsNullOrEmpty(_inModel))
_inModel = "";
return this._inModel; }
}
public string Columns
{
set { this._columns = value; }
get {
if (string.IsNullOrEmpty(_columns))
_columns = "";
return this._columns; }
}
public string Split
{
set { this._split = value; }
get
{
if (string.IsNullOrEmpty(_split))
_split = "";
return this._split;
}
}
public string FirstModel
{
set { this._firstModel = value; }
get
{
if (string.IsNullOrEmpty(_firstModel))
_firstModel = "";
return this._firstModel;
}
}
public string FirstColumn
{
set { this._firstColumn = value; }
get
{
if (string.IsNullOrEmpty(_firstColumn))
_firstColumn = "";
return this._firstColumn;
}
}
protected void Page_Load(object sender, EventArgs e)
{
}
public string getTableData()
{
System.Text.StringBuilder tempSb = new System.Text.StringBuilder("");
if (FirstModel.Length > 0)
goto HasFirst;
else
goto NoFirst;
HasFirst:
{
string[] columns = Columns.Split(',');
DataTable dt = new DbAccess().GetTable(Sql);
if (dt != null)
{
for (int j = 0; j < dt.Rows.Count;j++)
{
if (j == 0)
{
columns = FirstColumn.Split(',');
DataRow dr = dt.Rows[j];
string[] tempStr = new string[columns.Length];
for (int i = 0; i < tempStr.Length; i++)
{
tempStr[i] = Convert.ToString(dr[columns[i]]);
}
tempSb.AppendFormat(FirstModel,tempStr).Append(_split);
}
else
{
DataRow dr = dt.Rows[j];
string[] tempStr = new string[columns.Length];
for (int i = 0; i < tempStr.Length; i++)
{
tempStr[i] = Convert.ToString(dr[columns[i]]);
}
tempSb.AppendFormat(InModel, tempStr).Append(_split);
}
}
}
}
NoFirst: {
string[] columns = Columns.Split(',');
DataTable dt = new DbAccess().GetTable(Sql);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
string[] tempStr = new string[columns.Length];
for (int i = 0; i < tempStr.Length; i++)
{
tempStr[i] = Convert.ToString(dr[columns[i]]);
}
tempSb.AppendFormat(InModel, tempStr).Append(_split);
}
}
}
return OutModel.Length > 0 ? (string.Format(OutModel, (tempSb.Length > 0 ? (tempSb.Remove(tempSb.Length - Split.Length, Split.Length).ToString()) : (tempSb.ToString())))) : (tempSb.Length > 0 ? (tempSb.Remove(tempSb.Length - Split.Length, Split.Length).ToString()) : (tempSb.ToString()));
}
}
public class DbAccess
{
SqlConnection conn = null;
SqlCommand cmd = null;
public DbAccess()
{
//
// TODO: 在此处添加构造函数逻辑
//
conn = new SqlConnection();
conn.ConnectionString = Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]);
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
}
public DbAccess(string constr)
{
//
// TODO: 在此处添加构造函数逻辑
//
conn = new SqlConnection();
conn.ConnectionString = constr;// "initial catalog=idyan_new;data source=.;user id=bt;password=btbtbtbt";
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
}
/// <summary>
/// 获取数据根据sql语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable GetTable(string sql)
{
DataSet ds = new DataSet();
try
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return ds.Tables[0] ?? new DataTable();
}
/// <summary>
/// 获取数据根据sql语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet GetDataSet(string sql)
{
DataSet ds = new DataSet();
try
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return ds;
}
/// <summary>
/// 获取数据根据sql语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet GetDataSet(string sql, SqlParameter[] pas)
{
DataSet ds = new DataSet();
try
{
cmd.Parameters.Clear();
cmd.CommandText = sql;
foreach (SqlParameter pa in pas)
{
cmd.Parameters.Add(pa);
}
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return ds;
}
/// <summary>
/// 获取数据根据sql语句 带参数 的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public DataTable GetTable(string sql, params SqlParameter[] pas)
{
DataSet ds = new DataSet();
try
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
da.Fill(ds);
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return ds.Tables[0] ?? new DataTable();
}
/// <summary>
/// 获取数据根据sql语句 带参数 的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public DataTable GetProcTable(string procname, params SqlParameter[] pas)
{
DataSet ds = new DataSet();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
//cmd.CommandText = sql;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
da.Fill(ds);
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return ds.Tables[0] ?? new DataTable();
}
/// <summary>
/// 获取数据根据sql语句 带参数 的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public DataTable GetProcCursorTable(string procname, params SqlParameter[] pas)
{
DataSet ds = new DataSet();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
//cmd.CommandText = sql;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
da.Fill(ds);
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return ds.Tables[1] ?? new DataTable();
}
/// <summary>
/// 获取数据根据sql语句 带参数 的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public int GetProcState(string procname, params SqlParameter[] pas)
{
int state = 0;
try
{
OpenConn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
cmd.ExecuteNonQuery();
CloseConn();
state = Convert.ToInt32(pas[pas.Length - 1].Value);
}
catch
{
return 0;
}
return state;
}
/// <summary>
/// 获取数据根据sql语句 带参数 的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public int GetProcStateNo(string procname, params SqlParameter[] pas)
{
int state = 0;
try
{
OpenConn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
cmd.ExecuteNonQuery();
CloseConn();
state = 1;
}
catch (Exception ex)
{
return 0;
}
return state;
}
/// <summary>
/// 根据sql语句返回跟新状态
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool GetState(string sql)
{
bool succ = false;
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
OpenConn();
succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
CloseConn();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return false;
}
return succ;
}
/// <summary>
/// 根据sql语句返回跟新状态带参数的
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="pas">参数的集合</param>
/// <returns></returns>
public bool GetState(string sql, params SqlParameter[] pas)
{
bool succ = false;
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
OpenConn();
succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
CloseConn();
}
catch (Exception ex)
{
return false;
}
return succ;
}
/// <summary>
/// 根据sql语句返回第一个单元格的数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public string GetOne(string sql)
{
string res = "";
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
OpenConn();
res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
CloseConn();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return res;
}
/// <summary>
/// 根据sql语句返回第一个单元格的数据带参数的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public string GetOne(string sql, params SqlParameter[] pas)
{
string res = "";
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
OpenConn();
res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
CloseConn();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return res;
}
/// <summary>
/// 返回数据的DataReader
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public SqlDataReader GetDataReader(string sql)
{
SqlDataReader dr = null;
try
{
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.CommandText = sql;
dr = cmd.ExecuteReader();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return dr;
}
/// <summary>
/// 返回数据的DataReader带参数的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public SqlDataReader GetDataReader(string sql, params SqlParameter[] pas)
{
SqlDataReader dr = null;
try
{
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
cmd.CommandText = sql;
dr = cmd.ExecuteReader();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return dr;
}
/// <summary>
/// 事务处理函数
/// </summary>
/// <param name="al"></param>
/// <returns></returns>
public bool GetTranState(ArrayList al)
{
cmd.CommandType = CommandType.Text;
OpenConn();
SqlTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
try
{
for (int i = 0; i < al.Count; i++)
{
cmd.CommandText = Convert.ToString(al[i]);
cmd.ExecuteNonQuery();
}
tran.Commit();
CloseConn();
}
catch (Exception ex)
{
tran.Rollback();
return false;
}
return true;
}
/// <summary>
/// 事务处理函数
/// </summary>
/// <param name="al"></param>
/// <returns></returns>
public bool GetTranStateParameter(ArrayList al)
{
cmd.CommandType = CommandType.Text;
OpenConn();
SqlTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
try
{
for (int i = 0; i < al.Count; i++)
{
SqlTranModel stm = (SqlTranModel)al[i];
cmd.CommandText = Convert.ToString(stm.Sql);
SqlParameter[] pas = stm.Pas;
cmd.Parameters.Clear();
foreach (SqlParameter temp in pas)
{
cmd.Parameters.Add(temp);
}
cmd.ExecuteNonQuery();
}
tran.Commit();
CloseConn();
}
catch (Exception ex)
{
tran.Rollback();
return false;
}
return true;
}
/// <summary>
/// 分页函数
/// </summary>
/// <param name="pagesize"></param>
/// <param name="columns"></param>
/// <param name="tablename"></param>
/// <param name="pid"></param>
/// <param name="order"></param>
/// <param name="current"></param>
/// <returns></returns>
public DataTable GetPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order)
{
current = current - 1 >= 0 ? (current - 1) : (0);
string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5} order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
return GetTable(sql);
}
/// <summary>
/// 分页存储过程的调用
/// </summary>
/// <param name="current"></param>
/// <param name="pagesize"></param>
/// <param name="columns"></param>
/// <param name="tablename"></param>
/// <param name="pid"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetProcPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string ordertype)
{
SqlParameter[] pas = { new SqlParameter("@PageIndex", current), new SqlParameter("@PageSize", pagesize), new SqlParameter("@Columns", columns), new SqlParameter("@Tablename", tablename), new SqlParameter("@Where", where), new SqlParameter("@Order", order), new SqlParameter("@OrderType", ordertype), new SqlParameter("@Pid", pid) };
return GetProcTable("Pages", pas);
}
/// <summary>
/// 分页存储过程的调用
/// </summary>
/// <param name="current"></param>
/// <param name="pagesize"></param>
/// <param name="columns"></param>
/// <param name="tablename"></param>
/// <param name="pid"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetProcData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
{
SqlParameter[] pas = { new SqlParameter("@TableNames", SqlDbType.NVarChar, 200), new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 100), new SqlParameter("@Order", SqlDbType.NVarChar, 200), new SqlParameter("@CurrentPage", SqlDbType.Int), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@Fields", SqlDbType.NVarChar, 800), new SqlParameter("@Filter", SqlDbType.NVarChar, 1000), new SqlParameter("@ResultCount", SqlDbType.NVarChar, 12), new SqlParameter("@distinct", SqlDbType.NVarChar, 12) };
pas[0].Value = tablename;
pas[1].Value = pid;
pas[2].Value = order;
pas[3].Value = current;
pas[4].Value = pagesize;
pas[5].Value = columns;
pas[6].Value = where;
pas[7].Value = resultCount;
pas[8].Value = distinct;
return this.GetProcTable("Pages", pas);
}
/// <summary>
/// 分页存储过程的调用
/// </summary>
/// <param name="current"></param>
/// <param name="pagesize"></param>
/// <param name="columns"></param>
/// <param name="tablename"></param>
/// <param name="pid"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetProcAdminData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
{
SqlParameter[] pas = { new SqlParameter("@TableNames", SqlDbType.NVarChar, 200), new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 100), new SqlParameter("@Order", SqlDbType.NVarChar, 200), new SqlParameter("@CurrentPage", SqlDbType.Int), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@Fields", SqlDbType.NVarChar, 800), new SqlParameter("@Filter", SqlDbType.NVarChar, 200), new SqlParameter("@ResultCount", SqlDbType.NVarChar, 24), new SqlParameter("@Distinct", SqlDbType.NVarChar, 12) };
pas[0].Value = tablename;
pas[1].Value = pid;
pas[2].Value = order;
pas[3].Value = current;
pas[4].Value = pagesize;
pas[5].Value = columns;
pas[6].Value = where;
pas[7].Value = resultCount;
pas[8].Value = distinct;
return this.GetProcTable("Pages", pas);
}
/// <summary>
/// 打开连接
/// </summary>
public void OpenConn()
{
if (conn.State != ConnectionState.Open)
{
try
{
conn.Open();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return;
}
}
}
/// <summary>
/// 关闭连接
/// </summary>
public void CloseConn()
{
if (conn.State != ConnectionState.Closed)
{
try
{
conn.Close();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return;
}
}
}
public void DisposeConn()
{
try
{
if (conn.State != ConnectionState.Closed)
{
try
{
conn.Close();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return;
}
}
}
catch
{
}
}
/// <summary>
/// 弹出错误的信息
/// </summary>
/// <param name="err"></param>
public void ShowError(string err)
{
// System.Web..Current.Response.Write(Script(err, ""));
}
/// <summary>
/// 显示信息
/// </summary>
/// <param name="err"></param>
public void ShowMessage(string mes, string loc)
{
// System.Web.HttpContext.Current.Response.Write(Script(mes, loc));
}
/// <summary>
/// javascript脚本
/// </summary>
/// <param name="mess"></param>
/// <param name="loc"></param>
/// <returns></returns>
public string Script(string mess, string loc)
{
StringBuilder sb = new StringBuilder();
sb.Append("<script language='javascript'>");
sb.Append("alert('");
sb.Append(mess);
sb.Append("');");
sb.Append(loc);
sb.Append("</script>");
return sb.ToString();
}
/// <summary>
/// 弹出错误的信息
/// </summary>
/// <param name="err"></param>
public static void ShowErrorstatic(string err)
{
// System.Web.HttpContext.Current.Response.Write(DbAccess.Scriptstatic(err, ""));
}
/// <summary>
/// 显示信息
/// </summary>
/// <param name="err"></param>
public static void ShowMessagestatic(string mes, string loc)
{
//System.Web.HttpContext.Current.Response.Write(DbAccess.Scriptstatic(mes, loc));
}
//<summary>
//javascript脚本
//</summary>
//<param name="mess"></param>
//<param name="loc"></param>
//<returns></returns>
public static string Scriptstatic(string mess, string loc)
{
StringBuilder sb = new StringBuilder();
sb.Append("<script language='javascript'>");
sb.Append("alert('");
sb.Append(mess);
sb.Append("');");
sb.Append(loc);
sb.Append("</script>");
return sb.ToString();
}
}
public class SqlTranModel
{
private string _sql;
public string Sql
{
set { this._sql = value; }
get { return this._sql; }
}
private SqlParameter[] _pas;
public SqlParameter[] Pas
{
set { this._pas = value; }
get { return this._pas; }
}
}
}
