SQL Procedure Operations

最新推荐文章于 2026-08-20 13:01:28 发布

转载最新推荐文章于 2026-08-20 13:01:28 发布 · 196 阅读

本内容遵循CC 4.0 BY-SA版权协议

原文链接：http://www.cnblogs.com/Blackeye286/p/4710150.html

本文介绍了一个用于处理SQL存储过程的实用类SQLHelper，包括检查存储过程是否存在、创建存储过程及通过C#执行存储过程的方法。提供了ProcedureIsExist、CreateProcedure和ExecuteProcedure三个核心功能。

This Class is used to handle SQL procedures, including Verify SP exist or not, Create SP into DB and Execute SP via C#.

using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace SqlBIS.Auto.Framework
{
    public class SqlHelper
    {
        public string ConnString { get; set; }
        public SqlConnection Connection { get; set; }

        public SqlHelper(string connString)
        {
            this.ConnString = connString;
            Connection = new SqlConnection(connString);
        }

        /// <summary>
        /// Verify if DB contain procedure or not
        /// </summary>
        /// <param name="proName">procedure name</param>
        /// <returns></returns>
        public  bool ProcedureIsExist(string proName)
        {
            bool result = false;
            String sqlComm = String.Format("IF OBJECT_ID('{0}') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", proName);
            
            SqlCommand cmd = new SqlCommand(sqlComm, this.Connection);
            cmd.CommandType = CommandType.Text;
            this.Connection.Open();
            result = Convert.ToBoolean((cmd.ExecuteScalar()).ToString());
            this.Connection.Close();

            return result;
        }

        /// <summary>
        /// Create a new procedure
        /// </summary>
        /// <param name="proName"></param>
        /// <param name="proCommand"></param>
        public  void CreateProcedure(string proName, string proCommand)
        {
            SqlCommand cmd = new SqlCommand(proCommand, this.Connection);
            Connection.Open();
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            Connection.Close();
        }

        /// <summary>
        /// Execute procedure and return DataTable
        /// </summary>
        /// <param name="proName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedure(string proName, SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();

            SqlCommand cmd = new SqlCommand(proName, this.Connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            foreach (var item in parameters)
                cmd.Parameters.Add(item);
            SqlDataAdapter sa = new SqlDataAdapter(cmd);
            sa.Fill(dt);

            return dt;
        }
    }
}