CREATE TABLE [dbo].[Synonyms](
	[SynonymId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[SynonymText] [nvarchar](255) NOT NULL,
	[CircleId] [numeric](18, 0) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[Comment] [nvarchar](100) NULL,
	[Source] [nvarchar](100) NULL,
	[IsEthalon] [bit] NOT NULL,
 CONSTRAINT [PK_Synonyms] PRIMARY KEY CLUSTERED 
(
	[SynonymId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[Synonyms] ON 
INSERT [dbo].[Synonyms] ([SynonymId], [SynonymText], [CircleId], [IsActive], [Comment], [Source], [IsEthalon]) VALUES (CAST(65 AS Numeric(18, 0)), N'Новый синоним', CAST(3 AS Numeric(18, 0)), 1, N'', N'', 0)
SET IDENTITY_INSERT [dbo].[Synonyms] OFF

create PROCEDURE [dbo].[Synonym_Put]
      @Id numeric(18,0) OUTPUT
    , @Text nvarchar(255)
    , @CircleId numeric(18,0)
    , @Comment nvarchar(100)
    , @Source nvarchar(100)
    , @IsEthalon bit
AS
  if (@Id is null)
    -- insert
    BEGIN
      INSERT INTO dbo.Synonyms (SynonymText, CircleId, IsActive, Comment, Source, IsEthalon )
      VALUES (@Text, @CircleId, 1, @Comment, @Source, @IsEthalon)
      
      SELECT @Id = SCOPE_IDENTITY()
    END
    
    -- update
    BEGIN
      UPDATE dbo.Synonyms
      SET
        SynonymText = @Text,
        CircleId = @CircleId,
        IsActive = 1,
        Comment = @Comment,
        Source = @Source,
        IsEthalon = @IsEthalon
      WHERE SynonymId = @Id
    END

GO

protected void initAdapter()
        {
            Connection = new SqlConnection(SynonymsVS.Common.Utilities.ConnectionString);
            cmdSynonymsSelect = new SqlCommand("select * from dbo.Synonyms Where IsActive = 1 and CircleId = @CircleId and IsEthalon = @IsEthalon", Connection);
            cmdSynonymsSelect.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CircleId", System.Data.SqlDbType.Decimal, 18));
            cmdSynonymsSelect.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IsEthalon", System.Data.SqlDbType.Bit));

            cmdSynonymsPut = new SqlCommand("dbo.Synonym_Put", Connection);
            cmdSynonymsPut.CommandType = CommandType.StoredProcedure;
            cmdSynonymsPut.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id", System.Data.SqlDbType.Decimal, 18, ParameterDirection.InputOutput, true,0,0, "SynonymId", DataRowVersion.Default,null));
            cmdSynonymsPut.Parameters["@Id"].Direction = ParameterDirection.InputOutput;
            /*cmdSynonymsPut.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id", System.Data.SqlDbType.Int, 0, "SynonymId"));
            cmdSynonymsPut.Parameters["@Id"].Direction = ParameterDirection.InputOutput;*/
            cmdSynonymsPut.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Text", System.Data.SqlDbType.VarChar, 255, "SynonymText"));
            cmdSynonymsPut.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CircleId", System.Data.SqlDbType.Decimal, 18, "CircleId"));
            cmdSynonymsPut.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Comment", System.Data.SqlDbType.VarChar, 100, "Comment"));
            cmdSynonymsPut.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Source", System.Data.SqlDbType.VarChar, 100, "Source"));
            cmdSynonymsPut.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IsEthalon", System.Data.SqlDbType.Bit, 1, "IsEthalon"));

            cmdSynonymsDel = new SqlCommand("dbo.Synonym_Del", Connection);
            cmdSynonymsDel.CommandType = CommandType.StoredProcedure;
            cmdSynonymsDel.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id", System.Data.SqlDbType.Decimal, 18, "SynonymId"));

            daSynonyms = new SqlDataAdapter();
            daSynonyms.SelectCommand = cmdSynonymsSelect;
            daSynonyms.InsertCommand = cmdSynonymsPut;
            daSynonyms.UpdateCommand = cmdSynonymsPut;
            daSynonyms.DeleteCommand = cmdSynonymsDel;
        }

private void updateRow(DataRow rw)
        {
            try
            {
                SqlCommand cmd = cmdSynonymsPut;
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }

                cmd.Parameters["@Id"].Value = rw["SynonymId"];
                cmd.Parameters["@Text"].Value = rw["SynonymText"];
                cmd.Parameters["@CircleId"].Value = rw["CircleId"];
                cmd.Parameters["@Comment"].Value = rw["Comment"];
                cmd.Parameters["@Source"].Value = rw["Source"];
                cmd.Parameters["@IsEthalon"].Value = rw["IsEthalon"];

                cmd.ExecuteNonQuery();

                // out parameter
                rw["SynonymId"] = cmd.Parameters["@Id"].Value;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка при сохранении синонимов: "+ ex.Message, "Ошибка");
            }
            finally
            {
                if (Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
        }

/// <summary>
        /// Applies changes.
        /// </summary>
        public void ApplyChanges()
        {
            try
            {
                foreach (DataRow rw in this.Table.Rows)
                {
                    switch (rw.RowState)
                    {
                        case DataRowState.Added:
                        case DataRowState.Modified:
                            updateRow(rw);
                            break;
                        case DataRowState.Deleted:
                            deleteRow(rw);
                            break;
                    }
                }

                Refresh();

                //daSynonyms.Update(this.Table);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка при сохранении изменений в синонимах, данные:" + ex.Message);
            }
        }