Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 sql server + c# clr  [new]
noTpyJI
Member

Откуда:
Сообщений: 116
задача:

на стороне sql открыта транзакция внутри которой что то происходит.
внутри этой транзакции нужно вызвать clr процедуру на C# которая что то куда то запишет
и в случае отката транзакции, действия которые были выполнены в процессе работы clr функции
не должны откатится.

для теста накидал метод на C#
для теста накидал матод на C#

.........

namespace Future.SQLCLR
{
public class Exception
{
public static string ConnectionString
{
get
{
//string ret = "context connection = true";//
string ret = "Data Source = server; Initial Catalog = database; Integrated Security = True";
return ret;
}
}


[SqlFunction()]
public static void TestInsert(SqlString inputParam)
{
using (var cn = new SqlConnection(ConnectionString))
{
try
{
cn.ConnectionString = "Enlist=false";

cn.Open();

var cmd = new SqlCommand("insert into table(field)" +
"select field from table_1", cn);

cmd.ExecuteNonQuery();
}
catch (System.Exception)
{

throw;
}
finally
{
cn.Close();
}
}
}
}
.......
}


на sql
CREATE PROCEDURE [audit].[TestInsert]
(
@msg nvarchar(2048)
)
AS EXTERNAL NAME CLR.[SQLCLR.Exception].TestInsert

begin tran

exec [TestInsert] 'msg'

rollback


получаю ошибку System.Data.SqlClient.SqlException: Transaction context in use by another session.[url=][/url]
18 апр 16, 16:33    [19073080]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Во-первых, зачем Вы закомментировали
string ret = "context connection = true";
?
Во-вторых, причём здесь
[SqlFunction()]
, если это процедура?
18 апр 16, 16:44    [19073141]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31950
noTpyJI
получаю ошибку System.Data.SqlClient.SqlException: Transaction context in use by another session.[url=][/url]
А что это у вас за странное значение присваивается свойству ConnectionString объекта SqlConnection?
cn.ConnectionString = "Enlist=false";
18 апр 16, 16:53    [19073199]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31950
Jovanny
Во-первых, зачем Вы закомментировали
string ret = "context connection = true";
ТС ведь нужно уйти от текущего коннекта, нужно создать новый.
18 апр 16, 16:53    [19073206]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
mike909
Member

Откуда:
Сообщений: 662
noTpyJI,

В процессе изучения C#, тренируюсь на такой-же задаче.
Пока получилось следующее:
+ LazyWriter
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Threading;
using System.Threading.Tasks;
using System.Xml;
using System.Collections.Concurrent;

public partial class LazyWriter
{
  private static readonly Task<string> TaskConStr = InitConStr();
  public static string ConStrLocal{get {return TaskConStr.Result;}}
  private static Task<string> InitConStr()
  {
    string Res;

    using ( SqlConnection conn = new SqlConnection("context connection=true") )
    {
      conn.Open();

      using (SqlCommand cmd = new SqlCommand(
              "SELECT 'Persist Security Info=False;Integrated Security=true;Enlist=false;" +
              "Initial Catalog='+DB_NAME()+';server=' + @@SERVERNAME + ';'", conn))
      {
        Res = (string)cmd.ExecuteScalar();
      }
    }

    return Task<string>.Factory.StartNew(() => Res);
  }

  static ConcurrentDictionary<string, LWEntry> LWList = new ConcurrentDictionary<string, LWEntry>();

  public sealed class LWEntry
  {
    public readonly Task<DataTable> tdt;
    private object sync = new object();
    private DataTable FBuffTbl = null;
    private CancellationTokenSource LWECT;
    private Int32 FCurCnt = 0;

    public string TableName { get { return tdt.Result.TableName; } }
    public int MaxEmptyLoop = 3;
    public int WriteDelay   = 5000;
    public Int32 CurCnt { get { return FCurCnt; } }

    public LWEntry(string ADstTable)
    {
      //tdt = GetTableSchema(ADstTable, ConStrLocal);
      tdt = new Task<DataTable>(() => GetTableSchema(ADstTable, ConStrLocal).Result);
    }

    public bool CheckState()
    { 
      bool state = (tdt.Status != TaskStatus.Created);
      if (!state)
      {
        lock (sync)
        {
          state = (tdt.Status != TaskStatus.Created);
          if (!state)
          {
            //debug
            SqlContext.Pipe.Send("tdt.Start()");

            tdt.Start();
            state = true;
          }
        }
      }

      return state;
    }

    private async Task<DataTable> GetTableSchema(string ATableName, string AConStr)
    {
      DataTable Res = null;

      using (SqlConnection conn = new SqlConnection(AConStr))
      using (SqlCommand cmd = new SqlCommand("SELECT * FROM " + ATableName, conn))
      {
        await conn.OpenAsync();

        using (SqlDataReader dr = await cmd.ExecuteReaderAsync(CommandBehavior.KeyInfo))
        {
          Res = dr.GetSchemaTable();
          Res.TableName = ATableName;
        }
      }

      LWECT = new CancellationTokenSource();
      var t = Task.Factory.StartNew(() => DoWork());

      return Res;
    }

    public void AddRecords(SqlXml Rows, SqlString RowPath)
    {
      CheckState();

      using (XmlReader xrd = Rows.CreateReader())
      {
        XmlDocument drec = new XmlDocument();
        drec.Load(xrd);

        string rp = (RowPath.IsNull) ? "*/*" : RowPath.Value;

        var ta = Task.Run(() => AddRecords(drec, rp));

        //For debug only
        //ta.Wait();
      }
    }

    private void AddRecords(XmlDocument drec, String RowPath)
    {
      DataTable schema = tdt.Result;

      foreach (XmlNode xr in drec.SelectNodes(RowPath))
      {
        object[] rec = new object[schema.Rows.Count];

        for (int i = 0; i < schema.Rows.Count; i++)
        {
          XmlNode xf = xr.SelectSingleNode(schema.Rows[i]["ColumnName"].ToString());
          if (xf != null)
          {
            rec[i] = xf.InnerText;
          }
        }

        lock (sync)
        {
          if (FBuffTbl == null)
          {
            FBuffTbl = new DataTable(TableName);
            foreach (DataRow row in schema.Rows)
            {
              FBuffTbl.Columns.Add(new DataColumn(row["ColumnName"].ToString(), (Type)row["DataType"]));
            }
          }

          FBuffTbl.Rows.Add(rec);

          FCurCnt++;
        }
      }
    }

    private async Task<int> DoBulk()
    {
      int cnt = 0;

      DataTable DstTbl;
      lock (sync)
      {
        DstTbl = FBuffTbl;
        FBuffTbl = null;
        FCurCnt = 0;
      }

      if (DstTbl == null)
        return cnt;

      cnt = DstTbl.Rows.Count;
      if (cnt != 0)
      {
        using (SqlConnection conn = new SqlConnection(ConStrLocal))
        {
          await conn.OpenAsync();

          SqlBulkCopy sbc = new SqlBulkCopy(conn);
          sbc.DestinationTableName = TableName;

          await sbc.WriteToServerAsync(DstTbl);
        }
      }

      return cnt;
    }

    private async Task DoWork()
    {
      int cnt = 0;
      int CurEmptyLoop = 0;

      while (CurEmptyLoop < MaxEmptyLoop)
      {
        try
        {
          await Task.Delay(WriteDelay, LWECT.Token);
          cnt = await DoBulk();

          if (cnt != 0)
            CurEmptyLoop = 0;
          else
            CurEmptyLoop++;
        }
        catch {
          CurEmptyLoop = MaxEmptyLoop;
        }
      }

      LWEntry LWE = null;
      LWList.TryRemove(TableName, out LWE);

      await Task.Delay(100);

      if (LWE.FBuffTbl != null)
      {
        try
        {
          await DoBulk();
        }
        catch { }
      }
    }
  }

  #region Sql_SP_FN

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static Int32 PWriter(SqlString DstTbl, SqlXml Rows, SqlString RowPath)
  {
    LWEntry LW;

    LW = LWList.GetOrAdd(
          DstTbl.Value
        , (ADstTbl) => new Lazy<LWEntry>(
            () =>
            {
              //debug
              SqlContext.Pipe.Send("LWEntry Consturcor");

              LWEntry r = new LWEntry(ADstTbl);
              return r;
            }
            , LazyThreadSafetyMode.ExecutionAndPublication
          ).Value
    );

    LW.AddRecords(Rows, RowPath);

    return 0;
  }

  #endregion Sql_SP_FN
}


г_Код рабочий, но очень далек от конечного варианта.
Решил ответить исключительно в надежде получить советы/идеи на следующие вопросы, хотя тема скорее для другого форума.

Есть вероятность потери части записей при перезагрузке SQL_я или AppDomain_а. Потоки в default scheduler_е фоновые => тихо помрут.
Вопрос: Как отследить ?

В "System.Threading.Tasks.Dataflow" есть несколько очень полезных классов, позволяющих решить задачу намного более эффективно, но как подключить NuGet к проекту БД - загадка.

В коде есть еще несколько серьезных проблем, но о них после ...
19 апр 16, 12:10    [19076246]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
noTpyJI
Member

Откуда:
Сообщений: 116
alexeyvg,

ушел от текущего контекста. создаю новое соединение

string ret = "Data Source = server; Initial Catalog = database; Integrated Security = True";


в итоге при вызове процедуры в sql внутри транзакции получаю ошибку System.Data.SqlClient.SqlException: Transaction context in use by another session
20 апр 16, 16:40    [19084145]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
noTpyJI
Member

Откуда:
Сообщений: 116
Jovanny
Во-первых, зачем Вы закомментировали
string ret = "context connection = true";
?


в текущем контексте все мои действия внутри транзакции откатятся

Jovanny
Во-вторых, причём здесь
[SqlFunction()]
, если это процедура?

очепятка
20 апр 16, 16:43    [19084166]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31950
noTpyJI
ушел от текущего контекста. создаю новое соединение
string ret = "Data Source = server; Initial Catalog = database; Integrated Security = True";
Ну вы же эту строку не присваиваете свойству ConnectionString объекта SqlConnection.
У вас строка коннекта "Enlist=false"
Ваши server, database не используются.
20 апр 16, 21:38    [19085257]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
noTpyJI
Member

Откуда:
Сообщений: 116
alexeyvg,

        [SqlFunction()]
        public static void HandleException(out SqlString errorMessage, out SqlInt32 errorNumber, out SqlInt32 errorState, SqlInt32 errorProcId)
        // public static void HandleException(SqlInt32 errorProcId)
        {
            errorMessage = "";
            errorNumber = 0;
            errorState = 0;

            using (var connect = new SqlConnection(GetSqlConnectionLocal())) -- создает новое соединение   const string connectioncString = "Data Source = {0}; Initial Catalog = {1}; Integrated Security = True";
            {
                try
                {
                    connect.Open();

                    using (var command = new SqlCommand("[audit].[HandleException]", connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        Dictionary<string, SqlParameter> dictionary = new Dictionary<string, SqlParameter>();

                        dictionary.Add("ErrorMessage", new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, 2048));
                        dictionary.Add("ErrorNumber", new SqlParameter("@ErrorNumber", SqlDbType.Int));
                        dictionary.Add("ErrorState", new SqlParameter("@ErrorState", SqlDbType.Int));
                        dictionary.Add("ErrorProcID", new SqlParameter("@ErrorProcID", SqlDbType.Int));

                        foreach (var kvp in dictionary)
                        {
                            command.Parameters.Add(kvp.Value);

                            if (kvp.Key != "ErrorProcID")
                            {
                                kvp.Value.Direction = ParameterDirection.Output;
                            }
                        }

                        dictionary["ErrorProcID"].Value = errorProcId.Value;

                        command.ExecuteNonQuery();

                        if (dictionary["ErrorMessage"].Value != null)
                        {
                           errorMessage = (SqlString)dictionary["ErrorMessage"].Value;
                           errorNumber = (SqlInt32)dictionary["ErrorNumber"].Value;
                           errorState = (SqlInt32)dictionary["ErrorState"].Value;
                        }
                    }

                }
                catch (System.Exception ex)
                {
                    if (SqlContext.Pipe != null) SqlContext.Pipe.Send(ex.Message);

                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }
        }
21 апр 16, 07:05    [19085965]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Правда, не совсем понятно совместное использование using c SqlCommand и using с try...catch.
21 апр 16, 12:12    [19087374]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31950
noTpyJI
alexeyvg,

            using (var connect = new SqlConnection(GetSqlConnectionLocal())) -- создает новое соединение   const string connectioncString = "Data Source = {0}; Initial Catalog = {1}; Integrated Security = True";
            {
                try
                {
                    connect.Open();

                    using (var command = new SqlCommand("[audit].[HandleException]", connect))
И что?

Вы используете тот коннект, который был при вызове этой CLR функции. Командой connect = new SqlConnection(GetSqlConnectionLocal()), и потом командой connect.Open()

Понимаете, что бы сделать отдельный коннект, независимый от текущего, нужно его сделать. Вызвав соотв. функцию, и передав ей строку соединения.

А у вас этого нету.

Вы вызываете функцию, но не передаёте ей строку соединения, а говорите ей "дай мне текущий коннект".
21 апр 16, 22:05    [19090806]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
noTpyJI
Member

Откуда:
Сообщений: 116
alexeyvg,

интересно как вы пришли к такому выводу?

      
 private static string ConnectioncString => (string)"context connection = true";

 private static string GetConnectioncLocal()
        {
            const string connectioncString = "Data Source = {0}; Initial Catalog = {1}; Integrated Security = True";
            string result = null;

            using (var connection = new SqlConnection(ConnectioncString))
            {
                var command = new SqlCommand("select @@SERVERNAME , DB_NAME()", connection);

                try
                {
                    connection.Open();

                    var reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            result = string.Format(connectioncString, reader.GetString(0), reader.GetString(1));
                        }
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
            
            return result;
        }
22 апр 16, 08:43    [19091467]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31950
noTpyJI
alexeyvg,

интересно как вы пришли к такому выводу?
Это третий (или четвёртый?) вариант вашей функции.
В предыдущих я пришёл к такому выводу, анализируя текст.
В последнем варианте всё хорошо, строка соединения передаётся.
22 апр 16, 09:49    [19091749]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31950
alexeyvg
noTpyJI
alexeyvg,

интересно как вы пришли к такому выводу?
Это третий (или четвёртый?) вариант вашей функции.
В предыдущих я пришёл к такому выводу, анализируя текст.
В последнем варианте всё хорошо, строка соединения передаётся.
Вы же видите разницу между этими двумя вариантами?

using (var connect = new SqlConnection(GetSqlConnectionLocal())) -- создает новое соединение   
const string connectioncString = "Data Source = {0}; Initial Catalog = {1}; Integrated Security = True";
и
const string connectioncString = "Data Source = {0}; Initial Catalog = {1}; Integrated Security = True";
using (var connection = new SqlConnection(ConnectioncString))
22 апр 16, 09:54    [19091775]     Ответить | Цитировать Сообщить модератору
 Re: sql server + c# clr  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31950
alexeyvg
alexeyvg
пропущено...
Это третий (или четвёртый?) вариант вашей функции.
В предыдущих я пришёл к такому выводу, анализируя текст.
В последнем варианте всё хорошо, строка соединения передаётся.
Вы же видите разницу между этими двумя вариантами?

using (var connect = new SqlConnection(GetSqlConnectionLocal())) -- создает новое соединение   
const string connectioncString = "Data Source = {0}; Initial Catalog = {1}; Integrated Security = True";

и
const string connectioncString = "Data Source = {0}; Initial Catalog = {1}; Integrated Security = True";
using (var connection = new SqlConnection(ConnectioncString))
Причём вариант в начале топика, который похож на второй образец кода, не будет работать, потому что вы стираете параметры коннекта.

В общем, нужно быть внимательнее, не менять хаотично тексты, отлаживать что то одно :-)
22 апр 16, 09:57    [19091800]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить