static string? sqlcon = "server=.;database=;Integrated Security=true;";
/// <summary>
/// 新增數據
/// </summary>
public static void Add()
{
??? //string sqlcon = "server=.;database=;Integrated Security=true;";
??? SqlConnection conn=new SqlConnection(sqlcon);????????????
??? string sqlStr="insert into Boook(b_id,b_title,b_author,b_money)values(1,'ASP.NET'，'莫言',100)";
??? SqlCommand cmd=new SqlCommand(sqlStr,conn);
??? conn.Open();
??? int i = cmd.ExecuteNonQuery();
??? conn.Close();
??? if (i>0)
??? {
??????? Console.Write("新增成功！");
??? }?
??? else
??? {
??????? Console.Write("新增失敗！");
??? }
}
/// <summary>
/// 刪除數據
/// </summary>
public static void Delete()
{
??? //string sqlcon = "server=.;database=;Integrated Security=true;";
??? SqlConnection conn = new SqlConnection(sqlcon);
??? string sqlStr = "delete from book where b_id=1";
??? SqlCommand cmd = new SqlCommand(sqlStr,conn);
??? conn.Open();
??? int i = cmd.ExecuteNonQuery();
??? conn.Close();
??? if (i > 0)
??? {
??????? Console.Write("刪除成功！");
??? }
??? else
??? {
??????? Console.Write("刪除失敗！");
??? }
}
/// <summary>
/// 數據更新，軟刪除
/// </summary>
private static void Update()
{
??? //string sqlcon = "server=.;database=;Integrated Security=true;";
??? SqlConnection conn = new SqlConnection(sqlcon);
??? string sqlStr = "update Boook set b_id =2 where b_id=1";
??? SqlCommand cmd = new SqlCommand(sqlStr, conn);
??? conn.Open();
??? int i = cmd.ExecuteNonQuery();
??? conn.Close();
??? if (i > 0)
??? {
??????? Console.Write("更新成功！");
??? }
??? else
??? {
??????? Console.Write("更新失敗！");
??? }
}
/// <summary>
/// 讀取單個值
/// </summary>
private static void selectSingle()
{
??? SqlConnection conn = new SqlConnection(sqlcon);
??? string sqlStr = "select * from Book";
??? SqlCommand cmd = new SqlCommand(sqlStr, conn);
??? conn.Open();
??? object obj = cmd.ExecuteScalar();
??? conn.Close();
??? Console.Write(obj.ToString());
}
/// <summary>
/// dateReader讀取數據，逐行讀取，通過下表訪問列
/// </summary>
private static void dateReader()
{
??? SqlConnection conn = new SqlConnection(sqlcon);
??? string sqlStr = "select * from Book";
??? SqlCommand cmd = new SqlCommand(sqlStr, conn);
??? conn.Open();
??? SqlDataReader dr = cmd.ExecuteReader();
??? if (dr.HasRows)
??? {
??????? while (dr.Read())//如果讀到下一行數據就返回True，且本身就屬于那一行數據
??????? {
??????????? Console.Write(dr[0].ToString() + '_' + dr[1].ToString()+'_'+dr["ID"].ToString());
??????? }
??? }
??? else
??? {
??????? Console.Write("無數據");
??? }
??? dr.Close();
??? conn.Close();
}

//使用適配器填充數據集? SqlDataAdapter不需要手動開關，它能夠自己開關
public static void QueryListAdapter()
{
??? SqlConnection con = new SqlConnection(sqlcon);
??? string sqlStr = "select*from book";
??? SqlDataAdapter da = new SqlDataAdapter(sqlStr,con);
??? DataSet ds = new DataSet();
??? da.Fill(ds);
??? DataTable dt = ds.Tables[0];
??? //循環數據表中的每一行
??? for (int i = 0; i < dt.Rows.Count; i++)
??? {
??????? DataRow dr = dt.Rows[i];//將表中的一行拿出來給行對象
??????? Console.WriteLine(dr[0].ToString() + "_" + dr["ID"].ToString());
??? }
}

//使用適配器填充數據集? SqlDataAdapter不需要手動開關，它能夠自己開關
public static void QueryListAdapter2()
{
??? SqlConnection con = new SqlConnection(sqlcon);
??? string sqlStr = "select*from book";
??? SqlDataAdapter da = new SqlDataAdapter(sqlStr, con);
??? DataSet ds = new DataSet();
??? da.Fill(ds);
??? DataTable dt = ds.Tables[0];
??? //循環數據表中的每一行
??? for (int i = 0; i < dt.Rows.Count; i++)
??? {
??????? DataRow dr = dt.Rows[i];//將表中的一行拿出來給行對象
??????? Console.WriteLine(dr[0].ToString() + "_" + dr["ID"].ToString());
??? }
}
//調用存儲過程查詢數據
public static void QuerListByProc()
{
??? SqlConnection conn = new SqlConnection(sqlcon);
??? SqlCommand cmd=new SqlCommand("usp_GetBookMyCateId",conn);
??? //無參數的存儲過程
??? SqlParameter sp2 = new SqlParameter();
??? sp2.ParameterName = "@cateId";
??? sp2.SqlDbType = SqlDbType.Int;
??? sp2.Value = 2;
??? cmd.Parameters.Add(sp2);
??? //有兩個參數的存儲過程
??? SqlParameter sp = new SqlParameter("@cateId", 2);
??? cmd.Parameters.Add(sp);
??? SqlDataAdapter da = new SqlDataAdapter();
??? DataTable dt = new DataTable();
??? da.Fill(dt);
??? foreach(DataRow dr in dt.Rows)
??? {
??????? Console.WriteLine(dr[0].ToString() + "_" + dr["ID"].ToString());
??? }
}
//調用多個參數的存儲過程查詢
private static void QueryListByProc2()
{
??? SqlConnection conn = new SqlConnection(sqlcon);
??? SqlCommand cmd = new SqlCommand("proGetPageData", conn);
??? cmd.CommandType = CommandType.StoredProcedure;
??? //盡量不要使用兩個參數的存儲過程，類型是枚舉類型，另外一個兩個參數的函數值混淆
??? //SqlParameter par = new SqlParameter("@Id", DbType.Int32);
??? //SqlParameter par = new SqlParameter("@id", 11);
??? //賦值多個參數
??? SqlParameter[] paras ={
???????????????????????????? new SqlParameter("@pageIndex",SqlDbType.Int,4),//這里的4是代表整型的長度
???????????????????????????? new SqlParameter("@pageSize",SqlDbType.Int,4)
???????????????????????? };
??? //cmd.Parameters.AddRange(paras);
??? paras[0].Value = 1;//搜索第一頁
??? paras[1].Value = 2;//賦值的
??? cmd.Parameters.AddRange(paras);//為command對象添加pameters數組
??? conn.Open();
??? //SqlDataReader dr = cmd.ExecuteReader();
??? //while (dr.Read())
??? //{
??? //??? Console.Write("id=" + dr[0].ToString());
??? //}
??? //dr.Close();
??? SqlDataAdapter da = new SqlDataAdapter(cmd);
??? DataTable dt = new DataTable();
??? da.Fill(dt);
??? foreach (DataRow dr in dt.Rows)
??? {
??????? Console.WriteLine(dr[0].ToString() + "_" + dr[1].ToString());
??? }
??? conn.Close();
}

//調用帶輸出參數的存數過程
private static void QuerListProc3()
{
??? SqlConnection conn = new SqlConnection(sqlcon);
??? SqlCommand cmd = new SqlCommand("proGetData2", conn);
??? SqlParameter[] paras ={
???????????????????????????? new SqlParameter("@pageIndex",SqlDbType.Int),
???????????????????????????? new SqlParameter("@pageSize",SqlDbType.Int),
???????????????????????????? new SqlParameter("@pageCount",SqlDbType.Int),
???????????????????????????? new SqlParameter("@rowCount",SqlDbType.Int)????
???????????????????????? };
??? paras[0].Value = 1;
??? paras[1].Value = 2;
??? paras[2].Direction = ParameterDirection.Output;
??? paras[3].Direction = ParameterDirection.Output;//設置參數的輸出方向
??? cmd.Parameters.AddRange(paras);
??? SqlDataAdapter da = new SqlDataAdapter(cmd);
??? DataTable dt = new DataTable();
??? da.Fill(dt);
??? foreach (DataRow dr in dt.Rows)
??? {
??????? Console.WriteLine(dr[0].ToString() + "_" + dr[1].ToString());
??? }
??? int pageCount=Convert.ToInt32(cmd.Parameters[2].Value);
??? int rowCount=Convert.ToInt32(cmd.Parameters[3].Value);//獲取輸出參數
??? Console.WriteLine("pageCount=" + pageCount + ",rowCount=" + rowCount);
}

?

本文轉自蓬萊仙羽51CTO博客，原文鏈接：http://blog.51cto.com/dingxiaowei/1366638，如需轉載請自行聯系原作者

總結

以上是生活随笔為你收集整理的ADO.NET 数据连接查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。