dataaccess.cs

能对数据库进行维护

using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Xml;
using System.Windows.Forms;

namespace DBMaintainDll.Data_Structure
{
	/// <summary>
	/// DataAccess 的摘要说明。
	/// </summary>
	public class DataAccess
	{
		public DataAccess()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}

		#region 配置数据库连接字符串 
		/// <summary>
		/// 配置数据库连接字符串
		/// </summary>
//        private  static string ConnectionString="";	
		private  static string ConnectionString="Data Source=58.155.47.157;Initial Catalog=yusuanMIS;User Id=sa;Password=sa";
//		private SqlCommandBuilder sqlCmdBuilder; 
		#endregion
		
		#region 设置返回连接字符串 
		public void setconstr(string ConnectionString)
		{
          DataAccess.ConnectionString=ConnectionString;
		}

		
		public string getconnstr()
		{
			return ConnectionString;
		}
		#endregion

		#region  执行SQL语句，返回Bool值
		/// <summary>
		/// 执行SQL语句，返回Bool值
		/// </summary>
		/// <param name="sql">要执行的SQL语句</param>
		/// <returns>返回BOOL值，True为执行成功</returns>
		public static bool ExecuteSQL(string sql)
		{
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlCommand cmd=new SqlCommand(sql,con);
			try
			{
				con.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch
			{
				return false;
			}
			finally
			{
				con.Close();
				con.Dispose();
				cmd.Dispose();
			}
		}


		public bool ExSQL(string sql)
		{
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlCommand cmd=new SqlCommand(sql,con);
			try
			{
				con.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch
			{
				return false;
			}
			finally
			{
				con.Close();
				con.Dispose();
				cmd.Dispose();
			}
		}

		#endregion

		#region 执行SQL语句，返回SqlDataReader
		/// <summary>
		/// 执行SQL语句，返回SqlDataReader
		/// </summary>
		/// <param name="sql">要执行的SQL语句</param>
		/// <returns>返回SqlDataReader，需手工关闭连接</returns>
		public static  SqlDataReader GetReader(string sql)
		{
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlCommand cmd=new SqlCommand(sql,con);
			SqlDataReader dr=null;
			try
			{
				con.Open();
				dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch (Exception ex)
			{
				dr.Close();
				con.Dispose();
				cmd.Dispose();
				throw new Exception(ex.ToString());
			}
			return dr;
		}
		#endregion

		#region  分页，返回SqlDataReader
		/// <summary>
		/// 分页，返回SqlDataReader
		/// </summary>
		/// <param name="tblName">查询的表名</param>
		/// <param name="fldName">排序字段名</param>
		/// <param name="PageSize">每页中记录的数量</param>
		/// <param name="PageIndex">当前查询的页码</param>
		/// <param name="OrderType">设置排序类型, 非 0 值则降序</param>
		/// <param name="strWhere">查询条件(注意:不要加 where)</param>
		/// <returns>返回SqlDataReader，需手工关闭连接</returns>
		public SqlDataReader GetReaderPage(string tblName,string fldName,int PageSize,int PageIndex,int OrderType,string strWhere)
		{
			string strTmp,strOrder;
			string sql="";
			if (OrderType!=0)
			{
				strTmp="< (select min";
				strOrder=" order by " + fldName +" desc";
			}
			else
			{				
				strTmp=">(select max";
				strOrder=" order by " + fldName +" asc";
			}
			if (strWhere!="")
			{
				sql = "select top " + PageSize + " * from "+ tblName + " where " + fldName + strTmp + "(";
				sql+=fldName + ") from (select top " + (PageIndex-1)*PageSize +" "+ fldName + " from " + tblName + " where (" + strWhere + ") ";
				sql+=strOrder + ") as tblTmp) and (" + strWhere + ") " + strOrder;
			}
			if (PageIndex==1)
			{
				strTmp ="";
				if (strWhere != "")
				{
					strTmp = " where (" + strWhere + ")";
				}
				sql = "select top " + PageSize + " * from " + tblName + strTmp + " " + strOrder;
			}
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlCommand cmd=new SqlCommand(sql,con);
			SqlDataReader dr=null;
			try
			{
				con.Open();
				dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch (Exception ex)
			{				
				dr.Close();
				con.Dispose();
				cmd.Dispose();
				throw new Exception(ex.ToString());
			}
			return dr;
		}
		#endregion

		#region  执行SQL语句，返回DataSet
		/// <summary>
		/// 执行SQL语句，返回DataSet
		/// </summary>
		/// <param name="sql">要执行的SQL语句</param>
		/// <param name="tablename">DataSet中要填充的表名</param>
		/// <returns>返回dataSet类型的执行结果</returns>
		public  DataSet GetDataSet(string sql,string tablename)
		{

			DataSet ds= new DataSet();
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlDataAdapter da=new SqlDataAdapter(sql,con);
		
			try
			{
				da.Fill(ds,tablename);
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			finally
			{
				con.Close();
				con.Dispose();
				da.Dispose();
			}
			return ds;
		}
		//重载
		public static DataSet GetDataSet(SqlDataAdapter da,string sql,string tablename)
		{
			DataSet ds= new DataSet();
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlDataAdapter da1=new SqlDataAdapter(sql,con);
			da=da1;
			try
			{
				da.Fill(ds,tablename);
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			finally
			{
				con.Close();
				con.Dispose();
				da.Dispose();
			}
			return ds;
		}
		#endregion

		#region  执行SQL语句，返回DataTable
		/// <summary>
		/// 执行SQL语句，返回DataTable
		/// </summary>
		/// <param name="sql">要执行的SQL语句</param>
		/// <returns>返回DataTable类型的执行结果</returns>
		public DataTable GetDataTable(string sql)  
		{
			DataSet ds= new DataSet();
			
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlDataAdapter da=new SqlDataAdapter(sql,con);
			try
			{
				da.Fill(ds,"tb");
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			finally
			{
				con.Close();
				con.Dispose();
				da.Dispose();
			}
			DataTable result=ds.Tables["tb"];
			return result;
		}
		#endregion

		#region  执行SQL语句并返回受影响的行数
		/// <summary>
		/// 执行SQL语句并返回受影响的行数
		/// </summary>
		/// <param name="sql">要执行的SQL语句</param>
		/// <returns>返回Int类型的受影响的行数</returns>
		public int GetCount(string sql)
		{
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlCommand cmd=new SqlCommand(sql,con);
			try
			{
				con.Open();
				int count=(int)cmd.ExecuteScalar();
				return count;
			}
			catch 
			{
				return 0;
			}
			finally
			{
				con.Close();
				con.Dispose();
				cmd.Dispose();
			}
		}
		#endregion

     
		#region 验证用户是否合法管理员
		/// <summary>
		/// 验证用户是否合法管理员
		/// </summary>
		/// <param name="strname">要验证的用户名</param>
		/// <param name="strpwd">要验证的密码</param>
		/// <returns>返回BOOL值，True为验证成功</returns>
		public bool CheckAdmin(string strname,string strpwd)
		{
			string sql;
			
			strname=NameReplace(strname);
			sql="select count(1) from manager where ManagerID='"+ strname +"' and ManagerPWD='"+ strpwd +"'";
			if(GetCount(sql)>0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		#endregion
		
		#region 验证用户是否合法用户
		/// <summary>
		/// 验证用户是否合法用户
		/// </summary>
		/// <param name="strname">要验证的用户名</param>
		/// <param name="strpwd">要验证的密码</param>
		/// <returns>返回BOOL值，True为验证成功</returns>
		public bool CheckUser (string strname,string strpwd)
		{
			string sql;
			strname=NameReplace(strname);
			if(strpwd=="")
				sql="select count(1) from yhb where yhm='"+strname+"'";
			else
				sql="select count(1) from yhb where yhm='"+strname+"'and mm='"+strpwd+"'";
			if(GetCount(sql)>0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		#endregion

		#region 注册新的用户
		/// <summary>
		/// 注册新的用户
		/// </summary>
		/// <param name="strname">要注册的用户名</param>
		/// <param name="strpwd">要注册的密码</param>
		/// <returns>返回BOOL值，True为验证成功</returns>
		public bool  newUser (string stuid,string username,string sex,string major,string tel,string address,string btime,string password)
		{
			string sql;
			username=NameReplace(username);
			 
			sql="select count(1) from student where stuid='"+stuid+"'";
			if(GetCount(sql)>0)
			{
				return false;
			}
			
			string sql2;
			sql2="insert into student (stuid,name,sex,major,tel,address,btime,stupassword) values('"+stuid +"','"+username+"','"+sex+"','"+major+"','"+tel+"','"+address+"','"+btime+"','"+password+"')";
			ExecuteSQL(sql2);
			return true;
		}
		#endregion

		#region  过滤用户名中的非法字符
		/// <summary>
		/// 过滤用户名中的非法字符
		/// </summary>
		/// <param name="str">要被过滤的字符串</param>
		/// <returns>返回String类型的过滤后的字符串</returns>
		public string NameReplace(string str)
		{
			str=str.Trim();
			str=str.Replace("=","");
			str=str.Replace("'","");
			return str;
		}
		#endregion

//		#region//获得主键
//		public  Array GetPrimarykry()
//						 {
//						 
//							 //使用DataAdapter填充DataTable
//							 dataadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//							 dataadapter.Fill(Table);
//
//							 DataColumn[] cols;
//							 cols = Table.PrimaryKey;
//							 //注意不是cols是DataColumn数组，不是DataColumn变量。这样做主要是为了处理联合主键的问题。
//							 for(int i = 0; i < cols.Length; i++)
//						 {
//							 MessageBox.Show(cols[i].ColumnName);
//						 }
//
//						 }
//		#endregion

		//数据库数据更新(传DataSet和DataTable的对象) 
		#region//更新数据库方法 调试失败的，
		public DataSet UpdateDatabase(DataSet changedDs,string tableName) 

		{  
			string table=tableName;
			string sql="select * from "+table+"";
			DataSet ds= new DataSet();
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
//			SqlDataAdapter da=new SqlDataAdapter(sql,con);

			try 

			{ 

				SqlDataAdapter da = new SqlDataAdapter(sql,con); 

				SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da); 

				da.Update(changedDs,tableName); 

				changedDs.AcceptChanges(); 

				return changedDs;//返回更新了的数据库表 

			} 

			catch (Exception ex) 

			{ 

				MessageBox.Show(ex.Message,"数据库更新失败"); 

				throw; 

			} 

		} 
		#endregion

		#region //更新数据库
		public DataSet UpdateByDataSet(DataSet ds,string strTblName,string strConnection)
		{
   
  
			SqlConnection  conn = new SqlConnection(strConnection);         
			SqlCommand myCommand = new SqlCommand("select * from "+strTblName+"",conn);    

			SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand );   

			SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);     
			myAdapter.InsertCommand = myCommandBuilder .GetInsertCommand();
			myAdapter.UpdateCommand = myCommandBuilder .GetUpdateCommand();
			myAdapter.DeleteCommand = myCommandBuilder .GetDeleteCommand();
			try
			{


				conn.Open();

			
				myAdapter.Update(ds,strTblName);  
				
				conn.Close();
			
			}//数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds }
				
			catch(Exception ex)
			{
				MessageBox.Show(ex.ToString());
			}
			return ds;  
		}

		#endregion
	}
}