Csharp/C Sharp/Database ADO.net/SQL Utilities — различия между версиями

Материал из .Net Framework эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 11:42, 26 мая 2010

Sql String formatter

using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
    public class Form1 : System.Windows.Forms.Form
    {
      private System.Windows.Forms.RichTextBox rtfSql;
      private System.Windows.Forms.MainMenu mainMenu1;
      private System.Windows.Forms.MenuItem menuItem1;
      private System.Windows.Forms.MenuItem menuItemFormat;
      private System.Windows.Forms.MenuItem menuItem4;
      private System.Windows.Forms.MenuItem menuItemExit;
      private string[] mSqlKeyWords = new string[] {"select","from","where","in","between",
              "is","null","not","order by","asc","desc","insert","into","values","update", 
              "set","delete","truncate","table","join","on","create","drop"};
      public Form1() {
         InitializeComponent();
      }
      private void InitializeComponent() {
         this.rtfSql = new System.Windows.Forms.RichTextBox();
         this.mainMenu1 = new System.Windows.Forms.MainMenu();
         this.menuItem1 = new System.Windows.Forms.MenuItem();
         this.menuItemFormat = new System.Windows.Forms.MenuItem();
         this.menuItem4 = new System.Windows.Forms.MenuItem();
         this.menuItemExit = new System.Windows.Forms.MenuItem();
         this.SuspendLayout();
         // 
         // rtfSql
         // 
         this.rtfSql.Dock = System.Windows.Forms.DockStyle.Top;
         this.rtfSql.Font = new System.Drawing.Font("Courier New", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
         this.rtfSql.Location = new System.Drawing.Point(0, 0);
         this.rtfSql.Name = "rtfSql";
         this.rtfSql.Size = new System.Drawing.Size(290, 290);
         this.rtfSql.TabIndex = 0;
         this.rtfSql.Text = "";
         // 
         // mainMenu1
         // 
         this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
                                                                                  this.menuItem1});
         // 
         // menuItem1
         // 
         this.menuItem1.Index = 0;
         this.menuItem1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
                                                                                  this.menuItemFormat,
                                                                                  this.menuItem4,
                                                                                  this.menuItemExit});
         this.menuItem1.Text = "&Actions";
         // 
         // menuItemFormat
         // 
         this.menuItemFormat.Index = 0;
         this.menuItemFormat.Shortcut = System.Windows.Forms.Shortcut.F12;
         this.menuItemFormat.Text = "&Format statements";
         this.menuItemFormat.Click += new System.EventHandler(this.menuItemFormat_Click);
         // 
         // menuItem4
         // 
         this.menuItem4.Index = 1;
         this.menuItem4.Text = "-";
         // 
         // menuItemExit
         // 
         this.menuItemExit.Index = 2;
         this.menuItemExit.Text = "E&xit";
         this.menuItemExit.Click += new System.EventHandler(this.menuItemExit_Click);
         // 
         // frmSql
         // 
         this.AutoScaleBaseSize = new System.Drawing.Size(8, 15);
         this.ClientSize = new System.Drawing.Size(300, 300);
         this.Controls.Add(this.rtfSql);
         this.Font = new System.Drawing.Font("Courier New", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
         this.Menu = this.mainMenu1;
         this.Name = "frmSql";
         this.Text = "SQL Tool";
         this.WindowState = System.Windows.Forms.FormWindowState.Maximized;
         this.ResumeLayout(false);
      }
      static void Main() {
            Application.Run(new Form1());
      }

      private void menuItemFormat_Click(object sender, System.EventArgs e)
      {
         for (int i = 0; i < 23; i++) 
         {
            int index = 0;
            while ( (index = rtfSql.Find(mSqlKeyWords[i],index,RichTextBoxFinds.WholeWord)) >= 0) 
            {
               index++;
               rtfSql.SelectionColor = Color.Blue;
               rtfSql.SelectedText = mSqlKeyWords[i].ToUpper();
            }
         }
      }
      private void menuItemExit_Click(object sender, System.EventArgs e)
      {
         Application.Exit(); 
      }
    }


Sql tools

using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
    public class Form1 : System.Windows.Forms.Form
    {
      private System.Windows.Forms.RichTextBox rtfSql;
      private System.Windows.Forms.Splitter splitter1;
      private System.Windows.Forms.ListView listViewResult;
      private System.Windows.Forms.MainMenu mainMenu1;
      private System.Windows.Forms.MenuItem menuItem1;
      private System.Windows.Forms.MenuItem menuItemExecute;
      private System.Windows.Forms.MenuItem menuItem4;
      private System.Windows.Forms.MenuItem menuItemExit;
      private SqlCommand mCommand;
      private string[] mSqlKeyWords = new string[] {"select","from","where","in","between",
              "is","null","not","order by","asc","desc","insert","into","values","update", 
              "set","delete","truncate","table","join","on","create","drop"};
      private System.ruponentModel.Container components = null;
      public Form1() {
         InitializeComponent();
      }
      private void InitializeComponent() {
         this.rtfSql = new System.Windows.Forms.RichTextBox();
         this.splitter1 = new System.Windows.Forms.Splitter();
         this.listViewResult = new System.Windows.Forms.ListView();
         this.mainMenu1 = new System.Windows.Forms.MainMenu();
         this.menuItem1 = new System.Windows.Forms.MenuItem();
         this.menuItemExecute = new System.Windows.Forms.MenuItem();
         this.menuItem4 = new System.Windows.Forms.MenuItem();
         this.menuItemExit = new System.Windows.Forms.MenuItem();
         this.SuspendLayout();
         // 
         // rtfSql
         // 
         this.rtfSql.Dock = System.Windows.Forms.DockStyle.Top;
         this.rtfSql.Font = new System.Drawing.Font("Courier New", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
         this.rtfSql.Location = new System.Drawing.Point(0, 0);
         this.rtfSql.Name = "rtfSql";
         this.rtfSql.Size = new System.Drawing.Size(292, 96);
         this.rtfSql.TabIndex = 0;
         this.rtfSql.Text = "";
         // 
         // splitter1
         // 
         this.splitter1.Dock = System.Windows.Forms.DockStyle.Top;
         this.splitter1.Location = new System.Drawing.Point(0, 96);
         this.splitter1.Name = "splitter1";
         this.splitter1.Size = new System.Drawing.Size(292, 3);
         this.splitter1.TabIndex = 1;
         this.splitter1.TabStop = false;
         // 
         // listViewResult
         // 
         this.listViewResult.Dock = System.Windows.Forms.DockStyle.Fill;
         this.listViewResult.GridLines = true;
         this.listViewResult.Location = new System.Drawing.Point(0, 99);
         this.listViewResult.Name = "listViewResult";
         this.listViewResult.Size = new System.Drawing.Size(292, 173);
         this.listViewResult.TabIndex = 2;
         this.listViewResult.View = System.Windows.Forms.View.Details;
         // 
         // mainMenu1
         // 
         this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
                                                                                  this.menuItem1});
         // 
         // menuItem1
         // 
         this.menuItem1.Index = 0;
         this.menuItem1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
                                                                                  this.menuItemExecute,
                                                                                  this.menuItem4,
                                                                                  this.menuItemExit});
         this.menuItem1.Text = "&Actions";
         // 
         // menuItemExecute
         // 
         this.menuItemExecute.Index = 0;
         this.menuItemExecute.Shortcut = System.Windows.Forms.Shortcut.F5;
         this.menuItemExecute.Text = "&Execute";
         this.menuItemExecute.Click += new System.EventHandler(this.menuItemExecute_Click);
         // 
         // menuItem4
         // 
         this.menuItem4.Index = 1;
         this.menuItem4.Text = "-";
         // 
         // menuItemExit
         // 
         this.menuItemExit.Index = 2;
         this.menuItemExit.Text = "E&xit";
         this.menuItemExit.Click += new System.EventHandler(this.menuItemExit_Click);
         // 
         // frmSql
         // 
         this.AutoScaleBaseSize = new System.Drawing.Size(8, 15);
         this.ClientSize = new System.Drawing.Size(292, 272);
         this.Controls.Add(this.listViewResult);
         this.Controls.Add(this.splitter1);
         this.Controls.Add(this.rtfSql);
         this.Font = new System.Drawing.Font("Courier New", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
         this.Menu = this.mainMenu1;
         this.Name = "frmSql";
         this.Text = "SQL Tool";
         this.WindowState = System.Windows.Forms.FormWindowState.Maximized;
         this.ResumeLayout(false);
      }
      static void Main() {
            Application.Run(new Form1());
      }
      private void menuItemExecute_Click(object sender, System.EventArgs e)
      {
         listViewResult.Columns.Clear();
         listViewResult.Items.Clear();
         string selectedText = rtfSql.SelectedText;
         if (selectedText.Length == 0)
            selectedText = rtfSql.Text;

         if (selectedText.ToLower().IndexOf("select", 0) >= 0)
            ExecuteSelect(selectedText);
         else
            ExecuteNonQuery(selectedText);
    
      }
      private void ExecuteSelect(string pText)
      {
         SqlConnection mConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
        
         try 
         {
            bool first = true;
            ListViewItem lvi = null;
            mCommand = new SqlCommand(pText, mConnection);
            mConnection.Open();
            SqlDataReader dr = mCommand.ExecuteReader();
            if (dr == null) 
               return;
            while (dr.Read()) 
            {
               if (first) 
               {
                  for (int i = 0; i < dr.FieldCount; i++)
                     listViewResult.Columns.Add( dr.GetName(i).ToString(), 50, HorizontalAlignment.Left );
                  first = false;
               }
               for (int i = 0; i < dr.FieldCount; i++) 
               {
                  if (i == 0)
                     lvi = listViewResult.Items.Add(dr.GetValue(i).ToString());
                  else
                     lvi.SubItems.Add(dr.GetValue(i).ToString());
               }
            }
         }
         catch (System.Exception err) 
         {
            Console.WriteLine(err.Message);
         }
         finally 
         {
            mConnection.Close();
         }
      }
      private void ExecuteNonQuery(string pText)
      {
         SqlConnection mConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
         try 
         {
            int rowsAffected = 0;
            mCommand = new SqlCommand(pText, mConnection);
            mConnection.Open();
            rowsAffected = mCommand.ExecuteNonQuery();
            Console.WriteLine(rowsAffected + " row(s) affected");
         }
         catch (System.Exception err) 
         {
            Console.WriteLine(err.Message);
         }
         finally 
         {
            mConnection.Close();
         }
      }
      private void menuItemExit_Click(object sender, System.EventArgs e)
      {
         Application.Exit(); 
      }
    }