1.基本連接
Ado.net默認(rèn)是啟用連接池的
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name ="connStr" connectionString ="Server=127.0.0.1;Database=SMDB;Uid=sa;pwd=shangxi"/>
</connectionStrings>
</configuration>
-
Program.cs這種連接不安全，所以使用上下文管理器
static void Main(string[] args)
{
//1.獲取連接字符串,注意這里的connStr要和配置里面的name一致尊浓！
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//2.創(chuàng)建連接
SqlConnection conn = new SqlConnection(connStr);
//3.打開連接
conn.Open();
//4.關(guān)閉連接
conn.Close();
}
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace ado2
{
class Program
{
static void Main(string[] args)
{
//1.獲取連接字符串,注意這里的connStr要和配置里面的name一致焦除！
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
try
{
//2.創(chuàng)建連接,上下文管理器贬派，自動回收
using (SqlConnection conn = new SqlConnection(connStr))
{
//3.打開連接
conn.Open();
}
}
catch (SqlException ex)
{
throw;
}
}
}
}
- 注意：使用ConfigurationManager需要添加引用
2.常用對象
2.1 SqlCommand對象
SqlCommand是執(zhí)行數(shù)據(jù)庫命令的對象
重要屬性
-
Connection：是SqlCommand對象使用的鏈接對象 -
CommandText:獲取或者設(shè)置執(zhí)行的T-sql語句 -
CommandType:CommandType.Text表示執(zhí)行的是一個(gè)T-sql語句绊寻；CommandType.StoreProcedure量愧，是存儲過程 -
Parameters：SqlCommand對象的參數(shù)命令集合蔓纠，默認(rèn)是空集合 -
Transcation:獲取或設(shè)置要執(zhí)行的事務(wù)
創(chuàng)建SqlCommand對象
一般使用這種方式
static void Main(string[] args)
{
//1.獲取連接字符串,注意這里的connStr要和配置里面的name一致！
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
try
{
//2.創(chuàng)建連接,上下文管理器，自動回收
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "delete * from ScoreList where id=1";
//3.創(chuàng)建執(zhí)行命令的SqlCommand對象
SqlCommand cmd = new SqlCommand(sql, conn);
//4.默認(rèn)是關(guān)閉的布朦，所以在這里打開
conn.Open();
//5.執(zhí)行，增刪改
cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw;
}
}
- 方法二：了解
static void Main(string[] args)
{
//1.獲取連接字符串,注意這里的connStr要和配置里面的name一致昼窗！
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
try
{
//2.創(chuàng)建連接,上下文管理器是趴，自動回收
using (SqlConnection conn = new SqlConnection(connStr))
{
//3.實(shí)例化SqlCommand是執(zhí)行數(shù)據(jù)庫命令的對象！
SqlCommand cmd = new SqlCommand();
//4.連接數(shù)據(jù)庫
cmd.Connection = conn;
string sql = "select * from ScoreList";
//5.執(zhí)行sql語句
cmd.CommandText = sql;
//cmd.CommandText = CommandType.Text; 如果執(zhí)行sql不用設(shè)置澄惊；
//6.執(zhí)行操作
cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw;
}
}
注意：conn默認(rèn)是關(guān)閉狀態(tài)唆途，所以每次使用using的時(shí)候需要打開conn.Open()
2.2 ExecuteNonQuery()執(zhí)行插入，更新掸驱，刪除
- 返回的是受影響的行數(shù)
string sql = "delete * from ScoreList where Id=1";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
//返回受影響的行數(shù)：主要是插入肛搬，更新，刪除
int count = cmd.ExecuteNonQuery();
2.3 ExecuteScalar()執(zhí)行查詢
返回第一行毕贼，第一列的對像
static void Main(string[] args)
{
object obj1 = null;
//1.獲取連接字符串,注意這里的connStr要和配置里面的name一致温赔！
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
try
{
//2.創(chuàng)建連接,上下文管理器，自動回收
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select count(1) from ScoreList";
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
//3.執(zhí)行查詢
obj1 = cmd.ExecuteScalar();
}
if (obj1 !=null)
{
Console.WriteLine(Convert.ToInt32(obj1));
}
Console.ReadKey();
}
catch (SqlException ex)
{
throw;
}
}
2.4 ExecuteReader()執(zhí)行查詢
返回一個(gè)SqlDataReader對象結(jié)果集鬼癣，這個(gè)對象用于查詢少量數(shù)據(jù)陶贼，實(shí)時(shí)讀取，類似于游標(biāo)和指針待秃，讀取方式固定拜秧，只能前進(jìn)不能后退的數(shù)據(jù)流
//1.獲取連接字符串,注意這里的connStr要和配置里面的name一致！
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
try
{
//2.創(chuàng)建連接,上下文管理器章郁，自動回收
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select count(1) from ScoreList";
SqlCommand cmd = new SqlCommand(sql, conn);
//讀取對象的時(shí)候必須是連接是Open枉氮，所以
conn.Open();
//3.執(zhí)行查詢
objReader = cmd.ExecuteReader();
if (objReader != null)
{
//處理數(shù)據(jù)
while (objReader.Read())
{
//獲取查詢的數(shù)據(jù)
}
}
Console.ReadKey();
}
}
catch (SqlException ex)
{
throw;
}
注意：可以看出來，這個(gè)查詢操作的時(shí)候暖庄，必須保持conn是打開的聊替，所以在后 面的學(xué)習(xí)中，不能用using所以對于很大的數(shù)據(jù)查詢雄驹，他會一直占用數(shù)據(jù)庫佃牛，所以比較適合少量查詢
2.2 SqlParamter對象
添加單個(gè)參數(shù)
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from Books where Id =@Id";
SqlCommand cmd = new SqlCommand(sql, conn);
//添加單個(gè)參數(shù)
cmd.Parameters.AddWithValue("@Id", 1);
}
- 添加多個(gè)參數(shù)
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from Books where Id =@Id and bookName=@bookName";
SqlCommand cmd = new SqlCommand(sql, conn);
//添加多個(gè)參數(shù)
SqlParameter[] pars = {
new SqlParameter("@Id",1),
new SqlParameter("@bookName","fxx"),
};
cmd.Parameters.AddRange(pars);
}
2.3 DataTable，DataSet
DataSet是數(shù)據(jù)在內(nèi)存中的緩存医舆，DataTable是內(nèi)存中的一個(gè)表俘侠，一般作為DataSet的成員使用，結(jié)合DataAdapter使用蔬将，①DataAdapter將數(shù)據(jù)填充到DataSet中②DataAdapter將DataSet中更改提交到數(shù)據(jù)庫③XML文檔或者文本加載到DataSet中爷速。
DataSet將數(shù)據(jù)加載到內(nèi)存中執(zhí)行，提高了數(shù)據(jù)的訪問速度霞怀，提高硬盤數(shù)據(jù)的安全性惫东，不依賴任何數(shù)據(jù)庫
2.4 SqlDataAdapter
SqlDataAdapter是DataSet和SQLServer之間的橋接器，
使用Fill()方法將數(shù)據(jù)填充到DataSet里。
查詢
- 單一結(jié)果集廉沮，直接填充DataTable
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from Books";
//1.創(chuàng)建adpater
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
//2.創(chuàng)建table颓遏，用于單結(jié)果集
DataTable dt = new DataTable();
//3.填充dt
da.Fill(dt);
}
- 多結(jié)果集，填充DataSet
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from Books;select * from Readers";
//1.創(chuàng)建adpater
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
//2.創(chuàng)建ds,用于多結(jié)果集
DataSet ds = new DataSet();
da.Fill(ds);
}
SqlHelper封裝
- SqlCommand方法（conn必須打開狀態(tài)）
ExecuteNoneQuery() 執(zhí)行 增滞时，刪叁幢，改
ExecuteScalar()返回結(jié)果集的第一行，第一列的值
ExecuteReader() 坪稽，生成SqlDataReader
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Threading;
using System.Data.Common;
namespace SqlConsole
{
public class SqlHelper
{
//鏈接字符串
private static readonly string ConnStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
/// <summary>
/// 增曼玩，刪，改
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType">1sql2存儲過程</param>
/// <param name="paras"></param>
/// <returns>返回受影響的行數(shù)</returns>
public static int ExecuteNoneQuery(string sql, int cmdType, params SqlParameter[] paras)
{
int count = 0;
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
//如果是2,執(zhí)行存儲過程
if (cmdType==2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras!=null && paras.Length>0)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
count = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
return count;
}
/// <summary>
/// 返回第一行窒百，第一列的對象
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, int cmdType, params SqlParameter[] paras)
{
object obj = null;
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
//如果是2,執(zhí)行存儲過程
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
conn.Close();
}
return obj;
}
/// <summary>
/// 返回SqlDataReader
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="paras"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static SqlDataReader ExecuteReader(string sql, int cmdType, params SqlParameter[] paras)
{
SqlDataReader objReader = null;
SqlConnection conn = new SqlConnection(ConnStr);
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
try
{
conn.Open();
objReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
}
catch (SqlException ex)
{
conn.Close();
throw new Exception("執(zhí)行ExecuteReader異常",ex);
}
return objReader;
}
/// <summary>
/// 多個(gè)結(jié)果集
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql, int cmdType, params SqlParameter[] paras)
{
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sql,conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
da.Fill(ds);
conn.Close();
}
return ds;
}
/// <summary>
/// 一個(gè)結(jié)果集
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataTable GetDataTable(string sql, int cmdType, params SqlParameter[] paras)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
da.Fill(dt);
conn.Close();
}
return dt;
}
/// <summary>
/// 增 刪 改 事務(wù)——一系列的Sql語句
/// </summary>
/// <param name="listSql"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static bool ExecuteTrans(List<string> listSql)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
try
{
for (int i = 0; i < listSql.Count; i++)
{
cmd.CommandText = listSql[i];
cmd.ExecuteNonQuery();
}
trans.Commit();//全部成功黍判，提交
return true;
}
catch (SqlException ex)
{
trans.Rollback();//回滾
throw new Exception("執(zhí)行事務(wù)出錯(cuò)", ex);
}
}
}
//帶參數(shù)的事務(wù)（待補(bǔ)充）
}
}
