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; }