调用数据库的问题

新建一个数据集-----连接数据库!---绑定表格-----Dataset1xsd

private void Form1_Load(object sender, SystemEventArgs e)

{

for(int i = dataGrid1ControlsCount -1;i>=0;i--)//循环表格

{

Control ctr = dataGrid1Controls[i];

if(ctr is DataGridTextBox)//删除表格中的单元格

{

dataGrid1ControlsRemoveAt(i);//删除

}

}

sqlDataAdapter1Fill(thisdataset11Flight);

}

private Dataset1FlightRow currentRow;

private void dataGrid1_CurrentCellChanged(object sender, SystemEventArgs e)//表格改变事件CurrentCellChanged

{

thisdataGrid1Select(dataGrid1CurrentRowIndex);//选中整行

DataRowView view=(DataRowView)thisBindingContext//绑定数据行视图[dataset11,"Flight"]Current;

currentRow=(Dataset1FlightRow)viewRow;

thistxtcodeText=currentRowFlightCode;//选中的行在文本框中显示

thistxtstartText=currentRowSource;

thistxtoverText=currentRowDestination;

}

--------------------完整的例子--------------------------------------

using System;

using SystemDrawing;

using SystemCollections;

using SystemComponentModel;

using SystemWindowsForms;

using SystemData;

namespace Flight

{

/// <summary>

/// Form1 的摘要说明。

/// </summary>

public class Form1 : SystemWindowsFormsForm

{

private SystemWindowsFormsDataGrid dataGrid1;

private SystemWindowsFormsTextBox txtcode;

private SystemWindowsFormsTextBox txtstart;

private SystemWindowsFormsTextBox txtover;

private SystemWindowsFormsButton button1;

private SystemWindowsFormsButton button2;

private SystemWindowsFormsButton button3;

private SystemWindowsFormsButton button4;

private SystemDataSqlClientSqlDataAdapter sqlDataAdapter1;

private SystemDataSqlClientSqlCommand sqlSelectCommand1;

private SystemDataSqlClientSqlCommand sqlInsertCommand1;

private SystemDataSqlClientSqlCommand sqlUpdateCommand1;

private SystemDataSqlClientSqlCommand sqlDeleteCommand1;

private SystemDataSqlClientSqlConnection conn;

private FlightDataset1 dataset11;

private SystemWindowsFormsDataGridTableStyle dataGridTableStyle1;

private SystemWindowsFormsDataGridTextBoxColumn dataGridTextBoxColumn1;

private SystemWindowsFormsDataGridTextBoxColumn dataGridTextBoxColumn2;

private SystemWindowsFormsDataGridTextBoxColumn dataGridTextBoxColumn4;

/// <summary>

/// 必需的设计器变量。

/// </summary>

private SystemComponentModelContainer components = null;

public Form1()

{

//

// Windows 窗体设计器支持所必需的

//

InitializeComponent();

//

// TODO: 在 InitializeComponent 调用后添加任何构造函数代码

//

}

/// <summary>

/// 清理所有正在使用的资源。

/// </summary>

protected override void Dispose( bool disposing )

{

if( disposing )

{

if (components != null)

{

componentsDispose();

}

}

baseDispose( disposing );

}

#region Windows 窗体设计器生成的代码

/// <summary>

/// 设计器支持所需的方法 - 不要使用代码编辑器修改

/// 此方法的内容。

/// </summary>

private void InitializeComponent()

{

thisdataGrid1 = new SystemWindowsFormsDataGrid();

thisdataset11 = new FlightDataset1();

thisdataGridTableStyle1 = new SystemWindowsFormsDataGridTableStyle();

thisdataGridTextBoxColumn1 = new SystemWindowsFormsDataGridTextBoxColumn();

thisdataGridTextBoxColumn2 = new SystemWindowsFormsDataGridTextBoxColumn();

thisdataGridTextBoxColumn4 = new SystemWindowsFormsDataGridTextBoxColumn();

thistxtcode = new SystemWindowsFormsTextBox();

thistxtstart = new SystemWindowsFormsTextBox();

thistxtover = new SystemWindowsFormsTextBox();

thisbutton1 = new SystemWindowsFormsButton();

thisbutton2 = new SystemWindowsFormsButton();

thisbutton3 = new SystemWindowsFormsButton();

thisbutton4 = new SystemWindowsFormsButton();

thissqlDataAdapter1 = new SystemDataSqlClientSqlDataAdapter();

thissqlDeleteCommand1 = new SystemDataSqlClientSqlCommand();

thisconn = new SystemDataSqlClientSqlConnection();

thissqlInsertCommand1 = new SystemDataSqlClientSqlCommand();

thissqlSelectCommand1 = new SystemDataSqlClientSqlCommand();

thissqlUpdateCommand1 = new SystemDataSqlClientSqlCommand();

((SystemComponentModelISupportInitialize)(thisdataGrid1))BeginInit();

((SystemComponentModelISupportInitialize)(thisdataset11))BeginInit();

thisSuspendLayout();

//

// dataGrid1

//

thisdataGrid1DataMember = "Flight";

thisdataGrid1DataSource = thisdataset11;

thisdataGrid1HeaderForeColor = SystemDrawingSystemColorsControlText;

thisdataGrid1Location = new SystemDrawingPoint(8, 8);

thisdataGrid1Name = "dataGrid1";

thisdataGrid1ReadOnly = true;

thisdataGrid1SelectionForeColor = SystemDrawingColorBrown;

thisdataGrid1Size = new SystemDrawingSize(312, 208);

thisdataGrid1TabIndex = 0;

thisdataGrid1TableStylesAddRange(new SystemWindowsFormsDataGridTableStyle[] {

thisdataGridTableStyle1});

thisdataGrid1Navigate += new SystemWindowsFormsNavigateEventHandler(thisdataGrid1_Navigate);

thisdataGrid1CurrentCellChanged += new SystemEventHandler(thisdataGrid1_CurrentCellChanged);

//

// dataset11

//

thisdataset11DataSetName = "Dataset1";

thisdataset11Locale = new SystemGlobalizationCultureInfo("en-US");

//

// dataGridTableStyle1

//

thisdataGridTableStyle1DataGrid = thisdataGrid1;

thisdataGridTableStyle1GridColumnStylesAddRange(new SystemWindowsFormsDataGridColumnStyle[] {

thisdataGridTextBoxColumn1,

thisdataGridTextBoxColumn2,

thisdataGridTextBoxColumn4});

thisdataGridTableStyle1HeaderForeColor = SystemDrawingSystemColorsControlText;

thisdataGridTableStyle1MappingName = "Flight";

//

// dataGridTextBoxColumn1

//

thisdataGridTextBoxColumn1Format = "";

thisdataGridTextBoxColumn1FormatInfo = null;

thisdataGridTextBoxColumn1HeaderText = "航班号";

thisdataGridTextBoxColumn1MappingName = "FlightCode";

thisdataGridTextBoxColumn1Width = 75;

//

// dataGridTextBoxColumn2

//

thisdataGridTextBoxColumn2Format = "";

thisdataGridTextBoxColumn2FormatInfo = null;

thisdataGridTextBoxColumn2HeaderText = "起点";

thisdataGridTextBoxColumn2MappingName = "Source";

thisdataGridTextBoxColumn2Width = 75;

//

// dataGridTextBoxColumn4

//

thisdataGridTextBoxColumn4Format = "";

thisdataGridTextBoxColumn4FormatInfo = null;

thisdataGridTextBoxColumn4HeaderText = "终点";

thisdataGridTextBoxColumn4MappingName = "Destination";

thisdataGridTextBoxColumn4Width = 75;

//

// txtcode

//

thistxtcodeLocation = new SystemDrawingPoint(336, 24);

thistxtcodeName = "txtcode";

thistxtcodeSize = new SystemDrawingSize(168, 21);

thistxtcodeTabIndex = 1;

thistxtcodeText = "";

//

// txtstart

//

thistxtstartLocation = new SystemDrawingPoint(336, 72);

thistxtstartName = "txtstart";

thistxtstartSize = new SystemDrawingSize(168, 21);

thistxtstartTabIndex = 2;

thistxtstartText = "";

//

// txtover

//

thistxtoverLocation = new SystemDrawingPoint(336, 120);

thistxtoverName = "txtover";

thistxtoverSize = new SystemDrawingSize(168, 21);

thistxtoverTabIndex = 3;

thistxtoverText = "";

//

// button1

//

thisbutton1Location = new SystemDrawingPoint(344, 152);

thisbutton1Name = "button1";

thisbutton1TabIndex = 4;

thisbutton1Text = "添加";

thisbutton1Click += new SystemEventHandler(thisbutton1_Click);

//

// button2

//

thisbutton2Location = new SystemDrawingPoint(344, 192);

thisbutton2Name = "button2";

thisbutton2TabIndex = 5;

thisbutton2Text = "删除";

thisbutton2Click += new SystemEventHandler(thisbutton2_Click);

//

// button3

//

thisbutton3Location = new SystemDrawingPoint(432, 152);

thisbutton3Name = "button3";

thisbutton3TabIndex = 6;

thisbutton3Text = "修改";

thisbutton3Click += new SystemEventHandler(thisbutton3_Click);

//

// button4

//

thisbutton4Location = new SystemDrawingPoint(432, 192);

thisbutton4Name = "button4";

thisbutton4TabIndex = 5;

thisbutton4Text = "退出";

thisbutton4Click += new SystemEventHandler(thisbutton4_Click);

//

// sqlDataAdapter1

//

thissqlDataAdapter1DeleteCommand = thissqlDeleteCommand1;

thissqlDataAdapter1InsertCommand = thissqlInsertCommand1;

thissqlDataAdapter1SelectCommand = thissqlSelectCommand1;

thissqlDataAdapter1TableMappingsAddRange(new SystemDataCommonDataTableMapping[] {

new SystemDataCommonDataTableMapping("Table", "Flight", new SystemDataCommonDataColumnMapping[] {

new SystemDataCommonDataColumnMapping("FlightCode", "FlightCode"),

new SystemDataCommonDataColumnMapping("Source", "Source"),

new SystemDataCommonDataColumnMapping("Destination", "Destination")})});

thissqlDataAdapter1UpdateCommand = thissqlUpdateCommand1;

//

// sqlDeleteCommand1

//

thissqlDeleteCommand1CommandText = "DELETE FROM Flight WHERE (FlightCode = @Original_FlightCode) AND (Destination = @" +

"Original_Destination OR @Original_Destination IS NULL AND Destination IS NULL) A" +

"ND (Source = @Original_Source OR @Original_Source IS NULL AND Source IS NULL)";

thissqlDeleteCommand1Connection = thisconn;

thissqlDeleteCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Original_FlightCode", SystemDataSqlDbTypeVarChar, 10, SystemDataParameterDirectionInput, false, ((SystemByte)(0)), ((SystemByte)(0)), "FlightCode", SystemDataDataRowVersionOriginal, null));

thissqlDeleteCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Original_Destination", SystemDataSqlDbTypeVarChar, 50, SystemDataParameterDirectionInput, false, ((SystemByte)(0)), ((SystemByte)(0)), "Destination", SystemDataDataRowVersionOriginal, null));

thissqlDeleteCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Original_Source", SystemDataSqlDbTypeVarChar, 50, SystemDataParameterDirectionInput, false, ((SystemByte)(0)), ((SystemByte)(0)), "Source", SystemDataDataRowVersionOriginal, null));

//

// conn

//

thisconnConnectionString = "workstation id=PC210;packet size=4096;integrated security=SSPI;initial catalog=Ai" +

"r;persist security info=False";

//

// sqlInsertCommand1

//

thissqlInsertCommand1CommandText = "INSERT INTO Flight(FlightCode, Source, Destination) VALUES (@FlightCode, @Source," +

" @Destination); SELECT FlightCode, Source, Destination FROM Flight WHERE (Flight" +

"Code = @FlightCode)";

thissqlInsertCommand1Connection = thisconn;

thissqlInsertCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@FlightCode", SystemDataSqlDbTypeVarChar, 10, "FlightCode"));

thissqlInsertCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Source", SystemDataSqlDbTypeVarChar, 50, "Source"));

thissqlInsertCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Destination", SystemDataSqlDbTypeVarChar, 50, "Destination"));

//

// sqlSelectCommand1

//

thissqlSelectCommand1CommandText = "SELECT FlightCode, Source, Destination FROM Flight";

thissqlSelectCommand1Connection = thisconn;

//

// sqlUpdateCommand1

//

thissqlUpdateCommand1CommandText = @"UPDATE Flight SET FlightCode = @FlightCode, Source = @Source, Destination = @Destination WHERE (FlightCode = @Original_FlightCode) AND (Destination = @Original_Destination OR @Original_Destination IS NULL AND Destination IS NULL) AND (Source = @Original_Source OR @Original_Source IS NULL AND Source IS NULL); SELECT FlightCode, Source, Destination FROM Flight WHERE (FlightCode = @FlightCode)";

thissqlUpdateCommand1Connection = thisconn;

thissqlUpdateCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@FlightCode", SystemDataSqlDbTypeVarChar, 10, "FlightCode"));

thissqlUpdateCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Source", SystemDataSqlDbTypeVarChar, 50, "Source"));

thissqlUpdateCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Destination", SystemDataSqlDbTypeVarChar, 50, "Destination"));

thissqlUpdateCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Original_FlightCode", SystemDataSqlDbTypeVarChar, 10, SystemDataParameterDirectionInput, false, ((SystemByte)(0)), ((SystemByte)(0)), "FlightCode", SystemDataDataRowVersionOriginal, null));

thissqlUpdateCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Original_Destination", SystemDataSqlDbTypeVarChar, 50, SystemDataParameterDirectionInput, false, ((SystemByte)(0)), ((SystemByte)(0)), "Destination", SystemDataDataRowVersionOriginal, null));

thissqlUpdateCommand1ParametersAdd(new SystemDataSqlClientSqlParameter("@Original_Source", SystemDataSqlDbTypeVarChar, 50, SystemDataParameterDirectionInput, false, ((SystemByte)(0)), ((SystemByte)(0)), "Source", SystemDataDataRowVersionOriginal, null));

//

// Form1

//

thisAutoScaleBaseSize = new SystemDrawingSize(6, 14);

thisClientSize = new SystemDrawingSize(528, 229);

thisControlsAdd(thisbutton3);

thisControlsAdd(thisbutton2);

thisControlsAdd(thisbutton1);

thisControlsAdd(thistxtover);

thisControlsAdd(thistxtstart);

thisControlsAdd(thistxtcode);

thisControlsAdd(thisdataGrid1);

thisControlsAdd(thisbutton4);

thisName = "Form1";

thisText = "Form1";

thisLoad += new SystemEventHandler(thisForm1_Load);

((SystemComponentModelISupportInitialize)(thisdataGrid1))EndInit();

((SystemComponentModelISupportInitialize)(thisdataset11))EndInit();

thisResumeLayout(false);

}

#endregion

/// <summary>

/// 应用程序的主入口点。

/// </summary>

[STAThread]

static void Main()

{

ApplicationRun(new Form1());

}

private void button4_Click(object sender, SystemEventArgs e)

{

ApplicationExit();

}

private void Form1_Load(object sender, SystemEventArgs e)

{

for(int i = dataGrid1ControlsCount -1;i>=0;i--)

{

Control ctr = dataGrid1Controls[i];

if(ctr is DataGridTextBox)

{

dataGrid1ControlsRemoveAt(i);

}

}

sqlDataAdapter1Fill(thisdataset11Flight);

}

private Dataset1FlightRow currentRow;

private void dataGrid1_CurrentCellChanged(object sender, SystemEventArgs e)

{

thisdataGrid1Select(dataGrid1CurrentRowIndex);//选中整行

DataRowView view=(DataRowView)thisBindingContext[dataset11,"Flight"]Current;

currentRow=(Dataset1FlightRow)viewRow;

thistxtcodeText=currentRowFlightCode;

thistxtstartText=currentRowSource;

thistxtoverText=currentRowDestination;

}

private void button3_Click(object sender, SystemEventArgs e)

{

currentRowFlightCode=thistxtcodeText;

currentRowSource=thistxtstartText;

currentRowDestination= thistxtoverText;

sqlDataAdapter1Update(thisdataset11Flight);

}

private void button1_Click(object sender, SystemEventArgs e)

{

DataRow newrow = dataset11Tables[0]NewRow();

newrow[0]=thistxtcodeText;

newrow[1]=thistxtstartText;

newrow[2]=thistxtoverText;

dataset11FlightRowsAdd(newrow);

sqlDataAdapter1Update(thisdataset11Flight);

}

private void button2_Click(object sender, SystemEventArgs e)

{

currentRowDelete();

sqlDataAdapter1Update(thisdataset11Flight);

MessageBoxShow("删除成功！！！");

}

private void dataGrid1_Navigate(object sender, SystemWindowsFormsNavigateEventArgs ne)

{

}

}

}

----------------------------数据库--------------------------------

create database Air

go

use Air

go

create table Flight

(

FlightCode varchar(10) primary key,

Source varchar(50),

Destination varchar(50)

)

go

insert into Flight values('AF0001','厦门','上海')

insert into Flight values('BH8888','厦门','北京')

insert into Flight values('DR8988','北京','上海')

insert into Flight values('UI6766','福州','上海')

insert into Flight values('FG5432','北京','西安')

insert into Flight values('QW4555','兰州','上海')

insert into Flight values('ES4333','北京','成都')

insert into Flight values('NB5666','南昌','上海')

select from Flight

-----------------------还要建设一个数据集----------------

选择项目---添加新建项-----数据集(不要改名)---从服务器资源管理器(左边的数据库)把上面的表格拉到里面搞定!!!

R中还有一个有趣的sqldf包，它可以让你用SQL来 *** 作dataframe，这种功能能让会R的人能练习SQL，会SQL的人能练习R，不得不感叹R语言的强大技能和神奇魅力。 \x0d\ 当然也可以将R与外部数据库连接，直接在R中 *** 作数据库，并生成最终结果，这也是一种可行的方法。在R中连接数据库需要安装其它的扩展包，根据连接方式不同我们有两种选择：一种是ODBC方式，需要安装RODBC包并安装ODBC驱动。另一种是DBI方式，可以根据已经安装的数据库类型来安装相应的驱动。因为后者保留了各数据库原本的特性，所以个人比较偏好用DBI连接方式。有下面这几种主要的包提供了DBI连接：RMySQL，RSQLite，ROracle，RPostgreSQL。由名字看得出它们分别对应了几种主流的数据库。 \x0d\ 注：以上文字来自R-blogger—_《在R语言中使用SQL》 \x0d\ 值得一提的是在运用sqldf包时可能要对R软件升级到310版以上，否则安装不会成功，这里简单介绍一种在Windows环境下对R软件自动升级的方法。 \x0d\ Windows上升级R语言：\x0d\ installpackages("installr"); \x0d\ library(installr) #加载包\x0d\ updateR()\x0d\\x0d\之后选择“确定”就可以自动更新了 \x0d\ 虽然在R语言中有很多优秀的函数如aggregate()和daply()可以对数据框统计，但sql功能强大，不仅能实现数据的清洗、统计、运算,还可以实现数据存储、控制、定义和调用。越来越多的公司在招聘的时候都要求数据分析师除了要掌握统计建模和数据挖掘的理论方法和编程能力外，还要求其具备使用sql的能力，楼主也在积极学习sql当中。 \x0d\ 下面是我利用sqldf()完成的一些程序,虽然简单，但这是学习的第一步\x0d\> name subject score stuid stuscore library(sqldf)\x0d\\x0d\1计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)\x0d\> sqldf('select name,sum(score) as allscore from stuscore group by name order by allscore')\x0d\ name allscore\x0d\1 张三 239\x0d\2 李四 240\x0d\\x0d\2计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)\x0d\> sqldf('select name,stuid,sum(score) as allscore from stuscore group by name order by allscore')\x0d\ name stuid allscore\x0d\1 张三 1 239\x0d\2 李四 2 240\x0d\\x0d\3计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)\x0d\> sqldf('select stuid,name,subject, max(score) as maxscore from stuscore group by stuid')\x0d\ stuid name subject maxscore\x0d\1 1 张三 数学 89\x0d\2 2 李四 数学 90\x0d\\x0d\4计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）\x0d\> sqldf('select stuid,name,subject, avg(score) as avgscore from stuscore group by stuid')\x0d\ stuid name subject avgscore\x0d\1 1 张三 英语 7966667\x0d\2 2 李四 英语 8000000\x0d\\x0d\5列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)\x0d\> sqldf('select stuid,name,subject,max(score) as maxscore from stuscore group by subject order by stuid')\x0d\ stuid name subject maxscore\x0d\1 1 张三 语文 80\x0d\2 2 李四 数学 90\x0d\3 2 李四 英语 80\x0d\\x0d\8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）\x0d\> sqldf('select subject,avg(score) as avgscore from stuscore group by subject')\x0d\ subject avgscore\x0d\1 数学 895\x0d\2 英语 750\x0d\3 语文 750

你首先需要加载 RODBC 的包，如果没有，请先安装，譬如我读一个Excel表格的数据（xlsx格式的），代码供你参考:

library(RODBC)

a <- odbcConnectExcel("bzzxlsx")

dd<-sqlFetch(a，"bzz")

close(a)

summary(data)

另外，仔细阅读RODBC包对应的PDF文档，会找到答案的。

以上就是关于调用数据库的问题全部的内容，包括:调用数据库的问题、如何在R语言中使用SQL语句、如何让R语言通过RODBC库读写数据库的数据等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！