public static void UpdateBarcodeInfo(Guid id, byte[] data)
{
const string sql = "UPDATE TMP SET DATA=@DATA, LENGTH=@LENGTH WHERE ID=@ID";
MSSQL.ExecuteNonQuery(ConnectionString, sql,
new SqlParameter("@ID", id),
new SqlParameter("@LENGTH", data.Length),
new SqlParameter("@DATA", SqlDbType.Binary, data.Length)
{
Value = data
});
}
public static void InsertBarcodeInfo(Guid id, byte[] data)
{
const string sql = "INSERT INTO TMP(ID, DATA, LENGTH) VALUES (@ID, @DATA, @LENGTH)";
MSSQL.ExecuteNonQuery(ConnectionString, sql,
new SqlParameter("@ID", id),
new SqlParameter("@LENGTH", data.Length),
new SqlParameter("@DATA", SqlDbType.Binary, data.Length)
{
Value = data
});
}
/// <summary>
/// Selects value from config datatable, client database
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public static byte[] SelectConfigValue(string key)
{
const string sql = "SELECT @Result = [Value] FROM CONFIG WHERE [Key] = @Key;";
var result = new SqlParameter("@Result", SqlDbType.Binary, int.MaxValue)
{
Direction = ParameterDirection.Output
};
MSSQL.ExecuteNonQuery(ConnectionString, sql,
new SqlParameter("@Key", key.Top(100)), result);
return(result.Value as byte[]);
}
/// <summary>
/// Setup database by C# code
/// </summary>
/// <param name="directoryPath"></param>
/// <param name="sqlBatchText"></param>
public static void SetupDatabase(string directoryPath, string sqlBatchText)
{
if (!Directory.Exists(directoryPath))
{
Directory.CreateDirectory(directoryPath);
}
string[] sqlArray = sqlBatchText.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
string connString = null;
foreach (string sql in sqlArray)
{
try
{
string tsql = sql.Trim();
if (string.IsNullOrEmpty(tsql))
{
continue;
}
if (tsql.IndexOf("USE [master]", StringComparison.CurrentCultureIgnoreCase) != -1)
{
connString = MasterConnectionString;
continue;
}
else if (tsql.IndexOf("USE [PTFLocal]", StringComparison.CurrentCultureIgnoreCase) != -1)
{
connString = ConnectionString;
continue;
}
if (!string.IsNullOrEmpty(connString))
{
Trace.WriteLine(tsql, "ClientDataAccess.SetupDatabase");
Trace.WriteLine("----------->>");
MSSQL.ExecuteNonQuery(connString, tsql);
}
}
catch (Exception ex)
{
Trace.WriteLine(ex);
}
}
}
/// <summary>
/// Drops client database safely. Deletes database file as well.
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string DropDatabaseSafe(string path)
{
try
{
MSSQL.ExecuteNonQuery(MasterConnectionString, "drop database PTFLocal;");
if (!string.IsNullOrEmpty(path))
{
OS.DeleteFolder(path);
}
return(null);
}
catch (Exception ex)
{
//Save operation
//Do nothing.
return(ex.Message);
}
}
public static byte[] SelectBarcodeInfoData(Guid id)
{
const string sql = "SELECT @DATA = DATA, @LENGTH = LENGTH FROM TMP WHERE ID=@ID";
var length = new SqlParameter("@LENGTH", SqlDbType.Int);
length.Direction = ParameterDirection.Output;
var data = new SqlParameter("@DATA", SqlDbType.Binary, int.MaxValue);
data.Direction = ParameterDirection.Output;
MSSQL.ExecuteNonQuery(ConnectionString, sql, new SqlParameter("@ID", id), length, data);
int count = Convert.ToInt32(length.Value);
byte[] readArr = (byte[])data.Value;
byte[] result = new byte[count];
Array.Copy(readArr, result, result.Length);
return(result);
}
public static void DeleteBarcodeInfo(Guid id)
{
const string sql = "DELETE TMP WHERE ID=@ID";
MSSQL.ExecuteNonQuery(ConnectionString, sql, new SqlParameter("@ID", id));
}