/// <summary>
/// Выполить SQL запрос и вернуть DataSet
/// </summary>
/// <param name="_sql">SQL запрос</param>
/// <returns>Исходящий DataSet</returns>
public static DataSet ExecuteSQLRetDS(string _sql)
{
string InitString = "";
if (Config.DBAuth.Login == null || Config.DBAuth.Login=="")
InitString = "Data Source=" + Config.DBAuth.URL + ";Initial Catalog=" + Config.DBAuth.AuthToken + ";Trusted_Connection=true;Connect Timeout = 120;";
else
InitString = "Data Source=" + Config.DBAuth.URL + ";Initial Catalog=" + Config.DBAuth.AuthToken + ";User Id=" + Config.DBAuth.Login + ";Password="******";Connect Timeout = 120;";
DataSet retval = new DataSet();
using (var con = new SqlConnection(InitString))
{
using (var command = new SqlCommand(_sql, con) { CommandType = CommandType.Text })
{
try {
con.Open();
using (var da = new SqlDataAdapter(command))
{
da.Fill(retval);
}
con.Close();
}
catch (Exception _ex)
{
TLogger.Add("TMSSQLConnector. EXCEPTION: " + _ex.Message + " SQL: " + command.SQLCommandTextDump(), TLogger.MSGType.eEmail);
throw _ex;
}
}
}
return retval;
}
/// <summary>
/// Выполнить SQL процедуру и вернуть первую строку
/// </summary>
/// <param name="_procname">Имя процедуры</param>
/// <param name="_param">Массив параметров</param>
/// <returns>Результат в виде строки</returns>
public static string ExecuteProcRetString(string _procname, List<SqlParameter> _param = null)
{
string InitString = "";
if (Config.DBAuth.Login == null || Config.DBAuth.Login == "")
InitString = "Data Source=" + Config.DBAuth.URL + ";Initial Catalog=" + Config.DBAuth.AuthToken + ";Trusted_Connection=true;Connect Timeout = 120;";
else
InitString = "Data Source=" + Config.DBAuth.URL + ";Initial Catalog=" + Config.DBAuth.AuthToken + ";User Id=" + Config.DBAuth.Login + ";Password="******";Connect Timeout = 120;";
string retval = "";
using (var con = new SqlConnection(InitString))
{
using (var command = new SqlCommand(_procname, con) { CommandType = CommandType.StoredProcedure })
{
if (_param != null)
{
foreach (var val in _param)
{
command.Parameters.Add(val);
}
}
try
{
con.Open();
object ret = command.ExecuteScalar();
if (ret != null && ret != DBNull.Value)
retval = ret.ToString();
con.Close();
}
catch (Exception _ex)
{
TLogger.Add("TMSSQLConnector. EXCEPTION: " + _ex.Message + " SQL: " + command.SQLCommandTextDump(), TLogger.MSGType.eEmail);
throw _ex;
}
}
}
return retval;
}
public static int ExecuteSQLCommand(SqlCommand _cmd)
{
string InitString = "";
if (Config.DBAuth.Login == null || Config.DBAuth.Login == "")
InitString = "Data Source=" + Config.DBAuth.URL + ";Initial Catalog=" + Config.DBAuth.AuthToken + ";Trusted_Connection=true;Connect Timeout = 120;";
else
InitString = "Data Source=" + Config.DBAuth.URL + ";Initial Catalog=" + Config.DBAuth.AuthToken + ";User Id=" + Config.DBAuth.Login + ";Password="******";Connect Timeout = 120;";
int retval = -1;
using (var con = new SqlConnection(InitString))
{
_cmd.Connection = con;
try {
con.Open();
retval = int.Parse(_cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception _ex)
{
TLogger.Add("TMSSQLConnector. EXCEPTION: " + _ex.Message + " SQL: " + _cmd.SQLCommandTextDump(), TLogger.MSGType.eEmail);
throw _ex;
}
}
return retval;
}
/// <summary>
/// Выполнить SQL процедуру и вернуть DataSet
/// </summary>
/// <param name="_procname">Имя процедуры</param>
/// <param name="_param">Массив параметров</param>
/// <returns>Исходящий DataSet</returns>
public static DataSet ExecuteProcRetDS(string _procname, List<SqlParameter> _param = null)
{
string InitString = "";
if (Config.DBAuth.Login == null || Config.DBAuth.Login == "")
InitString = "Data Source=" + Config.DBAuth.URL + ";Initial Catalog=" + Config.DBAuth.AuthToken + ";Trusted_Connection=true;Connect Timeout = 120;";
else
InitString = "Data Source=" + Config.DBAuth.URL + ";Initial Catalog=" + Config.DBAuth.AuthToken + ";User Id=" + Config.DBAuth.Login + ";Password="******";Connect Timeout = 120;";
DataSet retval = new DataSet();
using (var con = new SqlConnection(InitString))
{
using (var command = new SqlCommand(_procname, con) {CommandType = CommandType.StoredProcedure})
{
if (_param!=null && _param.Count>0)
{
foreach (var val in _param)
{
command.Parameters.Add(val);
}
}
try {
con.Open();
using (var da = new SqlDataAdapter(command))
{
da.Fill(retval);
}
con.Close();
}
catch (Exception _ex)
{
TLogger.Add("TMSSQLConnector. EXCEPTION: " + _ex.Message + " SQL: " + command.SQLCommandTextDump(), TLogger.MSGType.eEmail);
throw _ex;
}
}
}
return retval;
}