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}】"); } }