您好,登錄后才能下訂單哦!
利用ASP.NET怎么對(duì)SQLServer數(shù)據(jù)庫(kù)進(jìn)行訪問(wèn)?很多新手對(duì)此不是很清楚,為了幫助大家解決這個(gè)難題,下面小編將為大家詳細(xì)講解,有這方面需求的人可以來(lái)學(xué)習(xí)下,希望你能有所收獲。
實(shí)現(xiàn)方法如下:
/// <summary> /// 數(shù)據(jù)庫(kù)訪問(wèn)通用類 /// </summary> public class SqlHelper { private string connectionString; /// <summary> /// 設(shè)定數(shù)據(jù)庫(kù)訪問(wèn)字符串 /// </summary> public string ConnectionString { set { connectionString = value; } } /// <summary> /// 構(gòu)造函數(shù) /// </summary> /// <param name="connectionString">數(shù)據(jù)庫(kù)訪問(wèn)字符串</param> public SqlHelper(string connectionString) { this.connectionString = connectionString; } /// <summary> /// 執(zhí)行一個(gè)查詢,并返回查詢結(jié)果 /// </summary> /// <param name="sql">要執(zhí)行的sql語(yǔ)句</param> /// <param name="commandType">要執(zhí)行的查詢語(yǔ)句的類型,如存儲(chǔ)過(guò)程或者sql文本命令</param> /// <returns>返回查詢結(jié)果集</returns> public DataTable ExecuteDataTable(string sql,CommandType commandType) { return ExecuteDataTable(sql, commandType, null); } /// <summary> /// 執(zhí)行一個(gè)查詢,并返回結(jié)果集 /// </summary> /// <param name="sql">要執(zhí)行的sql文本命令</param> /// <returns>返回查詢的結(jié)果集</returns> public DataTable ExecuteDataTable(string sql) { return ExecuteDataTable(sql, CommandType.Text, null); } /// <summary> /// 執(zhí)行一個(gè)查詢,并返回查詢結(jié)果 /// </summary> /// <param name="sql">要執(zhí)行的sql語(yǔ)句</param> /// <param name="commandtype">要執(zhí)行查詢語(yǔ)句的類型,如存儲(chǔ)過(guò)程或者sql文本命令</param> /// <param name="parameters">Transact-SQL語(yǔ)句或者存儲(chǔ)過(guò)程參數(shù)數(shù)組</param> /// <returns></returns> public DataTable ExecuteDataTable(string sql, CommandType commandtype, SqlParameter[] parameters) { DataTable data = new DataTable(); //實(shí)例化datatable,用于裝載查詢結(jié)果集 using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = commandtype;//設(shè)置command的commandType為指定的Commandtype //如果同時(shí)傳入了參數(shù),則添加這些參數(shù) if (parameters != null) { foreach (SqlParameter parameter in parameters) { cmd.Parameters.Add(parameter); } } //通過(guò)包含查詢sql的sqlcommand實(shí)例來(lái)實(shí)例化sqldataadapter SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(data);//填充datatable } } return data; } /// <summary> /// 返回一個(gè)SqlDataReader對(duì)象的實(shí)例 /// </summary> /// <param name="sql">要執(zhí)行的SQl查詢命令</param> /// <returns></returns> public SqlDataReader ExecuteReader(string sql) { return ExecuteReader(sql, CommandType.Text, null); } /// <summary> /// /// </summary> /// <param name="sql">要執(zhí)行的sql語(yǔ)句</param> /// <param name="commandType">要執(zhí)行查詢語(yǔ)句的類型,如存儲(chǔ)過(guò)程或者SQl文本命令</param> /// <returns></returns> public SqlDataReader ExecuteReader(string sql,CommandType commandType) { return ExecuteReader(sql, commandType, null); } /// <summary> /// 返回一個(gè)sqldatareader對(duì)象的實(shí)例 /// </summary> /// <param name="sql"></param> /// <param name="commandType"></param> /// <param name="parameters"></param> /// <returns></returns> public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(sql, con); if (parameters != null) { foreach (SqlParameter parameter in parameters) { cmd.Parameters.Add(parameters); } } con.Open(); //CommandBehavior.CloseConnection參數(shù)指示關(guān)閉reader對(duì)象時(shí)關(guān)閉與其關(guān)聯(lián)的Connection對(duì)象 return cmd.ExecuteReader(CommandBehavior.CloseConnection); } /// <summary> /// 執(zhí)行一個(gè)查詢,返回結(jié)果集的首行首列。忽略其他行,其他列 /// </summary> /// <param name="sql">要執(zhí)行的SQl命令</param> /// <returns></returns> public Object ExecuteScalar(string sql) { return ExecuteScalar(sql, CommandType.Text, null); } /// <summary> /// /// </summary> /// <param name="sql"></param> /// <param name="commandType"></param> /// <returns></returns> public Object ExecuteScalar(string sql, CommandType commandType) { return ExecuteScalar(sql, commandType, null); } /// <summary> /// /// </summary> /// <param name="sql"></param> /// <param name="commandType">參數(shù)類型</param> /// <param name="parameters"></param> /// <returns></returns> public Object ExecuteScalar(string sql,CommandType commandType, SqlParameter[] parameters) { Object result=null; SqlConnection con=new SqlConnection(connectionString); SqlCommand cmd=new SqlCommand(sql,con); cmd.CommandType= commandType; if(parameters!=null) { foreach (SqlParameter parapmeter in parameters) { cmd.Parameters.Add(parapmeter); } } con.Open(); result=cmd.ExecuteScalar(); con.Close(); return result; } /// <summary> /// 對(duì)數(shù)據(jù)庫(kù)進(jìn)行增刪改的操作 /// </summary> /// <param name="sql">要執(zhí)行的sql命令</param> /// <returns></returns> public int ExecuteNonQuery(string sql) { return ExecuteNonQuery(sql, CommandType.Text, null); } /// <summary> /// 數(shù)據(jù)庫(kù)進(jìn)行增刪改的操作 /// </summary> /// <param name="sql">對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作的sql命令</param> /// <param name="commandType">要執(zhí)行查詢語(yǔ)句的類型,如存儲(chǔ)過(guò)程或者sql文本命令</param> /// <returns></returns> public int ExecuteNonQuery(string sql, CommandType commandType) { return ExecuteNonQuery(sql, commandType, null); } /// <summary> /// 對(duì)數(shù)據(jù)庫(kù)進(jìn)行增刪改的操作 /// </summary> /// <param name="sql">要執(zhí)行的sql語(yǔ)句</param> /// <param name="commandType">要執(zhí)行的查詢語(yǔ)句類型,如存儲(chǔ)過(guò)程或者sql文本命令</param> /// <param name="parameters">Transact-SQL語(yǔ)句或者存儲(chǔ)過(guò)程的參數(shù)數(shù)組</param> /// <returns></returns> public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters) { int count = 0; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(sql, con); cmd.CommandType = commandType; if (parameters != null) { foreach(SqlParameter parameter in parameters) { cmd.Parameters.Add(parameter); } } con.Open(); count = cmd.ExecuteNonQuery(); con.Close(); return count; } /// <summary> /// 返回當(dāng)前連接的數(shù)據(jù)庫(kù)中所有用戶創(chuàng)建的數(shù)據(jù)庫(kù) /// </summary> /// <returns></returns> public DataTable GetTables() { DataTable table = null; using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); table = con.GetSchema("Tables"); } return table; } }
看完上述內(nèi)容是否對(duì)您有幫助呢?如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章,請(qǐng)關(guān)注億速云行業(yè)資訊頻道,感謝您對(duì)億速云的支持。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。