private string updateDB(List <updateOAStorageData> updateList, string fileName, string error)
{
string errorTxt = "";
string sSqlInsert = "insert into oaStorage (orderArtId, antal, regdat, result, fileName, resultDescr) "
+ " values(:orderArtId, :antal, :regdat, :result, :fileName, :resultDescr) ";
NxConnection cn = cdb.getConn();
NxCommand cm = new NxCommand(sSqlInsert, cn);
cm.Parameters.Add("orderArtId", DbType.Int32);
cm.Parameters.Add("antal", DbType.Decimal);
cm.Parameters.Add("result", DbType.String);
cm.Parameters.Add("regdat", System.DateTime.Now);
cm.Parameters.Add("fileName", fileName);
cm.Parameters.Add("resultDescr", error);
try
{
foreach (updateOAStorageData oad in updateList)
{
cm.Parameters["orderArtId"].Value = oad.orderArtId;
cm.Parameters["antal"].Value = oad.stockToSend;
int result = oad.error == "" ? 1 : 0;
cm.Parameters["result"].Value = result;
cm.Parameters["resultDescr"].Value = oad.error.Length > 50 ? oad.error.Substring(0, 49) : oad.error;
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
}
}
catch (Exception ex)
{
errorTxt = "Fel vid uppdatering av oaStorage : " + ex.Message;
}
return(errorTxt);
}
public virtual void Setup()
{
string dbFolder = string.Format(".\\dapperTest");
if (!Directory.Exists(dbFolder))
{
Directory.CreateDirectory(dbFolder);
}
string connectionString = string.Format("server={0};database={1};port={2};Embedded=true", "Embedded", dbFolder, "16080");
var connection = new NxConnection(connectionString);
var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new NexusDBDialect());
var sqlGenerator = new NexusDBSqlGenerator(config);
Db = new Database(connection, sqlGenerator);
var files = new List<string>
{
ReadScriptFile("CreateFooTable"),
ReadScriptFile("CreateMultikeyTable"),
ReadScriptFile("CreatePersonTable"),
ReadScriptFile("CreateCarTable"),
ReadScriptFile("CreateAnimalTable")
};
foreach (var setupFile in files)
{
connection.Execute(setupFile);
}
}
/// <summary>
/// Takes an sql clause and returns a DataTable with the result
/// 2016-02-02 KJBO Pegas AB
/// </summary>
/// <param name="sSql">The SQL clause to be executed</param>
/// <param name="errText">Reference parameter that returns error string</param>
/// <param name="pc">Parameter collection</param>
/// <returns>Result of SQL clause in a DataTable</returns>
public DataTable getData(string sSql, ref string errText, NxParameterCollection pc)
{
NxConnection cn = getConn();
NxCommand cm = new NxCommand(sSql, cn);
errText = "";
if (pc != null)
{
foreach (NxParameter np in pc)
{
NxParameter npInsert = (NxParameter)np.Clone();
cm.Parameters.Add(npInsert);
}
}
// Create a data adapter
NxDataAdapter da = new NxDataAdapter(cm);
// Datatable for the result
DataTable dt = new DataTable();
try
{
// Fill data table and...
da.Fill(dt);
}
catch (Exception ex)
{
errText = ex.Message;
}
// return result
return(dt);
}
/// <summary>
/// Get a connection with connection string from web.config
/// </summary>
/// <returns></returns>
public NxConnection getConn()
{
string cs = CConfig.getCS();
NxConnection cn = new NxConnection(cs);
return(cn);
}
private int getNextRowNo()
{
string sSql = " SELECT NextRowNo "
+ " FROM CompactStoreRow ";
NxConnection cn = cdb.getConn();
NxCommand cm = new NxCommand(sSql, cn);
cn.Open();
int nextRowNo = Convert.ToInt32(cm.ExecuteScalar());
cn.Close();
int updateRow = nextRowNo + 1;
string sSqlUpdate = " update CompactStoreRow "
+ " set NextRowNo = " + updateRow.ToString() + " ";
NxCommand cmUpdate = new NxCommand(sSqlUpdate, cn);
cn.Open();
cmUpdate.ExecuteNonQuery();
cn.Close();
return(nextRowNo);
}
/// <summary>
/// Updates the database from the given SQL Clause
/// </summary>
/// <param name="sSql">SQL Clause to be executed</param>
/// <param name="errText">Refernce string that returns error string</param>
/// <param name="pc">Collection of parameters</param>
/// <returns>Number of updated rows</returns>
public int updateData(string sSql, ref string errText, NxParameterCollection pc)
{
NxConnection cn = getConn();
NxCommand cm = new NxCommand(sSql, cn);
// Check if there are any parameters
if (pc != null)
{
foreach (NxParameter np in pc)
{
NxParameter npInsert = (NxParameter)np.Clone();
cm.Parameters.Add(npInsert);
}
}
errText = "";
int result = 0;
try
{
cn.Open();
result = cm.ExecuteNonQuery();
}
catch (Exception ex)
{
errText = ex.Message;
result = -1;
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
return(result);
}
