/// <summary>
/// 产生一个全局唯一标识符
/// </summary>
/// <returns>返回全局唯一标识符</returns>
public static Guid NewGuid(DataAccessType pdbtype)
{
switch (pdbtype)
{
case DataAccessType.SqlClient:
return(SqlQueryDAO.NewGuid());
case DataAccessType.OLEDB:
return(OleDbQueryDAO.NewGuid());
case DataAccessType.ODPNet:
return(OdpQueryDAO.NewGuid());
default:
throw(new Exception("此种访问方式没有定义!"));
}
}
private SqlQueryDAO SqlDao = new SqlQueryDAO(); //定义SqlQueryDAO的对象
#endregion Fields
#region Methods
/// <summary>
/// 获取所选SQL语句的查询结果
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public JsonResult GetSelectedData(int id)
{
TbSqlQuery tempQuery = new SqlQueryDAO().GetByID(id); //查找该id下的数据
List<object> resultList = new List<object>(); //定义一个object类型的集合
if (tempQuery != null)
{
string mySQL = tempQuery.SSqlStr; //获取SQL语句
Database db = DBHelper.CreateDataBase("PlatformData");//打开数据库
DbCommand cmd = db.GetSqlStringCommand(mySQL);
List<string> headerlist = new List<string>(); //定义表头对象集
using (DataSet myDs = db.ExecuteDataSet(cmd))
{
DataTable dt = myDs.Tables[0];
string tempheader;
for (int i = 0; i < dt.Columns.Count; i++) //取表头
{
tempheader = dt.Columns[i].ToString();
headerlist.Add(tempheader);
}
int RowsCount = dt.Rows.Count; //获取表的总行数
int ColCount = dt.Columns.Count;//获取表的总列数
for (int i = 0; i < RowsCount; i++)//读取每一行的数据
{
List<string> tempList = new List<string>();//定义一个string类型的集合，保存每一行的数据
for (int j = 0; j < ColCount; j++) //读取每一列的数据
{
string temp = dt.Rows[i][j].ToString();
tempList.Add(temp); //把每一列的数据加到tempList中
}
resultList.Add(tempList); //把每一行的数据加到resultList中
}
}
return Json(new { dataContent = resultList, dataHeader = headerlist }, JsonRequestBehavior.AllowGet);
}
else //未找到
{
return null;
}
}
/// <summary>
/// 查询所有的SQL语句
/// </summary>
/// <param name="firstResult">起始值</param>
/// <param name="pagesize">页面大小</param>
/// <param name="orderBy">排序列</param>
/// <param name="condition">查询条件</param>
/// <returns></returns>
public Object Search(int firstResult, int pagesize, string orderBy, string condition)
{
int totalCount = 0; //总的数据记录条数I_NoticesType
condition = HttpUtility.UrlDecode(condition);
if (condition.Length > 3) //说明不是默认查找,而是带有参数的查找
{
string substringfront = condition.Substring(0, 4); //获取condition的前四个字符串
int substringfrontlength = substringfront.Length;//获取子字串的长度
int conLength = condition.Length; //获取字符串的长度
string substringbehind = condition.Substring(5, conLength - substringfrontlength - 1);//获取condition的其余条件
if (substringfront == "(Id") //判断是否是按id查询
{
substringfront = "(Id"; //修正id
condition = substringfront + substringbehind; //重新组合查询条件
}
}
List<NewSqlQuery> resultList = new List<NewSqlQuery>();
IList<TbSqlQuery> QueryList = new SqlQueryDAO().GetByPageDataBase(firstResult, pagesize, orderBy, condition, out totalCount);
if (QueryList.Count > 0)
{
foreach (var item in QueryList)
{
NewSqlQuery tempNewSqlQuery = new NewSqlQuery();
int id = int.Parse(item.ICreateID.ToString());
TbSysUser tempUser = new SysUserDAO().GetByID(id);
tempNewSqlQuery.Id = item.Id;
tempNewSqlQuery.SSqlName = item.SSqlName;
tempNewSqlQuery.SSqlStr = item.SSqlStr;
tempNewSqlQuery.ICreateID = item.ICreateID;
tempNewSqlQuery.SUserName = tempUser.SUserName;
tempNewSqlQuery.DCreate = item.DCreate;
tempNewSqlQuery.ISort = item.ISort;
resultList.Add(tempNewSqlQuery);
}
}
return this.Json(new
{
DataCount = totalCount,
Data = resultList
}, JsonRequestBehavior.AllowGet);
}
public void PostDataToExcel(int id)
{
try
{
TbSqlQuery tempQuery = new SqlQueryDAO().GetByID(id);
if (tempQuery != null)
{
string mySql = tempQuery.SSqlStr; //获取SQL语句
Database db = DBHelper.CreateDataBase("PlatformData");
DbCommand cmd = db.GetSqlStringCommand(mySql);
using (DataSet myDs = db.ExecuteDataSet(cmd))
{
//excel
ExcelHelper.ExportDataSetToExcel(myDs, tempQuery.SSqlName + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls", tempQuery.SSqlName);
}
}
}
catch (Exception e2)
{
}
}
