?? sqlhelper.cs
字號:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
/// <summary>
/// SqlHelper 的摘要說明。
/// </summary>
public class SqlHelper
{
public static string conStr = ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;
#region 執行查詢sql
/// <summary>
/// 執行查詢sql
/// </summary>
/// <param name="procname">存儲過程名稱</param>
/// <param name="pars">參數數組</param>
/// <returns>返回DataTable</returns>
public static DataTable search(string procname, SqlParameter[] pars)
{
DataTable dt = new DataTable();
//1.建立一個連接
using (SqlConnection conn = new SqlConnection(conStr))
{
//2.創建cmd對象
SqlCommand cmd = new SqlCommand(procname, conn);
//3.參數加入到cmd
cmd.CommandType = CommandType.StoredProcedure;
if (pars != null)
{
foreach (SqlParameter item in pars)
{
cmd.Parameters.Add(item);
}
}
//4.創建da對象
SqlDataAdapter da = new SqlDataAdapter(cmd);
//5.填充數據集
da.Fill(dt);
}
return dt;
}
#endregion
#region 執行執行添加、修改、刪除sql
/// <summary>
/// 執行執行添加、修改、刪除sql
/// </summary>
/// <param name="procname">存儲過程名稱</param>
/// <param name="pars">參數數組</param>
/// <returns>返回受影響行數</returns>
public static int exec(string procname, SqlParameter[] pars)
{
int rowa = 0;
//1.建立一個連接
using (SqlConnection conn = new SqlConnection(conStr))
{
//2.創建cmd對象
SqlCommand cmd = new SqlCommand(procname, conn);
//3.參數加入到cmd
cmd.CommandType = CommandType.StoredProcedure;
if (pars != null)
{
foreach (SqlParameter item in pars)
{
cmd.Parameters.Add(item);
}
}
//4.開連接
conn.Open();
rowa = cmd.ExecuteNonQuery();
//5.關連接
conn.Close();
// cmd.Dispose();
}
return rowa;
}
#endregion
#region PrepareCommand
/// <summary>
/// 設置Command對象的屬性
/// </summary>
/// <param name="command">命令對象</param>
/// <param name="connection">連接對象</param>
/// <param name="commandType">命令類型(存儲過程、文本等等)</param>
/// <param name="commandText">存儲過程名或T-SQL語句</param>
/// <param name="commandParameters">命令的參數</param>
private static void PrepareCommand(SqlCommand command, SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
if( command == null )
throw new ArgumentNullException( "command" );
if( connection == null)
throw new ArgumentNullException( "connection" );
if( commandText == null || commandText.Length == 0 )
throw new ArgumentNullException( "commandText" );
command.Connection = connection;
command.Parameters.Clear();
command.CommandType = commandType;
command.CommandText = commandText;
if(commandParameters != null)//判斷命令參數
foreach(SqlParameter para in commandParameters)
command.Parameters.Add(para);//添加到命令參數集合
}
#endregion
#region ExecuteNonQuery
/// <summary>
/// 在指定的連接上用指定的參數執行SQL命令(不返回任何行)
/// 一般用語執行執行 UPDATE、INSERT 或 DELETE 語句。
/// </summary>
/// <param name="connectionString">連接字符串</param>
/// <param name="commandType">命令類型(存儲過程、文本等等)</param>
/// <param name="commandText">存儲過程名或T-SQL語句</param>
/// <param name="commandParameters">命令的參數</param></param>
/// <returns>該命令影響的行數</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//留給界面層處理異常 用throw
if( connectionString == null || connectionString.Length == 0 )
throw new ArgumentNullException( "connectionString" );
if( commandText == null || commandText.Length == 0 )
throw new ArgumentNullException( "commandText" );
//節省資源，方法結束后自動釋放
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();//打開數據庫
SqlCommand command = new SqlCommand();
PrepareCommand(command,connection,commandType,commandText,commandParameters);//命令在使用之前的準備工作
int retval = command.ExecuteNonQuery();//retval 命令影響的行數
connection.Close();
return retval;
}
}
#endregion
#region ExecuteReader
/// <summary>
/// 在指定的連接上用指定的參數執行SQL命令
/// </summary>
/// <param name="connectionString">連接字符串</param>
/// <param name="commandType">命令類型(存儲過程、文本等等)</param>
/// <param name="commandText">存儲過程名或T-SQL語句</param>
/// <param name="commandParameters">命令的參數</param>
/// <returns>返回一個SqlDataReader對象</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 )
throw new ArgumentNullException( "connectionString" );
if( commandText == null || commandText.Length == 0 )
throw new ArgumentNullException( "commandText" );
SqlConnection connection = null;
//防止數據庫打開時出現問題 用try｛｝
try
{
connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand();
PrepareCommand(command,connection,commandType,commandText,commandParameters);
SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return dataReader;
}
catch
{
if(connection != null)
connection.Close();
return null;
}
}
#endregion
#region ExecuteScalar
/// <summary>
/// 在指定的連接上用指定的參數執行SQL命令
/// </summary>
/// <param name="connectionString">連接字符串</param>
/// <param name="commandType">命令類型(存儲過程、文本等等)</param>
/// <param name="commandText">存儲過程名或T-SQL語句</param>
/// <param name="commandParameters">命令的參數</param>
/// <returns>返回一個單值</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 )
throw new ArgumentNullException( "connectionString" );
if( commandText == null || commandText.Length == 0 )
throw new ArgumentNullException( "commandText" );
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand();
PrepareCommand(command,connection,commandType,commandText,commandParameters);
object obj = command.ExecuteScalar();
connection.Close();
return obj;
}
}
#endregion
#region FillDataSet
/// <summary>
/// 填充數據集
/// </summary>
/// <param name="connectionString">連接字符串</param>
/// <param name="commandType">命令類型(存儲過程、文本等等)</param>
/// <param name="commandText">存儲過程名或T-SQL語句</param>
/// <param name="dataSet">要填充的數據集</param>
/// <param name="tableNames">數據集中的表名</param>
/// <param name="commandParameters">命令的參數</param>
public static void FillDataSet(string connectionString, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 )
throw new ArgumentNullException( "connectionString" );
if( commandText == null || commandText.Length == 0 )
throw new ArgumentNullException( "commandText" );
if( dataSet == null )
throw new ArgumentNullException( "dataSet" );
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand();
PrepareCommand(command,connection,commandType,commandText,commandParameters);
SqlDataAdapter da = new SqlDataAdapter(command);
//建立映射 （數據對應）if判斷可以省略
if((tableNames != null)&&(tableNames.Length > 0))
{
string srcTableName = "Table";
for(int i =0; i<tableNames.Length; i++)
{
da.TableMappings.Add(srcTableName, tableNames[i]);
srcTableName = "Table" + (i+1).ToString();
}
}
da.Fill(dataSet);
connection.Close();
}
}
#endregion
#region UpdateDataSet
/// <summary>
/// 更新數據集
/// </summary>
/// <param name="insertCommand">插入命令對象</param>
/// <param name="deleteCommand">刪除命令對象</param>
/// <param name="updateCommand">更新命令對象</param>
/// <param name="dataSet">用作更新數據源的數據集</param>
/// <param name="tableName">用作更新數據源的數據表</param>
public static void UpdateDataSet(SqlCommand insertCommand, SqlCommand deleteCommand,
SqlCommand updateCommand, DataSet dataSet, string tableName)
{
if( insertCommand == null ) throw new ArgumentNullException( "insertCommand" );
if( deleteCommand == null ) throw new ArgumentNullException( "deleteCommand" );
if( updateCommand == null ) throw new ArgumentNullException( "updateCommand" );
if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( "tableName" );
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
dataAdapter.Update (dataSet, tableName);
dataSet.AcceptChanges();
}
}
#endregion
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -