dataaccess.cs

来自「数据库开发与实例（1+1混合数据库开发实例丛书）人事管理系统的源代码」· CS 代码 · 共 213 行

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace HRMan
{
    /// <summary>
    /// DataAccess类的摘要说明。
    /// </summary>
    class DataAccess
    {
        
		SqlConnection conn;
		static string Str = "Data Source=IIZ;Initial Catalog=HRManBeifen;Integrated Security=SSPI;";

		public DataAccess()
		{
		    conn = new SqlConnection(Str);
			conn.Open();
		}
		

       
		public int ExeSQL(string sql)
		{
		    SqlCommand cmd = new SqlCommand(sql,this.conn);
			try
			{
			    cmd.ExecuteNonQuery();
				return 0;
			}
			catch(System.Data.SqlClient.SqlException ex)
			{			    
                MessageBox.Show(ex.Message.ToString());
                return -1;
			}
			finally
			{
			    cmd.Dispose();
				this.conn.Close();
			}
		}
		// 判断数据库是否有记录,有记录则返回真，无则返回假
		public bool IsRead(string sql)
		{
			SqlCommand cmd = new SqlCommand(sql,this.conn);
			SqlDataReader dr = cmd.ExecuteReader();
			try
			{
				if(dr.Read())
				{
					return true;
				}
				else
				{
					return false;
				}
			}
			catch(System.Data.SqlClient.SqlException ex)
			{
                MessageBox.Show(ex.Message.ToString());
                return false;
			}
			finally
			{
				dr.Close();
				cmd.Dispose();
				this.conn.Close();
			}
		}
		
		public void ExeSQLs(string []sql)
		{
            SqlCommand cmd = new SqlCommand();// OleDbCommand();
			int j = sql.Length;
			SqlTransaction transaction = this.conn.BeginTransaction();
			try
			{
			    cmd.Connection = this.conn;
				cmd.Transaction = transaction;
				foreach(string str in sql)
				{
				    cmd.CommandText = str;
					cmd.ExecuteNonQuery();
				}
				transaction.Commit();
			}
			catch(System.Data.SqlClient.SqlException ex)
			{
				transaction.Rollback();
                MessageBox.Show(ex.Message.ToString());
			}
			finally
			{
			    cmd.Dispose();
				this.conn.Close();
			}
		}
		
		public DataTable ExeSQLdt(string sql)
		{
			try
			{
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
				DataTable dt = new DataTable();
				da.Fill(dt);
				return dt;
			}
			catch(System.Data.SqlClient.SqlException ex)
			{
                MessageBox.Show(ex.Message.ToString());
                return null ;
			}
			finally
			{
			    this.conn.Close();
			}
		}
		
		public void ReadImage(int Id,PictureBox pb)
		{
			
			try
			{
				string sql = "select * from Photo where phId=" + Id;
                SqlCommand cmd = new SqlCommand(sql, this.conn);
				SqlDataReader dr = cmd.ExecuteReader();
				if(dr.Read())
				{
					MemoryStream ms = new MemoryStream((byte[])dr["pType"]);
					pb.Image = Image.FromStream(ms,true);
					dr.Close();
					cmd.Dispose();
					this.conn.Close(); 
				}
				else
				{
					pb.Image = null;
				}
				
			}
			catch(Exception ee)
			{
				MessageBox.Show(ee.ToString());
			}
			
		}
	
		public void WriteImage(string path,int Id)
		{
			string sqlUpdate = "update Photo set pType=@pType where phId=" + Id;//更新数据库语句
			string sqlInsert = "insert into Photo values (@phId,@pType)";//插入数据库语句
			string sqlSelect = "select * from Photo where phId=" + Id;//查询数据库语句
			DataAccess DA = new DataAccess();
			if(DA.IsRead(sqlSelect) == true)
			{
				try
				{
					DialogResult result = MessageBox.Show("相片已存在，是否替换?","提示!",MessageBoxButtons.YesNo,MessageBoxIcon.Question);
					if(result == DialogResult.Yes)
					{
                        SqlCommand cmd = new SqlCommand(sqlUpdate, this.conn);
						byte [] b = new byte[1024000];
						FileStream fs = new FileStream(path,FileMode.Open,FileAccess.Read);
						fs.Read(b,0,b.Length);
                        cmd.Parameters.Add("@pType", SqlDbType.VarBinary, (int)fs.Length);
						cmd.Parameters["@pType"].Value = b;
						cmd.ExecuteNonQuery();
						cmd.Dispose();
						this.conn.Close();
					}
					else
					{
						return ;
					}
				}
				catch(Exception ee)
				{
					MessageBox.Show(ee.Message.ToString());
				}
			}
			else
			{
				try
				{
                    SqlCommand cmd = new SqlCommand(sqlInsert, this.conn);
					byte [] b = new byte[1024000];
					FileStream fs = new FileStream(path,FileMode.Open,FileAccess.Read);
					fs.Read(b,0,b.Length);
                    cmd.Parameters.Add("@phId", SqlDbType.Int);
                    cmd.Parameters.Add("@pType", SqlDbType.VarBinary, (int)fs.Length);
					cmd.Parameters["@phId"].Value = Id;
					cmd.Parameters["@pType"].Value = b;
					cmd.ExecuteNonQuery();
					cmd.Dispose();
					this.conn.Close();
				}
				catch(Exception ee)
				{
					MessageBox.Show(ee.Message.ToString());
				}
			}
		}
		
    }
}