我使用数据读取器以此格式读取了一个字符串。如何使用类似的格式读取日期?
while (MyReader.Read())
{
TextBox1.Text = (string)MyReader["Note"];
}
我使用数据读取器以此格式读取了一个字符串。如何使用类似的格式读取日期?
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)
如果查询的列实际上是一个字符串,请参见其他答案。
GetOrdinal
时一直都很不稳定。 - Richard (DateTime)MyReader["ColumnName"];
或者
Convert.ToDateTime(MyReader["ColumnName"]);
DateTime
类型。同时左侧的表达式接受String
类型。因此您仍然需要调用ToString()
方法。 - abatishchev虽然这似乎与主题略有偏离,但当我想知道在C#中将列读取为DateTime时会发生什么时,我遇到了这篇文章。这篇文章反映了我希望能够找到关于此机制的信息。
我进行了更多研究,因为我总是非常谨慎地使用DateTime类,因为它会自动假设你使用的是哪个时区,并且因为很容易混淆本地时间和UTC时间。
在这里我要避免的是DateTime去认为:“哦,看起来运行我的计算机在时区x,因此这个时间也必须是在时区x中,当被问及我的值时,我将回复好像我在那个时区”
我尝试读取一个datetime2列。
从sql server获取的日期时间最终将变成Kind.Unspecified,这似乎意味着它会被视为UTC,这正是我想要的。
当读取日期列时,即使没有时间并且更容易受到时区干扰(因为它是在午夜),您还必须将其作为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在显示时间之前不会将其转换为本地时间。这让我松了一口气!
DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc)
,这样你就不用手动操作ticks了 :) - jocull我知道这是一个老问题,但我很惊讶没有答案提到GetDateTime
:
获取指定列的值作为
DateTime
对象。
你可以像这样使用它:
while (MyReader.Read())
{
TextBox1.Text = MyReader.GetDateTime(columnPosition).ToString("dd/MM/yyyy");
}
SQLite
数据库,在其中我们以字符串格式输入日期。参考:SQLite数据类型。 - nam /// <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;
}
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();
}
}
}