private void BindTableList(TreeNode sysTabRoot, TreeNode dataTabRoot) { try { using (PDBConnection conn = new PDBConnection(GlobalVar.ConnStr)) { conn.Open(); PDBCommand cmd = conn.CreateCommand(); DataTable dtTable = cmd.ExecuteQuery("select * from sys_table"); foreach (DataRow dr in dtTable.Rows) { string tabName = dr["tabname"].ToString(); DataTable colTable = cmd.ExecuteQuery(string.Format("select * from sys_column where tabname='{0}'", tabName)); if (tabName.StartsWith("sys_")) { BindTableInfo(sysTabRoot, tabName, colTable); } else { BindTableInfo(dataTabRoot, tabName, colTable); } } } } catch (Exception ex) { MsgBox.ShowError(ex.Message); } }
private void QuerySql(string sql) { dtResult = null; try { Stopwatch sw = new Stopwatch(); sw.Start(); using (PDBConnection conn = new PDBConnection(GlobalVar.ConnStr)) { conn.Open(); PDBCommand cmd = conn.CreateCommand(); dtResult = cmd.ExecuteQuery(sql); sw.Stop(); this.tabControlResult.SelectedTab = this.tabPageData; this.richTxtMsg.Text = "SQL:\n" + sql + "\n执行成功\n执行耗时:" + sw.ElapsedMilliseconds + "ms"; this.richTxtMsg.ForeColor = Color.Green; } } catch (Exception ex) { this.richTxtMsg.Text = "SQL:\n" + sql + "\n发生错误:" + ex.Message; this.richTxtMsg.ForeColor = Color.Red; this.tabControlResult.SelectedTab = this.tabPageMsg; } BindResult(); }
/// <summary> /// return a dataset from database with Parameter /// </summary> /// <param name="Command">SQL command String</param> /// <param name="Parameter">Parameter</param> /// <param name="Timeout">[option] timeout of database connect(seconds)</param> /// <returns> /// dataset value /// </returns> public DataSet GetDataSet(String Command, ParameterClass[] Parameter, Int32 Timeout = 30) { if (conn == null) { throw new Exception("Connection has not initialize."); } if (conn.IsValid() != true) { throw new Exception("Connection has not opened."); } PDBCommand cmd = conn.CreateCommand(); //add parameter PDBParameter[] parameter = new PDBParameter[Parameter.Length]; for (int number = 0; number < Parameter.Length; number++) { parameter[number] = new PDBParameter(Parameter[number].ParameterName, (PDBType)Enum.Parse(typeof(PDBType), Parameter[number].Value.GetType().ToString())); parameter[number].Value = Parameter[number].Value; } DataTable dt = cmd.ExecuteQuery(Command, parameter); DataSet ds = new DataSet(); ds.Tables.Add(dt); return(ds); }
/// <summary> /// return a datareader from database /// </summary> /// <param name="Command">SQL command String</param> /// <param name="Parameter">Parameter</param> /// <param name="Timeout">[option] timeout of database connect(seconds)</param> /// <returns> /// DataReader value /// </returns> public IDataReader GetDataReaderList(String Command, ParameterClass[] Parameter, Int32 Timeout = 30) { IDataReader Value; if (conn == null) { throw new Exception("Connection has not initialize."); } if (conn.IsValid() != true) { throw new Exception("Connection has not opened."); } PDBCommand cmd = conn.CreateCommand(); //add parameter PDBParameter[] parameter = new PDBParameter[Parameter.Length]; for (int number = 0; number < Parameter.Length; number++) { parameter[number] = new PDBParameter(Parameter[number].ParameterName, (PDBType)Enum.Parse(typeof(PDBType), Parameter[number].Value.GetType().ToString())); parameter[number].Value = Parameter[number].Value; } Value = cmd.ExecuteQuery(Command, parameter).CreateDataReader(); return(Value); }
/// <summary> /// get first row and first colunm value from database /// </summary> /// <param name="Command">SQL command String</param> /// <param name="Timeout">[option] timeout of database connect(seconds)</param> /// <returns> /// object value /// </returns> public object GetSpeciaRecordValue(String Command, Int32 Timeout = 30) { object Value; if (conn == null) { throw new Exception("Connection has not initialize."); } if (conn.IsValid() != true) { throw new Exception("Connection has not opened."); } PDBCommand cmd = conn.CreateCommand(); DataTable dt = cmd.ExecuteQuery(Command); if (dt.Rows.Count > 0) { Value = dt.Rows[0][0]; } else { Value = null; } return(Value); }
private void SysMessageForm_Load(object sender, EventArgs e) { try { string majorVer = ""; string minorVer = ""; string buildVer = ""; string dbSql = "select name,value from sys_config"; using (PDBConnection conn = new PDBConnection(GlobalVar.ConnStr)) { conn.Open(); PDBCommand cmd = conn.CreateCommand(); DataTable dtResult = cmd.ExecuteQuery(dbSql); foreach (DataRow dr in dtResult.Rows) { string nameStr = dr["name"].ToString().ToLower(); string valStr = dr["value"].ToString(); switch (nameStr) { case "majorversion": majorVer = valStr; break; case "minorversion": minorVer = valStr; break; case "buildversion": buildVer = valStr; break; case "devcnt": lblLicDevCnt.Text = valStr; break; } } lblDBVer.Text = majorVer + "." + minorVer + "." + buildVer; } }catch (Exception) { MsgBox.ShowError("读取系统配置失败"); return; } }
private void AboutForm_Load(object sender, EventArgs e) { String sql = "select name,value from sys_config"; DataTable dtResult = null; try { using (PDBConnection conn = new PDBConnection(GlobalVar.ConnStr)) { conn.Open(); PDBCommand cmd = conn.CreateCommand(); dtResult = cmd.ExecuteQuery(sql); } } catch (Exception ex) { MsgBox.ShowError("读取系统配置表出错"); return; } string majorVer = ""; string minorVer = ""; string buildVer = ""; foreach (DataRow dr in dtResult.Rows) { if (dr["name"].ToString().Equals("majorVersion")) { majorVer = dr["value"].ToString(); } else if (dr["name"].ToString().Equals("minorVersion")) { minorVer = dr["value"].ToString(); } else if (dr["name"].ToString().Equals("buildVersion")) { buildVer = dr["value"].ToString(); } } this.lblDBVersion.Text = majorVer + "." + minorVer + "." + buildVer; }
private string ExecuteQuery(QueryParam queryParam) { TableItem tabItem = TableSet.GetImpl().GetTableInfo(queryParam.GetTableName()); if (tabItem == null) { throw new Exception("table not found"); } string querySql = queryParam.GetSql(tabItem); if (string.IsNullOrEmpty(querySql)) { throw new Exception("invalid query param"); } string errMsg = string.Empty; DataTable resultTable = null; PDBConnection conn = ConnectionPoolSet.GetImpl().GetConnection(tabItem.ServerName); try { PDBCommand cmd = conn.CreateCommand(); resultTable = cmd.ExecuteQuery(querySql); } catch (Exception ex) { errMsg = ex.Message; } ConnectionPoolSet.GetImpl().BackConnection(tabItem.ServerName, conn, errMsg != string.Empty); if (errMsg != string.Empty) { throw new Exception(errMsg); } JsonConverter[] pinusConverter = { new DateTime2LongConverter(), new BlobConverter() }; return("{ \"err\":0, \"data\": " + JsonConvert.SerializeObject(resultTable, pinusConverter) + "}"); }
/// <summary> /// return a dataset from database /// </summary> /// <param name="Command">SQL command String</param> /// <param name="Timeout">[option] timeout of database connect(seconds)</param> /// <returns> /// dataset value /// </returns> public DataSet GetDataSet(String Command, Int32 Timeout = 30) { if (conn == null) { throw new Exception("Connection has not initialize."); } if (conn.IsValid() != true) { throw new Exception("Connection has not opened."); } PDBCommand cmd = conn.CreateCommand(); DataTable dt = cmd.ExecuteQuery(Command); DataSet ds = new DataSet(); ds.Tables.Add(dt); return(ds); }
/// <summary> /// get first row and first colunm value from database with Parameter /// </summary> /// <param name="Command">SQL command String</param> /// <param name="Parameter">Parameter</param> /// <param name="Timeout">[option] timeout of database connect(seconds)</param> /// <returns> /// object value /// </returns> public object GetSpeciaRecordValue(String Command, ParameterClass[] Parameter, Int32 Timeout = 30) { object Value; if (conn == null) { throw new Exception("Connection has not initialize."); } if (conn.IsValid() != true) { throw new Exception("Connection has not opened."); } PDBCommand cmd = conn.CreateCommand(); //add parameter PDBParameter[] parameter = new PDBParameter[Parameter.Length]; for (int number = 0; number < Parameter.Length; number++) { parameter[number] = new PDBParameter(Parameter[number].ParameterName, (PDBType)Enum.Parse(typeof(PDBType), Parameter[number].Value.GetType().ToString())); parameter[number].Value = Parameter[number].Value; } DataTable dt = cmd.ExecuteQuery(Command, parameter); if (dt.Rows.Count > 0) { Value = dt.Rows[0][0]; } else { Value = null; } return(Value); }
/// <summary> /// return a datareader from database /// </summary> /// <param name="Command">SQL command String</param> /// <param name="Timeout">[option] timeout of database connect(seconds)</param> /// <returns> /// DataReader value /// </returns> public IDataReader GetDataReaderList(String Command, Int32 Timeout = 30) { IDataReader Value; if (conn == null) { throw new Exception("Connection has not initialize."); } if (conn.IsValid() != true) { throw new Exception("Connection has not opened."); } PDBCommand cmd = conn.CreateCommand(); //add parameter Value = cmd.ExecuteQuery(Command).CreateDataReader(); return(Value); }