public void InsertMethod(DataTable dt)
{
string sql = "INSERT INTO T_METHODS(MID,MNAME,COMMENTARY,DETAIL,VALUETYPE,METHODTYPE) " +
"Values(@MID,@MNAME,@COMMENTARY,@DETAIL,@VALUETYPE,@METHODTYPE)";
CmdParameterCollection para = new CmdParameterCollection();
para.Add("@MID", dt.Rows[0]["MID"]);
para.Add("@MNAME", dt.Rows[0]["MNAME"]);
para.Add("@COMMENTARY", dt.Rows[0]["COMMENTARY"]);
para.Add("@DETAIL", dt.Rows[0]["DETAIL"]);
para.Add("@VALUETYPE", dt.Rows[0]["VALUETYPE"]);
para.Add("@METHODTYPE", dt.Rows[0]["METHODTYPE"]);
_data = DataAccessFactory.Instance.CreateDataAccess();
_data.Open();
try
{
_data.ExecuteNonQuery(sql, para);
}
catch (Exception e)
{
throw e;
}
finally
{
_data.Close();
}
}
public DataTable ExecSQL(string sql, Hashtable htAppend)
{
CmdParameterCollection cpc = new CmdParameterCollection();
DataTable dt = null;
_data = DataAccessFactory.Instance.CreateDataAccess();
try
{
if (htAppend != null)
{
foreach (DictionaryEntry de in htAppend)
{
CmdParameter cp = new CmdParameter(de.Key.ToString(), de.Value);
cpc.Add(cp);
}
}
_data.Open();
dt = _data.ExecuteDataSet(sql, cpc).Tables[0];
}
catch (Exception e)
{
throw e;
}
finally
{
_data.Close();
}
return(dt);
}
public void DeleteMethod(DataRow row)
{
string sql;
_data = DataAccessFactory.Instance.CreateDataAccess();
_data.Open();
var tran = _data.BeginTransaction();
CmdParameterCollection para = new CmdParameterCollection();
try
{
sql = "Delete From T_METHODS Where MID=@MID";
para.Add("@MID", row["MID"]);
_data.ExecuteNonQuery(sql, para);
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
finally
{
_data.Close();
}
}
public override int ExecuteNonQuery(string SQLString, CmdParameterCollection cmdParms)
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, this.DbConnection, SQLString, cmdParms);
return(cmd.ExecuteNonQuery());
}
}
public override IDataReader ExecuteReader(string SQLString, CmdParameterCollection cmdParms)
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, _conn, SQLString, cmdParms);
SqlDataReader reader = cmd.ExecuteReader();
return(reader);
}
}
public override IDataReader RunProcedure(string storedProcName, CmdParameterCollection parameters)
{
using (SqlCommand command = new SqlCommand())
{
PrepareCommand(command, _conn, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
SqlDataReader returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return(returnReader);
}
}
public override IDataReader ExecuteReader(string SQLString, CmdParameterCollection cmdParms)
{
return(this.ExecWithLog <IDataReader>(count =>
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, _conn, SQLString, cmdParms);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}, SQLString, cmdParms));
}
public override DataSet RunProcedure(string storedProcName, CmdParameterCollection parameters, string tableName)
{
using (SqlDataAdapter sqlDA = new SqlDataAdapter())
{
DataSet dataSet = new DataSet();
sqlDA.SelectCommand = new SqlCommand();
PrepareCommand(sqlDA.SelectCommand, _conn, storedProcName, parameters);
sqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDA.Fill(dataSet, tableName);
return(dataSet);
}
}
public override DataSet ExecuteDataSet(string SQLString, CmdParameterCollection cmdParms)
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, _conn, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds, "DataSet");
return(ds);
}
}
}
public override IDataReader RunProcedure(string storedProcName, CmdParameterCollection parameters)
{
return(this.ExecWithLog <IDataReader>(count =>
{
using (MySqlCommand command = new MySqlCommand())
{
PrepareCommand(command, _conn, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
MySqlDataReader returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
}, storedProcName, parameters));
}
public override int ExecuteNonQuery(string SQLString, CmdParameterCollection cmdParms)
{
return(this.ExecWithLog <int>(count =>
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, this.DbConnection, SQLString, cmdParms);
int rowCount = cmd.ExecuteNonQuery();
count(rowCount.ToString());
return rowCount;
}
}, SQLString, cmdParms));
}
public DataTable ExecProc(string procName, Dictionary <string, object> dic)
{
CmdParameterCollection cpc = new CmdParameterCollection();
_data = DataAccessFactory.Instance.CreateDataAccess();
foreach (KeyValuePair <string, object> de in dic)
{
cpc.Add(de.Key, de.Value);
}
DataTable dt = _data.RunProcedure(procName, cpc, procName).Tables[0];
return(dt);
}
public override int RunProcedure(string storedProcName, CmdParameterCollection parameters, out int rowsAffected)
{
using (SqlCommand command = new SqlCommand())
{
PrepareCommand(command, _conn, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
int result;
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
return(result);
}
}
public override object ExecuteScalar(string SQLString, CmdParameterCollection cmdParms)
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, _conn, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
}
public DataTable GetProcParams(string procName)
{
string sql = string.Empty;
_data = DataAccessFactory.Instance.CreateDataAccess();
switch (_data.DatabaseType)
{
case DatabaseType.DB2:
sql = @"select distinct PARMNAME
from SYSCAT.ROUTINEPARMS
WHERE ROUTINENAME = @PROCNAME";
break;
case DatabaseType.MSSQLServer:
sql = @"select distinct c.name as PARMNAME
from SysColumns c,sysobjects o,systypes b
where c.id = o.id and o.type = 'p' and c.xtype=b.xtype and b.status=0
AND o.name = @PROCNAME ";
break;
// case DatabaseType.OleDBSupported:
// break;
// case DatabaseType.Oracle:
// sql = @"
//select distinct ARGUMENT_NAME AS PARMNAME
//from user_arguments
//WHERE OBJECT_NAME =@PROCNAME and PACKAGE_NAME = @PACKAGENAME";
// break;
}
if (string.IsNullOrEmpty(sql))
{
return(null);
}
CmdParameterCollection qpc = new CmdParameterCollection();
//if (_data.DatabaseType == DatabaseType.Oracle)
//{
// qpc.Add("@PROCNAME", procName.Split('.')[1]);
// qpc.Add("@PACKAGENAME", procName.ToUpper().Split('.')[0]);
//}
//else
qpc.Add("@PROCNAME", procName.ToUpper());
return(_data.ExecuteDataSet(sql, qpc).Tables[0]);
}
public override object ExecuteScalar(string SQLString, CmdParameterCollection cmdParms)
{
return(this.ExecWithLog <object>(count =>
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, _conn, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
count("0");
return null;
}
else
{
count("1");
return obj;
}
}
}, SQLString, cmdParms));
}
public string GetInfo(string openId)
{
Dictionary <string, object> dicRst = new Dictionary <string, object>();
IDataAccess data = null;
try
{
API api = new API();
data = DataAccessFactory.Instance.CreateDataAccess();
data.Open();
CmdParameterCollection paras = new CmdParameterCollection();
paras.Add("@openId", openId);
IDataReader reader = data.ExecuteReader(@"
select * from SignRecord
where OpenId = @openId", paras);
if (reader.Read())
{
dicRst.Add("count", 1);
dicRst.Add("type", reader["UserType"]);
dicRst.Add("code", reader["Code"]);
dicRst.Add("name", reader["UserName"]);
dicRst.Add("phone", reader["PhoneNumber"]);
}
else
{
dicRst.Add("count", 0);
}
}
catch (Exception ex)
{
ErrorLog.WriteLog(ex);
dicRst.Add("errmsg", "出现错误，请联系管理员");
}
finally
{
data.Close();
}
return(dicRst.ToJsonString());
}
public override int RunProcedure(string storedProcName, CmdParameterCollection parameters, out int rowsAffected)
{
int result = 0, affectCount = 0;
result = this.ExecWithLog <int>(count =>
{
using (MySqlCommand command = new MySqlCommand())
{
PrepareCommand(command, _conn, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new MySqlParameter("ReturnValue",
MySqlDbType.Int32, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
affectCount = command.ExecuteNonQuery();
int rValue = (int)command.Parameters["ReturnValue"].Value;
count(rValue.ToString());
return(rValue);
}
}, storedProcName, parameters);
rowsAffected = affectCount;
return(result);
}
public override DataSet RunProcedure(string storedProcName, CmdParameterCollection parameters, string tableName)
{
return(this.ExecWithLog <DataSet>(count =>
{
using (SqlDataAdapter sqlDA = new SqlDataAdapter())
{
DataSet dataSet = new DataSet();
sqlDA.SelectCommand = new SqlCommand();
PrepareCommand(sqlDA.SelectCommand, _conn, storedProcName, parameters);
sqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDA.Fill(dataSet, tableName);
if (dataSet.Tables.Count > 0)
{
count(dataSet.Tables[0].Rows.Count.ToString());
}
else
{
count("0");
}
return dataSet;
}
}, storedProcName, parameters));
}
public override DataSet ExecuteDataSet(string SQLString, CmdParameterCollection cmdParms)
{
return(this.ExecWithLog <DataSet>(count =>
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, _conn, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds, "DataSet");
if (ds.Tables.Count > 0)
{
count(ds.Tables[0].Rows.Count.ToString());
}
else
{
count("0");
}
return ds;
}
}
}, SQLString, cmdParms));
}
public override IDataReader RunProcedure(string storedProcName, CmdParameterCollection parameters)
{
throw new NotImplementedException();
}
public void UpdateMethod(DataTable dt)
{
string sql;
_data = DataAccessFactory.Instance.CreateDataAccess();
_data.Open();
var tran = _data.BeginTransaction();
CmdParameterCollection para = new CmdParameterCollection();
try
{
foreach (DataRow dr in dt.Rows)
{
switch (dr.RowState)
{
case DataRowState.Added:
sql = "INSERT INTO T_METHODS(MID,MNAME,COMMENTARY,DETAIL,VALUETYPE,METHODTYPE)" +
"Values(@MID,@MNAME,@COMMENTARY,@DETAIL,@VALUETYPE,@METHODTYPE)";
para.Add("@MID", dt.Rows[0]["MID"]);
para.Add("@MNAME", dt.Rows[0]["MNAME"]);
para.Add("@COMMENTARY", dt.Rows[0]["COMMENTARY"]);
para.Add("@DETAIL", dt.Rows[0]["DETAIL"]);
para.Add("@VALUETYPE", dt.Rows[0]["VALUETYPE"]);
para.Add("@METHODTYPE", dt.Rows[0]["METHODTYPE"]);
break;
case DataRowState.Modified:
sql = "UPDATE T_METHODS SET MNAME=@MNAME,COMMENTARY=@COMMENTARY,DETAIL=@DETAIL,METHODTYPE=@METHODTYPE Where MID=@MID ";
para.Add("@MNAME", dt.Rows[0]["MNAME"]);
para.Add("@COMMENTARY", dt.Rows[0]["COMMENTARY"]);
para.Add("@DETAIL", dt.Rows[0]["DETAIL"]);
para.Add("@VALUETYPE", dt.Rows[0]["VALUETYPE"]);
para.Add("@METHODTYPE", dt.Rows[0]["METHODTYPE"]);
para.Add("@MID", dt.Rows[0]["MID"]);
break;
case DataRowState.Deleted:
sql = "Delete From T_METHODS Where MID=@MID ";
para.Add("@MID", dt.Rows[0]["MID"]);
break;
default:
continue;
}
_data.ExecuteNonQuery(sql, para);
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
finally
{
_data.Close();
}
}
public override DataSet RunProcedure(string storedProcName, CmdParameterCollection parameters, string tableName)
{
throw new NotImplementedException();
}
public override int RunProcedure(string storedProcName, CmdParameterCollection parameters, out int rowsAffected)
{
throw new NotImplementedException();
}
public override void PrepareCommand(IDbCommand cmd, IDbConnection conn, string cmdText, CmdParameterCollection cmdParms)
{
cmd.Connection = _conn;
cmd.CommandText = cmdText;
cmd.Transaction = Trans;
cmd.CommandTimeout = 600;
cmd.CommandType = CommandType.Text;
if (cmdParms != null)
{
foreach (CmdParameter param in cmdParms)
{
SqlParameter paras = new SqlParameter(param.Name, param.Value);
paras.Direction = param.Direction;
paras.Size = param.Size;
paras.DbType = param.DbType;
cmd.Parameters.Add(paras);
}
}
}
public string SignIn(string type, string openId, string code, string name, string phoneNumber)
{
Dictionary <string, object> dicRst = new Dictionary <string, object>();
IDataAccess data = null;
try
{
data = DataAccessFactory.Instance.CreateDataAccess();
data.Open();
if (type == "Staff")
{
CmdParameterCollection paras = new CmdParameterCollection();
paras.Add("@WorkNumber", code);
paras.Add("@UserName", name);
object obj = data.ExecuteScalar(@"
select count(1) from Staff
where WorkNumber=@WorkNumber and UserName=@UserName ", paras);
if (obj == null || obj == DBNull.Value)
{
dicRst.Add("errmsg", "工号与姓名不符，请核对");
}
else
{
if (Convert.ToInt32(obj) == 1)
{
CmdParameterCollection paras3 = new CmdParameterCollection();
paras3.Add("@Code", code);
IDataReader reader = data.ExecuteReader(@"
select * from SignRecord
where Code=@Code", paras3);
if (reader.Read())
{
dicRst.Add("result", "您已经于" +
Convert.ToDateTime(reader["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss") +
"进行过签到，无需再次签到");
reader.Close();
}
else
{
reader.Close();
CmdParameterCollection paras2 = new CmdParameterCollection();
paras2.Add("@Id", BitConverter.ToInt64(Guid.NewGuid().ToByteArray(), 0));
paras2.Add("@OpenId", openId);
paras2.Add("@UserType", type);
paras2.Add("@Code", code);
paras2.Add("@UserName", name);
paras2.Add("@PhoneNumber", phoneNumber);
paras2.Add("@CreateTime", DateTime.Now);
data.ExecuteNonQuery(@"
insert into SignRecord(Id,OpenId,UserType,Code,UserName,PhoneNumber,CreateTime)
values(@Id,@OpenId,@UserType,@Code,@UserName,@PhoneNumber,@CreateTime)", paras2);
dicRst.Add("result", "签到成功！");
}
}
else
{
dicRst.Add("errmsg", "工号与姓名不符，请核对");
}
}
}
else
{
dicRst.Add("errmsg", "暂未开通服务");
}
}
catch (Exception ex)
{
ErrorLog.WriteLog(ex);
dicRst.Add("errmsg", "签到错误，请联系管理员");
}
finally
{
data.Close();
}
return(dicRst.ToJsonString());
}
