c# - 如何将最后插入的 id 转换为字符串？

Question

我有一个创建帐户页面，在该页面上我有一个按钮可以将所有详细信息插入到两个单独的表中，其中一个表 Pictures 依赖于通过 UserID 的 User 表 1:1 关系。

我已经编写了一些代码来尝试获取最后插入的 ID，以便我可以插入到图片表中:

    protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            try
            {
                OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=root; Password=commando;");
cn.Open();

                OdbcCommand cmd = new OdbcCommand("INSERT INTO User (Email, FirstName, SecondName, DOB, Location, Aboutme, username, password) VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + TextBox4.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "', '" + TextBox7.Text + "', '" + TextBox8.Text + "')", cn);
                OdbcCommand sc = new OdbcCommand("SELECT LAST_INSERT_ID()", cn);
                //convert LAST INSERT into string theUserId

                string filenameDB = Path.GetFileName(FileUpload1.FileName);
                string fileuploadpath = Server.MapPath("~/userdata/" + theUserId + "/uploadedimage/") + Path.GetFileName(FileUpload1.FileName);
                FileUpload1.SaveAs(fileuploadpath);
                string fileuploadpaths = ("~/userdata/" + theUserId + "/uploadedimage/") + filenameDB;
                Label10.Text = "Upload status: File uploaded!";
                OdbcCommand cm = new OdbcCommand("INSERT INTO Pictures (picturepath, UserId) VALUES ('" + fileuploadpaths + "', " + theUserId + ")", cn);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Label10.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;

            }
            //e.Authenticated = true;
            //Response.Redirect("Login.aspx");
            // Event useradded is true forward to login
        }
    }
}

不确定这是否正确，我还需要知道如何将 select 语句转换为字符串，以便我可以从 User 表中检索 UserID，请参阅数据库结构:

编辑

protected void Button1_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        try
        {
            OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=root; Password=commando;");


            OdbcCommand cmd = new OdbcCommand("INSERT INTO User (Email, FirstName, SecondName, DOB, Location, Aboutme, username, password) VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + TextBox4.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "', '" + TextBox7.Text + "', '" + TextBox8.Text + "')SELECT LAST_INSERT_ID()", cn);
            //OdbcCommand sc = new OdbcCommand("SELECT LAST_INSERT_ID()", cn);
            //convert LAST INSERT into string theUserId
            //using (DataTable dt = DataTier.ExecuteQuery(cmd))
            ////error for datatable and datatier
            //if (dt.Rows.Count == 1)
            //{
            //    //Read the new ID from the record that has just been inserted
            //    string theUserId = dt.Rows[0]["UserID"].ToString();
            using (OdbcDataReader reader = cmd.ExecuteReader())
            {
           
            string theUserId = String.Format("{0}", reader.GetString(0));

            string filenameDB = Path.GetFileName(FileUpload1.FileName);
            string fileuploadpath = Server.MapPath("~/userdata/" + theUserId + "/uploadedimage/") + Path.GetFileName(FileUpload1.FileName);
            FileUpload1.SaveAs(fileuploadpath);
            string fileuploadpaths = ("~/userdata/" + theUserId + "/uploadedimage/") + filenameDB;
            Label10.Text = "Upload status: File uploaded!";
            OdbcCommand cm = new OdbcCommand("INSERT INTO Pictures (picturepath, UserId) VALUES ('" + fileuploadpaths + "', " + theUserId + ")", cn);
            cn.Open();
            cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            Label10.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;

        }
        //e.Authenticated = true;
        //Response.Redirect("Login.aspx");
        // Event useradded is true forward to login
    }
}

Answer 1

您可以使用类似于以下的查询来发布记录，然后获取 ID。

 string sQuery = @"INSERT INTO [ExpenseType]
                                      (
                                         [ExpenseTypeName]
                                        ,[Deleted]
                                        ,[IsTaxable]
                                        ,[UpdatedDate]
                                        ,[UpdatedUser]
                                        ,[ParentCategoryComponentID]
                                        ,[CategoryComponentID]
                                        ,[NLNominalAccountID]
                                        ,[SYSTaxCodeID]
                                      )
                                      VALUES
                                      (
                                         @ExpenseTypeName
                                        ,@Deleted
                                        ,@IsTaxable
                                        ,@UpdatedDate
                                        ,@UpdatedUser
                                        ,@ParentCategoryComponentID
                                        ,@CategoryComponentID
                                        ,@NLNominalAccountID
                                        ,@SYSTaxCodeID
                                      )
                                      SELECT SCOPE_IDENTITY() AS 'ID' ";

                    using ( SqlCommand oSqlCommand = new SqlCommand( sQuery ) )
                    {
                        oSqlCommand.Parameters.AddWithValue( "@ExpenseTypeName", this.ExpenseTypeName );
                        oSqlCommand.Parameters.AddWithValue( "@Deleted", this.Deleted );
                        oSqlCommand.Parameters.AddWithValue( "@IsTaxable", this.IsTaxable );
                        oSqlCommand.Parameters.AddWithValue( "@UpdatedDate", base.GetUpdatedDate() );
                        oSqlCommand.Parameters.AddWithValue( "@UpdatedUser", base.GetUpdatedUser() );
                        oSqlCommand.Parameters.AddWithValue( "@ParentCategoryComponentID", this.ParentCategoryComponentID );
                        oSqlCommand.Parameters.AddWithValue( "@CategoryComponentID", this.CategoryComponentID );
                        oSqlCommand.Parameters.AddWithValue( "@NLNominalAccountID", this.NLNominalAccountID );
                        oSqlCommand.Parameters.AddWithValue( "@SYSTaxCodeID", this.SYSTaxCodeID );

                        using ( DataTable dt = DataTier.ExecuteQuery( oSqlCommand ) )
                        {
                            if ( dt.Rows.Count == 1 )
                            {
                                //Read the new ID from the record that has just been inserted
                                                                string RecordID =  dt.Rows[ 0 ][ "ID" ].ToString();
                            }
                        }
                    }

注意查询末尾的 SELECT SCOPE_IDENTITY() AS 'ID'。

和

          if ( dt.Rows.Count == 1 )
                        {
                            //Read the new ID from the record that has just been inserted
                            string RecordID =  dt.Rows[ 0 ][ "ID" ].ToString();
                        }