使用DataReader读取日期
本文关键字：取日期 读取 DataReader 使用 | 更新日期: 2023-09-27 17:49:35
我用数据读取器使用这种格式读取字符串。如何使用类似格式在约会中阅读？
while (MyReader.Read())
{
TextBox1.Text = (string)MyReader["Note"];
}
按照下面给出的方法尝试：
while (MyReader.Read())
{
TextBox1.Text = Convert.ToDateTime(MyReader["DateField"]).ToString("dd/MM/yyyy");
}
在ToString()方法中，您可以根据需要更改数据格式。
如果查询的列具有适当的类型，则
var dateString = MyReader.GetDateTime(MyReader.GetOrdinal("column")).ToString(myDateFormat)
如果查询的列实际上是一个字符串，则查看其他答案。
(DateTime)MyReader["ColumnName"];
或
Convert.ToDateTime(MyReader["ColumnName"]);
这似乎有点偏离主题，但这是我在想当你在c#中读一列dateTime时会发生什么时看到的帖子。这篇帖子反映了我希望能够找到的关于这一机制的信息如果您担心utc和时区，请阅读
我做了更多的研究，因为我总是对DateTime作为一个类非常谨慎，因为它会自动假设你使用的时区，而且很容易混淆当地时间和utc时间。
我在这里试图避免的是DateTime进入"哦，看我正在运行的计算机在时区x，因此这个时间也必须在时区x中，当我被问及我的值时，我会像在那个时区一样回答">
我正试着阅读datetime2专栏。
您从sql server返回的日期时间将是Kind.Unspecified，这似乎意味着它将被视为UTC，这正是我想要的。
当阅读date专栏时，你也必须将其读为DateTime，即使它没有时间，而且更容易被时区搞砸(就像午夜一样(。
我当然认为这是读取DateTime的更安全的方式，因为我怀疑它可能可以通过sql server中的设置或c#中的静态设置进行修改：
var time = reader.GetDateTime(1);
var utcTime = new DateTime(time.Ticks, DateTimeKind.Utc);
从那里你可以获得组件(天、月、年(等，并按照你喜欢的方式格式化。
如果你拥有的实际上是一个日期+一个时间，那么Utc可能不是你想要的——因为你在处理客户，你可能需要先将其转换为当地时间(取决于时间的含义(。然而，这打开了一整罐蠕虫。。如果你需要这样做，我建议你使用像noda time这样的图书馆。标准库中有TimeZoneInfo，但经过简单的研究，它似乎没有一套合适的时区。您可以使用TimeZoneInfo.GetSystemTimeZones(); 方法查看TimeZoneInfo提供的列表
我还发现sql server management studio在显示时间之前不会将时间转换为本地时间。这是一种解脱！
我知道这是一个老问题，但我很惊讶没有答案提到GetDateTime:
获取指定列的值作为
DateTime对象。
你可以像这样使用：
while (MyReader.Read())
{
TextBox1.Text = MyReader.GetDateTime(columnPosition).ToString("dd/MM/yyyy");
}
/// <summary>
/// Returns a new conContractorEntity instance filled with the DataReader's current record data
/// </summary>
protected virtual conContractorEntity GetContractorFromReader(IDataReader reader)
{
return new conContractorEntity()
{
ConId = reader["conId"].ToString().Length > 0 ? int.Parse(reader["conId"].ToString()) : 0,
ConEmail = reader["conEmail"].ToString(),
ConCopyAdr = reader["conCopyAdr"].ToString().Length > 0 ? bool.Parse(reader["conCopyAdr"].ToString()) : true,
ConCreateTime = reader["conCreateTime"].ToString().Length > 0 ? DateTime.Parse(reader["conCreateTime"].ToString()) : DateTime.MinValue
};
}
或
/// <summary>
/// Returns a new conContractorEntity instance filled with the DataReader's current record data
/// </summary>
protected virtual conContractorEntity GetContractorFromReader(IDataReader reader)
{
return new conContractorEntity()
{
ConId = GetValue<int>(reader["conId"]),
ConEmail = reader["conEmail"].ToString(),
ConCopyAdr = GetValue<bool>(reader["conCopyAdr"], true),
ConCreateTime = GetValue<DateTime>(reader["conCreateTime"])
};
}
// Base methods
protected T GetValue<T>(object obj)
{
if (typeof(DBNull) != obj.GetType())
{
return (T)Convert.ChangeType(obj, typeof(T));
}
return default(T);
}
protected T GetValue<T>(object obj, object defaultValue)
{
if (typeof(DBNull) != obj.GetType())
{
return (T)Convert.ChangeType(obj, typeof(T));
}
return (T)defaultValue;
}
在我的案例中，我将SQL数据库中的datetime字段更改为不允许为null。SqlDataReader允许我将该值直接转换为DateTime。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Library
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)'MSSQLLocalDB;AttachDbFilename=C:'Users'NIKHIL R'Documents'Library.mdf;Integrated Security=True;Connect Timeout=30");
string query = "INSERT INTO [Table] (BookName , AuthorName , Category) VALUES('" + textBox1.Text.ToString() + "' , '" + textBox2.Text.ToString() + "' , '" + textBox3.Text.ToString() + "')";
SqlCommand com = new SqlCommand(query, con);
con.Open();
com.ExecuteNonQuery();
con.Close();
MessageBox.Show("Entry Added");
}
private void button3_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)'MSSQLLocalDB;AttachDbFilename=C:'Users'NIKHIL R'Documents'Library.mdf;Integrated Security=True;Connect Timeout=30");
string query = "SELECT * FROM [TABLE] WHERE BookName='" + textBox1.Text.ToString() + "' OR AuthorName='" + textBox2.Text.ToString() + "'";
string query1 = "SELECT BookStatus FROM [Table] where BookName='" + textBox1.Text.ToString() + "'";
string query2 = "SELECT DateOfReturn FROM [Table] where BookName='" + textBox1.Text.ToString() + "'";
SqlCommand com = new SqlCommand(query, con);
SqlDataReader dr, dr1,dr2;
con.Open();
com.ExecuteNonQuery();
dr = com.ExecuteReader();
if (dr.Read())
{
con.Close();
con.Open();
SqlCommand com1 = new SqlCommand(query1, con);
com1.ExecuteNonQuery();
dr1 = com1.ExecuteReader();
dr1.Read();
string i = dr1["BookStatus"].ToString();
if (i =="1" )
{
con.Close();
con.Open();
SqlCommand com2 = new SqlCommand(query2, con);
com2.ExecuteNonQuery();
dr2 = com2.ExecuteReader();
dr2.Read();
MessageBox.Show("This book is already issued'n " + "Book will be available by "+ dr2["DateOfReturn"] );
}
else
{
con.Close();
con.Open();
dr = com.ExecuteReader();
dr.Read();
MessageBox.Show("BookFound'n" + "BookName=" + dr["BookName"] + "'n AuthorName=" + dr["AuthorName"]);
}
con.Close();
}
else
{
MessageBox.Show("This Book is not available in the library");
}
}
private void button2_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)'MSSQLLocalDB;AttachDbFilename=C:'Users'NIKHIL R'Documents'Library.mdf;Integrated Security=True;Connect Timeout=30");
string query = "SELECT * FROM [TABLE] WHERE BookName='" + textBox1.Text.ToString() + "'";
string dateofissue1 = DateTime.Today.ToString("dd-MM-yyyy");
string dateofreturn = DateTime.Today.AddDays(15).ToString("dd-MM-yyyy");
string query1 = "update [Table] set BookStatus=1,DateofIssue='"+ dateofissue1 +"',DateOfReturn='"+ dateofreturn +"' where BookName='" + textBox1.Text.ToString() + "'";
con.Open();
SqlCommand com = new SqlCommand(query, con);
SqlDataReader dr;
com.ExecuteNonQuery();
dr = com.ExecuteReader();
if (dr.Read())
{
con.Close();
con.Open();
string dateofissue = DateTime.Today.ToString("dd-MM-yyyy");
textBox4.Text = dateofissue;
textBox5.Text = DateTime.Today.AddDays(15).ToString("dd-MM-yyyy");
SqlCommand com1 = new SqlCommand(query1, con);
com1.ExecuteNonQuery();
MessageBox.Show("Book Isuued");
}
else
{
MessageBox.Show("Book Not Found");
}
con.Close();
}
private void button4_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)'MSSQLLocalDB;AttachDbFilename=C:'Users'NIKHIL R'Documents'Library.mdf;Integrated Security=True;Connect Timeout=30");
string query1 = "update [Table] set BookStatus=0 WHERE BookName='"+textBox1.Text.ToString()+"'";
con.Open();
SqlCommand com = new SqlCommand(query1, con);
com.ExecuteNonQuery();
string today = DateTime.Today.ToString("dd-MM-yyyy");
DateTime today1 = DateTime.Parse(today);
string query = "SELECT dateofReturn from [Table] where BookName='" + textBox1.Text.ToString() + "'";
con.Close();
con.Open();
SqlDataReader dr;
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
dr.Read();
string DOR = dr["DateOfReturn"].ToString();
DateTime dor = DateTime.Parse(DOR);
TimeSpan ts = today1.Subtract(dor);
string query2 = "update [Table] set DateOfIssue=NULL, DateOfReturn=NULL WHERE BookName='" + textBox1.Text.ToString() + "'";
con.Close();
con.Open();
SqlCommand com2 = new SqlCommand(query2, con);
com2.ExecuteNonQuery();
int x = int.Parse(ts.Days.ToString());
if (x > 0)
{
int fine = x * 5;
textBox6.Text = fine.ToString();
MessageBox.Show("Book Received'nFine=" + fine);
}
else
{
textBox6.Text = "0";
MessageBox.Show("Book Received'nFine=0");
}
con.Close();
}
}
}