service.cs

用于c#.net数据库操作的类库。能把结婚集以数组的形成操作。

				// return...
				return outputValueParam.Value.ToString();
			}
		}
		public string GetIdBh(string BhId,System.DateTime DT,bool isRed)
		{
			return GetBh(BhId,DT,isRed);
		}
		/// <summary>
		/// 获取一个自动生成的编号(格式：前缀－001)
		/// </summary>
		/// <param name="BhId"> 编号前缀 </param>
		/// <param name="isRed"> 读？写？ </param>
		/// <returns> 生成的编号 </returns>
		public static string GetBh(string BhId,bool isRed)
		{
			return GetBh(BhId,System.DateTime.Now,isRed);
		}
		public string GetIdBh(string BhId,bool isRed)
		{
			return GetBh(BhId,isRed);
		}

		/// <summary>
		/// 根据DataRow生成插入数据库的SQL语句
		/// </summary>
		/// <param name="dr"> 数据行 </param>
		/// <param name="sTableName"> 表名 </param>
		/// <returns> SQL语句 </returns>
		public static string GetInsertSql(DataRow dr,string sTableName)
		{
			System.Text.StringBuilder sbfld=new System.Text.StringBuilder(255);
			System.Text.StringBuilder sbfldvalues=new System.Text.StringBuilder(255);
			bool isAdd=false;
			for(int i=0;i<dr.Table.Columns.Count;i++)
			{
				if(dr[i]!=System.DBNull.Value)
				{
					if(isAdd)
					{
						sbfld.Append(",");
						sbfldvalues.Append(",");
					}
					sbfld.Append("["+dr.Table.Columns[i].ColumnName+"]");
					if(dr.Table.Columns[i].DataType.ToString()=="System.Boolean")
					{
						if((bool)dr[i]){sbfldvalues.Append("1");}
						else {sbfldvalues.Append("0");}
					}
					else
					{
						sbfldvalues.Append("'"+dr[i].ToString()+"'");
					}
					isAdd=true;
				}//if(dr[i]!=System.DBNull.Value)
			}//for
			if(isAdd)
			{
				System.Text.StringBuilder sql=new System.Text.StringBuilder(500);
				sql.Append("insert into "+sTableName+" (");
				sql.Append(sbfld.ToString()+") values (");
				sql.Append(sbfldvalues.ToString()+")");
				return sql.ToString();
			}
			else
			{
				return "";
			}

		}

		/// <summary>
		/// 根据DataRow生成 [UPDATE] 数据的SQL语句
		/// </summary>
		/// <param name="dr"> 数据行 </param>
		/// <param name="sTableName"> 表名 </param>
		/// <returns> SQL语句 </returns>
		public static string GetUpdatetSql(DataRow dr,string sTableName)
		{
			System.Text.StringBuilder sql=new System.Text.StringBuilder(500);
			bool isAdd=false;
			for(int i=0;i<dr.Table.Columns.Count;i++)
			{
				if(dr[i]!=System.DBNull.Value)
				{
					if(isAdd)
					{
						sql.Append(",");
					}
					sql.Append(dr.Table.Columns[i].ColumnName);
					sql.Append("='"+dr[i].ToString()+"'");
					isAdd=true;
				}//if(dr[i]!=System.DBNull.Value)
				else
				{
					if(isAdd)
					{
						sql.Append(",");
					}
					sql.Append(dr.Table.Columns[i].ColumnName);
					sql.Append("=NULL");
					isAdd=true;
          
				}
			}//for
			if(isAdd)
			{
				System.Text.StringBuilder upsql=new System.Text.StringBuilder(500);
				upsql.Append("update "+sTableName+" set ");
				upsql.Append(sql.ToString());
				return upsql.ToString();
			}
			else
			{
				return "";
			}

		}
		public IDataSetHelper GetDataSetHelper(string commandText)
		{
			return GetDataSetHelper(commandText,"Table");
		}
		public IDataSetHelper GetDataSetHelper(string commandText,string TableName)
		{
			// do we have a connection?
			SqlConnection connection = null;

				connection = new SqlConnection(EnterpriseApplication.Application.ConnectionString);
				connection.Open();


			// run...
			DataSet dataset = new DataSet();
			SqlDataAdapter adapter = new SqlDataAdapter();
			adapter.SelectCommand=new SqlCommand(commandText,connection);
			adapter.Fill(dataset,TableName);
			//adapter.Dispose();

			// close the connection...
			if(connection != null)
				connection.Close();
			DataSetHelperSql sqlhelper=new DataSetHelperSql( connection, adapter, dataset);

			// return...
			return sqlhelper;
		}

		public int ExeStroeProcedure(string spName,System.Data.DataRow dr)
		{
			// create a connection...
			System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(EnterpriseObjects.EnterpriseApplication.Application.ConnectionString);
			connection.Open();
			// create a command...
			System.Data.SqlClient.SqlCommand command = GetStroeProcedureParamsByID(spName);
			command.Connection=connection;
			command.CommandTimeout=0;

//			System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(spName, connection);
//			command.CommandType = System.Data.CommandType.StoredProcedure;
//			command=GetStroeProcedureParamsByID(command,spName);
			string strsp;

			try
			{
				for(int i=0;i<command.Parameters.Count;i++)
				{
					strsp=command.Parameters[i].ParameterName;
					strsp=strsp.Substring(1,strsp.Length-1);
					command.Parameters[i].Value=dr[strsp];
				}
				return command.ExecuteNonQuery();
			}
			catch
			{
				return -1;
			}
			finally
			{
				// cleanup...
				command.Dispose();
				connection.Close();
			}

		}
		/// <summary>
		/// 为传入SqlCommand对象建立存储过程的参数数组
		/// </summary>
		/// <remarks >参数只付值ParameterName,SqlDbType,IsNullable,Direction</remarks>
		/// <param name="spid">存储过程</param>
		/// <returns>SqlCommand</returns>
		public SqlCommand GetStroeProcedureParamsByID(string spName)
		{ 
			SqlCommand command;

			command=_spParameterCache[spName] as SqlCommand;
			if(null!=command)return command;

			command=new SqlCommand(spName);
			command.CommandType=CommandType.StoredProcedure;

			SqlConnection connection = new SqlConnection(EnterpriseApplication.Application.ConnectionString);
			connection.Open();

			SqlCommand command1= new SqlCommand("sp_procedure_params_rowset",connection);

			command1.CommandType = CommandType.StoredProcedure;
			command1.Parameters.Add(new SqlParameter("@procedure_name", SqlDbType.NVarChar, 255));
			command1.Parameters[0].Value = spName;

			try
			{
				using (SqlDataReader reader1 = command1.ExecuteReader())
				{
					SqlParameter parameter1 = null;
					while (reader1.Read())
					{
						parameter1 = new SqlParameter();
						parameter1.ParameterName = (string) reader1["PARAMETER_NAME"];
						parameter1.SqlDbType = getSqlDbType((short) reader1["DATA_TYPE"], (string) reader1["TYPE_NAME"]);
						object obj1 = reader1["CHARACTER_MAXIMUM_LENGTH"];
						if (obj1 is int)
						{
							parameter1.Size = (int) obj1;
						}
						parameter1.Direction = getParameterDirection((short) reader1["PARAMETER_TYPE"]);
						if (parameter1.SqlDbType == SqlDbType.Decimal)
						{
							parameter1.Scale = (byte) (((short) reader1["NUMERIC_SCALE"]) & 0xff);
							parameter1.Precision = (byte) (((short) reader1["NUMERIC_PRECISION"]) & 0xff);
						}
						command.Parameters.Add(parameter1);
					}
					reader1.Close();
					connection.Close();
				}
			}
			finally
			{
				if(null!=connection)
				connection.Close();
				command1.Connection = null;
			}
			_spParameterCache[spName]=command;
			return command;
		}
		/// <summary>
		/// 为传入SqlCommand对象建立存储过程的参数数组
		/// </summary>
		/// <remarks >参数只付值ParameterName,SqlDbType,IsNullable,Direction</remarks>
		/// <param name="spid">存储过程</param>
		/// <returns>SqlCommand</returns>
//		public SqlCommand GetStroeProcedureParamsByID(System.Data.SqlClient.SqlCommand command,string spName)
//		{ 
////			string sql="select a.name,b.name as type,a.length,a.isoutparam,a.isnullable  from ";
////			sql+=" syscolumns a,systypes b,sysobjects c where a.xtype=b.xtype and a.id=c.id and c.name='";
////			sql+=spName+"'";
////			System.Data.DataTable dt=this.GetDataSet(sql).Tables[0];
//
//			SqlConnection connection = new SqlConnection(EnterpriseApplication.Application.ConnectionString);
//			connection.Open();
//
//			SqlCommand command1= new SqlCommand("sp_procedure_params_rowset",connection);
//
//			command1.CommandType = CommandType.StoredProcedure;
//			command1.Parameters.Add(new SqlParameter("@procedure_name", SqlDbType.NVarChar, 255));
//			command1.Parameters[0].Value = spName;
//
//			try
//			{
//				using (SqlDataReader reader1 = command1.ExecuteReader())
//				{
//					SqlParameter parameter1 = null;
//					while (reader1.Read())
//					{
//						parameter1 = new SqlParameter();
//						parameter1.ParameterName = (string) reader1["PARAMETER_NAME"];
//						parameter1.SqlDbType = getSqlDbType((short) reader1["DATA_TYPE"], (string) reader1["TYPE_NAME"]);
//						object obj1 = reader1["CHARACTER_MAXIMUM_LENGTH"];
//						if (obj1 is int)
//						{
//							parameter1.Size = (int) obj1;
//						}
//						parameter1.Direction = getParameterDirection((short) reader1["PARAMETER_TYPE"]);
//						if (parameter1.SqlDbType == SqlDbType.Decimal)
//						{
//							parameter1.Scale = (byte) (((short) reader1["NUMERIC_SCALE"]) & 0xff);
//							parameter1.Precision = (byte) (((short) reader1["NUMERIC_PRECISION"]) & 0xff);
//						}
//						command.Parameters.Add(parameter1);
//					}
//				}
//			}
//			finally
//			{
//				connection.Close();
//				command1.Connection = null;
//			}
//
////			// parameters...
////			System.Data.SqlClient.SqlParameter sqlPter;
////			for(int i=0;i<dt.Rows.Count;i++)
////			{
////				//参数名称
////				string pname=dt.Rows[i]["name"].ToString();
////				SqlDbType ptp=getSqlDbType(dt.Rows[i]["type"].ToString());
////				//参数的长度定义
////				int flength=Convert.ToInt32(dt.Rows[i]["length"].ToString());
////				//创建一个参数
////				if(ptp==SqlDbType.VarChar||ptp==SqlDbType.Char||ptp==SqlDbType.NChar||ptp==SqlDbType.NVarChar)
////				{
////					sqlPter=new SqlParameter(pname,ptp,flength);
////				}
////				else
////				{
////					sqlPter=new SqlParameter(pname,ptp);
////					
////				}
////				//sqlPter.IsNullable=(bool)dt.Rows[i]["isnullable"];
////				//定义参数的INPUT和OUTPUT 
////				if((int)(dt.Rows[i]["isoutparam"])==1)
////				{
////					sqlPter.Direction =ParameterDirection.Output;
////				}
////				else
////				{
////					sqlPter.Direction =ParameterDirection.Input;
////				}
////				command.Parameters.Add(sqlPter);
////			}
////			sqlPter=new SqlParameter("@RETURN_VALUE",SqlDbType.Int);
////			sqlPter.Direction =ParameterDirection.ReturnValue;
////			command.Parameters.Add(sqlPter);
//
//			return command;
//		}
		public System.Data.DataSet GetDataSetByStroeProcedure(string spName,DbParams dbparam)
		{
			return GetDataSetByStroeProcedure(spName,dbparam,typeof(System.Data.DataSet));
		}
		public System.Data.DataSet GetDataSetByStroeProcedure(string spName,DbParams dbparam,Type type)
		{
			// create a connection...
			System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(EnterpriseObjects.EnterpriseApplication.Application.ConnectionString);
			connection.Open();
			// create a command...
			System.Data.SqlClient.SqlCommand command = GetStroeProcedureParamsByID(spName);
			command.Connection=connection;
			command.CommandTimeout=0;

//			System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(spName, connection);
//			command.CommandType = System.Data.CommandType.StoredProcedure;
//			command=GetStroeProcedureParamsByID(command,spName);
			//DbParams ResultDbparams=new DbParams();
			try
			{
				for(int i=0;i<command.Parameters.Count;i++)
				{
					command.Parameters[i].Value=dbparam.GetValueByParam(command.Parameters[i].ParameterName);
				}
				System.Data.SqlClient.SqlDataAdapter sqladp=new SqlDataAdapter(command);
				System.Data.DataSet dataset = ((System.Data.DataSet)(System.Activator.CreateInstance(type)));
				sqladp.Fill(dataset);
                //xuc add 20081022取存储过程的外参数
                for (int i = 0; i < command.Parameters.Count; i++)
                {
                    if (command.Parameters[i].Direction == ParameterDirection.Output || command.Parameters[i].Direction == ParameterDirection.InputOutput)
                    {
                        dbparam[command.Parameters[i].ParameterName.Replace("@","")] = command.Parameters[i].Value;
                    }
                    else if (command.Parameters[i].Direction == ParameterDirection.ReturnValue)
                    {
                        dbparam.ReturnValue = (int)command.Parameters[i].Value;
                    }
                }//end add 

                return dataset;
			}
			catch
			{
				return null;
			}
			finally
			{
				// cleanup...
				command.Dispose();
				connection.Close();
			}
		}


		public DbParams ExeStroeProcedure(string spName,DbParams dbparam)
		{
			// create a connection...
			using(System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(EnterpriseObjects.EnterpriseApplication.Application.ConnectionString))
			{
				connection.Open();
				// create a command...
				System.Data.SqlClient.SqlCommand command = GetStroeProcedureParamsByID(spName);
				command.Connection=connection;
				command.CommandTimeout=0;
				try
				{
					for(int i=0;i<command.Parameters.Count;i++)
					{
						command.Parameters[i].Value=dbparam.GetValueByParam(command.Parameters[i].ParameterName);
					}
					dbparam.ResultCount=command.ExecuteNonQuery();
					for(int i=0;i<command.Parameters.Count;i++)