How to store and retrieve a Signature content in SQL database?

3 mins read

Description

The ASP.NET Web Forms Signature control by default records the drawn signature in Base64 string format for later regeneration. Hence, we can use this to store and retrieve the signature in SQL database.

Solution

To store the drawn signature image in the SQL database, follow the steps below:

Define a Signature control and buttons for Save and Restore the signatures from database.
On client side click handler, get the signature content and convert it to the Base64 string value by using the canvas toDataURL() function and then pass it the code behind by using jQuery AJAX or AJAX ScriptManager PageMethods.

Refer to the code for control creation - [Default.aspx]

<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true"></asp:ScriptManager>
    <div class="frame ">
        <h2>Signature:</h2>
        <div class="control">
           
             <ej:Signature ID="Signature1" Height="400px" StrokeWidth="3" IsResponsive="true" runat="server"></ej:Signature>       // Render signature control
        </div>
 
        <input id="btnUpload" type="button" value="Upload data to Database" onclick="Uploaddata()" /> // Render button for upload data into data base
 
    <input id="btnclear" type="button" value="Clear" onclick="onclear()" />   // Render button for clear the singnature
 
        <input id="btnget" type="button" value="Retrive image from Database" onclick="Getdata()" /> // Render button for get the data from data base
 
    </div>
 
<script>
 
// function to convert the image to Base64 string value and then passing it to the database
        function Uploaddata() {
 
            var target = $("#<%=Signature1.ClientID%>").ejSignature("instance")._canvas[0].toDataURL()
            PageMethods.Upload(target, onSucess, onError); 
 
            function onSucess(response) {
                alert("succesfully sent to datasource")
            }
            function onError(response) {
                alert('Something wrong.');
            }
 
        }
 
// function to retrieve the signature content from the database
        function Getdata() {
            PageMethods.Getuploaddata(onSucess, onError);
 
            function onSucess(response) {
                        var obj = $("#<%=Signature1.ClientID%>").ejSignature("instance");  // Create object for signature control
                        canvas = obj._canvas[0];
                        ctx = canvas.getContext("2d");
                        var img = new Image;
                        img.src = response;
                        ctx.clearRect(0, 0, canvas.width, canvas.height);  // Clear specified pixel
                        ctx.drawImage(img, 0, 0);  
                        obj.refresh();
            }
            function onError(response) {
                alert('Something wrong.');
            }
        }
 
   function onclear() {    // Triggered when you click the clear button
           var obj = $("#<%=Signature1.ClientID%>").ejSignature("instance");  // Created object for signature control
           obj.clear();    // Clear the signature control using clear public method
      }
    </script>

Now, establish a connection to the database SQL and then need to store it in the database table.
The same way is required to retrieve the stored canvas string from the database.
We have set our system location for the local database file. So, we suggest you change to your local system location like below.

// method to store the signature content in the database table
 
[System.Web.Services.WebMethod]
        public static string Upload(string target)
        {
            // Convert string into stream
            var stream = new MemoryStream();
            var writer = new StreamWriter(stream);
            writer.Write(target);
            writer.Flush();
            stream.Position = 0;
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\David\WebSample\App_Data\Signaturedata.mdf;Integrated Security=True;Connect Timeout=30");  // Set your system local database file location
            con.Open();
            string query = "INSERT INTO newData (DataValue) VALUES ('" + stream+ "')";
            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            con.Close();
            return target;
        }
 
// method to retrieve the stored signature content from database
        [System.Web.Services.WebMethod]
        public static string Getuploaddata()
        {
            using (SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename= D:\David\WebSample\App_Data\Signaturedata.mdf;Integrated Security=True;Connect Timeout=30")) // Your database path
            using (SqlCommand command = new SqlCommand("SELECT DataValue FROM newData"))
            {
                command.Connection = conn;
                conn.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    return reader.GetString(0);
                }
                return null;
            }
        }