public void IndexAccessFile(ABS_Conversion.ConversionForm frm, string sqlTable, string primVal, string altVal, string idx)
{
string primary = "";
string secondary = "";
string rec = "";
string[] recs;
string[] indxs;
string outRec = "";
long position = 0;
long recNum = 0;
bool eof = false;
FileStream fs;
StreamWriter sw;
if (fileInfo.Exists == true)
{
string text = fileInfo.FullName.ToString();
int num = text.IndexOf(".", 0);
text = text.Substring(0, num);
FileInfo f = new FileInfo(text + ".dat");
if (f.Exists == false)
{
fs = f.OpenWrite();
sw = new StreamWriter(fs);
DateTime sDate = DateTime.Now;
frm.DisplayResults(sDate.ToLongTimeString());
while (eof == false)
{
rec = reader.ReadLine();
if (rec == "" || rec == null)
{
break;
}
else if (char.Parse(rec.Substring(1 - 1, 1)) == (char)26)
{
break;
}
else
{
if (idx == "")
{
if (altVal.Trim() == "")
{
primary = GetValueInFile(primVal, rec);
}
else
{
primary = GetValueInFile(primVal, rec);
secondary = GetValueInFile(altVal, rec);
}
outRec = position.ToString() + "%" + primary + "%" + secondary;
sw.WriteLine(outRec);
sw.Flush();
primary = "";
secondary = "";
recNum++;
if (recNum % 100 == 0)
{
frm.DisplayResults("Creating Index File " + f.Name.ToUpper() + "..." + recNum.ToString());
}
position = (rec.Length + 2) + position;
}
else
{
recs = rec.Split('\t');
if (idx.IndexOf("|", 0) > -1)
{
indxs = idx.Split('|');
for (int iCnt = 0; iCnt < indxs.Length; iCnt++)
{
primary = primary + recs[int.Parse(indxs[iCnt])];
}
}
else
{
primary = recs[int.Parse(idx)];
}
outRec = position.ToString() + "%" + primary + "%" + secondary;
sw.WriteLine(outRec);
sw.Flush();
primary = "";
secondary = "";
recNum++;
if (recNum % 100 == 0)
{
frm.DisplayResults("Creating Index File " + f.Name.ToUpper() + "..." + recNum.ToString());
}
position = (rec.Length + 2) + position;
}
}
}
fs.Close();
sw.Close();
frm.DisplayResults("Removing..." + sqlTable);
SqlCommand removeCommand = new SqlCommand("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + sqlTable + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE " + sqlTable, conn);
removeCommand.ExecuteNonQuery();
SqlCommand createCommand = new SqlCommand("CREATE TABLE [dbo].[" + sqlTable + "] ([RecPosition] [numeric](10, 0) NULL ,[PrimaryVal] [varchar] (25) NULL ,[SecondaryVal] [varchar] (25) NULL ) ON [PRIMARY]", conn);
createCommand.CommandType = CommandType.Text;
createCommand.ExecuteNonQuery();
string command = "BULK INSERT Sumter.." + sqlTable + " FROM '" + f.FullName + "' WITH (DATAFILETYPE = 'char',FIELDTERMINATOR = '%',ROWTERMINATOR = '\n')";
SqlCommand bulkCommand = new SqlCommand(command, conn);
bulkCommand.CommandType = CommandType.Text;
bulkCommand.CommandTimeout = 1800;
bulkCommand.Connection = conn;
bulkCommand.ExecuteNonQuery();
SqlCommand idxCommand = new SqlCommand("CREATE INDEX [IX_PRIMARY] ON [dbo].[" + sqlTable + "]([PrimaryVal]) ON [PRIMARY]", conn);
idxCommand.CommandType = CommandType.Text;
idxCommand.CommandTimeout = 1800;
idxCommand.ExecuteNonQuery();
DateTime eDate = DateTime.Now;
frm.DisplayResults(sDate.ToLongTimeString() + " " + eDate.ToLongTimeString());
}
}
}
public void Index(ABS_Conversion.ConversionForm frm, string database, string sqlTable, string primVal, string altVal, string idx, string remoteServer, string remotePath)
{
string primary = "";
string secondary = "";
string rec = "";
string[] recs;
string[] indxs;
string outRec = "";
long position = 0;
long recNum = 0;
bool eof = false;
char tab = '\t';
FileStream fs;
StreamWriter sw;
if (fileInfo.Exists == true)
{
string text = fileInfo.FullName.ToString();
int num = text.IndexOf(".", 0);
if (num > -1)
{
text = text.Substring(0, num);
}
else
{
num = text.Length;
}
FileInfo f = new FileInfo(text + ".dat");
if (f.Exists == false)
{
fs = f.OpenWrite();
sw = new StreamWriter(fs);
DateTime sDate = DateTime.Now;
frm.DisplayResults(sDate.ToLongTimeString());
while (eof == false)
{
rec = reader.ReadLine();
if (rec == "" || rec == null)
{
break;
}
else if (char.Parse(rec.Substring(1 - 1, 1)) == (char)26)
{
break;
}
else
{
if (idx == "")
{
if (altVal.Trim() == "")
{
primary = GetValueInFile(primVal, rec);
}
else
{
primary = GetValueInFile(primVal, rec);
secondary = GetValueInFile(altVal, rec);
}
primary = primary.Trim();
secondary = secondary.Trim();
outRec = position.ToString() + "%" + primary + "%" + secondary;
sw.WriteLine(outRec);
sw.Flush();
primary = "";
secondary = "";
recNum++;
if (recNum % 100 == 0)
{
frm.DisplayResults("Creating Index File " + f.Name.ToUpper() + "..." + recNum.ToString());
}
//position = (rec.Length + 1) + position;
position = (rec.Length + 2) + position;
}
else
{
//recs = rec.Split('\t');
recs = rec.Split('|');
if (idx.IndexOf("|", 0) > -1)
{
indxs = idx.Split('|');
for (int iCnt = 0; iCnt < indxs.Length; iCnt++)
{
primary = primary + recs[int.Parse(indxs[iCnt])];
}
}
else
{
primary = recs[int.Parse(idx)];
}
outRec = position.ToString() + "%" + primary + "%" + secondary;
sw.WriteLine(outRec);
sw.Flush();
primary = "";
secondary = "";
recNum++;
if (recNum % 100 == 0)
{
frm.DisplayResults("Creating Index File " + f.Name.ToUpper() + "..." + recNum.ToString());
}
//position = (rec.Length + 1) + position;
position = (rec.Length + 2) + position;
}
}
}
sw.Close();
fs.Close();
frm.DisplayResults("Removing..." + sqlTable);
SqlCommand removeCommand = new SqlCommand("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + sqlTable + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE " + sqlTable, conn);
removeCommand.ExecuteNonQuery();
SqlCommand createCommand = new SqlCommand("CREATE TABLE [dbo].[" + sqlTable + "] ([RecPosition] [numeric](11, 0) NULL ,[PrimaryVal] [varchar] (25) NULL ,[SecondaryVal] [varchar] (25) NULL ) ON [PRIMARY]", conn);
createCommand.CommandType = CommandType.Text;
createCommand.ExecuteNonQuery();
DataTable tbl = ImportTemplate.IndexTable();
tbl.TableName = sqlTable;
Import(f.FullName, false, tbl, "%", 3);
// //FileInfo fi = new FileInfo(f.FullName);
// //fi.CopyTo(@"\\" + remoteServer + remotePath.Replace(":","$") + f.Name,true);
// string command = "BULK INSERT " + database + ".." + sqlTable + " FROM '" + f.FullName + "' WITH (DATAFILETYPE = 'char',FIELDTERMINATOR = '%',ROWTERMINATOR = '\n')";
// ////command = command.Replace(f.FullName, @"\\mphsmdev02\Input\" + f.Name);
//// //@"D:\HospitalData\jap\Delta\temp\Pathology\" mphsmsqldev01
// //command = command.Replace(f.FullName, remotePath + fi.Name);
// SqlCommand bulkCommand = new SqlCommand(command, conn);
// bulkCommand.CommandType = CommandType.Text;
// bulkCommand.CommandTimeout = 1800;
// bulkCommand.Connection = conn;
// bulkCommand.ExecuteNonQuery();
SqlCommand idxCommand = new SqlCommand("CREATE INDEX [IX_PRIMARY] ON [dbo].[" + sqlTable + "]([PrimaryVal]) ON [PRIMARY]", conn);
idxCommand.CommandType = CommandType.Text;
idxCommand.CommandTimeout = 1800;
idxCommand.ExecuteNonQuery();
DateTime eDate = DateTime.Now;
frm.DisplayResults(sDate.ToLongTimeString() + " " + eDate.ToLongTimeString());
}
}
}