Archive for category Database

Getting started with MongoDB in Asp.Net Core

Introduction

In this article we will discuss on the procedures to build a simple application in Asp.Net Core which communicates with MongoDB database.

Description

This application will do the CRUD operation on a Mongo database and then displays the details in a table.

ThirdPartyTool Used

Robo 3T – is a third party tool which provides a lightweight MongoDB management tool

Implementation

Setting up MongoDB:

If you have not installed the mongodb exe then download and install the same from MongoDB Download Center

After installation of the database, in order to access the mongodb we have to start the MongoDB Process.

To start MongoDB, run mongod.exe in command prompt. Make sure you are running the command prompt from installation folder of Mongodb.  By default installation path is set as C:\Program Files\MongoDB\Server\3.6\bin\

Additionally we need a data directory to store all data in MongoDB. User can set the path for data files using the –dbpath option to mongod.exe

CommandStart

You can begin using the Robo 3T after starting mongodb process.

For this demo I have created a Collections namely Customer with 3 columns

Robo3T.png

Since the database is ready now we will start building the application. Follow along the article to create sample application

Below is a sample demonstration of the application we are going to make

demo.gif

Create a new Project in Visual Studio

CoreProject

And Select the Template for Asp.Net Core MVC Web Application

MVCCore

To interact with MongoDB from c# code, we need to install .NET MongoDB Driver which provides asynchronous interaction with MongoDB.  I utilized the below nugget commands to add driver into my project

Model Class

Lets make an entity class called “Customer” which fits the schema of the Customer table in the database

The class contains Id property of the type ObjectId. This property is used to match an item in MongoDB collections. We as well have another attribute, namely BsonElement which is applied to represent an “element” in the MongoDB collection.

Controller Methods

In Controller we will add code for reading, editing, creating and deleting records from MongoDB.  I have moved the code to retrieve the mongodb database details to a common method.

Views Code

Since this was a more of a MongoDB demo I have used the scaffolding option available with MVC to generate View. You can modify this as per your needs.

Index View

Create View

Delete View

Details View

Edit View

Conclusion

In this article we have looked into procedures to create a simple application in MVC Core using MongoDB as database.

You can download the source code for Asp.NetCoreMVCMongoDBDemo from GitHub.

 

Advertisements

, , , , , ,

Leave a comment

Search and Populate GridView from database using Jquery Ajax

Introduction:

In this article I am going to explain how to search and Populate GridView from database using Jquery Ajax.

Prerequisites:

You need to add the necessary JQuery files to your page prior to use this plugin. In this example I have used the online JQuery CDN files available. If you don’t want to use the online available cdn file then you can manually add the jquery files to your solution. I have explained the steps here in detail.

I have used Address table from AdventureWorks Database. You can download it from here.

Implementation:

Here we are make use of Jquery Ajax post method in Jquery to check if record exists in database and get the details on the blur event of textbox.

HTML

 <html xmlns="http://www.w3.org/1999/xhtml">
 <head runat="server">
     <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
     <title runat="server"></title>
     <script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
     <script type='text/javascript'>
         $(document).ready(function () {
             //Attach on change event to textbox here
             $("#TextBox1").change(function () {
                 //Remove all previously added rows in gridview
                 $("[id*=sampleGV] tr").not($("[id*=sampleGV] tr:first-child")).remove();
                 $.ajax({
                     type: "POST",
                     //Call the method to get data from database 
                     url: "WebForm4.aspx/GetOrderDetails",
                     //Pass the value from textbox to method
                     data: '{"addressID":' + $("#TextBox1").val() + '}',
                     contentType: "application/json; charset=utf-8",
                     dataType: "json",
                     success: function (result) {
                         //Add the resul to gridview
                         $("#sampleGV").append("<tr><td>" + result.d[0][0] + "</td><td>" + result.d[0][1] + "</td><td>" + result.d[0][2] + "</td></tr>");
                     },
                     failure: function (response) {
                         alert(response.d);
                     },
                     error: function (response) {
                         alert(response.d);
                     }
                 });
 
             });
         });
     </script>
 </head>
 <body class="myCss">
     <div>
         <form id="form1" runat="server" class="hidden">
             <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
             <asp:GridView ID="sampleGV" runat="server" Width="1000px" ShowHeaderWhenEmpty="true"
                 CellPadding="0" CellSpacing="0">
             </asp:GridView>
         </form>
     </div>
 </body>
 </html>
 

C#:

First we need to populate an empty row in GridView. Reason behind this is if we didn’t assign any datasource then the GridView won’t populate at runtime and we won’t be able to access it at client side

 protected void Page_Load(object sender, System.EventArgs e)
 {
         //Load and empty row to gridview
         if (!IsPostBack) {
                DataTable dt = new DataTable();
                dt.Columns.Add("AddressID");
                dt.Columns.Add("AddressLine1");
                dt.Columns.Add("City");
                sampleGV.DataSource = dt;
                sampleGV.DataBind();
         }
 }
 

Next is the GetOrderDetails method which will read the parameter from client side and the search the database and then pass the result set back. Note the [Services.WebMethod()] attribute I added to the GetOrderDetails method,this is important  otherwise the serverside method will not be called from client side jQuery AJAX call.

[Services.WebMethod()]
public static Array GetOrderDetails(string addressID)
{
	//Create sql connection object here
	SqlConnection conn = null;
	//Create sql DataTable object here
	DataTable dt = new DataTable();
	//Create sql SqlDataAdapter object here
	SqlDataAdapter da = default(SqlDataAdapter);
	//Create sql ArrayList object here
	ArrayList rows = new ArrayList();
	//Get the connectionstring from webconfig file
	string connection = ConfigurationManager.ConnectionStrings("Adventure WorksConnectionStringPersonal").ConnectionString;
	//Create sql SqlConnection object here and assign the conenctionstring
	conn = new SqlConnection(connection);
	//Create sql query here
	string sql = "SELECT AddressID,AddressLine1,City FROM Person.Address a WHERE a.AddressID = @AddressID";
	//Create sql SqlCommand and assign connection and command here
	SqlCommand cmd = new SqlCommand(sql, conn);
	//Pass your parameter here
	cmd.Parameters.AddWithValue("@AddressID", addressID);
	//Open the SQLConnection here
	conn.Open();
	//Create SqlDataAdapter object here
	da = new SqlDataAdapter(cmd);
	using (DataSet ds = new DataSet()) {
		//Load the DataTable
		da.Fill(dt);
		foreach (DataRow dataRow in dt.Rows) {
			rows.Add(dataRow.ItemArray.Select(item => item.ToString()));
		}
		//Create an array and then return the values
		return rows.ToArray();
	}
	conn.Close();
}

Demo

, , , ,

Leave a comment

How to check the ID availability in Database using Jquery Ajax.

Introduction:

In this article I am going to explain how to check the data availability in database using Jquery Ajax.

Description:

Here I am going to explain one of the options to check if record is available in database and show error message according to the availability. This method or logic which I have explained here can be useful to check if the username is already used or it is available to use.

Prerequisites:

You need to add the necessary JQuery files to your page prior to use this plugin. In this example I have used the online JQuery CDN files available. If you don’t want to use the online available cdn file then you can manually add the jquery files to your solution. I have explained the steps here in detail.

Implementation:

Here we are make use of Jquery Ajax post method in Jquery to check if record exists in database or not on the blur event of textbox.

HTML:

 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
    <script type="text/javascript">
        function checkIDAvailability() {
            $.ajax({
                type: "POST",
                url: "JqueryAjaxPost.aspx/checkUserName",
                data: "{IDVal: '" + $("#<% =txtID.ClientID %>").val() + "' }",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: onSuccess,
                failure: function (AjaxResponse) {
                    document.getElementById("Label1").innerHTML = "Error Occured";
                }
            });
            function onSuccess(AjaxResponse) {
                document.getElementById("Label1").innerHTML = AjaxResponse.d;
            }
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Enter your ID here
     <asp:TextBox ID="txtID" runat="server" onblur="checkIDAvailability();"></asp:TextBox>
        <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    </div>
    </form>
</body>
</html>

C#:

This method will get called from clientside using Jquery Ajax. You need to ensure that you have added the WebMethod attribute to the method.

 
        //Server Side Method which will get called from Client Side
        //Ensure that you have declared the method like WebMethod
        [System.Web.Services.WebMethod(EnableSession = true)]
        public static string checkUserName(string IDVal)
        {
            string result = string.Empty;
            //Get your connection string here
            string conString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorks2008R2ConnectionString2"].ConnectionString;
            //Change your query here
            string qry = "Select AddressID from Person.Address Where AddressID =@AddressID";
            SqlDataAdapter da = new SqlDataAdapter(qry, conString);
            //Pass the value to paramter
            da.SelectCommand.Parameters.AddWithValue("@AddressID", IDVal.Trim());
            DataSet ds = new DataSet();
            da.Fill(ds, "IDTable");
            //Check if dataset is having any value
            if (ds.Tables["IDTable"].Rows.Count > 0)
            {
                // User Name Not Available
                result = "ID already in use";
            }
            else
            {
                //User_Name is available
                result = "ID is available, you can use it";
            }
            //Return the result
            return result;
        }

Demo

, , , , , ,

Leave a comment