procedures.cs

非常实用的NET的SQL 程序

		}
		/// <summary>
		///			This sends a tooltip when the mouse is over the Execute button
		/// </summary>
		/// <param name="sender"> </param>
		/// <param name="e"> </param>
		protected void btnExecute_OnMouseHover (object sender, System.EventArgs e)
		{
			const string message = @"Execute the selected stored procedure";

			toolTip1.Active = true;

			toolTip1.SetToolTip(btnExecute, message);
		}
		/// <summary>
		///			This sends a tooltip when the mouse is over the View Button
		/// </summary>
		/// <param name="sender"> </param>
		/// <param name="e"> </param>
		protected void btnInfo_OnMouseHover (object sender, System.EventArgs e)
		{
			const string message = @"List all parameters for the selected stored procedure";

			toolTip1.Active = true;

			toolTip1.SetToolTip(btnInfo, message);
		}
		/// <summary>
		///			This sends a tooltip when the mouse is over the List Command button
		/// </summary>
		/// <param name="sender"> </param>
		/// <param name="e"> </param>
		protected void btnProcNames_OnMouseHover (object sender, System.EventArgs e)
		{
			const string message = @"List all user stored procedures for the database";

			toolTip1.Active = true;

			toolTip1.SetToolTip(btnProcNames, message);
		}
        

		/// <summary>
		///			This grabs the Stored Procs for a given Database
		/// </summary>
		/// <param name="sender"> </param>
		/// <param name="e"> </param>
		protected void btnProcNames_Click (object sender, System.EventArgs e)
		{
			string connection = "Initial Catalog=" + txtDataBase.Text.Trim() +
								";Data Source="    + txtServer.Text.Trim() +
								";Uid="			   + txtUser.Text.Trim() +
								";Pwd="			   + txtPassword.Text.Trim();

			SQLConnection cnn = new SQLConnection(connection);
			Data d = new Data();
			SQLDataReader dr = null;
			
			try
			{
				cnn.Open();

				dr = d.SelectProcs(ref cnn);

				int i = 0;

				lstProcs.Items.Clear();
				
				while(dr.Read())
				{
					lstProcs.InsertItem(i , dr["Name"]);
					i++;
				}
				
				btnExecute.Enabled = true;
				btnInfo.Enabled = true;
			}
			catch(Exception ex)
			{
				ErrorLog errLog = new ErrorLog(); 
				
				errLog.LogError(ex.Message ,"btnProcNames_Click");
			}
			finally
			{
				dr.Close();
				cnn.Close();
			}
		}
		/// <summary>
		///			This Executes a selected stored procedure
		/// </summary>
		/// <param name="sender"> </param>
		/// <param name="e"> </param>
		protected void btnExecute_Click (object sender, System.EventArgs e)
		{
			string connection = "Provider=sqloledb;" +
								"Initial Catalog=" + txtDataBase.Text.Trim() +
								";Data Source="    + txtServer.Text.Trim() +
								";Uid="			   + txtUser.Text.Trim() +
								";Pwd="			   + txtPassword.Text.Trim();
			Data d = new Data();
			ADOConnection cnn = new ADOConnection(connection);
			cnn.ConnectionTimeout = 1;

			try
			{	//if nothing is selected let the user know
				if(lstProcs.SelectedItem == null )
					MessageBox.Show("You must select a procedure before execution","Rut Ro");
				
				else
				{
					//Don't allow a proc to run until viewed
					if( !datInfo.CaptionText.Trim().Equals(lstProcs.Text))
						MessageBox.Show("You must select the correct proc information to execute the" +
										" selected stored procedure","Rut Ro");
					else
					{
						cnn.Open();
						
						if(!rbSelect.Checked)//the other check is selected
						{
							try
							{
								object[] arr = new object[lblCount.Text.ToInt16()];
								arr = ReturnParameters(arr);
								d.ExecuteProc(ref cnn, lstProcs.Text, arr);
							}
							catch(Exception ex)
							{
								ErrorLog errLog = new ErrorLog(); 
								
								errLog.LogError(ex.Message ,"Btn_Execute_Click");
							}

						}
						else 
						{	//	build Parameter Array to be passed into the stored proc if more than 1
							if(lblCount.Text.ToInt16() > 1 )
							{
								try
								{
									object[] arr = new object[lblCount.Text.ToInt16()];
									arr = ReturnParameters(arr);
									datReturn.DataSource  = d.Select( cnn, lstProcs.Text, arr);
									datReturn.DataMember  = "Return";
									datReturn.CaptionText = lstProcs.Text;
								}
								catch(Exception ex)
								{
									ErrorLog errLog = new ErrorLog(); 
									
									errLog.LogError(ex.Message ,"Btn_Execute_Click");
								} 
							}
							else
							{ 
								try
								{	//Select List statement with no paramaters 
									datReturn.DataSource  = d.Select(cnn,lstProcs.Text.ToString());
									datReturn.DataMember = "Return";
									datReturn.CaptionText = lstProcs.Text;
								}
								catch(Exception ex)
								{
									ErrorLog errLog = new ErrorLog(); 
									
									errLog.LogError(ex.Message ,"Btn_Execute_Click");
								} 
							}
						}
					}
				}
			}
			finally
			{	//cleanup - close and release the connection
				if(cnn.State == DBObjectState.Open)
				{	
					cnn.Close();
					cnn.Dispose();
				}
			}
		}

		/// <summary>
		///			This creates an array for multiple parameters
		/// </summary>
		private object[] ReturnParameters(params object[] arr)
		{			
			//cast the Datagrid datasource to a DataTable 
			DataTable dtt = (DataTable)datInfo.DataSource;
			
			// fill an array with the values
			for(int i = 0;i < dtt.Rows.Count ;i++)
				arr[i] = dtt.Rows[i]["Value"];

			return(arr);
		}


		/// <summary>
		///			This Selects the Parameter Information for a Stored Proc
		/// </summary>
		/// <param name="sender"> </param>
		/// <param name="e"> </param>
		protected void btnInfo_Click (object sender, System.EventArgs e)
		{
			string connection = "Provider=sqloledb.1;" + 
								"Initial Catalog=" + txtDataBase.Text.Trim() +
								";Data Source="    + txtServer.Text.Trim() +
								";Uid="			   + txtUser.Text.Trim() +
								";Pwd="			   + txtPassword.Text.Trim();

			Data d = new Data();
			DataTable dt = null;
			ADOConnection cnn = new ADOConnection(connection);
			cnn.ConnectionTimeout = 1;
			
			//if the user does nto select any procedure then let him know
			if(lstProcs.SelectedItem == null)
				MessageBox.Show("You must select a stored procedure to view information", "Rut Ro");	

			else
			{
				try
				{
					cnn.Open();
					dt = d.SelectParms(cnn, lstProcs.Text);
					
					datInfo.DataSource = dt;
					//set the properties of the grid
					datInfo.CurrentGridTable.GridColumns[0].Width = 35;
					datInfo.CurrentGridTable.GridColumns[1].Width = 110;
					datInfo.CurrentGridTable.GridColumns[2].Width = 80;
					datInfo.CurrentGridTable.GridColumns[3].Width = 40;

					if(dt.Rows.Count > 4)
						datInfo.CurrentGridTable.GridColumns[4].Width = 240;
					else
						datInfo.CurrentGridTable.GridColumns[4].Width = 258;
					
					datInfo.CaptionText = lstProcs.Text;
					lblCount.Text = dt.Rows.Count.ToString();
				}
				finally
				{
					dt.Dispose();
					if(cnn.State == DBObjectState.Open)
					{	//cleanup - close and release the connection
						cnn.Close();
						cnn = null;
					}
				}
			}
		}

		public static void Main(string[] args) 
        {
            Application.Run(new Procedures());
        }
    }
}