SQL samples

Database sample:

Table ACCOUNT
+----+----------+----------+------------------+
| ID | USERNAME | PASSWORD | EMAIL            |
+----+----------+----------+------------------+
| 1  | user01   | pw01     | user01@email.com |
+----+----------+----------+------------------+
| 2  | user02   | pw02     | user02@email.com |
+----+----------+----------+------------------+
| 3  | user03   | pw03     | user03@email.com |
+----+----------+----------+------------------+

Authentication:

//LoadAssembly:System.dll
//LoadAssembly:System.Data.dll

using System;
using System.Data;
using System.Data.SqlClient;
using NSi.AutoStore.Capture.DataModel;

public static class Script
{
  public static void CustomAuthenticate(MetadataCollection authData, AuthResult result)
  {
    //Sample custom authentication for a configuration with two authentication prompts: Username and Password
    string username = authData.GetData("Username");
    string password = authData.GetData("Password");

    string connectionStr = @"Data Source=<DB Server>;Initial Catalog=<DB Name>;User ID=<username>;Password=<Password>";
    SqlConnection conn = new SqlConnection(connectionStr);

    conn.Open();
    SqlCommand command = new SqlCommand("select USERNAME, EMAIL from ACCOUNT where USERNAME = @USERNAME and PASSWORD = @PASSWORD", conn);
    command.Parameters.Add("@USERNAME", SqlDbType.NVarChar);
    command.Parameters.Add("@PASSWORD", SqlDbType.NVarChar);
    command.Parameters["@USERNAME"].Value = username;
    command.Parameters["@PASSWORD"].Value = password;
    SqlDataReader dataReader = command.ExecuteReader();
    while (dataReader.Read())
    {
      result.IsAuthenticated = true;
      result.UserInfo = new UserInfo();
      result.UserInfo.UserName = dataReader.GetString(1);
      result.UserInfo.Email = dataReader.GetString(2);
      break;
    }
    conn.Close();
  }
}

Data lookup:

//LoadAssembly:System.dll
//LoadAssembly:System.Data.dll

using System;
using System.Data.SqlClient;
using NSi.AutoStore.Capture.DataModel;

public static class Script
{
  public static void Form_OnLoad(MFPEventData eventData)
  {
    //Initialize dynamic field
    ListField userList = new ListField();
    userList.Name = "UserList";
    userList.Display = "User List";
    userList.RaiseFindEvent = true;
    //Can populate data here
    PopulateUser(userList, null);
    eventData.Form.Fields.Add(userList);
  }

  public static void UserList_OnFind(MFPEventData eventData)
  {
    //Can populate data here, filtered by search criteria
    ListField userList = (ListField)eventData.Form.Fields.GetField("UserList");
    PopulateUser(userList, eventData.SearchCriteria);
  }

  private static void PopulateUser(ListField userList, string searchCriteria)
  {
    if (userList == null)
    {
      userList = new ListField();
    }
    userList.Items.Clear();
    bool isSearchable = string.IsNullOrEmpty(searchCriteria) ? false : true;

    string connectionStr = @"Data Source=<DB Server>;Initial Catalog=<DB Name>;User ID=<username>;Password=<password>";
    SqlConnection conn = new SqlConnection(connectionStr);

    conn.Open();
    string query = string.Empty;
    if (isSearchable)
    {
      query = "select ID, USERNAME from ACCOUNT where USERNAME like @USERNAME";
    }
    else
    {
      query = "select ID, USERNAME from ACCOUNT";
    }
    SqlCommand command = new SqlCommand(query, conn);
    if (isSearchable)
    {
      command.Parameters.AddWithValue("@USERNAME", "%" + searchCriteria + "%");
    }
    SqlDataReader dataReader = command.ExecuteReader();
    while (dataReader.Read())
    {
      ListItem item = new ListItem(dataReader.GetString(1), dataReader.GetValue(0).ToString());
      userList.Items.Add(item);
    }
    conn.Close();
  }
}