/*本文档主要讲述.net环境下SQL Server的读写操作*/
1、在头文件中定义SqlConnection
SqlConnection ^ConnSql;
2、链接SQL Server数据库
buttonConnectToDB_Click函数为按钮“连接数据库”的响应函数。
private: System::Void buttonConnectToDB_Click(System::Object^ sender, System::EventArgs^ e) {
if (Lin_BConnectedToDB == false)
{
Lin_DataSource = textBoxDataSource->Text;
Lin_DataBaseName = textBoxInitialCatalog->Text;
//Windows验证访问，使用下面这行代码。
String^ strConn = "Data Source = " + Lin_DataSource + ";Initial Catalog=" + Lin_DataBaseName + ";Integrated Security=True";//SQL Server链接字符串
//sa账户密码访问用下面这行代码。
//String^ strConn = "Data Source = " + Lin_DataSource + ";Initial Catalog=" + Lin_DataBaseName + ";uid = sa;pwd = 123456";//SQL Server链接字符串
ConnSql = gcnew SqlConnection(strConn); //Sql链接类的实例化
try
{
ConnSql->Open();//打开数据库
if (ConnSql->State == ConnectionState::Open)
{
Lin_BConnectedToDB = true;
label15->Text = "系统状态：成功连接数据库!";
}
}
catch (Exception ^sb)
{
MessageBox::Show("数据库..连不上...");
}
}
}
3、从数据库读取数据
bool FormMulti::whu_ReadDataFromDB(double angle,array<short>^DestData)
{
bool m_BRead = false;
String ^m_AngleStr = String::Format("{0:##0.0}",angle);
String ^strSQL = "SELECT [Temperature] FROM dbo.SourceData2 where angle = " + m_AngleStr;
array<byte>^ m_byte = gcnew array<byte>(DestData->Length*sizeof(short));
SqlCommand ^cmd = gcnew SqlCommand();
cmd->Connection = ConnSql;
cmd->CommandText = strSQL;
SqlDataReader ^sdr = cmd->ExecuteReader();
while(sdr->Read())//读最新的数据//
{
sdr->GetBytes(0,0,m_byte,0,m_byte->Length);
System::Buffer::BlockCopy(m_byte,0,DestData,0,m_byte->Length);
m_BRead = true;
}
sdr->Close();
return m_BRead;
}
4、写入数据到SQL Server数据库
bool FormMulti::whu_SaveDataToDB()
{
DateTime ^now = DateTime::Now;
System::Windows::Forms::DateTimePicker^ dateTimePicker2 = gcnew System::Windows::Forms::DateTimePicker();
String ^m_time = now->ToString("yyyy-MM-dd HH:mm:ss:fff");
////写入：
array<short> ^m_AngleWithValues = gcnew array<short>(Lin_AngleWithValues->Length/(360*AngleAccuracy));
for (int i=0;i<360*AngleAccuracy;i++)
{
int pos = i*(Lin_AngleWithValues->Length/(360*AngleAccuracy));
short m_VerifyValue = 0xffff;
if(Lin_AngleWithValues[pos]==m_VerifyValue)
{
for (int j=0;j<m_AngleWithValues->Length;j++)
{
m_AngleWithValues[j] = Lin_AngleWithValues[j+pos];
}
double My_Angle = ((double)Lin_AngleWithValues[pos+1])/(double)AngleAccuracy;
//存入数据//
String ^m_InsertImageSQL = "insert into dbo.SourceData2(time,angle,Temperature) values(@time,@angle,@Temperature)";
SqlCommand ^CmdObj = gcnew SqlCommand(m_InsertImageSQL, ConnSql);
//写入：
CmdObj->Parameters->Add("@time",SqlDbType::DateTime);
CmdObj->Parameters->Add("@angle", SqlDbType::Float );
CmdObj->Parameters->Add("@Temperature", SqlDbType::Image);
CmdObj->Parameters["@time"]->Value = now;
CmdObj->Parameters["@angle"]->Value = My_Angle;
array<Byte>^ m_ByteDestData = gcnew array<Byte>(sizeof(short)*(m_AngleWithValues->Length));
System::Buffer::BlockCopy(m_AngleWithValues,0,m_ByteDestData,0,m_ByteDestData->Length);
CmdObj->Parameters["@Temperature"]->Value = m_ByteDestData;
CmdObj->ExecuteNonQuery();
}
}
return true;
}
5、断开SQL Server数据库
private: System::Void buttonDisConnectToDB_Click(System::Object^ sender, System::EventArgs^ e) {
if (Lin_BConnectedToDB)
{
ConnSql->Close();//关闭数据库
Lin_BConnectedToDB = false;
label15->Text = "系统状态：未连接数据库!";
}
}