传入一个地址于excel表名,返回一个datatable,里面是与userinf表里不相符的信息
晚上终于把一个困扰很久的验证实现了,但大师姐明天就走了,再加上瑞星免费期已到,一点都高兴不起来。
验证的类中代码:
public
bool
validateUser(
string
uName,
string
uRealName)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
SqlParameter[] parameters =
{
new SqlParameter("@username", SqlDbType.NVarChar,256),
new SqlParameter("@realname", SqlDbType.VarChar,250)
};
parameters[0].Value = uName;
parameters[1].Value = uRealName;
![](/Images/OutliningIndicators/InBlock.gif)
if (sdh.ExecuteSql4Value("select count(*) from UserInfo where UserName=@username and URealName=@realname", parameters) > 0)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return true;
}
else
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return false;
}
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
public
DataTable validateUserErr(
string
path)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
eh.ExcelDB(path);
OleDbDataReader dr = eh.execSQL4DR("SELECT * FROM [Sheet1$]");
DataTable dt = new DataTable();
DataRow row = dt.NewRow();
while (dr.Read())
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string uid = dr["人员编号"].ToString();
string uname = dr["姓名"].ToString();
![](/Images/OutliningIndicators/InBlock.gif)
//Response.Write("人员姓名:" + uname + " 人员编号: " + uid + "-----");
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
if (validateUser(uid, uname))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//Response.Write("<font color=green>通过!</font>");
![](/Images/OutliningIndicators/InBlock.gif)
}
else
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
dt.Columns.Add("人员编号");
dt.Columns.Add("姓名");
row["人员编号"] = uid;
row["姓名"] = uname;
dt.Rows.Add(row);
//Response.Write("<font color=red>没这人!</font>");
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/InBlock.gif)
// Response.Write("<br />");
}
return dt;
}
ExcelDBHelper.cs中的代码
public
void
ExcelDB(
string
ExcelPath)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
_ExcelPath = ExcelPath;
}
public
OleDbDataReader execSQL4DR(
string
sql)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
OleDbConnection conn = createEconn();
OleDbCommand cmd = new OleDbCommand(sql, conn);
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception error)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw error;
}
finally
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
}
SqlDbHelper.cs类中的方法:
/**/
/// <summary>
/// 执行SQL语句,返回第一行,第一列(sea)
/// </summary>
/// <param name="SQLString">sql语句</param>
/// <param name="cmdParms">参数数组</param>
/// <returns>int</returns>
public
int
ExecuteSql4Value(
string
SQLString,
params
SqlParameter[] cmdParms)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
using (SqlConnection connection = new SqlConnection(strConn))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
using (SqlCommand cmd = new SqlCommand())
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object r = cmd.ExecuteScalar();
if (Object.Equals(r, null))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new Exception("value unavailable!");
}
else
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
try
{ int a = (int)r; }
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
catch
{ return 0; }
return (int)r;
}
![](/Images/OutliningIndicators/InBlock.gif)
}
catch (System.Data.SqlClient.SqlException E)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new Exception(E.Message);
}
finally
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
cmd.Parameters.Clear();
}
}
}
}