DBCommunication 1.0.5

This ReadMe shows how the DBCommunication package should be used. The examples below make us of the following database- and C# objects.

================ Database Objects

CREATE TABLE [dbo].[Employee]( [EmployeeKey] [int] IDENTITY(1,1) NOT NULL, [EmployeeNumber] [int] NULL, [EmployeeName] nvarchar NULL, [EmployeeStartDate] [date] NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmployeeKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

SET IDENTITY_INSERT [dbo].[Employee] ON GO INSERT [dbo].[Employee] ([EmployeeKey], [EmployeeNumber], [EmployeeName], [EmployeeStartDate]) VALUES (1, 10000, N'Bau ter Ham', CAST(N'2020-02-01' AS Date)) GO INSERT [dbo].[Employee] ([EmployeeKey], [EmployeeNumber], [EmployeeName], [EmployeeStartDate]) VALUES (2, 10001, N'Connie Veren', CAST(N'2020-05-01' AS Date)) GO INSERT [dbo].[Employee] ([EmployeeKey], [EmployeeNumber], [EmployeeName], [EmployeeStartDate]) VALUES (3, 10002, N'Anna Nas', CAST(N'2020-05-01' AS Date)) GO INSERT [dbo].[Employee] ([EmployeeKey], [EmployeeNumber], [EmployeeName], [EmployeeStartDate]) VALUES (4, 10003, N'Mario Netten', CAST(N'2020-06-01' AS Date)) GO SET IDENTITY_INSERT [dbo].[Employee] OFF GO

CREATE PROCEDURE [dbo].[uspEmployee_Get_All] AS BEGIN SELECT [EmployeeKey] ,[EmployeeNumber] ,[EmployeeName] ,[EmployeeStartDate] FROM [dbo].[Employee] ORDER BY [EmployeeName] END GO

CREATE PROCEDURE [dbo].[uspEmployee_Get_ByDate] ( @date DATE ) AS BEGIN SELECT [EmployeeKey] ,[EmployeeNumber] ,[EmployeeName] ,[EmployeeStartDate] FROM [dbo].[Employee] WHERE [EmployeeStartDate] >= @date ORDER BY [EmployeeStartDate],[EmployeeName] END GO

CREATE PROCEDURE [dbo].[uspEmployee_Insert] ( @EmployeeNumber INT ,@EmployeeName NVARCHAR(128) ,@EmployeeStartDate DATE ) AS BEGIN INSERT INTO [dbo].[Employee] ([EmployeeNumber],[EmployeeName],[EmployeeStartDate]) VALUES (@EmployeeNumber,@EmployeeName,@EmployeeStartDate)

SELECT SCOPE_IDENTITY() AS [EmployeeKey] 

END GO

================ C# Objects

public class Employee { public int EmployeeKey { get; set; } public int EmployeeNumber { get; set; } public string EmployeeName { get; set; } public DateTime EmployeeStartDate { get; set; } }

================ Examples

Retrieve data from custom query

using (SqlDb db = new SqlDb(dbConnectionString)) { string commandText = "SELECT [EmployeeKey],[EmployeeNumber],[EmployeeName],[EmployeeStartDate] FROM [dbo].[Employee] ORDER BY [EmployeeName]";

// Return format DataTable.
DataTable dt = db.GetDataTable(CommandType.Text, commandText);

foreach (DataRow row in dt.Rows)
{
	MessageBox.Show(row["EmployeeName"].ToString());
}

// Return format List<T>
List<Employee> employees = db.GetList<Employee>(CommandType.Text, commandText);

foreach(Employee employee in employees)
{
	MessageBox.Show(employee.EmployeeName);
}

// Return format Json.
string jsonData = db.GetJson(CommandType.Text, commandText);

JArray json = JArray.Parse(jsonData);
dynamic employeesJson = json;

foreach (dynamic employeeJson in employeesJson)
{
	MessageBox.Show(employeeJson.EmployeeName.ToString());
}

}

Retrieve data from stored procedure

using (SqlDb db = new SqlDb(dbConnectionString)) { string commandText = "[dbo].[uspEmployee_Get_All]";

// Return format DataTable.
DataTable dt = db.GetDataTable(CommandType.StoredProcedure, commandText);

foreach (DataRow row in dt.Rows)
{
	MessageBox.Show("From DataTable: " + row["EmployeeName"].ToString());
}

// Return format List<T>
List<Employee> employees = db.GetList<Employee>(CommandType.StoredProcedure, commandText);

foreach(Employee employee in employees)
{
	MessageBox.Show("From List<T>: " + employee.EmployeeName);
}

// Return format Json.
string jsonData = db.GetJson(CommandType.StoredProcedure, commandText);

JArray json = JArray.Parse(jsonData);
dynamic employeesJson = json;

foreach (dynamic employeeJson in employeesJson)
{
	MessageBox.Show("From Json: " + employeeJson.EmployeeName.ToString());
}

}

Retrieve data from stored procedure with parameters

using (SqlDb db = new SqlDb(dbConnectionString)) { DateTime datDate = DateTime.Parse("2020-05-01"); SqlParameter parDate = new SqlParameter("@date", datDate); List<SqlParameter> parameters = new List<SqlParameter>() { parDate }; string commandText = "[dbo].[uspEmployee_Get_ByDate]";

// Return format DataTable.
DataTable dt = db.GetDataTable(CommandType.StoredProcedure, commandText, parameters);

foreach (DataRow row in dt.Rows)
{
	MessageBox.Show("From DataTable: " + row["EmployeeName"].ToString());
}

// Return format List<T>
List<Employee> employees = db.GetList<Employee>(CommandType.StoredProcedure, commandText, parameters);

foreach (Employee employee in employees)
{
	MessageBox.Show("From List<T>: " + employee.EmployeeName);
}

// Return format Json.
string jsonData = db.GetJson(CommandType.StoredProcedure, commandText, parameters);

JArray json = JArray.Parse(jsonData);
dynamic employeesJson = json;

foreach (dynamic employeeJson in employeesJson)
{
	MessageBox.Show("From Json: " + employeeJson.EmployeeName.ToString());
}

}

Commit data through stored procedure without return values

using (SqlDb db = new SqlDb(dbConnectionString)) { string commandText = "[dbo].[uspEmployee_Insert]"; SqlParameter parEmployeeNumber = new SqlParameter("@EmployeeNumber", 10005); SqlParameter parEmployeeName = new SqlParameter("@EmployeeName", "Fer Huizer"); SqlParameter parEmployeeStartDate = new SqlParameter("@EmployeeStartDate", DateTime.Parse("2020-07-01")); List<SqlParameter> parameters = new List<SqlParameter>() { parEmployeeNumber, parEmployeeName, parEmployeeStartDate };

db.Commit(CommandType.StoredProcedure, commandText, parameters);

}

Commit data through stored procedure with return values

using (SqlDb db = new SqlDb(dbConnectionString)) { string commandText = "[dbo].[uspEmployee_Insert]"; SqlParameter parEmployeeNumber = new SqlParameter("@EmployeeNumber", 10005); SqlParameter parEmployeeName = new SqlParameter("@EmployeeName", "Fer Huizer"); SqlParameter parEmployeeStartDate = new SqlParameter("@EmployeeStartDate", DateTime.Parse("2020-07-01")); List<SqlParameter> parameters = new List<SqlParameter>() { parEmployeeNumber, parEmployeeName, parEmployeeStartDate };

DataTable dt = db.GetDataTable(CommandType.StoredProcedure, commandText, parameters);

// When returning one record.
int employeeKey = int.Parse(dt.Rows[0]["EmployeeKey"].ToString());

// Your code here.

// When returning multiple records.
foreach (DataRow row in dt.Rows)
{
	int employeeKey = int.Parse(row["EmployeeKey"].ToString());
	
	// Your code here.
}

}

================ Other features

• The methods GetDataAsTable, GetDataAsList<T> and GetDataAsJson have the following parameters:

  1. commandType: indicates the command type of the command. Possible values are: StoredProcedure, TableDirect and Text.
  2. commandText: contains the name of the stored procedure, name of the table or custom query, depending on the commandType.
  3. parameters: Optional. Contains a list of parameters.
  4. leaveConnectionOpen: Optional. Indicates whether or not the database connection should be closed after completing the task. This may come in handy when you need to perform several consecutive actions.
  5. customConnection: Optional. Contains a SqlConnection/OleDbConnection object that overrides the connection string provided during the declaration of the SqlDb/OleDb object.

• The SqlDb class also contains a BulkInsert method that enables you to insert a DataTable object into a table. Use this method as follows:

  DataTable dt = new DataTable();

  // Fill the DataTable here.

  using (SqlDb db = new SqlDb(dbConnectionString)) { db.BulkInsert(dt, "[dbo].[Employee]", 10000); }