c#&sql server

- ============================================= -- Author: <allen> -- Create date: <2007-11-12> -- Description: <确认员工单据时间是否有重叠> -- ============================================= CREATE PROCEDURE [dbo].[p_CheckBillEmpTimeRepeat] ( @ObjectName varchar(50), @ObjectID int, @EmployeeUVID varchar(20) , @BeginDate varchar(20), @EndDate varchar(20), @BeginTime varchar(10), @EndTime varchar(10), @Count int output ) AS begin tran declare @BeginDateTime smalldatetime ,@EndDateTime smalldatetime ,@IsRepeat bit set @BeginDateTime=Cast((@BeginDate+' '+@BeginTime)as smalldatetime) set @EndDateTime=Cast((@EndDate+' '+@EndTime)as smalldatetime) set @IsRepeat=0 if @ObjectName='Leave' begin select @Count=count(1) from tleave where ID<>@ObjectID and EmpUVID=@EmployeeUVID and ( (cast((Convert(varchar(10),BeginDate,120)+' '+BeginTime) as smalldatetime)>=@BeginDateTime and cast((Convert(varchar(10),BeginDate,120)+' '+BeginTime) as smalldatetime)<@EndDateTime) or (cast((Convert(varchar(10),EndDate,120)+' '+EndTime) as smalldatetime)>@BeginDateTime and cast((Convert(varchar(10),EndDate,120)+' '+EndTime) as smalldatetime)<=@EndDateTime) or (cast((Convert(varchar(10),EndDate,120)+' '+EndTime) as smalldatetime)<@BeginDateTime and cast((Convert(varchar(10),EndDate,120)+' '+EndTime) as smalldatetime)>@EndDateTime) ) end else if @ObjectName='Egress' begin select @Count=count(1) from tEgressEmployee where PKID<>@ObjectID and EmployeeUVID=@EmployeeUVID and ( (cast((Convert(varchar(10),BeginDate,120)+' '+BeginTime) as smalldatetime)>=@BeginDateTime and cast((Convert(varchar(10),BeginDate,120)+' '+BeginTime) as smalldatetime)<@EndDateTime) or (cast((Convert(varchar(10),EndDate,120)+' '+EndTime) as smalldatetime)>@BeginDateTime and cast((Convert(varchar(10),EndDate,120)+' '+EndTime) as smalldatetime)<=@EndDateTime) or (cast((Convert(varchar(10),EndDate,120)+' '+EndTime) as smalldatetime)<@BeginDateTime and cast((Convert(varchar(10),EndDate,120)+' '+EndTime) as smalldatetime)>@EndDateTime) ) end commit tran GO public static void RunProcedure(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); connection.Close(); } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } public DataSet GetList(int PageSize, int PageIndex, string strWhere) { SqlParameter[] parameters = { new SqlParameter("@tblName", SqlDbType.VarChar, 255), new SqlParameter("@fldName", SqlDbType.VarChar, 255), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@IsReCount", SqlDbType.Bit), new SqlParameter("@OrderType", SqlDbType.Bit), new SqlParameter("@strWhere", SqlDbType.VarChar,1000), }; parameters[0].Value = "vwProject"; parameters[1].Value = "CreateTime"; //parameters[0].Value = "f_GetProject()"; //parameters[1].Value = "CreateTime"; parameters[2].Value = PageSize; parameters[3].Value = PageIndex; parameters[4].Value = 0; parameters[5].Value = 0; parameters[6].Value = strWhere; //return DbHelperSQL.RunProcedure("p_GetAssetsByPage", parameters, "ds"); return DbHelperSQL.RunProcedure("p_GetRecordByPage", parameters, "ds"); } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand</returns> private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } return command; }