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;
}
public void Property(string key, SqlDataReader reader, int idx)
{
jsonWriter.WritePropertyName(key);
string typeName = reader.GetDataTypeName(idx);
if (reader.IsDBNull(idx))
jsonWriter.WriteNull();
else if (typeName == "decimal")
jsonWriter.WriteValue(reader.GetDecimal(idx));
else if (typeName == "datetime")
jsonWriter.WriteValue(reader.GetDateTime(idx));
else if (typeName == "varchar" || typeName == "nvarchar")
jsonWriter.WriteValue(reader.GetString(idx));
else
throw new Exception("Unknown data type " + typeName);
}
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 DateTime GetDbDateTime(SqlDataReader myReader, int myColumnNo)
{
DateTime myData = new DateTime(1900, 1, 1);
string data = "";
string datatype = "";
try
{
if (!myReader.IsDBNull(myColumnNo))
{
datatype = myReader.GetDataTypeName(myColumnNo);
if (datatype.Equals("datetime") || datatype.Equals("date"))
{
myData = myReader.GetDateTime(myColumnNo);
}
else if (datatype.Equals("varchar"))
{
data = myReader.GetString(myColumnNo);
//Debug.Print("varchar data [" + data + "]");
if (data.Length <= 0)
myData = new DateTime(1900, 1, 1);
else if (data.IndexOf("x") >= 0)
Debug.Print("varchar mask data [" + data + "]");
else
myData = Convert.ToDateTime(data);
}
}
else
myData = new DateTime(1900, 1, 1);
}
catch (Exception)
{
myData = new DateTime(1900, 1, 1);
}
return myData;
}
public void GetDataTypeNameTest ()
{
cmd.CommandText = "Select id, type_tinyint, 10,null from numeric_family where id=1";
reader = cmd.ExecuteReader ();
Assert.AreEqual ("tinyint", reader.GetDataTypeName(1), "#1");
Assert.AreEqual ("int", reader.GetDataTypeName(2), "#2");
//need check on windows
Assert.AreEqual ("int", reader.GetDataTypeName(3), "#3");
try {
reader.GetDataTypeName (10);
Assert.Fail ("#4 Exception shud be thrown");
} catch (IndexOutOfRangeException e) {
Assert.AreEqual (typeof (IndexOutOfRangeException), e.GetType (),
"#5 Incorrect Exception : " + e);
}
}
protected void Page_Load(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand();
cmd1.CommandType = System.Data.CommandType.Text;
cmd1.Connection = sqlConnection1;
cmd1.CommandText = "SELECT * FROM portfolio_funds ;";
sqlConnection1.Open();
System.Data.SqlClient.SqlDataReader reader2 = cmd1.ExecuteReader();
reader2.Close();
reader2 = cmd1.ExecuteReader();
TableHeaderCell header = new TableHeaderCell();
header.RowSpan = 1;
header.ColumnSpan = 3;
header.Text = "Показатели счета";
header.Font.Bold = true;
header.HorizontalAlign = HorizontalAlign.Center;
header.VerticalAlign = VerticalAlign.Middle;
// Add the header to a new row.
TableRow headerRow = new TableRow();
headerRow.Cells.Add(header);
// Add the header row to the table.
Table1.Rows.AddAt(0, headerRow);
Table1.BorderWidth = 1;
Table1.CellPadding = 5;
if (reader2.HasRows)
{
while (reader2.Read())
{
TableRow r = new TableRow();
for (int i = 0; i <= 7; i++)
{
TableCell c = new TableCell();
if (reader2.GetDataTypeName(i) == "nvarchar" || reader2.GetDataTypeName(i) == "nchar")
{
c.Text = reader2.GetString(i);
}
else
{
c.Text = reader2.GetDouble(i).ToString();
}
r.HorizontalAlign = HorizontalAlign.Center;
r.BorderWidth = 1;
c.HorizontalAlign = HorizontalAlign.Center;
c.BorderWidth = 1;
r.Cells.Add(c);
}
Table1.Rows.Add(r);
}
}
cmd1.CommandText = "SELECT * FROM dbo.[Current] where Ticker = 'SiZ6' or Ticker = 'SNGSP' or Ticker = 'BRX6' ;";
reader2.Close();
System.Data.SqlClient.SqlDataReader reader = cmd1.ExecuteReader();
header = new TableHeaderCell();
header.RowSpan = 1;
header.ColumnSpan = 4;
header.Text = "Текущие цены";
header.Font.Bold = true;
header.HorizontalAlign = HorizontalAlign.Center;
header.VerticalAlign = VerticalAlign.Middle;
// Add the header to a new row.
headerRow = new TableRow();
headerRow.Cells.Add(header);
// Add the header row to the table.
Table2.Rows.AddAt(0, headerRow);
Table2.BorderWidth = 1;
Table2.CellPadding = 5;
if (reader.HasRows)
{
while (reader.Read())
{
TableRow r = new TableRow();
for (int i = 0; i <= 3; i++)
{
TableCell c = new TableCell();
if (reader.GetDataTypeName(i) == "nvarchar" || reader.GetDataTypeName(i) == "nchar")
{
c.Text = reader.GetString(i);
}
else
{
c.Text = reader.GetDouble(i).ToString();
}
r.HorizontalAlign = HorizontalAlign.Center;
r.BorderWidth = 1;
c.HorizontalAlign = HorizontalAlign.Center;
c.BorderWidth = 1;
r.Cells.Add(c);
}
TableCell b = new TableCell();
// Создать новый объект кнопки.
LinkButton LinkBtn = new LinkButton();
LinkBtn.CommandArgument = reader.GetString(0);
LinkBtn.CommandName = reader.GetString(0);
LinkBtn.Command += new CommandEventHandler(LinkBtn_Command);
LinkBtn.Text = "Удалить";
//Page.Form.Controls.Add(LinkBtn);
// b.Text = reader.GetString(0);
b.Controls.Add(LinkBtn);
///r.Cells.Add(b);
r.Cells.Add(b);
Table2.Rows.Add(r);
}
}
reader.Close();
sqlConnection1.Close();
Button1.Click += new EventHandler(this.GreetingBtn_Click);
/* if (IsPostBack)
* {
* GuestResponse rsvp = new GuestResponse();
*
* if (TryUpdateModel(rsvp, new FormValueProvider(ModelBindingExecutionContext)))
* {
* ResponseRepository.GetRepository().AddResponse(rsvp);
*
*
*
* if (rsvp.WillAttend.HasValue && rsvp.WillAttend.Value)
* {
*
* string path = @"d:\\MyTest.txt";
*
* try
* {
*
*
* // Create the file.
* using (FileStream fs = File.Create(path))
* {
* Byte[] info = new UTF8Encoding(true).GetBytes(rsvp.Name.ToString());
* // Add some information to the file.
*
* fs.Write(info, 0, info.Length);
* }
*
*
* }
*
* catch (Exception ex)
* {
* ;
* }
* Response.Redirect("myweb.aspx");
* }
* else
* {
* Response.Redirect("myweb.aspx");
* }
* }
* }*/
}
/// <summary>
/// Converts an individual SqlDataReader row referenced by its column index integer into JSON
/// </summary>
/// <param name="dr">SqlDataReader - the row returned from the SQL select operation</param>
/// <param name="colno">The column index used to look up the column meta-data</param>
/// <returns>String</returns>
public static String JsonFormat(SqlDataReader dr, Int32 colno)
{
String result = String.Empty;
String sval = String.Empty;
switch (dr.GetDataTypeName(colno).ToLower())
{
case "datetime":
case "datetime2":
sval = dr.GetValue(colno).ToString().Trim();
if (sval.Length < 1)
result = String.Empty;
else {
CultureInfo ci = CultureInfo.InvariantCulture;
DateTime rdate = DateTime.Parse(sval);
//result = string.Format("\"{0}\":\"{1}\"", dr.GetName(colno), rdate.ToString("MM/dd/yyyy HH:mm:ss.FFF", ci.DateTimeFormat));
// format to JavaScript DateTime formatting
result = String.Format("\"{0}\":new Date({1})", dr.GetName(colno), ToUnixTime(rdate));
}
break;
case "varchar":
case "nvarchar":
case "uniqueidentifier":
case "smalldatetime":
case "char":
case "nchar":
case "date":
case "text":
case "ntext":
case "string":
sval = dr.GetValue(colno).ToString().Trim();
if (sval.Length < 1)
result = String.Empty;
//is this a json object column?
else if (sval.StartsWith("{"))
{
if (sval.Substring(1, sval.Length - 1).Trim().Length < 1)//ignore it if it is empty
sval = String.Empty;
else
sval = String.Format("\"{0}\":{1}", dr.GetName(colno), dr.GetValue(colno));
result = sval;
}
//is this a json array column?
else if (sval.StartsWith("["))
{
if (sval.Substring(1, sval.Length - 1).Trim().Length < 1)//ignore it if it is empty
sval = String.Empty;
else
sval = String.Format("\"{0}\":{1}", dr.GetName(colno), dr.GetValue(colno));
result = sval;
}
else
result = string.Format("\"{0}\":\"{1}\"", dr.GetName(colno), JsonEscape(sval));
break;
case "int":
case "smallint":
case "tinyint":
case "decimal":
case "money":
case "bigint":
case "float":
case "numeric":
case "number":
case "real":
case "smallmoney":
result = string.Format("\"{0}\":{1}", dr.GetName(colno), dr.GetValue(colno));
break;
case "boolean":
case "bit":
result = string.Format("\"{0}\":{1}", dr.GetName(colno), dr.GetValue(colno).Equals(true) ? "true" : "false");
break;
default:
result = String.Empty;
break;
}
return result;
}
