Kangal

Kangal is extension library for data operations. Like DataReader, SqlConnection or DataTable.

The project has no dependence.

Nuget library
Install-Package Kangal

Some Features

DataTable

In the DataTable records returns back as IEnumerable<T>

public static IEnumerable<T> ToList<T>(this DataTable dataTable) where T : new()

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var dataTable = new DataTable();
    var reader = new SqlCommand(query, connection).ExecuteReader();
    dataTable.Load(reader);
    return dataTable.ToList<Person>();
}

DataTable's content convert to Csv formatted string.

public static string ToCsv(this DataTable dataTable, string comma = null,bool ignoreNull = false)

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var dataTable = new DataTable();
    var reader = new SqlCommand(query, connection).ExecuteReader();
    dataTable.Load(reader);
    return dataTable.ToCsv("-", true);
}

DataTable's content convert to XDocument.

public static XDocument ToXDocument(this DataTable dataTable,XmlWriteMode xmlWriteMode = XmlWriteMode.IgnoreSchema,string nodeName = null,bool writeHierarchy = true)

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var dataTable = new DataTable();
    var reader = new SqlCommand(query, connection).ExecuteReader();
    dataTable.Load(reader);
    return dataTable.ToXDocument(xmlWriteMode: XmlWriteMode.WriteSchema, nodeName: "persons",
        writeHierarchy: false);
}

DataTable's content convert to Json.

public static string ToJson(this DataTable dataTable,JsonFormat jsonFormat = JsonFormat.Simple,JsonFormatSettings jsonFormatSettings = null)

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var dataTable = new DataTable();
    var reader = new SqlCommand(query, connection).ExecuteReader();
    dataTable.Load(reader);
    return dataTable.ToJson(JsonFormat.Showy, new JsonFormatSettings("dd/MM/yyyy", "0:00.0"));
}

You can change DataTable column name.

public static void ChangeColumnName(this DataTable dataTable, string currentColumnName, string newColumnName)

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var dataTable = new DataTable();
    var reader = new SqlCommand(query, connection).ExecuteReader();
    dataTable.Load(reader);
    dataTable.ChangeColumnName("FirstName", "NickName");
}

You can remove DataTable column

public static void RemoveColumn(this DataTable dataTable, string columnName)

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var dataTable = new DataTable();
    var reader = new SqlCommand(query, connection).ExecuteReader();
    dataTable.Load(reader);
    dataTable.RemoveColumn("FirstName");
}

SqlConnection

You can insert to MSSQL database to your DataTable

Save(this SqlConnection connection, DataTable dataTable, string tableName,SqlTransaction transaction = null)

var dataTable = new DataTable();
dataTable.Columns.Add("FirstName",typeof(string));
dataTable.Columns.Add("LastName", typeof(string));
dataTable.Columns.Add("Age", typeof(short));
dataTable.Rows.Add("selçuk", "güral", 35);
dataTable.Rows.Add("songül", "güral", 30);
dataTable.Rows.Add("zeynep sare", "güral", 1);
dataTable.AcceptChanges();

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    return connection.Save(dataTable);
}

Save your .Net POCO objects

public static int Save<T>(this SqlConnection connection, IEnumerable<T> entities, SqlTransaction transaction = null, string tableName = null) where T : class

var persons = new List<Person>
{
    new Person("selçuk","güral",35),
    new Person("songül","güral",30),
    new Person("zeynep sare","güral",1)
};
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    return connection.Save(persons);
}

Getting sql query result and object mapping. Also you can use some useful Attributes. Like ColumnAlias and Ignore

public static IEnumerable<T> Get<T>(this SqlConnection connection,string query) where T : class ,new ()

public class Person
{
    [ColumnAlias("FirstName")] 
    public string Name { get; set; }

    [ColumnAlias("LastName")]
    public string Surname { get; set; }

    [Ignore]
    public short Age { get; set; }

    public Person()
    {
        
    }
    public Person(string name,string surname,short age)
    {
        this.Name = name;
        this.Surname = surname;
        this.Age = age;
    }
}


public static IEnumerable<Person> IDataReader_ToList()
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        return new SqlCommand(query, connection).ExecuteReader().ToList<Person>();
    }
}

DataReader

You can convert IDataReader object to IEnumerable<T>

public static IEnumerable<T> ToList<T>(this IDataReader reader) where T :class, new()

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    return new SqlCommand(query, connection).ExecuteReader().ToList<Person>();
}

Also it is possible convert IDataReader object to XDocument

public static XDocument ToXDocument(this IDataReader reader,string rootName,string nodeName)

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    return new SqlCommand(query, connection).ExecuteReader().ToXDocument("persons", "person");
}

In the records IDataReader object returns back as IEnumerable<DataTable>

public static IEnumerable<DataTable> ToDataTable(this IDataReader reader)

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    return new SqlCommand(query, connection).ExecuteReader().ToDataTable();
}

List

The Generic List convert to DataTable

public static DataTable ToDataTable<T>(this IEnumerable<T> entityList) where T : class

return new List<Person>
{
    new Person("selçuk", "güral", 35),
    new Person("songül", "güral", 30),
    new Person("zeynep sare", "güral", 1)
}.ToDataTable();

The Entities convert to XDocument.

public static XDocument ToXDocument<T>(this IEnumerable<T> entities,string rootName) where T : class

return new List<Person>
{
    new Person("selçuk", "güral", 35),
    new Person("songül", "güral", 30),
    new Person("zeynep sare", "güral", 1)
}.ToXDocument("persons");