如何使用存储过程进行插入/更新并使用视图绑定网格 [英] How to use stored procedure for insert/update and binding the grid using view

问题描述

嗨
我正在进行网格CRUD操作，我想分别使用存储过程和查看。这就是我尝试的内容



< b>我尝试了什么：

Hi I am working on the Grid CRUD operations and I want to use both Stored procedure and View separately.Here what I have tried

What I have tried:

<pre>protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            { bind();
            
            BindGrid();
           
            BindDepartment();
            }
        }

protected void bind()
        {
            cn.Open();
            SqlCommand cmd = new SqlCommand("Select * from Record", cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cn.Close();
            if (ds.Tables[0].Rows.Count > 0)
            {
                gvDetails.DataSource = ds;
                gvDetails.DataBind();
            }

        }
protected void gvDetails_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
        {
            DataTable dt = new DataTable();

            try
            {

                cn.Open();
                SqlCommand cmd = new SqlCommand("Select * from Record", cn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                cn.Close();
                gvDetails.DataSource = ds;


            }
            finally
            {


            }
        }

protected void INSERTEmployee(string Id, string name, string department, string age, string salary)
        {

            SqlCommand cmd = new SqlCommand();
            cn.Open();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "gvempdetails";

            cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@EMPLOYEEID", int.Parse(age.Trim())));
            cmd.Parameters.Add(new SqlParameter("@EMPLOYEENAME", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@DEPARTMENT", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@AGE", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@SALARY", int.Parse(salary.Trim())));

            cmd.Parameters["@Action"].Value = "INSERT";
            cmd.Parameters["@EMPLOYEEID"].Value = Id;
            cmd.Parameters["@EMPLOYEENAME"].Value = name;
            cmd.Parameters["@DEPARTMENT"].Value = department;
            cmd.Parameters["@AGE"].Value = age;
            cmd.Parameters["@SALARY"].Value = salary;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            gvDetails.DataSource = dt;
            gvDetails.DataBind();
            cn.Close();


        }
        protected void gvDetails_InsertCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)
        {
           if (e.CommandName.Equals("Add"))
            {

            GridEditFormInsertItem insertedItem = (GridEditFormInsertItem)e.Item;
            string EmployeeId = (insertedItem["EmpId"].Controls[0] as TextBox).Text;
            string EmployeeName = (insertedItem["EmpName"].Controls[0] as TextBox).Text;
            string Department = (insertedItem["Dep"].Controls[0] as TextBox).Text;
            string Age = (insertedItem["Age"].Controls[0] as TextBox).Text;
            string Sal = (insertedItem["Sal"].Controls[0] as TextBox).Text;
            INSERTEmployee(EmployeeId, EmployeeName, Department, Age, Sal);
            bind();
}}

<pre lang="HTML"><pre><div>
            <table style="margin-top:-25px;margin-bottom:100px;width:100%" >
                <tr>
                    <td>

            <telerik:RadGrid ID="gvDetails"  runat="server"  Skin="WebBlue"

            GridLines="None" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" 

             OnNeedDataSource="gvDetails_NeedDataSource" OnDeleteCommand="gvDetails_DeleteCommand" 

           OnInsertCommand="gvDetails_InsertCommand" OnUpdateCommand="gvDetails_UpdateCommand" EnableAJAX="True" PageSize="15"  > 
            <PagerStyle Mode="NextPrevAndNumeric"></PagerStyle> 
              
            <MasterTableView DataKeyNames="EmpId" GridLines="None" Width="100%" CommandItemDisplay ="Top" > 
              
                <Columns> 
                    
                    <telerik:GridBoundColumn DataField="EmpId" ColumnEditorID="EmpId" HeaderText="EmployeeID" UniqueName="EmpId" >  
                    </telerik:GridBoundColumn> 
                    <telerik:GridBoundColumn DataField="EmpName" HeaderText="EmployeeName" UniqueName="EmpName">  
                    </telerik:GridBoundColumn> 
                    <telerik:GridBoundColumn DataField="Dep" HeaderText="Department" UniqueName="Dep">  
                    </telerik:GridBoundColumn> 
                    <telerik:GridBoundColumn DataField="Age" HeaderText="Age" UniqueName="Age">  
                    </telerik:GridBoundColumn> 
                    <telerik:GridBoundColumn DataField="Sal" HeaderText="Salary" UniqueName="Sal">  
                    </telerik:GridBoundColumn> 
                    <telerik:GridButtonColumn CommandName="Delete" Text="Delete" UniqueName="Delete">  
                    </telerik:GridButtonColumn> 
                    <telerik:GridButtonColumn CommandName="Edit" Text="Edit" UniqueName="Edit"> 
                    </telerik:GridButtonColumn> 
                </Columns> 

                <EditFormSettings ColumnNumber="2" CaptionFormatString="Edit details for employee with ID {0}" 

                    CaptionDataField="EmpId">  
                    <FormTableItemStyle Wrap="False"></FormTableItemStyle> 
                    <FormCaptionStyle CssClass="EditFormHeader"></FormCaptionStyle> 
                    <FormMainTableStyle CellSpacing="0" CellPadding="3" Width="100%" /> 
                    <FormTableStyle GridLines="Horizontal" CellSpacing="0" CellPadding="2" CssClass="module" 

                        Height="110px" Width="100%" /> 
                    <FormTableAlternatingItemStyle Wrap="False"></FormTableAlternatingItemStyle> 
                    <FormStyle Width="100%" BackColor="#EEF2EA"></FormStyle> 
                    <EditColumn UpdateText="Update record" UniqueName="EditCommandColumn1" CancelText="Cancel">  
                    </EditColumn> 
                    <FormTableButtonRowStyle HorizontalAlign="Right" CssClass="EditFormButtonRow"></FormTableButtonRowStyle> 
                </EditFormSettings> 
                <ExpandCollapseColumn Visible="False">  
                    <HeaderStyle Width="19px"></HeaderStyle> 
                </ExpandCollapseColumn> 
                <RowIndicatorColumn Visible="False">  
                    <HeaderStyle Width="20px" /> 
                </RowIndicatorColumn> 
            </MasterTableView> 
        </telerik:RadGrid>
                        </td>
                    </tr>
                </table> 
        </div>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[gvempdetails]

     @Action VARCHAR (10),
     @EMPLOYEEID INT =0,
     @EMPLOYEENAME VARCHAR(100)='',
     @DEPARTMENT VARCHAR(100)= '',
     @AGE VARCHAR(30)= '',
     @SALARY INT = 0
     
AS
BEGIN
      SET NOCOUNT ON 
     IF @Action = 'SELECT'
      BEGIN
      SELECT EmpId,EmpName,Dep,Age,Sal
      FROM gvdetails17
       END
       
     IF @Action='INSERT'
       BEGIN
       INSERT INTO gvdetails17(EmpId,EmpName,Dep,Age,Sal) VALUES(@EMPLOYEEID,@EMPLOYEENAME,@DEPARTMENT,@AGE,@SALARY)
        END       
            
     IF @Action ='UPDATE'
      BEGIN
      UPDATE gvdetails17 SET EmpName=@EMPLOYEENAME,Dep=@DEPARTMENT,Age=@AGE,Sal=@SALARY WHERE EmpId=@EMPLOYEEID
      END
      
      
     IF @Action='DELETE'
      BEGIN
      DELETE FROM gvdetails17 where EmpId=@EMPLOYEEID
      END
      SET NOCOUNT OFF
      END

推荐答案

closing this post by posting as solution from comments,



Validate for null values of an object before accessing the members of it

validate for proper numeric format string before casting it an integer, you shall use Int32.TryParse Method [^] to convert to an int value, but you will have to figure out why the value turned out be null at the first place.

closing this post by posting as solution from comments,

Validate for null values of an object before accessing the members of it
validate for proper numeric format string before casting it an integer, you shall use Int32.TryParse Method [^] to convert to an int value, but you will have to figure out why the value turned out be null at the first place.

cmd.Parameters.Add(new SqlParameter("@EMPLOYEEID", int.Parse(id.Trim())));

这篇关于如何使用存储过程进行插入/更新并使用视图绑定网格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！