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