Data.Net


Data.Net

A simple, lightweight data access library for any ADO.Net providers like SQL server, Oracle & MySQL.

Source – https://github.com/deepumi/Data.Net

No Dependencies

Since Data.Net is written in .NetStandard 2.0 no third party libraries are required.

Supporting platforms

Data.Net support multiple frameworks and platforms.

  • Net Core 2.0 / ASP.NET Core 2.0
  • Net Standard 2.0
  • NetFramework 4.6.1
  • NetFramework 4.6.2
  • NetFramework 4.7.0

Nuget

Install Data.Net via Nuget

PM> Install-Package Data.Net

Execute a query and return a single value from the first row

using (var db = new Database()) //make sure connection string is configured in the config file with <connectionStrings><clear/><add../></connectionStrings>
{
  var firstName = db.ExecuteScalar<string>("SELECT FirstName From Users_Test Where Email = @Email",
                  parameters:new DataParameters{{"Email", "user@domain.com" } });
  Assert.IsTrue(!string.IsNullOrWhiteSpace(firstName));
}

Execute a query and convert to POCO object

public class User
{
    public string FirstName { get; set; } //make sure property name is same as table column name, otherwise use alias in sql query.
    public string LastName { get; set; }
    public string Email { get; set; }
}

using(var db = new Database(new OracleConnection("connectionString")))
{
    List<User> q = db.Query<User>("SELECT EmailAddress as Email,LastName FROM Users_Test");
    Assert.IsTrue(q.Count > 0);
}

Execute a stored procedure and get multiple output parameter and return value.

 var parameters = new DataParameters
 {
    { "@Email", "user1@domain.com" },
    { "@RecordCount"}, //Default parameter direction is 'output'
    { "@AnotherOutParameter"},
    { "@ReturnVal",ParameterDirection.ReturnValue}
 };

  var q = db.Query<User>("PROC_NAME", CommandType.StoredProcedure, parameters);
  
  Assert.IsTrue(q.Count > 0);
  Assert.IsTrue(dataParameter.Value<int>("@RecordCount") > 0);
  Assert.IsTrue(dataParameter.Value<int>("@AnotherOutParameter") > 0);
  Assert.IsTrue(dataParameter.Value<int>("@ReturnVal") > 0);

Execute a command with DB transaction.

using (var db = new Database())
{
  try
  {
      db.BeginTransaction(); //create transaction.
    
      db.ExecuteNonQuery("sql", parameters:new DataParameters{{"Email", "user@domain.com" } });
      db.ExecuteNonQuery("sql", parameters:new DataParameters{{"Email", "user@domain.com" } });
      
      db.CommitTransaction(); //commit transaction.
  }
  catch (Exception)
  {
      db.RollbackTransaction(); // rollback if any failure. 
  }
}

Execute multiple query using a single DB connection instance

using(var db = new Database())
{
  var reader1 = db.ExecuteReader("sql",CommandType.Text,parameters,CommandBehavior.Default);
  var reader2 = db.ExecuteReader("sql",CommandType.Text,parameters); //default behaviour is close connection
}

Create DB provider specific parameter object

Data.Net support multiple overloads so that you can create DB specific parameter object like below.

public void Add(IDbDataParameter parameter) {}  //For any Ado.Net specific provider
public void Add(string name, object value) {}
public void Add(string name, ParameterDirection direction = ParameterDirection.Output,DbType dbType = DbType.Int32, int size = 0) {}

Example usage

var p = new DataParameter();

p.Add(new OracleParameter("name","value"));
p.Add("name","value");
p.Add("name",ParameterDirection.Output,DbType.Int32,0);

Please check out more samples in the Unit test app.

Advertisements

Convert C# camel case json property to pascal case


I am pretty sure that many of them faced the same scenario where you would end up copy/paste some json data from a third party API sites And using one of the tools like Visual Studio IDE or json2csharp.com to generate POCO classes. However, these tools will not generate pascal case property’s automatically And if you want to maintain the coding standard you have to change the property’s to pascal case manually.

For example : Default camel case property’s generated based on Visual Studio IDE

public class Refunds
{
   public string @object { get; set; }
   public bool has_more { get; set; }
}

Since I had to convert many json property’s to pascal casing which is a tedious task so I have developed an app to convert camel case csharp json property to pascal case. http://deepumi.com/jsonproperty2TitleCase/

jsonProperty2TitleCase Core Features:

  • Convert property to Title case which is Pascal case
  • Add NewtonsoftJson property attribute to add the actual json property name for deserializing purpose.
  • Also, handles nested classes hierarchies.
  • Beautify your csharp code  (Remove all extra white spaces)

jsonProperty2TitleCase Output:

using Newtonsoft.Json;

public class Refunds
{
    [JsonProperty("object")]
    public string Object { get; set; }

    [JsonProperty("has_more")]
    public bool HasMore { get; set; }
}

http://deepumi.com/jsonproperty2TitleCase/

jsonproperty

Please feel free to give this a try and let me know your thoughts and suggestions.

http://deepumi.com/jsonproperty2TitleCase/

MadMimi.Net Integration


I have been playing around with Transaction Email Services for a while and came across the tool named MadMimi, A email marketing tool lets you to send email from a different server so that you can track whether the email delivered or bounce.

MadMimi already have a C# version API and its quite old. The reason I wrote my own version is to prevent less code change to my current project so that if I want to switch to a different provider in the feature it would be easy.

A typical email sending code

using (var message = new MailMessage())
{
    message.To.Add("receiver");
    message.From = new MailAddress("from");
    message.Subject = "Subject";
    message.Body = "This is test";
    using (var client = new SmtpClient())
    {
      client.Send(message);
    }
}

Using Library

  var mailer = new Mailer("username", "apikey");
  using (var message = new MailMessage())
  {
      message.From = new MailAddress("from email", "display name");
      message.To.Add("sender email");
      message.Subject = "Test";
      message.Body = "Email body";
      await mailer.SendAsync("Promotion Name", message);
  }
  var status = mailer.TrackStatus; //URL to track the email
  var mailTransactionId = mailer.TransactionId; //Transaction Id.

When you compare the above code snippets you should see additional two liner code which integrate the library (Mailer class) and that is the only change you should make your existing project and apps if you would like to integrate this.

Pros
SPF (
Sender Policy Framework) – MadMimi does not care if you have set up a SPF record in DNS. While most of the competitors, this is a mandatory requirement if you want to use your own “FROM” email address other than your DOMAIN.

Cons
1) Delivery speed – Some time the email take 5-6 minutes to deliver.
2) No SMTP relay support.
3) Attachment does not support via API.

Feel free to play around and fork it if you would like to make any change to this. The GitHub link is provided below for the sample project and library.

Repository – https://github.com/deepumi/MadMimi.Net

First Data Global Gateway Integration in Asp.Net MVC C#


I have added a new Github repository for First Data Global Gateway Integration in Asp.Net MVC.  The sample provides a rest api implementation for credit card based payment processing. Since, I have choosen Asp.Net MVC empty project template you might need to install System.Net.HttpClient library from nuget.org or install directly from visual studio.

Command – Install-Package Microsoft.Net.Http

Download Location or copy paste the url

https://github.com/deepumi/FirstDataPaymentGateway-CSharp.Net

This code was taken from the original source https://support.payeezy.com/hc/en-us/articles/204699865-C-REST-Sample-Code-For-Use-with-v12-v13

Example Usage:

 var paymentService = new PaymentService("1.50", "0515","Card holder name","4111111111111111", "23".ToString()); //customer Id for Internal purpose to track which customer made the transaction...
 var transactionResult = await paymenetService.PostAsync();
 if (transactionResult == null) return;
 if (transactionResult.TransactionApproved && !transactionResult.TransactionError && transactionResult.CustomerRef == customerId.ToString())
 {
 //transaction success....
 }
 else
 {
 //transaction failed
 var message = transactionResult.Message;
 }

Configuration Settings:
Following values needs to be changed in the web.config file.

<add key="GATEWAY_ID" value="Exact ID" />
<add key="GATEWAY_PWD" value="Password" />
<add key="TRANS_TYPE" value="00" />
<add key="HMAC_KEY" value="HMAC Key" />
<add key="KEY_ID" value="Key Id" />
<add key="GATEWAY_URL" value="https://api.demo.globalgatewaye4.firstdata.com" />

Transaction Summary file(transaction_summary.xml) from payment gateway is included in the App_Data folder.

Also, refer : https://support.payeezy.com/hc/en-us/articles/203731109-First-Data-Global-Gateway-e4-Web-Service-API-Reference-Guide

As per First Data knowledge base Note: “Code samples are provided “as is” and are not designed to be used in production.”

 

jQuery Autocomplete with multiple fileds in Asp.Net C#


This  article explains jQuery autocomplete with  multiple fields using Asp.Net C#.

Scenario :  Listing business titles for a specific city/state location.

Initially I used AJAX CONTROL TOOLKIT (ACT) but I ran in to many issues and finally decided to use jQuery approach which really saved my time.

You can Download the complete source code from here

Add jQuery assets on your page.

<link href="styles/jquery-ui.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="scripts/jquery.min.js"></script>
<script type="text/javascript" src="scripts/jquery-ui.min.js"></script>

jQuery Code for consuming web service.

<script type="text/javascript">
$(function () {
var search = $("#<%=txtSearch.ClientID%>");
var location = $("#<%=txtLocation.ClientID %>");
search.autocomplete({
    source: function (request, response) {
        $.ajax({
            url: '<%=ResolveUrl("~/") %>AutoCompleteService.asmx/GetKeyWords',
            data: "{ 'keyword': '" + search.val() + "', 'location' : '" + location.val() + "'}",
            dataType: "json",
            type: "POST",
            contentType: "application/json; charset=utf-8",
            dataFilter: function (data) { return data; },
            success: function (data) {
                if (data.d != null) {
                    response($.map(data.d, function (item) {
                        return {
                            value: item
                        }
                    }))
                }
            },
            error: function (XMLHttpRequest, textStatus, error) {
                //alert(textStatus);
            }
        });
    },
    minLength: 1
});

location.autocomplete({
    source: function (request, response) {
        $.ajax({
            url: '<%=ResolveUrl("~/") %>AutoCompleteService.asmx/GetLocations',
            data: "{ 'location': '" + location.val() + "'}",
            dataType: "json",
            type: "POST",
            contentType: "application/json; charset=utf-8",
            dataFilter: function (data) { return data; },
            success: function (data) {
                if (data.d != null) {
                    response($.map(data.d, function (item) {
                        return {
                            value: item
                        }
                    }))
                }
            },
            error: function (XMLHttpRequest, textStatus, errorThrown) {
                // alert(textStatus);
            }
        });
    },
    minLength: 1
});

});
</script>

Code-behind for ASMX class (don’t forget to uncomment  System.Web.Script.Services.ScriptService)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Services;
using System.Collections;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class AutoCompleteService : System.Web.Services.WebService {

public AutoCompleteService () {
}

IDictionary<string, string[]> LoadItems()
{
    IDictionary<string, string[]> items = new Dictionary<string, string[]>();

    items.Add("Newyork", new string[]
                                        {
                                            "Applebee's", "Apiary", "Brenda's French Soul Food","TGI Friday's","Cafe Venue"
                                        });

    items.Add("Los Angeles", new string[]
                                        {
                                            "Los 1", "Los 2", "Los 3","TGI Friday's","Cafe Venue"
                                        });

    items.Add("Las Vegas", new string[]
                                        {
                                            "Los 1", "Los 2", "Los 3","TGI Friday's","Cafe Venue"
                                        });

    return items;
}

[WebMethod]
public string[] GetKeyWords(string keyword, string location)
{
    var items = LoadItems();
    if (items != null && items.Count > 0)
    {
        //Get all items for a specific location
        var result = (from item in items
                        where item.Key.Equals(location, StringComparison.OrdinalIgnoreCase)
                        select item.Value)
                    .FirstOrDefault();

        //check whether the items start with the keyword
        return result.Where
                            (
                                o => o.StartsWith(keyword, StringComparison.OrdinalIgnoreCase)
                            ).ToArray<string>();
    }
    return null;
}

[WebMethod]
public string[] GetLocations(string location)
{
    var items = LoadItems();
    if (items != null && items.Count > 0)
    {
        //check whether the items start with the location
        return (from item in items
                        where item.Key.StartsWith(location, StringComparison.OrdinalIgnoreCase)
                        select item.Key).ToArray<string>();

    }
    return null;
}
}

You can Download the complete source code from here

Hope this help and If you have any comments, please feel free to write your feedback.

Thanks
Deepu

 

Twitter Tweets in Asp.Net C#


I’ve been working on the twitter feed in one of my recent project where tweets getting updated on a regular interval time (say for every 30 minutes or 1 hr). I am creating this as a ASCX control so that I can reuse for multiple projects.

You can Download the complete source code from here

I am dropping a ListView control in the ASCX page which render the Twitter Profile name along with  Title, Description and Published date for the latest tweets.

Couple of properties need to configure to use this control

TwitterProfileName – Your twitter profile name or screen name

TweetsCount – No of tweets you want to return (default is 10).

<asp:ListView ID="lvTweets" runat="server">
<LayoutTemplate>
<table border="0" cellpadding="2" cellspacing="0">
<tr>
<td height="30" runat="server">
<a href="http://twitter.com/<%=<span class=&quot;hiddenSpellError&quot; pre=&quot;&quot;>TwitterProfileName</span>%>" target="_new"></a>
<%= TwitterProfileName%>
</td>
</tr>
<tr>
<td>
<asp:PlaceHolder ID="itemPlaceholder" runat="server" />
</td>
</tr>
<tr>
77B5D2;">
</td>
</tr>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr>
<td>
<a target="_new" href='<%# DataBinder.Eval(Container.DataItem, "Link")%>'>
<%# DataBinder.Eval(Container.DataItem, "Title")%></a>
<br />
<div>
<%# DataBinder.Eval(Container.DataItem, "PublishedDate", "{0:h:mm  tt MMM d}")%>
</td>
</tr>
</ItemTemplate>
<EmptyDataTemplate>
<div>
<h3>
No tweets available.</h3>
</div>
</EmptyDataTemplate>
<ItemSeparatorTemplate>
<tr>
1px solid lightgrey;">
</td>
</tr>
</ItemSeparatorTemplate>
</asp:ListView>
using System;
using System.Linq;
using System.Xml.Linq;
using System.Collections;
using System.Collections.Generic;

public partial class TweetsControl : System.Web.UI.UserControl
{
private static DateTime? lastUpdated = null; //holds last updated time

private static XDocument xDoc = null; //static variable to store the result xml.

//Updates latest Tweets for every 10 minutes in page refresh.
private static Double Interval = 10;

//Determine its time to get the new tweets
private static Boolean IsTimeForUpdate
{
get
{
if (lastUpdated.HasValue && DateTime.Now > lastUpdated.Value.AddMinutes(Interval))
{
return true;
}
return false;
}
}

//Hold no of tweets default set it as 10.
public Int32? TweetsCount { get; set; }

//Twitter profile name or screen name.
public String TwitterProfileName { get; set; }

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetTweets();
}
}

private void GetTweets()
{
var xml = LoadXML();
IEnumerable query = null;
if (xml != null)
{
query = from e in xml.Descendants("item")
select new
{
Title = e.Element("title").Value,
Link = e.Element("link").Value,
PublishedDate = Convert.ToDateTime((e.Descendants("pubDate").First().Value)),
};
}
lvTweets.DataSource = query;
lvTweets.DataBind();
}

private XDocument LoadXML()
{
if (xDoc != null && !IsTimeForUpdate)
{
return xDoc;
}
else
{
try
{
TweetsCount = TweetsCount.HasValue ? TweetsCount : 10;
var url = string.Format("http://api.twitter.com/statuses/user_timeline.rss?screen_name={0}&count={1}", TwitterProfileName, TweetsCount);
xDoc = XDocument.Load(url);
lastUpdated = DateTime.Now;
return xDoc;
}
catch
{
return null;
}
}
}

}

Refer more api information on Twitter API

You can Download the complete source code from here

Hope this helps

Thanks
Deepu

using System; using System.Linq; using System.Xml.Linq; using System.Collections; using System.Collections.Generic; public partial class TweetsControl : System.Web.UI.UserControl { private static DateTime? lastUpdated = null; private static XDocument xDoc = null; private static Double Interval = 10; private static Boolean IsTimeForUpdate { get { if (lastUpdated.HasValue && DateTime.Now > lastUpdated.Value.AddMinutes(Interval)) { return true; } return false; } } public Int32? TweetsCount { get; set; } public String TwitterProfileName { get; set; } protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { GetTweets(); } } private void GetTweets() { var xml = LoadXML(); IEnumerable query = null; if (xml != null) { query = from e in xml.Descendants(“item”) select new { Title = e.Element(“title”).Value, Link = e.Element(“link”).Value, PublishedDate = Convert.ToDateTime((e.Descendants(“pubDate”).First().Value)), }; } lvTweets.DataSource = query; lvTweets.DataBind(); } private XDocument LoadXML() { if (xDoc != null && !IsTimeForUpdate) { return xDoc; } else { try { TweetsCount = TweetsCount.HasValue ? TweetsCount : 10; var url = string.Format(“http://api.twitter.com/1/statuses/user_timeline.rss?screen_name={0}&count={1}”, TwitterProfileName, TweetsCount); xDoc = XDocument.Load(url); lastUpdated = DateTime.Now; return xDoc; } catch { return null; } } } }

Google Spell Checker Api Asp.Net C#


In this article you will learn how to use the Google Spell Checker API in Asp.Net C# apps

Download the complete source code from here

The API is very simple,  spell checking is done through a XML http post to the following url

https://www.google.com/tbproxy/spell?lang=en:

Request XML structure

<?xml version=”1.0encoding=”utf-8?>
<spellrequest textalreadyclipped=”0ignoredups=”0ignoredigits=”1ignoreallcaps=”1>
<text>Hotal</text>
</spellrequest
>

The folloing are the Response XML from Google API

<?xml version=”1.0encoding=”UTF-8?>
<spellresult error=”0clipped=”0charschecked=”12>
<c o=”0l=”5s=”0″>
Hotel Hotly Total Ital Hots</c>
<
/spellresult
>

Tag Description
o The offset from the start of the text of the word
l Length of misspelled word
s Confidence of the suggestion
text Tab delimited list of suggestions

See the complete code here

using System;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;

public static class SpellChecker
{
 public static String DidYouMean(string word)
 {
 string retValue = string.Empty;
 try
 {
 string uri = "https://www.google.com/tbproxy/spell?lang=en:";
 using (WebClient webclient = new WebClient())
 {
 string postData = string.Format("<?xml version=\"1.0\" encoding=\"utf-8\" ?><spellrequest textalreadyclipped=\"0\" ignoredups=\"0\" ignoredigits=\"1\" "
 + "ignoreallcaps=\"1\"><text>{0}</text></spellrequest>",word);

 webclient.Headers.Add("Content-Type", "application/x-www-form-urlencoded");
 byte[] bytes = Encoding.ASCII.GetBytes(postData);
 byte[] response = webclient.UploadData(uri, "POST", bytes);
 string data = Encoding.ASCII.GetString(response);
 if (data != string.Empty)
 {
    retValue = Regex.Replace(data, @"<(.|\n)*?>", string.Empty).Split('\t')[0];
 }
 }
 }
 catch (Exception exp)
 {

 }
 return retValue;
 }
 }

protected void Page_Load(object sender, EventArgs e)
{
    string word = SpellChecker.DidYouMean("Hotal");
    if(word != string.Empty)
    {
         labMessage.Text = "<font style='font-size:12px;color:red;'>Did you mean </font><b>" + retValue + "</b>";
    }
}

You can Download the complete source code from here

Hope this help and If you have any comments, please feel free to write your feedback.

Thanks
Deepu