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
close icon

how to connect my Grid with database SQLserver

PLEASE HELP 
Im using ASP.net Grids
I read your documents but im confused 

1) How to connect my grid with DATABASE (using dummy grid and fields for now )

2) How to add Selection in my cells after wards when DATABASE connected

3) How to add contectxt Menu on my Cell Selection for exmaple , When I Click on 2,3(or many) cells and perform action over it like delete and it wtil delete my data in sql server DATABASE too ? 

MY  ASPX FILE

---------------------------------------------------------------------------------------------------------------------------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="TESGRID.aspx.cs" Inherits="TESGRID" %>

<%@ Register Assembly="Syncfusion.EJ.Web, Version=17.3460.0.26, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89"
    Namespace="Syncfusion.JavaScript.Web" TagPrefix="ej" %>


<%@ Register Assembly="Syncfusion.EJ, Version=17.3460.0.26, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89"
    Namespace="Syncfusion.JavaScript.Web" TagPrefix="ej" %>

<%@ Register Assembly="Syncfusion.EJ, Version=17.3460.0.26, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89"
    Namespace="Syncfusion.JavaScript.Models" TagPrefix="ej" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <meta charset="utf-8" />

    <script
        src="https://code.jquery.com/jquery-3.4.1.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>
    <link rel='nofollow' href="http://cdn.syncfusion.com/17.3.0.9/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" />
    <link rel='nofollow' href="https://cdn.syncfusion.com/17.3.0.9/js/web/ej.widgets.core.min.css" rel="stylesheet" />
    <script src="https://cdn.syncfusion.com/17.3.0.9/js/web/ej.web.all.js"></script>

    <script src="http://cdn.syncfusion.com/js/assets/external/jquery-3.3.1.min.js"></script>

    <script src="http://cdn.syncfusion.com/js/assets/external/jquery.easing.1.3.min.js"></script>

    <script src="http://cdn.syncfusion.com/js/assets/external/jquery.globalize.min.js"></script>

    <script src="http://cdn.syncfusion.com/js/assets/external/jsrender.min.js"></script>

    <script src="http://cdn.syncfusion.com/17.3.0.9/js/web/ej.web.all.min.js"></script>
    <script src="https://cdn.syncfusion.com/17.3.0.9/js/common/ej.unobtrusive.min.js"></script>

    <script src="http://cdn.syncfusion.com/17.3.0.9/js/common/ej.webform.min.js"></script>
    <script src="https://cdn.syncfusion.com/js/assets/external/jsrender.min.js"></script>
    <script src="https://cdn.syncfusion.com/js/assets/external/jquery.validate.min.js"></script>
    <link rel='nofollow' href="themes/flat-lime/ej.web.all.min.css" rel="stylesheet" type="text/css" />

</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>



        <ej:Grid ID="FlatGrid" runat="server" AllowPaging="true" DataSourceCachingMode="ViewState">
            <Columns>
                <ej:Column Field="OrderID" HeaderText="Order ID" IsPrimaryKey="True" TextAlign="Right" Width="75" />
                <ej:Column Field="CustomerID" HeaderText="Customer ID" Width="80" />
                <ej:Column Field="EmployeeID" HeaderText="Employee ID" TextAlign="Right" Width="75" />
                <ej:Column Field="Freight" HeaderText="Freight" TextAlign="Right" Width="75" Format="{0:C}" />
                <ej:Column Field="OrderDate" HeaderText="Order Date" TextAlign="Right" Width="80" Format="{0:MM/dd/yyyy}" />
                <ej:Column Field="ShipCity" HeaderText="Ship City" Width="110" />
            </Columns>
        </ej:Grid>

     </form>
</body>
</html>

1 Reply

FS Farveen Sulthana Thameeztheen Basha Syncfusion Team November 26, 2019 03:31 PM UTC

Hi Nice, 

Thanks for contacting Syncfusion Support. 
 
Query#:-  How to connect my grid with DATABASE (using dummy grid and fields for now ) 
 
We have created sample by using Grid with SQL database connection. Refer to the sample Link:- 

<ej:Grid ID="OrdersGrid" runat="server" AllowPaging="True" AllowSorting="True" OnServerEditRow="EditEvents_ServerEditRow" AllowSelection="true"  Selectiontype="Multiple" 
             OnServerAddRow="EditEvents_ServerAddRow" OnServerDeleteRow="EditEvents_ServerDeleteRow"> 
            <ClientSideEvents ActionComplete="complete" EndAdd="endAdd" EndDelete="endDelete" EndEdit="endEdit"  
                RecordDoubleClick="doubleClick" ContextClick="Click" CellSelected="cellSelected" /> 
             <SelectionSettings SelectionMode="cell"/>  
            <ContextMenuSettings EnableContextMenu="true"></ContextMenuSettings> 
           <Columns> 
                <ej:Column Field="OrderID" HeaderText=" Order ID" IsPrimaryKey="True" TextAlign="Right" Width="75"> 
                    <ValidationRule> 
                        <ej:KeyValue Key="required" Value="true" /> 
                        <ej:KeyValue Key="number" Value="true" /> 
                    </ValidationRule> 
                </ej:Column> 
                   
                <ej:Column Field="ShipCountry" HeaderText="Country" Width="80" EditType="DropdownEdit"> 
                </ej:Column> 
            </Columns> 
            <EditSettings AllowEditing="True" AllowAdding="True" AllowDeleting="True"></EditSettings> 
            <ToolbarSettings ShowToolbar="True" ToolbarItems="add,edit,delete,update,cancel"></ToolbarSettings> 
        </ej:Grid> 
 
           <asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 
            SelectCommand="SELECT * FROM [Orders]"></asp:SqlDataSource> 
 
serverside: 
 
   protected void Page_Load(object sender, EventArgs e) 
        { 
            if (!IsPostBack) 
            { 
                SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ToString()); 
                dt = new DataTable("Order"); 
                SqlCommand cmd = new SqlCommand(); 
                cmd.Connection = myConnection; 
                cmd.CommandText = "select * from Orders"; 
                cmd.CommandType = CommandType.Text; 
                SqlDataAdapter da = new SqlDataAdapter(); 
                da.SelectCommand = cmd; 
                if (myConnection.State == ConnectionState.Closed) 
                { 
                    myConnection.Open(); 
                } 
                da.Fill(dt); 
                Session["SqlDataSource"] = dt; 
                dataBind(); 
            } 
 
 
 
Documentation and Demo Links:- 
 
Query#2:-  How to add Selection in my cells after wards when DATABASE connected 
 
To enable cell selection we need to set selectionMode as Cell on SelectionSettings property. Refer to the code example:- 

<ej:Grid ID="OrdersGrid" runat="server" AllowPaging="True" AllowSorting="True" OnServerEditRow="EditEvents_ServerEditRow" AllowSelection="true"  Selectiontype="Multiple"/> 
             <SelectionSettings SelectionMode="cell"/>  
            <ContextMenuSettings EnableContextMenu="true"></ContextMenuSettings> 
           <Columns> 
                <ej:Column Field="OrderID" HeaderText=" Order ID" IsPrimaryKey="True" TextAlign="Right" Width="75"> 
                    <ValidationRule> 
                        <ej:KeyValue Key="required" Value="true" /> 
                        <ej:KeyValue Key="number" Value="true" /> 
                    </ValidationRule> 
                </ej:Column> 
                     .     .     . 
        </ej:Grid> 
 
           <asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 
            SelectCommand="SELECT * FROM [Orders]"></asp:SqlDataSource> 
 
                 </ContentTemplate> 
        </asp:UpdatePanel> 
         


Refer to the documentation Link:- 

Query#3:- How to add contectxt Menu on my Cell Selection for exmaple , When I Click on 2,3(or many) cells and perform action over it like delete and it wtil delete my data in sql server DATABASE too ?  
 
You can enable context menu using EnableContextMenu of ContextMenuSettings property. ContextClick ClientSide event get triggers while on selecting deleteRecord Item on the ContextMenu List. We can get the selected cells using CellSelected event of the Grid and then delete records using deleteRecord method of the Grid which will trigger OnServerDeleteRow event which will delete records from database, 
Refer to the code example:- 
 
<ej:Grid ID="OrdersGrid" runat="server" AllowPaging="True" AllowSorting="True" OnServerEditRow="EditEvents_ServerEditRow" AllowSelection="true"  Selectiontype="Multiple" 
             OnServerAddRow="EditEvents_ServerAddRow" OnServerDeleteRow="EditEvents_ServerDeleteRow"> 
            <ClientSideEvents  
ContextClick="Click" CellSelected="cellSelected" /> 
             <SelectionSettings SelectionMode="cell"/>  
            <ContextMenuSettings EnableContextMenu="true"></ContextMenuSettings> 
           <Columns> 
                .   .      .  
            </Columns> 
             
         <script> 
            var records = []; 
            function cellSelected(args) { 
                records.push(args.data); 
            } 
            function Click(args) { 
                if (args.text == "Delete Record") { 
                 for (var del = 0; del < records.length; del++) { 
                    this.deleteRecord("OrderID", records[del]); 
                    } 
                } 
               args.cancel = true 
            } 
        </script> 
 
 
Refer to the documentation Link:- 
 
Demo Link for ServerEvents:- 
 
Please get back to us if you need any further assistance. 

Regards, 
Farveen sulthana T 


Loader.
Live Chat Icon For mobile
Up arrow icon