using System;
using System.Data;
using System.Data.OracleClient;
using System.Web.UI.WebControls;
namespace SiFenManager.Util
{
/// <summary>
/// 数据库通用操作类
/// </summary>
public class Database
{
protected OracleConnection con;//连接对象
public Database()
{
con=new OracleConnection(DafangFramework.AppConfig.DataBaseConnectionString);
}
public Database(string constr)
{
con=new OracleConnection(constr);
}
#region 打开数据库连接
/// <summary>
/// 打开数据库连接
/// </summary>
private void Open()
{
//打开数据库连接
if(con.State==ConnectionState.Closed)
{
try
{
//打开数据库连接
con.Open();
}
catch(Exception e)
{
throw e;
}
}
}
#endregion
#region 关闭数据库连接
/// <summary>
/// 关闭数据库连接
/// </summary>
private void Close()
{
//判断连接的状态是否已经打开
if(con.State==ConnectionState.Open)
{
con.Close();
}
}
#endregion
#region 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )
/// <summary>
/// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>OracleDataReader</returns>
public OracleDataReader ExecuteReader(string sql)
{
OracleDataReader myReader;
Open();
OracleCommand cmd = new OracleCommand(sql, con);
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
#endregion
#region 执行带参数的SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string sql, params OracleParameter[] cmdParms)
{
OracleCommand cmd = new OracleCommand();
{
try
{
PrepareCommand(cmd, con, null, sql, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.OracleClient.OracleException e)
{
throw e;
}
}
}
#endregion
#region 执行带参数的SQL语句
/// <summary>
/// 执行不带参数的SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
public void ExecuteSql(string sql)
{
OracleCommand cmd = new OracleCommand(sql,con);
try
{
Open();
cmd.ExecuteNonQuery();
Close();
}
catch (System.Data.OracleClient.OracleException e)
{
Close();
throw e;
}
}
#endregion
#region 执行SQL语句，返回数据到DataSet中
/// <summary>
/// 执行SQL语句，返回数据到DataSet中
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回DataSet</returns>
public DataSet GetDataSet(string sql)
{
DataSet ds=new DataSet();
try
{
Open();//打开数据连接
OracleDataAdapter adapter=new OracleDataAdapter(sql,con);
adapter.Fill(ds);
}
catch//(Exception ex)
{
}
finally
{
Close();//关闭数据库连接
}
return ds;
}
#endregion
#region 执行SQL语句，返回数据到自定义DataSet中
/// <summary>
/// 执行SQL语句，返回数据到DataSet中
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="DataSetName">自定义返回的DataSet表名</param>
/// <returns>返回DataSet</returns>
public DataSet GetDataSet(string sql,string DataSetName)
{
DataSet ds=new DataSet();
Open();//打开数据连接
OracleDataAdapter adapter=new OracleDataAdapter(sql,con);
adapter.Fill(ds,DataSetName);
Close();//关闭数据库连接
return ds;
}
#endregion
#region 执行Sql语句,返回带分页功能的自定义dataset
/// <summary>
/// 执行Sql语句,返回带分页功能的自定义dataset
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="CurrPageIndex">当前页</param>
/// <param name="DataSetName">返回dataset表名</param>
/// <returns>返回DataSet</returns>
public DataSet GetDataSet(string sql,int PageSize,int CurrPageIndex,string DataSetName)
{
DataSet ds=new DataSet();
Open();//打开数据连接
OracleDataAdapter adapter=new OracleDataAdapter(sql,con);
adapter.Fill(ds,PageSize * (CurrPageIndex - 1), PageSize,DataSetName);
Close();//关闭数据库连接
return ds;
}
#endregion
#region 执行SQL语句，返回记录总数
/// <summary>
/// 执行SQL语句，返回记录总数
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回记录总条数</returns>
public int GetRecordCount(string sql)
{
int recordCount = 0;
Open();//打开数据连接
OracleCommand command = new OracleCommand(sql,con);
OracleDataReader dataReader = command.ExecuteReader();
while(dataReader.Read())
{
recordCount++;
}
dataReader.Close();
Close();//关闭数据库连接
return recordCount;
}
#endregion
#region 统计某表记录总数
/// <summary>
/// 统计某表记录总数
/// </summary>
/// <param name="KeyField">主键/索引键</param>
/// <param name="TableName">数据库.用户名.表名</param>
/// <param name="Condition">查询条件</param>
/// <returns>返回记录总数</returns>
public int GetRecordCount(string keyField, string tableName, string condition)
{
int RecordCount = 0;
string sql = "select count(" + keyField + ") as count from " + tableName + " " + condition;
DataSet ds = GetDataSet(sql);
if (ds.Tables[0].Rows.Count > 0)
{
RecordCount =Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
ds.Clear();
ds.Dispose();
return RecordCount;
}
/// <summary>
/// 统计某表记录总数
/// </summary>
/// <param name="Field">可重复的字段</param>
/// <param name="tableName">数据库.用户名.表名</param>
/// <param name="condition">查询条件</param>
/// <param name="flag">字段是否主键</param>
/// <returns>返回记录总数</returns>
public int GetRecordCount(string Field, string tableName, string condition, bool flag)
{
int RecordCount = 0;
if (flag)
{
RecordCount = GetRecordCount(Field, tableName, condition);
}
else
{
string sql = "select count(distinct(" + Field + ")) as count from " + tableName + " " + condition;
DataSet ds = GetDataSet(sql);
if (ds.Tables[0].Rows.Count > 0)
{
RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
ds.Clear();
ds.Dispose();
}
return RecordCount;
}
#endregion
#region 统计某表分页总数
/// <summary>
/// 统计某表分页总数
/// </summary>
/// <param name="keyField">主键/索引键</param>
/// <param name="tableName">表名</param>
/// <param name="condition">查询条件</param>
/// <param name="pageSize">页宽</param>
/// <param name="RecordCount">记录总数</param>
/// <returns>返回分页总数</returns>
public int GetPageCount(string keyField, string tableName, string condition, int pageSize, int RecordCount)
{
int PageCount = 0;
PageCount = (RecordCount % pageSize) > 0 ? (RecordCount / pageSize) + 1 : RecordCount / pageSize;
if (PageCount < 1) PageCount = 1;
return PageCount;
}
/// <summary>
/// 统计某表分页总数
/// </summary>
/// <param name="keyField">主键/索引键</param>
/// <param name="tableName">表名</param>
/// <param name="condition">查询条件</param>
/// <param name="pageSize">页宽</param>
/// <returns>返回页面总数</returns>
public int GetPageCount(string keyField, string tableName, string condition, int pageSize, ref int RecordCount)
{
RecordCount = GetRecordCount(keyField, tableName, condition);
return GetPageCount(keyField, tableName, condition, pageSize, RecordCount);
}
/// <summary>
/// 统计某表分页总数
/// </summary>
/// <param name="Field">可重复的字段</param>
/// <param name="tableName">表名</param>
/// <param name="condition">查询条件</param>
/// <param name="pageSize">页宽</param>
/// <param name="flag">是否主键</param>
/// <returns>返回页页总数</returns>
public int GetPageCount(string Field, string tableName, string condition, ref int RecordCount, int pageSize, bool flag)
{
RecordCount = GetRecordCount(Field, tableName, condition, flag);
return GetPageCount(Field, tableName, condition, pageSize, ref RecordCount);
}
#endregion
#region Sql分页函数
/// <summary>
/// 构造分页查询SQL语句
/// </summary>
/// <param name="KeyField">主键</param>
/// <param name="FieldStr">所有需要查询的字段(field1,field2...)</param>
/// <param name="TableName">库名.拥有者.表名</param>
/// <param name="where">查询条件1(where ...)</param>
/// <param name="order">排序条件2(order by ...)</param>
/// <param name="CurrentPage">当前页号</param>
/// <param name="PageSize">页宽</param>
/// <returns>SQL语句</returns>
public string JoinPageSQL(string KeyField, string FieldStr, string TableName, string Where, string Order, int CurrentPage, int PageSize)
{
string sql = null;
if (CurrentPage == 1)
{
sql = "select " + CurrentPage * PageSize + " " + FieldStr + " from " + TableName + " " + Where + " " + Order + " ";
}
else
{
sql = "select * from (";
sql += "select " + CurrentPage * PageSize + " " + FieldStr + " from " + TableName + " " + Where + " " + Order + ") a ";
sql += "where " + KeyField + " not in (";
sql += "select " + (CurrentPage - 1) * PageSize + " " + KeyField + " from " + TableName + " " + Where + " " + Order + ")";
}
return sql;
}
/// <summary>
/// 构造分页查询SQL语句
/// </summary>
/// <param name="Field">字段名(非主键)</param>
/// <param name="TableName">库名.拥有者.表名</param>
/// <param name="where">查询条件1(where ...)</param>
/// <param name="order">排序条件2(order by ...)</param>
/// <param name="CurrentPage">当前页号</param>
/// <param name="PageSize">页宽</param>
/// <returns>SQL语句</returns>
public string JoinPageSQL(string Field, string TableName,string Where, string Order, int CurrentPage, int PageSize)
{
string sql = null;
if (CurrentPage == 1)
{
sql = "select rownum " + CurrentPage * PageSize + " " + Field + " from " + TableName + " " + Where + " " + Order + " group by " + Field;
}
else
{
sql = "select * from (";
sql += "select rownum " + CurrentPage * PageSize + " " + Field + " from " + TableName + " " + Where + " " + Order + " group by " + Field + " ) a ";
sql += "where " + Field + " not in (";
sql += "select rownum " + (CurrentPage - 1) * PageSize + " " + Field + " from " + TableName + " " + Where + " " + Order + " group by " + Field + ")";
}
return sql;
}
#endregion
#region 根据系统时间动态生成各种查询语句(现已经注释掉，以备以后使用)
// #region 根据查询时间的条件，动态生成查询语句
// /// <summary>
// /// 根据查询时间的条件，动态生成查询语句
// /// </summary>
// /// <param name="starttime">开始时间</param>
// /// <param name="endtime">结束时间</param>
// /// <param name="dw">单位</param>
// /// <param name="startxsl">开始线损率</param>
// /// <param name="endxsl">结束线损率</param>
// /// <param name="danwei">单位字段</param>
// /// <param name="xiansunlv">线损率字段</param>
// /// <param name="tablehz">表后缀</param>
// /// <returns>SQL语句</returns>
// public string SQL(DateTime starttime,DateTime endtime,string dw,float startxsl,float endxsl,string danwei,string xiansunlv,string tablehz)
// {
//
// string sql=null;
// //将输入的时间格式转换成固定的格式"yyyy-mm-dd"
// string zstarttime=starttime.GetDateTimeFormats('D')[1].ToString();
// string zendtime=endtime.GetDateTimeFormats('D')[1].ToString();
// string nTime=DateTime.Now.GetDateTimeFormats('D')[1].ToString();
//
//
// //取日期值的前六位，及年月值
// string sTime=zstarttime.Substring(0,4)+zstarttime.Substring(5,2);
// string eTime=zendtime.Substring(0,4)+zendtime.Substring(5,2);
// string nowTime=nTime.Substring(0,4)+nTime.Substring(5,2);
// //分别取日期的年和月
// int sy=Convert.ToInt32(zstarttime.Substring(0,4));
// int ey=Convert.ToInt32(zendtime.Substring(0,4));
// int sm=Convert.ToInt32(zstarttime.Substring(5,2));
// int em=Convert.ToInt32(zendtime.Substring(5,2));
// //相关变量定义
// int s;
// int e;
// int i;
// int j;
// int js;
// int nz;
// string x;
// //一，取当前表生成SQL语句
// if(sTime==nowTime&&eTime==nowTime)
// {
// sql="select * from "+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" ";
// }
// //二，取当前表和其他表生成SQL语句
// else if(sTime==nowTime&&eTime!=nowTime)
// {
// sql="select * from "+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
// //如果年份相等
// if(sy==ey)
// {
// s=Convert.ToInt32(sTime);
// e=Convert.ToInt32(eTime);
// for(i=s+1;i<e;i++)
// {
// i=i++;
// sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
// }
// sql+="select * from "+e.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" ";
// }
// //结束年份大于开始年份
// else
// {
// //1，先循环到起始时间和起始时间的12月
// s=Convert.ToInt32(sTime);
// x=zstarttime.Substring(0,4)+"12";
// nz=Convert.ToInt32(x);
// for(i=s+1;i<=nz;i++)
// {
// i=i++;
// sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
// }
// //2，循环两者相差年份
// for(i=sy+1;i<ey;i++)
// {
//
// for(j=1;j<=12;j++)
// {
// if(j<10)
// {
// sql+="select * from "+i.ToString()+"0"+j.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
// }
// else
// {
// sql+="select * from "+i.ToString()+j.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
// }
// }
// }
// //3，循环到结束的月份
// js=Convert.ToInt32(zendtime.Substring(0,4)+"00");
// for(i=js;i<Convert.ToInt32(eTime);i++)
// {
// i++;
// sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
//
// }
// sql+="select * from "+eTime.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" ";
//
// }
// }
// //三，取其他表生成生成SQL语句
// else
// {
// //1，先循环到起始时间和起始时间的12月
// s=Convert.ToInt32(sTime);
// x=zstarttime.Substring(0,4)+"12";
// nz=Convert.ToInt32(x);
// for(i=s;i<=nz;i++)
// {
// i=i++;
// sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
// }
// //2，循环两者相差年份
// for(i=sy+1;i<ey;i++)
// {
//
// for(j=1;j<=12;j++)
// {
// if(j<10)
// {
// sql+="select * from "+i.ToString()+"0"+j.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
// }
// else
// {
// sql+="select * from "+i.ToString()+j.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
// }
// }
// }
// //3，循环到结束的月份
// js=Convert.ToInt32(zendtime.Substring(0,4)+"00");
// for(i=js;i<Convert.ToInt32(eTime);i++)
// {
// i++;
// sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";
//
// }
// sql+="select * from "+eTime.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" ";
//
// }
// return sql;
// }
// #endregion
//
// #region 根据查询时间的条件，动态生成查询语句
// /// <summary>
// /// 根据查询时间的条件，动态生成查询语句
// /// </summary>
// /// <param name="starttime">开始时间</param>
// /// <param name="endtime">结束时间</param>
// /// <param name="zhiduan">查询字段</param>
// /// <param name="tiaojiao">查询条件</param>
// /// <param name="tablehz">表后缀</param>
// /// <returns>SQL语句</returns>
// public string SQL(DateTime starttime,DateTime endtime,string zhiduan,string tiaojiao,string tablehz)
// {
//
// string sql=null;
// //将输入的时间格式转换成固定的格式"yyyy-mm-dd"
// string zstarttime=starttime.GetDateTimeFormats('D')[1].ToString();
// string zendtime=endtime.GetDateTimeFormats('D')[1].ToString();
// string nTime=DateTime.Now.GetDateTimeFormats('D')[1].ToString();
//
//
// //取日期值的前六位，及年月值
// string sTime=zstarttime.Substring(0,4)+zstarttime.Substring(5,2);
// string eTime=zendtime.Substring(0,4)+zendtime.Substring(5,2);
// string nowTime=nTime.Substring(0,4)+nTime.Substring(5,2);
// //分别取日期的年和月
// int sy=Convert.ToInt32(zstarttime.Substring(0,4));
// int ey=Convert.ToInt32(zendtime.Substring(0,4));
// int sm=Convert.ToInt32(zstarttime.Substring(5,2));
// int em=Convert.ToInt32(zendtime.Substring(5,2));
// //相关变量定义
// int s;
// int e;
// int i;
// int j;
// int js;
// int nz;
// string x;
// //一，取当前表生成SQL语句
// if(sTime==nowTime&&eTime==nowTime)
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" ";
//
// }
// //二，取当前表和其他表生成SQL语句
// else if(sTime==nowTime&&eTime!=nowTime)
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// //如果年份相等
// if(sy==ey)
// {
// s=Convert.ToInt32(sTime);
// e=Convert.ToInt32(eTime);
// for(i=s+1;i<e;i++)
// {
// i=i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// }
// sql+="select"+" "+zhiduan+" "+"from"+" "+e.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" ";
//
// }
// //结束年份大于开始年份
// else
// {
// //1，先循环到起始时间和起始时间的12月
// s=Convert.ToInt32(sTime);
// x=zstarttime.Substring(0,4)+"12";
// nz=Convert.ToInt32(x);
// for(i=s+1;i<=nz;i++)
// {
// i=i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// }
// //2，循环两者相差年份
// for(i=sy+1;i<ey;i++)
// {
//
// for(j=1;j<=12;j++)
// {
// if(j<10)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+"0"+j.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// }
// else
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+j.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
// }
// }
// }
// //3，循环到结束的月份
// js=Convert.ToInt32(zendtime.Substring(0,4)+"00");
// for(i=js;i<Convert.ToInt32(eTime);i++)
// {
// i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// }
// sql+="select"+" "+zhiduan+" "+"from"+" "+eTime.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" ";
//
// }
// }
// //三，取其他表生成生成SQL语句
// else
// {
// //1，先循环到起始时间和起始时间的12月
// s=Convert.ToInt32(sTime);
// x=zstarttime.Substring(0,4)+"12";
// nz=Convert.ToInt32(x);
// for(i=s;i<=nz;i++)
// {
// i=i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// }
// //2，循环两者相差年份
// for(i=sy+1;i<ey;i++)
// {
//
// for(j=1;j<=12;j++)
// {
// if(j<10)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+"0"+j.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// }
// else
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+j.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
// }
// }
// }
// //3，循环到结束的月份
// js=Convert.ToInt32(zendtime.Substring(0,4)+"00");
// for(i=js;i<Convert.ToInt32(eTime);i++)
// {
// i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// }
// sql+="select"+" "+zhiduan+" "+"from"+" "+eTime.ToString()+'_'+tablehz+" "+"where"+" "+tiaojiao+" ";
//
// }
// return sql;
// }
// #endregion
//
// #region 根据查询时间的条件，动态生成查询语句
// /// <summary>
// /// 根据查询时间的条件，动态生成查询语句
// /// </summary>
// /// <param name="starttime">开始时间</param>
// /// <param name="endtime">结束时间</param>
// /// <param name="zhiduan">查询字段</param>
// /// <param name="tablehz">表后缀</param>
// /// <returns>SQL语句</returns>
// public string SQL(DateTime starttime,DateTime endtime,string zhiduan,string tablehz)
// {
//
// string sql=null;
// //将输入的时间格式转换成固定的格式"yyyy-mm-dd"
// string zstarttime=starttime.GetDateTimeFormats('D')[1].ToString();
// string zendtime=endtime.GetDateTimeFormats('D')[1].ToString();
// string nTime=DateTime.Now.GetDateTimeFormats('D')[1].ToString();
//
//
// //取日期值的前六位，及年月值
// string sTime=zstarttime.Substring(0,4)+zstarttime.Substring(5,2);
// string eTime=zendtime.Substring(0,4)+zendtime.Substring(5,2);
// string nowTime=nTime.Substring(0,4)+nTime.Substring(5,2);
// //分别取日期的年和月
// int sy=Convert.ToInt32(zstarttime.Substring(0,4));
// int ey=Convert.ToInt32(zendtime.Substring(0,4));
// int sm=Convert.ToInt32(zstarttime.Substring(5,2));
// int em=Convert.ToInt32(zendtime.Substring(5,2));
// //相关变量定义
// int s;
// int e;
// int i;
// int j;
// int js;
// int nz;
// string x;
// //一，取当前表生成SQL语句
// if(sTime==nowTime&&eTime==nowTime)
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" ";
//
// }
// //二，取当前表和其他表生成SQL语句
// else if(sTime==nowTime&&eTime!=nowTime)
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" ";
//
// //如果年份相等
// if(sy==ey)
// {
// s=Convert.ToInt32(sTime);
// e=Convert.ToInt32(eTime);
// for(i=s+1;i<e;i++)
// {
// i=i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+" "+"union"+" ";
//
// }
// sql+="select"+" "+zhiduan+" "+"from"+" "+e.ToString()+'_'+tablehz+" ";
//
// }
// //结束年份大于开始年份
// else
// {
// //1，先循环到起始时间和起始时间的12月
// s=Convert.ToInt32(sTime);
// x=zstarttime.Substring(0,4)+"12";
// nz=Convert.ToInt32(x);
// for(i=s+1;i<=nz;i++)
// {
// i=i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+"union"+" ";
//
// }
// //2，循环两者相差年份
// for(i=sy+1;i<ey;i++)
// {
//
// for(j=1;j<=12;j++)
// {
// if(j<10)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+"0"+j.ToString()+'_'+tablehz+" "+"union"+" ";
//
// }
// else
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+j.ToString()+'_'+tablehz+" "+"union"+" ";
// }
// }
// }
// //3，循环到结束的月份
// js=Convert.ToInt32(zendtime.Substring(0,4)+"00");
// for(i=js;i<Convert.ToInt32(eTime);i++)
// {
// i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+"union"+" ";
//
// }
// sql+="select"+" "+zhiduan+" "+"from"+" "+eTime.ToString()+'_'+tablehz+" ";
//
// }
// }
// //三，取其他表生成生成SQL语句
// else
// {
// //1，先循环到起始时间和起始时间的12月
// s=Convert.ToInt32(sTime);
// x=zstarttime.Substring(0,4)+"12";
// nz=Convert.ToInt32(x);
// for(i=s;i<=nz;i++)
// {
// i=i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+"union"+" ";
//
// }
// //2，循环两者相差年份
// for(i=sy+1;i<ey;i++)
// {
//
// for(j=1;j<=12;j++)
// {
// if(j<10)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+"0"+j.ToString()+'_'+tablehz+" "+"union"+" ";
//
// }
// else
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+j.ToString()+'_'+tablehz+" "+"union"+" ";
// }
// }
// }
// //3，循环到结束的月份
// js=Convert.ToInt32(zendtime.Substring(0,4)+"00");
// for(i=js;i<Convert.ToInt32(eTime);i++)
// {
// i++;
// sql+="select"+" "+zhiduan+" "+"from"+" "+i.ToString()+'_'+tablehz+" "+"union"+" ";
//
// }
// sql+="select"+" "+zhiduan+" "+"from"+" "+eTime.ToString()+'_'+tablehz+" ";
//
// }
// return sql;
// }
// #endregion
//
// #region 根据查询时间的条件，动态生成查询语句
// /// <summary>
// /// 根据查询时间的条件，动态生成查询语句
// /// </summary>
// /// <param name="zhiduan">查询字段</param>
// /// <param name="tablehz">表后缀</param>
// /// <returns>SQL语句</returns>
// public string SQL(DateTime time,string zhiduan,string tablehz)
// {
// string sql=null;
// //将输入的时间格式转换成固定的格式"yyyy-mm-dd"
// string stime=time.GetDateTimeFormats('D')[1].ToString();
// string nTime=DateTime.Now.GetDateTimeFormats('D')[1].ToString();
// //取日期值的前六位，及年月值
// string szTime=stime.Substring(0,4)+stime.Substring(5,2);
// string nowTime=nTime.Substring(0,4)+nTime.Substring(5,2);
// //一，取当前表生成SQL语句
// if(szTime==nowTime)
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" ";
//
// }
// else
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+szTime+tablehz+" ";
//
// }
// return sql;
// }
// #endregion
//
// #region 根据查询时间的条件，动态生成修改语句
// /// <summary>
// /// 根据查询时间的条件，动态生成修改语句
// /// </summary>
// /// <param name="zhiduan">修改字段</param>
// /// <param name="tiaojian">条件</param>
// /// <param name="tablehz">表后缀</param>
// /// <returns>SQL语句</returns>
// public string Update(DateTime time,string zhiduan,string tiaojian,string tablehz)
// {
// string sql=null;
// //将输入的时间格式转换成固定的格式"yyyy-mm-dd"
// string stime=time.GetDateTimeFormats('D')[1].ToString();
// string nTime=DateTime.Now.GetDateTimeFormats('D')[1].ToString();
// //取日期值的前六位，及年月值
// string szTime=stime.Substring(0,4)+stime.Substring(5,2);
// string nowTime=nTime.Substring(0,4)+nTime.Substring(5,2);
// //一，取当前表生成SQL语句
// if(szTime==nowTime)
// {
// sql="update"+" "+tablehz+" "+"set"+" "+zhiduan+" "+"where"+" "+tiaojian+" ";
//
// }
// else
// {
// sql="update"+" "+szTime+tablehz+" "+"set"+" "+zhiduan+" "+"where"+" "+tiaojian+" ";
//
//
// }
// return sql;
// }
// #endregion
//
// #region 根据查询时间的条件，动态生成表名
// /// <summary>
// /// 根据查询时间的条件，动态生成表名
// /// </summary>
// /// <param name="tablehz">表后缀</param>
// /// <returns>tablename</returns>
// public string table(DateTime time,string tablehz)
// {
// string table=null;
// //将输入的时间格式转换成固定的格式"yyyy-mm-dd"
// string stime=time.GetDateTimeFormats('D')[1].ToString();
// string nTime=DateTime.Now.GetDateTimeFormats('D')[1].ToString();
// //取日期值的前六位，及年月值
// string szTime=stime.Substring(0,4)+stime.Substring(5,2);
// string nowTime=nTime.Substring(0,4)+nTime.Substring(5,2);
// //一，取当前表生成SQL语句
// if(szTime==nowTime)
// {
// table=tablehz;
//
// }
// else
// {
// table=szTime+tablehz;
//
//
// }
// return table;
// }
// #endregion
#endregion
#region 根据数据库时间，动态生成各种查询语句
#region 根据查询时间的条件，动态生成查询语句
/// <summary>
/// 根据查询时间的条件，动态生成查询语句
/// </summary>
/// <param name="starttime">开始时间</param>
/// <param name="endtime">结束时间</param>
/// <param name="zhiduan">查询字段</param>
/// <param name="tiaojiao">查询条件</param>
/// <param name="tablehz">表后缀</param>
/// <returns>SQL语句</returns>
public string SQL(DateTime starttime,DateTime endtime,string zhiduan,string tiaojiao,string tablehz)
{
DataSet ds=new DataSet();
string sql=null;
string zstarttime=starttime.GetDateTimeFormats('D')[1].ToString();
string zendtime=endtime.GetDateTimeFormats('D')[1].ToString();
//取日期值的前六位，及年月值
string sTime=zstarttime.Substring(0,4)+zstarttime.Substring(5,2);
string eTime=zendtime.Substring(0,4)+zendtime.Substring(5,2);
string nowTime=SiFenManager.SFObject.SF_DIC_DATE.GetCurrentMonth();
//一，取当前表生成SQL语句
if(sTime==nowTime&&eTime==nowTime)
{
sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" ";
}
//二，取当前表和其他表生成SQL语句
// else if(sTime==nowTime&&eTime!=nowTime)
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// ds=SiFenManager.Util.Database.GetSJDSet(sTime,eTime);
//
// for(int i=1;i<ds.Tables[0].Rows.Count-1;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
// }
// for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" ";
// }
// }
else if(sTime!=nowTime&&eTime==nowTime)
{
ds=GetSJDSet(sTime,eTime);
for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
}
for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
{
sql+="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" ";
}
}
else
{
ds=GetTimeSet(sTime,eTime);
for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
{
if((ds.Tables[0].Rows[i]["flag"]).ToString()=="1")
{
sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
}
else
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
}
}
for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
{
if((ds.Tables[0].Rows[i]["flag"]).ToString()=="1")
{
sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" ";
}
else
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" ";
}
}
// ds=SiFenManager.Util.Database.GetSJDSet(sTime,eTime);
//
// for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
// {
//
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
// }
// for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" ";
// }
}
return sql;
}
#endregion
#region 根据查询时间的条件，动态生成查询语句
/// <summary>
/// 根据查询时间的条件，动态生成查询语句
/// </summary>
/// <param name="starttime">开始时间</param>
/// <param name="endtime">结束时间</param>
/// <param name="zhiduan">查询字段</param>
/// <param name="tiaojiao">查询条件</param>
/// <param name="tablehz">表后缀</param>
/// <param name="sort">排序</param>
/// <returns>SQL语句</returns>
public string SQL(DateTime starttime,DateTime endtime,string zhiduan,string tiaojiao,string tablehz,string sort)
{
DataSet ds=new DataSet();
string sql=null;
string zstarttime=starttime.GetDateTimeFormats('D')[1].ToString();
string zendtime=endtime.GetDateTimeFormats('D')[1].ToString();
//取日期值的前六位，及年月值
string sTime=zstarttime.Substring(0,4)+zstarttime.Substring(5,2);
string eTime=zendtime.Substring(0,4)+zendtime.Substring(5,2);
string nowTime=SiFenManager.SFObject.SF_DIC_DATE.GetCurrentMonth();
//一，取当前表生成SQL语句
if(sTime==nowTime&&eTime==nowTime)
{
sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" "+sort;
}
//二，取当前表和其他表生成SQL语句
// else if(sTime==nowTime&&eTime!=nowTime)
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
//
// ds=SiFenManager.Util.Database.GetSJDSet(sTime,eTime);
//
// for(int i=1;i<ds.Tables[0].Rows.Count-1;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
// }
// for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" ";
// }
// }
else if(sTime!=nowTime&&eTime==nowTime)
{
ds=GetSJDSet(sTime,eTime);
for(int i=0;i<1;i++)
{
sql+="select * from (select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
}
for(int i=1;i<ds.Tables[0].Rows.Count-1;i++)
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
}
for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
{
sql+="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" "+")"+sort;
}
}
else
{
ds=GetTimeSet(sTime,eTime);
for(int i=0;i<1;i++)
{
if((ds.Tables[0].Rows[i]["flag"]).ToString()=="1")
{
sql="select * from (select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
}
else
{
sql+="select * from (select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
}
}
for(int i=1;i<ds.Tables[0].Rows.Count-1;i++)
{
if((ds.Tables[0].Rows[i]["flag"]).ToString()=="1")
{
sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
}
else
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
}
}
for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
{
if((ds.Tables[0].Rows[i]["flag"]).ToString()=="1")
{
sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"where"+" "+tiaojiao+" "+")"+sort;
}
else
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+")"+sort;
}
}
// ds=SiFenManager.Util.Database.GetSJDSet(sTime,eTime);
//
// for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
// {
//
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" "+"union"+" ";
// }
// for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"where"+" "+tiaojiao+" ";
// }
}
return sql;
}
#endregion
#region 根据查询时间的条件，动态生成查询语句
/// <summary>
/// 根据查询时间的条件，动态生成查询语句
/// </summary>
/// <param name="starttime">开始时间</param>
/// <param name="endtime">结束时间</param>
/// <param name="zhiduan">查询字段</param>
/// <param name="tablehz">表后缀</param>
/// <returns>SQL语句</returns>
public string SQL(DateTime starttime,DateTime endtime,string zhiduan,string tablehz)
{
DataSet ds=new DataSet();
string sql=null;
//将输入的时间格式转换成固定的格式"yyyy-mm-dd"
string zstarttime=starttime.GetDateTimeFormats('D')[1].ToString();
string zendtime=endtime.GetDateTimeFormats('D')[1].ToString();
//取日期值的前六位，及年月值
string sTime=zstarttime.Substring(0,4)+zstarttime.Substring(5,2);
string eTime=zendtime.Substring(0,4)+zendtime.Substring(5,2);
string nowTime=SiFenManager.SFObject.SF_DIC_DATE.GetCurrentMonth();
//一，取当前表生成SQL语句
if(sTime==nowTime&&eTime==nowTime)
{
sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" ";
}
//二，取当前表和其他表生成SQL语句
// else if(sTime==nowTime&&eTime!=nowTime)
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"union"+" ";
//
// ds=SiFenManager.Util.Database.GetSJDSet(sTime,eTime);
//
// for(int i=1;i<ds.Tables[0].Rows.Count-1;i++)
// {
// //text[i]+=(ds.Tables[0].Rows[i]["MONTH"]).ToString();
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"union"+" ";
// }
// for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz;
// }
// }
else if(sTime!=nowTime&&eTime==nowTime)
{
ds=GetSJDSet(sTime,eTime);
for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"union"+" ";
}
for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
{
sql+="select"+" "+zhiduan+" "+"from"+" "+tablehz+" ";
}
}
else
{
ds=GetTimeSet(sTime,eTime);
for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
{
if((ds.Tables[0].Rows[i]["flag"]).ToString()=="1")
{
sql+="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"union"+" ";
}
else
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"union"+" ";
}
}
for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
{
if((ds.Tables[0].Rows[i]["flag"]).ToString()=="1")
{
sql+="select"+" "+zhiduan+" "+"from"+" "+tablehz+" ";
}
else
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz;
}
}
// ds=SiFenManager.Util.Database.GetSJDSet(sTime,eTime);
//
// for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
// {
// //text[i]+=(ds.Tables[0].Rows[i]["MONTH"]).ToString();
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"union"+" ";
// }
// for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz;
// }
}
return sql;
}
#endregion
#region 根据查询时间的条件，动态生成查询语句
/// <summary>
/// 根据查询时间的条件，动态生成查询语句
/// </summary>
/// <param name="starttime">开始时间</param>
/// <param name="endtime">结束时间</param>
/// <param name="zhiduan">查询字段</param>
/// <param name="tablehz">表后缀</param>
/// <returns>SQL语句</returns>
public string OtherSQL(DateTime starttime,DateTime endtime,string zhiduan,string tablehz,string other)
{
DataSet ds=new DataSet();
string sql=null;
//将输入的时间格式转换成固定的格式"yyyy-mm-dd"
string zstarttime=starttime.GetDateTimeFormats('D')[1].ToString();
string zendtime=endtime.GetDateTimeFormats('D')[1].ToString();
//取日期值的前六位，及年月值
string sTime=zstarttime.Substring(0,4)+zstarttime.Substring(5,2);
string eTime=zendtime.Substring(0,4)+zendtime.Substring(5,2);
string nowTime=SiFenManager.SFObject.SF_DIC_DATE.GetCurrentMonth();
//一，取当前表生成SQL语句
if(sTime==nowTime&&eTime==nowTime)
{
sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+other+" ";
}
//二，取当前表和其他表生成SQL语句
// else if(sTime==nowTime&&eTime!=nowTime)
// {
// sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"union"+" ";
//
// ds=SiFenManager.Util.Database.GetSJDSet(sTime,eTime);
//
// for(int i=1;i<ds.Tables[0].Rows.Count-1;i++)
// {
// //text[i]+=(ds.Tables[0].Rows[i]["MONTH"]).ToString();
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"union"+" ";
// }
// for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+other+" ";
// }
// }
else if(sTime!=nowTime&&eTime==nowTime)
{
ds=GetSJDSet(sTime,eTime);
for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"union"+" ";
}
for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
{
sql+="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+other+" ";
}
}
else
{
ds=GetTimeSet(sTime,eTime);
for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
{
if((ds.Tables[0].Rows[i]["flag"]).ToString()=="1")
{
sql+="select"+" "+zhiduan+" "+"from"+" "+tablehz+" "+"union"+" ";
}
else
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"union"+" ";
}
}
for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
{
if((ds.Tables[0].Rows[i]["flag"]).ToString()=="1")
{
sql+="select"+" "+zhiduan+" "+"from"+" "+tablehz+" ";
}
else
{
sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+other+" ";
}
}
// ds=SiFenManager.Util.Database.GetSJDSet(sTime,eTime);
//
// for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
// {
// //text[i]+=(ds.Tables[0].Rows[i]["MONTH"]).ToString();
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+"union"+" ";
// }
// for(int i=ds.Tables[0].Rows.Count-1;i<ds.Tables[0].Rows.Count;i++)
// {
// sql+="select"+" "+zhiduan+" "+"from"+" "+"M"+(ds.Tables[0].Rows[i]["MONTH"]).ToString()+"_"+tablehz+" "+other+" ";
// }
}
return sql;
}
#endregion
#region 根据查询时间的条件，动态生成查询语句
/// <summary>
/// 根据查询时间的条件，动态生成查询语句
/// </summary>
/// <param name="zhiduan">查询字段</param>
/// <param name="tablehz">表后缀</param>
/// <returns>SQL语句</returns>
public string SQL(DateTime time,string zhiduan,string tablehz)
{
string sql=null;
//将输入的时间格式转换成固定的格式"yyyy-mm-dd"
string stime=time.GetDateTimeFormats('D')[1].ToString();
//取日期值的前六位，及年月值
string szTime=stime.Substring(0,4)+stime.Substring(5,2);
string nowTime=SiFenManager.SFObject.SF_DIC_DATE.GetCurrentMonth();
//一，取当前表生成SQL语句
if(SiFenManager.SFObject.SF_DIC_DATE.Exists(szTime))
{
if(szTime==nowTime)
{
sql="select"+" "+zhiduan+" "+"from"+" "+tablehz+" ";
}
else
{
sql="select"+" "+zhiduan+" "+"from"+" "+"M"+szTime+"_"+tablehz+" ";
}
}
else
{
return null;
}
return sql;
}
#endregion
#region 根据查询时间的条件，动态生成修改语句
/// <summary>
/// 根据查询时间的条件，动态生成修改语句
/// </summary>
/// <param name="zhiduan">修改字段</param>
/// <param name="tiaojian">条件</param>
/// <param name="tablehz">表后缀</param>
/// <returns>SQL语句</returns>
public string Update(DateTime time,string zhiduan,string tiaojian,string tablehz)
{
string sql=null;
//将输入的时间格式转换成固定的格式"yyyy-mm-dd"
string stime=time.GetDateTimeFormats('D')[1].ToString();
//取日期值的前六位，及年月值
string szTime=stime.Substring(0,4)+stime.Substring(5,2);
string nowTime=SiFenManager.SFObject.SF_DIC_DATE.GetCurrentMonth();
//一，取当前表生成SQL语句
if(szTime==nowTime)
{
sql="update"+" "+tablehz+" "+"set"+" "+zhiduan+" "+"where"+" "+tiaojian+" ";
}
else
{
sql="update"+" "+"M"+szTime+"_"+tablehz+" "+"set"+" "+zhiduan+" "+"where"+" "+tiaojian+" ";
}
return sql;
}
#endregion
#region 根据查询时间的条件，动态生成表名
/// <summary>
/// 根据查询时间的条件，动态生成表名
/// </summary>
/// <param name="tablehz">表后缀</param>
/// <returns>tablename</returns>
public string table(DateTime time,string tablehz)
{
string table=null;
//将输入的时间格式转换成固定的格式"yyyy-mm-dd"
string stime=time.GetDateTimeFormats('D')[1].ToString();
//取日期值的前六位，及年月值
string szTime=stime.Substring(0,4)+stime.Substring(5,2);
string nowTime=SiFenManager.SFObject.SF_DIC_DATE.GetCurrentMonth();
//一，取当前表生成SQL语句
if(SiFenManager.SFObject.SF_DIC_DATE.Exists(szTime))
{
if(szTime==nowTime)
{
table=tablehz;
}
else
{
table="M"+szTime+"_"+tablehz;
}
}
else
{
return null;
}
return table;
}
#endregion
#region 根据查询的条件，动态生成查询语句
/// <summary>
/// 根据查询的条件，动态生成查询语句
/// </summary>
/// <param name="zhiduan">查询字段</param>
/// <param name="tiaojiao">查询条件</param>
/// <param name="tablehz">表后缀</param>
/// <returns>SQL语句</returns>
public string SQL(string zhiduan,string tiaojiao,string tablename)
{
string sql="select"+" "+zhiduan+" "+"from"+" "+tablename+" "+"where"+" "+tiaojiao+" ";
return sql;
}
#endregion
#endregion
#region 存储过程操作
/// <summary>
/// 执行存储过程，返回OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleDataReader</returns>
public OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
OracleDataReader returnReader;
Open();//打开数据连接
OracleCommand command = BuildQueryCommand(con,storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
/// <summary>
/// 执行存储过程，无返回结果
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleDataReader</returns>
public void ExceuteProcedure(string storedProcName, IDataParameter[] parameters)
{
Open();//打开数据连接
OracleCommand command = BuildQueryCommand(con,storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
Close();
}
/// <summary>
/// 执行存储过程,返回DataSet
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
DataSet dataSet = new DataSet();
Open();//打开数据连接
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = BuildQueryCommand(con,storedProcName, parameters);
adapter.Fill(dataSet, tableName);
Close();//关闭数据库连接
return dataSet;
}
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
int result;
Open();
OracleCommand command = BuildIntCommand(con, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
Close();
return result;
}
#endregion
#region 私有成员
private void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (OracleParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
/// <summary>
/// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleCommand</returns>
private OracleCommand BuildQueryCommand(OracleConnection connection,string storedProcName, IDataParameter[] parameters)
{
Open();//打开数据连接
OracleCommand command = new OracleCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter 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;
}
/// <summary>
/// 创建 OracleCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleCommand 对象实例</returns>
private OracleCommand BuildIntCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
{
OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new OracleParameter("ReturnValue",
OracleType.Int16, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
#endregion
#region 绑定下拉框
/// <summary>
/// 绑定下拉框
/// </summary>
/// <param name="Ddl">DDL列表框</param>
/// <param name="Sql">绑定表格的Sql语句</param>
/// <param name="TextField">显示值</param>
/// <param name="ValueField">主键</param>
public void Bind(DropDownList Ddl,string Sql,string TextField,string ValueField)
{
Ddl.DataSource = GetDataSet(Sql);
Ddl.DataTextField = TextField;
Ddl.DataValueField = ValueField;
Ddl.DataBind();
}
#endregion
#region 绑定DataGrid
/// <summary>
/// 绑定DataGrid
/// </summary>
/// <param name="Ddl">DataGrid</param>
/// <param name="Sql">绑定表格的Sql语句</param>
public void Bind(DataGrid dg,string Sql)
{
dg.DataSource = GetDataSet(Sql);
dg.DataBind();
}
#endregion
#region 修改数据集
public static System.Data.DataSet PrepareDataSet(System.Data.DataSet Source)
{
int Need=12-Source.Tables[0].Rows.Count;
for(int i=0;i<Need&&Need>=0;i++)
{
System.Data.DataRow temp=Source.Tables[0].NewRow();
temp[0]="0";
temp[1]=System.DateTime.Now;
Source.Tables[0].Rows.Add(temp);
}
return Source;
}
#endregion
#region 动态分配二维数组维数
//调整长度
public static Array Redim(Array origArray,params int[] lengths)
{
//确定每个元素的类型
Type t=origArray.GetType().GetElementType();
//创建新的数组
Array newArray=Array.CreateInstance(t,lengths);
//原数组中的数据拷贝到新数组中
for ( int i = origArray.GetLowerBound(0); i <= Math.Min(origArray.GetUpperBound(0),newArray.GetUpperBound(0)); i++ )
for ( int j = origArray.GetLowerBound(1); j <= Math.Min(origArray.GetUpperBound(1),newArray.GetUpperBound(1)); j++ )
newArray.SetValue( origArray.GetValue( i, j ) , i, j );
//在这里没有用Copy方法，如果用此方法，会把原数组中所有数据逐个拷贝到新数组中
return newArray;
}
#endregion
#region 获得指定时间段内数据记录
public DataSet GetSJDSet(string stime ,string etime)
{
DataSet ds = new DataSet();
string sql="Select MONTH From SF_DIC_DATE WHERE TO_NUMBER(MONTH)>="+stime
+" AND TO_NUMBER(MONTH)<="+etime + "order by month asc";
ds=GetDataSet(sql);
return ds;
}
#endregion
#region 获得指定时间段内数据记录
public DataSet GetTimeSet(string stime ,string etime)
{
DataSet ds = new DataSet();
string sql="Select MONTH,flag From SF_DIC_DATE WHERE TO_NUMBER(MONTH)>="+stime
+" AND TO_NUMBER(MONTH)<="+etime + "order by month asc";
ds=GetDataSet(sql);
return ds;
}
#endregion
}
}