private void NonQuerySql(string sql)
{
dtResult = null;
try
{
using (PDBConnection conn = new PDBConnection(GlobalVar.ConnStr))
{
conn.Open();
PDBCommand cmd = conn.CreateCommand();
cmd.ExecuteNonQuery(sql);
this.richTxtMsg.Text = "SQL:\n" + sql + "\n执行成功";
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>
/// 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 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();
}
private void InsertSql(string sql)
{
dtResult = null;
try
{
using (PDBConnection conn = new PDBConnection(GlobalVar.ConnStr))
{
conn.Open();
PDBCommand cmd = conn.CreateCommand();
PDBErrorCode retVal = cmd.ExecuteInsert(sql);
if (retVal != PDBErrorCode.PdbE_OK)
{
this.richTxtMsg.Text = "SQL:\n" + sql + "\n发生错误:\n";
this.richTxtMsg.Text += PDBErrorMsg.GetErrorMsg(retVal);
this.richTxtMsg.ForeColor = Color.Red;
}
else
{
this.richTxtMsg.Text = "SQL:\n" + sql + "\n执行成功!";
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>
/// Execue sql Command 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>
/// The number of rows affected
/// </returns>
public Int32 ExecueCommand(String Command, ParameterINClass[] Parameter, Int32 Timeout = 30)
{
Int32 Value = 0;
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();
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;
}
cmd.ExecuteNonQuery(Command, parameter);
return(Value);
}
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);
}
}
/// <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);
}
private bool updatePwd()
{
if (this.txbPwd.Text.Trim().Length < 6 || this.txbPwd1.Text.Trim().Length < 6)
{
MsgBox.ShowError("密码不能小于6位!");
return(false);
}
if (!this.txbPwd.Text.Trim().Equals(this.txbPwd1.Text.Trim()))
{
MsgBox.ShowError("两次输入密码不相等，请重新输入！");
this.txbPwd.Text = "";
this.txbPwd1.Text = "";
this.txbPwd.Focus();
return(false);
}
else if (this.txbPwd.Text.Trim().Equals("pinusdb"))
{
MsgBox.ShowError("不能设置为与初始密码相同！");
return(false);
}
else
{
try
{
string sql = "set password for sa=password('" + this.txbPwd.Text.Trim() + "')";
using (PDBConnection conn = new PDBConnection(GlobalVar.ConnStr))
{
conn.Open();
PDBCommand cmd = conn.CreateCommand();
cmd.ExecuteNonQuery(sql);
GlobalVar.Pwd = this.txbPwd.Text.Trim();
string connStr = string.Format("server={0};port={1};username={2};password={3}",
GlobalVar.Server, GlobalVar.Port, GlobalVar.User, GlobalVar.Pwd);
GlobalVar.ConnStr = connStr;
this.DialogResult = DialogResult.OK;
}
return(true);
}
catch (Exception ex)
{
MsgBox.ShowError(ex.Message);
return(false);
}
}
}
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;
}
/// <summary>
/// Execue sql Command
/// </summary>
/// <param name="Command">SQL command String</param>
/// <param name="Timeout">[option] timeout of database connect(seconds)</param>
/// <returns>
/// The number of rows affected
/// </returns>
public Int32 ExecueCommand(String Command, Int32 Timeout = 30)
{
Int32 Value = 0;
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();
cmd.ExecuteInsert(Command);
return(cmd.SuccessCount);
}
private bool CreateTable(string sql)
{
try
{
using (PDBConnection conn = new PDBConnection(GlobalVar.ConnStr))
{
conn.Open();
PDBCommand cmd = conn.CreateCommand();
cmd.ExecuteNonQuery(sql);
}
}
catch (Exception ex)
{
MsgBox.ShowError(ex.Message);
return(false);
}
initEvent();
MsgBox.ShowInfo("创建表成功");
return(true);
}
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);
}
private void detachTable()
{
if (tvNav.SelectedNode != null)
{
//根节点没有父节点会报错
if (tvNav.SelectedNode.Level == 0 || tvNav.SelectedNode.Parent.Text != "数据表")
{
MsgBox.ShowError("请选择要分离的数据表!");
return;
}
string tableName = tvNav.SelectedNode.Text;
DialogResult dr = MsgBox.ShowQuestion("您确定要分离数据表【" + tableName + "】吗?");
if (dr == DialogResult.OK)
{
string sql = "detach table " + tableName;
try
{
using (PDBConnection conn = new PDBConnection(GlobalVar.ConnStr))
{
conn.Open();
PDBCommand cmd = conn.CreateCommand();
cmd.ExecuteNonQuery(sql);
}
}
catch (Exception ex)
{
MsgBox.ShowError(ex.Message);
return;
}
InitTreeNav();
MsgBox.ShowInfo("分离表成功");
}
}
else
{
MsgBox.ShowError("请选择要分离的数据表!");
}
}
/// <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);
}
/// <summary>
/// Execue sql Command
/// </summary>
/// <param name="Command">SQL command String</param>
/// <param name="Timeout">[option] timeout of database connect(seconds)</param>
/// <returns>
/// The number of rows affected
/// </returns>
public Int32 ExecueTransactionCommand(String[] Command, Int32 Timeout = 30)
{
Int32 Value = 0;
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();
for (int i = 0; i < Command.Length; i++)
{
cmd.ExecuteNonQuery(Command[i]);
}
return(Value);
}