参考http://www.shareblogs.top/517/
一、简介
VS2015 连接数据库,前面已经写了两篇博客,但是这两篇博客并没有实现对数据表的增删改查等功能:
SQL Server基础(七) VS2015 连接数据库——基础入门篇:主要分为配置并加载App.config文件、创建数据工厂对象、创建连接对象、创建命令对象、创建读取器对象几个步骤。
SQL Server基础(八) VS2015 连接数据库——中级篇:System.Data.SqlClient命名空间每个类的使用方法。
下面的这篇博客虽然实现了增删改查等功能,但是其实直接在数据库里面进行操作的,而不是在C#工程里面操作。
SQL Server基础(一) VS2015 建立数据表:建表、修改表名字、往表中添加数据、查找表,最后是数据库文件的属性
那么本篇博客要做的,在C#工程里面操作数据库,如下图所示:
参考书本的章节(书本才是source,好好看书):
二、数据库
数据库有好几个表,我现在只对表Inventory进行操作。关于如何建立数据等等,请参考我的博客:
三、DLL
类库,实际上相对于函数的功能,生成DLL的目的,是为了方便给其他人调用而已。其功能和C++的接口生成了DLL一样。
好了,我们这里建立了这样的类库:
AutoLotComDAL.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace AutoLotConnectedLayer
{
//InventoryDAL = Inventory Database Access Layer
public class InventoryDAL
{
private SqlConnection sqlConnection = null;
/// <summary>
/// 打开连接
/// </summary>
/// <param name="strConnectionString"></param>
public void OpenConnection(string strConnectionString)
{
sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = strConnectionString;
sqlConnection.Open();
}
/// <summary>
/// 关闭连接
/// </summary>
public void CloseConnection()
{
sqlConnection.Close();
}
/// <summary>
/// 传输对象car的方式,向表Inventory 插入一条记录
/// </summary>
/// <param name="car"></param>
public void InsertAuto(NewCar car)
{
//格式化并执行SQL语句
string strSql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) Values" + "('{0}', '{1}', '{2}' ,'{3}')", car.CarID, car.Make, car.Color, car.PetName);
using (SqlCommand sqlCommand = new SqlCommand(strSql, this.sqlConnection))
{
sqlCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 传输int/string的方式,向表Inventory 插入一条记录
/// </summary>
/// <param name="id"></param>
/// <param name="color"></param>
/// <param name="make"></param>
/// <param name="petName"></param>
public void InsertAuto1(int id, string color, string make, string petName)
{
//格式化并执行SQL语句
string strSql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) Values" + "('{0}', '{1}', '{2}' ,'{3}')", id, make, color, petName);
using (SqlCommand sqlCommand = new SqlCommand(strSql, this.sqlConnection))
{
sqlCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 参数化命令的方法,向表Inventory插入一条记录.该方法可以解决SQL注入攻击的安全性问题。
/// </summary>
/// <param name="id"></param>
/// <param name="color"></param>
/// <param name="make"></param>
/// <param name="petName"></param>
public void InsertAuto2(int id, string color, string make, string petName)
{
//格式化并执行SQL语句
string strSql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) Value" + "(@CarID, @Make, @Color, @PetName)");
using (SqlCommand sqlCommand = new SqlCommand(strSql, this.sqlConnection))
{
//填充参数集合
SqlParameter param = null;
param = new SqlParameter();
param.ParameterName = "@CarID"; //参数名称
param.Value = id; //参数值
param.SqlDbType = SqlDbType.Int; //参数类型
sqlCommand.Parameters.Add(param);//向数据表Inventory的CarID那一列,插入一个 param.SqlDbTyp类型(比如int)的汽车id值。
param = new SqlParameter();
param.ParameterName = "@Color"; //参数名称
param.Value = color; //参数值
param.SqlDbType = SqlDbType.Char;//参数类型
sqlCommand.Parameters.Add(param);//向数据表Inventory的CarID那一列,插入一个 param.SqlDbTyp类型(比如int)的汽车id值。
param = new SqlParameter();
param.ParameterName = "@Make"; //参数名称
param.Value = make; //参数值
param.SqlDbType = SqlDbType.Char;//参数类型
sqlCommand.Parameters.Add(param);//向数据表Inventory的CarID那一列,插入一个 param.SqlDbTyp类型(比如int)的汽车id值。
param = new SqlParameter();
param.ParameterName = "@PetName";//参数名称
param.Value = petName; //参数值
param.SqlDbType = SqlDbType.Char;//参数类型
sqlCommand.Parameters.Add(param);//向数据表Inventory的CarID那一列,插入一个 param.SqlDbTyp类型(比如int)的汽车id值。
sqlCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 删除Inventory的一条记录
/// </summary>
/// <param name="id"></param>
public void DeleteCar(int id)
{
//获取要删除的汽车ID,然后删除
string strSql = string.Format("Delete from Inventory where CarID = '{0}'", id);
using (SqlCommand sqlCommand = new SqlCommand(strSql, this.sqlConnection))
{
try
{
sqlCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
Exception error = new Exception("Sorry! That car is on order", ex);
throw error;
}
}
}
/// <summary>
/// 更改汽车的昵称,更改之前必须获取汽车的ID。不然不知道要改变哪俩汽车的昵称。
/// </summary>
/// <param name="id"></param>
/// <param name="newPetName"></param>
public void UpdateCarPetName(int id, string newPetName)
{
string strSql = string.Format("Update Inventory Set PetName = '{0}' where CarId = '{1}'", newPetName, id);
using (SqlCommand sqlCommand = new SqlCommand(strSql, this.sqlConnection))
{
sqlCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 返回Inventory表的集合
/// </summary>
/// <returns></returns>
public List<NewCar> GetAllInventoryAsList()
{
//保存数据表的集合
List<NewCar> listNewCar = new List<NewCar>();
//准备命令对象
string strSql = "Select * from Inventory";
using (SqlCommand sqlCommand = new SqlCommand(strSql, this.sqlConnection))
{
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
//加载数据
while (sqlDataReader.Read())
{
listNewCar.Add(new NewCar
{
CarID = (int)sqlDataReader["CarID"],
Color = (string)sqlDataReader["Color"],
Make = (string)sqlDataReader["Make"],
PetName = (string)sqlDataReader["PetName"],
});
}
sqlDataReader.Close();
}
return listNewCar;
}
/// <summary>
/// 返回Inventory表的集合
/// </summary>
/// <returns></returns>
public DataTable GetAllInventoryAsDataTable()
{
//保存数据表的集合
DataTable dataTableNewCar = new DataTable();
//准备命令对象
string strSql = "Select * from Inventory";
using (SqlCommand sqlCommand = new SqlCommand(strSql, this.sqlConnection))
{
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
//加载数据
dataTableNewCar.Load(sqlDataReader);
sqlDataReader.Close();
}
return dataTableNewCar;
}
/// <summary>
/// 调用数据库的存储过程
/// </summary>
/// <param name="carID"></param>
/// <returns></returns>
public string LookUpPetName(int carID)
{
string carPetName = null;
using (SqlCommand sqlCommand = new SqlCommand("GetPetName", this.sqlConnection))
{
//定义sqlCommand指令类型为存储过程
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter param = null;
//输入参数
param = new SqlParameter();
param.ParameterName = "@CarID"; //参数名称
param.Value = carID; //参数值
param.SqlDbType = SqlDbType.Int; //参数类型
param.Direction = ParameterDirection.Input; //参数方向:输入(默认是输入)。
sqlCommand.Parameters.Add(param); //索引汽车id值。
//输出参数
param = new SqlParameter();
param.ParameterName = "@PetName"; //参数名称
param.SqlDbType = SqlDbType.Char; //参数类型
param.Size = 10; //参数类型
param.Direction = ParameterDirection.Output; //参数方向:输出。
sqlCommand.Parameters.Add(param); //索引汽车id值后,输出该id对应的PetName
sqlCommand.ExecuteNonQuery();
carPetName = (string)sqlCommand.Parameters["@petName"].Value;
}
return carPetName;
}
//static void Main(string[] args)
//{
//}
}
}
NewCar.cs(用来插入一个汽车对象等等):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AutoLotConnectedLayer
{
public class NewCar
{
public int CarID { get; set; }
public string Color { get; set; }
public string Make { get; set; }
public string PetName { get; set; }
}
}
自己生成DLL即可。
四、C#工程调用DLL操作数据库
添加好命名空间即可。
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AutoLotConnectedLayer;
//using System.Data;
using System.Configuration;
using System.Data;
namespace AutoLotGUIclient
{
class Program
{
static void Main(string[] args)
{
string cnStr = ConfigurationManager.ConnectionStrings["AutoLotSqlProvider"].ConnectionString;
bool userDone = false;
string userCommand = "";
//创建InventoryDAL对象。
InventoryDAL invDAL = new InventoryDAL();
//打开数据连接
invDAL.OpenConnection(cnStr);
try
{
ShowInstructions();
do
{
Console.Write("\nPlease enter your command: ");
//输入指令
userCommand = Console.ReadLine();
Console.WriteLine("你刚才输入的字母已经被转为大写:{0}", userCommand);
Console.WriteLine();
switch (userCommand.ToUpper())
{
case "I":
InsertNewCar(invDAL);
break;
case "U":
UpdateCarPetName(invDAL);
break;
case "D":
DeleteCar(invDAL);
break;
case "L":
// ListInventory(invDAL);
ListInventoryViaList(invDAL);
break;
case "S":
ShowInstructions();
break;
case "P":
LookUpPetName(invDAL);
break;
case "Q":
userDone = true;
break;
default:
Console.WriteLine("Bad data! Try again");
break;
}
} while (!userDone);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
invDAL.CloseConnection();
}
}
#region Show instructions
private static void ShowInstructions()
{
Console.WriteLine("I: Inserts a new car.");
Console.WriteLine("U: Updates an existing car.");
Console.WriteLine("D: Deletes an existing car.");
Console.WriteLine("L: Lists current inventory.");
Console.WriteLine("S: Shows these instructions.");
Console.WriteLine("P: Looks up pet name.");
Console.WriteLine("Q: Quits program.");
}
#endregion
#region List inventory
private static void ListInventory(InventoryDAL invDAL)
{
// Get the list of inventory.
DataTable dt = invDAL.GetAllInventoryAsDataTable();
DisplayTable(dt);
}
private static void ListInventoryViaList(InventoryDAL invDAL)
{
// Get the list of inventory.
List<NewCar> record = invDAL.GetAllInventoryAsList();
foreach (NewCar c in record)
{
Console.WriteLine("CarID: {0}, Make: {1}, Color: {2}, PetName: {3}",
c.CarID, c.Make, c.Color, c.PetName);
}
}
private static void DisplayTable(DataTable dt)
{
// Print out the column names.
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Columns[curCol].ColumnName + "\t");
}
Console.WriteLine("\n----------------------------------");
// Print the DataTable.
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Rows[curRow][curCol].ToString() + "\t");
}
Console.WriteLine();
}
}
#endregion
#region Delete car
private static void DeleteCar(InventoryDAL invDAL)
{
// Get ID of car to delete.
Console.Write("Enter ID of Car to delete: ");
int id = int.Parse(Console.ReadLine());
// Just in case we have a primary key
// violation!
try
{
invDAL.DeleteCar(id);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
#endregion
#region Insert car
private static void InsertNewCar(InventoryDAL invDAL)
{
// First get the user data.
int newCarID;
string newCarColor, newCarMake, newCarPetName;
Console.Write("Enter Car ID: ");
newCarID = int.Parse(Console.ReadLine());
Console.Write("Enter Car Color: ");
newCarColor = Console.ReadLine();
Console.Write("Enter Car Make: ");
newCarMake = Console.ReadLine();
Console.Write("Enter Pet Name: ");
newCarPetName = Console.ReadLine();
// Now pass to data access library.
// invDAL.InsertAuto(newCarID, newCarColor, newCarMake, newCarPetName);
NewCar c = new NewCar
{
CarID = newCarID,
Color = newCarColor,
Make = newCarMake,
PetName = newCarPetName
};
invDAL.InsertAuto(c);
}
#endregion
#region Update pet name
private static void UpdateCarPetName(InventoryDAL invDAL)
{
// First get the user data.
int carID;
string newCarPetName;
Console.Write("Enter Car ID: ");
carID = int.Parse(Console.ReadLine());
Console.Write("Enter New Pet Name: ");
newCarPetName = Console.ReadLine();
// Now pass to data access library.
invDAL.UpdateCarPetName(carID, newCarPetName);
}
#endregion
#region Look up name
private static void LookUpPetName(InventoryDAL invDAL)
{
// Get ID of car to look up.
Console.Write("Enter ID of Car to look up: ");
int id = int.Parse(Console.ReadLine());
Console.WriteLine("Petname of {0} is {1}.",
id, invDAL.LookUpPetName(id).TrimEnd());
}
#endregion
}
}
五、输出结果
我只插入一个行数据到表里面,其他功能请自己演示。
插入前:
运行C#,准备插入:
插入后: