SQL Server I want to display identity column populated in textbox that is generated by database using ADO.NET / ASP.NET MVC

nwo49xxi 于 2023-10-15 发布在 .NET

关注(0)|答案(1)|浏览(110)

Model class Department :

[Table("Department")]
public class Department
{
    [Display(Name ="Sno")]
    public int id { get; set; }
    [Display(Name = "Department_Id")]
    public String Dno { get; set; }
    [Required]
    [Display(Name ="Department_Name")]
    public String Dname { get; set; }
}

Repository class

public List<Department> GetDepartmentList()
{
        connection();
        List<Department> list = new List<Department>();
        SqlCommand cmd = new SqlCommand("SpDepartmentList", con);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        con.Open();
        da.Fill(dt);
        con.Close();

        foreach (DataRow dr in dt.Rows)
        {
            list.Add(
                new Department
                {
                    id = Convert.ToInt32(dr["id"]),
                    Dno = Convert.ToString(dr["Dno"]),
                    Dname = Convert.ToString(dr["Dname"]),
                    //DnoList = Convert.To(dr["DnoList"])
                });
        }

        return list;
}

Submitting data using ADO.NET in the Repository class:

public bool AddDepartment(Department obj)
{
        connection();

        SqlCommand cmd = new SqlCommand("spAddDepartments", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@Dname", obj.Dname);

        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();

        if (i >= 1)
        {
            return true;
        }
        else
        {
            return false;
        }
}

Controller

[HttpGet]
public ActionResult AddDepartment()
{
    // Here I want get Dno id displayed that is generated by 
    // database with that id I have to submit data to data table
    return View();
}

[HttpPost]
public ActionResult AddDepartment(Department Dep)
{
        try
        {
            if (ModelState.IsValid)
            {
                DepartmentRep Repo = new DepartmentRep();

                if (Repo.AddDepartment(Dep))
                {
                    ModelState.Clear();
                    ViewBag.Message = "Details added successfully";
                }
            }

            return View();
        }
        catch
        {
            return View();
        }
}

Table definition:

CREATE TABLE [dbo].[Department] 
(
    [id] INT IDENTITY (1, 1) NOT NULL,
    [Dno] AS (CONCAT('Dno_', [id])),
    [Dname] VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Dno] ASC)
);

I have populate Dno that is an identity automatically generated by database. With that id I have to submit data to database in to the table using ADO.NET.

Please can anyone help me?

I am new to this website if I did any mistake please excuse me.

sql-server

来源：https://stackoverflow.com/questions/77170075/i-want-to-display-identity-column-populated-in-textbox-that-is-generated-by-data

1条答案

按热度按时间

iezvtpos1#

To display the Dno (identity column) generated by the database when adding a new Department , you can follow these steps:

Modify your AddDepartment action method to fetch the generated Dno after inserting the data into the database using ADO.NET.
Pass the generated Dno to your view, so it can be displayed to the user.

Here's how you can modify your code:

Controller (Add the OUTPUT clause to the SQL query):

[HttpPost]
public ActionResult AddDepartment(Department Dep)
{
    try
    {
        if (ModelState.IsValid)
        {
            DepartmentRep Repo = new DepartmentRep();

            int generatedDno = Repo.AddDepartment(Dep); // Get the generated Dno

            if (generatedDno > 0)
            {
                ModelState.Clear();
                ViewBag.Message = "Details added successfully";
                ViewBag.GeneratedDno = generatedDno; // Pass the generated Dno to the view
            }
        }

        return View();
    }
    catch
    {
        return View();
    }
}

Repository (Return the generated Dno):

public int AddDepartment(Department obj)
{
    connection();

    SqlCommand cmd = new SqlCommand("spAddDepartments", con);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@Dname", obj.Dname);

    SqlParameter outputParam = new SqlParameter("@GeneratedDno", SqlDbType.Int)
    {
        Direction = ParameterDirection.Output // Define an OUTPUT parameter to get the generated Dno
    };
    cmd.Parameters.Add(outputParam);

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();

    if (outputParam.Value != DBNull.Value)
    {
        return Convert.ToInt32(outputParam.Value); // Return the generated Dno
    }
    else
    {
        return 0;
    }
}

View (Display the generated Dno):

@{
    ViewBag.Title = "AddDepartment";
}

<h2>Add Department</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Department</h4>
        <hr />

        <div class="form-group">
            @Html.LabelFor(model => model.Dname, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Dname, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Dname, "", new { @class = "text-danger" })
            </div>
        </div>

        @if (ViewBag.GeneratedDno > 0)
        {
            <div class="form-group">
                <label class="control-label col-md-2">Generated Dno:</label>
                <div class="col-md-10">
                    @ViewBag.GeneratedDno
                </div>
            </div>
        }

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

In this modified code:

The AddDepartment method in your repository now returns the generated Dno as an int . It also defines an OUTPUT parameter in the SQL command to retrieve the generated Dno .
The controller's AddDepartment action method now fetches the generated Dno from the repository and passes it to the view using ViewBag .
The view checks if ViewBag.GeneratedDno is greater than 0 (indicating a valid generated Dno ) and displays it to the user if available.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server I want to display identity column populated in textbox that is generated by database using ADO.NET / ASP.NET MVC

1条答案

相关问题

热门标签

最新问答