用事务同时插入数据到两个父子关系的表

[xiaoym1116]

建立一条信息，有名字、图片。


因为图片数是不固定的，所以打算另外建立一个photo表，把公司名插入到图片存到Company表中，并立刻取得自动增加的ID，把图片名插入到Photo表中，并把Company中新添加的ID记录到Photo表中的parentid中。

上网看了很多，都说是要用事务防止多用户同时操作造成的问题。事务还从来没有用过。

@@IDENTITY 比如这个，都不知道该用在哪里？CS里还是写在存储过程里？

网上给的都是简单几句最关键的部分，实在是没看懂，希望能给我几段完整简单，但是详细点的例子学习一下，谢谢了


修改的表结构

Company表

ID SchIndustry CompanyName Picture

Photo表

pid parentid path



以下是没分表之前的实现办法，只能插固定的图片数

cs部分

SqlCommand cmd = new SqlCommand("inserCompany", dbcom.conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter spr = new SqlParameter("@SchIndustry", SqlDbType.VarChar, 50);
spr.Value = this.SchIndustry.SelectedValue.ToString();
cmd.Parameters.Add(spr);

spr = new SqlParameter("@CompanyName", SqlDbType.VarChar, 50);
spr.Value = this.addCompanyName.Text;
cmd.Parameters.Add(spr);

spr = new SqlParameter("@Picture", SqlDbType.VarChar, 5000);
spr.Value = "Upload/" + UploadFileName + FU.FileName;
cmd.Parameters.Add(spr);

cmd.ExecuteNonQuery();


存储过程

CREATE procedure inserCompany
@SchIndustry varchar(50),@CompanyName varchar(50),@Picture varchar(50)

as
insert into Company (SchIndustry,CompanyName,Picture) values (@SchIndustry,@CompanyName,@Picture)
GO

[qituoshi]

编程/JAVA/.NET/C++ 群号：38850938

[xiaoym1116]

as
insert into Picnews (picclass,pictitle,piccontent,onoff) values (@ProductsClass,@Title,@Content,@State)

insert into photo (picid,path) values (@@Identity,@Picture)

GO

谢谢楼上朋友帮忙。终于实现同时插入了，有个问题

@@Identity是取得当前发布的ID，可是如果多用户操作，并发数大了会出问题么？

看了网上资料说，最好把操作锁定，事务完成后再解锁

这个改如何实现呢？分不够再加，麻烦大家了

[xiaoym1116]

事务的问题也解决了

现在就是多用户同时操作的话，不知道会不会有错误

[cat_hsfz]

@@IDENTITY用在存储过程里。

[nghf102]

收藏,学习了

[nghf102]

收藏,学习了

[xiaoym1116]

能给几行最关键的示例吗？比如我上面那段。麻烦了，谢谢

[lovehongyun]

先来个返回自动增长id的存储过程:

CREATE PROCEDURE dbo.usp_Area_Insert
(
	@AreaID int OUTPUT,
	@AreaName varchar(20) = NULL
)
AS
BEGIN
	SET NOCOUNT ON	
		
	INSERT INTO [Area]
	( 
		[AreaName]
	)
	VALUES
	(
		@AreaName
	)
	
	SET @AreaID = SCOPE_IDENTITY()

	SET NOCOUNT OFF	
END

[lovehongyun]

事务的(数据库中):

CREATE PROCEDURE dbo.usp_xxxx
(
	@xxx int
)
AS 
BEGIN

BEGIN TRAN
.........
..........
insert into ....
IF @@ERROR<>0 GOTO ERROR

COMMIT TRAN--提交事务	
RETURN
		
ERROR:		
ROLLBACK TRAN--回滚
RETURN

END

[lovehongyun]

cs里搞:

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction("SampleTransaction");

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // Attempt to roll back the transaction.
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);
            }
        }
    }
}

[程序员老干部]

set id=@@IDENTITY
insert 子表