using system;
using system.collections;
using system.collections.specialized;
using system.runtime.remoting.messaging;
using system.data;
using system.data.sqlclient;
using system.configuration;
namespace ltp.sqlserverdal
{
 /// <summary>
 /// ado.net数据库操作基础类。
 /// </summary>
 public abstract class dbmanagersql
 {
  //数据库连接字符串
  protected static string connectionstring = configurationsettings.appsettings["connectionstring"]; 
  public dbmanagersql()
  {
   //
   // todo: 在此处添加构造函数逻辑
   //
  }
  /// <summary>
  /// 执行sql语句，返回影响的记录数
  /// </summary>
  /// <param name="sqlstring"></param>
  /// <returns></returns>
  public static int executesql(string sqlstring)
  {
   using (sqlconnection connection = new sqlconnection(connectionstring))
   {    
    using (sqlcommand cmd = new sqlcommand(sqlstring,connection))
    {
     try
     {  
      connection.open();
      int rows=cmd.executenonquery();
      return rows;
     }
     catch(system.data.sqlclient.sqlexception e)
     {    
      throw new exception(e.message);
     }
    }    
   }
  }
  /// <summary>
  /// 执行两条sql语句，实现数据库事务。
  /// </summary>
  /// <param name="sqlstring1"></param>
  /// <param name="sqlstring2"></param>
  public static void executesqltran(string sqlstring1,string sqlstring2)
  {
   using (sqlconnection connection = new sqlconnection(connectionstring))
   {
    connection.open();
    sqlcommand cmd = new sqlcommand();
    cmd.connection=connection;    
    sqltransaction tx=connection.begintransaction();   
    cmd.transaction=tx;    
    try
    {     
     cmd.commandtext=sqlstring1;
     cmd.executenonquery();
     cmd.commandtext=sqlstring2;
     cmd.executenonquery();     
     tx.commit();     
    }
    catch(system.data.sqlclient.sqlexception e)
    {  
     tx.rollback();
     throw new exception(e.message);
    }
    finally
    {
     cmd.dispose();
     connection.close();
    } 
   }
  } 
  /// <summary>
  /// 执行多条sql语句，实现数据库事务，每条语句以“;”分割。
  /// </summary>
  /// <param name="sqlstringlist"></param>
  public static void executesqltran(string sqlstringlist)
  {
   using (odbcconnection conn = new odbcconnection(connectionstring))
   {
    conn.open();
    odbccommand cmd = new odbccommand();
    cmd.connection=conn;    
    odbctransaction tx=conn.begintransaction();   
    cmd.transaction=tx;    
    try
    {  
     string [] split= sqlstringlist.split(new char [] { ;});
     foreach (string strsql in split)
     {
      if (strsql.trim()!="")
      {
       cmd.commandtext=strsql;
       cmd.executenonquery();
      }
     }       
     tx.commit();     
    }
    catch(system.data.odbc.odbcexception e)
    {  
     tx.rollback();
     throw new exception(e.message);
    }
   }
  }
  /// <summary>
  /// 执行带一个存储过程参数的的sql语句。
  /// </summary>
  /// <param name="sqlstring"></param>
  /// <param name="content"></param>
  /// <returns></returns>
  public static int executesql(string sqlstring,string content)
  {    
   using (sqlconnection connection = new sqlconnection(connectionstring))
   {
    sqlcommand cmd = new sqlcommand(sqlstring,connection);  
    system.data.sqlclient.sqlparameter  myparameter = new system.data.sqlclient.sqlparameter ( "@content", sqldbtype.ntext);
    myparameter.value = content ;
    cmd.parameters.add(myparameter);
    try
    {
     connection.open();
     int rows=cmd.executenonquery();
     return rows;
    }
    catch(system.data.sqlclient.sqlexception e)
    {    
     throw new exception(e.message);
    }
    finally
    {
     cmd.dispose();
     connection.close();
    } 
   }
  }  
  /// <summary>
  /// 向数据库里插入图像格式的字段
  /// </summary>
  /// <param name="strsql"></param>
  /// <param name="fs"></param>
  /// <returns></returns>
  public static int executesqlinsertimg(string strsql,byte[] fs)
  {  
   using (sqlconnection connection = new sqlconnection(connectionstring))
   {
    sqlcommand cmd = new sqlcommand(strsql,connection); 
    system.data.sqlclient.sqlparameter  myparameter = new system.data.sqlclient.sqlparameter ( "@fs", sqldbtype.image);
    myparameter.value = fs ;
    cmd.parameters.add(myparameter);
    try
    {
     connection.open();
     int rows=cmd.executenonquery();
     return rows;
    }
    catch(system.data.sqlclient.sqlexception e)
    {    
     throw new exception(e.message);
    }
    finally
    {
     cmd.dispose();
     connection.close();
    } 
    
   }
  }
  /// <summary>
  /// 执行一条计算查询结果语句，返回查询结果（整数）。
  /// </summary>
  /// <param name="strsql"></param>
  /// <returns></returns>
  public static int getcount(string strsql)
  {
   using (sqlconnection connection = new sqlconnection(connectionstring))
   {
    sqlcommand cmd = new sqlcommand(strsql,connection);    
    try
    {
     connection.open();
     sqldatareader result = cmd.executereader();
     int i=0;
     while(result.read())
     {
      i=result.getint32(0);
     }
     result.close();    
     return i;
    }
    catch(system.data.sqlclient.sqlexception e)
    {        
     throw new exception(e.message);
    } 
    finally
    {
     cmd.dispose();
     connection.close();
    }
   }
  } 
  /// <summary>
  /// 执行一条计算查询结果语句，返回查询结果（object）。
  /// </summary>
  /// <param name="sqlstring"></param>
  /// <returns></returns>
  public static object getsingle(string sqlstring)
  {
   using (sqlconnection connection = new sqlconnection(connectionstring))
   {
    sqlcommand cmd = new sqlcommand(sqlstring,connection);  
    try
    {
     connection.open();
     object obj = cmd.executescalar();
     if((object.equals(obj,null))||(object.equals(obj,system.dbnull.value)))
     {     
      return null;
     }
     else
     {
      return obj;
     }    
    }
    catch(system.data.sqlclient.sqlexception e)
    {    
     throw new exception(e.message);
    }
    finally
    {
     cmd.dispose();
     connection.close();
    }
   }
  }
  /// <summary>
  /// 执行查询语句，返回sqldatareader
  /// </summary>
  /// <param name="strsql"></param>
  /// <returns></returns>
  public static sqldatareader executereader(string strsql)
  {
   using (sqlconnection connection = new sqlconnection(connectionstring))
   {
    sqlcommand cmd = new sqlcommand(strsql,connection); 
    sqldatareader myreader;   
    try
    {
     connection.open(); 
     myreader = cmd.executereader();
     return myreader;
    }
    catch(system.data.sqlclient.sqlexception e)
    {        
     throw new exception(e.message);
    }  
    finally
    {
     cmd.dispose();
     connection.close();
    } 
   }
  }  
  /// <summary>
  /// 执行查询语句，返回dataset
  /// </summary>
  /// <param name="sqlstring"></param>
  /// <returns></returns>
  public static dataset query(string sqlstring)
  {
   using (sqlconnection connection = new sqlconnection(connectionstring))
   {
    dataset ds = new dataset();
    try
    {
     connection.open();
     sqldataadapter command = new sqldataadapter(sqlstring,connection);    
     command.fill(ds,"ds");
    }
    catch(system.data.sqlclient.sqlexception ex)
    {    
     throw new exception(ex.message);
    }   
    return ds;
   }
   
  }

  #region 存储过程操作

  /// <summary>
  /// 运行存储过程
  /// </summary>
  /// <param name="storedprocname"></param>
  /// <param name="parameters"></param>
  /// <returns></returns>
  public static sqldatareader runprocedure(string storedprocname, idataparameter[] parameters )
  {
   using (sqlconnection connection = new sqlconnection(connectionstring))
   {
    sqldatareader returnreader;
    connection.open();
    sqlcommand command = buildquerycommand( connection,storedprocname, parameters );
    command.commandtype = commandtype.storedprocedure;

    returnreader = command.executereader();
    //connection.close();
    return returnreader;
   }
  }
  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)
    {
     command.parameters.add( parameter );
    }
    return command;
   
  }  
  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;
   }
  }

  #endregion 
 }
}