internal static DataSet ExecuteDataSet(string CommandName, Int32 tipo, DbParameter[] param = null, DbTransaction transaction = null)
{
try
{
db = SetEnviroment(tipo);
using (DbCommand cmd = db.GetStoredProcCommand(CommandName))
{
if (param != null)
{
cmd.Parameters.AddRange(param);
}
//db.AddInParameter(cmd, "dni", DbType.String,"41856906");
DataSet ds = null;
if (transaction != null)
{
ds = db.ExecuteDataSet(cmd, transaction);
}
else
{
ds = db.ExecuteDataSet(cmd);
}
cmd.Dispose();
return(ds);
}
}
catch (Exception ex)
{
throw new Exception("Error Inesperado=>" + ex.Message, ex);
}
}
/// <summary>
/// ExecuteDataSet 返回结果集DataSet
/// </summary>
/// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>返回一个包含结果的DataSet；失败则返回null</returns>
public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
try
{
DataSet ds = new DataSet();
Open();
if (cmdType == CommandType.StoredProcedure)
{
mDbCommand = mDatabase.GetStoredProcCommand(cmdText);
}
else
{
mDbCommand = mDatabase.GetSqlStringCommand(cmdText);
}
if ((commandParameters != null) && (commandParameters.Length > 0))
{
foreach (IDbDataParameter mParameter in commandParameters)
{
mDatabase.AddParameter(mDbCommand, mParameter.ParameterName, mParameter.DbType, mParameter.Direction, mParameter.SourceColumn, mParameter.SourceVersion, mParameter.Value);
}
}
ds = mDatabase.ExecuteDataSet(mDbCommand);
return(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Close();
}
}
public void btnDataSetToGrid_Click(System.Object sender, System.EventArgs e)
{
//ExecuteDataSet
DataSet ds = db.ExecuteDataSet(CommandType.Text, "SELECT * FROM SampleData");
//Bind the default table in the dataset to the datagridview
dgvSampleData.DataSource = ds.Tables[0];
}
/// <summary>
/// 执行查询语句，返回DataTable
/// </summary>
/// <param name="dc">查询语句</param>
/// <param name="db">操作目标数据库</param>
/// <returns>DataTable</returns>
public static DataTable DataTableQuery(DbCommand dc, Database db)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
PrepareCommand(ref dc, db);
ds = db.ExecuteDataSet(dc);
if (ds.Tables.Count > 0)
{
dt = ds.Tables[0];
ds.Dispose();
ds = null;
}
else
{
ds.Dispose();
ds = null;
return null;
}
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
return dt;
}
/// <summary>
/// 获取一个DataSet数据集
/// </summary>
/// <param name="cmdType">命令类型,SQL文本或存储过程</param>
/// <param name="cmdText">SQL语句或存储过程名称</param>
/// <param name="paras">查询参数</param>
/// <returns>DataSet数据集</returns>
public static DataSet ExecuteDataSet(Database db, DbTransaction dbTransaction, CommandType cmdType, string cmdText, params DbParameter[] paras)
{
DbCommand dbcmd = GetDbCommand(db, cmdType, cmdText);
PrepareDbCommand(dbcmd, paras);
DataSet ds = db.ExecuteDataSet(dbcmd, dbTransaction);
return ds;
}
/// <summary>
/// 获取待办事项调转地址
/// </summary>
/// <param name="moduleID"></param>
/// <param name="moduleName"></param>
/// <param name="instanceID"></param>
/// <param name="workflowTasksSetp"></param>
/// <returns></returns>
//private string GetAuditUrl(string moduleID, string moduleName, string instanceID, string workflowTasksSetp, string applyBasisType, string taskStatus)
//{
// string sRtn = string.Empty;
// string showType = basepage.CurrUserInfo().RoleShowType;
// if (moduleName == Constant.TaskCYGYWSP)//车易购业务审批
// {
// switch (workflowTasksSetp)
// {
// case "0":
// if (applyBasisType == "1")
// {
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/ApplyAddPersonage.aspx?Desktop=true&id=" + moduleID + "&showType=" + showType + "');>办理</a>";
// }
// if (applyBasisType == "2")
// {
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/ApplyAddCompany.aspx?Desktop=true&id=" + moduleID + "&showType=" + showType + "');>办理</a>";
// }
// break;
// case "1":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/InstructionsAuditOption1.aspx?Desktop=true&id=" + moduleID + "&ApplyBasis_Type=" + applyBasisType + "&step=" + workflowTasksSetp + "&showType=" + showType + "');>办理</a>";
// break;
// case "2":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/InstructionsAuditOption2.aspx?Desktop=true&id=" + moduleID + "&ApplyBasis_Type=" + applyBasisType + "&step=" + workflowTasksSetp + "&showType=" + showType + "');>办理</a>";
// break;
// case "3":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/InstructionsAuditOption3.aspx?Desktop=true&id=" + moduleID + "&ApplyBasis_Type=" + applyBasisType + "&step=" + workflowTasksSetp + "&showType=" + showType + "');>办理</a>";
// break;
// case "4":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/InstructionsAuditOption4.aspx?Desktop=true&id=" + moduleID + "&ApplyBasis_Type=" + applyBasisType + "&step=" + workflowTasksSetp + "&showType=" + showType + "');>办理</a>";
// break;
// case "5":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/InstructionsAuditOption5.aspx?Desktop=true&id=" + moduleID + "&ApplyBasis_Type=" + applyBasisType + "&step=" + workflowTasksSetp + "&showType=" + showType + "');>办理</a>";
// break;
// case "6":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/InstructionsAuditOption6.aspx?Desktop=true&id=" + moduleID + "&ApplyBasis_Type=" + applyBasisType + "&step=" + workflowTasksSetp + "&showType=" + showType + "');>办理</a>";
// break;
// }
// }
// if (moduleName == Constant.TaskCYGTQHK)//车易购业务提前还款
// {
// switch (workflowTasksSetp)
// {
// case "0":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Repayment/AdvancePayAdd.aspx?Desktop=true&id=" + moduleID + "&step=" + workflowTasksSetp + "');>办理</a>";
// break;
// case "1":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Repayment/AdvancePayApproveAudit.aspx?Desktop=true&id=" + moduleID + "&step=" + workflowTasksSetp + "');>办理</a>";
// break;
// }
// }
// string ExpressInfo_Status = string.Empty;
// string ApplyBasis_Code = string.Empty;
// string ApplyBasis_Name = string.Empty;
// string Department_Name = string.Empty;
// string ApplyBasis_VehicleType = string.Empty;
// string ApplyBasisID = string.Empty;
// if (moduleName == Constant.TaskCYGTQHK || moduleName == Constant.TaskCLIENTFILE || moduleName == Constant.TaskCLIENTFILESHPI)
// {
// Business_ExpressInfo ExpressInfo = new Business_ExpressInfoBLL().Find(p => p.ExpressInfoID == moduleID);
// if (ExpressInfo != null)
// {
// ExpressInfo_Status = ExpressInfo.ExpressInfo_Status;
// ApplyBasisID = ExpressInfo.ExpressInfo_ApplyBasisId;
// View_Business_ApplyBasis ApplyBasis = new View_Business_ApplyBasisBLL().Find(p => p.ApplyBasisId == ExpressInfo.ExpressInfo_ApplyBasisId);
// if (ApplyBasis != null)
// {
// ApplyBasis_Code = ApplyBasis.ApplyBasis_Code;
// ApplyBasis_Name = ApplyBasis.ApplyBasis_Name;
// Department_Name = ApplyBasis.Department_Name;
// ApplyBasis_VehicleType = ApplyBasis.ApplyBasis_VehicleType;
// }
// }
// }
// if (moduleName == Constant.TaskCLIENTFILE)//客户资料管理
// {
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Archives/ExpressInfoAudit.aspx?Desktop=true&id=" + ApplyBasisID + "&ExpressInfoID=" + moduleID + "&ExpressInfo_Status=" + ExpressInfo_Status + "&ApplyBasis_Code=" + ApplyBasis_Code + "&ApplyBasis_Name=" + ApplyBasis_Name + "&Department_Name=" + Department_Name + "&ApplyBasis_VehicleType=" + ApplyBasis_VehicleType + "');>办理</a>";
// }
// if (moduleName == Constant.TaskCLIENTFILESHPI)//快递资料审批
// {
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Archives/ExpressInfoEdit.aspx?Desktop=true&id=" + ApplyBasisID + "&ExpressInfoID=" + moduleID + "&ExpressInfo_Status=" + ExpressInfo_Status + "&ApplyBasis_Code=" + ApplyBasis_Code + "&ApplyBasis_Name=" + ApplyBasis_Name + "&Department_Name=" + Department_Name + "&ApplyBasis_VehicleType=" + ApplyBasis_VehicleType + "');>办理</a>";
// }
// if (moduleName == Constant.TaskCYGYWKHXXBG)//车易购业务客户信息变更
// {
// string ApplyBasis_Type = string.Empty;
// string CustomerInfoChange_ApplyBasisId = string.Empty;
// string CustomerInfoChange_StateCode = string.Empty;
// Business_CustomerInfoChange CustomerInfoChange = new Business_CustomerInfoChangeBLL().Find(p => p.CustomerInfoChange_ID == moduleID);
// CustomerInfoChange_ApplyBasisId = CustomerInfoChange.CustomerInfoChange_ApplyBasisId;
// CustomerInfoChange_StateCode = CustomerInfoChange.CustomerInfoChange_State;
// if (CustomerInfoChange != null)
// {
// View_Business_ApplyBasis ApplyBasis = new View_Business_ApplyBasisBLL().Find(p => p.ApplyBasisId == CustomerInfoChange_ApplyBasisId);
// if (ApplyBasis != null)
// {
// ApplyBasis_Type = ApplyBasis.ApplyBasis_Type.ToString();
// }
// }
// switch (workflowTasksSetp)
// {
// case "0":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/CustomerInfoChangeAdd.aspx?Desktop=true&id=" + moduleID + "&step=" + workflowTasksSetp + "&ApplyBasis_Type=" + ApplyBasis_Type + "&CustomerInfoChange_StateCode=" + CustomerInfoChange_StateCode + "');>办理</a>";
// break;
// case "1":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Business/CustomerInfoApprove.aspx?Desktop=true&id=" + moduleID + "&step=" + workflowTasksSetp + "&ApplyBasis_Type=" + ApplyBasis_Type + "&CustomerInfoChange_ApplyBasisId=" + CustomerInfoChange_ApplyBasisId + "');>办理</a>";
// break;
// }
// }
// if (moduleName == Constant.TaskCYGYWBDXB)//车易购业务保单续保
// {
// switch (workflowTasksSetp)
// {
// case "0":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Warranty/WarrantyRenewalApply.aspx?Desktop=true&BusinessID=RenewalApplyList,Modify&id=" + moduleID + "&step=" + workflowTasksSetp + "');>办理</a>";
// break;
// case "1":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Warranty/WarrantyRenewalApprove.aspx?Desktop=true&id=" + moduleID + "&step=" + workflowTasksSetp + "');>办理</a>";
// break;
// }
// }
// if (moduleName == Constant.TaskCYGYWBDLP)//车易购业务保单理赔
// {
// switch (workflowTasksSetp)
// {
// case "0":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Warranty/ClaimsApply.aspx?Desktop=true&BusinessID=CustomerClaimsList&id=" + moduleID + "&step=" + workflowTasksSetp + "');>办理</a>";
// break;
// case "1":
// sRtn = "<a href=javascript:TaskShowClick('" + taskStatus + "','/Manage/Warranty/ClaimsApprove.aspx?Desktop=true&id=" + moduleID + "&step=" + workflowTasksSetp + "');>办理</a>";
// break;
// }
// }
// return sRtn;
//}
/// <summary>
/// 其他待办事项(PendingMatter_ToRoleName:多角色 userInfo.RoleName:多角色)
/// </summary>
public List <string> LoadPendingMatterInfo()
{
List <string> PendingMatterList = new List <string>();//待办事项集合
string sqlString = "exec GetPendingMatterRecord '" + basepage.CurrUserInfo().RoleID + "','" + basepage.CurrUserInfo().UserID + "'";
Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(sqlString);
DataSet ds = db.ExecuteDataSet(dbCommand);
if (ds != null)
{
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string title = ds.Tables[0].Rows[i]["PendingMatter_Title"].ToString();
string url = ds.Tables[0].Rows[i]["PendingMatter_URL"].ToString();
string iCount = ds.Tables[0].Rows[i]["iCount"].ToString();
title = string.Format(Constant.TesksAuditTitle, title, iCount);
PendingMatterList.Add("<li>·<a href=javascript:showTabs('待办事项','" + url + "');>" + title + "</a></li>");
}
}
}
}
return(PendingMatterList);
}
public DataSet GetAll(string OrderBy, Database db)
{
orderBy = "";
if (OrderBy.Length > 0) orderBy = " Order By " + OrderBy;
sqlText="Select ID,DUYURU_TIP_ID,ORGANIZASYON_ID,BASLIK,BITIS_TARIHI from DUYURU "+orderBy;
dbComm = db.GetSqlStringCommand(sqlText);
return db.ExecuteDataSet(dbComm);
}
public DataSet GetAll(string OrderBy, Database db)
{
orderBy = "";
if (OrderBy.Length > 0) orderBy = " Order By " + OrderBy;
sqlText="Select ORGANIZASYON_ID,KATILIMCI_ID,KAYIT_NO from KATILIMCI_ORGANIZASYON_ARATABLO "+orderBy;
dbComm = db.GetSqlStringCommand(sqlText);
return db.ExecuteDataSet(dbComm);
}
public DataSet GetAll(string OrderBy, Database db)
{
orderBy = "";
if (OrderBy.Length > 0) orderBy = " Order By " + OrderBy;
sqlText="Select ID,AD,SOYAD,TELEFON,EMAIL,UNVAN_ID from KATILIMCI "+orderBy;
dbComm = db.GetSqlStringCommand(sqlText);
return db.ExecuteDataSet(dbComm);
}
public DataSet GetAll(string OrderBy, Database db)
{
orderBy = "";
if (OrderBy.Length > 0) orderBy = " Order By " + OrderBy;
sqlText="Select ID,ORGANIZASYON_TIP_ID,KULUP_ID,AD,TARIH from ORGANIZASYON "+orderBy;
dbComm = db.GetSqlStringCommand(sqlText);
return db.ExecuteDataSet(dbComm);
}
public DataSet GetAll(string OrderBy, Database db)
{
orderBy = "";
if (OrderBy.Length > 0) orderBy = " Order By " + OrderBy;
sqlText="Select ID,UZMANLIK_ADI from UZMANLIK_ALANLARI "+orderBy;
dbComm = db.GetSqlStringCommand(sqlText);
return db.ExecuteDataSet(dbComm);
}
public DataSet GetAll(string OrderBy, Database db)
{
orderBy = "";
if (OrderBy.Length > 0) orderBy = " Order By " + OrderBy;
sqlText="Select KATILIMCI_ID,UZMANLIK_ALANI_ID from KATILIMCI_UZMANLIK_ARATABLO "+orderBy;
dbComm = db.GetSqlStringCommand(sqlText);
return db.ExecuteDataSet(dbComm);
}
public DataSet GetAll(string OrderBy, Database db)
{
orderBy = "";
if (OrderBy.Length > 0) orderBy = " Order By " + OrderBy;
sqlText="Select ID,ACIKLAMA from SECENEK_TIP "+orderBy;
dbComm = db.GetSqlStringCommand(sqlText);
return db.ExecuteDataSet(dbComm);
}
public DataSet GetAll(string OrderBy, Database db)
{
orderBy = "";
if (OrderBy.Length > 0) orderBy = " Order By " + OrderBy;
sqlText="Select SORU_ID,SECENEK_ID from SORU_SECENEK_ARATABLO "+orderBy;
dbComm = db.GetSqlStringCommand(sqlText);
return db.ExecuteDataSet(dbComm);
}
public static DataSet ExecuteDataSet(EntLib.Database db, DbCommand cmd)
{
var f = new Func <DataSet>(() =>
{
return(db.ExecuteDataSet(cmd));
});
return(RetryIt(f, cmd));
}
public static DataSet GetAllOperateLogs(Database db)
{
string cmdText = string.Format("select * from OperateLog order by Id");
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetWindowCameraInfoByRowCol(Database db, int row, int col)
{
string cmdText = string.Format("select * from [WindowCameraInfo] where [row]={0} and [col]={1}", row, col);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetAllDeviceInfo(Database db)
{
string cmdText = string.Format("select * from DeviceInfo order by DeviceId");
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetAllWindowCameraInfo(Database db)
{
string cmdText = string.Format("select * from [WindowCameraInfo] order by [Id]");
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetSystemLogs(Database db, string filter)
{
string cmdText = string.Format("select ID as 索引号, happentime as 发生时间,clientusername as 用户名, systemtypename as 操作类型, content as 内容 from SystemLog {0} order by Id", filter);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetSystemLogTypes(Database db)
{
string cmdText = string.Format("select distinct systemtypename from SystemLog");
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Descripción: Método para obtener todos los registros de la base de datos en un DataSet. Generalmente este obtiene solo los registros que no estan dados de baja.
/// </summary>
/// <param name="pdb">Instancia de la Base de Datos</param>
/// <returns>Devuelve un objeto DataSet con la coleccion de los registros obtenidos en la consulta.</returns>
public System.Data.DataSet m_Load(Microsoft.Practices.EnterpriseLibrary.Data.Database pdb)
{
try
{
DbCommand oCmd = pdb.GetStoredProcCommand("svc_Tblcodigopostal");
DataSet ds = pdb.ExecuteDataSet(oCmd);
return(ds);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetUserInfo(Database db,int userid)
{
string cmdText = string.Format("select * from [UserInfo] where [UserId]={0}", userid);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet ExecuteGeneralSplitPageProcedure(Database db, string fields,string tables,string condition, string ordercolumn,byte ordertype,string pkcolumn,int pageno,int pagesize)
{
string cmdText = string.Format("exec GeneralSplitPageProcedure {0},'{1}','{2}','{3}','{4}',{5},'{6}',{7}", pageno, fields, tables, condition, ordercolumn, ordertype, pkcolumn, pagesize);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetWindowCameraInfoById(Database db, int id)
{
string cmdText = string.Format("select * from [WindowCameraInfo] where [Id]={0}", id);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetAllUsers(Database db)
{
string cmdText = string.Format("select [userID] as 索引号, [username] as 用户名, [usertypename] as 用户类型, [createdatetime] as 创建时间 from [userinfo] order by [userId]");
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Executes the <paramref name="dbCommand"/> and returns the results in a new <see cref="DataSet"/>.
/// </summary>
/// <param name="database">The database to execute the command within.</param>
/// <param name="dbCommand">The command that contains the query to execute.</param>
/// <returns>A <see cref="DataSet"/> containing the results of the command.</returns>
/// <exception cref="System.Exception">The command could not be executed.</exception>
/// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception>
public static DataSet ExecuteDataSet(Enterprise.Database database, DbCommand dbCommand)
{
DataSet results = null;
try
{
results = database.ExecuteDataSet(dbCommand);
}
catch (Exception ex)
{
throw new BusinessException(ex);
}
return(results);
}
public static DataSet GetCapturePicture(Database db, int id)
{
string cmdText = string.Format("select * from CapturePicture where PictureID={0}", id);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetAllCameraIconInfo(Database db)
{
string cmdText = string.Format("select CameraIconInfo.*,CameraInfo.Name as CameraName from (CameraIconInfo inner join CameraInfo on CameraIconInfo.CameraId = CameraInfo.CameraId) order by CameraInfo.CameraId");
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetVideoInfoByCameraDateTime(Database db, int cameraId, DateTime captureBeginTime, DateTime captureEndTime)
{
string cmdText = string.Format("select * from VideoInfo where CameraId={0} and captureBeginTime >='{1}' and captureEndTime<'{2}' order by Id", cameraId,captureBeginTime,captureEndTime);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetVideoInfoByCamera(Database db, int cameraId)
{
string cmdText = string.Format("select * from VideoInfo where CameraId={0} order by Id", cameraId);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetCameraIconInfoByMapId(Database db, int mapId)
{
string cmdText = string.Format("select CameraIconInfo.*,CameraInfo.Name as CameraInfo from (CameraIconInfo inner join CameraInfo on CameraIconInfo.CameraId = CameraInfo.CameraId) where CameraIconInfo.map={0}", mapId);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetAlarmInfoByDeviceId(Database db, int DeviceId)
{
string cmdText = string.Format("select AlarmInfo.*,DeviceInfo.Name as DeviceName from (AlarmInfo inner join DeviceInfo on AlarmInfo.deviceid = DeviceInfo.deviceid) where DeviceInfo.DeviceId={0} order by AlarmId", DeviceId);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetGroupInfoByGroupName(Database db, string groupName)
{
string cmdText = string.Format("select * from GroupInfo where Name='{0}' ", groupName);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetCapturePicture(Database db, int cameraId, DateTime dt)
{
string cmdText = string.Format("select * from CapturePicture where CameraID={0} and Datetime={1}", cameraId,dt);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetGroupSwitchGroupById(Database db, int groupId)
{
string cmdText = string.Format("select * from GroupSwitchGroup where Id={0} ", groupId);
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetSystemParameters(Database db)
{
string cmdText = string.Format("select * from systemParameter;");
try
{
return db.ExecuteDataSet(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Takes a command object and returns a dataset
/// </summary>
/// <param name="sqlCommand">SQLCommand</param>
/// <returns>SqlDataReader</returns>
public DataSet ExecuteDataSet(SqlCommand command)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
int timeout = CommandTimeOut;
if (timeout > 0)
{
command.CommandTimeout = timeout;
}
//Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDatabase();
return(db.ExecuteDataSet((DbCommand)command));
}
public DataSet ExecuteDataSet(string query, CommandType commandType, List <IDbDataParameter> parameters)
{
DbConnection _connection;
_connection = _database.CreateConnection();
var cmd = commandType == CommandType.StoredProcedure ? _database.GetStoredProcCommand(query) : _database.GetSqlStringCommand(query);
cmd.CommandTimeout = 300;
cmd.Connection = _connection;
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
return(_database.ExecuteDataSet(cmd));
}
public bool IsInitialized()
{
bool isInitialized = false;
DataSet ds = new DataSet();
string sql = "SELECT COUNT(*) FROM sysobjects WHERE type = 'U' AND name = 'chpt09_SchemaVersions'";
using (DbCommand dbCmd = db.GetSqlStringCommand(sql))
{
ds = db.ExecuteDataSet(dbCmd);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
int count = (int)ds.Tables[0].Rows[0][0];
isInitialized = (count == 1);
}
}
return(isInitialized);
}
public DataSet ExecuteProcedure(string procedureName, List <SqlParameter> sqlParameters)
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
Microsoft.Practices.EnterpriseLibrary.Data.Database db = factory.Create("dProvider");
DbCommand dbCommand = db.GetStoredProcCommand(procedureName);
if ((sqlParameters != null) && (sqlParameters.Count > 0))
{
foreach (SqlParameter sqlParameter in sqlParameters)
{
db.AddInParameter(dbCommand, sqlParameter.ParameterName, sqlParameter.DbType, sqlParameter.Value);
}
}
DataSet customerDataSet = db.ExecuteDataSet(dbCommand);
return(customerDataSet);
}
/***************** Follow Function Created by levinknight 2006.06.07 *****************/
#region GetAllRules
public string[] GetAllRules()
{
string rules = string.Empty;
DbCommand cmd = dbRules.GetStoredProcCommand("dbo.GetAllRules");
using (DataSet ds = dbRules.ExecuteDataSet(cmd))
{
foreach (DataRow rule in ds.Tables[0].Rows)
{
rules += (string)rule["Name"] + ",";
}
if (rules.Length > 0)
{
rules = rules.Substring(0, rules.Length - 1);
return(rules.Split(','));
}
return(new string[0]);
}
}
/// <summary>
/// 获取待办事项明细信息
/// </summary>
public List <string> LoadPendingMatterDetailInfo()
{
string SQLApplyBasis = string.Empty;
//if (basepage.CurrUserInfo().RoleName.Contains("运营专员"))
//{
// SQLApplyBasis = SQLWhere("ApplyList.aspx");
//}
SQLApplyBasis = SQLWhere("/ApplyList.aspx");
//else if (basepage.CurrUserInfo().RoleName.Contains("初审专员"))
//{ SQLApplyBasis = SQLWhere("AuditInstructionsList.aspx?step=1"); }
//else if (basepage.CurrUserInfo().RoleName.Contains("信贷专员"))
//{ SQLApplyBasis = SQLWhere("AuditInstructionsList.aspx?step=2"); }
//else if (basepage.CurrUserInfo().RoleName.Contains("信贷主管"))
//{ SQLApplyBasis = SQLWhere("AuditInstructionsList.aspx?step=3"); }
//else if (basepage.CurrUserInfo().RoleName.Contains("贷前审核"))
//{ SQLApplyBasis = SQLWhere("AuditInstructionsList.aspx?step=5"); }
//else if (basepage.CurrUserInfo().RoleName.Contains("财务放款"))
//{ SQLApplyBasis = SQLWhere("AuditInstructionsList.aspx?step=6"); }
//string SQLApplyBasis = SQLWhere("ApplyList.aspx");
//SQLApplyBasis += SQLWhere("AuditInstructionsList.aspx?step=1");
//SQLApplyBasis += SQLWhere("AuditInstructionsList.aspx?step=2");
//SQLApplyBasis += SQLWhere("AuditInstructionsList.aspx?step=3");
//SQLApplyBasis += SQLWhere("AuditInstructionsList.aspx?step=4");
//SQLApplyBasis += SQLWhere("AuditInstructionsList.aspx?step=5");
//SQLApplyBasis += SQLWhere("AuditInstructionsList.aspx?step=6");
SQLApplyBasis = SQLApplyBasis.Replace("'", "''");
List <string> PendingMatterList = new List <string>();//待办事项集合
string sqlString = "exec GetPendingMatterRecordDetail '" + basepage.CurrUserInfo().RoleID + "','" + basepage.CurrUserInfo().UserID + "','" + SQLApplyBasis + "'";
Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(sqlString);
DataSet ds = db.ExecuteDataSet(dbCommand);
if (ds != null)
{
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
string trStr = "";
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
trStr = "";
trStr += "<tr>";
trStr += "<td align=center style=\"border-bottom:1pt dotted\"><img src=\"/Images/Desktop/audit.png\" /></td>";
trStr += "<td align=center style=\"border-bottom:1pt dotted\">" + ds.Tables[0].Rows[i]["Stauts"].ToString() + "</td>";
trStr += "<td align=center style=\"border-bottom:1pt dotted\">" + ds.Tables[0].Rows[i]["ApplyBasis_Code"].ToString() + "</td>";
trStr += "<td align=center style=\"border-bottom:1pt dotted\">" + ds.Tables[0].Rows[i]["ApplyBasis_Name"].ToString() + "</td>";
trStr += "<td align=center style=\"border-bottom:1pt dotted\">" + ds.Tables[0].Rows[i]["VehicleTypeName"].ToString() + "</td>";
//trStr += "<td align=center style=\"border-bottom:1pt dotted\">" + ds.Tables[0].Rows[i]["CustomProduct_Loan"].ToString() + "</td>";
trStr += "<td align=center style=\"border-bottom:1pt dotted\">" + ds.Tables[0].Rows[i]["ApplyBasis_Date"].ToString() + "</td>";
//trStr += "<td align=center style=\"border-bottom:1pt dotted\">" + GetAuditUrl(ds.Tables[0].Rows[i]["PendingMatter_ModuleID"].ToString(), ds.Tables[0].Rows[i]["PendingMatter_ModuleName"].ToString(), ds.Tables[0].Rows[i]["WorkflowTasksInstanceID"].ToString(), ds.Tables[0].Rows[i]["WorkflowTasksSetp"].ToString(), ds.Tables[0].Rows[i]["ApplyBasis_Type"].ToString(), ds.Tables[0].Rows[i]["Stauts"].ToString()) + "</td>";
trStr += "</tr>";
PendingMatterList.Add(trStr);
}
}
}
}
return(PendingMatterList);
}
