using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DBHelper
{
public class SqlHelper
{
private static string connectionstring =ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
public static int ExecuteCommand(string sql)
{
using(SqlConnection conn = new SqlConnection(connectionstring))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
int result = cmd.ExecuteNonQuery();
return result;
}
}
}
public static int ExecuteCommand(string SQL, params SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
using (SqlCommand cmd = new SqlCommand(SQL, conn))
{
cmd.Parameters.AddRange(values);
conn.Open();
int result = cmd.ExecuteNonQuery();
return result;
}
}
}
public static string ReturnStringScalar(string SQL)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
using (SqlCommand cmd = new SqlCommand(SQL, conn))
{
conn.Open();
string result = cmd.ExecuteScalar().ToString();
return result;
}
}
}
public static int GetScalar(string SQL)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
using (SqlCommand cmd = new SqlCommand(SQL, conn))
{
conn.Open();
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
}
}
public static int GetScalar(string sql, params SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(values);
conn.Open();
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
}
}
public static SqlDataReader GetReader(string sql)
{
SqlConnection conn = new SqlConnection(connectionstring);
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
}
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlConnection conn = new SqlConnection(connectionstring);
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(values);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
}
public static DataTable GetDataSet(string sql)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
DataTable dt = new DataTable();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
return dt;
}
}
}
}
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
DataTable dt = new DataTable();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(values);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
return dt;
}
}
}
}
public static bool ExecProc(string strProcName)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strProcName;
conn.Open();
cmd.ExecuteNonQuery();
return true;
}
}
}
public static void ExecProc(string strProcName, SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = strProcName;
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
public static bool ExecTSQL(string[] sql)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
conn.Open();
for (int i = 0; i < sql.Length; i++)
{
if (sql[i] == "" || sql[i] == null) { continue; }
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandText = sql[i];
cmd.ExecuteNonQuery();
}
}
trans.Commit();
return true;
}
catch (Exception)
{
trans.Rollback();
return false;
}
}
}
}
}