public void Insert(FileInfo fileInfo, AudioFileInfo audioFileInfo)
{
using (SqlConnection connection = DBConnectionFactory.GetConnection())
{
connection.Open();
using (SqlCommand insertCommand = new SqlCommand(@"TrackInsert_SP", connection))
{
insertCommand.CommandType = CommandType.StoredProcedure;
#region satelite table values
int result1 = InsertValueByTableName(fileInfo.DirectoryName, TableList.Path);
SqlParameter PathID = new SqlParameter("@PathID", result1);
insertCommand.Parameters.Add(PathID);
PathID.Direction = ParameterDirection.Input;
int result2 = InsertValueByTableName(audioFileInfo.Artist, TableList.Artist);
SqlParameter ArtistID = new SqlParameter("@ArtistID", result2);
insertCommand.Parameters.Add(ArtistID);
ArtistID.Direction = ParameterDirection.Input;
int result3 = InsertValueByTableName(audioFileInfo.Album, TableList.Album);
SqlParameter AlbumID = new SqlParameter("@AlbumID", result3);
insertCommand.Parameters.Add(AlbumID);
AlbumID.Direction = ParameterDirection.Input;
int result4 = InsertValueByTableName(audioFileInfo.Genre, TableList.Genre);
SqlParameter GenreID = new SqlParameter("@GenreID", result4);
insertCommand.Parameters.Add(GenreID);
GenreID.Direction = ParameterDirection.Input;
int result5 = InsertValueByTableName(audioFileInfo.Bitrate, TableList.Bitrate);
SqlParameter BitrateID = new SqlParameter("@BitrateID", result5);
insertCommand.Parameters.Add(BitrateID);
BitrateID.Direction = ParameterDirection.Input;
#endregion
SqlParameter TrackTitle = new SqlParameter("@TrackTitle", audioFileInfo.Title);
insertCommand.Parameters.Add(TrackTitle);
TrackTitle.Direction = ParameterDirection.Input;
SqlParameter FileName = new SqlParameter("@FileName", fileInfo.Name);
insertCommand.Parameters.Add(FileName);
FileName.Direction = ParameterDirection.Input;
SqlParameter FileSize = new SqlParameter("@FileSize", fileInfo.Length);
insertCommand.Parameters.Add(FileSize);
FileSize.Direction = ParameterDirection.Input;
string newName = audioFileInfo.Artist + "_" + audioFileInfo.Title;
SqlParameter NewFileName = new SqlParameter("@NewFileName", newName);
insertCommand.Parameters.Add(NewFileName);
NewFileName.Direction = ParameterDirection.Input;
int affectedRows = (int)insertCommand.ExecuteNonQuery();
}
}
}
private int InsertValueByTableName(string Value, TableList tableName)
{
int result = 0;
using (SqlConnection connection = DBConnectionFactory.GetConnection())
{
connection.Open();
SqlCommand insertCommand = null;
switch (tableName)
{
case TableList.Artist:
{
insertCommand = new SqlCommand(@"ArtistInsert_SP", connection);
}
break;
case TableList.Album:
{
insertCommand = new SqlCommand(@"AlbumInsert_SP", connection);
}
break;
case TableList.Bitrate:
{
insertCommand = new SqlCommand(@"BitrateInsert_SP", connection);
}
break;
case TableList.Genre:
{
insertCommand = new SqlCommand(@"GenreInsert_SP", connection);
}
break;
case TableList.Path:
{
insertCommand = new SqlCommand(@"PathInsert_SP", connection);
}
break;
}
if (insertCommand != null)
{
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter returnValue = new SqlParameter("@return_value", null);
insertCommand.Parameters.Add(returnValue);
returnValue.Direction = ParameterDirection.ReturnValue;
SqlParameter nameParameter = new SqlParameter("@ValueToInsert", Value);
insertCommand.Parameters.Add(nameParameter);
nameParameter.Direction = ParameterDirection.Input;
int affectedRows = (int)insertCommand.ExecuteNonQuery();
result = Convert.ToInt32(returnValue.Value);
}
}
return(result);
}
public List <string> GetListByTableName(TableList tableName)
{
List <string> nameList = new List <string>();
using (SqlConnection connection = DBConnectionFactory.GetConnection())
{
connection.Open();
SqlCommand insertCommand = null;
string sqlCommand = string.Empty;
string parameter = "Name";
switch (tableName)
{
case TableList.Artist:
{
sqlCommand = @"SELECT [Name] FROM [MediaLibrary].[dbo].[Artist]";
}
break;
case TableList.Album:
{
sqlCommand = @"SELECT [Name] FROM [MediaLibrary].[dbo].[Album]";
}
break;
case TableList.Genre:
{
sqlCommand = @"SELECT [Name] FROM [MediaLibrary].[dbo].[Genre]";
}
break;
case TableList.Title:
{
sqlCommand = @"SELECT [TrackTitle] FROM [MediaLibrary].[dbo].[Track]";
parameter = "TrackTitle";
} break;
case TableList.FileName:
{
sqlCommand = @"SELECT [FileName] FROM [MediaLibrary].[dbo].[Track]";
parameter = "FileName";
} break;
}
using (insertCommand = new SqlCommand(sqlCommand, connection))
{
insertCommand.CommandType = CommandType.Text;
using (SqlDataReader dataReader = insertCommand.ExecuteReader())
{
while (dataReader.Read())
{
nameList.Add(dataReader[parameter].ToString());
}
}
}
}
return(nameList);
}
public string Create(SuppliersDto t)
{
using (sqlConnection = DBConnectionFactory.GetConnection())
{
SqlParameter idParameter = new SqlParameter("@SupplierId", SqlDbType.Int);
SqlParameter companyNameParameter = new SqlParameter("@CompanyName", SqlDbType.NVarChar);
SqlParameter contactNameParameter = new SqlParameter("@ContactName", SqlDbType.NVarChar);
SqlParameter contactTitleParameter = new SqlParameter("@ContactTitle", SqlDbType.NVarChar);
SqlParameter addressParameter = new SqlParameter("@Address", SqlDbType.NVarChar);
SqlParameter cityParameter = new SqlParameter("@City", SqlDbType.NVarChar);
SqlParameter regionParameter = new SqlParameter("@Region", SqlDbType.NVarChar);
SqlParameter postalCodeParameter = new SqlParameter("@PostalCode", SqlDbType.NVarChar);
SqlParameter countryParameter = new SqlParameter("@Country", SqlDbType.NVarChar);
SqlParameter phoneParameter = new SqlParameter("@Phone", SqlDbType.NVarChar);
SqlParameter faxParameter = new SqlParameter("@Fax", SqlDbType.NVarChar);
SqlParameter homePageParameter = new SqlParameter("@HomePage", SqlDbType.NText);
idParameter.Value = t.SupplierId;
companyNameParameter.Value = t.CompanyName;
contactNameParameter.Value = t.ContactName;
contactTitleParameter.Value = t.ContactTitle;
addressParameter.Value = t.Address;
cityParameter.Value = t.City;
regionParameter.Value = t.Region;
postalCodeParameter.Value = t.ContactTitle;
countryParameter.Value = t.Address;
phoneParameter.Value = t.City;
faxParameter.Value = t.Region;
homePageParameter.Value = t.HomePage;
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = "CreateNewSupplier";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add(idParameter);
sqlCommand.Parameters.Add(companyNameParameter);
sqlCommand.Parameters.Add(contactNameParameter);
sqlCommand.Parameters.Add(contactTitleParameter);
sqlCommand.Parameters.Add(addressParameter);
sqlCommand.Parameters.Add(cityParameter);
sqlCommand.Parameters.Add(regionParameter);
sqlCommand.Parameters.Add(postalCodeParameter);
sqlCommand.Parameters.Add(countryParameter);
sqlCommand.Parameters.Add(phoneParameter);
sqlCommand.Parameters.Add(faxParameter);
sqlCommand.Parameters.Add(homePageParameter);
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
return(t.SupplierId.ToString());
}
}
}
public void ClearDataBase()
{
using (SqlConnection connection = DBConnectionFactory.GetConnection())
{
connection.Open();
using (SqlCommand insertCommand = new SqlCommand(@"TrancateDataBase_SP", connection))
{
insertCommand.CommandType = CommandType.StoredProcedure;
int affectedRows = (int)insertCommand.ExecuteNonQuery();
}
}
}
public void Delete(int id)
{
using (sqlConnection = DBConnectionFactory.GetConnection())
{
string baseQuery = "DELETE FROM [NORTHWNDSDP-162].[dbo].[Suppliers] WHERE SupplierId = '{0}'";
string realQuery = String.Format(baseQuery, id);
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand(realQuery, sqlConnection))
{
sqlCommand.ExecuteNonQuery();
}
sqlConnection.Close();
}
}
public string Update(SuppliersDto t)
{
using (sqlConnection = DBConnectionFactory.GetConnection())
{
string baseQuery = "UPDATE [NORTHWNDSDP-162].[dbo].[Suppliers] SET SupplierId = '{0}',CompanyName = '{1}',ContactName = '{2}',ContactTitle = {3},Address = {4},City = '{5}',Region = '{6}',PostalCode = {7},Country = {8},Phone = {9},Fax = {10},HomePage = {11}";
string realQuery = String.Format(baseQuery, t.SupplierId, t.CompanyName,
t.ContactName, t.ContactTitle, t.Address,
t.City, t.Region, t.PostalCode, t.Country, t.Phone, t.Fax, t.HomePage);
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand(realQuery, sqlConnection))
{
sqlConnection.Close();
return(sqlCommand.ExecuteNonQuery().ToString());
}
}
}
public static string GetQBPath()
{
SqlCommand cmd;
SqlParameter param;
SqlDataReader drSync;
SqlConnection Con = (DBConnectionFactory.GetConnection <SQLLocalConnection>()).Connection;
string sqlQry = "";
string QBPath = "";
Con.Open();
//sqlQry = "SELECT Value FROM Config WHERE Key = 'QBDB'";
cmd = new SqlCommand();
cmd.CommandText = "spConfig";
cmd.CommandType = CommandType.StoredProcedure;
param = new SqlParameter("@DMLType", 1);
cmd.Parameters.Add(param);
//cmd = new SqlCommand(sqlQry, Con);
try
{
drSync = cmd.ExecuteReader();
if (drSync.HasRows)
{
drSync.Read();
QBPath = drSync["Value"].ToString();
drSync.Dispose();
}
else
{
throw new Exception("Quick Book data path has not been set");
}
return(QBPath);
}
catch (SqlException sqlEx)
{
throw sqlEx;
}
finally
{
Con.Close();
cmd.Dispose();
sqlQry = string.Empty;
}
}
public bool SaveMergeBill(LH_MergeBill bill, List <string> LHOBODNOS)
{
var conn = DBConnectionFactory.GetConnection(DBTypeEnums.ORACLE);
conn.Open();
var tran = conn.BeginTransaction();
try
{
bool result = false;
LH_MergeBillRepository repository = new LH_MergeBillRepository();
var lhoutorderRepository = new DefaultRepository <LH_OUTBOUNDORDER>(DBTypeEnums.ORACLE);
var sql = "SELECT * FROM MERGEBILL WHERE FBILLNO=:FBILLNO";
var temp = repository.GetSingle(sql, bill);
if (temp == null)
{
result = repository.Insert(bill, tran);
}
else
{
result = repository.Update(bill, new { FBILLNO = bill.FBILLNO });
}
sql = $"UPDATE LH_OUTBOUNDORDER SET FMEGREBILLNO=:FBILLNO,FCARNO=:CARNO WHERE LHODONO IN :LHODONOS";
result = lhoutorderRepository.Execute(sql, new { FBILLNO = bill.FBILLNO, LHODONOS = LHOBODNOS, CARNO = bill.FCARNO }, tran) == LHOBODNOS.Count;
tran.Commit();
conn.Close();
return(result);
}
catch (Exception e)
{
tran.Rollback();
conn.Close();
LogHelper.Error(e);
throw;
}
}
public ICollection <SuppliersDto> Read()
{
List <SuppliersDto> supplierDTOsToReturn = new List <SuppliersDto>();
using (sqlConnection = DBConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string realSelectQuery = @"SELECT * FROM [NORTHWNDSDP-162].[dbo].[Suppliers]";
sqlCommand.CommandText = realSelectQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
supplierDTOsToReturn.Add(new SuppliersDto()
{
SupplierId = Int32.Parse(reader["CustomerId"].ToString()),
CompanyName = reader["CompanyName"].ToString(),
ContactName = reader["ContactName"].ToString(),
ContactTitle = reader["ContactTitle"].ToString(),
Address = reader["Address"].ToString(),
City = reader["City"].ToString(),
Region = reader["Region"].ToString(),
PostalCode = reader["PostalCode"].ToString(),
Country = reader["Country"].ToString(),
Phone = reader["Phone"].ToString(),
Fax = reader["Fax"].ToString(),
HomePage = reader["HomePage"].ToString()
});
}
}
}
sqlConnection.Close();
}
return(supplierDTOsToReturn);
}
public SuppliersDto Read(int id)
{
using (sqlConnection = DBConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string baseSelectQuery = @"SELECT * FROM [NORTHWNDSDP-162].[dbo].[Customers]" +
"WHERE [SupplierId = {0}]";
string realSelectQuery = String.Format(baseSelectQuery, id.ToString());
sqlCommand.CommandText = realSelectQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
supplierDTOToReturn = new SuppliersDto()
{
SupplierId = Int32.Parse(reader["SupplierId"].ToString()),
CompanyName = reader["CompanyName"].ToString(),
ContactName = reader["ContactName"].ToString(),
ContactTitle = reader["ContactTitle"].ToString(),
Address = reader["Address"].ToString(),
City = reader["City"].ToString(),
Region = reader["Region"].ToString(),
PostalCode = reader["PostalCode"].ToString(),
Country = reader["Country"].ToString(),
Phone = reader["Phone"].ToString(),
Fax = reader["Fax"].ToString(),
HomePage = reader["HomePage"].ToString()
};
}
}
sqlConnection.Close();
}
return(supplierDTOToReturn);
}
public Microsoft.Practices.EnterpriseLibrary.Data.Database CreateDatabase()
{
return(new Microsoft.Practices.EnterpriseLibrary.
Data.Sql.SqlDatabase(DBConnectionFactory.
GetConnection().ConnectionString));
}
public object GetCustomizedViewByTableName(
int pageSize,
int pageIndex,
string albumFilter,
string artistFilter,
string genreFilter,
string fileNameFilter)
{
DataTable dt = new DataTable();
using (SqlConnection connection = DBConnectionFactory.GetConnection())
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand(@"[MediaLibrary.GetAllWithPaging]", connection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@PageSize", pageSize);
sqlCommand.Parameters.AddWithValue("@PageIndex", pageIndex);
sqlCommand.Parameters.AddWithValue("@Album", albumFilter);
sqlCommand.Parameters.AddWithValue("@Artist", artistFilter);
sqlCommand.Parameters.AddWithValue("@Genre", genreFilter);
sqlCommand.Parameters.AddWithValue("@FileName", fileNameFilter);
SqlDataReader reader = sqlCommand.ExecuteReader();
// create view list for dataGridView
while (reader.Read())
{
int id = Convert.ToInt32(reader["TrackID"]);
string genre = Convert.ToString(reader["GenreName"]);
string albumName = Convert.ToString(reader["AlbumName"]);
string artistName = Convert.ToString(reader["ArtistName"]);
string pathName = Convert.ToString(reader["FullPath"]);
string bitrate = Convert.ToString(reader["Bitrate"]);
string fileSize = Convert.ToString(reader["FileSize"]);
string newFileName = Convert.ToString(reader["NewName"]);
string fileName = Convert.ToString(reader["FileName"]);
string trackTitle = Convert.ToString(reader["TrackTitle"]);
resultList.Add(new ResultTable()
{
GenreName = genre,
AlbumName = albumName,
ArtistName = artistName,
PathName = pathName,
Bitrate = bitrate,
FileSize = fileSize,
NewFileName = newFileName,
FileName = fileName,
TrackTitle = trackTitle
});
}
reader.Close();
reader.Dispose();
reader = sqlCommand.ExecuteReader();
dt.Load(reader);
return(new DataView(dt));
}
}
/// <summary>
/// 保存数据
/// </summary>
/// <param name="vi">表头</param>
/// <param name="temperaList">明细数据</param>
/// <param name="pressureRecords">加压记录</param>
void SaveData(VerInfo vi, List <TEMPerA> temperaList, List <PressureRecord> pressureRecords)
{
#region 查询是否有该条记录
var viwhere = new VerInfo()
{
QR = vi.QR,
DeviceNum = vi.DeviceNum,
DSType = "true"
};
var dic = new Dictionary <string, CompareEnum>();
dic.Add("QR", CompareEnum.Equal);
dic.Add("DeviceNum", CompareEnum.Equal);
dic.Add("DSTYPE", CompareEnum.Equal);
var count = verInfoRepository.Select(viwhere, dic).Count();
if (count > 0)
{
return;
}
#endregion
var startTime = DateTime.Now;
#region 组装数据
var targetList = pressureRecords.Where(p => p.DicCode == "TargetPressureForPressurization").AsParallel();
var pressauList = pressureRecords.Where(p => p.DicCode == "PressureOfPressurizedState").AsParallel();
var outputList = pressureRecords.Where(p => p.DicCode == "LevelOutputValueForPressurization").AsParallel();
foreach (var record in pressauList)
{
var targetRow = targetList.FirstOrDefault(p => p.VerInfoID == record.VerInfoID && p.RecordTime == record.RecordTime);
var outputRow = outputList.FirstOrDefault(p => p.VerInfoID == record.VerInfoID && p.RecordTime == record.RecordTime);
record.OutputVal = outputRow is null ? null : outputRow.RecordVal;
record.TargetVal = targetRow is null ? null : targetRow.RecordVal;
}
pressureRecords = pressauList.ToList();
#endregion
// 开始事务
using (var conn = DBConnectionFactory.GetConnection(DBTypeEnums.MYSQL))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var tran = conn.BeginTransaction();
if (vi.DSType == "true")
{
verInfoRepository.Update(vi, new { QR = vi.QR }, tran);
}
else
{
verInfoRepository.Insert(vi, tran);
}
temPerARepository.InsertBulk(temperaList, tran);
pressureRepository.InsertBulk(pressureRecords, tran);
//for (int i = 0; i < (int)Math.Ceiling((decimal)temperaList.Count / tCount); i++)
//{
// temPerARepository.InsertBulk(temperaList.Skip(i * tCount).Take(tCount).ToList());
//}
//for (int i = 0; i < (int)Math.Ceiling((decimal)pressureRecords.Count / tCount); i++)
//{
// pressureRepository.InsertBulk(pressureRecords.Skip(i * tCount).Take(tCount).ToList());
//}
tran.Commit();
}
//
var timespan = DateTime.Now - startTime;
try
{
MySqlHelper.ExecuteNonQueryStoredProcedure($"ver_qr", new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("str", vi.QR) });
LogHelper.Info($"【{vi.QR}】数据保存成功，数据写入耗时【{timespan.TotalSeconds}】秒");
}
catch (Exception ex)
{
LogHelper.Info($"【{vi.QR}】数据call ver_qr('{vi.QR}')异常【{ex.Message}】");
}
}
