frmwebtools.cs

用于定时执行存储过程的程序

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data .Odbc ;
using System.Data .OracleClient ;

namespace WebTools
{
	/// <summary>
	/// Form1 的摘要说明。
	/// </summary>
	public class frmWebTools : System.Windows.Forms.Form
	{
		private OdbcConnection conn;
		private OracleConnection connOra;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.ComboBox combWay;
		private System.Windows.Forms.Button btnDo;
		private System.Windows.Forms.TextBox textNY1;
		private System.Windows.Forms.Button btnDBA16;
		private System.Windows.Forms.TabControl tabControl1;
		private System.Windows.Forms.TabPage tabPageDBB03;
		private System.Windows.Forms.TabPage tabPageDBA16;
		private System.Windows.Forms.TextBox textNY2;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.TabPage tabPageSTAT;
		private System.Windows.Forms.Button btnStatInfo;
		private System.Windows.Forms.TextBox textBox1;
		private System.Windows.Forms.TabPage tabPageDKB01;
		private System.Windows.Forms.Button btnRefreshDKB01;
		/// <summary>
		/// 必需的设计器变量。
		/// </summary>
		private System.ComponentModel.Container components = null;

		public frmWebTools()
		{
			//
			// Windows 窗体设计器支持所必需的
			//
			InitializeComponent();

			//
			// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
			//
			combWay.SelectedIndex = 0;
			OracleConnectDB();
			System.DateTime dt = System.DateTime .Now ;
			if(dt.Month == 1)
			{
				textNY1.Text = string.Format ("{0}12" , dt.Year - 1 );
				textNY2.Text = string.Format ("{0}12" , dt.Year - 1 );
			}
			else if(dt.Month <= 10)
			{
				textNY1.Text = string.Format ("{0}0{1}" , dt.Year ,dt.Month - 1 );
				textNY2.Text = string.Format ("{0}0{1}" , dt.Year ,dt.Month - 1 );
			}
			else
			{
				textNY1.Text = string.Format ("{0}{1}" , dt.Year ,dt.Month - 1 );
				textNY2.Text = string.Format ("{0}{1}" , dt.Year ,dt.Month - 1 );
			}
		}

		private void OdbcConnectDB()
		{
			conn = new OdbcConnection (string.Format ("Provider=MSDAORA.1;Data Source={0};User ID={1} ;Password={2};" , 
				"hskst" , 
				"hskst" , 
				"hskst"));
			try
			{
				conn.Open ();
				return;
			}
			catch (OdbcException)
			{
				btnDo.Enabled = false;
			}
		}

		private void OracleConnectDB()
		{
			connOra = new OracleConnection (string.Format ("Data Source={0};User ID={1};Password={2}" , 
				"hskst",
				"hskst" , 
				"hskst"));
			try
			{
				connOra.Open ();
				return;
			}
			catch (OracleException)
			{
				btnDo.Enabled = false;
			}
		}

		/// <summary>
		/// 清理所有正在使用的资源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows 窗体设计器生成的代码
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{
			this.btnDo = new System.Windows.Forms.Button();
			this.combWay = new System.Windows.Forms.ComboBox();
			this.textNY1 = new System.Windows.Forms.TextBox();
			this.label1 = new System.Windows.Forms.Label();
			this.btnDBA16 = new System.Windows.Forms.Button();
			this.tabControl1 = new System.Windows.Forms.TabControl();
			this.tabPageDBB03 = new System.Windows.Forms.TabPage();
			this.tabPageDBA16 = new System.Windows.Forms.TabPage();
			this.textNY2 = new System.Windows.Forms.TextBox();
			this.label2 = new System.Windows.Forms.Label();
			this.tabPageSTAT = new System.Windows.Forms.TabPage();
			this.textBox1 = new System.Windows.Forms.TextBox();
			this.btnStatInfo = new System.Windows.Forms.Button();
			this.tabPageDKB01 = new System.Windows.Forms.TabPage();
			this.btnRefreshDKB01 = new System.Windows.Forms.Button();
			this.tabControl1.SuspendLayout();
			this.tabPageDBB03.SuspendLayout();
			this.tabPageDBA16.SuspendLayout();
			this.tabPageSTAT.SuspendLayout();
			this.tabPageDKB01.SuspendLayout();
			this.SuspendLayout();
			// 
			// btnDo
			// 
			this.btnDo.Location = new System.Drawing.Point(211, 103);
			this.btnDo.Name = "btnDo";
			this.btnDo.Size = new System.Drawing.Size(120, 24);
			this.btnDo.TabIndex = 3;
			this.btnDo.Text = "一月一日";
			this.btnDo.Click += new System.EventHandler(this.btnDo_Click);
			// 
			// combWay
			// 
			this.combWay.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
			this.combWay.Items.AddRange(new object[] {
														 "1.只插入数据（DBB011源，有数据则终止）",
														 "2.只更新数据（无数据则终止）",
														 "3.更新数据（DBB011源，先删除后处理）",
														 "4.只插入数据（DBA04源，有数据则终止）",
														 "5.只更新数据（同2）",
														 "6.更新数据（DBA04源，先删除后处理）"});
			this.combWay.Location = new System.Drawing.Point(19, 69);
			this.combWay.Name = "combWay";
			this.combWay.Size = new System.Drawing.Size(309, 20);
			this.combWay.TabIndex = 2;
			// 
			// textNY1
			// 
			this.textNY1.Location = new System.Drawing.Point(173, 26);
			this.textNY1.Name = "textNY1";
			this.textNY1.Size = new System.Drawing.Size(157, 21);
			this.textNY1.TabIndex = 0;
			this.textNY1.Text = "200606";
			this.textNY1.TextAlign = System.Windows.Forms.HorizontalAlignment.Center;
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(19, 26);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(145, 16);
			this.label1.TabIndex = 1;
			this.label1.Text = "月份（YYYYMM）";
			this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
			// 
			// btnDBA16
			// 
			this.btnDBA16.Location = new System.Drawing.Point(192, 86);
			this.btnDBA16.Name = "btnDBA16";
			this.btnDBA16.Size = new System.Drawing.Size(134, 25);
			this.btnDBA16.TabIndex = 1;
			this.btnDBA16.Text = "一月一日";
			this.btnDBA16.Click += new System.EventHandler(this.btnDBA16_Click);
			// 
			// tabControl1
			// 
			this.tabControl1.Controls.Add(this.tabPageDBB03);
			this.tabControl1.Controls.Add(this.tabPageDBA16);
			this.tabControl1.Controls.Add(this.tabPageSTAT);
			this.tabControl1.Controls.Add(this.tabPageDKB01);
			this.tabControl1.Location = new System.Drawing.Point(10, 9);
			this.tabControl1.Name = "tabControl1";
			this.tabControl1.SelectedIndex = 0;
			this.tabControl1.Size = new System.Drawing.Size(374, 189);
			this.tabControl1.TabIndex = 2;
			// 
			// tabPageDBB03
			// 
			this.tabPageDBB03.Controls.Add(this.btnDo);
			this.tabPageDBB03.Controls.Add(this.label1);
			this.tabPageDBB03.Controls.Add(this.textNY1);
			this.tabPageDBB03.Controls.Add(this.combWay);
			this.tabPageDBB03.Location = new System.Drawing.Point(4, 21);
			this.tabPageDBB03.Name = "tabPageDBB03";
			this.tabPageDBB03.Size = new System.Drawing.Size(366, 164);
			this.tabPageDBB03.TabIndex = 0;
			this.tabPageDBB03.Text = "DBB03";
			// 
			// tabPageDBA16
			// 
			this.tabPageDBA16.Controls.Add(this.textNY2);
			this.tabPageDBA16.Controls.Add(this.label2);
			this.tabPageDBA16.Controls.Add(this.btnDBA16);
			this.tabPageDBA16.Location = new System.Drawing.Point(4, 21);
			this.tabPageDBA16.Name = "tabPageDBA16";
			this.tabPageDBA16.Size = new System.Drawing.Size(366, 164);
			this.tabPageDBA16.TabIndex = 1;
			this.tabPageDBA16.Text = "DBA16";
			// 
			// textNY2
			// 
			this.textNY2.Location = new System.Drawing.Point(180, 26);
			this.textNY2.Name = "textNY2";
			this.textNY2.Size = new System.Drawing.Size(157, 21);
			this.textNY2.TabIndex = 2;
			this.textNY2.Text = "200606";
			this.textNY2.TextAlign = System.Windows.Forms.HorizontalAlignment.Center;
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(29, 26);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(145, 16);
			this.label2.TabIndex = 3;
			this.label2.Text = "月份（YYYYMM）";
			this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
			// 
			// tabPageSTAT
			// 
			this.tabPageSTAT.Controls.Add(this.textBox1);
			this.tabPageSTAT.Controls.Add(this.btnStatInfo);
			this.tabPageSTAT.Location = new System.Drawing.Point(4, 21);
			this.tabPageSTAT.Name = "tabPageSTAT";
			this.tabPageSTAT.Size = new System.Drawing.Size(366, 164);
			this.tabPageSTAT.TabIndex = 2;
			this.tabPageSTAT.Text = "StatInfo Timer";
			// 
			// textBox1
			// 
			this.textBox1.Enabled = false;
			this.textBox1.Location = new System.Drawing.Point(10, 52);
			this.textBox1.Multiline = true;
			this.textBox1.Name = "textBox1";
			this.textBox1.Size = new System.Drawing.Size(345, 94);
			this.textBox1.TabIndex = 1;
			this.textBox1.Text = "1、PROC_STATINFO_SUB：数据状态统计\r\n2、SP_STATWELL_DATA：单井信息统计函数\r\n3、刷新DBA08的层位代码信息\r\n4、PROC" +
				"_STATCSINFO：刷新DBB04数据\r\n5、PROC_STATINFO_TLJXX：统计提捞井数据到DBA08_TLJ";
			// 
			// btnStatInfo
			// 
			this.btnStatInfo.Location = new System.Drawing.Point(230, 17);
			this.btnStatInfo.Name = "btnStatInfo";
			this.btnStatInfo.Size = new System.Drawing.Size(106, 25);
			this.btnStatInfo.TabIndex = 0;
			this.btnStatInfo.Text = "定时数据刷新";
			this.btnStatInfo.Click += new System.EventHandler(this.btnStatInfo_Click);
			// 
			// tabPageDKB01
			// 
			this.tabPageDKB01.Controls.Add(this.btnRefreshDKB01);
			this.tabPageDKB01.Location = new System.Drawing.Point(4, 21);
			this.tabPageDKB01.Name = "tabPageDKB01";
			this.tabPageDKB01.Size = new System.Drawing.Size(366, 164);
			this.tabPageDKB01.TabIndex = 3;
			this.tabPageDKB01.Text = "DKB01";
			// 
			// btnRefreshDKB01
			// 
			this.btnRefreshDKB01.Location = new System.Drawing.Point(192, 96);
			this.btnRefreshDKB01.Name = "btnRefreshDKB01";
			this.btnRefreshDKB01.Size = new System.Drawing.Size(96, 23);
			this.btnRefreshDKB01.TabIndex = 0;
			this.btnRefreshDKB01.Text = "一天一日";
			this.btnRefreshDKB01.Click += new System.EventHandler(this.btnRefreshDKB01_Click);
			// 
			// frmWebTools
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(396, 205);
			this.Controls.Add(this.tabControl1);
			this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;
			this.MaximizeBox = false;
			this.Name = "frmWebTools";
			this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
			this.Text = "Oracle数据后期处理工具";
			this.tabControl1.ResumeLayout(false);
			this.tabPageDBB03.ResumeLayout(false);
			this.tabPageDBA16.ResumeLayout(false);
			this.tabPageSTAT.ResumeLayout(false);
			this.tabPageDKB01.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 应用程序的主入口点。
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new frmWebTools());
		}

		private void btnDo_Click(object sender, System.EventArgs e)
		{
			DoProcess_DBB03(textNY1.Text , combWay.SelectedIndex + 1 , connOra);
			MessageBox.Show ("执行开发部产量、井数分类汇总DBB03完成");
		}

		private void DoProcess_DBB03(string strNY , int nWay , OdbcConnection conn)
		{
			OdbcCommand command = new OdbcCommand ("proc_dbb03" , conn);
			command.CommandType = CommandType.StoredProcedure ;
			OdbcParameter param1 = new OdbcParameter ("m_ny" , OdbcType.VarChar , 20);
			param1.Value = strNY ;
			command.Parameters .Add (param1 );
			OdbcParameter param2 = new OdbcParameter ("m_way" , OdbcType.Int );
			param2.Value = nWay;
			command.Parameters .Add (param2);
			command.ExecuteNonQuery ();
		}

		private void DoProcess_DBB03(string strNY , int nWay , OracleConnection conn)
		{
			OracleCommand command = new OracleCommand ("proc_dbb03" , conn);
			command.CommandType = CommandType.StoredProcedure ;
			OracleParameter param1 = new OracleParameter ("m_ny" , OracleType.VarChar , 20);
			param1.Value = strNY ;
			command.Parameters .Add (param1 );
			OracleParameter param2 = new OracleParameter ("m_way" , OracleType.Int32 );
			param2.Value = nWay;
			command.Parameters .Add (param2);
			command.ExecuteNonQuery ();
		}

		private void btnDBA16_Click(object sender, System.EventArgs e)
		{
			DoProcess_DBA16(textNY2.Text , connOra);
			MessageBox.Show ("执行井号表分类汇总DBA16完成");
		}

		private void DoProcess_DBA16(string strNY , OdbcConnection conn)
		{
			OdbcCommand command = new OdbcCommand ("proc_dba16" , conn);
			command.CommandType = CommandType.StoredProcedure ;
			OdbcParameter param1 = new OdbcParameter ("m_ny" , OdbcType.VarChar , 20);
			param1.Value = strNY ;
			command.Parameters .Add (param1 );
			command.ExecuteNonQuery ();
		}

		private void DoProcess_DBA16(string strNY , OracleConnection conn)
		{
			OracleCommand command = new OracleCommand ("proc_dba16" , conn);
			command.CommandType = CommandType.StoredProcedure ;
			OracleParameter param1 = new OracleParameter ("m_ny" , OracleType.VarChar , 20);
			param1.Value = strNY ;
			command.Parameters .Add (param1 );
			command.ExecuteNonQuery ();
		}

		private void btnStatInfo_Click(object sender, System.EventArgs e)
		{
			int nRet = DoProcess_STATINFO(connOra);
			if(nRet < 0)
				MessageBox.Show ("执行异常，请检查存储过程PROC_STATINFO");
			else
				MessageBox.Show ("执行PROC_STATINFO存储过程完成");
		}

		private void btnRefreshDKB01_Click(object sender, System.EventArgs e)
		{
			DoProcess_DKB01(connOra);
			MessageBox.Show ("执行PROC_DKB01_BAK存储过程完成");
		}

		private int DoProcess_STATINFO(OdbcConnection conn)
		{
			OdbcCommand command = new OdbcCommand ("proc_statinfo" , conn);
			command.CommandType = CommandType.StoredProcedure ;
			OdbcParameter param2 = new OdbcParameter ("OUT_NUMRTNCD" , OdbcType.Numeric);
			param2.Direction = ParameterDirection.Output;
			command.Parameters .Add (param2);
			command.ExecuteNonQuery ();
			return int.Parse (param2.Value.ToString ()) ;
		}

		private int DoProcess_STATINFO(OracleConnection conn)
		{
			OracleCommand command = new OracleCommand ("proc_statinfo" , conn);
			command.CommandType = CommandType.StoredProcedure ;
			OracleParameter param2 = new OracleParameter ("OUT_NUMRTNCD" , OracleType.Number);
			param2.Direction = ParameterDirection.Output ;
			command.Parameters .Add (param2);
			command.ExecuteNonQuery ();
			return int.Parse (param2.Value.ToString ()) ;
		}

		private void DoProcess_DKB01(OracleConnection conn)
		{
			OracleCommand command = new OracleCommand ("proc_dkb01_bak" , conn);
			command.CommandType = CommandType.StoredProcedure ;
			command.ExecuteNonQuery ();
		}
	}
}