SQL Procedure Operations
时间：2021-07-01 10:21:17
帮助过：2人阅读
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;
}
}
}
SQL Procedure Operations
标签：
