excesql.cs

股票操作员每日一记 源程序 股票操作员每日一记 源程序

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient; 
using System.Security.Cryptography;
using System.Text; 
using System.Data.OleDb;
using System.IO;
namespace stock_Log
{
	/// <summary>
	/// ExceSql 的摘要说明。
	/// </summary>
	public class ExceSql
	{

        public static string strConn = ConnectionInfo.GetConnString("config.xml", "stock");//string.Format(System.Configuration.ConfigurationSettings.AppSettings["Oleconnstring"]);
		//public static string str_conn=string.Format(System.Configuration.ConfigurationSettings.AppSettings["SQLConnString"]);
		public ExceSql()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}


		public static DataSet GetBySqlToDS(string sql )
		{ 
			
			DataSet ds = new DataSet();				
			try
			{
				OleDbConnection Conn = new OleDbConnection(strConn);
				Conn.Open();
				Int32 status = 0; 
				//StringBuilder commandText = new StringBuilder("SELECT * FROM BASE_ACC_TYPE_ParameterInfo a10 WHERE (a10.DPTCODE_P = '"+strPrmDPTCODE_P+"')");
				OleDbCommand DSCmd = new OleDbCommand(sql,Conn);
				if(status ==  0 ) 
				{		

					OleDbDataAdapter DBAdopter = new OleDbDataAdapter(DSCmd);
					DBAdopter.Fill(ds, "Main");
					 
					
					if(status ==  0 )
					{
						
					}
					else
					{
						ds = new DataSet();
												
					}
				}
				else
					return ds;
				Conn.Close();
			}
			catch (Exception e)
			{
				
				throw new Exception(e.Message);
			}
			return ds;
		}


		public static  DataSet LoadList(string type)
		{ 
			DataSet ds = new DataSet();				
			try
			{
				OleDbConnection Conn = new OleDbConnection(strConn);
				Conn.Open();
				Int32 status = 0; 
				String sql="SELECT * FROM BASE_ACC_TYPE where dir='"+type+"'";
				 
				//StringBuilder commandText = new StringBuilder("SELECT * FROM BASE_ACC_TYPE_ParameterInfo a10 WHERE (a10.DPTCODE_P = '"+strPrmDPTCODE_P+"')");
				OleDbCommand DSCmd = new OleDbCommand(sql,Conn);
				if(status ==  0 ) 
				{		

					OleDbDataAdapter DBAdopter = new OleDbDataAdapter(DSCmd);
					DBAdopter.Fill(ds);
					 
					
					if(status ==  0 )
					{
						
					}
					else
					{
						ds = new DataSet();
												
					}
				}
				else
					return ds;
				Conn.Close();
			}
			catch (Exception e)
			{
				
				throw new Exception(e.Message);
			}
			return ds;
		}

        public static bool execTX(ArrayList thelists)
        {

            OleDbConnection myCn = new OleDbConnection(strConn);
            OleDbCommand myCmd = new OleDbCommand();
            int j = thelists.Count;
            bool flag = false;
            try
            {
                myCn.Open();
            }
            catch (Exception es)
            {
                throw new Exception(es.Message);
            }
            OleDbTransaction myTrans = myCn.BeginTransaction();

            try
            {
                myCmd.Connection = myCn;
                myCmd.Transaction = myTrans;

                foreach (string str in thelists)
                {
                    myCmd.CommandText = str;
                    myCmd.ExecuteNonQuery();
                }
                myTrans.Commit();
                flag = true;
            }
            catch (Exception e)
            {
                myTrans.Rollback();
                flag = false;
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
                myCn.Close();
            }
            return flag;
        }
		public static Int32 Delete(String commandText )
		{
			
			Int32 result = 0;
		 
			try
			{
				OleDbConnection Conn = new OleDbConnection(strConn);
				OleDbCommand DSCmd = new OleDbCommand(commandText,Conn);
				Conn.Open();
				
				//DSCmd.Properties("NDatatype") = true;	
				Int32 status = 0;
				
				if(status ==  0 )
				{
					//DSCmd.Parameters.Add("DPTCODE", OleDbType.VarChar).Value = objInfo.DPTCODE;
					result = DSCmd.ExecuteNonQuery();					
					
					if(status ==  0 )
					{
						
					}	
					else
					{
						result = 0;
												
					}	
				}
				else
					result = 0;
				Conn.Close();
			}
			catch (Exception e)
			{
										
				throw new Exception(e.Message);  
			}			
			return result;
		}

		public static Int32 insertUpdate(String commandText )
		{
			
			Int32 result = 0;
		 
			try
			{
                 //  string strPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
		       // strConn= ConnectionInfo.GetConnString("config.xml");//string.Format(System.Configuration.ConfigurationSettings.AppSettings["Oleconnstring"]);

				OleDbConnection Conn = new OleDbConnection(strConn);
				OleDbCommand DSCmd = new OleDbCommand(commandText,Conn);
				Conn.Open();
				
				//DSCmd.Properties("NDatatype") = true;	
				Int32 status = 0;
				
				if(status ==  0 )
				{
					//DSCmd.Parameters.Add("DPTCODE", OleDbType.VarChar).Value = objInfo.DPTCODE;
					result = DSCmd.ExecuteNonQuery();					
					
					if(status ==  0 )
					{
						
					}	
					else
					{
						result = 0;
												
					}	
				}
				else
					result = 0;
				Conn.Close();
			}
			catch (Exception e)
			{
										
				throw new Exception(e.Message);  
			}			
			return result;
		}
 
		public static OleDbDataReader dataReader(String sql)
		{
			
		 	OleDbConnection Conn = new OleDbConnection(strConn);
	    try
			{
			
				OleDbCommand DSCmd = new OleDbCommand(sql,Conn);
				Conn.Open();
			 
			   return DSCmd.ExecuteReader(CommandBehavior.CloseConnection);
			 
				 
			}			
			catch (Exception e)
			{
				Conn.Close();
				throw new Exception(e.Message);  
			}			
			 
		}

		//用户名是否存在
		public static bool checkCustomer(String name)
		{
		 
	 
			name=name.Replace("'","''");
			string  sqls="SELECT customer_ID FROM base_customers WHERE company='"+name+"'";
			OleDbDataReader dr=dataReader(sqls);	
			if(dr.Read())
			{
				dr.Close();
				
				return false;
			}
			else
			{
				dr.Close();
				return true;
				//Response.Write("<script>alert('当前系统中没有该客户的资料，可以继续添加！'); </script>");			
			}
			
		
		} 





//		
//		private void DataBind(int CurPage)　　//数据绑定方法
//		{
//			cmd=new OleDbCommand("select * from news order by id ",conn);
//			OleDbDataAdapter adapter=new OleDbDataAdapter(cmd);
//			DataSet ds=new DataSet();
//			adapter.Fill(ds,Pager1.PageSize*CurPage,Pager1.PageSize,"news");
//
//			DataGrid1.DataSource=ds.Tables["news"];
//			DataGrid1.DataBind();
//
//			cmd=new OleDbCommand("select count(id) from news",conn);
//			conn.Open();
//			Pager1.RecTotal = (int)cmd.ExecuteScalar();
//			Pager1.DataBind();
//			conn.Close();
//		}


		//分页
//		public DataTable getPage_DataBind(int CurPage,int pagesize,string sqldata,String tablename)　　//数据绑定方法
//		{
//		  
//			OleDbConnection Conn = new OleDbConnection(strConn);
//
//			OleDbCommand cmd=new OleDbCommand(sqldata,Conn);
//
//			OleDbDataAdapter adapter=new OleDbDataAdapter(cmd);
//
//			DataSet ds=new DataSet();
//			Conn.Open();
//			adapter.Fill(ds,CurPage,pagesize,tablename);
//
//			return ds.Tables[tablename];
//			Conn.Close();
//		}


//		public  int getcount(string sqlcount)
//		{
//		　	OleDbConnection conn = new OleDbConnection(strConn);			
//			OleDbCommand cmd = new OleDbCommand(sqlcount,conn);
//			try
//			{
//				conn.Open();
//				return (int)cmd.ExecuteScalar();
//			}
//			catch
//			{				
//				//throw new Exception("数据库操作失败！");
//				throw new Exception(sql);
//			}
//			finally
//			{
//				cmd.Dispose();
//				conn.Close();
//			}
//		}

		//分页
		public static DataSet getPage_DataBind(int CurPage,int pagesize,string sqldata,String tablename)　　//数据绑定方法
		{
			

			DataSet ds = new DataSet();				
			try
			{
				OleDbConnection Conn = new OleDbConnection(strConn);
				Conn.Open();
				Int32 status = 0; 
				  
				OleDbCommand DSCmd = new OleDbCommand(sqldata,Conn);
				if(status ==  0 ) 
				{		
					
					OleDbDataAdapter DBAdopter = new OleDbDataAdapter(DSCmd);
					DBAdopter.Fill(ds,CurPage,pagesize,tablename);
					
					if(status ==  0 )
					{
						
					}
					else
					{
						ds = new DataSet();
												
					}
				}
				else
					return ds;
				Conn.Close();
			}
			catch (Exception e)
			{
				
				throw new Exception(e.Message);
			}
			return ds;
		}



		//得到记录数
		public static Int32 getCount(string sqlcount)
		{
			Int32 nRecCount = 0;			
			try
			{
				OleDbConnection Conn = new OleDbConnection(strConn);
				Conn.Open();
				Int32 status = 0; 				 
				OleDbCommand DSCmd = new OleDbCommand(sqlcount,Conn);
				if(status ==  0 ) 
				{ 
					nRecCount = Convert.ToInt32(DSCmd.ExecuteScalar());
				}
				else
					nRecCount = 0;
				Conn.Close();
			}
			catch (Exception e)
			{
			 throw new Exception(e.Message);
			}
			return nRecCount;
		}

 

		//插入数据
		public static Int32 Insert(string insertSql)
		{
			
			Int32 result = 0;
 			try
			{
				OleDbConnection Conn = new OleDbConnection(strConn);
				OleDbCommand DSCmd = new OleDbCommand(insertSql,Conn);
				Conn.Open();
 				Int32 status = 0;
				
				if(status ==  0 )
				{ 	
					result = DSCmd.ExecuteNonQuery();				
					DSCmd.Dispose();
 					if(status ==  0 )					
					{
						
					}	
					else
					{
						result = 0;
												
					}	
				}
				Conn.Close();					
			}
			catch (Exception e)
			{
										
				throw new Exception(e.Message);  
			}			
			return result;
		}


		/// <summary>
		/// execute多条 SQL commands 
		/// </summary>
		/// <param name="strSQLs">string</param>
		/// <returns>int</returns>
		public  static int ExecuteSqls(string[] strSQLs)
		{
		 
			OleDbConnection myCn = new OleDbConnection(strConn);			
			OleDbCommand myCmd = new OleDbCommand();			
			int j=strSQLs.Length;

			try
			{
				myCn.Open();				
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
			OleDbTransaction myTrans = myCn.BeginTransaction();

			try