|
Загрузка файла (sql)
#38655401
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
День добрый. Суть проблемы такова, нужно загрузить содержимое dbf файла в sql не загружая сам файл на сервер.
Пробую так.
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45. 46. 47. 48. 49. 50. 51. 52. 53. 54. 55. 56. 57. 58. 59. 60. 61. 62. 63. 64. 65. 66. 67. 68. 69. 70. 71. 72. 73. 74. 75. 76. 77. 78. 79. 80. 81. 82. 83. 84. 85. 86. 87. 88. 89. 90. 91. 92. 93. 94. 95. 96. 97. 98. 99. 100. 101. 102. 103. 104. 105. 106. 107. 108. 109. 110. 111. 112. 113. 114. 115. 116. 117. 118. 119. 120. 121. 122. 123. 124. 125. 126. 127. 128. 129. 130. 131. 132. 133. 134. 135. 136. 137. 138. 139. 140. 141. 142. 143. 144. 145. 146. 147. 148. 149. 150. 151. 152. 153. 154. 155. 156. 157. 158. 159. 160. 161. 162. 163. 164. 165. 166. 167. 168. 169. 170. 171. 172. 173. 174. 175. 176. 177. 178. 179. 180. 181. 182. 183. 184. 185. 186. 187. 188. 189. 190. 191. 192. 193. 194. 195. 196. 197. 198. 199. 200. 201. 202. 203. 204. 205. 206. 207. 208. 209. 210. 211. 212. 213. 214. 215. 216. 217. 218. 219. 220. 221. 222. 223. 224. 225. 226. 227. 228. 229. 230. 231. 232. 233. 234. 235. 236.
#region балбла..
public DataTable ReadDBF(object dbfFile)
{
long start = DateTime.Now.Ticks;
DataTable dt = new DataTable();
BinaryReader recReader;
string number;
string year;
string month;
string day;
long lDate;
long lTime;
DataRow row;
int fieldIndex;
if (File.Exists(dbfFile.ToString()))
{
BinaryReader br = null;
try
{
// Read the header into a buffer
br = new BinaryReader(File.OpenRead(dbfFile.ToString()));
byte[] buffer = br.ReadBytes(Marshal.SizeOf(typeof(DBFHeader)));
// Marshall the header into a DBFHeader structure
GCHandle handle = GCHandle.Alloc(buffer, GCHandleType.Pinned);
DBFHeader header = (DBFHeader)Marshal.PtrToStructure(handle.AddrOfPinnedObject(), typeof(DBFHeader));
handle.Free();
// Read in all the field descriptors. Per the spec, 13 (0D) marks the end of the field descriptors
ArrayList fields = new ArrayList();
while ((13 != br.PeekChar()))
{
buffer = br.ReadBytes(Marshal.SizeOf(typeof(FieldDescriptor)));
handle = GCHandle.Alloc(buffer, GCHandleType.Pinned);
fields.Add((FieldDescriptor)Marshal.PtrToStructure(handle.AddrOfPinnedObject(), typeof(FieldDescriptor)));
handle.Free();
}
// Read in the first row of records, we need this to help determine column types below
((FileStream)br.BaseStream).Seek(header.headerLen + 1, SeekOrigin.Begin);
buffer = br.ReadBytes(header.recordLen);
recReader = new BinaryReader(new MemoryStream(buffer));
// Create the columns in our new DataTable
DataColumn col = null;
foreach (FieldDescriptor field in fields)
{
number = Encoding.GetEncoding(866).GetString(recReader.ReadBytes(field.fieldLen));
switch (field.fieldType)
{
case 'N':
col = new DataColumn(field.fieldName, typeof(string));
break;
case 'C':
col = new DataColumn(field.fieldName, typeof(string));
break;
case 'T':
// You can uncomment this to see the time component in the grid
//col = new DataColumn(field.fieldName, typeof(string));
col = new DataColumn(field.fieldName, typeof(DateTime));
break;
case 'D':
col = new DataColumn(field.fieldName, typeof(DateTime));
break;
case 'L':
col = new DataColumn(field.fieldName, typeof(bool));
break;
case 'F':
col = new DataColumn(field.fieldName, typeof(Double));
break;
}
//col = new DataColumn(field.fieldName, typeof(string));
dt.Columns.Add(col);
}
// Skip past the end of the header.
((FileStream)br.BaseStream).Seek(header.headerLen, SeekOrigin.Begin);
// Read in all the records
for (int counter = 0; counter <= header.numRecords - 1; counter++)
{
// First we'll read the entire record into a buffer and then read each field from the buffer
// This helps account for any extra space at the end of each record and probably performs better
buffer = br.ReadBytes(header.recordLen);
recReader = new BinaryReader(new MemoryStream(buffer));
// All dbf field records begin with a deleted flag field. Deleted - 0x2A (asterisk) else 0x20 (space)
if (recReader.ReadChar() == '*')
{
continue;
}
// Loop through each field in a record
fieldIndex = 0;
row = dt.NewRow();
foreach (FieldDescriptor field in fields)
{
switch (field.fieldType)
{
case 'N': // Number
// If you port this to .NET 2.0, use the Decimal.TryParse method
number = Encoding.GetEncoding(866).GetString(recReader.ReadBytes(field.fieldLen));
if (IsNumber(number))
{
if (number.IndexOf(".") > -1)
{
row[fieldIndex] = decimal.Parse(number.Replace('.', ','));
}
else
{
row[fieldIndex] = int.Parse(number);
}
}
else
{
row[fieldIndex] = number;
}
break;
case 'C': // String
row[fieldIndex] = Encoding.GetEncoding(866).GetString(recReader.ReadBytes(field.fieldLen));
break;
case 'D': // Date (YYYYMMDD)
year = Encoding.GetEncoding(866).GetString(recReader.ReadBytes(4));
month = Encoding.GetEncoding(866).GetString(recReader.ReadBytes(2));
day = Encoding.GetEncoding(866).GetString(recReader.ReadBytes(2));
row[fieldIndex] = System.DBNull.Value;
try
{
if (IsNumber(year) && IsNumber(month) && IsNumber(day))
{
if ((Int32.Parse(year) > 1900))
{
row[fieldIndex] = new DateTime(Int32.Parse(year), Int32.Parse(month), Int32.Parse(day));
}
}
}
catch
{ }
break;
case 'T': // Timestamp, 8 bytes - two integers, first for date, second for time
// Date is the number of days since 01/01/4713 BC (Julian Days)
// Time is hours * 3600000L + minutes * 60000L + Seconds * 1000L (Milliseconds since midnight)
lDate = recReader.ReadInt32();
lTime = recReader.ReadInt32() * 10000L;
row[fieldIndex] = JulianToDateTime(lDate).AddTicks(lTime);
break;
case 'L': // Boolean (Y/N)
//char tf = Convert.ToChar(recReader.ReadByte());
if ('T' == recReader.ReadByte())
{
row[fieldIndex] = true;
}
else
{
row[fieldIndex] = false;
}
break;
case 'F':
number = Encoding.GetEncoding(866).GetString(recReader.ReadBytes(field.fieldLen));
if (IsNumber(number))
{
row[fieldIndex] = double.Parse(number);
}
else
{
row[fieldIndex] = 0.0F;
}
break;
}
fieldIndex++;
}
recReader.Close();
dt.Rows.Add(row);
}
}
catch (Exception ex)
{
error.Text = ex.Message;
}
finally
{
if (null != br)
{
br.Close();
}
}
}
else
{
error.Text = "нет файла";
}
return dt;
}
public static bool IsNumber(string numberString)
{
if (numberString.Length > 9)
return false;
if (numberString.IndexOf('.') == numberString.LastIndexOf('.'))
{
try
{
double chk_nmb = Convert.ToDouble(numberString.Replace('.', ','));
return true;
}
catch (Exception)
{
return false;
}
}
else return false;
}
private static DateTime JulianToDateTime(long lJDN)
{
double s1 = Convert.ToDouble(lJDN) + 68569;
double n = Math.Floor(4 * s1 / 146097);
double s2 = s1 - Math.Floor((146097 * n + 3) / 4);
double i = Math.Floor(4000 * (s2 + 1) / 1461001);
double s3 = s2 - Math.Floor(1461 * i / 4) + 31;
double q = Math.Floor(80 * s3 / 2447);
double s4 = Math.Floor(q / 11);
return new DateTime(Convert.ToInt32(100 * (n - 49) + i + s4), Convert.ToInt32(q + 2 - 12 * s4), Convert.ToInt32(s3 - Math.Floor(2447 * q / 80)));
}
#endregion
и сам процесс загрузки
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26.
protected void Button1_Click(object sender, EventArgs e)
{
string f = Path.GetFileName(FileUpload1.PostedFile.FileName) ;
DataTable dt = ReadDBF(f);
error.Text +=f;
using (SqlConnection NlsSqlConn = new SqlConnection(CS))
{
try
{
if (NlsSqlConn.State == ConnectionState.Closed) NlsSqlConn.Open();
SqlDataReader Reader;
SqlCommand NlsSqlComm = new SqlCommand();
NlsSqlComm.Connection = NlsSqlConn;
NlsSqlComm.CommandType = CommandType.Text;
for (int j = 0; j < dt.Rows.Count; j++)
{
string com = "delete fp where ids='" + Application["users"] + "' insert into fp values('" + Application["users"] + "',";
for (int i = 0; i < dt.Rows[j].ItemArray.Length; i++)
com += "'" + dt.Rows[j].ItemArray[i].ToString().Trim() + "',";
com = com.Remove(com.Length - 1, 1);
com += ")";
NlsSqlComm.CommandText = com;
NlsSqlComm.ExecuteNonQuery();
локально работает, а вот если страничку загрузить на сервак то выдают ошибку "нет файла". Я как понял что то не так с путями но как поправить(((
|
|
|