/// <summary>
/// 将DataReader转为DataTable
/// </summary>
/// <param name="DataReader">DataReader</param>
public static DataTable ConvertdrTodt(SqlDataReader dataReader)
{
///定义DataTable
DataTable datatable = new DataTable();
///动态添加表的数据列
for (int i = 0; i < dataReader.FieldCount; i++)
{
DataColumn mydc = new DataColumn();
mydc.DataType = dataReader.GetFieldType(i);
mydc.ColumnName = dataReader.GetName(i);
datatable.Columns.Add(mydc);
}
///添加表的数据
while (dataReader.Read())
{
DataRow mydr = datatable.NewRow();
for (int i = 0; i < dataReader.FieldCount; i++)
{
mydr[i] = dataReader[i].ToString();
}
datatable.Rows.Add(mydr);
mydr = null;
}
///关闭数据读取器
dataReader.Close();
return datatable;
}
/// <summary>
/// SqlDataReaderתDataTableTable
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public static DataTable Convertdrtodt(SqlDataReader dr, DataTable dataTable)
{
//DataTable dataTable = new DataTable();//��һ���µ�ʵ��
for (int i = 0; i < dr.FieldCount; i++)
{
DataColumn mydc = new DataColumn();//�ؼ���һ��
mydc.DataType = dr.GetFieldType(i);
mydc.ColumnName = dr.GetName(i);
dataTable.Columns.Add(mydc);//�ؼ��ĵڶ���
}
while (dr.Read())
{
DataRow mydr = dataTable.NewRow();//�ؼ��ĵ�����
for (int i = 0; i < dr.FieldCount; i++)
{
mydr[i] = dr[i];//.ToString();
}
dataTable.Rows.Add(mydr);//�ؼ��ĵ��IJ�
mydr = null;
}
dr.Close();
return (dataTable);//������Ҫ����datatable���������
}
/// <summary>
/// returns DataTable from stored procedure.
/// </summary>
/// <param name="storeProcedure"></param>
/// <returns></returns>
public DataTable TableFromProc(SqlCommand cmd)
{
DataTable table = new DataTable();
cmd.Connection = new SqlConnection(this._connectionString);
cmd.Connection.Open();
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
if ((table.Columns.Count == 0))
{
table.TableName = cmd.CommandText;
for (int i = 0; (i < reader.FieldCount); i = i + 1
)
{
System.Type type = reader.GetFieldType(i);
string name = reader.GetName(i);
table.Columns.Add(name, type);
}
}
table.Clear();
int result = 0;
for (; reader.Read(); result = result + 1)
{
System.Data.DataRow row = table.NewRow();
object[] rowdata = new object[reader.FieldCount];
reader.GetValues(rowdata);
row.ItemArray = rowdata;
table.Rows.Add(row);
}
reader.Close();
return(table);
}
// Метод формирования DataTable с именем tableName из SqlDataReader
private static DataTable CreateSchemaFromReader(SqlDataReader reader, string tableName)
{
DataTable table = new DataTable(tableName);
// Добавление в таблицу нового столбца (Имя - Тип )
for (int i = 0; i < reader.FieldCount; i++)
table.Columns.Add(new DataColumn(reader.GetName(i), reader.GetFieldType(i)));
return table;
}
/// <summary>
/// Given a DataReader a set of columns are created for the ListControl
/// </summary>
/// <param name="dataReader"></param>
/// <returns></returns>
private static List <SQLColumn> CreateColumns(System.Data.SqlClient.SqlDataReader dataReader)
{
List <SQLColumn> superListColumns = new List <SQLColumn>();
for (int i = 0; i < dataReader.FieldCount; i++)
{
superListColumns.Add(CreateColumn(dataReader.GetFieldType(i), dataReader.GetName(i), i));
}
return(superListColumns);
}
private string GetObjectValue(System.Data.SqlClient.SqlDataReader reader, int i)
{
string fieldType = reader.GetFieldType(i).ToString();
object FieldValue = null;;
if (fieldType.Contains("Int32"))
{
FieldValue = reader.GetInt32(i);
}
else if (fieldType.Contains("Int64"))
{
FieldValue = reader.GetInt64(i);
}
else if (fieldType.Contains("Decimal"))
{
FieldValue = reader.GetDecimal(i);
}
else if (fieldType.Contains("Double"))
{
FieldValue = reader.GetDouble(i);
}
else if (fieldType.Contains("DateTime"))
{
FieldValue = reader.GetDateTime(i);
}
else if (fieldType.Contains("String"))
{
FieldValue = reader.GetString(i);
}
else if (fieldType.Contains("Boolean"))
{
FieldValue = reader.GetBoolean(i);
}
else if (fieldType.Contains("Byte"))
{
FieldValue = reader.GetByte(i);
}
else if (fieldType.Contains("Guid"))
{
FieldValue = reader.GetGuid(i);
}
else
{
FieldValue = reader.GetString(i);
}
if (FieldValue != null)
{
return(FieldValue.ToString());
}
else
{
return("");
}
}
public SqlDataHelper(SqlDataReader reader)
{
_reader=reader;
if (_cnames == null)
{
_cnames = new string[_reader.FieldCount];
_cdbtypes = new string[_reader.FieldCount];
_ctypes = new Type[_reader.FieldCount];
for (int i = 0; i < _reader.FieldCount; i++)
{
_cdbtypes[i] = _reader.GetDataTypeName(i);
_ctypes[i] = _reader.GetFieldType(i);
_cnames[i] = _reader.GetName(i);
}
}
}
public static string CreateObject(SqlDataReader sdr, string objectName, bool camelCase)
{
if (sdr == null || !sdr.HasRows) return string.Empty;
System.Text.StringBuilder js = new System.Text.StringBuilder("var " + objectName + " = [");
string[] names = JSOMembers(sdr, camelCase);
while (sdr.Read())
{
js.Append("\n{");
for (int i = 0; i < sdr.FieldCount; i++)
{
// Response.Write(sdr.GetFieldType(i).Name.ToLower() + "\n");
switch (sdr.GetFieldType(i).Name.ToLower())
{
case "int":
case "byte":
case "long":
case "short":
case "decimal":
case "double":
js.Append(names[i] + ": " + sdr[i].ToString());
break;
case "boolean":
js.Append(names[i] + ": " + sdr.GetBoolean(i).ToString().ToLower());
break;
default:
js.Append(names[i] + ": '" + sdr[i].ToString() + "'");
break;
}
if ((i + 1) < sdr.FieldCount)
js.Append(',');
}
js.Append("},");
}
js[js.Length - 1] = ']';
js.Append(';');
return js.ToString();
}
/// <summary>
/// ��DataReaderתΪDataTable
/// </summary>
/// <param name="DataReader">DataReader</param>
public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
{
///����DataTable
DataTable datatable = new DataTable();
try
{ ///��̬��ӱ��������
for(int i = 0; i < dataReader.FieldCount; i++)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = dataReader.GetFieldType(i);
myDataColumn.ColumnName = dataReader.GetName(i);
datatable.Columns.Add(myDataColumn);
}
///��ӱ������
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < dataReader.FieldCount; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
///�ر����ݶ�ȡ��
dataReader.Close();
return datatable;
}
catch(Exception ex)
{
///�׳�����ת������
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message,ex);
}
}
/// <summary>
/// DataReader格式转换成DataTable
/// </summary>
/// <param name="DataReader">OleDbDataReader</param>
private DataTable GetConvertDataReaderToDataTable(SqlDataReader reader)
{
DataTable objDataTable = new DataTable("TmpDataTable");
int intCounter;
try
{
//获取当前行中的列数；
int intFieldCount = reader.FieldCount;
for (intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
{
objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
}
//populate datatable
objDataTable.BeginLoadData();
//object[] objValues = new object[intFieldCount -1];
object[] objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData();
return objDataTable;
}
catch (SqlException ex)
{
throw ex;
}
}
public static System.DateTime getFieldValue(SqlDataReader dr, int ind, System.DateTime def)
{
System.DateTime val = def;
if (!dr.IsDBNull(ind))
{
System.TypeCode tp = System.Type.GetTypeCode(dr.GetFieldType(ind));
System.TypeCode typeCode = tp;
switch (typeCode)
{
case System.TypeCode.Byte:
val = System.Convert.ToDateTime(dr.GetByte(ind));
break;
case System.TypeCode.Int16:
val = System.Convert.ToDateTime(dr.GetInt16(ind));
break;
case System.TypeCode.UInt16:
break;
case System.TypeCode.Int32:
val = System.Convert.ToDateTime(dr.GetInt32(ind));
break;
default:
if (typeCode == System.TypeCode.DateTime)
{
val = dr.GetDateTime(ind);
}
break;
}
}
return val;
}
public static bool getFieldValue(SqlDataReader dr, int ind, bool def)
{
bool val = def;
if (!dr.IsDBNull(ind))
{
switch (System.Type.GetTypeCode(dr.GetFieldType(ind)))
{
case System.TypeCode.Boolean:
val = dr.GetBoolean(ind);
break;
case System.TypeCode.Byte:
val = System.Convert.ToBoolean(dr.GetByte(ind));
break;
case System.TypeCode.Int16:
val = System.Convert.ToBoolean(dr.GetInt16(ind));
break;
case System.TypeCode.Int32:
val = System.Convert.ToBoolean(dr.GetInt32(ind));
break;
}
}
return val;
}
/// <summary>
/// Constructs the data which was extracted
/// from the database according to user's query.
/// </summary>
/// <param name="reader">SqlReader - holds the queried data.</param>
///<returns>Queried data in DataTable.</returns>
private static DataTable ConstructData(SqlDataReader reader)
{
try
{
if (reader.IsClosed)
throw new
InvalidOperationException("Attempt to" +
" use a closed SqlDataReader");
DataTable dataTable = new DataTable();
// constructs the columns data.
for (int i = 0; i < reader.FieldCount; i++)
dataTable.Columns.Add(reader.GetName(i),
reader.GetFieldType(i));
// constructs the table's data.
while (reader.Read())
{
object[] row = new object[reader.FieldCount];
reader.GetValues(row);
dataTable.Rows.Add(row);
}
// Culture info.
// TODO: get locale from app
dataTable.Locale = new System.Globalization.CultureInfo("en-US");
// Accepts changes.
dataTable.AcceptChanges();
return dataTable;
}
catch (Exception ex)
{
logger.Error(SQLSchemaTool.ERRORFORMAT, ex.Message, ex.Source, ex.StackTrace);
throw ex;
}
}
// Jika tidak menggunakan constructor
// hapus pada baris yg di comment
// ====================================
// | Alt + Insert untuk meng-generate |
// | construktor dari class yg dibuat |
// ====================================
public string PengecekField(SqlDataReader hasilPembaca, byte kolom)
{
string hasil = "";
if (hasilPembaca.IsDBNull(kolom))
{
if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Byte")
hasil = "0";
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Boolean")
hasil = "false";
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Int32")
hasil = "0";
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Int16")
hasil = "0";
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.String")
hasil = "";
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Decimal")
hasil = "0";
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Int64")
hasil = "0";
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.DateTime")
hasil = "0";
else
{
MessageBox.Show(Resources.CAlat_PengecekField_Tipe_data_tidak_di_kenali, Resources.CAlat_PengecekField_Infomasi, MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
else
{
if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Byte")
hasil = hasilPembaca.GetByte(kolom).ToString().Trim();
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Boolean")
hasil = hasilPembaca.GetBoolean(kolom).ToString().Trim();
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Int32")
hasil = hasilPembaca.GetInt32(kolom).ToString().Trim();
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Int16")
hasil = hasilPembaca.GetInt16(kolom).ToString().Trim();
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.String")
hasil = hasilPembaca.GetString(kolom).ToString().Trim();
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Decimal")
hasil = hasilPembaca.GetDecimal(kolom).ToString().Trim();
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.Int64")
hasil = hasilPembaca.GetInt64(kolom).ToString().Trim();
else if (hasilPembaca.GetFieldType(kolom).ToString().Trim() == "System.DateTime")
hasil = string.Format("{0:MM/dd/yyyy}", hasilPembaca.GetDateTime(kolom));
else
{
MessageBox.Show(Resources.CAlat_PengecekField_Tipe_data_tidak_di_kenali, Resources.CAlat_PengecekField_Infomasi, MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
return hasil;
}
private static DataTable CreateSchemaFromReader(SqlDataReader reader, string tableName)
{
DataTable table = new DataTable(tableName);
for (int i = 0; i < reader.FieldCount; i++)
table.Columns.Add(new DataColumn(reader.GetName(i), reader.GetFieldType(i)));
return table;
}
/// <summary>
/// Constructs the data which was extracted from the database according to user's query.
/// </summary>
/// <param name="reader">SqlReader - holds the queried data.</param>
///<returns>Queried data in DataTable.</returns>
private static DataTable ConstructData(SqlDataReader reader)
{
try
{
if(reader.IsClosed)
throw new InvalidOperationException("Attempt to use a closed SqlDataReader");
DataTable dataTable = new DataTable();
// constructs the columns data.
for(int i=0; i<reader.FieldCount; i++)
dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
// constructs the table's data.
while(reader.Read())
{
object[] row = new object[reader.FieldCount];
reader.GetValues(row);
dataTable.Rows.Add(row);
}
// Culture info.
dataTable.Locale = CultureInfo.InvariantCulture;
// Accepts changes.
dataTable.AcceptChanges();
return dataTable;
}
catch(Exception e)
{
Log.WriteErrorToLog(e.Message);
throw;
}
}
long GetInt64(SqlDataReader reader, int colnum) {
// For some reason, it won't just up-convert int32 to int64
if (reader.GetFieldType(colnum) == typeof(System.Int32)) {
return reader.GetInt32(colnum);
} else if (reader.GetFieldType(colnum) == typeof(System.Int64)) {
return reader.GetInt64(colnum);
} else if (reader.GetFieldType(colnum) == typeof(System.Decimal)) {
return (long)reader.GetDecimal(colnum);
} else {
// Unknown type
bool unknown_type_in_result = true;
WVFAIL(unknown_type_in_result);
return -1;
}
}
public static StringBuilder ReplaceTemplate(SqlDataReader sdr, StringBuilder template, bool IsRemoveNoData)
{
DataTable dt = new DataTable();
int fcnt=sdr.FieldCount;
for (int i = 0; i < fcnt; i++)
{
dt.Columns.Add(new DataColumn(sdr.GetName(i),sdr.GetFieldType(i)));
}
DataRow row = dt.NewRow();
object[] Values = new object[fcnt];
sdr.GetValues(Values);
row.ItemArray = Values;
return ReplaceTemplate(row, template, IsRemoveNoData);
}
private static List<Dictionary<string, object>> CheckAndReturnValue(SqlDataReader reader)
{
// Change History
//
// Date Edit Author Comment
// -----------+-------+-------+---------------------------------------------
// 02-Feb-12 [100] SSN Created
// -----------+-------+-------+---------------------------------------------
object obj;
int Ordinal = 0;
obj = null;
List<Dictionary<string, object>> results = new List<Dictionary<string, object>>();
Dictionary<string, object> ColValues;
while (reader.Read())
{
ColValues = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetFieldType(i) == Type.GetType("System.String"))
{
obj = reader.IsDBNull(i) ? String.Empty : reader.GetValue(i);
}
else if (reader.GetFieldType(i) == Type.GetType("System.Decimal"))
{
obj = reader.IsDBNull(i) ? 0 : reader.GetValue(i);
}
else if (reader.GetFieldType(i) == Type.GetType("System.DateTime"))
{
obj = reader.IsDBNull(i) ? new DateTime() : reader.GetDateTime(i);
}
else
{
obj = reader.IsDBNull(Ordinal) ? null : reader.GetValue(i);
}
ColValues.Add(reader.GetName(i).ToUpper(), obj);
}
results.Add(ColValues);
}
return results;
}
/// <summary>
/// Convierte un valor en un SqlDataReader a JSON para ser incluido en un JSN array
/// </summary>
/// <param name="Lector">Referencia hacia al lector (SqlDataReader)</param>
/// <param name="Puntero">Puntero del valor a convertir dentro del lector (SqlDataReader)</param>
/// <returns>Valor para un objeto JSON</returns>
private string TomarValorJSON(ref SqlDataReader Lector,ref short Puntero)
{
return string.Format(
"\"{0}\":{1}",
Lector.GetName(Puntero),
(Lector.IsDBNull(Puntero)) ? "null" :
((Lector.GetFieldType(Puntero) == typeof(bool) || Lector.GetFieldType(Puntero) == typeof(Boolean)) ?
((Lector.GetFieldValue<bool>(Puntero)) ? "true" : "false") :
((Lector.GetFieldType(Puntero) == typeof(int) || Lector.GetFieldType(Puntero) == typeof(byte)
|| Lector.GetFieldType(Puntero) == typeof(Int16) || Lector.GetFieldType(Puntero) == typeof(float)
|| Lector.GetFieldType(Puntero) == typeof(double)) || Lector.GetFieldType(Puntero) == typeof(decimal) ?
Lector.GetFieldValue<object>(Puntero) :
(Lector.GetFieldType(Puntero) == typeof(DateTime) ? UnixTicks(Lector.GetDateTime(Puntero)) :
"\"" + Lector.GetFieldValue<object>(Puntero)
.ToString()
.Replace("\n", "<br>")
//.Replace("\t", "\\\\u09")
.Replace("\r", "<br>")
//.Replace("\\", "\\\\u2f")
//.Replace("'", "\\\\u27")
.Replace("\"", "\\\\u22")
//.Replace("{", "\\\\u7b")
//.Replace("}", "\\\\u7e")
//.Replace("[", "\\\\u5b")
//.Replace("]", "\\\\u5d")
//.Replace(":", "\\\\u3a")
+ "\""))
)
)
;
}
/// <summary>
/// [To be supplied.]
/// </summary>
/// <returns>[To be supplied.]</returns>
public override string ToString()
{
if (!this.recordWasLoadedFromDB)
{
throw new ArgumentException("No record was loaded from the database. The DisplayName is not available.");
}
if (this.displayName == null)
{
bool alreadyOpened = false;
Params.spS_Customers_Display Param = new Params.spS_Customers_Display(true);
switch (this.lastKnownConnectionType)
{
case Bob.DataClasses.ConnectionType.ConnectionString:
Param.SetUpConnection(this.connectionString);
break;
case Bob.DataClasses.ConnectionType.SqlConnection:
Param.SetUpConnection(this.sqlConnection);
alreadyOpened = (this.sqlConnection.State == System.Data.ConnectionState.Open);
break;
case Bob.DataClasses.ConnectionType.SqlTransaction:
Param.SetUpConnection(this.sqlTransaction);
break;
}
Param.Param_CustomerID = this.col_CustomerID;
System.Data.SqlClient.SqlDataReader sqlDataReader = null;
SPs.spS_Customers_Display Sp = new SPs.spS_Customers_Display(false);
if (Sp.Execute(ref Param, out sqlDataReader))
{
if (sqlDataReader.Read())
{
if (!sqlDataReader.IsDBNull(SPs.spS_Customers_Display.Resultset1.Fields.Column_Display.ColumnIndex))
{
if (sqlDataReader.GetFieldType(SPs.spS_Customers_Display.Resultset1.Fields.Column_Display.ColumnIndex) == typeof(string))
{
this.displayName = sqlDataReader.GetString(SPs.spS_Customers_Display.Resultset1.Fields.Column_Display.ColumnIndex);
}
else
{
this.displayName = sqlDataReader.GetValue(SPs.spS_Customers_Display.Resultset1.Fields.Column_Display.ColumnIndex).ToString();
}
}
}
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
CloseConnection(Sp.Connection, alreadyOpened);
}
else
{
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
CloseConnection(Sp.Connection, alreadyOpened);
throw new Bob.DataClasses.CustomException(Param, "Bob.BusinessComponents.Customer", "ToString");
}
}
return(this.displayName);
}
private Dictionary<string, string> GetRow(SqlDataReader reader)
{
var headers = new Dictionary<string, string>();
for (var i = 0; i < reader.FieldCount; ++i)
{
if (reader.GetFieldType(i) == typeof (DateTime))
{
headers.Add(reader.GetName(i), ((DateTime) reader.GetValue(i)).ToString("o")+"Z");
}
else if (reader.GetFieldType(i) == typeof(Byte[]))
{
}
else
{
headers.Add(reader.GetName(i), reader.GetValue(i).ToString());
}
}
return headers;
}
static JToken ToToken(SqlDataReader reader, int i)
{
if (reader.IsDBNull(i))
{
return JValue.Parse("null");
}
else if (typeof(bool).IsAssignableFrom(reader.GetFieldType(i)))
{
return new JValue((bool)reader.GetValue(i));
}
else if (typeof(int).IsAssignableFrom(reader.GetFieldType(i)))
{
return new JValue((int)reader.GetValue(i));
}
else if (typeof(double).IsAssignableFrom(reader.GetFieldType(i)))
{
return new JValue((double)reader.GetValue(i));
}
else
{
return new JValue(reader.GetValue(i).ToString());
}
}
/// <summary>
/// [To be supplied.]
/// </summary>
public void Refresh()
{
internalRecords = new System.Collections.ArrayList();
bool alreadyOpened = false;
Params.spS_JobPartType_Display Param = new Params.spS_JobPartType_Display(true);
Param.CommandTimeOut = this.selectCollectionCommandTimeOut;
switch (this.lastKnownConnectionType)
{
case Bob.DataClasses.ConnectionType.ConnectionString:
Param.SetUpConnection(this.connectionString);
break;
case Bob.DataClasses.ConnectionType.SqlConnection:
Param.SetUpConnection(this.sqlConnection);
alreadyOpened = (this.sqlConnection.State == System.Data.ConnectionState.Open);
break;
case Bob.DataClasses.ConnectionType.SqlTransaction:
Param.SetUpConnection(this.sqlTransaction);
break;
}
System.Data.SqlClient.SqlDataReader sqlDataReader = null;
SPs.spS_JobPartType_Display Sp = new SPs.spS_JobPartType_Display(false);
if (Sp.Execute(ref Param, out sqlDataReader))
{
while (sqlDataReader.Read())
{
JobPartType_Record record = null;
switch (this.lastKnownConnectionType)
{
case Bob.DataClasses.ConnectionType.ConnectionString:
record = new JobPartType_Record(this.connectionString, sqlDataReader.GetSqlInt32(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_ID1.ColumnIndex));
break;
case Bob.DataClasses.ConnectionType.SqlConnection:
record = new JobPartType_Record(this.sqlConnection, sqlDataReader.GetSqlInt32(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_ID1.ColumnIndex));
break;
case Bob.DataClasses.ConnectionType.SqlTransaction:
record = new JobPartType_Record(this.sqlTransaction, sqlDataReader.GetSqlInt32(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_ID1.ColumnIndex));
break;
}
record.UpdateCommandTimeOut = this.updateCommandTimeOut;
record.SelectCommandTimeOut = this.selectCommandTimeOut;
if (sqlDataReader.GetFieldType(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_Display.ColumnIndex) == typeof(string))
{
record.displayName = sqlDataReader.GetString(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_Display.ColumnIndex);
}
else
{
record.displayName = sqlDataReader.GetValue(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_Display.ColumnIndex).ToString();
}
internalRecords.Add(record);
}
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
CloseConnection(Sp.Connection, alreadyOpened);
this.recordsAreLoaded = true;
}
else
{
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
CloseConnection(Sp.Connection, alreadyOpened);
this.recordsAreLoaded = false;
throw new Bob.DataClasses.CustomException(Param, "Bob.BusinessComponents.JobPartType_Collection", "Refresh");
}
}
private DataTable CreateTable(SqlDataReader reader)
{
var ds = new DataSet();
DataTable table = ds.Tables.Add("temp");
// Add the table columns.
for (int i = 0; i < reader.FieldCount; i++)
table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
return table;
}
public bool SetData(SqlDataReader reader, string TabName)
{
bool result = false;
int i = 0;
bool HeaderGenerated = false;
WorksheetRow row = null;
WorksheetColumn col = null;
WorksheetStyle style = this.Styles.Add("Default");
style = this.Styles.Add("_HeaderStyle");
style.Font.Bold = true;
style = this.Styles.Add("_DateTime");
style.NumberFormat = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";
style = this.Styles.Add("_Integer");
style.NumberFormat = "0";
ExcelWorksheet sheet = this.WorkSheets.Add(TabName);
if (reader.HasRows)
{
while (reader.Read())
{
row = sheet.Table.Rows.Add();
if (!HeaderGenerated)
{
for (i = 0; i < reader.FieldCount - 1; i++)
{
col = sheet.Table.Columns.Add();
col.AutoFitWidth = true;
col.Width = 99.75;
switch (reader.GetFieldType(i).Name)
{
case "DateTime":
col.StyleName = "_DateTime";
break;
case "Int16":
col.StyleName = "_Integer";
break;
}
row.Cells.Add(reader.GetName(i), "_HeaderStyle");
}
HeaderGenerated = true;
}
else
{
WorksheetCell cell = null;
for (i = 0; i < reader.FieldCount - 1; i++)
{
switch (reader.GetFieldType(i).Name)
{
case "DateTime":
cell = new WorksheetCell();
if (reader[i] != System.DBNull.Value)
cell = row.Cells.Add(reader.GetDateTime(i).ToString("yyyy-MM-ddTHH:mm:ss.fff"), CellDataType.DateTime);
else
cell = row.Cells.Add("", CellDataType.String);
break;
case "Int16":
cell = new WorksheetCell();
if (reader[i] != System.DBNull.Value)
cell = row.Cells.Add(reader.GetInt16(i).ToString(), CellDataType.Number);
else
cell = row.Cells.Add("", CellDataType.String);
break;
default:
row.Cells.Add(reader[i].ToString());
break;
}
}
}
}
result = true;
}
else
{
result = false;
}
return result;
}
public static string getFieldValue(SqlDataReader dr, int ind, string def)
{
string val = def;
if (!dr.IsDBNull(ind))
{
switch (System.Type.GetTypeCode(dr.GetFieldType(ind)))
{
case System.TypeCode.Boolean:
val = (dr.GetBoolean(ind) ? "1" : "0");
break;
case System.TypeCode.Int16:
val = dr.GetInt16(ind).ToString();
break;
case System.TypeCode.Int32:
val = dr.GetInt32(ind).ToString();
break;
case System.TypeCode.Int64:
val = dr.GetInt64(ind).ToString();
break;
case System.TypeCode.Single:
val = dr.GetFloat(ind).ToString();
break;
case System.TypeCode.Double:
val = dr.GetDouble(ind).ToString();
break;
case System.TypeCode.Decimal:
val = dr.GetDecimal(ind).ToString();
break;
case System.TypeCode.DateTime:
val = DBManagerSQL.DBDateToFormatedDate(dr.GetDateTime(ind));
break;
case System.TypeCode.String:
val = dr.GetString(ind);
break;
}
}
return val;
}
public static BsonDocument Build(SqlDataReader dr)
{
BsonDocument bsonDocument = new BsonDocument();
for(int i=0; i<dr.FieldCount; i++)
{
Type propertyType = dr.GetFieldType(i);
if (dr.GetName(i) == "ObjectId")
{
WriteObjectId(dr, i, bsonDocument);
}
else if (dr.GetName(i) == "Timestamp")
{
WriteTimestamp(dr, i, bsonDocument);
}
else if (dr.GetName(i) == "ReportNo")
{
WriteReportNo(dr, i, bsonDocument);
}
else if (dr.GetName(i) == "PanelOrderId")
{
WriteReportNo(dr, i, bsonDocument);
}
else if (propertyType == typeof(string))
{
WriteString(dr, i, bsonDocument);
}
else if (propertyType == typeof(int))
{
WriteInt(dr, i, bsonDocument);
}
else if (propertyType == typeof(double))
{
WriteDouble(dr, i, bsonDocument);
}
else if (propertyType == typeof(decimal))
{
WriteDouble(dr, i, bsonDocument);
}
else if (propertyType == typeof(Nullable<int>))
{
WriteInt(dr, i, bsonDocument);
}
else if (propertyType == typeof(DateTime))
{
WriteDateTime(dr, i, bsonDocument);
}
else if (propertyType == typeof(bool))
{
WriteBoolean(dr, i, bsonDocument);
}
else if (propertyType == typeof(Nullable<bool>))
{
WriteBoolean(dr, i, bsonDocument);
}
else if (propertyType == typeof(Nullable<DateTime>))
{
WriteDateTime(dr, i, bsonDocument);
}
else
{
throw new Exception("This Data Type is Not Implemented: " + dr.GetFieldType(i).ToString() + ": " + dr.GetName(i));
}
}
return bsonDocument;
}
public Object GetItem(SqlDataReader _reader, int i)
{
object value = _reader.GetValue(i);
var fieldType = _reader.GetFieldType(i);
//if (res != null && res.GetType() == typeof(DateTime))
// return ((DateTime)res).ToString("yyyy-MM-dd HH:mm:ss");
if (value == DBNull.Value)
{
if (fieldType== typeof(String))
return string.Empty;
else
return Single.NaN;
}
else
{
if (fieldType == typeof(long))
return Convert.ToInt32(value);
}
return value;
}
public static double getFieldValue(SqlDataReader dr, int ind, double def)
{
double val = def;
if (!dr.IsDBNull(ind))
{
switch (System.Type.GetTypeCode(dr.GetFieldType(ind)))
{
case System.TypeCode.Boolean:
val = (dr.GetBoolean(ind) ? 1.0 : 0.0);
break;
case System.TypeCode.Int16:
val = System.Convert.ToDouble(dr.GetInt16(ind));
break;
case System.TypeCode.Int32:
val = System.Convert.ToDouble(dr.GetInt32(ind));
break;
case System.TypeCode.Int64:
val = System.Convert.ToDouble(dr.GetInt64(ind));
break;
case System.TypeCode.Single:
val = System.Convert.ToDouble(dr.GetFloat(ind));
break;
case System.TypeCode.Double:
val = dr.GetDouble(ind);
break;
case System.TypeCode.Decimal:
val = System.Convert.ToDouble(dr.GetDecimal(ind));
break;
}
}
return val;
}
/// <summary>
/// 根据datareader获取datatable
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
///
public static DataTable GetTableByReader(SqlDataReader reader)
{
DataTable dt = new DataTable();
for (int i = 0; i < reader.FieldCount; i++)
{
DataColumn dc = new DataColumn(reader.GetName(i), reader.GetFieldType(i));
dt.Columns.Add(dc);
}
while (reader.Read())
{
DataRow dr = dt.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dr[i] = reader[i].ToString();
}
dt.Rows.Add(dr);
dr = null;
}
reader.Close();
return dt;
}
public static short getFieldValue(SqlDataReader dr, int ind, short def)
{
short val = def;
if (!dr.IsDBNull(ind))
{
System.TypeCode tp = System.Type.GetTypeCode(dr.GetFieldType(ind));
System.TypeCode typeCode = tp;
switch (typeCode)
{
case System.TypeCode.Boolean:
val = (dr.GetBoolean(ind) ? System.Convert.ToInt16(1) : System.Convert.ToInt16(0));
break;
case System.TypeCode.Char:
case System.TypeCode.SByte:
case System.TypeCode.UInt16:
break;
case System.TypeCode.Byte:
val = System.Convert.ToInt16(dr.GetByte(ind));
break;
case System.TypeCode.Int16:
val = dr.GetInt16(ind);
break;
case System.TypeCode.Int32:
val = System.Convert.ToInt16(dr.GetInt32(ind));
break;
default:
if (typeCode == System.TypeCode.Decimal)
{
val = System.Convert.ToInt16(dr.GetDecimal(ind));
}
break;
}
}
return val;
}
