ADO.Net 之手机通讯录

SQLHelper代码部分

class SQLHelper

    {

        private static readonly string cs = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;

        public static SqlConnection CreateConnection()

        {

            SqlConnection conn = new SqlConnection(cs);

            conn.Open();

            return conn;

        }

        public static int ExecuteNonQuery(SqlConnectionconn, string sql, params SqlParameter[] parameters)

        {

            using (SqlCommand cmd = conn.CreateCommand())

            {

                cmd.CommandText = sql;

                cmd.Parameters.AddRange(parameters);

                return cmd.ExecuteNonQuery();

            }

        }

        public static int ExecuteNonQuery(stringsql, params SqlParameter[] parameters)

        {

            using (SqlConnection conn = CreateConnection())

            {

                return ExecuteNonQuery(conn, sql,parameters);

            }

        }

        public static object ExecuteScalar(SqlConnectionconn, string sql, params SqlParameter[] parameters)

        {

            using (SqlCommand cmd = conn.CreateCommand())

            {

                cmd.CommandText = sql;

                cmd.Parameters.AddRange(parameters);

                return cmd.ExecuteScalar();

            }

        }

        public static object ExecuteScalar(stringsql, params SqlParameter[] parameters)

        {

            using (SqlConnection conn = CreateConnection())

            {

                return ExecuteScalar(conn, sql,parameters);

            }

        }

        public static DataTable ExecuteQuery(SqlConnectionconn, string sql, params SqlParameter[] parameters)

        {

            DataTable table = new DataTable();

            using (SqlCommand cmd = conn.CreateCommand())

            {

                cmd.CommandText = sql;

                cmd.Parameters.AddRange(parameters);

                using (SqlDataReader reader = cmd.ExecuteReader())

                {

                    table.Load(reader);

                }

                return table;

            }

        }

        public static DataTable ExecuteQuery(stringsql, params SqlParameter[] parameters)

        {

            using (SqlConnection conn = CreateConnection())

            {

                return ExecuteQuery(conn, sql, parameters);

            }

        }

}

配置文件：

<?xml version="1.0"encoding="utf-8" ?>

<configuration>

    <startup>

        <supportedRuntime version="v4.0"sku=".NETFramework,Version=v4.5" />

    </startup>

  <connectionStrings>

    <add name="cs" connectionString="Server= 127.0.0.1; user id = sa; password = 111111; database = rupeng"/>

    </connectionStrings>

</configuration>

主界面：

public partial classForm1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void DataLoad()

        {

            DataTable table = SQLHelper.ExecuteQuery("select* from[rupeng].[dbo].[T_Contact]");

            Person.DataSource = table;

            

        }

        private void Form1_Load(object sender, EventArgse)

        {

            Person.AutoGenerateColumns = false;

            DataLoad();

        }

 

        private void Person_CellContentClick(objectsender, DataGridViewCellEventArgs e)

        {

            DataTable table =(DataTable) Person.DataSource;

            DataRow row = table.Rows[e.RowIndex];

            int id = (int)row["Id"];

            if (e.ColumnIndex == 0) //修改

            {

                FormPersonManager form = new FormPersonManager();

                form.Action = "Edit";

                form.EditingId = id;

                form.ShowDialog();

                DataLoad();

            }

            if (e.ColumnIndex == 1) //删除

            {

                if (MessageBox.Show("真的要删除吗？", "提示", MessageBoxButtons.YesNo,MessageBoxIcon.Question) != DialogResult.Yes)

                {

                    return;

                }

                SQLHelper.ExecuteNonQuery("Deletefrom [rupeng].[dbo].[T_Contact] where Id=@Id",

                    new SqlParameter { ParameterName= "@Id", Value = id });

                DataLoad();

            }

 

        }

 

        private void RefreshBtn_Click(object sender,EventArgs e)

        {

            DataLoad();

        }

 

        private void AddBtn_Click(object sender,EventArgs e)

        {

            FormPersonManager form = new FormPersonManager();

            form.Action = "AddNew";

            form.ShowDialog();

            DataLoad();

        }

    }

二级界面：

public partial classFormPersonManager : Form

    {

        public string Action { get; set; }

        public int EditingId { get; set; }

        public FormPersonManager()

        {

            InitializeComponent();

        }

 

        private void Form2_Load(object sender, EventArgse)

        {

            if (Action == "Edit")

            {

                DataTable table = SQLHelper.ExecuteQuery("select* from [rupeng].[dbo].[T_Contact] where Id =@Id",

                    new SqlParameter { ParameterName= "@Id", Value = EditingId });

                if (table.Rows.Count <= 0)

                {

                    MessageBox.Show("找不到数据");

                        return;

                }

                DataRow row = table.Rows[0];

                nameBox.Text = (string)row["Name"];

                genderBox.Text = (string)row["Gender"];

                birthBox.Text = Convert.ToString(row["Birth"]);

                phoneBox.Text = (string)row["PhoneNum"];

                emailBox.Text = (string)row["Email"];

                adressBox.Text = (string)row["Adress"];

            }

        }

 

        private void saveBtn_Click(object sender,EventArgs e)

        {

            if (Action == "Edit")

            {

                string name = nameBox.Text ;

                string gender = genderBox.Text;

                DateTime  birth = Convert.ToDateTime (birthBox.Text );

                string phone= phoneBox.Text;

                string email = emailBox.Text;

                string adress = adressBox.Text;

                SQLHelper.ExecuteNonQuery("Update[rupeng].[dbo].[T_Contact] set Name = @Name , Gender = @Gender, Birth = @Birth,PhoneNum = @PhoneNum, Email= @Email,Adress = @Adress where Id =@Id",

                    new SqlParameter { ParameterName= "@Name", Value = name },

                    new SqlParameter { ParameterName= "@Gender", Value = gender },

                    new SqlParameter { ParameterName= "@Birth", Value = birth },

                    new SqlParameter { ParameterName= "@PhoneNum", Value = phone },

                    new SqlParameter { ParameterName= "@Email", Value = email },

                    new SqlParameter { ParameterName= "@Adress", Value = adress },

                    new SqlParameter {ParameterName= "@Id",Value = EditingId });

                Close();

            }

            else if (Action == "AddNew")

            {

                string name = nameBox.Text;

                string gender = genderBox.Text;

                DateTime birth = Convert.ToDateTime(birthBox.Text);

                string phone = phoneBox.Text;

                string email = emailBox.Text;

                string adress = adressBox.Text;

                SQLHelper.ExecuteNonQuery("Insertinto [rupeng].[dbo].[T_Contact] (Name,Gender,Birth,PhoneNum,Email,Adress)values(@Name , @Gender, @Birth, @PhoneNum, @Email, @Adress)",

                    new SqlParameter { ParameterName= "@Name", Value = name },

                    new SqlParameter { ParameterName= "@Gender", Value = gender },

                    new SqlParameter { ParameterName= "@Birth", Value = birth },

                    new SqlParameter { ParameterName= "@PhoneNum", Value = phone },

                    new SqlParameter { ParameterName= "@Email", Value = email },

                    new SqlParameter { ParameterName= "@Adress", Value = adress });

                Close();

 

            }

        }

}