ADO.NET对数据库进行增删查改

最新推荐文章于 2020-05-27 14:41:34 发布
原创最新推荐文章于 2020-05-27 14:41:34 发布 · 1k 阅读
0 ·
CC 4.0 BY-SA版权
文章标签：
#ado.net #数据库
ASP.NET 专栏收录该内容
4 篇文章
订阅专栏
本文介绍了一个使用 ASP.NET 进行数据库操作的例子，包括连接数据库、查询、更新、插入和删除客户信息等操作。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button4_Click(object sender, EventArgs e)
    {
        string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

        SqlConnection con = new SqlConnection(conStr);

        SqlCommand cmd = new SqlCommand("select CustomerID,CompanyName,City,PostalCode from Customers where CustomerID=@cid", con);

        string cid = txtID.Text.Trim();
        SqlParameter paraID = new SqlParameter("@cid", cid);
        cmd.Parameters.Add(paraID);

        try
        {
            con.Open();

            SqlDataReader sdr = cmd.ExecuteReader();

            while (sdr.Read())
            {
                txtName.Text = sdr[1].ToString();
                txtCity.Text = sdr[2].ToString();
                txtPostcode.Text = sdr[3].ToString();
            }

        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
        finally
        {
            con.Close();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        SqlConnection con = new SqlConnection(conStr);

        SqlCommand cmd = new SqlCommand("delete from Customers where CustomerID=@cid", con);

        string cid = txtID.Text.Trim();
        SqlParameter paraID = new SqlParameter("@cid", cid);
        cmd.Parameters.Add(paraID);

        try
        {
            con.Open();

            cmd.ExecuteNonQuery();

            Response.Write("<script>alert('删除完成。')</script>");

        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
        finally
        {
            con.Close();
        }
    }
    protected void Button5_Click(object sender, EventArgs e)
    {
        string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

        SqlConnection con = new SqlConnection(conStr);

        SqlCommand cmd = new SqlCommand("update Customers Set CompanyName=@cname,City=@city,PostalCode=@postcode where CustomerID=@cid", con);

        string cname = txtName.Text.Trim();
        SqlParameter paraName = new SqlParameter("@cname", cname);
        cmd.Parameters.Add(paraName);

        string city = txtCity.Text.Trim();
        SqlParameter paraCity = new SqlParameter("@city", city);
        cmd.Parameters.Add(paraCity);

        string postcode = txtPostcode.Text.Trim();
        SqlParameter paraPostcode = new SqlParameter("@postcode", postcode);
        cmd.Parameters.Add(paraPostcode);

        string cid = txtID.Text.Trim();
        SqlParameter paraID = new SqlParameter("@cid", cid);
        cmd.Parameters.Add(paraID);

        try
        {
            con.Open();

            cmd.ExecuteNonQuery();

            Response.Write("<script>alert('修改完成！')</script>");
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
        finally
        {
            con.Close();
        }
    }
    protected void Button6_Click(object sender, EventArgs e)
    {
        string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        SqlConnection con = new SqlConnection(conStr);
        SqlCommand cmd = new SqlCommand("insert into Customers(CustomerID, CompanyName) values(@cid,@cname)", con);

        string cid = txtID.Text.Trim();
        SqlParameter paraID = new SqlParameter("@cid", cid);
        cmd.Parameters.Add(paraID);

        string cname = txtName.Text.Trim();
        SqlParameter paraName = new SqlParameter("@cname", cname);
        cmd.Parameters.Add(paraName);

        try
        {
            con.Open();

            cmd.ExecuteNonQuery();

            Response.Write("<script>alert('添加完成。')</script>");

        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
        finally
        {
            con.Close();
        }
    }
    protected void Button7_Click(object sender, EventArgs e)
    {
        string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        SqlConnection con = new SqlConnection(conStr);

        SqlCommand cmd = new SqlCommand("queryCustome", con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter paraNum = new SqlParameter("@num", SqlDbType.Int);
        paraNum.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(paraNum);

        SqlParameter paraID = new SqlParameter();
        paraID.ParameterName = "@cid";
        paraID.SqlDbType = SqlDbType.NVarChar;
        paraID.Direction = ParameterDirection.Input;
        paraID.Value = txtID.Text.Trim();
        cmd.Parameters.Add(paraID);

        SqlParameter paraName = new SqlParameter();
        paraName.ParameterName = "@cname";
        paraName.SqlDbType = SqlDbType.NVarChar;
        paraName.Direction = ParameterDirection.Input;
        paraName.Value = txtName.Text.Trim();
        cmd.Parameters.Add(paraName);

        int num = -2;

        try
        {
            con.Open();

            cmd.ExecuteNonQuery();

            Response.Write("<script>alert('" + paraNum.Value.ToString() + "')</script>");

            num = Convert.ToInt16(paraNum.Value);
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
        finally
        {
            con.Close();
        }

        if (num == 0)
        {
            Response.Write("<script>alert('没有对应的数据。')</script>");
        }
        else
        {
            Response.Write("<script>alert('包含对应的数据。')</script>");
        }
    }
    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {

    }
}