public void FillComboBoxItems(System.Data.SqlClient.SqlDataReader sdr, string displayMember, string valueMember)
{
if (sdr == null)
{
return;
}
int iValue = -1;
int iDisplay = -1;
for (int i = 0; i < sdr.FieldCount; i++)
{
if (valueMember == sdr.GetName(i))
{
iValue = i;
}
else if (displayMember == sdr.GetName(i))
{
iDisplay = i;
}
}
if (iDisplay == -1 || iValue == -1)
{
return;
}
FillComboBoxItems(sdr, iDisplay, iValue);
}
private ICollection <UsageObject> GetResultFromReader(System.Data.SqlClient.SqlDataReader reader)
{
var databaseName = (string)reader["database_name"];
var start_time = DateTime.SpecifyKind((DateTime)reader["start_time"], DateTimeKind.Utc);
var result = new List <UsageObject>();
for (var i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
if (NotCounterColumns.Contains(name))
{
continue;
}
var valueResult = GetDouble(reader[i]);
if (valueResult == null)
{
continue;
}
result.Add(new UsageObject {
Timestamp = start_time.ToString("o"), Value = (double)valueResult, GraphiteCounterName = GetCounterName(databaseName, name)
});
}
return(result);
}
public static DataTable GetDataTableFromReader(System.Data.SqlClient.SqlDataReader DataReader)
{
DataTable newTable = new DataTable();
DataColumn Col;
DataRow Row;
for (int i = 0; i < DataReader.FieldCount - 1; i++)
{
Col = new DataColumn();
Col.ColumnName = DataReader.GetName(i);
Col.DataType = DataReader.GetFieldType(i);
newTable.Columns.Add(Col);
}
while (DataReader.Read())
{
Row = newTable.NewRow();
for (int i = 0; i < DataReader.FieldCount - 1; i++)
{
Row[i] = DataReader[i];
}
newTable.Rows.Add(Row);
}
return(newTable);
}
public static void Out2ExcelDataReader(System.Data.SqlClient.SqlDataReader dr, string fileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
HSSFRow dataRow = null;
HSSFCell cell = null;
int sheetMaxRowIndex = 65535;
int currentRowIndex = 0;
List <string> drColumnNameList = new List <string>();
for (int i = 0; i < dr.FieldCount; i++)
{
drColumnNameList.Add(dr.GetName(i));
}
while (dr.Read())
{
if (currentRowIndex == 0)
{
//填充表头
dataRow = sheet.CreateRow(currentRowIndex) as HSSFRow;
for (int i = 0; i < drColumnNameList.Count; i++)
{
cell = dataRow.CreateCell(i) as HSSFCell;
cell.SetCellValue(drColumnNameList[i]);
}
currentRowIndex++;
}
dataRow = sheet.CreateRow(currentRowIndex) as HSSFRow;
for (int i = 0; i < drColumnNameList.Count; i++)
{
cell = dataRow.CreateCell(i) as HSSFCell;
cell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cell.SetCellValue(dr[i].ToString());
}
currentRowIndex++;
if (currentRowIndex > sheetMaxRowIndex)
{
sheet = workbook.CreateSheet() as HSSFSheet;
currentRowIndex = 0;
}
}
dr.Close();
dr.Dispose();
//保存
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
//sheet.Dispose();
//workbook.Dispose();
cell = null;
dataRow = null;
sheet = null;
workbook = null;
Out2Client(ms, fileName);
}
}
public string GetFieldsFromQuery(string Query)
{
using (System.Data.SqlClient.SqlCommand Command = GetCommand("SELECT * FROM (" + Query + ") S WHERE 1=2")) {
using (System.Data.SqlClient.SqlDataReader reader = Command.ExecuteReader()) {
string result = "";
for (int I = 0; I < reader.VisibleFieldCount; I++)
{
result += (I == 0 ? "" : ", ") + "[" + reader.GetName(I) + "]";
}
return(result);
}
}
}
public string GetName(int Index)
{
if (DataReader == null)
{
return(CAbc.EMPTY);
}
try {
return(DataReader.GetName(Index));
} catch (System.Exception Excpt) {
Err.Add(Excpt);
return(CAbc.EMPTY);
}
}
/// <summary>
/// DataReader转换为Json
/// </summary>
/// <param name="dataReader">DataReader对象</param>
/// <returns>Json字符串(数组）</returns>
///
public static string DataReaderToJson(System.Data.SqlClient.SqlDataReader dataReader)
{
StringBuilder jsonString = new StringBuilder();
jsonString.Append("[");
while (dataReader.Read())
{
jsonString.Append("{");
for (int i = 0; i < dataReader.FieldCount; i++)
{
Type type = dataReader.GetFieldType(i);
string strKey = dataReader.GetName(i);
string strValue = dataReader[i].ToString();
jsonString.Append("\"" + strKey + "\":");
strValue = String.Format(strValue, type);
//datetime不能出现为空的情况,所以将其转换成字符串来进行处理。
//需要加""的
if (type == typeof(string) || type == typeof(DateTime))
{
if (i <= dataReader.FieldCount - 1)
{
jsonString.Append("\"" + strValue + "\",");
}
else
{
jsonString.Append(strValue);
}
}
//不需要加""的
else
{
if (i <= dataReader.FieldCount - 1)
{
jsonString.Append("" + strValue + ",");
}
else
{
jsonString.Append(strValue);
}
}
}
jsonString.Append("},");
}
dataReader.Close();
jsonString.Remove(jsonString.Length - 3, 3);
jsonString.Append("}");
jsonString.Append("]");
return(jsonString.ToString());
}
public static void OutCsvDataReader(System.Data.SqlClient.SqlDataReader dr, string fileName)
{
StringBuilder sb = new StringBuilder();
//int sheetMaxRowIndex = 65535;
int currentRowIndex = 0;
List <string> drColumnNameList = new List <string>();
for (int i = 0; i < dr.FieldCount; i++)
{
drColumnNameList.Add(dr.GetName(i));
}
while (dr.Read())
{
if (currentRowIndex == 0)
{
//填充表头
for (int i = 0; i < drColumnNameList.Count; i++)
{
sb.Append("\"");
sb.Append(drColumnNameList[i]);
sb.Append("\",");
}
sb.Append("\r\n");
currentRowIndex++;
}
for (int i = 0; i < drColumnNameList.Count; i++)
{
sb.Append("\"");
sb.Append(dr[i].ToString());
sb.Append("\",");
}
sb.Append("\r\n");
currentRowIndex++;
//if (currentRowIndex > sheetMaxRowIndex)
//{
// break;
//}
}
dr.Close();
dr.Dispose();
//保存
Out2ClientCsv(sb.ToString(), fileName);
}
/// <summary>
/// 执行标准的SQL语句并返回单行数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public Row GetRow(string sql)
{
//throw new NotImplementedException();
Row row = new Row();
using (System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(sql, dbc)) {
using (System.Data.SqlClient.SqlDataReader reader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.Default)) {
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
//item.Set(SqlReader.GetName(i), SqlReader[i].ToString());
string szName = reader.GetName(i);
row[szName] = reader[i].ToString();
}
}
reader.Close();
}
}
return(row);
}
public string GetFieldName(int fieldIndex)
{
return(_reader.GetName(fieldIndex));
}
void PreserveSQLHistory(ref string _SQLTable)
{
string sHistorySuffix = "History";
if (_NumInstance > 1)
{
sHistorySuffix = "_History";
}
StringBuilder messageText = new StringBuilder();
try
{
SqlConnectionInfo connInfo = new SqlConnectionInfo(Properties.Settings.Default.SQLServer);
IDbConnection conn = connInfo.CreateConnectionObject();
conn.Open();
conn.ChangeDatabase(Properties.Settings.Default.SQLServerDatabase);
string sSQL = @"if object_id('[" + _SQLTable.Replace("'", "''") + @"]') is not null
select top 1 * from [" + _SQLTable.Replace("]", "]]") + "]";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sSQL, (System.Data.SqlClient.SqlConnection)conn);
cmd.CommandTimeout = 0;
System.Data.SqlClient.SqlDataReader datareader = cmd.ExecuteReader();
string sColumns = "";
for (int i = 0; i < datareader.FieldCount; i++)
{
if (!string.IsNullOrEmpty(sColumns))
{
sColumns += ", ";
}
sColumns += "[" + datareader.GetName(i) + "]";
}
datareader.Close();
if (sColumns != "")
{
sSQL = @"
if object_id('[" + _SQLTable.Replace("'", "''") + @"]') is not null
begin
if object_id('[" + _SQLTable.Replace("'", "''") + sHistorySuffix + @"]') is null
begin
select * into [" + _SQLTable.Replace("]", "]]") + sHistorySuffix + "] from [" + _SQLTable.Replace("]", "]]") + @"]
end
else
begin
SET IDENTITY_INSERT [" + _SQLTable.Replace("]", "]]") + sHistorySuffix + @"] ON
insert into [" + _SQLTable.Replace("]", "]]") + sHistorySuffix + "] (" + sColumns + @")
select * from [" + _SQLTable.Replace("]", "]]") + @"]
SET IDENTITY_INSERT [" + _SQLTable.Replace("]", "]]") + sHistorySuffix + @"] OFF
end
end
";
cmd.CommandText = sSQL;
int iRowsPreserved = cmd.ExecuteNonQuery();
messageText.Append(DateTime.Now.ToString() + ": Successfully preserved " + iRowsPreserved + " rows of history to table: " + _SQLTable + sHistorySuffix);
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Information);
}
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
messageText.Append(DateTime.Now.ToString() + ": Cannot preserve history of trace table. ").AppendLine();
messageText.Append("Error: " + ex.Message).AppendLine();
messageText.Append(ex.StackTrace).AppendLine();
while (ex.InnerException != null)
{
messageText.Append("INNER EXCEPTION: ");
messageText.Append(ex.InnerException.Message).AppendLine();
messageText.Append(ex.InnerException.StackTrace).AppendLine();
ex = ex.InnerException;
}
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Warning);
}
}
private async Task readFromDataReader(TNDMetaEntity metaEntity, List <TNDEntityItem> result, System.Data.SqlClient.SqlDataReader sqlDataReader)
{
// loop over NextResult
bool next = true;
while (next)
{
var fieldCount = sqlDataReader.FieldCount;
string[] names = new string[fieldCount];
Type[] types = new Type[fieldCount];
TNDPropertyCache[] caches = new TNDPropertyCache[fieldCount];
object[] values = new object[fieldCount];
int idxMetaEntityName = -1;
for (int idx = 0; idx < fieldCount; idx++)
{
names[idx] = sqlDataReader.GetName(idx);
types[idx] = sqlDataReader.GetFieldType(idx);
caches[idx] = new TNDPropertyCache();
if (string.Equals(names[idx], "MetaEntityName", StringComparison.Ordinal))
{
idxMetaEntityName = idx;
}
}
// loop over Read
bool read = true;
while (read)
{
var taskReadAsync = sqlDataReader.ReadAsync();
try {
read = await taskReadAsync;
} catch (Exception exception) {
throw;
}
if (read)
{
sqlDataReader.GetValues(values);
var item = new TNDEntityItem();
for (int idx = 0; idx < fieldCount; idx++)
{
if (idx == idxMetaEntityName)
{
item.MetaEntityName = values[idx] as string;
}
else
{
var property = new TNDProperty(names[idx], types[idx], values[idx]);
if (caches[idx] != null)
{
property = caches[idx].GetOrCreate(property);
}
item.Property.Add(property);
}
}
if (item.MetaEntityName != null)
{
item.MetaEntity = this._ApplicationBuis.ApplicationModel.Configuration.FindMetaEntity(item.MetaEntityName);
}
if (item.MetaEntity == null)
{
item.MetaEntity = metaEntity;
item.MetaEntityName = metaEntity.MetaEntityName;
}
result.Add(item);
}
}
var taskNextResult = sqlDataReader.NextResultAsync();
try {
next = await taskNextResult;
} catch (Exception exception) {
throw;
}
}
}
/// <summary>
/// 备份指定的数据表。
/// </summary>
/// <param name="Connection">已经打开的数据库连接。</param>
/// <param name="xtw">XML 书写器。</param>
/// <param name="TableName">数据表名称。</param>
private void BackupTable(System.Data.SqlClient.SqlConnection Connection, System.Xml.XmlTextWriter xtw, string TableName)
{
xtw.WriteStartElement(TableName);
System.Data.SqlClient.SqlDataReader sdr = null;
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
comm.Connection = Connection;
comm.CommandText = @"SELECT * FROM [" + TableName + "]";
sdr = comm.ExecuteReader();
try
{
for (int i = 0; i < sdr.FieldCount; i++)
{
xtw.WriteAttributeString(sdr.GetName(i), sdr.GetDataTypeName(i));
}
while (sdr.Read())
{
xtw.WriteStartElement("Row");
for (int i = 0; i < sdr.FieldCount; i++)
{
switch ((System.Data.SqlDbType)System.Enum.Parse(typeof(System.Data.SqlDbType), sdr.GetDataTypeName(i), true))
{
case System.Data.SqlDbType.BigInt:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.Bit:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.Date:
case System.Data.SqlDbType.DateTime:
case System.Data.SqlDbType.DateTime2:
case System.Data.SqlDbType.SmallDateTime:
case System.Data.SqlDbType.Time:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.DateTimeOffset:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.Decimal:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.Float:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.Int:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.Money:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.Char:
case System.Data.SqlDbType.Text:
case System.Data.SqlDbType.VarChar:
case System.Data.SqlDbType.NChar:
case System.Data.SqlDbType.NText:
case System.Data.SqlDbType.NVarChar:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.Real:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.SmallInt:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.SmallMoney:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.TinyInt:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.UniqueIdentifier:
xtw.WriteElementString(sdr.GetName(i), sdr[i].ToString());
break;
case System.Data.SqlDbType.Binary:
xtw.WriteElementString(sdr.GetName(i), System.Text.Encoding.UTF8.GetString(sdr.GetSqlBytes(i).Value));
break;
case System.Data.SqlDbType.VarBinary:
xtw.WriteElementString(sdr.GetName(i), System.Text.Encoding.UTF8.GetString(sdr.GetSqlBytes(i).Value));
break;
case System.Data.SqlDbType.Timestamp:
xtw.WriteElementString(sdr.GetName(i), System.Text.Encoding.UTF8.GetString(sdr.GetSqlBytes(i).Value));
break;
case System.Data.SqlDbType.Image:
xtw.WriteElementString(sdr.GetName(i), System.Text.Encoding.UTF8.GetString(sdr.GetSqlBytes(i).Value));
break;
case System.Data.SqlDbType.Structured: //此类型操作待处理
break;
case System.Data.SqlDbType.Udt: //此类型操作待处理
break;
case System.Data.SqlDbType.Variant: //此类型操作待处理
break;
case System.Data.SqlDbType.Xml: //此类型操作待处理
break;
}
}
xtw.WriteEndElement();
}
}
finally
{
sdr.Close();
}
xtw.WriteEndElement();
}
public static void OutHtml2ExcelDataReader(System.Data.SqlClient.SqlDataReader dr, string fileName)
{
StringBuilder sb = new StringBuilder();
sb.Append("<table border='1'>");
int sheetMaxRowIndex = 65535;
int currentRowIndex = 0;
List <string> drColumnNameList = new List <string>();
for (int i = 0; i < dr.FieldCount; i++)
{
drColumnNameList.Add(dr.GetName(i));
}
while (dr.Read())
{
if (currentRowIndex == 0)
{
//填充表头
sb.Append("<tr>");
for (int i = 0; i < drColumnNameList.Count; i++)
{
sb.Append("<td>");
sb.Append(drColumnNameList[i]);
sb.Append("</td>");
}
sb.Append("</tr>");
currentRowIndex++;
}
sb.Append("<tr>");
for (int i = 0; i < drColumnNameList.Count; i++)
{
sb.Append("<td>");
if (dr[i].ToString() != "")
{
sb.Append(dr[i].ToString());
}
else
{
sb.Append(" ");
}
sb.Append("</td>");
}
sb.Append("</tr>");
currentRowIndex++;
if (currentRowIndex > sheetMaxRowIndex)
{
break;
}
}
sb.Append("</table>");
dr.Close();
dr.Dispose();
//保存
Out2ClientByString(sb.ToString(), fileName);
}