EXEC against SQL server database:

Stored Procedure:


USE [TestDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_Name_1]
  @Param1 INT,
  @Param2 VARCHAR(20),
  @RtnCode INT OUTPUT,
  @RtnMsg VARCHAR(100) OUTPUT
AS
BEGIN

  SET NOCOUNT ON;

  SELECT @RtnCode = 0, @RtnMsg = ''

  IF (@@ERROR <> 0)
    BEGIN
      SELECT @RtnCode = @@ERROR, @RtnMsg = '...'
    END

END

 

Utility:


using System.Data;
using System.Data.SqlClient;

namespace Test.Util
{
  public class Database
  {
    private int _timeout;
    private string _connStr;
    private SqlConnection _sqlConn;
    private SqlDataAdapter _sqlDataAdapter;

    public SqlConnection Connection
    {
      get
      {
        return _sqlConn;
      }
    }

    public Database(string connStr, int timeout = 5)
    {
      _timeout = timeout;
      _connStr = string.Format("{0}; Connection Timeout={1};", connStr, timeout);
      _sqlConn = new SqlConnection(_connStr);
    }

    public DataTable ExecStoredProcedure(string spName, SqlParameter[] paramArray)
    {
      DataTable result;

      try
      {
        Open();

        //#region If input SQL string
        //_sqlDataAdapter = new SqlDataAdapter(sqlStr, _sqlConn);
        //new SqlCommandBuilder(_sqlDataAdapter);
        //#endregion

        #region If input params
        SqlCommand cmd = new SqlCommand();
        cmd.CommandTimeout = (_timeout > 0) ? _timeout : cmd.CommandTimeout;
        cmd.Connection = Connection;
        cmd.CommandText = spName;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(paramArray);
        _sqlDataAdapter = new SqlDataAdapter(cmd);
        #endregion

        DataTable table = new DataTable();
        _sqlDataAdapter.Fill(table);
        result = table;
      }
      catch (SqlException ex)
      {
        throw ex;
      }
      finally
      {
        Close();
      }

      return result;
    }

    private void Open()
    {
      try
      {
        if (_sqlConn.State != ConnectionState.Open)
        {
          _sqlConn.ConnectionString = _connStr;
          _sqlConn.Open();
        }
      }
      catch (SqlException ex)
      {
        throw ex;
      }
    }

    private void Close()
    {
      try
      {
        if (_sqlConn.State == ConnectionState.Open)
        {
          _sqlConn.Close();
        }
      }
      catch (SqlException ex)
      {
        throw ex;
      }
    }
  }
}

 

Test code:


public DataTable Test(int param1, string param2, out int rtnCode, out string rtnMsg)
{
  rtnCode = 0;
  rtnMsg = string.Empty;
  DataTable result = null;

  SqlParameter paramRtnCode = new SqlParameter("@RtnCode", SqlDbType.Int) { Direction = ParameterDirection.Output };
  SqlParameter paramRtnMsg = new SqlParameter("@RtnMsg", SqlDbType.VarChar, 100) { Direction = ParameterDirection.Output };

  SqlParameter[] paramArray = new SqlParameter[]
  {
    new SqlParameter("@Param1", param1),
    new SqlParameter("@Param2", param2),
    paramRtnCode,
    paramRtnMsg
  };

  Database db = new Database(decryptedConnStr);

  try
  {
    result = db.ExecStoredProcedure("SP_Name_1", paramArray);
  }
  catch (Exception ex)
  {
    _logger.Error(ex);
  }

  rtnCode = (int)paramRtnCode.Value;
  rtnMsg = paramRtnMsg.Value as string;

  return result;
}

 

Reference:

https://forums.asp.net/t/1798529.aspx?DataAdapter+vs+SqlCommand+or+DataAdapter+SqlCommand+vs+Impact+on+Performance

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google photo

您的留言將使用 Google 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s