用户代码未处理SqlException 列名AreaId无效
using System;using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.Sql;
namespace Fishing.App_Code
{
public class SQLServerOperation
{
protected string SQL;
SqlConnection conn = new SqlConnection(ConnStr);
private static string ConnStr = MakeConnStr();
//拼接形成数据库连接字符串
public static string MakeConnStr()
{
//下面为测试数据库连接
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
return connectionString;
}
public DataTable Select_DT(string sql, string DataTableName)
{
//输入标准SQl语句，返回DataTable类型数据
conn.Open();
SQL = sql;
SqlDataAdapter DA = new SqlDataAdapter(SQL, conn);
DataTable DT = new DataTable();
DT.TableName = DataTableName;
DA.Fill(DT);
conn.Close();
return DT;
}
public DataSet Select_DS(string sql, string DataTableName)
{
//输入标准SQl语句，返回DataSet类型数据
conn.Open();
SQL = sql;
SqlDataAdapter da = new SqlDataAdapter(SQL, conn);
DataTable DT = new DataTable();
da.Fill(DT);
DataSet DS = new DataSet();
DS.Tables.Add(DT);
conn.Close();
return DS;
}
public string Insert_SQL(string sql, string DataTableName)
{
//将数据插入数据表，输入标准SQL语句，返回成功插入行数
conn.Open();
SQL = sql;
SqlCommand CMD = new SqlCommand(sql, conn);
int CNT = CMD.ExecuteNonQuery();
conn.Close();
return CNT.ToString();
}
public string Insert_SQL_ReturnId(string sql, string DataTableName)
{
conn.Open();
SqlCommand CMD = new SqlCommand(sql, conn);
int CNT = CMD.ExecuteNonQuery();
conn.Close();
return CNT.ToString();
}
public void Update_SQL(string sql, string DataTableName)
{
//更新数据表，输入标准SQL语句，返回成功插入行数
conn.Open();
SQL = sql;
SqlCommand CMD = new SqlCommand(sql, conn);
int CNT = CMD.ExecuteNonQuery();
conn.Close();
}
public void Delete_SQL(string sql, string DataTableName)
{
conn.Open();
SQL = sql;
SqlCommand CMD = new SqlCommand(sql, conn);
int CNT = CMD.ExecuteNonQuery();
conn.Close();
}
public int CNT_SQL(string sql, string DataTableName)
{
//将数据插入数据表，输入标准SQL语句，返回成功插入行数
conn.Open();
SqlCommand CMD = new SqlCommand(sql,conn);
SqlDataAdapter DA = new SqlDataAdapter(sql, conn);
DataTable DT = new DataTable();
DA.Fill(DT);
int CNT = DT.Rows.Count;
conn.Close();
return CNT;
}
public string getUserIdByName(string name)
{
DataTable dt=Select_DT("select UserId from SSC_Users where UserName='" + name + "'", "SSC_Users");
int count=dt.Rows.Count;
if (count > 0)
{
return dt.Rows[0][0].ToString();
}
else
{
return "";
}
}
public string getUserNameById(string id)
{
Guid UserId = new Guid(id);
DataTable dt=this.Select_DT("select UserName from SSC_Users where UserId='" + UserId + "'", "SSC_Users");
if (dt.Rows.Count > 0)
{
return dt.Rows[0][0].ToString();
}
else
{
return "";
}
}
public string getTopicNameById(string id)
{
Guid TopicId = new Guid(id);
return this.Select_DT("select TopicName from SSC_Topics where TopicId='" + TopicId + "'", "SSC_Topics").Rows[0][0].ToString();
}
public string getTopicIdByName(string name)
{
return this.Select_DT("select TopicId from SSC_Topics where TopicName='" + name + "'", "SSC_Topics").Rows[0][0].ToString();
}
public string getLocationNameById(string id)
{
return this.Select_DT("select TopicName from SSC_Topics where TopicId='" + id + "'", "SSC_Topics").Rows[0][0].ToString();
}
public string[] getFunctionNameAndFunctionUrlById(string id)
{
//Guid TopicId = new Guid(id);
DataTable dt=Select_DT("select FunctionName,FunctionUrl from SSC_Functions where FunctionId='" + id + "'", "SSC_Functions");
string[] nameAndUrl=new string[]{"",""};
if(dt.Rows.Count>0)
{
nameAndUrl = new string[]{ dt.Rows[0]["FunctionName"].ToString(), dt.Rows[0]["FunctionUrl"].ToString()};
}
return nameAndUrl;
}
public string getFunctionIdByFunctionName(string name)
{
DataTable dt=Select_DT("select FunctionId from SSC_Functions where FunctionName='" + name + "'", "SSC_Functions");
if (dt.Rows.Count > 0)
{
return dt.Rows[0][0].ToString();
}
else
{
return "";
}
}
public string[] getStartEndDateByPartTimeId(string id)
{
DataTable dt = Select_DT("select StartDate,EndDate from SSC_PartTimes where PartTimeId='" + id + "'", "SSC_PartTimes");
string[] SEDate={"",""};
if (dt.Rows.Count > 0)
{
SEDate[0]=dt.Rows[0][0].ToString();
SEDate[1]=dt.Rows[0][1].ToString();
return SEDate;
}
else
{
return SEDate;
}
}
public string getRoleIdByUserId(string id)
{
Guid UserId = new Guid(id);
DataTable dt=Select_DT("select RoleId from SSC_UsersInRoles where UserId='" + UserId + "'", "SSC_UsersInRoles");
int count = dt.Rows.Count;
if (count > 0)
{
return dt.Rows[0][0].ToString();
}
else
{
return "";
}
}
public string getRoleNameByRoleId(string id)
{
Guid RoleId = new Guid(id);
DataTable dt=Select_DT("select RoleName from SSC_Roles where RoleId='" + RoleId + "'", "SSC_UsersInRoles");
int count=dt.Rows.Count;
if (count > 0)
{
return dt.Rows[0][0].ToString();
}
else
{
return "";
}
}
public string getRoleIdByRoleName(string name)
{
return this.Select_DT("select RoleId from SSC_Roles where RoleName='" + name + "'", "SSC_Roles").Rows[0][0].ToString();
}
public string getAreaNameByAreaId(string id)
{
string AreaId = getUserIdByName(id);
return this.Select_DT("select AreaName from SSC_Areas whereAreaId='" + id + "'", "SSC_Areas").Rows[0][0].ToString();
}
public string getAreaIdByAreaName(string name)
{
return this.Select_DT("select AreaId from SSC_Areas where AreaName='" + name + "'", "SSC_Areas").Rows[0][0].ToString();
}
public string getAreaIdByUserName(string name)
{
string userid = getUserIdByName(name);
return this.Select_DT("select AreaId from SSC_Users where UserId='" + userid + "'", "SSC_Users").Rows[0][0].ToString();
}
public string getEmployeeIdByUserId(string id)
{
return this.Select_DT("select EmployeeId from SSC_Users where UserId='" + id + "'", "SSC_Users").Rows[0][0].ToString();
}
public int getFunctionByUserName(string username,string functionname)
{
string roleid = getRoleIdByUserId(getUserIdByName(username));
string functionid = getFunctionIdByFunctionName(functionname);
if (functionid != ""&&roleid!="")
{
return this.Select_DT("select FunctionId from SSC_RolesFunctions where FunctionId='" + functionid + "' and RoleId='" + roleid + "'", "SSC_RolesFunctions").Rows.Count;
}
else
{
return 0;
}
}
public string getRoleIdByFunctionId(string id, string rid)
{
Guid FunctionId = new Guid(id);
if (Select_DT("select RoleId from SSC_RolesFunctions where FunctionId='" + FunctionId + "' and RoleId='" + rid + "'", "SSC_RolesFunctions").Rows.Count > 0)
{
return this.Select_DT("select RoleId from SSC_RolesFunctions where FunctionId='" + FunctionId + "' and RoleId='" + rid + "'", "SSC_RolesFunctions").Rows[0][0].ToString();
}
else
{
return "";
}
}
public string getPartTimeNameByPartTimeId(string id)
{
return this.Select_DT("select PartTimeName from SSC_PartTimes where PartTimeId='" + id + "'", "SSC_PartTimes").Rows[0][0].ToString();
}
public string getWorkNameByWorkId(string id)
{
return this.Select_DT("select WorkName from SSC_Works where WorkId='" + id + "'", "SSC_Works").Rows[0][0].ToString();
}
public string getWorkIdByPartTimeId(string id)
{
return this.Select_DT("select WorkId from SSC_PartTimes where PartTimeId='" + id + "'", "SSC_PartTimes").Rows[0][0].ToString();
}
public string getWorkIdByWorkName(string name)
{
if (name != "")
{
return this.Select_DT("select WorkId from SSC_Works where WorkName='" + name + "'", "SSC_Works").Rows[0][0].ToString();
}
else
{
return "";
}
}
public string getDescriptionByPartTimeId(string id)
{
return this.Select_DT("select Description from SSC_PartTimes where PartTimeId='" + id + "'", "SSC_PartTimes").Rows[0][0].ToString();
}
}
}
