public string del(string iID, string iType)
{
string sErr = "";
ArrayList aList = new ArrayList();
try
{
if (iType == "1")
{
sSQL = "select count(1) from dbo._LookUpDate where SexID = '" + iID + "' ";
long iCou = Convert.ToInt64(clsSQLCommond.ExecGetScalar(sSQL));
if (iCou > 0)
{
throw new Exception("编号 " + iID + " 已经使用不能删除\n");
}
}
else if (iType == "2")
{
sSQL = "select count(1) from dbo.Project where cPCCode = '" + iID + "' ";
long iCou = Convert.ToInt64(clsSQLCommond.ExecGetScalar(sSQL));
if (iCou > 0)
{
throw new Exception("编号 " + iID + " 已经使用不能删除\n");
}
}
else if (iType == "3")
{
sSQL = "select count(1) from dbo.ProjectRecord where cRsCode = '" + iID + "' ";
long iCou = Convert.ToInt64(clsSQLCommond.ExecGetScalar(sSQL));
if (iCou > 0)
{
throw new Exception("编号 " + iID + " 已经使用不能删除\n");
}
}
sSQL = "delete " + tablename + " where " + Code + " = '" + iID + "' and iType='" + iType + "' ";
aList.Add(sSQL);
if (sErr != "")
{
throw new Exception(sErr);
}
if (aList.Count > 0)
{
int iCou = clsSQLCommond.ExecSqlTran(aList);
}
}
catch (Exception ee)
{
sErr = ee.Message;
}
if (sErr == "")
{
return("ok");
}
else
{
return(sErr);
}
}
public string siID(int type, string iID, string cRsCode)
{
string s = "";
try
{
if (type == 1)
{
sSQL = "select min(" + tableid + ") as iID from " + tablename + " where 1=1 and cRsCode='" + cRsCode + "'";
}
else if (type == 2)
{
sSQL = "select " + tableid + " from " + tablename + " where " + tableid + "<'" + iID + "' and cRsCode='" + cRsCode + "' order by " + tableid + " desc";
}
else if (type == 3)
{
sSQL = "select " + tableid + " from " + tablename + " where " + tableid + ">'" + iID + "' and cRsCode='" + cRsCode + "' order by " + tableid + " ";
}
else if (type == 4)
{
sSQL = "select max(" + tableid + ") as iID from " + tablename + " where 1=1 and cRsCode='" + cRsCode + "'";
}
s = clsSQLCommond.ExecGetScalar(sSQL).ToString();
}
catch (Exception ee)
{
s = "-1";
}
return(s);
}
public DateTime dtm当前服务器时间()
{
DateTime dToday = Convert.ToDateTime("1900-01-01");
try
{
string sSQL = "select getdate()";
dToday = Convert.ToDateTime(clsSQLCommond.ExecGetScalar(sSQL).ToString());
}
catch (Exception ee)
{
}
return(dToday);
}
public bool ChkClosed(string sRoleID)
{
bool b = false;
try
{
string sSQL = "SELECT bClosed FROM _RoleInfo WHERE vchrRoleID='" + sRoleID + "' ORDER BY vchrRoleID ";
b = Convert.ToBoolean(clsSQLCommond.ExecGetScalar(sSQL));
}
catch
{
throw new Exception("获得用户信息失败！");
}
return(b);
}
public DateTime GetSerTime()
{
DateTime dTime;
try
{
string sSQL = "select getdate() as sTime ";
dTime = Convert.ToDateTime(clsSQLCommond.ExecGetScalar(sSQL));
}
catch
{
throw new Exception("获得服务器时间失败！");
}
return(dTime);
}
public bool ChkRoleID(string sRoleID)
{
bool b = false;
try
{
string sSQL = "SELECT COUNT(vchrRoleID) AS iCount FROM _RoleInfo WHERE (vchrRoleID = '" + sRoleID + "')";
int iCount = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL));
b = (iCount < 1);
}
catch
{
throw new Exception("判断角色是否存在失败！");
}
return(b);
}
public bool ChkUID(string sUid)
{
bool b = false;
try
{
string sSQL = "SELECT COUNT(vchrUid) AS iCount FROM _UserInfo WHERE (vchrUid = '" + sUid + "') ";
int iCount = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL));
b = (iCount < 1);
}
catch
{
throw new Exception("判断用户是否存在失败！");
}
return(b);
}
public string save(DataTable dtGrid)
{
string sErr = "";
ArrayList aList = new ArrayList();
try
{
sSQL = "select * from " + tablename + " where 1=-1";
DataTable dt = clsSQLCommond.ExecQuery(sSQL);
sSQL = "select isnull(max(iID)+1,1) as iID from " + tablename;
long iID = Convert.ToInt64(clsSQLCommond.ExecGetScalar(sSQL));
for (int i = 0; i < dtGrid.Rows.Count; i++)
{
if (dtGrid.Rows[i]["iSave"].ToString().Trim() == "update" || dtGrid.Rows[i]["iSave"].ToString().Trim() == "add")
{
#region 判断
if (dtGrid.Rows[i][Code].ToString().Trim() == "" &&
dtGrid.Rows[i][Name].ToString().Trim() == "")
{
continue;
}
if (dtGrid.Rows[i][Code].ToString().Trim() == "")
{
sErr = sErr + "行" + (i + 1) + CodeTitle + "不能为空\n";
continue;
}
if (dtGrid.Rows[i][Name].ToString().Trim() == "")
{
sErr = sErr + "行" + (i + 1) + NameTitle + "不能为空\n";
continue;
}
#endregion
#region 判断是否重复
for (int j = 0; j < i; j++)
{
if (dtGrid.Rows[i][Code].ToString().Trim() == dtGrid.Rows[j][Code].ToString().Trim())
{
sErr = sErr + "行" + (i + 1) + CodeTitle + "重复\n";
continue;
}
}
for (int j = 0; j < i; j++)
{
if (dtGrid.Rows[i][Name].ToString().Trim() == dtGrid.Rows[j][Name].ToString().Trim())
{
sErr = sErr + "行" + (i + 1) + NameTitle + "重复\n";
continue;
}
}
#endregion
#region 生成table
DataRow dr = dt.NewRow();
if (dtGrid.Rows[i]["iID"].ToString().Trim() != "")
{
dr["iID"] = dtGrid.Rows[i]["iID"];
}
else
{
dr["iID"] = iID;
iID = iID + 1;
}
dr[Code] = dtGrid.Rows[i][Code].ToString().Trim();
dr[Name] = dtGrid.Rows[i][Name].ToString().Trim();
if (dtGrid.Rows[i]["bClosed"].ToString().Trim() == "")
{
dr["bClosed"] = 0;
}
else
{
dr["bClosed"] = dtGrid.Rows[i]["bClosed"].ToString().Trim();
}
dr["Remark"] = dtGrid.Rows[i]["Remark"].ToString().Trim();
dt.Rows.Add(dr);
#endregion
if (dtGrid.Rows[i]["iSave"].ToString().Trim() == "update")
{
sSQL = clsGetSQL.GetUpdateSQL(ClsBaseDataInfo.sDataBaseName, tablename, dt, dt.Rows.Count - 1);
aList.Add(sSQL);
}
if (dtGrid.Rows[i]["iSave"].ToString().Trim() == "add")
{
sSQL = clsGetSQL.GetInsertSQL(ClsBaseDataInfo.sDataBaseName, tablename, dt, dt.Rows.Count - 1);
aList.Add(sSQL);
}
}
}
if (sErr != "")
{
throw new Exception(sErr);
}
if (aList.Count > 0)
{
int iCou = clsSQLCommond.ExecSqlTran(aList);
}
}
catch (Exception ee)
{
sErr = ee.Message;
}
if (sErr == "")
{
return("ok");
}
else
{
return(sErr);
}
}
public string save(DataTable dtGrid)
{
string sErr = "";
ArrayList aList = new ArrayList();
try
{
sSQL = "select * from " + tablename + " where 1=-1";
DataTable dt = clsSQLCommond.ExecQuery(sSQL);
sSQL = "select isnull(max(iID)+1,1) as iID from " + tablename;
long iID = Convert.ToInt64(clsSQLCommond.ExecGetScalar(sSQL));
for (int i = 0; i < dtGrid.Rows.Count; i++)
{
if (dtGrid.Rows[i]["iSave"].ToString().Trim() == "update" || dtGrid.Rows[i]["iSave"].ToString().Trim() == "add")
{
#region 判断
if (dtGrid.Rows[i][Code].ToString().Trim() == "" &&
dtGrid.Rows[i][Name].ToString().Trim() == "")
{
continue;
}
if (dtGrid.Rows[i][Code].ToString().Trim() == "")
{
sErr = sErr + "行" + (i + 1) + CodeTitle + "不能为空\n";
continue;
}
if (dtGrid.Rows[i][Name].ToString().Trim() == "")
{
sErr = sErr + "行" + (i + 1) + NameTitle + "不能为空\n";
continue;
}
//if (dtGrid.Rows[i]["cDepCode"].ToString().Trim() == "")
//{
// sErr = sErr + "行" + (i + 1) +"部门不能为空\n";
// continue;
//}
#endregion
#region 判断是否重复
for (int j = 0; j < i; j++)
{
if (dtGrid.Rows[i][Code].ToString().Trim() == dtGrid.Rows[j][Code].ToString().Trim())
{
sErr = sErr + "行" + (i + 1) + CodeTitle + "重复\n";
continue;
}
}
for (int j = 0; j < i; j++)
{
if (dtGrid.Rows[i][Name].ToString().Trim() == dtGrid.Rows[j][Name].ToString().Trim())
{
sErr = sErr + "行" + (i + 1) + NameTitle + "重复\n";
continue;
}
}
#endregion
#region 生成table
DataRow dr = dt.NewRow();
if (dtGrid.Rows[i]["iID"].ToString().Trim() != "")
{
dr["iID"] = dtGrid.Rows[i]["iID"];
}
else
{
dr["iID"] = iID;
iID = iID + 1;
}
dr[Code] = dtGrid.Rows[i][Code].ToString().Trim();
dr[Name] = dtGrid.Rows[i][Name].ToString().Trim();
dr["cDepCode"] = dtGrid.Rows[i]["cDepCode"].ToString().Trim();
dr["SexID"] = dtGrid.Rows[i]["SexID"].ToString().Trim();
dr["cDCCode"] = dtGrid.Rows[i]["cDCCode"].ToString().Trim();
dr["BeginDate"] = dtGrid.Rows[i]["BeginDate"];
dr["EndDate"] = dtGrid.Rows[i]["EndDate"];
dt.Rows.Add(dr);
if (dtGrid.Rows[i]["isUserInfo"].ToString().Trim() == "")
{
dr["isUserInfo"] = 0;
}
else
{
dr["isUserInfo"] = dtGrid.Rows[i]["isUserInfo"].ToString().Trim();
}
#endregion
if (dtGrid.Rows[i]["iSave"].ToString().Trim() == "update")
{
sSQL = clsGetSQL.GetUpdateSQL(ClsBaseDataInfo.sDataBaseName, tablename, dt, dt.Rows.Count - 1);
aList.Add(sSQL);
}
if (dtGrid.Rows[i]["iSave"].ToString().Trim() == "add")
{
sSQL = clsGetSQL.GetInsertSQL(ClsBaseDataInfo.sDataBaseName, tablename, dt, dt.Rows.Count - 1);
aList.Add(sSQL);
}
if (dtGrid.Rows[i]["isUserInfo"].ToString().Trim() == "True")
{
sSQL = @"IF EXISTS(select vchrUid From _UserInfo with (XLOCK) Where vchrUid='" + dtGrid.Rows[i][Code].ToString().Trim() + "') " +
" update _UserInfo set vchrName='" + dtGrid.Rows[i][Name].ToString().Trim() + "' Where vchrUid='" + dtGrid.Rows[i][Code].ToString().Trim() + "'" +
" else insert into _UserInfo(vchrUid,vchrName,vchrPwd) values('" + dtGrid.Rows[i][Code].ToString().Trim() + "','" + dtGrid.Rows[i][Name].ToString().Trim() + "','ADBF8135A642B58A')";
aList.Add(sSQL);
}
}
}
if (sErr != "")
{
throw new Exception(sErr);
}
if (aList.Count > 0)
{
int iCou = clsSQLCommond.ExecSqlTran(aList);
}
}
catch (Exception ee)
{
sErr = ee.Message;
}
if (sErr == "")
{
return("ok");
}
else
{
return(sErr);
}
}
