[OperationContract]
void DoWork();
[OperationContract]
bool CheckUser(string sUser, string sPsd);
[OperationContract]
bool CheckSQL(string sql);
[OperationContract]
int ExecuteSQL(string sql);
[OperationContract]
string QuerySQL(string sql);
public class DBService : IDBService
{
private SqlConnection pSqlConn = null;
private string sSqlConnString = "Data Source=D413-8;Initial Catalog=Webgis;Integrated Security=True";
public void DoWork()
{
}
/// <summary>
/// 检查用户密码是否正确
/// </summary>
/// <param name="sUser"></param>
/// <param name="sPsd"></param>
/// <returns></returns>
public bool CheckUser(string sUser, string sPsd)
{
try
{
//连接数据库
if (CheckSqlConn())
{
string sql = "select * from dbo.UerTab where NAME='{0}' and PASSKEY='{1}'";
sql = String.Format(sql, sUser, sPsd);
// 查询数据库里面的记录
SqlDataAdapter pSQLDataAdapter = new SqlDataAdapter(sql, pSqlConn);
DataSet pDataSet = new DataSet();
pSQLDataAdapter.Fill(pDataSet);
int ResultCount = pDataSet.Tables[0].Rows.Count;
//判断该记录是否存在
if (ResultCount > 0)
{
return true;
}
else
{
return false;
}
}
return false;
}
catch
{
return false;
}
}
/// <summary>
/// 判断查询结果是否存在
/// </summary>
/// <param name="sUser"></param>
/// <param name="sPsd"></param>
/// <returns></returns>
public bool CheckSQL(string sql)
{
try
{
//连接数据库
if (CheckSqlConn())
{
// 查询数据库里面的记录
SqlDataAdapter pSQLDataAdapter = new SqlDataAdapter(sql, pSqlConn);
DataSet pDataSet = new DataSet();
pSQLDataAdapter.Fill(pDataSet);
int ResultCount = pDataSet.Tables[0].Rows.Count;
//判断该记录是否存在
if (ResultCount > 0)
{
return true;
}
else
{
return false;
}
}
return false;
}
catch
{
return false;
}
}
/// <summary>
/// 根据SQL语句，执行查询操作，返回string结果
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public string QuerySQL(string sql)
{
try
{
string sResoult = string.Empty;
if (CheckSqlConn())
{
SqlDataAdapter pSqlDataAdapter = new SqlDataAdapter(sql, pSqlConn);
DataSet pDs = new DataSet();
pSqlDataAdapter.Fill(pDs);
DataTable pDt = pDs.Tables[0];
int nRowCout = pDt.Rows.Count;
int nCoulmnCount = pDs.Tables[0].Columns.Count;
for (int i = 0; i < nRowCout; i++)
{
for (int j = 0; j < nCoulmnCount; j++)
{
if (j == 0)
{
sResoult += "#";
}
sResoult += pDt.Rows[i][j].ToString() + "*";
}
}
return sResoult;
}
else
{
return string.Empty;
}
}
catch
{
return "";
}
}
/// <summary>
/// 执行单条SQL语句
/// </summary>
/// <param name="sql"></param>
/// <returns>影响的记录数</returns>
public int ExecuteSQL(string sql)
{
try
{
string sResoult = string.Empty;
if (CheckSqlConn())
{
SqlCommand com = new SqlCommand(sql,pSqlConn);
int rows = com.ExecuteNonQuery();
return rows;
}
else
{
return 0;
}
SqlCoonClose();
}
catch
{
return 0;
}
}
/// <summary>
/// 检测SQL连接是否存在，并实例化，打开
/// </summary>
/// <returns></returns>
private bool CheckSqlConn()
{
try
{
if (pSqlConn == null)
{
pSqlConn = new SqlConnection(sSqlConnString);
}
else
{
if (pSqlConn.State != ConnectionState.Open)
{
pSqlConn.Open();
}
}
if (pSqlConn.State == ConnectionState.Closed)
{
pSqlConn.Open();
}
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
/// <returns></returns>
private void SqlCoonClose()
{
try
{
if (pSqlConn.State != ConnectionState.Closed)
{
pSqlConn.Close();
}
}
catch
{
}
}
}