C＃中的Asp.net webform将SQL表标识插入为fk [英] Asp.net webform in C# insert SQL table identity as fk

问题描述

我正在使用C＃编写ASP.NET网站。我的想法是通过网站将数据插入SQL Server数据库。



我有一个存储过程用于检索身份并将其作为外键写入另一个表。我的问题是如何用C＃编写Web表单代码。 ASP.NET，因为我写的所有内容都有一些错误。



这是程序并且运行正常。

I'm working on an ASP.NET website in C#. The idea is to insert data into a SQL Server database through the website.

I have a stored procedure for retrieval of identity and writing it to another table as foreign key. My problem is how to write web form code in C# & ASP.NET, because everything I wrote has some errors in it.

This is procedure and works fine.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TEST]
	// Table1 
	@col1 NVARCHAR(MAX)
	,@col2 NVARCHAR(MAX)
	,@col3 NVARCHAR(MAX)
	,@col4 NVARCHAR(MAX)
	//Table 2 
	@FileName VARCHAR(50)
	,@FilePath VARCHAR(200)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Table_Var TABLE (ID1 INT)

	INSERT INTO Table1 (
		col1,col2,col3,col4
		)
	OUTPUT inserted.ID1
	INTO @Table_Var(ID1)
	SELECT @col1,@col2,@col3,@col4

	INSERT INTO Table2 (
		colID,FileName,FilePath
		)
	SELECT ID1,@FileName,@FilePath
	FROM @Table_Var
END

ID1 是表1中的标识和表2中的外键  col。



有没有人有想法？谢谢！



我的尝试：



想法是有一些列有关产品的详细信息（表1），另一个表中我将存储产品图片（表2）。



这是不起作用的代码：

ID1 is identity in Table1 and foreign key in Table2 in column col.

Does anyone have an idea? Thanks!

What I have tried:

The idea is to have some columns for details about products (Table 1) and in the other table I will store pictures of products (Table2).

This is the code that's not working:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e) { }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        string query = "TEST"; int ID; string connect = @"Server=.\SQLExpress;Database=database;Trusted_Connection=Yes;";
        using (SqlConnection conn = new SqlConnection(connect))
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@col1", col1.Text);
                cmd.Parameters.AddWithValue("@col2", col2.Text);
                cmd.Parameters.AddWithValue("@col3", col3.Text);
                cmd.Parameters.AddWithValue("@col4", col4.Text);
                cmd.Parameters.Add("@ID1", SqlDbType.Int, 0, "ID1");
                cmd.Parameters["@ID1"].Direction = ParameterDirection.Output; conn.Open();
                cmd.ExecuteNonQuery();
                ID = (int)cmd.Parameters["@ID1"].Value;
            }
            conn.Close();
            conn.Dispose();
        }
    }
    protected void btnUploadImage_Click(object sender, EventArgs e)
    {
        if (FileUploadImage.PostedFile != null)
        {
            string FileName = Path.GetFileName(FileUploadImage.PostedFile.FileName);
            FileUploadImage.SaveAs(Server.MapPath("image/" + FileName));
            String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["databaseConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
            string strQuery = "insert into Table2(colID, FileName, FilePath)" + " values(@ID, @FileName, @FilePath)";
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.Parameters.Add("@ID");
            cmd.Parameters.AddWithValue("@FileName", FileName);
            cmd.Parameters.AddWithValue("@FilePath", "image/" + FileName);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con; try
            {
                con.Open(); cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                con.Close(); con.Dispose();
            }
        }
    }
}

推荐答案

根据评论..

based on the comments..

protected void btnInsert_Click(object sender, EventArgs e)
       {
           string spName = "TEST"; int ID;
           string conString = @"Server=.\SQLExpress;Database=database;Trusted_Connection=Yes;";
           if (FileUploadImage.PostedFile != null)
           {
               string FileName = Path.GetFileName(FileUploadImage.PostedFile.FileName);
               FileUploadImage.SaveAs(Server.MapPath("image/" + FileName));

               using (SqlConnection conn = new SqlConnection(conString))
               {
                   using (SqlCommand cmd = new SqlCommand(spName, conn))
                   {
                       cmd.CommandType = CommandType.StoredProcedure;
                       cmd.Parameters.AddWithValue("@col1", col1.Text);
                       cmd.Parameters.AddWithValue("@col2", col2.Text);
                       cmd.Parameters.AddWithValue("@col3", col3.Text);
                       cmd.Parameters.AddWithValue("@col4", col4.Text);
                       cmd.Parameters.AddWithValue("@FileName", FileName);
                       cmd.Parameters.AddWithValue("@FilePath", "image/" + FileName);
                       cmd.CommandType = CommandType.StoredProcedure;
                       cmd.ExecuteNonQuery();

                   }
                   conn.Close();
                   conn.Dispose();
               }
           }

       }

这篇关于C＃中的Asp.net webform将SQL表标识插入为fk的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！