We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

How to connect database to Kanban

Hi,

I love Kanban and have finished my customizing.
Now I try to connect the board to a SQL server database for managing my business data.

Do you have any hint for me how to do this?


Kind regards,
Lars

8 Replies

SK Sarath Kumar P Syncfusion Team November 4, 2016 03:34 PM UTC

    
Thanks for contacting Syncfusion Support. 
 
You can manage your data by performing Kanban CRUD operations(edit, card drop, add, remove) with the help of Ajax Post Request.  
 
Please refer to the below codes,     
 
[KanbanFeatures.aspx]     
 
//Here you can perform Kanban CRUD operations(edit, card drop, add, remove) with the help of Ajax Post Request  
function kanbanComplete(args) {    
            var kbnUrl; 
            if (args.action == "add" && args.requestType == "save") 
                kbnUrl = "KanbanFeatures.aspx/AddAction"; 
            else if (args.requestType == "drop" || (args.action == "edit" && args.requestType == "save"))  
                kbnUrl = "KanbanFeatures.aspx/EditDropAction"; 
            else if(args.requestType == "delete") 
                kbnUrl = "KanbanFeatures.aspx/removeAction" 
            if (args.requestType == "delete" || args.requestType == "save" || args.requestType == "drop") { 
             var card = { 
                    "Id": args.data[0]["Id"], 
                    "Status": args.data[0]["Status"], 
                    "Assignee": args.data[0]["Assignee"], 
                    "Estimate": args.data[0]["Estimate"], 
                    "Summary": args.data[0]["Summary"] 
            }; 
            $.ajax({ 
                async: true, 
                type: "POST", 
                contentType: "application/json; charset=utf-8", 
                url: kbnUrl, 
                data: JSON.stringify(card), 
                dataType: "json", 
                success: function (res) { 
                }, 
                error: function (e) { 
                } 
            }); 
            } 
        } 
 
<ej:Kanban ID="Kanban" runat="server" KeyField="Status" AllowTitle="true"> 
                <Columns> 
                    <ej:KanbanColumn HeaderText="Backlog" Key="Open" ShowAddButton="true" /> // ShowAddButton— ”Add” toolbar to add kanban card. 
                    <ej:KanbanColumn HeaderText="In Progress" Key="InProgress" /> 
                    <ej:KanbanColumn HeaderText="Done" Key="Close" /> 
                </Columns> 
                <Fields Content="Summary" PrimaryKey="Id" /> 
                <CustomToolBarItems> 
                    <ej:KanbanCustomToolBarItems Template="#Delete" /> //Custom delete toolbar to delete card 
                </CustomToolBarItems> 
                <ClientSideEvents ToolbarClick="toolbarClick" ActionComplete="kanbanComplete" />  //Intailize actioncomplete event to perform Kanban CRUD Operations 
                <EditSettings AllowAdding="true" AllowEditing="true" EditMode="Dialog"> //Enable Add, Edit Settings to add, edit card 
                    <EditItems> 
                        <ej:KanbanEditItem Field="Id"> 
                        </ej:KanbanEditItem> 
                        <ej:KanbanEditItem Field="Status"></ej:KanbanEditItem> 
                        <ej:KanbanEditItem Field="Assignee"></ej:KanbanEditItem> 
                        <ej:KanbanEditItem Field="Estimate"> 
                        </ej:KanbanEditItem> 
                        <ej:KanbanEditItem Field="Summary"> 
                        </ej:KanbanEditItem> 
                    </EditItems> 
                </EditSettings> 
            </ej:Kanban> 
        </div> 
    </div> 
    <script type="text/javascript"> 
        function toolbarClick(args) {  //Here you can perform delete operation in the custom delete toolbar click event 
            if (args.itemName == "Delete" && this.element.find(".e-kanbancard").hasClass("e-cardselection")) { 
                var selectedcard = this.element.find(".e-cardselection"); 
                this.KanbanEdit.deleteCard(selectedcard.attr("id")); 
            } 
        } 
    </script> 
    //Customize custom delete tool bar 
    <script id="Delete" type="text/x-jsrender"> 
        <a class="e-customdelete  e-icon" /> 
    </script> 
    <style> 
        .e-customdelete:before { 
            content: "\e800"; 
            line-height: 26px; 
            min-height: 26px; 
            min-width: 14px; 
            display: inline-block; 
        } 
    </style> 
  
    
 
[KanbanFeatures.aspx.cs]     
 
DataTable dt = new DataTable("Task"); 
        protected void Page_Load(object sender, EventArgs e) 
        { 
            if (!IsPostBack) 
            { 
                //Render all kanban cards. 
                SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ToString()); 
                dt = new DataTable("Task"); 
                SqlCommand cmd = new SqlCommand(); 
                cmd.Connection = myConnection; 
                cmd.CommandText = "select * from Tasks"; 
                cmd.CommandType = CommandType.Text; 
                SqlDataAdapter da = new SqlDataAdapter(); 
                da.SelectCommand = cmd; 
                if (myConnection.State == ConnectionState.Closed) 
                { 
                    myConnection.Open(); 
                } 
                da.Fill(dt); 
                //To render all Kanban cards. 
                Kanban.DataSource = (DataTable)dt; 
                Kanban.DataBind(); 
            } 
        } 
        // Here you can perform Edit Operation 
        [WebMethod] 
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
        public static object EditDropAction(int Id, string Status, string Assignee, float Estimate, string Summary) 
        { 
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ToString()); 
            if (myConnection.State == ConnectionState.Closed) 
            { 
                myConnection.Open(); 
            } 
            string updateQuery = "UPDATE Tasks SET Assignee=@Assignee, Summary=@Summary, Estimate=@Estimate, Status=@Status where Id=@Id"; 
            SqlCommand UpdateCmd = new SqlCommand(updateQuery, myConnection); 
            UpdateCmd.Parameters.Add(new SqlParameter("@Assignee", SqlDbType.VarChar, 10)).Value = Assignee; 
            UpdateCmd.Parameters.Add(new SqlParameter("@Summary", SqlDbType.VarChar, 150)).Value = Summary; 
            UpdateCmd.Parameters.Add(new SqlParameter("@Estimate", SqlDbType.Float)).Value = Estimate; 
            UpdateCmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, 10)).Value = Status; 
            UpdateCmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int)).Value = Id;  
            UpdateCmd.ExecuteNonQuery(); 
            var ID = Id; 
            return ID; 
        } 
        // Here you can perform Add Operation 
        [WebMethod] 
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
        public static object AddAction(int Id, string Status, string Assignee, float Estimate, string Summary) 
        { 
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ToString()); 
            if (myConnection.State == ConnectionState.Closed) 
            { 
                myConnection.Open(); 
            } 
            string insertQuery = "INSERT INTO Tasks(Assignee,Summary,Estimate,Status,Id,RankId) VALUES (@Assignee,@Summary,@Estimate,@Status,@Id,@RankId)"; 
            SqlCommand InsertCmd = new SqlCommand(insertQuery, myConnection); 
            InsertCmd.Parameters.Add(new SqlParameter("@Assignee", SqlDbType.VarChar, 10)).Value = Assignee; 
            InsertCmd.Parameters.Add(new SqlParameter("@Summary", SqlDbType.VarChar, 150)).Value = Summary; 
            InsertCmd.Parameters.Add(new SqlParameter("@Estimate", SqlDbType.Float)).Value = Estimate; 
            InsertCmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, 10)).Value = Status; 
            InsertCmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int)).Value = Id; 
            InsertCmd.Parameters.Add(new SqlParameter("@RankId", SqlDbType.Int)).Value = 0; 
            InsertCmd.ExecuteNonQuery(); 
            var ID = Id; 
            return ID; 
        } 
        //Here you can perform Remove Operation 
        [WebMethod] 
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
        public static object removeAction(int Id, string Status, string Assignee, float Estimate, string Summary) 
        { 
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ToString()); 
            if (myConnection.State == ConnectionState.Closed) 
            { 
                myConnection.Open(); 
            } 
            string deleteQuery = "DELETE from Tasks where Id='" + Id + "'"; 
            SqlCommand deleteCmd = new SqlCommand(deleteQuery, myConnection); 
            deleteCmd.ExecuteNonQuery(); 
            var ID = Id; 
            return ID; 
        } 
  
Please refer to the attached sample and the video, 
     
 
 
Regards, 
Sarath Kumar P     



CR Carlos rojas replied to Sarath Kumar P September 16, 2017 02:42 PM UTC

    
Thanks for contacting Syncfusion Support. 
 
You can manage your data by performing Kanban CRUD operations(edit, card drop, add, remove) with the help of Ajax Post Request.  
 
Please refer to the below codes,     
 
[KanbanFeatures.aspx]     
 
//Here you can perform Kanban CRUD operations(edit, card drop, add, remove) with the help of Ajax Post Request  
function kanbanComplete(args) {    
            var kbnUrl; 
            if (args.action == "add" && args.requestType == "save") 
                kbnUrl = "KanbanFeatures.aspx/AddAction"; 
            else if (args.requestType == "drop" || (args.action == "edit" && args.requestType == "save"))  
                kbnUrl = "KanbanFeatures.aspx/EditDropAction"; 
            else if(args.requestType == "delete") 
                kbnUrl = "KanbanFeatures.aspx/removeAction" 
            if (args.requestType == "delete" || args.requestType == "save" || args.requestType == "drop") { 
             var card = { 
                    "Id": args.data[0]["Id"], 
                    "Status": args.data[0]["Status"], 
                    "Assignee": args.data[0]["Assignee"], 
                    "Estimate": args.data[0]["Estimate"], 
                    "Summary": args.data[0]["Summary"] 
            }; 
            $.ajax({ 
                async: true, 
                type: "POST", 
                contentType: "application/json; charset=utf-8", 
                url: kbnUrl, 
                data: JSON.stringify(card), 
                dataType: "json", 
                success: function (res) { 
                }, 
                error: function (e) { 
                } 
            }); 
            } 
        } 
 
<ej:Kanban ID="Kanban" runat="server" KeyField="Status" AllowTitle="true"> 
                <Columns> 
                    <ej:KanbanColumn HeaderText="Backlog" Key="Open" ShowAddButton="true" /> // ShowAddButton— ”Add” toolbar to add kanban card. 
                    <ej:KanbanColumn HeaderText="In Progress" Key="InProgress" /> 
                    <ej:KanbanColumn HeaderText="Done" Key="Close" /> 
                </Columns> 
                <Fields Content="Summary" PrimaryKey="Id" /> 
                <CustomToolBarItems> 
                    <ej:KanbanCustomToolBarItems Template="#Delete" /> //Custom delete toolbar to delete card 
                </CustomToolBarItems> 
                <ClientSideEvents ToolbarClick="toolbarClick" ActionComplete="kanbanComplete" />  //Intailize actioncomplete event to perform Kanban CRUD Operations 
                <EditSettings AllowAdding="true" AllowEditing="true" EditMode="Dialog"> //Enable Add, Edit Settings to add, edit card 
                    <EditItems> 
                        <ej:KanbanEditItem Field="Id"> 
                        </ej:KanbanEditItem> 
                        <ej:KanbanEditItem Field="Status"></ej:KanbanEditItem> 
                        <ej:KanbanEditItem Field="Assignee"></ej:KanbanEditItem> 
                        <ej:KanbanEditItem Field="Estimate"> 
                        </ej:KanbanEditItem> 
                        <ej:KanbanEditItem Field="Summary"> 
                        </ej:KanbanEditItem> 
                    </EditItems> 
                </EditSettings> 
            </ej:Kanban> 
        </div> 
    </div> 
    <script type="text/javascript"> 
        function toolbarClick(args) {  //Here you can perform delete operation in the custom delete toolbar click event 
            if (args.itemName == "Delete" && this.element.find(".e-kanbancard").hasClass("e-cardselection")) { 
                var selectedcard = this.element.find(".e-cardselection"); 
                this.KanbanEdit.deleteCard(selectedcard.attr("id")); 
            } 
        } 
    </script> 
    //Customize custom delete tool bar 
    <script id="Delete" type="text/x-jsrender"> 
        <a class="e-customdelete  e-icon" /> 
    </script> 
    <style> 
        .e-customdelete:before { 
            content: "\e800"; 
            line-height: 26px; 
            min-height: 26px; 
            min-width: 14px; 
            display: inline-block; 
        } 
    </style> 
  
    
 
[KanbanFeatures.aspx.cs]     
 
DataTable dt = new DataTable("Task"); 
        protected void Page_Load(object sender, EventArgs e) 
        { 
            if (!IsPostBack) 
            { 
                //Render all kanban cards. 
                SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ToString()); 
                dt = new DataTable("Task"); 
                SqlCommand cmd = new SqlCommand(); 
                cmd.Connection = myConnection; 
                cmd.CommandText = "select * from Tasks"; 
                cmd.CommandType = CommandType.Text; 
                SqlDataAdapter da = new SqlDataAdapter(); 
                da.SelectCommand = cmd; 
                if (myConnection.State == ConnectionState.Closed) 
                { 
                    myConnection.Open(); 
                } 
                da.Fill(dt); 
                //To render all Kanban cards. 
                Kanban.DataSource = (DataTable)dt; 
                Kanban.DataBind(); 
            } 
        } 
        // Here you can perform Edit Operation 
        [WebMethod] 
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
        public static object EditDropAction(int Id, string Status, string Assignee, float Estimate, string Summary) 
        { 
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ToString()); 
            if (myConnection.State == ConnectionState.Closed) 
            { 
                myConnection.Open(); 
            } 
            string updateQuery = "UPDATE Tasks SET Assignee=@Assignee, Summary=@Summary, Estimate=@Estimate, Status=@Status where Id=@Id"; 
            SqlCommand UpdateCmd = new SqlCommand(updateQuery, myConnection); 
            UpdateCmd.Parameters.Add(new SqlParameter("@Assignee", SqlDbType.VarChar, 10)).Value = Assignee; 
            UpdateCmd.Parameters.Add(new SqlParameter("@Summary", SqlDbType.VarChar, 150)).Value = Summary; 
            UpdateCmd.Parameters.Add(new SqlParameter("@Estimate", SqlDbType.Float)).Value = Estimate; 
            UpdateCmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, 10)).Value = Status; 
            UpdateCmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int)).Value = Id;  
            UpdateCmd.ExecuteNonQuery(); 
            var ID = Id; 
            return ID; 
        } 
        // Here you can perform Add Operation 
        [WebMethod] 
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
        public static object AddAction(int Id, string Status, string Assignee, float Estimate, string Summary) 
        { 
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ToString()); 
            if (myConnection.State == ConnectionState.Closed) 
            { 
                myConnection.Open(); 
            } 
            string insertQuery = "INSERT INTO Tasks(Assignee,Summary,Estimate,Status,Id,RankId) VALUES (@Assignee,@Summary,@Estimate,@Status,@Id,@RankId)"; 
            SqlCommand InsertCmd = new SqlCommand(insertQuery, myConnection); 
            InsertCmd.Parameters.Add(new SqlParameter("@Assignee", SqlDbType.VarChar, 10)).Value = Assignee; 
            InsertCmd.Parameters.Add(new SqlParameter("@Summary", SqlDbType.VarChar, 150)).Value = Summary; 
            InsertCmd.Parameters.Add(new SqlParameter("@Estimate", SqlDbType.Float)).Value = Estimate; 
            InsertCmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, 10)).Value = Status; 
            InsertCmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int)).Value = Id; 
            InsertCmd.Parameters.Add(new SqlParameter("@RankId", SqlDbType.Int)).Value = 0; 
            InsertCmd.ExecuteNonQuery(); 
            var ID = Id; 
            return ID; 
        } 
        //Here you can perform Remove Operation 
        [WebMethod] 
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
        public static object removeAction(int Id, string Status, string Assignee, float Estimate,