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();
}
public static PMSAgency Read(SqlDataReader reader)
{
PMSAgency retval = new PMSAgency();
for (int i = 0; i < reader.FieldCount; i++)
{
switch (reader.GetName(i))
{
case "GroupSegment":
retval.Segment = Helper.ToString(reader[i]);
break;
case "AType":
retval.ActionType = Helper.ToString(reader[i]);
break;
case "Agency":
retval.AgencyName = Helper.ToString(reader[i]);
break;
case "Market":
retval.Market = Helper.ToString(reader[i]);
break;
case "RepGroup":
retval.RepGroup = Helper.ToString(reader[i]);
break;
case "SiteID":
retval.SiteID = Helper.ToInt32(reader[i]);
break;
}
}
return retval;
}
private static string[] JSOMembers(SqlDataReader sdr, bool camelCase)
{
string[] names = new string[sdr.FieldCount];
for (int i = 0; i < sdr.FieldCount; i++)
{
names[i] = (camelCase ? sdr.GetName(i).ToCamelCase() : sdr.GetName(i));
}
return names;
}
/// <summary>
/// Verifica se o campo está explícito na instrução Sql
/// </summary>
/// <param name="oDr">DataReader</param>
/// <param name="Campo">Nome do Campo</param>
/// <returns></returns>
public bool Coluna(SqlDataReader oDr, string Campo)
{
for (int i = 0; i < oDr.FieldCount; i++)
{
string nome = oDr.GetName(i);
if (oDr.GetName(i) == Campo)
return true;
}
return false;
}
public static Tuple<int, string> GetPK(SqlDataReader model)
{
for (int i = 0; i < model.FieldCount; i++)
{
if (model.GetName(i).EndsWith("id", StringComparison.InvariantCultureIgnoreCase))
{
return new Tuple<int, string>(i, model.GetName(i));
}
}
return null;
}
public Model(SqlDataReader reader)
: this()
{
for (int i = 0; i < reader.FieldCount; i++)
{
PropertyInfo property = this.GetType().GetProperty(reader.GetName(i));
if (property != null)
{
property.SetValue(this, reader[reader.GetName(i)] == System.DBNull.Value ? null : reader[reader.GetName(i)]);
}
}
}
public static SIHOT_Package Read(SqlDataReader reader)
{
SIHOT_Package retval = new SIHOT_Package();
for (int i = 0; i < reader.FieldCount; i++)
{
switch (reader.GetName(i))
{
case "PCIID":
retval.PCIID = Helper.ToInt32(reader[i]);
break;
case "FROM_PACKAGE":
retval.SIHOT_PACKAGE_FROM = Helper.ToDateTime(reader[i]);
break;
case "TO_PACKAGE":
retval.SIHOT_PACKAGE_TO = Helper.ToDateTime(reader[i]);
break;
case "SID":
retval.SIHOT_PACKAGE_SID = Helper.ToString(reader[i]);
break;
case "RS":
retval.SIHOT_PACKAGE_RS = Helper.ToString(reader[i]);
break;
case "SIDT":
retval.SIHOT_PACKAGE_SIDT = Helper.ToString(reader[i]);
break;
}
}
return retval;
}
private StringBuilder LireSQL(SqlDataReader dataR1)
{
StringBuilder BLD = new StringBuilder();
BLD.Append("<table style='background-color: #0066ff; color: white; width: 100%' class='tabProduits'><tr>");
for (int i = 0; i < dataR1.FieldCount; i++)
{
BLD.Append("<th style='background-color: black'>" + dataR1.GetName(i) + "</th>");
}
BLD.Append("<tr>");
while (dataR1.Read())
{
BLD.Append("<tr>");
for (int i = 0; i < dataR1.FieldCount; i++)
{
BLD.Append("<th>" + dataR1[i] + "</th>");
}
BLD.Append("</tr>");
}
BLD.Append("</table>");
return BLD;
}
static Document CreateDocument(SqlDataReader reader, IDictionary<int, List<string>> packageFrameworks)
{
var package = new Dictionary<string, string>();
for (var i = 0; i < reader.FieldCount; i++)
{
if (!reader.IsDBNull(i))
{
string name = reader.GetName(i);
object obj = reader.GetValue(i);
if (name == "key")
{
var key = (int)obj;
List<string> targetFrameworks;
if (packageFrameworks.TryGetValue(key, out targetFrameworks))
{
package.Add("supportedFrameworks", string.Join("|", targetFrameworks));
}
}
var value = (obj is DateTime) ? ((DateTime)obj).ToUniversalTime().ToString("O") : obj.ToString();
package.Add(name, value);
}
}
return DocumentCreator.CreateDocument(package);
}
public static JArray SqlDataReader2Json(SqlDataReader reader, string col0, string col1)
{
var colNames = new Dictionary<string, int>();
for (int i = 0; i < reader.FieldCount; i++)
{
colNames[reader.GetName(i)] = i;
}
var parent = new Dictionary<string, HashSet<string>>(StringComparer.OrdinalIgnoreCase);
while (reader.Read())
{
string parentColumn = reader.GetString(colNames[col0]);
string childColumn = reader.GetString(colNames[col1]);
HashSet<string> child;
if (!parent.TryGetValue(parentColumn, out child))
{
child = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
parent.Add(parentColumn, child);
}
child.Add(childColumn);
}
return MakeJArray(parent);
}
public WebSqlCommandResult(SqlDataReader reader)
{
this.rows = new List<List<string>>(reader.RecordsAffected + 1);
this.rowsAffected = reader.RecordsAffected;
int fieldCount = reader.FieldCount;
this.columns = new List<string>(fieldCount);
int internalRowCount = 0;
for (int i = 0; i < fieldCount; i++)
{
this.columns.Add(reader.GetName(i));
}
while (reader.Read())
{
var columns = new List<string>(fieldCount);
for (int i = 0; i < fieldCount; i++)
{
columns.Add(reader[i].ToString());
}
rows.Add(columns);
internalRowCount++;
}
if (this.rowsAffected == -1 && internalRowCount > 0)
{
this.rowsAffected = internalRowCount;
}
this.errorCode = 0;
this.errorLineNumber = 0;
this.errorMessage = "";
}
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);
}
/// <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);
}
public static bool ColumnExists(SqlDataReader dataReader, string columnName)
{
for (Int32 iCol = 0; iCol < dataReader.FieldCount; iCol++)
if (columnName.ToUpper().Equals(dataReader.GetName(iCol).ToUpper()))
return true;
return false;
}
/// <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���������
}
private static void DisplayQueryResultHeader(SqlDataReader queryResult)
{
for (int i = 0; i < queryResult.FieldCount; i++)
{
Console.Write("{0}\t", queryResult.GetName(i));
}
Console.WriteLine();
}
// Метод формирования 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;
}
public static void AddColumnHeadersFromSqlDataReader(string path, string sheetName, SqlDataReader reader)
{
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
AddTextToWorkSheet(path, sheetName, fieldName, GetColumnName(i + 1), 1);
}
}
private void createColumns(SqlDataReader queryReader, DataGridView dgv, int fieldCount)
{
String column = "";
for (int i = 0; i < fieldCount; i++)
{
column = queryReader.GetName(i);
dgv.Columns.Add(column, column);
}
}
List<FieldDefintion> getFields(System.Data.SqlClient.SqlDataReader reader) {
var ret = new List<FieldDefintion>();
var fieldCount = reader.FieldCount;
var fieldIndex = 0;
while (fieldIndex < fieldCount) {
ret.Add(new FieldDefintion(reader.GetName(fieldIndex), reader.GetDataTypeName(fieldIndex)));
fieldIndex++;
}
return ret;
}
private void OutputResults( SqlDataReader reader )
{
while (reader.Read())
{
Console.WriteLine("********Record*********");
for (int i = 0; i < reader.FieldCount; i++ )
Console.WriteLine("{0} = {1}", reader.GetName(i), reader.GetValue(i).ToString().Trim());
Console.WriteLine();
}
}
private static void WriteHeader(SqlDataReader reader, TextWriter output)
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (i > 0)
output.Write(',');
output.Write(reader.GetName(i));
}
output.WriteLine();
}
/// <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 void Form1_Load(object sender, EventArgs e)
{
try
{
con = new SqlConnection(connectionstring);
cmd = new SqlCommand("select studentName,fatherName,PhoneNumber from School", con);
con.Open();
dr = cmd.ExecuteReader();
lbl1.Text = dr.GetName(0).ToString();
lbl2.Text = dr.GetName(1).ToString();
lbl3.Text = dr.GetName(2).ToString();
ShowData();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static List<string> GetColNames(SqlDataReader reader)
{
List<string> ret = new List<string>();
if (reader == null || !reader.HasRows)
return ret;
for (int i = 0; i < reader.FieldCount; i++)
{
ret.Add(reader.GetName(i));
}
return ret;
}
static public Dictionary <string, object> ToDataRow(this System.Data.SqlClient.SqlDataReader me)
{
string _message = string.Format("ToDictionary Call Failed");
try {
return(Enumerable.Range(0, me.FieldCount).ToDictionary(field => me.GetName(field).ToString(), field => me.GetValue(field)));
}
catch (Exception ex) {
// err.WriteError(_message, ProcessHelper.GetProcessName(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType), ex);
}
return(new Dictionary <string, object>());
}
public string dataReadertoJson(SqlDataReader dr)
{
string json = "{";
for (int i = 0; i < dr.FieldCount; i++)
{
if (i > 0)
{
json += ",";
}
if (dr[i].ToString().Length > 6 && dr[i].ToString().Substring(0, 6) == "{\\rtf1")
{
json += "\"" + dr.GetName(i) + "\":\"" + RTFtoTXT(dr[i].ToString().Replace("\"", "'")) + "\"";
}
else
{
json += "\"" + dr.GetName(i) + "\":\"" + dr[i].ToString().Replace("\"","'") + "\"";
}
}
json += "}";
return json;
}
public static SIHOT_Suites Read(SqlDataReader reader)
{
SIHOT_Suites retval = new SIHOT_Suites();
for (int i = 0; i < reader.FieldCount; i++)
{
switch (reader.GetName(i))
{
case "PCIID":
retval.PCIID = Helper.ToInt32(reader[i]);
break;
case "RN":
retval.SuiteRN = Helper.ToString(reader[i]);
break;
case "RN1":
retval.RN1 = Helper.ToString(reader[i]);
break;
case "RN2":
retval.RN2 = Helper.ToString(reader[i]);
break;
case "RN3":
retval.RN3 = Helper.ToString(reader[i]);
break;
case "RN4":
retval.RN4 = Helper.ToString(reader[i]);
break;
case "RN5":
retval.RN5 = Helper.ToString(reader[i]);
break;
case "PH0":
retval.PH0 = Helper.ToString(reader[i]);
break;
case "PH1":
retval.PH1 = Helper.ToString(reader[i]);
break;
case "PH2":
retval.PH2 = Helper.ToString(reader[i]);
break;
case "PH3":
retval.PH3 = Helper.ToString(reader[i]);
break;
case "PH4":
retval.PH4 = Helper.ToString(reader[i]);
break;
case "PH5":
retval.PH5 = Helper.ToString(reader[i]);
break;
}
}
return retval;
}
public static SIHOT_Reservation Read(SqlDataReader reader)
{
SIHOT_Reservation retval = new SIHOT_Reservation();
for (int i = 0; i < reader.FieldCount; i++)
{
switch (reader.GetName(i))
{
case "TN":
retval.TN = Helper.ToInt32(reader[i]);
break;
case "OC":
retval.OC = Helper.ToString(reader[i]);
break;
case "HN":
retval.HN = Helper.ToInt32(reader[i]);
break;
case "USERID":
retval.UserId = Helper.ToString(reader[i]);
break;
case "WS":
retval.WS = Helper.ToString(reader[i]);
break;
case "ObjectId":
retval.OBJID = Helper.ToString(reader[i]);
break;
case "RNO":
retval.RNO = Helper.ToInt32(reader[i]);
break;
case "RSNO":
retval.RSNO = Helper.ToInt32(reader[i]);
break;
case "ARR_Date":
retval.ARR = Helper.ToDateTime(reader[i]);
break;
case "DEP_Date":
retval.DEP = Helper.ToDateTime(reader[i]);
break;
case "NOPAX":
retval.NOPAX = Helper.ToInt32(reader[i]);
break;
case "RT":
retval.RT = Helper.ToString(reader[i]);
break;
case "ORG":
retval.ORG = Helper.ToString(reader[i]);
break;
}
}
return retval;
}
public static TSWGift Read(SqlDataReader reader)
{
TSWGift retval = new TSWGift();
for (int i = 0; i < reader.FieldCount; i++)
{
switch (reader.GetName(i))
{
case "PremInvTransId":
retval.PremID = Helper.ToInt32(reader[i]);
break;
case "PersonnelID":
retval.PersonnelID = Helper.ToString(reader[i]);
break;
case "GiftCharge":
retval.GiftCharge = Helper.ToDecimal(reader[i]);
break;
case "TourID":
retval.TourID = Helper.ToInt32(reader[i]);
break;
case "VoidVoucher":
retval.VoidVoucher = Helper.ToString(reader[i]);
break;
case "Budget":
retval.Budget = Helper.ToInt32(reader[i]);
break;
case "PremName":
retval.PremName = Helper.ToString(reader[i]);
break;
case "PremCount":
retval.Qty = Helper.ToInt32(reader[i]);
break;
case "Siteid":
retval.SiteId = Helper.ToInt32(reader[i]);
break;
case "LastName":
retval.LastName = Helper.ToString(reader[i]);
break;
case "FirstName":
retval.FirstName = Helper.ToString(reader[i]);
break;
case "Personnel":
retval.Personnel = Helper.ToString(reader[i]);
break;
case "TourStatus":
retval.TourStatus = Helper.ToString(reader[i]);
break;
}
}
return retval;
}
/// <summary>
/// return data from reader
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private static SiteMapData GetSiteMapDataFromReader(SqlDataReader reader)
{
var prefUrl = SettingsMain.SiteUrl + "/";
var siteMapData = new SiteMapData
{
Changefreq = DefaultChangeFreq,
Priority = DefaultPriority
};
if (reader.GetName(0).ToLower() == "categoryid")
{
siteMapData.Loc = prefUrl + UrlService.GetLink(ParamType.Category, SQLDataHelper.GetString(reader, "UrlPath"), SQLDataHelper.GetInt(reader, "CategoryId"));
siteMapData.Lastmod = DateTime.Now;
}
else if (reader.GetName(0).ToLower() == "productid")
{
siteMapData.Loc = prefUrl + UrlService.GetLink(ParamType.Product, SQLDataHelper.GetString(reader, "UrlPath"), SQLDataHelper.GetInt(reader, "Productid"));
siteMapData.Lastmod = SQLDataHelper.GetDateTime(reader, "DateModified");
}
else if (reader.GetName(0).ToLower() == "newsid")
{
siteMapData.Loc = prefUrl + UrlService.GetLink(ParamType.News, SQLDataHelper.GetString(reader, "UrlPath"), SQLDataHelper.GetInt(reader, "NewsID"));
siteMapData.Lastmod = SQLDataHelper.GetDateTime(reader, "AddingDate");
}
else if (reader.GetName(0).ToLower() == "staticpageid")
{
siteMapData.Loc = prefUrl + UrlService.GetLink(ParamType.StaticPage, SQLDataHelper.GetString(reader, "UrlPath"), SQLDataHelper.GetInt(reader, "StaticPageID"));
siteMapData.Lastmod = SQLDataHelper.GetDateTime(reader, "ModifyDate");
}
else if (reader.GetName(0).ToLower() == "brandid")
{
siteMapData.Loc = prefUrl + UrlService.GetLink(ParamType.Brand, SQLDataHelper.GetString(reader, "UrlPath"), SQLDataHelper.GetInt(reader, "BrandID"));
siteMapData.Lastmod = DateTime.Now;
}
return siteMapData;
}
public static bool LoadClassFromSQLDataReader(object myClass, SqlDataReader dr)
{
if (dr.HasRows)
{
dr.Read();
Type typeOfClass = myClass.GetType();
object[] dataMappingAttributes = typeOfClass.GetCustomAttributes(typeof(DataMappingAttribute), false);
for (int columnIndex = 0; columnIndex <= dr.FieldCount - 1; columnIndex++)
{
//Get the name of the column.
string columnName = dr.GetName(columnIndex);
//Check if a property exists that matches that name.
PropertyInfo propertyInfo = null;
//Check if an attribute exists that maps this column to a property.
foreach (DataMappingAttribute dataMappingAttribute in dataMappingAttributes)
{
if (dataMappingAttribute.ColumnName == columnName)
{
propertyInfo = typeOfClass.GetProperty(dataMappingAttribute.PropertyName);
break;
}
}
//The the property was mapped explicitely then try to find a
//property with the same name as the column.
if (propertyInfo == null)
{
propertyInfo = typeOfClass.GetProperty(columnName);
}
//If you found a property then set its value.
if (propertyInfo != null)
{
//Set the value to the value in the SqlDataReader
propertyInfo.SetValue(myClass, dr.GetValue(columnIndex));
}
}
return true;
}
else
{
return false;
}
}
private static IEnumerable<Dictionary<string, object>> SerializeReader(SqlDataReader reader)
{
var result = new List<Dictionary<string, object>>();
var cols = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
cols.Add(reader.GetName(i));
}
while (reader.Read())
{
result.Add(GetItem(cols, reader));
}
return result;
}
void AddRow(TableRow tr,SqlDataReader dr,bool bCaption)
{
for(int i=4;i<dr.FieldCount;i++)
{
TableCell tc = new TableCell();
if(bCaption==false)
tc.Text = dr.GetValue(i).ToString();
else
{
tc.Text = dr.GetName(i).ToString();
tc.BackColor = Color.FromArgb(0xf8,0xf8,0xf8);
}
tr.Controls.Add(tc);
}
}
private void csv_arc_Click(object sender, EventArgs e)
{
string cConnectiongSting = @"Data Source =" + data_src.Text + "; Initial Catalog =" + tabela.Text + "; User ID =" + user.Text + ";Password ="******"; Integrated Security = true";
SQL_CLIENT.SqlConnection con = new SQL_CLIENT.SqlConnection(cConnectiongSting);
con.Open();
SQL_CLIENT.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM ARC", con);
SQL_CLIENT.SqlDataReader dr = cmd.ExecuteReader();
using (System.IO.StreamWriter fs = new System.IO.StreamWriter(@"C:\Users\Marcin Jackowiak\Desktop\Projekt_AC\arc.cvs"))
{
fs.WriteLine("sep=,");
for (int i = 0; i < dr.FieldCount; i++)
{
string name = dr.GetName(i);
if (name.Contains(","))
{
name = "\"" + name + "\"";
}
fs.WriteLine(name + ",");
}
fs.WriteLine();
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
string value = dr[i].ToString();
if (value.Contains(","))
{
value = "\"" + value + "\"";
}
fs.Write(value + ",");
}
fs.WriteLine();
}
fs.Close();
}
}
/// <summary>
/// Populate a <see cref="QueryResult"/> with result-sets from the specified <see cref="SqlClient.SqlDataReader"/>.
/// </summary>
/// <param name="reader">
/// The <see cref="SqlClient.SqlDataReader"/> to read from.
/// </param>
/// <param name="queryResult">
/// The <see cref="QueryResult"/> to populate.
/// </param>
/// <returns>
/// A <see cref="Task"/> representing the operation.
/// </returns>
async Task ReadResults(SqlClient.SqlDataReader reader, QueryResult queryResult)
{
if (reader == null)
{
throw new ArgumentNullException(nameof(reader));
}
if (queryResult == null)
{
throw new ArgumentNullException(nameof(queryResult));
}
ResultSet resultSet = new ResultSet();
queryResult.ResultSets.Add(resultSet);
while (await reader.ReadAsync())
{
var row = new ResultRow();
resultSet.Rows.Add(row);
for (int fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
{
string fieldName = reader.GetName(fieldIndex);
if (!reader.IsDBNull(fieldIndex))
{
row.Columns[fieldName] = new JValue(
reader.GetValue(fieldIndex)
);
}
else
{
row.Columns[fieldName] = null;
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
Master.Title = "执行SQL语句";
builder.Append(Out.Tab("<div class=\"title\">执行SQL语句</div>", ""));
string act = Utils.GetRequest("act", "post", 1, "", "");
if (act == "ok")
{
string SqlText = Request.Form["SqlText"];
if (string.IsNullOrEmpty(SqlText))
{
Utils.Error("SQL语句不能为空", "");
}
SqlText = SqlText.ToLower();
string SqlKey = "exec,declare,netuser,xp_cmdshell,drop,master,create,alter";//truncate
string[] temp = SqlKey.Split(",".ToCharArray());
for (int i = 0; i < temp.Length; i++)
{
if (SqlText.Contains(temp[i]))
{
Utils.Error("为了系统安全，" + temp[i] + "方法已被禁止", "");
}
}
builder.Append(Out.Tab("<div>", ""));
try
{
if (!SqlText.Contains("select"))
{
int rows = BCW.Data.SqlHelper.ExecuteSql(SqlText.Trim());
builder.Append("执行" + SqlText + "成功|影响" + rows + "行");
}
else
{
builder.Append("执行" + SqlText + "<br />");
using (System.Data.SqlClient.SqlDataReader reader = BCW.Data.SqlHelper.ExecuteReader(SqlText))
{
int fieldCount = reader.FieldCount;
builder.Append("<table><tr>");
for (int i = 0; i < fieldCount; i++)
{
builder.Append("<td>" + reader.GetName(i) + "</td>");
}
builder.Append("</tr>");
while (reader.Read())
{
builder.Append("<tr>");
for (int i = 0; i < fieldCount; i++)
{
builder.Append("<td><textarea>");
if (!reader.IsDBNull(i))
{
builder.Append(GetObjectValue(reader, i));
}
builder.Append("</textarea></td>");
}
builder.Append("</tr>");
}
builder.Append("</table>");
}
}
}
catch
{
builder.Append("执行" + SqlText + "失败");
}
builder.Append(Out.Tab("</div>", ""));
builder.Append(Out.Tab("<div>", "<br />"));
builder.Append("<a href=\"" + Utils.getUrl("datasql.aspx") + "\">返回上一级</a>");
builder.Append(Out.Tab("</div>", ""));
}
else
{
builder.Append(Out.Tab("<div>", ""));
builder.Append("语句案例:<br />插入语句: insert into 表名(字段1,字段2)values('内容1','内容2')<br />更新语句: update 表名 set 字段1='内容1',字段2='内容2' where 字段3='内容3'<br />删除语句: delete from 表名 where 字段='内容'<br />查询语句: select top 显示的记录数目 字段1,字段2 from 表名 where 字段1='内容1'");
builder.Append(Out.Tab("</div>", ""));
string strText = "输入一条SQL语句:/,";
string strName = "SqlText,act";
string strType = "big,hidden";
string strValu = "'ok";
string strEmpt = "true,false";
string strIdea = "/";
string strOthe = "确定执行|reset,datasql.aspx,post,1,red|blue";
builder.Append(Out.wapform(strText, strName, strType, strValu, strEmpt, strIdea, strOthe));
builder.Append(Out.Tab("<div>", "<br />"));
builder.Append("重要提示:<br />必须先备份数据库再进行操作.<br />必须熟悉SQL语句才可以进行操作.");
builder.Append(Out.Tab("</div>", ""));
}
builder.Append(Out.Tab("<div class=\"hr\"></div>", Out.Hr()));
builder.Append(Out.Tab("<div>", ""));
builder.Append("<a href=\"" + Utils.getUrl("default.aspx") + "\">系统服务中心</a><br />");
builder.Append(Out.Tab("</div><div class=\"title\"><a href=\"" + Utils.getUrl("../default.aspx") + "\">返回管理中心</a>", "<a href=\"" + Utils.getUrl("../default.aspx") + "\">返回管理中心</a>"));
builder.Append(Out.Tab("</div>", "<br />"));
}
