Asp.net C# JQuery draggable item save position in sql database
Question
Answers
-
User-1330468790 posted
Hi Schlumpinator,
Firstly, we need to confirm if we want to achieve the same target.
1.After you drag and drop the image, the position of the image will be stored in the db. The method we want to take is ajax post + WebMethod.
2.If you or other people load the page and people will see that the image is located in the last position.
3.We take user id as '1' to simplify the scenario.
Then, I need to point out few mistakes you made which might be helpful for your following development.
1.In your code, you wrong used the web method as the attribute should be added on the method which is static.
2.If you call the web method through ajax, you'd better directly use the url for the web method instead of nested url which possibly is incorrect.
3.If you are willing to make the picture rendered in the same position, you should take the coords from the same position field. However, what I can see is that you take position x and y coordinates from offset and add the x and y coordinates to position property of Css style.
PS: Here I takes the "id" as the identifier of the element so that the page can capture it and assign the value to its position style.
More details, you could refer to below code and focus on the content in yellow background:
.aspx page: Script + HTML
<script type="text/javascript"> $(function () { $("#d1").draggable( { drag: function (event, ui) { $("#d1").css("opacity", "0.6"); // Semi-transparent when dragging }, stop: function (event, ui) { saveCoords(ui.position.left, ui.position.top, $('#d1').attr('id')); $("#d1").css("opacity", "1.0"); // Full opacity when stopped }, cursor: "move" }); }); function saveCoords(x, y, el, userid) { $.ajax({ type: "POST", url: "DraggableAndSaveCoords.aspx/SaveCoords", data: "{x: '" + x + "', y: '" + y + "', element: '" + el + "', userid: '1'}", contentType: "application/json; charset=utf-8", dataType: "json", success: function (response) {
// if '1', it means that the insert is successful. console.log(response); }, error: function (response) { alert(response.responseText); } }); } </script> </head> <body> <form id="form1" runat="server"> <div> <img src="Images/personbg.png" alt="" id="d1" width="30" height="30" runat="server" /> </div> </form> </body>Code behind:
protected void Page_Load(object sender, EventArgs e) { SetCoordsForElement(); } [WebMethod] public static int SaveCoords(int x, int y, string element, int userid) { string sql = "INSERT INTO [Coords] VALUES (@xPos, @yPos, @element, @userid)"; SqlParameter[] parameters = { new SqlParameter("@xPos", x), new SqlParameter("@yPos",y), new SqlParameter("@element", element), new SqlParameter("@userid", userid) }; return ExecuteNonQuery(sql, parameters); } public void SetCoordsForElement() { string sql = "SELECT TOP 1 xPos, yPos, Element FROM [Coords] WHERE UserID = @UserID ORDER BY ID DESC"; SqlParameter[] parameters = { new SqlParameter("@UserID", 1) }; DataTable dt = SelectFromDatabase(sql, parameters); foreach(DataRow row in dt.Rows) { HtmlControl ctl = (HtmlControl)this.FindControl(row["Element"].ToString()); if(ctl != null) { ctl.Style.Add("left", row["xPos"].ToString() + "px"); ctl.Style.Add("top", row["yPos"].ToString() + "px"); } } } public static DataTable SelectFromDatabase(string sql, SqlParameter[] parameters) { string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { if (parameters != null) { cmd.Parameters.AddRange(parameters); } using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); sda.Fill(dt); return dt; } } } } public static int ExecuteNonQuery(string sql, SqlParameter[] parameters) { // -1 means the query is not successful int result = -1; string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (SqlConnection conn = new SqlConnection(constr)) { try { conn.Open(); using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (null != parameters) { cmd.Parameters.AddRange(parameters); } result = cmd.ExecuteNonQuery(); } } catch (SqlException ex) { Debug.WriteLine(ex.ToString()); } finally { conn.Close(); } } return result; }Demo: I refresh the page to demonstrate that the position is saved in database and recovered after the page loaded.
Hope this can help you.
Best regards,
Sean
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
All replies
-
User-1330468790 posted
Hi Schlumpinator,
Firstly, we need to confirm if we want to achieve the same target.
1.After you drag and drop the image, the position of the image will be stored in the db. The method we want to take is ajax post + WebMethod.
2.If you or other people load the page and people will see that the image is located in the last position.
3.We take user id as '1' to simplify the scenario.
Then, I need to point out few mistakes you made which might be helpful for your following development.
1.In your code, you wrong used the web method as the attribute should be added on the method which is static.
2.If you call the web method through ajax, you'd better directly use the url for the web method instead of nested url which possibly is incorrect.
3.If you are willing to make the picture rendered in the same position, you should take the coords from the same position field. However, what I can see is that you take position x and y coordinates from offset and add the x and y coordinates to position property of Css style.
PS: Here I takes the "id" as the identifier of the element so that the page can capture it and assign the value to its position style.
More details, you could refer to below code and focus on the content in yellow background:
.aspx page: Script + HTML
<script type="text/javascript"> $(function () { $("#d1").draggable( { drag: function (event, ui) { $("#d1").css("opacity", "0.6"); // Semi-transparent when dragging }, stop: function (event, ui) { saveCoords(ui.position.left, ui.position.top, $('#d1').attr('id')); $("#d1").css("opacity", "1.0"); // Full opacity when stopped }, cursor: "move" }); }); function saveCoords(x, y, el, userid) { $.ajax({ type: "POST", url: "DraggableAndSaveCoords.aspx/SaveCoords", data: "{x: '" + x + "', y: '" + y + "', element: '" + el + "', userid: '1'}", contentType: "application/json; charset=utf-8", dataType: "json", success: function (response) {
// if '1', it means that the insert is successful. console.log(response); }, error: function (response) { alert(response.responseText); } }); } </script> </head> <body> <form id="form1" runat="server"> <div> <img src="Images/personbg.png" alt="" id="d1" width="30" height="30" runat="server" /> </div> </form> </body>Code behind:
protected void Page_Load(object sender, EventArgs e) { SetCoordsForElement(); } [WebMethod] public static int SaveCoords(int x, int y, string element, int userid) { string sql = "INSERT INTO [Coords] VALUES (@xPos, @yPos, @element, @userid)"; SqlParameter[] parameters = { new SqlParameter("@xPos", x), new SqlParameter("@yPos",y), new SqlParameter("@element", element), new SqlParameter("@userid", userid) }; return ExecuteNonQuery(sql, parameters); } public void SetCoordsForElement() { string sql = "SELECT TOP 1 xPos, yPos, Element FROM [Coords] WHERE UserID = @UserID ORDER BY ID DESC"; SqlParameter[] parameters = { new SqlParameter("@UserID", 1) }; DataTable dt = SelectFromDatabase(sql, parameters); foreach(DataRow row in dt.Rows) { HtmlControl ctl = (HtmlControl)this.FindControl(row["Element"].ToString()); if(ctl != null) { ctl.Style.Add("left", row["xPos"].ToString() + "px"); ctl.Style.Add("top", row["yPos"].ToString() + "px"); } } } public static DataTable SelectFromDatabase(string sql, SqlParameter[] parameters) { string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { if (parameters != null) { cmd.Parameters.AddRange(parameters); } using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); sda.Fill(dt); return dt; } } } } public static int ExecuteNonQuery(string sql, SqlParameter[] parameters) { // -1 means the query is not successful int result = -1; string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (SqlConnection conn = new SqlConnection(constr)) { try { conn.Open(); using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (null != parameters) { cmd.Parameters.AddRange(parameters); } result = cmd.ExecuteNonQuery(); } } catch (SqlException ex) { Debug.WriteLine(ex.ToString()); } finally { conn.Close(); } } return result; }Demo: I refresh the page to demonstrate that the position is saved in database and recovered after the page loaded.
Hope this can help you.
Best regards,
Sean
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
