ADO.NET 資料連接配接查詢

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("新增失敗！");

    }

}

/// 删除資料

public static void Delete()

    SqlConnection conn = new SqlConnection(sqlcon);

    string sqlStr = "delete from book where b_id=1";

    SqlCommand cmd = new SqlCommand(sqlStr,conn);

    if (i > 0)

        Console.Write("删除成功！");

        Console.Write("删除失敗！");

/// 資料更新，軟删除

private static void Update()

    string sqlStr = "update Boook set b_id =2 where b_id=1";

    SqlCommand cmd = new SqlCommand(sqlStr, conn);

        Console.Write("更新成功！");

        Console.Write("更新失敗！");

/// 讀取單個值

private static void selectSingle()

    string sqlStr = "select * from Book";

    object obj = cmd.ExecuteScalar();

    Console.Write(obj.ToString());

/// dateReader讀取資料，逐行讀取，通過下表通路列

private static void dateReader()

    SqlDataReader dr = cmd.ExecuteReader();

    if (dr.HasRows)

        while (dr.Read())//如果讀到下一行資料就傳回True，且本身就屬于那一行資料

        {

            Console.Write(dr[0].ToString() + '_' + dr[1].ToString()+'_'+dr["ID"].ToString());

        }

        Console.Write("無資料");

    dr.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());

public static void QueryListAdapter2()

    SqlDataAdapter da = new SqlDataAdapter(sqlStr, con);

//調用存儲過程查詢資料

public static void QuerListByProc()

    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)

//調用多個參數的存儲過程查詢

private static void QueryListByProc2()

    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數組

    //SqlDataReader dr = cmd.ExecuteReader();

    //while (dr.Read())

    //{

    //    Console.Write("id=" + dr[0].ToString());

    //}

    //dr.Close();

    SqlDataAdapter da = new SqlDataAdapter(cmd);

    foreach (DataRow dr in dt.Rows)

        Console.WriteLine(dr[0].ToString() + "_" + dr[1].ToString());

//調用帶輸出參數的存數過程

private static void QuerListProc3()

    SqlCommand cmd = new SqlCommand("proGetData2", conn);

                             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);

    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，如需轉載請自行聯系原作者