1、 ADO.NET简介
ADO.NET是.NET Framework中不可缺少的一部分，它是一组类，通过这些类，我们的.NET应用程序就可以访问数据库了。ADO.NET的功能非常强大，它提供了对关系数据库、XML以及其他数据储存的访问，我们的应用程序可以通过ADO.NET连接到这些数据源，对数据进行增删改查。
.NET Framework数据库提供程序的4个核心对象
Connection：建立于特定数据源的连接。
Command：对数据源执行命令。
DataReader：从数据源中读取只进且只读的数据流。
DataAdapter：用数据源填充DataSet并解析更新。
1.1 认识Connection对象
有了Connection对象，我们的应用程序就能够连接到数据库了。
Connection对象的主要属性和方法
ConnectionString属性：用于连接数据库的连接字符串。
Open方法：使用ConnectionString属性所指定的设置打开数据库连接。
Close方法：关闭与数据库的连接。
1.2 认识Command对象
Command对象的主要属性
Connection属性：Command对象使用的数据库连接。
CommandText属性：执行的SQL语句。
Command对象的主要方法
ExecuteNonQuery方法：执行不返回行的语句，如UPDATE等。
ExecuteReader方法：执行查询命令，返回DataReader对象。
ExecuteScalar方法：返回单个值，如执行COUNT(*)。
2、 ADO.NET使用
2.1 配置数据库连接字符串
数据库连接属于站点级的配置，所以我们一般将其储存在web.config文件中。
<connectionStrings>
<add name="MyDB" connectionString="Data Source=.;Initial Catalog=MyStudy;User ID=sa;pwd=123456" providerName="System.Data.SqlClient"/>
</connectionStrings>2.2 创建DBHelper.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MyStudy.DAL
{
public static class DBHelper
{
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
#region 执行SQL语句
/// <summary>
/// 执行无参数的SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommand(string sql)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
int result = cmd.ExecuteNonQuery();
connection.Close();
return result;
}
/// <summary>
/// 执行有参数的SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="values">参数集合</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
connection.Close();
return result;
}
/// <summary>
/// 返回单个值无参数的SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回受SQL语句查询的行数</returns>
public static int GetScalar(string sql)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
return result;
}
/// <summary>
/// 返回单个值有参数的SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>返回受SQL语句查询的行数</returns>
public static int GetScalar(string sql, params SqlParameter[] parameters)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(parameters);
int result = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
return result;
}
/// <summary>
/// 执行查询无参数SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回数据集</returns>
public static DataSet GetReader(string sql)
{
SqlDataAdapter da = new SqlDataAdapter(sql, Connection);
DataSet ds = new DataSet();
da.Fill(ds);
connection.Close();
return ds;
}
/// <summary>
/// 执行查询有参数SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>返回数据集</returns>
public static DataSet GetReader(string sql, params SqlParameter[] parameters)
{
SqlDataAdapter da = new SqlDataAdapter(sql, Connection);
da.SelectCommand.Parameters.AddRange(parameters);
DataSet ds = new DataSet();
da.Fill(ds);
connection.Close();
return ds;
}
#endregion
#region 执行存储过程
/// <summary>
/// 执行无参数的存储过程
/// </summary>
/// <param name="spName">存储过程名</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommandProc(string spName)
{
int result = 0;
SqlCommand cmd = new SqlCommand(spName, Connection);
cmd.CommandType = CommandType.StoredProcedure;
result = cmd.ExecuteNonQuery();
cmd.Clone();
return result;
}
/// <summary>
/// 执行有参数的存储过程
/// </summary>
/// <param name="spName">存储过程名</param>
/// <param name="parameters">参数集合</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommandProc(string spName, params SqlParameter[] parameters)
{
int result = 0;
SqlCommand cmd = new SqlCommand(spName, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
result = cmd.ExecuteNonQuery();
cmd.Clone();
return result;
}
/// <summary>
/// 返回单个值无参数的存储过程
/// </summary>
/// <param name="spName">存储过程名</param>
/// <returns>返回受SQL语句查询的行数</returns>
public static int GetScalarProc(string spName)
{
int result = 0;
SqlCommand cmd = new SqlCommand(spName, Connection);
cmd.CommandType = CommandType.StoredProcedure;
result = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Clone();
return result;
}
/// <summary>
/// 返回单个值有参数的存储过程
/// </summary>
/// <param name="spName">存储过程名</param>
/// <param name="parameters">参数集合</param>
/// <returns>返回受SQL语句查询的行数</returns>
public static int GetScalarProc(string spName, params SqlParameter[] parameters)
{
int result = 0;
SqlCommand cmd = new SqlCommand(spName, Connection);
cmd.Parameters.AddRange(parameters);
cmd.CommandType = CommandType.StoredProcedure;
result = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Clone();
return result;
}
/// <summary>
/// 执行查询无参数存储过程名
/// </summary>
/// <param name="spName">存储过程名</param>
/// <returns>返回数据集</returns>
public static DataSet GetDataSetProc(string spName)
{
SqlDataAdapter da = new SqlDataAdapter(spName, Connection);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
/// <summary>
/// 执行查询有参数存储过程名
/// </summary>
/// <param name="spName">存储过程名</param>
/// <param name="parameters">参数集合</param>
/// <returns>返回数据集</returns>
public static DataSet GetDataSetProc(string spName, params SqlParameter[] parameters)
{
SqlDataAdapter da = new SqlDataAdapter(spName, Connection);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddRange(parameters);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
#endregion
}
}2.3 使用SQL语句执行操作
/// <summary>
/// 查询用户列表
/// </summary>
public List<UserInfo> GetUserList()
{
List<UserInfo> userList = new List<UserInfo>();
string sql = "SELECT * FROM UserInfo";
DataSet dateSet = DBHelper.GetReader(sql);
userList = TranUserList(dateSet);
return userList;
}
/// <summary>
/// 获取用户总数
/// </summary>
public int GetUserCount()
{
int result = 0;
string sql = "SELECT COUNT(*) FROM UserInfo";
result = DBHelper.GetScalar(sql);
return result;
}
/// <summary>
/// 新增用户信息
/// </summary>
public int AddUserInfo(UserInfo param)
{
int result = 0;
string sql = "INSERT INTO UserInfo(UserName,Age,Address) VALUES(@UserName,@Age,@Address)";
SqlParameter[] sqlParam = new SqlParameter[]
{
new SqlParameter("@UserName", param.UserName),
new SqlParameter("@Age", param.Age),
new SqlParameter("@Address",param.Address)
};
result = DBHelper.ExecuteCommand(sql, sqlParam);
return result;
}
/// <summary>
/// 修改用户信息
/// </summary>
public int UpdateUserInfo(UserInfo param)
{
int result = 0;
if (param.UserId > 0)
{
string sql = "UPDATE UserInfo SET UserName=@UserName,Age=@Age,Address=@Address WHERE UserId=@UserId";
SqlParameter[] sqlParam = new SqlParameter[]
{
new SqlParameter("@UserId",param.UserId),
new SqlParameter("@UserName", param.UserName),
new SqlParameter("@Age", param.Age),
new SqlParameter("@Address",param.Address)
};
result = DBHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
/// <summary>
/// 删除用户信息
/// </summary>
public int DeleteUserInfo(UserInfo param)
{
int result = 0;
if (param.UserId > 0)
{
string sql = "DELETE UserInfo WHERE UserId=@UserId";
SqlParameter[] sqlParam = new SqlParameter[]
{
new SqlParameter("@UserId",param.UserId),
};
result = DBHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}2.4 使用储存过程执行操作
/// <summary>
/// 查询用户列表
/// </summary>
public List<UserInfo> GetUserList()
{
List<UserInfo> userList = new List<UserInfo>();
string spName = "Proc_GetUserList";
DataSet dateSet = DBHelper.GetDataSetProc(spName);
userList = TranUserList(dateSet);
return userList;
}
/// <summary>
/// 获取用户总数
/// </summary>
public int GetUserCount()
{
int result = 0;
string spName = "Proc_GetUserCount";
result = DBHelper.GetScalarProc(spName);
return result;
}
/// <summary>
/// 新增用户信息
/// </summary>
public int AddUserInfo(UserInfo param)
{
int result = 0;
string spName = "Proc_AddUserInfo";
SqlParameter[] sqlParam = new SqlParameter[]
{
new SqlParameter("@UserName", param.UserName),
new SqlParameter("@Age", param.Age),
new SqlParameter("@Address",param.Address)
};
result = DBHelper.ExecuteCommandProc(spName, sqlParam);
return result;
}
/// <summary>
/// 修改用户信息
/// </summary>
public int UpdateUserInfo(UserInfo param)
{
int result = 0;
if (param.UserId > 0)
{
string spName = "Proc_UpdateUserInfo";
SqlParameter[] sqlParam = new SqlParameter[]
{
new SqlParameter("@UserId",param.UserId),
new SqlParameter("@UserName", param.UserName),
new SqlParameter("@Age", param.Age),
new SqlParameter("@Address",param.Address)
};
result = DBHelper.ExecuteCommandProc(spName, sqlParam);
}
return result;
}
/// <summary>
/// 删除用户信息
/// </summary>
public int DeleteUserInfo(UserInfo param)
{
int result = 0;
if (param.UserId > 0)
{
string spName = "Proc_DeleteUserInfo";
SqlParameter[] sqlParam = new SqlParameter[]
{
new SqlParameter("@UserId",param.UserId),
};
result = DBHelper.ExecuteCommandProc(spName, sqlParam);
}
return result;
}将DataSet转换成List
/// <summary>
/// 转换成列表
/// </summary>
public List<UserInfo> TranUserList(DataSet dateSet)
{
List<UserInfo> resultList = new List<UserInfo>();
if (dateSet != null)
{
//遍历DataSet
foreach (DataRow row in dateSet.Tables[0].Rows)
{
UserInfo user = new UserInfo();
if (dateSet.Tables[0].Columns.Contains("UserId") && !Convert.IsDBNull(row["UserId"]))
user.UserId = Convert.ToInt32(row["UserId"]);
if (dateSet.Tables[0].Columns.Contains("UserName") && !Convert.IsDBNull(row["UserName"]))
user.UserName = Convert.ToString(row["UserName"]);
if (dateSet.Tables[0].Columns.Contains("Age") && !Convert.IsDBNull(row["Age"]))
user.Age = Convert.ToInt32(row["Age"]);
if (dateSet.Tables[0].Columns.Contains("Address") && !Convert.IsDBNull(row["Address"]))
user.Address = Convert.ToString(row["Address"]);
resultList.Add(user);
}
}
return resultList;
}UserInfo实体类
public class UserInfo
{
public int UserId { get; set; }
public string UserName { get; set; }
public int Age { get; set; }
public string Address { get; set; }
}2.5 其他
创建存储过程的SQL脚本
/*---创建查询用户存储过程----*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Proc_GetUserList' )
DROP PROCEDURE Proc_GetUserList
GO
CREATE PROCEDURE Proc_GetUserList
AS
SELECT * FROM UserInfo
GO
/*---创建获取用户总数存储过程----*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Proc_GetUserCount' )
DROP PROCEDURE Proc_GetUserCount
GO
CREATE PROCEDURE Proc_GetUserCount
AS
SELECT COUNT(*) FROM UserInfo
GO
/*---创建新增用户存储过程----*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Proc_AddUserInfo' )
DROP PROCEDURE Proc_AddUserInfo
GO
CREATE PROCEDURE Proc_AddUserInfo
@UserName VARCHAR(50),
@Age INT,
@Address VARCHAR(50)
AS
INSERT INTO UserInfo(UserName,Age,Address) VALUES(@UserName,@Age,@Address)
GO
/*---创建修改用户存储过程----*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Proc_UpdateUserInfo' )
DROP PROCEDURE Proc_UpdateUserInfo
GO
CREATE PROCEDURE Proc_UpdateUserInfo
@UserId INT,
@UserName VARCHAR(50),
@Age INT,
@Address VARCHAR(50)
AS
UPDATE UserInfo SET UserName=@UserName,Age=@Age,Address=@Address WHERE UserId=@UserId
GO
/*---创建删除用户存储过程----*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Proc_DeleteUserInfo' )
DROP PROCEDURE Proc_DeleteUserInfo
GO
CREATE PROCEDURE Proc_DeleteUserInfo
@UserId INT
AS
DELETE UserInfo WHERE UserId=@UserId
GO
488
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
