using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace hetaoTest
{
public partial class Form1 : Form
    {
static SqlConnection conn = null;
public Form1()
        {
            InitializeComponent();
        }

private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }
/// <summary>
/// 添加的方法
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
try
            {

                {
string conStr = "Data Source=127.0.0.1;Initial Catalog=TestDB;Integrated Security=True";
                conn = new SqlConnection(conStr);
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
//-----------------------------添加方法1-----------------------------
//cmd.CommandText = "insert into T_UserName(name,age)Values('" + txtName.Text.Trim() + "','" 
//   + txtAge.Text.Trim() + "')";
//-----------------------------添加方法2-----------------------------
                cmd.CommandText = "INSERT INTO T_UserName(name,age)VALUES(@name,@age)";
//cmd.Parameters.AddWithValue("@name",txtName.Text.Trim());
//cmd.Parameters.AddWithValue("@age",txtAge.Text.Trim());
//-----------------------------添加方法3-----------------------------
                cmd.Parameters.Add("@name", SqlDbType.VarChar);
                cmd.Parameters["@name"].Value = txtName.Text.Trim();

                cmd.Parameters.Add("@age", SqlDbType.VarChar);
                cmd.Parameters["@age"].Value = txtAge.Text.Trim();
if (MessageBox.Show("确定添加么？", "My Application", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
== DialogResult.Yes)
                {

                    cmd.ExecuteNonQuery();
                }



                }
            }
catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

/// <summary>
/// 使用sqlcommand对象来返回数据集合的方法
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>

private void Form1_Load(object sender, EventArgs e)
        {
string connStr = "Data Source=127.0.0.1;Initial Catalog=TestDB;Integrated Security=True";
string SqlStr = "SELECT * FROM T_UserName";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = SqlStr;

//int flat = (int)(cmd.ExecuteScalar());
//MessageBox.Show(Convert.ToString(flat));

            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adp.Fill(dt);

            dataGridView1.DataSource = dt;


        }

/// <summary>
/// 
///使用sqlcommand对象来返回SQL执行正确与否
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>

private void btnScan1_Click(object sender, EventArgs e)
        {
string connStr = "Data Source=127.0.0.1;Initial Catalog=TestDB;Integrated Security=True";
string sqlStr = "SELECT * FROM T_UserName1";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sqlStr;
try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("成功查询", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
            }

catch
            {
                MessageBox.Show("查询失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
            }
        }

private void button2_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }



/// <summary>
///   使用sqlcommand对象来操作存储过程
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
string strconn = "Data Source=127.0.0.1;Initial Catalog=TestDB;Integrated Security=True";
            SqlConnection conn = new SqlConnection(strconn);
            conn.Open();
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.CommandText = "a";
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Connection = conn;
//sqlcmd.Parameters.Add("@param1", textBox1.Text.Trim());
//sqlcmd.Parameters.Add("@param2", textBox2.Text.Trim());
//sqlcmd.Parameters.Add("@param3", textBox3.Text.Trim());

            SqlDataAdapter adp = new SqlDataAdapter(sqlcmd);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            dataGridView1.DataSource = dt;
        }

private void button3_Click(object sender, EventArgs e)
        {
string connStr = "Data Source=127.0.0.1;Initial Catalog=TestDB;Integrated Security=True";
string SqlStr = "SELECT * FROM T_UserName";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand cmd =new SqlCommand(SqlStr,conn);

            SqlDataReader dr = cmd.ExecuteReader();//返回只能读取,不能写入的SqlDataReader对象

while (dr.Read())
            {
                MessageBox.Show("ID："+dr.GetValue(0)+"   Name: "+dr.GetValue(1)+"   Age: "+dr.GetValue(2)+"  Sex: "+dr.GetValue(3));
            }


        }
/// <summary>
/// ExecuteNonQuery方法
///一般修改,删除,插入都用它
///返回受影响的行数。
/// sql="update 表 set 栏位1=值1，栏位2=值2 where (条件);"
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
        private void button5_Click(object sender, EventArgs e)
        {
string connStr = "Data Source=127.0.0.1;Initial Catalog=TestDB;Integrated Security=True";
string sqlStr = "Update T_UserName set name='Admin',Age='100',Sex='男' where(ID=66)";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sqlStr,conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("修改成功！");
        }
    }
}