private static string toJSON(SqlDataReader rdr)
{
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
JsonWriter jsonWriter = new JsonTextWriter(sw);
jsonWriter.WriteStartArray();
while (rdr.Read())
{
int fieldcount = rdr.FieldCount; // count how many columns are in the row
object[] values = new object[fieldcount]; // storage for column values
rdr.GetValues(values); // extract the values in each column
jsonWriter.WriteStartObject();
for (int index = 0; index < fieldcount; index++)
{
string colName = rdr.GetName(index);
object value = values[index];
jsonWriter.WritePropertyName(colName);
if (value == DBNull.Value)
value = "";
else if (colName.Contains("date") && !colName.Contains("by"))
value = String.Format("{0:MM/dd/yyyy HH:mm tt}", value);
jsonWriter.WriteValue(value);
}
jsonWriter.WriteEndObject();
}
jsonWriter.WriteEndArray();
return sb.ToString();
}
private static void imprimirBusqueda(SqlDataReader resultado, string mensaje)
{
Console.WriteLine(mensaje);
while (resultado.Read())
{
Object[] columnas = new Object[resultado.FieldCount];
resultado.GetValues(columnas);
foreach (var atributo in columnas)
{
Console.Write("{0} ", atributo);
}
Console.WriteLine();
}
Console.WriteLine();
}
/// <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);
}
internal void DeriveParameters()
{
if (commandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException(String.Format("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
}
ValidateCommand("DeriveParameters", false);
string procName = CommandText;
string schemaName = String.Empty;
int dotPosition = procName.IndexOf('.');
if (dotPosition >= 0)
{
schemaName = procName.Substring(0, dotPosition);
procName = procName.Substring(dotPosition + 1);
}
procName = EscapeProcName(procName, false);
schemaName = EscapeProcName(schemaName, true);
SqlParameterCollection localParameters = new SqlParameterCollection(this);
localParameters.Add("@procedure_name", SqlDbType.NVarChar, procName.Length).Value = procName;
if (schemaName.Length > 0)
{
localParameters.Add("@procedure_schema", SqlDbType.NVarChar, schemaName.Length).Value = schemaName;
}
string sql = "sp_procedure_params_rowset";
try {
Connection.Tds.ExecProc(sql, localParameters.MetaParameters, 0, true);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
SqlDataReader reader = new SqlDataReader(this);
parameters.Clear();
object[] dbValues = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(dbValues);
parameters.Add(new SqlParameter(dbValues));
}
reader.Close();
if (parameters.Count == 0)
{
throw new InvalidOperationException("Stored procedure '" + procName + "' does not exist.");
}
}
public List <Routines> GetListRoutines(string cadenaconexion)
{
List <Routines> RoutineList = new List <Routines>();
DALSQLNative bd = new DALSQLNative(cadenaconexion);
StringBuilder _SQL = new StringBuilder();
_SQL.Append(" select ROUTINE_TYPE, ");
_SQL.Append(" ROUTINE_SCHEMA+'.'+ROUTINE_NAME ROUTINE_NAME, ");
_SQL.Append(" ROUTINE_DEFINITION ");
_SQL.Append(" from information_schema.routines");
try
{
bd.conectar();
using (SqlCommand _comando = bd.crearComandoText(_SQL.ToString()))
{
using (System.Data.SqlClient.SqlDataReader reader = _comando.ExecuteReader())
{
if (reader.HasRows)
{
Object[] RegistOfSelect = new Object[reader.FieldCount];
int IdxROUTINE_TYPE = reader.GetOrdinal("ROUTINE_TYPE");
int IdxROUTINE_NAME = reader.GetOrdinal("ROUTINE_NAME");
int IdxROUTINE_DEFINITION = reader.GetOrdinal("ROUTINE_DEFINITION");
while (reader.Read())
{
Routines _Routines = new Routines();
reader.GetValues(RegistOfSelect);
if (!(RegistOfSelect[IdxROUTINE_TYPE] is DBNull))
{
_Routines.Type = RegistOfSelect[IdxROUTINE_TYPE].ToString();
}
if (!(RegistOfSelect[IdxROUTINE_NAME] is DBNull))
{
_Routines.Name = RegistOfSelect[IdxROUTINE_NAME].ToString();
}
if (!(RegistOfSelect[IdxROUTINE_DEFINITION] is DBNull))
{
_Routines.Definition = RegistOfSelect[IdxROUTINE_DEFINITION].ToString();
}
RoutineList.Add(_Routines);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
bd.Desconectar();
}
return(RoutineList);
}
/// <summary>
/// 輸入的SqlDataReader,用ArrayList 方式取回全部資料
/// </summary>
/// <param name="SDR">SqlDataReader Source</param>
/// <returns></returns>
public static ArrayList GetRows(SqlDataReader SDR)
{
ArrayList myResults = new ArrayList();
object[] objOneRow;
while (SDR.Read())
{
objOneRow = (object[])Array.CreateInstance(typeof(object), SDR.FieldCount);
SDR.GetValues(objOneRow);
myResults.Add(objOneRow);
}
return myResults;
}
public List <DefitionItem> GetDefinitionObj(string cadenaconexion, Routines routine)
{
List <DefitionItem> ListDefinition = new List <DefitionItem>();
DALSQLNative bd = new DALSQLNative(cadenaconexion);
StringBuilder _SQL = new StringBuilder();
_SQL.Append(" EXEC sp_helptext '" + routine.Name + "'; ");
try
{
bd.conectar();
using (SqlCommand _comando = bd.crearComandoText(_SQL.ToString()))
{
using (System.Data.SqlClient.SqlDataReader reader = _comando.ExecuteReader())
{
if (reader.HasRows)
{
Object[] RegistOfSelect = new Object[reader.FieldCount];
int IdxText = reader.GetOrdinal("Text");
while (reader.Read())
{
DefitionItem _Definition = new DefitionItem();
reader.GetValues(RegistOfSelect);
if (!(RegistOfSelect[IdxText] is DBNull))
{
//_Definition.Text = RegistOfSelect[IdxText].ToString().ToUpper().Replace("CREATE PROC", "ALTER PROC");
_Definition.Text = RegistOfSelect[IdxText].ToString();
}
ListDefinition.Add(_Definition);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
bd.Desconectar();
}
return(ListDefinition);
}
public void get_rows(dbtable t, row_cb cb)
{
string cmd = "select * from " + t.name;
System.Data.SqlClient.SqlCommand c = new System.Data.SqlClient.SqlCommand(cmd, con);
c.Transaction = trans;
System.Data.SqlClient.SqlDataReader r = c.ExecuteReader();
while (r.Read())
{
object[] o = new object[r.FieldCount];
r.GetValues(o);
dbrow row;
row.fields = o;
row.table_id = t.id;
cb(row);
}
r.Close();
}
private static object[] GetRowValues(SqlDataReader sqlReader)
{
object[] rowValues = new object[sqlReader.FieldCount];
sqlReader.GetValues(rowValues);
return rowValues;
}
private static void processData(SqlDataReader reader, FlexResult result)
{
try
{
int fieldCount = reader.FieldCount;
var data = new List<Object[]>();
while (reader.Read())
{
Object[] values = new Object[fieldCount];
reader.GetValues(values);
data.Add(values);
}
result.data = data;
}
catch (Exception ex)
{
result.exceptions.Add(ex);
}
}
List<BD2.Conv.Frontend.Table.Row> readRowsData(SqlConnection connection, SqlCommand command, SqlDataReader reader, Table table)
{
Console.WriteLine ("readRowsData()");
List<BD2.Conv.Frontend.Table.Row> rows = new List<BD2.Conv.Frontend.Table.Row> ();
object[] values;
//SortedDictionary<string, Column> table = tableColumns [context.Table.SqlTableID];
string[] rowTFQNs = new string[reader.FieldCount];
BD2.Conv.Frontend.Table.Column[] cols = new BD2.Conv.Frontend.Table.Column[reader.FieldCount];
for (int n = 0; n != reader.FieldCount; n++) {
cols [n] = psc.GetColumnByName (table, reader.GetName (n));
rowTFQNs [n] = cols [n].TFQN;
}
int rc = 0;
ColumnSet columnSet = new ColumnSet (cols);
while (reader.Read ()) {
rc++;
values = new object[reader.FieldCount];
reader.GetValues (values);
BD2.Conv.Frontend.Table.Row r = new BD2.Conv.Frontend.Table.Row (columnSet, values);
rows.Add (r);
}
reader.Close ();
connection.Close ();
return rows;
}
/// <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;
}
}
/// <summary>
/// Maps from data reader.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader">The reader.</param>
/// <param name="closeReader">if set to <c>true</c> [close reader].</param>
/// <returns></returns>
protected List <T> MapFromDataReader <T>(ref System.Data.SqlClient.SqlDataReader reader, bool closeReader)
{
method = MethodBase.GetCurrentMethod();
try
{
Type newType = typeof(T);
if (_currentType != newType)
{
_currentType = newType;
FindColumnNames(_currentType);
}
List <T> returnList = new List <T>();
object[] values = new object[reader.FieldCount];
Dictionary <string, int> columnsIds = new Dictionary <string, int>(reader.FieldCount);
List <string> schemaFieldsName = null;
while (reader.Read())
{
reader.GetValues(values);
T objTarget = Activator.CreateInstance <T>();
if (schemaFieldsName == null)
{
using (DataTable dtSchema = reader.GetSchemaTable())
{
schemaFieldsName = dtSchema.AsEnumerable().Select(x => x.Field <string>("ColumnName").ToUpper()).ToList();
}
}
foreach (MemberInfo member in _PropertiesToColumnsNames.Keys)
{
string columnName = _PropertiesToColumnsNames[member];
if (schemaFieldsName.Contains(columnName.ToUpper()))
{
int columnId = 0;
if (columnsIds.ContainsKey(columnName))
{
columnId = columnsIds[columnName];
}
else
{
columnId = reader.GetOrdinal(columnName);
columnsIds.Add(columnName, columnId);
}
PropertyInfo pMember = (PropertyInfo)member;
object value;
try
{ value = ConvertValueToType(values[columnId], pMember.PropertyType); }
catch { value = values[columnId]; }
pMember.SetValue(objTarget, value, null);
}
}
returnList.Add(objTarget);
}
if (closeReader)
{
reader.Dispose();
}
return(returnList.Count > 0 ? returnList : null);
}
catch (Exception ex)
{
log.escribirError(ex.Message, stackTrace.GetFrame(1).GetMethod().Name, method.Name, method.ReflectedType.Name);
throw new Exception(ex.InnerException.ToString());
}
finally
{
if (!reader.IsClosed)
{
reader.Close();
}
reader.Dispose();
}
}
private static void SendTable(SqlDataReader reader, TRowSetMap Map)
{
//SqlDataRecord ReadRecord = new SqlDataRecord(DataReaderFields(reader));
DataTable LDataTable = reader.GetSchemaTable();
SqlDataRecord WriteRecord;
List<TFieldAlias> Fields = new List<TFieldAlias>();
TFieldAlias Field;
string FieldName;
int FieldCount = reader.FieldCount, WriteFieldCount = 0;
int i;
SqlMetaData[] WriteFields;
if(Map.Fields.Length > 0)
{
WriteFields = new SqlMetaData[0];
foreach (string FieldMap in Map.Fields.Split(new char[] {','}))
{
i = FieldMap.IndexOf('=');
if(i >= 0)
{
Field.Name = FieldMap.Substring(0, i);
FieldName = FieldMap.Substring(i + 1);
}
else
{
Field.Name = FieldMap;
FieldName = FieldMap;
}
for(i = 0; i < FieldCount; i++)
{
if(FieldName.ToUpper() == reader.GetName(i).ToUpper())
break;
}
if((i < 0) || (i >= FieldCount))
throw new SystemException("RowSet Field = [" + FieldName + "] not found.");
Field.FieldIndex = i;
Fields.Add(Field);
Array.Resize(ref WriteFields, ++WriteFieldCount);
//WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[WriteFieldCount - 1], Field.Name);
WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[Field.FieldIndex], Field.Name);
}
}
else
{
WriteFields = new SqlMetaData[FieldCount];
for (; WriteFieldCount < reader.FieldCount; WriteFieldCount++)
WriteFields[WriteFieldCount] = SqlMetaData(LDataTable.Rows[WriteFieldCount]);
}
WriteRecord = new SqlDataRecord(WriteFields);
try
{
SqlContext.Pipe.SendResultsStart(WriteRecord);
Object[] values = new Object[FieldCount];
while (reader.Read())
{
reader.GetValues(values);
if(Map.Fields.Length > 0)
{
for(i = 0; i < WriteFieldCount; i++)
WriteRecord.SetValue(i, values[Fields[i].FieldIndex]);
}
else
{
WriteRecord.SetValues(values);
}
SqlContext.Pipe.SendResultsRow(WriteRecord);
}
}
finally
{
SqlContext.Pipe.SendResultsEnd();
}
}
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);
}
public ApiResponse TableDefs()
{
DataSet dsSchemaExport = new DataSet();
Dictionary <string, object> defs = new Dictionary <string, object>();
List <string> cols = new List <string>();
System.Data.SqlClient.SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AgmDataContext"].ConnectionString);
try
{
conn.Open();
System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand("select * from utenti where idutente=21", conn);
System.Data.SqlClient.SqlDataReader sqlreader = command.ExecuteReader();
var schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "utenti"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("utenti", cols);
sqlreader.Read();
object[] resUtenti = new object[cols.Count];
sqlreader.GetValues(resUtenti);
defs.Add("utenti example", JsonConvert.SerializeObject(resUtenti));
sqlreader.Close();
command = new System.Data.SqlClient.SqlCommand("UPDATE utenti set utente=email where utente is NULL;", conn);
var resupd = command.ExecuteNonQuery();
defs.Add("update result1", resupd);
//command = new System.Data.SqlClient.SqlCommand("insert into rappcausali (idcausale,nome) values (10,'Permessi ex-festività')", conn);
//var resAlter = command.ExecuteNonQuery();
//defs.Add("update result1", resAlter);
//command = new System.Data.SqlClient.SqlCommand("ALTER TABLE utenti ADD isDeleted bit DEFAULT 0 NOT NULL", conn);
//var resAlter = command.ExecuteNonQuery();
//defs.Add("update result1", resAlter);
//command = new System.Data.SqlClient.SqlCommand("update utenti set utenti=1 where idutente=21", conn);
//var resAlter = command.ExecuteNonQuery();
//defs.Add("update result1", resAlter);
//command = new System.Data.SqlClient.SqlCommand("update utenti set email='*****@*****.**' where idutente=21", conn);
//var resAlter = command.ExecuteNonQuery();
//defs.Add("update result1", resAlter);
//command = new System.Data.SqlClient.SqlCommand("update utenti set email='*****@*****.**' where idutente=4", conn);
//resAlter = command.ExecuteNonQuery();
//defs.Add("update result2", resAlter);
//command = new System.Data.SqlClient.SqlCommand("update utenti set email='*****@*****.**' where idutente=3", conn);
//resAlter = command.ExecuteNonQuery();
//defs.Add("update result3", resAlter);
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappore where idutente=38", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappore"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappore", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select * from rappcausali", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappcausali"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappcausali", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select * from rappcausalispese", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappcausalispese"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappcausalispese", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappspese where idutente=38", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappspese"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappspese", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappdescrizioni where idutente=38", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappdescrizioni"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappdescrizioni", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappfestivi", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappfestivi"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappfestivi", cols);
sqlreader.Read();
defs.Add("rappfestivi example", sqlreader[1].ToString());
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from annunci", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "annunci"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} [{1}({2})] - {3}", col["ColumnName"], col["DataTypeName"], col["ColumnSize"], col["IsIdentity"]));
}
defs.Add("annunci", cols);
sqlreader.Read();
defs.Add("annunci example", sqlreader[4].ToString());
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappvociretributive", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappvociretributive"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} [{1}({2})] - {3}", col["ColumnName"], col["DataTypeName"], col["ColumnSize"], col["IsIdentity"]));
}
defs.Add("rappvociretributive", cols);
sqlreader.Close();
dsSchemaExport.WriteXml(HttpContext.Current.Server.MapPath("~/App_Data/tables.xml"), XmlWriteMode.WriteSchema);
dsSchemaExport.WriteXmlSchema(HttpContext.Current.Server.MapPath("~/App_Data/tables_schema.xsd"));
conn.Close();
}
catch (Exception e)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
return(new ApiResponse(false)
{
Errors = new ApiResponseError[] { new ApiResponseError()
{
Message = e.Message
} }
});
}
return(new ApiResponse(true)
{
Data = defs
});
}
public void GetValuesTest ()
{
cmd.CommandText = "Select 10,20,30 from numeric_family where id=1";
reader = cmd.ExecuteReader ();
reader.Read ();
object[] arr = null;
int count = 0;
arr = new object[1];
count = reader.GetValues (arr);
Assert.AreEqual (10, (int)arr[0], "#1 Only first object shud be copied");
Assert.AreEqual (1, count, "#1 return value shud equal objects copied");
arr = new object[3];
count = reader.GetValues (arr);
Assert.AreEqual (3, count, "#2 return value shud equal objects copied");
arr = new object [5];
count = reader.GetValues (arr);
Assert.AreEqual (3, count, "#3 return value shud equal objects copied");
Assert.IsNull (arr[3], "#4 Only 3 objects shud be copied");
reader.Close ();
}
/// <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;
}
}
internal void DeriveParameters ()
{
if (commandType != CommandType.StoredProcedure)
throw new InvalidOperationException (String.Format ("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
ValidateCommand ("DeriveParameters");
SqlParameterCollection localParameters = new SqlParameterCollection (this);
localParameters.Add ("@procedure_name", SqlDbType.NVarChar, commandText.Length).Value = commandText;
string sql = "sp_procedure_params_rowset";
Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
SqlDataReader reader = new SqlDataReader (this);
parameters.Clear ();
object[] dbValues = new object[reader.FieldCount];
while (reader.Read ()) {
reader.GetValues (dbValues);
parameters.Add (new SqlParameter (dbValues));
}
reader.Close ();
}
public List<List<object>> search(string query)
{
List<List<object>> array = new List<List<object>>();
try
{
conn.Open();
}
catch (Exception ex)
{
error = "connection fail:" + ex.Message;
return null;
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = query;
try
{
sdr = cmd.ExecuteReader();
while (sdr.Read())
{
List<object> tmparr = new List<object>();
Object[] values = new Object[sdr.FieldCount];
int fieldCount = sdr.GetValues(values);
for (int i = 0; i < fieldCount; i++)
tmparr.Add(values[i]);
array.Add(tmparr);
}
}
catch (Exception ex)
{
error = "search fail:" + ex.Message;
}
conn.Close();
return array;
}
/// <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;
}
}
internal void DeriveParameters ()
{
if (commandType != CommandType.StoredProcedure)
throw new InvalidOperationException (String.Format ("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
ValidateCommand ("DeriveParameters", false);
string procName = CommandText;
string schemaName = String.Empty;
int dotPosition = procName.LastIndexOf ('.');
// Procedure name can be: [database].[user].[procname]
if (dotPosition >= 0) {
schemaName = procName.Substring (0, dotPosition);
procName = procName.Substring (dotPosition + 1);
if ((dotPosition = schemaName.LastIndexOf ('.')) >= 0)
schemaName = schemaName.Substring (dotPosition + 1);
}
procName = EscapeProcName (procName, false);
schemaName = EscapeProcName (schemaName, true);
SqlParameterCollection localParameters = new SqlParameterCollection (this);
localParameters.Add ("@procedure_name", SqlDbType.NVarChar, procName.Length).Value = procName;
if (schemaName.Length > 0)
localParameters.Add ("@procedure_schema", SqlDbType.NVarChar, schemaName.Length).Value = schemaName;
string sql = "sp_procedure_params_rowset";
try {
Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset ();
throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
} catch (TdsInternalException ex) {
Connection.Close ();
throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
}
SqlDataReader reader = new SqlDataReader (this);
parameters.Clear ();
object[] dbValues = new object[reader.FieldCount];
while (reader.Read ()) {
reader.GetValues (dbValues);
parameters.Add (new SqlParameter (dbValues));
}
reader.Close ();
if (parameters.Count == 0)
throw new InvalidOperationException ("Stored procedure '" + procName + "' does not exist.");
}
/// <summary>
/// 若使用【读写分离】，查询【从库】条件cmdText.StartsWith("SELECT ")，否则查询【主库】
/// </summary>
/// <param name="readerHander"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
public void ExecuteReader(Action <SqlDataReader> readerHander, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
DateTime dt = DateTime.Now;
string logtxt = "";
DateTime logtxt_dt = DateTime.Now;
var pool = this.MasterPool;
bool isSlave = false;
//读写分离规则
if (this.SlavePools.Any() && cmdText.StartsWith("SELECT ", StringComparison.CurrentCultureIgnoreCase))
{
var availables = slaveUnavailables == 0 ?
//查从库
this.SlavePools : (
//查主库
slaveUnavailables == this.SlavePools.Count ? new List <SqlConnectionPool>() :
//查从库可用
this.SlavePools.Where(sp => sp.IsAvailable).ToList());
if (availables.Any())
{
isSlave = true;
pool = availables.Count == 1 ? availables[0] : availables[slaveRandom.Next(availables.Count)];
}
}
Object <SqlConnection> conn = null;
var pc = PrepareCommand(cmdType, cmdText, cmdParms, ref logtxt);
if (IsTracePerformance)
{
logtxt += $"PrepareCommand: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
}
Exception ex = null;
try {
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
if (isSlave)
{
//从库查询切换，恢复
bool isSlaveFail = false;
try {
if (pc.cmd.Connection == null)
{
pc.cmd.Connection = (conn = pool.Get()).Value;
}
//if (slaveRandom.Next(100) % 2 == 0) throw new Exception("测试从库抛出异常");
} catch {
isSlaveFail = true;
}
if (isSlaveFail)
{
if (conn != null)
{
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
pool.Return(conn, ex);
if (IsTracePerformance)
{
logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms";
}
}
LoggerException(pool, pc.cmd, new Exception($"连接失败，准备切换其他可用服务器"), dt, logtxt, false);
pc.cmd.Parameters.Clear();
ExecuteReader(readerHander, cmdType, cmdText, cmdParms);
return;
}
}
else
{
//主库查询
if (pc.cmd.Connection == null)
{
pc.cmd.Connection = (conn = pool.Get()).Value;
}
}
if (IsTracePerformance)
{
logtxt += $"Open: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
logtxt_dt = DateTime.Now;
}
using (SqlDataReader dr = pc.cmd.ExecuteReader()) {
if (IsTracePerformance)
{
logtxt += $"ExecuteReader: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
}
while (true)
{
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
bool isread = dr.Read();
if (IsTracePerformance)
{
logtxt += $" dr.Read: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
}
if (isread == false)
{
break;
}
if (readerHander != null)
{
object[] values = null;
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
values = new object[dr.FieldCount];
dr.GetValues(values);
logtxt += $" dr.GetValues: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
logtxt_dt = DateTime.Now;
}
readerHander(dr);
if (IsTracePerformance)
{
logtxt += $" readerHander: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms ({string.Join(",", values)})\r\n";
}
}
}
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
dr.Close();
}
if (IsTracePerformance)
{
logtxt += $"ExecuteReader_dispose: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
}
} catch (Exception ex2) {
ex = ex2;
}
if (conn != null)
{
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
pool.Return(conn, ex);
if (IsTracePerformance)
{
logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms";
}
}
LoggerException(pool, pc.cmd, ex, dt, logtxt);
pc.cmd.Parameters.Clear();
}
