首页 » .NET » 入门C#之数据库增删改查I

入门C#之数据库增删改查I

2017-06-26 21:20:05阅读(501)

一.连接数据库

1.打开Visual Studio 2015

创建一个Windows Forms Application,命名为CustomerProject(Lab4),其布局参照下图(Id和Update、Delete是后面再次添加的)


它们所对应的属性如下



2.打开SQL Server 2014

右键Database,新建一个,命名为CustomerDB。

右键CustomerDB里的Tables,新建一个表格。

依次添加几个列,并命名为tblCustomer,如下图所示


CustomerId下方的Column Properties中的Identity Specification改为Yes,如图



3.在Visual Studio 2015创建BusinessLogicCustomer

BusinessLogicCustomer为Class Library,其类更名为BLCustomer,其代码如下

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace BusinessLogicCustomer
{
    public class BLCustomer
    {
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
        public string PhoneNumber { get; set; }
        public string ProductName { get; set; }
        public decimal BillAmount { get; set; }
        public bool Validate()
        {
            if (CustomerName.Length == 0)
            {
                throw new Exception("Customer Name is required");
            }
            if (PhoneNumber.Length == 0)
            {
                throw new Exception("Phone Name is required");
            }
            if (ProductName.Length == 0)
            {
                throw new Exception("Product Name is required");
            }
            if (BillAmount == 0)
            {
                throw new Exception("Bill Amount is required");
            }
            return true;
        }
    }
}

右键Customer里的Reference,在弹出对话框中的Projects里的Solution中添加BusinessLogicCustomer


二.数据库的增加数据

1.在Visual Studio 2015创建Dal

Dal为Class Library,其类更名为DALCustomer和CustomerDal(DALCustomer为数据实体,CustomerDal为数据关系),其代码如下

其中的Add为ADO.NET语法

using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace Dal
{
    public class DALCustomer
    {
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
        public string PhoneNumber { get; set; }
        public string ProductName { get; set; }
        public decimal BillAmount { get; set; }
    }
    public class CustomerDal
    {
        public bool Add(DALCustomer obj)
        {
            //open connection
            try
            {
                string connstr = @"Data Source=ACER-PC\SQLEXPRESS;Initial Catalog=CustomerDB;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();
                //sql--> command
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "insert into tblCustomer " +
                    "(CustomerName,PhoneNumber,ProductName,BIllAmount)" +
                    "values(' " + obj.CustomerName +
                    " ',' " + obj.PhoneNumber + " ',' " + obj.ProductName +
                    " ', " + obj.BillAmount.ToString() + ")";
                command.ExecuteNonQuery();
                //close connection
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}

其中的数据库地址按照如下方法拿

在VS中,View→Server Explorer,右键Data Connections→Add Connection,在Server name里粘贴上(在SQL Server中,Object Explorer下的Connect,选择Database Engine,然后复制Server Name)

选择Windows Authentication

选择CustomerDB,点OK即可

然后去建立好的Data Connections里,右键建立好的数据库,查看其属性

复制其Connection string

粘贴进代码string connstr=后即可,记得加@


右键BusinessLogicCustomer里的Reference,在弹出对话框中的Projects里的Solution中添加Dal

2.在Dal中再添加一个Data,命名为DbCustomer,选择最后一个Code First from database,选择要用的table,同时也会自动生成一个叫tblCustomer的类

安装Entity Framework

Tools→nuget package manager→management nuget packages for solution

选择Entity Framework,然后install,一直点yes即可


3.在BusinessLogicCustomer中再添加一个类,名为CustomerService

其代码如下

其中的Add是连接Dal中的Add和UI

EF_Save为Entity Framework语法

EF_All为Entity Framework语法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dal;
namespace BusinessLogicCustomer
{
   public class CustomerService
    {
        public void Add(BLCustomer customer)
        {
            new CustomerDal().Add(new DALCustomer()
            {
                PhoneNumber = customer.PhoneNumber,
                CustomerName = customer.CustomerName,
                ProductName = customer.ProductName,
                BillAmount = customer.BillAmount
            });
        }
        public void EF_Save(BLCustomer customer)
        {
            using (var context = new DbCustomer())
            {
                context.tblCustomers.Add(new tblCustomer()
                {
                    CustomerName = customer.CustomerName,
                    PhoneNumber = customer.PhoneNumber,
                    ProductName = customer.ProductName,
                    BillAmount = customer.BillAmount
                });
                context.SaveChanges();
            }
        }
        public IEnumerable<BLCustomer> EF_All()
        {
            using (var context = new DbCustomer())
            {
                var list = context.tblCustomers.OrderByDescending(x => x.CustomerId).ToList();
                var result = new List<BLCustomer>();
                foreach (var item in list)
                {
                    result.Add(new BLCustomer()
                    {
                        CustomerName = item.CustomerName,
                        PhoneNumber = item.PhoneNumber,
                        ProductName = item.ProductName,
                        BillAmount = item.BillAmount.Value,
                       CustomerId=item.CustomerId
                    });
                }
                return result;
            }
        }
    }
}
安装Entity Framework


4.创建CustomerUI的Save按钮click事件

其代码如下

Save按钮click事件是调用了Dal的Add函数

using System;
using System.Windows.Forms;
using BusinessLogicCustomer;
namespace CustomerProject_Lab4___
{
    public partial class CustomerUI : Form
    {
        public CustomerUI()
        {
            InitializeComponent();
        }
        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                BLCustomer custobj = new BLCustomer();
                custobj.CustomerName = txtCustomerName.Text;
                custobj.PhoneNumber = txtPhoneNumber.Text;
                custobj.ProductName = txtProduct.Text;
                custobj.BillAmount = Convert.ToDecimal(txtBillAmount.Text);
                var validateResult = custobj.Validate();
                if (!validateResult)
                {
                    MessageBox.Show("validate failed!");
                    return;
                }
                var service = new CustomerService();
                service.Add(custobj);
                dtgCustomers.DataSource = service.EF_All();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}

安装Entity Framework


三.数据库的查询

1.在CustomerUI.cs文件中添加DataGridView控件的语句


2.在CustomerUI.cs文件中添加CustomerUI_Load语句

双击UI的空白处,添加代码


3.CustomerUI.cs文件代码如下

其中LoadGrid函数中调用了Dal的Read函数

using System;
using System.Windows.Forms;
using BusinessLogicCustomer;
using System.Data;
namespace CustomerProject_Lab4___
{
    public partial class CustomerUI : Form
    {
        public CustomerUI()
        {
            InitializeComponent();
        }
        private void LoadGrid()
        {
            CustomerService bl = new CustomerService();
            DataSet customers = bl.Read();
            dtgCustomers.DataSource = customers.Tables[0];
        }
        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                BLCustomer custobj = new BLCustomer();
                custobj.CustomerName = txtCustomerName.Text;
                custobj.PhoneNumber = txtPhoneNumber.Text;
                custobj.ProductName = txtProduct.Text;
                custobj.BillAmount = Convert.ToDecimal(txtBillAmount.Text);
                var validateResult = custobj.Validate();
                if (!validateResult)
                {
                    MessageBox.Show("validate failed!");
                    return;
                }
                var service = new CustomerService();
                service.Add(custobj);
                
                dtgCustomers.DataSource = service.EF_All();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void CustomerUI_Load(object sender, EventArgs e)
        {
            LoadGrid();
        }
    }
}


4.在CustomerService.cs文件里添加bl.Read()函数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dal;
using System.Data;
namespace BusinessLogicCustomer
{
   public class CustomerService
    {
        public void Add(BLCustomer customer)
        {
            new CustomerDal().Add(new DALCustomer()
            {
                PhoneNumber = customer.PhoneNumber,
                CustomerName = customer.CustomerName,
                ProductName = customer.ProductName,
                BillAmount = customer.BillAmount
            });
        }
        public DataSet Read()
        {
            var dal = new CustomerDal();
            return dal.Read();
            
        }
        public void EF_Save(BLCustomer customer)
        {
            using (var context = new DbCustomer())
            {
                context.tblCustomers.Add(new tblCustomer()
                {
                    CustomerName = customer.CustomerName,
                    PhoneNumber = customer.PhoneNumber,
                    ProductName = customer.ProductName,
                    BillAmount = customer.BillAmount
                });
                context.SaveChanges();
            }
        }
        public IEnumerable<BLCustomer> EF_All()
        {
            using (var context = new DbCustomer())
            {
                var list = context.tblCustomers.OrderByDescending(x => x.CustomerId).ToList();
                var result = new List<BLCustomer>();
                foreach (var item in list)
                {
                    result.Add(new BLCustomer()
                    {
                        CustomerName = item.CustomerName,
                        PhoneNumber = item.PhoneNumber,
                        ProductName = item.ProductName,
                        BillAmount = item.BillAmount.Value,
                       CustomerId=item.CustomerId
                    });
                }
                return result;
            }
        }
    }
}


5.在Dal.cs文件里添加Read()函数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace Dal
{
    public class DALCustomer
    {
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
        public string PhoneNumber { get; set; }
        public string ProductName { get; set; }
        public decimal BillAmount { get; set; }
    }
    public class CustomerDal
    {
        public static decimal BillAmount { get; set; }
        public static int CustomerId { get; set; }
        public static string CustomerName { get; set; }
        public static string PhoneNumber { get; set; }
        public static string ProductName { get; set; }
        public bool Add(DALCustomer obj)
        {
            //open connection
            try
            {
                string connstr = @"Data Source=ACER-PC\SQLEXPRESS;Initial Catalog=CustomerDB;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();
                //sql--> command
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "insert into tblCustomer " +
                    "(CustomerName,PhoneNumber,ProductName,BIllAmount)" +
                    "values(' " + obj.CustomerName +
                    " ',' " + obj.PhoneNumber + " ',' " + obj.ProductName +
                    " ', " + obj.BillAmount.ToString() + ")";
                command.ExecuteNonQuery();
                //close connection
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public DataSet Read()
        {
            string connstr = @"Data Source=ACER-PC\SQLEXPRESS;Initial Catalog=CustomerDB;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            //sql--> command
            SqlCommand command = new SqlCommand();
            command.Connection = conn;
            command.CommandText = "select * from tblcustomer";
            
            //Dataset and Dataadapter
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet customers = new DataSet() {};
            adapter.Fill(customers);
            //close connection
            conn.Close();
            using(var c = conn)
            {
            }
            return customers;
        }
    }
} 

其中的*可以根据具体想要显示的列而具体定义,例如

SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "select  CustomerId,CustomerName,PhoneNumber,BillAmount,ProduceName  from tblcustomer";



最新发布

CentOS专题

关于本站

5ibc.net旗下博客站精品博文小部分原创、大部分从互联网收集整理。尊重作者版权、传播精品博文,让更多编程爱好者知晓!

小提示

按 Ctrl+D 键,
把本文加入收藏夹