Public Shared Sub GetChange(ByVal tabName As String)

        Dim i As Integer
        Dim myCount As Integer

        myCount = CompanyForm.dsCompany.Tables(tabName).Rows.Count -  1 

        For i =  0  To myCount

            Select Case CompanyForm.dsCompany.Tables(tabName).Rows(i).RowState

                 'Обрабатываем удаленные записи 
            Case DataRowState.Deleted
                    teloid = CompanyForm.dsCompany.Tables(tabName).Rows(i).Item("CompanyContragentId", DataRowVersion.Original) 
 '...Вызываем процедуру для удаления строки в качестве параметра передаем значение teloid 

                     'Обрабатываем измененные записи. 
                Case DataRowState.Modified
                    teloid = CompanyForm.dsCompany.Tables(tabName).Rows(i).Item("CompanyContragentId", DataRowVersion.Original) 
 '...Вызываем процедуру для редактирования строки в качестве параметра передаем значение teloid 

                     'Обрабатываем добавленные записи. 
                Case DataRowState.Added
  '...Вызываем процедуру для добавления новой строки  
            End Select
        Next i

    End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

         'Настройка команды SELECT 
        cmdSqlServerSelect.Connection = cnSqlServer
        cmdSqlServerSelect.CommandType = CommandType.StoredProcedure
        cmdSqlServerSelect.CommandText = "dbo.spSelect"
         '------------------------------------------------------------------ 

         'Настройка команды UPDATE 
        cmdSqlServerUpdate.Connection = cnSqlServer
        cmdSqlServerUpdate.CommandType = CommandType.StoredProcedure
        cmdSqlServerUpdate.CommandText = "dbo.spUpdateTelo"

        cmdSqlServerUpdate.Parameters.Add("@TeloId", SqlDbType.Int,  4 , "teloid")
        cmdSqlServerUpdate.Parameters("@TeloId").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@TeloId").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@City", SqlDbType.Char,  40 , "city")
        cmdSqlServerUpdate.Parameters("@City").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@City").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@FIO", SqlDbType.Char,  50 , "fio")
        cmdSqlServerUpdate.Parameters("@FIO").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@FIO").SourceVersion = DataRowVersion.Current


         '-------------------------------------------------------------- 

         'Настройка команды DELETE 
        cmdSqlServerDelete.Connection = cnSqlServer
        cmdSqlServerDelete.CommandType = CommandType.StoredProcedure
        cmdSqlServerDelete.CommandText = "dbo.spDeleteTelo"

        cmdSqlServerDelete.Parameters.Add("@TeloId", SqlDbType.Int,  4 , "teloid")
        cmdSqlServerDelete.Parameters("@TeloId").Direction = ParameterDirection.Input
        cmdSqlServerDelete.Parameters("@TeloId").SourceVersion = DataRowVersion.Original

                '---------------------------------------------------------- 

         'Настройка команды INSERT 
        cmdSqlServerInsert.Connection = cnSqlServer
        cmdSqlServerInsert.CommandType = CommandType.StoredProcedure
        cmdSqlServerInsert.CommandText = "dbo.spInsertTelo"

        cmdSqlServerInsert.Parameters.Add("@City", SqlDbType.Char,  40 , "city")
        cmdSqlServerInsert.Parameters("@City").Direction = ParameterDirection.Input
         'cmdSqlServerInsert.Parameters("@City").SourceVersion = DataRowVersion.Original 
        
        cmdSqlServerInsert.Parameters.Add("@FIO", SqlDbType.Char,  50 , "fio")
        cmdSqlServerInsert.Parameters("@FIO").Direction = ParameterDirection.Input
         'cmdSqlServerInsert.Parameters("@FIO").SourceVersion = DataRowVersion.Original 

         '--------------------------------------------------------------------- 

        daTest.TableMappings.Add("Table", "TeloTbl")
        
        daTest.SelectCommand = cmdSqlServerSelect
        daTest.UpdateCommand = cmdSqlServerUpdate
        daTest.InsertCommand = cmdSqlServerInsert
        daTest.DeleteCommand = cmdSqlServerDelete


        cnSqlServer.Open()

        daTest.Fill(dsTest)

        dgTelo.DataSource = dsTest.Tables("TeloTbl")
       
        cnSqlServer.Close()

    End Sub

Private Sub bttnDelonServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnDelonServer.Click

        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Deleted))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Added))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.ModifiedCurrent))
    End Sub

daTest.Update(dsTest.Tables(...).Select(...))

daTest.Update(dsTest)

daTest.Update(dsTest)

daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Deleted))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Added))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.ModifiedCurrent))

PROCEDURE dbo.spUpdateTelo
(@TeloId int,@FIO char( 50 ),@City char( 40 ),@CityId char,@CityIdold char)

AS
UPDATE dbo.CityTbl SET CityId=@CityId WHERE CityId=@CityIdold
UPDATE dbo.TeloTbl SET FIO=@FIO,City=@City WHERE TELOID=@TELOID
	RETURN 

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

         'Настройка команды SELECT 
        cmdSqlServerSelect.Connection = cnSqlServer
        cmdSqlServerSelect.CommandType = CommandType.StoredProcedure
        cmdSqlServerSelect.CommandText = "dbo.spSelect"
         '------------------------------------------------------------------ 

         'Настройка команды UPDATE 
        cmdSqlServerUpdate.Connection = cnSqlServer
        cmdSqlServerUpdate.CommandType = CommandType.StoredProcedure
        cmdSqlServerUpdate.CommandText = "dbo.spUpdateTelo"

        cmdSqlServerUpdate.Parameters.Add("@TeloId", SqlDbType.Int,  4 , "teloid")
        cmdSqlServerUpdate.Parameters("@TeloId").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@TeloId").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@City", SqlDbType.Char,  40 , "city")
        cmdSqlServerUpdate.Parameters("@City").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@City").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@FIO", SqlDbType.Char,  50 , "fio")
        cmdSqlServerUpdate.Parameters("@FIO").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@FIO").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@CityId", SqlDbType.Char,  40 , "CityId")
        cmdSqlServerUpdate.Parameters("@CityId").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@CityId").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@CityIdold", SqlDbType.Char,  40 , "CityId")
        cmdSqlServerUpdate.Parameters("@CityId").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@CityId").SourceVersion = DataRowVersion.Original

         '-------------------------------------------------------------- 

         'Настройка команды DELETE 
        cmdSqlServerDelete.Connection = cnSqlServer
        cmdSqlServerDelete.CommandType = CommandType.StoredProcedure
        cmdSqlServerDelete.CommandText = "dbo.spDeleteTelo"

        cmdSqlServerDelete.Parameters.Add("@TeloId", SqlDbType.Int,  4 , "teloid")
        cmdSqlServerDelete.Parameters("@TeloId").Direction = ParameterDirection.Input
        cmdSqlServerDelete.Parameters("@TeloId").SourceVersion = DataRowVersion.Original

        cmdSqlServerDelete.Parameters.Add("@CityId", SqlDbType.Char,  40 , "cityid")
        cmdSqlServerDelete.Parameters("@CityId").Direction = ParameterDirection.Input
        cmdSqlServerDelete.Parameters("@CityId").SourceVersion = DataRowVersion.Original
         '---------------------------------------------------------- 

         'Настройка команды INSERT 
        cmdSqlServerInsert.Connection = cnSqlServer
        cmdSqlServerInsert.CommandType = CommandType.StoredProcedure
        cmdSqlServerInsert.CommandText = "dbo.spInsertTelo"

        cmdSqlServerInsert.Parameters.Add("@City", SqlDbType.Char,  40 , "city")
        cmdSqlServerInsert.Parameters("@City").Direction = ParameterDirection.Input
         'cmdSqlServerInsert.Parameters("@City").SourceVersion = DataRowVersion.Current 

        cmdSqlServerInsert.Parameters.Add("@FIO", SqlDbType.Char,  50 , "fio")
        cmdSqlServerInsert.Parameters("@FIO").Direction = ParameterDirection.Input
         'cmdSqlServerInsert.Parameters("@FIO").SourceVersion = DataRowVersion.Current 

        cmdSqlServerInsert.Parameters.Add("@CityId", SqlDbType.Char,  40 , "cityid")
        cmdSqlServerInsert.Parameters("@CityId").Direction = ParameterDirection.Input
         'cmdSqlServerInsert.Parameters("@CityId").SourceVersion = DataRowVersion.Current 

         '--------------------------------------------------------------------- 

        daTest.TableMappings.Add("Table", "TeloTbl")
        daTest.TableMappings.Add("Table1", "CityTbl")

        daTest.SelectCommand = cmdSqlServerSelect
        daTest.UpdateCommand = cmdSqlServerUpdate
        daTest.InsertCommand = cmdSqlServerInsert
        daTest.DeleteCommand = cmdSqlServerDelete


        daTest.Fill(dsTest)

        dgTelo.DataSource = dsTest.Tables("TeloTbl")
        dgCity.DataSource = dsTest.Tables("CityTbl")

  End Sub
     'Функция создания строки подключения к серверу 
    Public Shared Function BuildCnnStr() As String

        Dim strTemp As String

        strTemp = "data source=409ADMIN;integrated security=SSPI;initial catalog=delupins;persist security info=False"
         'strTemp = "data source=STALKER;integrated security=FALSE;initial catalog=delupins;persist security info=False;uid=sa;pwd=" 

        Return strTemp

    End Function

    Private Sub DelDataGrid(ByVal dg As DataGrid)
        If Not Me.BindingContext(dg.DataSource).Count =  0  Then
            Me.BindingContext(dg.DataSource).RemoveAt(Me.BindingContext(dg.DataSource).Position)
        End If
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        DelDataGrid(dgTelo)
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        DelDataGrid(dgCity)
    End Sub

    Private Sub bttnDelonServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnDelonServer.Click
        
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Deleted))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Added))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.ModifiedCurrent))

        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.Deleted))
        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.Added))
        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.ModifiedCurrent))

            End Sub

PROCEDURE dbo.spInsertTelo
(@FIO char( 50 )= 'XXX',@City char(40)='XXX',@CityId char(40)='XXX') 

AS
if @cityid<> 'XXX'  
INSERT INTO dbo.CityTbl(CityId) VALUES (@CityId)
if @FIO<> 'XXX' 
INSERT INTO dbo.TeloTbl(FIO, City) VALUES(@FIO,@City)

	/* SET NOCOUNT ON */
	RETURN 

cmdSqlServerInsert.CommandText = "dbo.spInsertTelo"

Public Shared dsTest As New DataSet
    Public Shared daTest As New SqlClient.SqlDataAdapter

    Public Shared cmdSqlServerSelect As New SqlClient.SqlCommand
    Public Shared cmdSqlServerDelete As New SqlClient.SqlCommand
    Public Shared cmdSqlServerUpdate As New SqlClient.SqlCommand
    Public Shared cmdSqlServerInsert As New SqlClient.SqlCommand

    Public Shared cnSqlServer As New SqlClient.SqlConnection(BuildCnnStr())

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

         'Настройка команды SELECT 
        cmdSqlServerSelect.Connection = cnSqlServer
        cmdSqlServerSelect.CommandType = CommandType.StoredProcedure
        cmdSqlServerSelect.CommandText = "dbo.spSelect"
         '------------------------------------------------------------------ 

         'Настройка команды UPDATE 
        cmdSqlServerUpdate.Connection = cnSqlServer
        cmdSqlServerUpdate.CommandType = CommandType.StoredProcedure
        cmdSqlServerUpdate.CommandText = "dbo.spUpdateTelo"

        cmdSqlServerUpdate.Parameters.Add("@TeloId", SqlDbType.Int,  4 , "teloid")
        cmdSqlServerUpdate.Parameters("@TeloId").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@TeloId").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@City", SqlDbType.Char,  40 , "city")
        cmdSqlServerUpdate.Parameters("@City").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@City").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@FIO", SqlDbType.Char,  50 , "fio")
        cmdSqlServerUpdate.Parameters("@FIO").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@FIO").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@CityId", SqlDbType.Char,  40 , "CityId")
        cmdSqlServerUpdate.Parameters("@CityId").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@CityId").SourceVersion = DataRowVersion.Current

        cmdSqlServerUpdate.Parameters.Add("@CityIdold", SqlDbType.Char,  40 , "CityId")
        cmdSqlServerUpdate.Parameters("@CityIdold").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@CityIdold").SourceVersion = DataRowVersion.Original

         '-------------------------------------------------------------- 

         'Настройка команды DELETE 
        cmdSqlServerDelete.Connection = cnSqlServer
        cmdSqlServerDelete.CommandType = CommandType.StoredProcedure
        cmdSqlServerDelete.CommandText = "dbo.spDeleteTelo"

        cmdSqlServerDelete.Parameters.Add("@TeloId", SqlDbType.Int,  4 , "teloid")
        cmdSqlServerDelete.Parameters("@TeloId").Direction = ParameterDirection.Input
        cmdSqlServerDelete.Parameters("@TeloId").SourceVersion = DataRowVersion.Original

        cmdSqlServerDelete.Parameters.Add("@CityId", SqlDbType.Char,  40 , "cityid")
        cmdSqlServerDelete.Parameters("@CityId").Direction = ParameterDirection.Input
        cmdSqlServerDelete.Parameters("@CityId").SourceVersion = DataRowVersion.Original
         '---------------------------------------------------------- 

         'Настройка команды INSERT 
        cmdSqlServerInsert.Connection = cnSqlServer
        cmdSqlServerInsert.CommandType = CommandType.StoredProcedure
        cmdSqlServerInsert.CommandText = "dbo.spInsertTelo"

        cmdSqlServerInsert.Parameters.Add("@City", SqlDbType.Char,  40 , "city")
        cmdSqlServerInsert.Parameters("@City").Direction = ParameterDirection.Input
         'cmdSqlServerInsert.Parameters("@City").SourceVersion = DataRowVersion.Current 

        cmdSqlServerInsert.Parameters.Add("@FIO", SqlDbType.Char,  50 , "fio")
        cmdSqlServerInsert.Parameters("@FIO").Direction = ParameterDirection.Input
         'cmdSqlServerInsert.Parameters("@FIO").SourceVersion = DataRowVersion.Current 

        cmdSqlServerInsert.Parameters.Add("@CityId", SqlDbType.Char,  40 , "cityid")
        cmdSqlServerInsert.Parameters("@CityId").Direction = ParameterDirection.Input
         'cmdSqlServerInsert.Parameters("@CityId").SourceVersion = DataRowVersion.Current 

         '--------------------------------------------------------------------- 

        daTest.TableMappings.Add("Table", "TeloTbl")
        daTest.TableMappings.Add("Table1", "CityTbl")

        daTest.SelectCommand = cmdSqlServerSelect
        daTest.UpdateCommand = cmdSqlServerUpdate
        daTest.InsertCommand = cmdSqlServerInsert
        daTest.DeleteCommand = cmdSqlServerDelete

         daTest.Fill(dsTest)

        dgTelo.DataSource = dsTest.Tables("TeloTbl")
        dgCity.DataSource = dsTest.Tables("CityTbl")
      
    End Sub

     'Функция создания строки подключения к серверу 
    Public Shared Function BuildCnnStr() As String

        Dim strTemp As String

        strTemp = "data source=409ADMIN;integrated security=SSPI;initial catalog=delupins;persist security info=False"
         'strTemp = "data source=STALKER;integrated security=FALSE;initial catalog=delupins;persist security info=False;uid=sa;pwd=" 

        Return strTemp

    End Function

    Private Sub DelDataGrid(ByVal dg As DataGrid)
        If Not Me.BindingContext(dg.DataSource).Count =  0  Then
            Me.BindingContext(dg.DataSource).RemoveAt(Me.BindingContext(dg.DataSource).Position)
        End If
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        DelDataGrid(dgTelo)
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        DelDataGrid(dgCity)
    End Sub

    Private Sub bttnDelonServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnDelonServer.Click
       
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Deleted))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Added))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.ModifiedCurrent))

        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.Deleted))
        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.Added))
        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.ModifiedCurrent))

       End Sub

PROCEDURE dbo.spDeleteTelo (@teloid int= 9999 ,@cityid char( 40 )='xxxx')
AS
delete dbo.telotbl where telotbl.teloid=@teloid
delete dbo.citytbl where citytbl.cityid=@cityid
	RETURN 

PROCEDURE dbo.spInsertTelo (@FIO char( 50 )='XXX',@City char( 40 )='XXX',@CityId char( 40 )='XXX')

AS
if @cityid<>'XXX' 
INSERT INTO dbo.CityTbl(CityId) VALUES (@CityId)
if @FIO<>'XXX'
INSERT INTO dbo.TeloTbl(FIO, City)
VALUES(@FIO,@City)
	RETURN 

PROCEDURE dbo.spUpdateTelo
(@TeloId int=NULL,@FIO char( 50 )=NULL,@City char( 40 )=NULL,@CityId char( 40 )=NULL,@CityIdold char( 40 )=NULL)

AS
UPDATE dbo.CityTbl SET CityId=isnull(@CityId,Cityid) WHERE CityId=@CityIdold
UPDATE dbo.TeloTbl SET FIO=isnull(@FIO,fio),City=isnull(@City,city) WHERE TELOID=@TELOID

	RETURN 

PROCEDURE dbo.spSelect

AS
	SET NOCOUNT ON
	select TeloTbl.Teloid,telotbl.fio,telotbl.city from dbo.telotbl
	select citytbl.cityid from dbo.cityTbl
	RETURN 

PROCEDURE dbo.spCompanyUpdate
(
@CompanyID int=null,@Name char( 30 )=null,@Descript text=null,@WebSite char( 50 )=null,@History text = null,@DataZan datetime=null,
@CompanyAdrId int=null,@PostIndex int= null,@Street char( 50 )= null,@HomeNum char( 10 )= null,@RoomNum int= null,@CityId int= null,
@CompanyRecvId int= null,@RatedAccount char( 20 )= null,@CorrAccount char( 20 )= null,@BankName char( 100 )= null,@BIK char( 20 )= null,@INN char( 10 )= null,@KPP char( 10 )= null,@OKOHX char( 10 )= null,@OKPO char( 10 )= null,
@Discount numeric( 10 , 0 )= null,@TovarGroupId char( 25 )= null,@ProducerId char( 15 )= null,
@CompanySotrudnID int= null,@Family char( 25 )= null,@NameSotr char( 30 )= null ,@PatrName char( 25 )= null,@Title char( 100 )= null,@Beathday datetime= null,
@CompanyContragentId int= null,@NameContr char( 30 )= null,@RatedAccountContr int= null,@CorrAccountContr int= null,@BIKContr int= null,@BankNameContr char( 100 )= null,@INNContr int= null,@KPPContr int= null,@OKOHXContr int= null,@OKPOContr int= null,@WebSiteContr char( 30 )= null,@DescriptContr varchar( 150 )= null,@DataZanContr datetime= null,
@CompanyActivityId int= null,@ActivityData datetime= null,@TitleAct char( 100 )= null,@DescriptAct text= null,
@CompanyLicId int= null,@DescriptLic text= null,
@ContragentSotrudnId int= null,@FamilyContrSotr char( 25 )= null,@NameContrSotr char( 30 )= null,@PartNameContrSotr char( 25 )= null,@titleContrSotr char( 100 )= null,
@ContragentAdrId int= null,@PostIndexAdrContr int= null,@StreetAdrContr char( 50 )= null,@HomeNumAdrContr char( 10 )= null,@RoomNumAdrContr int= null,@CityIdAdrContr int= null)

AS

UPDATE dbo.CompanyTbl SET dbo.Companytbl.Name=isnull(@Name,name),Descript=isnull(@Descript,descript),WebSite=isnull(@WebSite,WebSite),History=isnull(@History,history),DataZan=isnull(@DataZan,datazan) WHERE CompanyId=@companyid
UPDATE dbo.CompanyAdrTbl SET PostIndex=isnull(@PostIndex,PostIndex),Street=isnull(@Street,Street),HomeNum=isnull(@HomeNum,HomeNum),RoomNum=isnull(@RoomNum,RoomNum),CityId=isnull(@CityId,CityId) WHERE CompanyId=@companyid and CompanyAdrId=@CompanyAdrId
UPDATE dbo.CompanyRecvTbl SET  RatedAccount=isnull(@RatedAccount,RatedAccount), CorrAccount=isnull(@CorrAccount,CorrAccount),BankName=isnull(@BankName,BankName),BIK=isnull(@BIK,BIK),INN=isnull(@INN,INN),KPP=isnull(@KPP,KPP),OKOHX=isnull(@OKOHX,OKOHX),OKPO=isnull(@OKPO,OKPO) WHERE CompanyRecvId=@CompanyRecvId
UPDATE dbo.CompanyDiscountTbl SET Discount=isnull(@Discount,Discount) WHERE CompanyID=@CompanyID AND TovarGroupId=@TovarGroupId AND ProducerId=@ProducerId
UPDATE dbo.CompanySotrudnTbl SET dbo.CompanySotrudnTbl.Family=isnull(@Family,dbo.CompanySotrudnTbl.Family),dbo.CompanySotrudnTbl.Name=isnull(@NameSotr,dbo.CompanySotrudnTbl.Name),dbo.CompanySotrudnTbl.PatrName=isnull(@PatrName,dbo.CompanySotrudnTbl.PatrName),dbo.CompanySotrudnTbl.Title=isnull(@Title,dbo.CompanySotrudnTbl.Title),dbo.CompanySotrudnTbl.Beathday=isnull(@Beathday,dbo.CompanySotrudnTbl.Beathday) WHERE CompanySotrudnID=@CompanySotrudnID
UPDATE dbo.CompanyContragentTbl SET dbo.CompanyContragentTbl.Name=isnull(@NameContr,dbo.CompanyContragentTbl.Name),dbo.CompanyContragentTbl.RatedAccount=isnull(@RatedAccountContr,dbo.CompanyContragentTbl.RatedAccount),dbo.CompanyContragentTbl.CorrAccount=isnull(@CorrAccountContr,dbo.CompanyContragentTbl.CorrAccount),dbo.CompanyContragentTbl.BIK=isnull(@BIKContr,dbo.CompanyContragentTbl.BIK),dbo.CompanyContragentTbl.BankName=isnull(@BankNameContr,dbo.CompanyContragentTbl.BankName),dbo.CompanyContragentTbl.INN=isnull(@INNContr,dbo.CompanyContragentTbl.INN),dbo.CompanyContragentTbl.KPP=isnull(@KPPContr,dbo.CompanyContragentTbl.KPP),dbo.CompanyContragentTbl.OKOHX=isnull(@OKOHXContr,dbo.CompanyContragentTbl.OKOHX),dbo.CompanyContragentTbl.OKPO=isnull(@OKPOContr,dbo.CompanyContragentTbl.OKPO),dbo.CompanyContragentTbl.WebSite=isnull(@WebSiteContr,dbo.CompanyContragentTbl.WebSite),dbo.CompanyContragentTbl.Descript=isnull(@DescriptContr,dbo.CompanyContragentTbl.Descript),dbo.CompanyContragentTbl.DataZan=isnull(@DataZanContr,dbo.CompanyContragentTbl.DataZan) WHERE CompanyContragentId=@CompanyContragentId
UPDATE dbo.CompanyActivityTbl SET ActivityData=isnull(@ActivityData,ActivityData),Title=isnull(@TitleAct,Title),Descript=isnull(@DescriptAct,Descript) WHERE CompanyActivityId=@CompanyActivityId
UPDATE dbo.CompanyLicTbl SET Descript=isnull(@DescriptLic,Descript)WHERE CompanyLicId=@CompanyLicId
UPDATE dbo.ContragentSotrudnTbl SET dbo.ContragentSotrudnTbl.Family=isnull(@FamilyContrSotr,Family),dbo.ContragentSotrudnTbl.Name=isnull(@NameContrSotr,Name),dbo.ContragentSotrudnTbl.PartName=isnull(@PartNameContrSotr,PartName),dbo.ContragentSotrudnTbl.title=isnull(@titleContrSotr,title) WHERE ContragentSotrudnId=@ContragentSotrudnId 
UPDATE dbo.ContragentAdrTbl SET dbo.ContragentAdrTbl.PostIndex=isnull(@PostIndexAdrContr,PostIndex),dbo.ContragentAdrTbl.Street=isnull(@StreetAdrContr,Street),dbo.ContragentAdrTbl.HomeNum=isnull(@HomeNumAdrContr,HomeNum), dbo.ContragentAdrTbl.RoomNum=isnull(@RoomNumAdrContr,RoomNum),dbo.ContragentAdrTbl.CityId=isnull(@CityIdAdrContr,CityId) WHERE ContragentAdrId=@ContragentAdrId
	
	/* SET NOCOUNT ON */
	RETURN 

cmdSqlServerUpdate.Connection = cnSqlServer
cmdSqlServerUpdate.CommandType = CommandType.StoredProcedure

 'Описываем все параметры 
cmdSqlServerUpdate.Parameters.Add("@CompanyId", SqlDbType.Int,  4 , "CompanyId")
        cmdSqlServerUpdate.Parameters("@CompanyId").Direction = ParameterDirection.Input
        cmdSqlServerUpdate.Parameters("@CompanyId").SourceVersion = DataRowVersion.Current
.
.
.
cmdSqlServerUpdate.CommandText = "dbo.spUpdateCompany"
daTest.Update(dsTest.Tables("CompanyTbl").Select("", "", DataViewRowState.ModifiedCurrent))
.
.
.
cmdSqlServerUpdate.CommandText = "dbo.spUpdateContragent"
daTest.Update(dsTest.Tables("ContragentTbl").Select("", "", DataViewRowState.ModifiedCurrent))
.
.
.

Public Shared dsTest As New DataSet
    Public Shared daTest As New SqlClient.SqlDataAdapter

    Public Shared cmdSqlServerSelect As New SqlClient.SqlCommand
    Public Shared cmdSqlServerDelete As New SqlClient.SqlCommand
    Public Shared cmdSqlServerUpdate As New SqlClient.SqlCommand
    Public Shared cmdSqlServerInsert As New SqlClient.SqlCommand

    Public Shared cnSqlServer As New SqlClient.SqlConnection(BuildCnnStr())

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         '-------------------------------------------- 
         'Настройка команды SELECT 
         '-------------------------------------------- 
        cmdSqlServerSelect.Connection = cnSqlServer
        cmdSqlServerSelect.CommandType = CommandType.StoredProcedure
        cmdSqlServerSelect.CommandText = "dbo.spSelect"
         '-------------------------------------------- 
         'Настройка команды UPDATE 
         '-------------------------------------------- 
        buildParams(cmdSqlServerUpdate, "dbo.spUpdateTelo")
        cmdSqlServerUpdate.Parameters("@CityIdold").SourceVersion = DataRowVersion.Original
         '-------------------------------------------- 
         'Настройка команды DELETE 
         '-------------------------------------------- 
        buildParams(cmdSqlServerDelete, "dbo.spDeleteTelo")
        cmdSqlServerDelete.Parameters("@TeloId").SourceVersion = DataRowVersion.Original
        cmdSqlServerDelete.Parameters("@CityId").SourceVersion = DataRowVersion.Original
         '-------------------------------------------- 
         'Настройка команды INSERT 
         '-------------------------------------------- 
        buildParams(cmdSqlServerInsert, "dbo.spInsertTelo")
         '--------------------------------------------------------------------- 

        daTest.TableMappings.Add("Table", "TeloTbl")
        daTest.TableMappings.Add("Table1", "CityTbl")

        daTest.SelectCommand = cmdSqlServerSelect
        daTest.UpdateCommand = cmdSqlServerUpdate
        daTest.InsertCommand = cmdSqlServerInsert
        daTest.DeleteCommand = cmdSqlServerDelete

        daTest.Fill(dsTest)

        dgTelo.DataSource = dsTest.Tables("TeloTbl")
        dgCity.DataSource = dsTest.Tables("CityTbl")

    End Sub

     'Функция создания строки подключения к серверу 
    Public Shared Function BuildCnnStr() As String

        Dim strTemp As String

        strTemp = "data source=409ADMIN;integrated security=SSPI;initial catalog=delupins;persist security info=False"
         'strTemp = "data source=STALKER;integrated security=FALSE;initial catalog=delupins;persist security info=False;uid=sa;pwd=" 

        Return strTemp

    End Function

     'При нажатии на эту кнопку обновления отправляются на сервер. 
    Private Sub bttnDelonServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnDelonServer.Click

              daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Deleted))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Added))
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.ModifiedCurrent))

        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.Deleted))
        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.Added))
        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.ModifiedCurrent))

            End Sub

     'Подпрограмма для построения cmdSqlServerUpdate,cmdSqlServerInsert,cmdSqlServerDelete. 
     'cmdSqlServer - команда, spName имя хранимой процедуры. 
    Public Sub buildParams(ByVal cmdSqlServer As SqlClient.SqlCommand, ByVal spName As String)

        cmdSqlServer.Connection = cnSqlServer
        cmdSqlServer.CommandType = CommandType.StoredProcedure
        cmdSqlServer.CommandText = spName

        cmdSqlServer.Parameters.Add("@TeloId", SqlDbType.Int,  4 , "teloid")
        cmdSqlServer.Parameters.Add("@City", SqlDbType.Char,  40 , "city")
        cmdSqlServer.Parameters.Add("@FIO", SqlDbType.Char,  50 , "fio")
        cmdSqlServer.Parameters.Add("@CityId", SqlDbType.Char,  40 , "CityId")
        cmdSqlServer.Parameters.Add("@CityIdold", SqlDbType.Char,  40 , "CityId")

    End Sub

PROCEDURE dbo.spDeleteTelo(@teloid int= 9999 ,@cityid char( 40 )= 'xxxx',@FIO char(50)=null,@City char(40)=null,@CityIdOld char(40)=null) 
AS

BEGIN TRANSACTION deleteitem

delete dbo.telotbl where telotbl.teloid=@teloid
delete dbo.citytbl where citytbl.cityid=@cityid

IF @@ERROR<> 0 
BEGIN
  ROLLBACK TRANSACTION deleteitem
  RETURN - 1 
END
ELSE
BEGIN
  COMMIT TRANSACTION deleteitem
  RETURN  0 
END

PROCEDURE dbo.spInsertTelo
(@FIO char( 50 )= 'XXX',@City char(40)='XXX',@CityId char(40)='XXX',@teloid int=null,@CityIdOld char(40)=null) 
AS

BEGIN TRANSACTION insertitem

if @cityid<> 'XXX'  
INSERT INTO dbo.CityTbl(CityId) VALUES (@CityId)
if @FIO<> 'XXX' 
INSERT INTO dbo.TeloTbl(FIO, City)
VALUES(@FIO,@City)

IF @@ERROR<> 0 
BEGIN
  ROLLBACK TRANSACTION insertitem
  RETURN - 1 
END
ELSE
BEGIN
  COMMIT TRANSACTION insertitem
  RETURN  0 
END

PROCEDURE dbo.spUpdateTelo
(@TeloId int=NULL,@FIO char( 50 )=NULL,@City char( 40 )=NULL,@CityId char( 40 )=NULL,@CityIdold char( 40 )=NULL)
AS

BEGIN TRANSACTION updateitem

UPDATE dbo.CityTbl SET CityId=isnull(@CityId,Cityid) WHERE CityId=@CityIdold
UPDATE dbo.TeloTbl SET FIO=isnull(@FIO,fio),City=isnull(@City,city) WHERE TELOID=@TELOID

IF @@ERROR<> 0 
BEGIN
  ROLLBACK TRANSACTION updateitem
  RETURN - 1 
END
ELSE
BEGIN
  COMMIT TRANSACTION updateitem
  RETURN  0 
END

Public Shared dsTest As New DataSet
    Public Shared daTest As New SqlClient.SqlDataAdapter

    Public Shared cmdSqlServerSelect As New SqlClient.SqlCommand

    Public Shared cmdSqlServerTelo As New SqlClient.SqlCommand
    Public Shared cmdSqlServerCity As New SqlClient.SqlCommand

    Public Shared cnSqlServer As New SqlClient.SqlConnection(BuildCnnStr())

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         '-------------------------------------------- 
         'Настройка команды SELECT 
         '-------------------------------------------- 
        cmdSqlServerSelect.Connection = cnSqlServer
        cmdSqlServerSelect.CommandType = CommandType.StoredProcedure
        cmdSqlServerSelect.CommandText = "dbo.spSelect"

        daTest.SelectCommand = cmdSqlServerSelect
         '-------------------------------------------- 
         'Настройка команды для TeloTbl 
         '-------------------------------------------- 
        cmdSqlServerTelo.Connection = cnSqlServer
        cmdSqlServerTelo.CommandType = CommandType.StoredProcedure
        cmdSqlServerTelo.CommandText = "spTeloUniv"

        cmdSqlServerTelo.Parameters.Add("@TeloId", SqlDbType.Int,  4 , "teloid")
        cmdSqlServerTelo.Parameters.Add("@City", SqlDbType.Char,  40 , "city")
        cmdSqlServerTelo.Parameters.Add("@FIO", SqlDbType.Char,  50 , "fio")
        cmdSqlServerTelo.Parameters.Add("@flag", SqlDbType.Int)
         '-------------------------------------------- 
         'Настройка команды для CityTbl 
         '-------------------------------------------- 
        cmdSqlServerCity.Connection = cnSqlServer
        cmdSqlServerCity.CommandType = CommandType.StoredProcedure
        cmdSqlServerCity.CommandText = "spCityUniv"

        cmdSqlServerCity.Parameters.Add("@CityId", SqlDbType.Char,  40 , "CityId")
        cmdSqlServerCity.Parameters.Add("@CityIdOld", SqlDbType.Char,  40 , "CityId")
        cmdSqlServerCity.Parameters.Add("@flag", SqlDbType.Int)
         '------------------------------------------------ 

        daTest.TableMappings.Add("Table", "TeloTbl")
        daTest.TableMappings.Add("Table1", "CityTbl")

        daTest.Fill(dsTest)

        dgTelo.DataSource = dsTest.Tables("TeloTbl")
        dgCity.DataSource = dsTest.Tables("CityTbl")

    End Sub

     'Функция создания строки подключения к серверу 
    Public Shared Function BuildCnnStr() As String

        Dim strTemp As String

        strTemp = "data source=409ADMIN;integrated security=SSPI;initial catalog=delupins;persist security info=False"
         'strTemp = "data source=STALKER;integrated security=FALSE;initial catalog=delupins;persist security info=False;uid=sa;pwd=" 

        Return strTemp

    End Function
    
    Private Sub bttnDelonServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnDelonServer.Click
         '-----Обнавляем таблицу TeloTbl---------------------------- 
        daTest.UpdateCommand = cmdSqlServerTelo
        daTest.InsertCommand = cmdSqlServerTelo
        daTest.DeleteCommand = cmdSqlServerTelo

        cmdSqlServerTelo.Parameters("@flag").Value =  3 
        cmdSqlServerTelo.Parameters("@TeloId").SourceVersion = DataRowVersion.Original
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Deleted))

        cmdSqlServerTelo.Parameters("@flag").Value =  2 
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.Added))

        cmdSqlServerTelo.Parameters("@flag").Value =  1 
        daTest.Update(dsTest.Tables("TeloTbl").Select("", "", DataViewRowState.ModifiedCurrent))

         '----Обнавляем таблицу СityTbl------------------------------ 
        daTest.UpdateCommand = cmdSqlServerCity
        daTest.InsertCommand = cmdSqlServerCity
        daTest.DeleteCommand = cmdSqlServerCity

        cmdSqlServerCity.Parameters("@flag").Value =  3 
        cmdSqlServerCity.Parameters("@CityId").SourceVersion = DataRowVersion.Original
        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.Deleted))

        cmdSqlServerCity.Parameters("@flag").Value =  2 
        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.Added))

        cmdSqlServerCity.Parameters("@flag").Value =  1 
        cmdSqlServerCity.Parameters("@CityIdOld").SourceVersion = DataRowVersion.Original
        daTest.Update(dsTest.Tables("CityTbl").Select("", "", DataViewRowState.ModifiedCurrent))

    End Sub

PROCEDURE dbo.spCityUniv
(@CityId char( 40 )=NULL,@CityIdold char( 40 )=NULL,@flag int=NULL)

AS

if @flag= 1 
UPDATE dbo.CityTbl SET CityId=isnull(@CityId,Cityid) WHERE CityId=@CityIdold
if @flag= 2 
INSERT INTO dbo.CityTbl(CityId) VALUES (@CityId)
if @flag= 3 
delete dbo.citytbl where citytbl.cityid=@cityid

	RETURN 

PROCEDURE dbo.spTeloUniv
(@TeloId int=NULL,@FIO char( 50 )=NULL,@City char( 40 )=NULL,@flag int=NULL)

AS

if @flag= 1 
UPDATE dbo.TeloTbl SET FIO=isnull(@FIO,fio),City=isnull(@City,city) WHERE TELOID=@TELOID
if @flag= 2 
INSERT INTO dbo.TeloTbl(FIO, City) VALUES(@FIO,@City)
if @flag= 3 
delete dbo.telotbl where telotbl.teloid=@teloid

	RETURN 

Public Shared dsTest As New DataSet
    Public Shared daTest As New SqlClient.SqlDataAdapter

    Public Shared cmdSqlServerSelect As New SqlClient.SqlCommand

    Public Shared cmdSqlServerTelo As New SqlClient.SqlCommand
    Public Shared cmdSqlServerCity As New SqlClient.SqlCommand

    Public Shared cnSqlServer As New SqlClient.SqlConnection(BuildCnnStr())

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         '-------------------------------------------- 
         'Настройка команды SELECT 
         '-------------------------------------------- 
        cmdSqlServerSelect.Connection = cnSqlServer
        cmdSqlServerSelect.CommandType = CommandType.StoredProcedure
        cmdSqlServerSelect.CommandText = "dbo.spSelect"

        daTest.SelectCommand = cmdSqlServerSelect
         '-------------------------------------------- 
         'Настройка команды для TeloTbl 
         '-------------------------------------------- 
        buildCmd(cmdSqlServerTelo, "spTeloUniv")

        cmdSqlServerTelo.Parameters.Add("@TeloId", SqlDbType.Int,  4 , "teloid")
        cmdSqlServerTelo.Parameters.Add("@City", SqlDbType.Char,  40 , "city")
        cmdSqlServerTelo.Parameters.Add("@FIO", SqlDbType.Char,  50 , "fio")
        cmdSqlServerTelo.Parameters.Add("@flag", SqlDbType.Int)
         '-------------------------------------------- 
         'Настройка команды для CityTbl 
         '-------------------------------------------- 
        buildCmd(cmdSqlServerCity, "spCityUniv")

        cmdSqlServerCity.Parameters.Add("@CityId", SqlDbType.Char,  40 , "CityId")
        cmdSqlServerCity.Parameters.Add("@CityIdOld", SqlDbType.Char,  40 , "CityId")
        cmdSqlServerCity.Parameters.Add("@flag", SqlDbType.Int)
         '------------------------------------------------ 

        daTest.TableMappings.Add("Table", "TeloTbl")
        daTest.TableMappings.Add("Table1", "CityTbl")

        daTest.Fill(dsTest)

        dgTelo.DataSource = dsTest.Tables("TeloTbl")
        dgCity.DataSource = dsTest.Tables("CityTbl")

    End Sub

     'Функция создания строки подключения к серверу 
    Public Shared Function BuildCnnStr() As String

        Dim strTemp As String

        strTemp = "data source=409ADMIN;integrated security=SSPI;initial catalog=delupins;persist security info=False"
         'strTemp = "data source=STALKER;integrated security=FALSE;initial catalog=delupins;persist security info=False;uid=sa;pwd=" 

        Return strTemp

    End Function
    
    Private Sub bttnDelonServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnDelonServer.Click
         '-----Обнавляем таблицу TeloTbl---------------------------- 
        getChanges(cmdSqlServerTelo, "TeloTbl")

         '----Обнавляем таблицу СityTbl------------------------------ 
        getChanges(cmdSqlServerCity, "CityTbl")

    End Sub
     'Подпрограмма для настройки объекта Command. 
    Public Sub buildCmd(ByVal cmdSqlServer As SqlClient.SqlCommand, ByVal spName As String)
        cmdSqlServer.Connection = cnSqlServer
        cmdSqlServer.CommandType = CommandType.StoredProcedure
        cmdSqlServer.CommandText = spName
    End Sub

     'Подпрограмма для записи изменений на SQL Server 
    Public Sub getChanges(ByVal cmdSqlServer As SqlClient.SqlCommand, ByVal tblName As String)

        daTest.UpdateCommand = cmdSqlServer
        daTest.InsertCommand = cmdSqlServer
        daTest.DeleteCommand = cmdSqlServer

        cmdSqlServer.Parameters("@flag").Value =  3 
        daTest.Update(dsTest.Tables(tblName).Select("", "", DataViewRowState.Deleted))

        cmdSqlServer.Parameters("@flag").Value =  2 
        daTest.Update(dsTest.Tables(tblName).Select("", "", DataViewRowState.Added))

        cmdSqlServer.Parameters("@flag").Value =  1 
        If tblName = "CityTbl" Then
            cmdSqlServerCity.Parameters("@CityIdOld").SourceVersion = DataRowVersion.Original
        End If
        daTest.Update(dsTest.Tables(tblName).Select("", "", DataViewRowState.ModifiedCurrent))

    End Sub