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);
}