votedb.cs

ASP C#代码实例 适合初学人士学习使用

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Example_10_4
{
	/// <summary>
	/// VoteDB 的摘要说明。
	/// </summary>
	public class VoteDB
	{
		public static string SQLCONNECTIONSTRING = ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString();

		public SqlDataReader GetVotes()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetVotes",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}
			
			return(dr);
		}

		public int AddVote(String sItem)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_AddVote",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数	
			SqlParameter parameterItem = new SqlParameter("@Item",SqlDbType.VarChar,100);
			parameterItem.Value = sItem;
			myCommand.Parameters.Add(parameterItem);

			SqlParameter parameterVoteID = new SqlParameter("@VoteID",SqlDbType.Int,4);
			parameterVoteID.Direction = ParameterDirection.ReturnValue;
			myCommand.Parameters.Add(parameterVoteID);

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}

			return (int)parameterVoteID.Value;
		}

		public void UpdateVote(int nVoteID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateVote",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数	
			SqlParameter parameterVoteID = new SqlParameter("@VoteID",SqlDbType.Int,4);
			parameterVoteID.Value = nVoteID;
			myCommand.Parameters.Add(parameterVoteID);		

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}
		}

		public void DeleteVote(int nVoteID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteVote",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数	
			SqlParameter parameterVoteID = new SqlParameter("@VoteID",SqlDbType.Int,4);
			parameterVoteID.Value = nVoteID;
			myCommand.Parameters.Add(parameterVoteID);		

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}
		}
	}
}