sqlhelper.cs

来自「我的sqlhelper 大家一起研究一下」· CS 代码 · 共 636 行 · 第 1/2 页

							break;

						case "int":
						{
							sqlDbType = SqlDbType.Int;
						}
							break;

						case "smalldatetime":
						{
							sqlDbType = SqlDbType.SmallDateTime;
						}
							break;

						case "real":
						{
							sqlDbType= SqlDbType.Real;
						}
							break;

						case "money":
						{
							sqlDbType = SqlDbType.Money;
						}
							break;

						case "datetime":
						{
							sqlDbType = SqlDbType.DateTime;
						}
							break;	
								
						case "float":
						{
							sqlDbType = SqlDbType.Float;
						}
							break;

						case "ntext":
						{
							sqlDbType = SqlDbType.NText;
						}
							break;

						case "bit":
						{
							sqlDbType = SqlDbType.Bit;
						}
							break;

						case "decimal":
						{
							sqlDbType = SqlDbType.Decimal;
						}
							break;

						case "smallmoney":
						{
							sqlDbType = SqlDbType.SmallMoney;
						}
							break;

						case "BigInt":
						{
							sqlDbType = SqlDbType.BigInt;
						}
							break;

						case "varbinary":
						{
							sqlDbType = SqlDbType.VarBinary;
						}
							break;

						case "varchar":
						{
							sqlDbType = SqlDbType.VarChar;
						}
							break;

						case "binary":
						{
							sqlDbType = SqlDbType.Binary;
						}
							break;

						case "char":
						{
							sqlDbType = SqlDbType.Char;
						}
							break;

						case "timestamp":
						{
							sqlDbType = SqlDbType.Timestamp;
						}
							break;

						case "nvarchar":
						{
							sqlDbType = SqlDbType.NVarChar;
						}
							break;

						case "nchar":
						{
							sqlDbType = SqlDbType.NChar;
						}
							break;

						default:
							break;
					}

					//当前参数名
					string parameterName = Convert.ToString(this.dataTableRetunParameters.Rows[i]["Name"]);

					
					SqlParameter addParameter = new SqlParameter(parameterName,sqlDbType);

					//输入输出类型
					if(Convert.ToString(this.dataTableRetunParameters.Rows[i]["Isoutparam"]) == "INOUT")
					{
						addParameter.Direction = ParameterDirection.Output;

						//当前存储过程有返回值
						this.haveOutValue = true;
					}

					else
					{

						if (this.useNoNmae == true)
						{
							addParameter.Value = sqlDbType == SqlDbType.Bit?Convert.ToByte(this.valueNoName[i]):this.valueNoName[i];
						}
						else
						{
							addParameter.Value = sqlDbType == SqlDbType.Bit?Convert.ToByte(this.valueHaveName[parameterName]):this.valueHaveName[parameterName];
						
						}

					}

					this.mySqlCommand.Parameters.Add(addParameter);
				}
			}

			return returnSqlCommand;


		}

		/// <summary>
		/// 返回存储过程中的返回值的值
		/// </summary>
		private void BackOutValue()
		{
			if (this.haveOutValue == true)
			{
				for(int i=0;i<=this.dataTableRetunParameters.Rows.Count - 1;i++)
				{
					if (Convert.ToString(this.dataTableRetunParameters.Rows[i]["Isoutparam"])=="INOUT")
					{
						this.outValue.Add(Convert.ToString(this.dataTableRetunParameters.Rows[i]["Name"]),this.mySqlCommand.Parameters[Convert.ToString(this.dataTableRetunParameters.Rows[i]["Name"])].Value);
					}
				}
			}
			this.Conn.Close();
			this.dataTableRetunParameters = null;
		 }
		#endregion

		#region 公开方法

		/// <summary>
		/// 通过存储过程的名称执行存储过程,返回 DataSet。
		/// </summary>
		/// <param name="procedureName">存储过程的名称</param>
		/// <returns>返回类型 DataSet </returns>
		public DataSet RunProcReturnDataSet(string procedureName)
		{
				DataSet returnDataSet= new DataSet();

				SqlDataAdapter ap = new SqlDataAdapter(this.BeginAddParameters(procedureName));

				ap.Fill(returnDataSet);

				this.BackOutValue();

				return returnDataSet;
		}

		/// <summary>
		/// 通过存储过程的名称执行存储过程,返回 DataSet。此方法必须先设置好属性 ProcedureName的值。
		/// </summary>
		/// <returns>返回类型 DataSet </returns>
		public DataSet RunProcReturnDataSet()
		{
			return RunProcReturnDataSet(this.myProName);
		}

		/// <summary>
		/// 通过存储过程的名称执行存储过程,返回 DataTable。
		/// </summary>
		/// <param name="procedureName">存储过程的名称</param>
		/// <param name="tableName">指定所返回的DataTable的TableName</param>
		/// <returns>返回类型 DataTable </returns>
		public DataTable RunProcReturnDataTable(string procedureName,string tableName)
		{
			
			DataTable returnDataTable = new DataTable();

			if(tableName!="")
			{
				returnDataTable.TableName = tableName;
			}

			SqlDataAdapter ap   = new SqlDataAdapter(this.BeginAddParameters(procedureName));

			ap.Fill(returnDataTable);

			this.BackOutValue();

			return returnDataTable;
		}

		/// <summary>
		/// 通过存储过程的名称执行存储过程,返回 DataTable。
		/// </summary>
		/// <param name="procedureName">存储过程的名称</param>
		/// <returns>返回类型 DataTable </returns>
		public DataTable RunProcReturnDataTable(string procedureName)
		{
			return RunProcReturnDataTable(procedureName,"");
		}

		/// <summary>
		/// 执行指定的存储过程。无返回类型。
		/// </summary>
		/// <param name="procedureName">存储过程名称</param>
		public void RunProcedure(string procedureName)
		{
			 this.BeginAddParameters(procedureName).ExecuteNonQuery();
			 this.BackOutValue();
		}

		/// <summary>
		/// 执行存储过程，此方法必须先设置好属性 ProcedureName的值。无返回类型。
		/// </summary>
		public void RunProcedure()
		{
			RunProcedure(this.myProName);
		}

		/// <summary>
		/// 执行SQL语句。返回 DataSet
		/// </summary>
		/// <param name="sql">所要执行的SQL语句</param>
		/// <param name="tableName">返回的表名</param>
		/// <returns>DataSet</returns>
		public DataSet RunSqlReturnDataSet(string sql,string tableName)
		{
			SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
			DataSet retrunDataSet = new DataSet();
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
			sqlDataAdapter.Fill(retrunDataSet,tableName);
			return retrunDataSet;
		}

		/// <summary>
		/// 执行SQL语句。返回 DataTable
		/// </summary>
		/// <param name="sql">所要执行的SQL语句</param>
		/// <param name="tableName">返回的表名</param>
		/// <returns>DataTable</returns>
		public DataTable RunSqlReturnDataTable(string sql,string tableName)
		{

			SqlCommand sqlCommand			 = new SqlCommand(sql,this.Conn);

			DataTable retrunDataTable		 = new DataTable();

			retrunDataTable.TableName		 = tableName;

			SqlDataAdapter sqlDataAdapter	 = new SqlDataAdapter(sqlCommand);

			sqlDataAdapter.Fill(retrunDataTable);

			return retrunDataTable;
		}

		/// <summary>
		/// 执行SQL语句。返回Boolean类型
		/// </summary>
		/// <param name="sql">所要执行的SQL语句</param>
		/// <returns>Boolean</returns>
		public Boolean  RunSql(string sql)
		{
			try
			{
				SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
				this.Conn.Open();
				sqlCommand.ExecuteNonQuery();
				this.Conn.Close();
				return true;
			}
			catch
			{
				return false;
			}
			
		}
		#endregion

	}
}