c#数据库操作大全

简介: 原文: c#数据库操作大全 1.提取单条记录 //using System.Data; //using System.Data.SqlClient;  using (SqlConnection cn = new SqlConnection(%%1)) //ConfigurationManager.

原文: c#数据库操作大全

1.提取单条记录
//using System.Data;
//using System.Data.SqlClient;
 using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
      {
          SqlCommand cmd = new SqlCommand("Select Count(*) From jobs",cn);
          cn.Open();
%%2=cmd.ExecuteScalar(); //Message.InnerHtml
      }

2.单值比较
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
      {
          SqlCommand cmd = new SqlCommand("Select Count(*) From jobs",cn);
          cn.Open();
%%2=cmd.ExecuteScalar(); //Message.InnerHtml
if(%%2==%%3)
{
%%4
}
      }

3.显示表格
//using System.Data;
//using System.Data.SqlClient;
DataSet ds=null;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
      {
          cn.Open();
cmd=new SqlDataAdapter("Select * From Author",cn);
ds=new DataSet();
cmd.Fill(ds,%%2); //"作者"
MyDataGrid.DataSource=ds.Tables(%%2).DefaultView; //"作者"
MyDataGrid.DataBind();
      }

4.操作表格
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
      {
          cn.Open();
cmd=new SqlDataAdapter("Select * From Author",cn);
ds=new DataSet();
cmd.Fill(ds,%%2); //"作者"
MyDataGrid.DataSource=ds.Tables(%%2).DefaultView; //"作者"
MyDataGrid.DataBind();
      }

5.数值范围查询
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
      {
 cn.Open();
int min = Int32.Parse(jcb1.selectedItem);
int max = Int32.Parse(jcb2.selectedItem);
cmd=new SqlDataAdapter("Select count(*) as pro_count From ProPrice where price between "
+ min + " and " + max,cn);
ds=new DataSet();
cmd.Fill(ds,%%2); //"作者"
MyDataGrid.DataSource=ds.Tables(%%2).DefaultView; //"作者"
MyDataGrid.DataBind();
      }

6.关闭时断开连接
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
      {
      }

7.执行命令
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
      {
          SqlCommand cmd = new SqlCommand("insert userRegister_t values('"
              + TextBox1.Text + "','" + TextBox2.Text + "')",cn);
          cn.Open();
          cmd.ExecuteNonQuery();
      }

7.Oracle8/8i/9i数据库(thin模式)
//using System.Data;
Oracle Set Your custom connection strings values 

ODBC
New version
 
Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;

 
Old version
 
Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;


OLE DB, OleDbConnection (.NET)
Standard security
This connection string uses a provider from Microsoft. 
Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

 
Standard Security
This connection string uses a provider from Oracle. 
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

 
Trusted Connection
 
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;

 
OracleConnection (.NET)
Standard
 
Data Source=MyOracleDB;Integrated Security=yes;

This one works only with Oracle 8i release 3 or later 
 
Specifying username and password
 
Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no;

This one works only with Oracle 8i release 3 or later 
Missing the System.Data.OracleClient namespace? Download .NET Managed Provider for Oracle >>
Great article! "Features of Oracle Data Provider for .NET" by Rama Mohan G. at C# Corner >>
 
Omiting tnsnames.ora
This is another type of Oracle connection string that doesn't rely on you to have a DSN for the connection. You create a connection string based on the format used in the tnsnames.ora file without the need to actually have one of these files on the client pc. 
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword;

 
Core Labs OraDirect (.NET)
Standard
 
User ID=myUsername;Password=myPassword;Host=ora;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;

Read more at Core Lab >>
And at the product page >>
 
Data Shape
MS Data Shape
 
Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;User Id=myUsername;Password=myPassword;

8.DB2数据库
string connString ="Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;";

9.SQL Server7.0/2000数据库
string connString ="Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;";
/*
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
*/
SQL Server2005数据库
string connString ="Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword";
/*
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
*/

10.Sybase数据库
string connString ="Data Source='myASEserver';Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"; //Sybase Adaptive Server Enterprise
string connString ="Data Source=//myserver/myvolume/mypat/mydd.add;User ID=myUsername;Password=myPassword;ServerType=REMOTE;"; //Sybase Advantage Database Server

11.Informix数据库
string connString ="Database=myDataBase;Host=192.168.10.10;Server=db_engine_tcp;Service=1492; Protocol=onsoctcp;UID=myUsername;Password=myPassword;";

12.MySQL数据库
string connString ="Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

13.PostgreSQL数据库
string connString ="User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase; Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;";

14.连接access数据库
string connString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/mydatabase.mdb;User Id=admin;Password=;";

15.连接MySql数据库
string connString ="Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

16.程序计时
    DateTime runTime = Convert.ToDateTime(System.Configuration.ConfigurationSettings.AppSettings["TimerRunAt"]);
    //运行时间
    TimeSpan runTime = new TimeSpan(runTime.Ticks);
    //现在时间
    TimeSpan timeNow = new TimeSpan(DateTime.Now.Ticks);
    //时间间隔
    TimeSpan ts = runTime.Subtract(timeNow);
    //如果运行时间与当前时间的差大于0,则任务执行时间为当日
    if(ts.TotalMilliseconds > 0) 
    {
        return ts.TotalMilliseconds;
    }
    //如果运行时间与当前时间的差小于0,则任务执行时间为次日
    Else
    {
        //差值的绝对值应加1天
        return ts.Duration().Add(new TimeSpan(1,0,0,0,0)).TotalMilliseconds;
    }

17.延时
//using System.Threading;
Thread.Sleep(Int32.Parse(%%1));

18.连接Excel文件
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excel文件名(绝对路径) +";Extended Properties=Excel 8.0;";

19.GDI+时钟
/*
using System.Data;
using System.Drawing;
using System.Drawing.Drawing2D;
*/
        private Point mickeyMouse = new Point(0, 0);
        private void Form1_Paint(object sender, PaintEventArgs e)
        {
            DrawClock(e.Graphics);
            timer1.Start();
        }

        private void DrawClock(Graphics g)
        {
            ///centre(120, 130);
            Rectangle outRect = new Rectangle(0, 0, 240, 260);
            Rectangle midRect = new Rectangle(7, 7, 226, 246);
            Rectangle inRect = new Rectangle(10, 10, 220, 240);
 
            LinearGradientBrush outlBlueBrush = new LinearGradientBrush(outRect, Color.FromArgb(0, 0, 100),
                Color.FromArgb(0, 0, 255), LinearGradientMode.BackwardDiagonal);
            LinearGradientBrush midlBlueBrush = new LinearGradientBrush(midRect, Color.FromArgb(0, 0, 255),
                Color.FromArgb(0, 0, 100), LinearGradientMode.BackwardDiagonal);
            LinearGradientBrush inlBlueBrush = new LinearGradientBrush(inRect, Color.FromArgb(0, 0, 100),
               Color.FromArgb(0, 0, 255), LinearGradientMode.BackwardDiagonal);
 
            g.FillEllipse(outlBlueBrush, outRect);
            g.FillEllipse(midlBlueBrush, midRect);
            g.FillEllipse(inlBlueBrush, inRect);
 
            outlBlueBrush.Dispose();
            midlBlueBrush.Dispose();
            inlBlueBrush.Dispose();
 
            //
            Font myFont = new Font("Arial", 20, FontStyle.Bold);
            SolidBrush whiteBrush = new SolidBrush(Color.White);
            g.DrawString("12", myFont, whiteBrush, 100, 10);
            g.DrawString("6", myFont, whiteBrush, 110, 223);
            g.DrawString("3", myFont, whiteBrush, 210, 120);
            g.DrawString("9", myFont, whiteBrush, 10, 120);
            g.DrawString("1", myFont, whiteBrush, 160, 26);
            g.DrawString("2", myFont, whiteBrush, 194, 64);
            g.DrawString("5", myFont, whiteBrush, 156, 210);
            g.DrawString("4", myFont, whiteBrush, 192, 174);
            g.DrawString("11", myFont, whiteBrush, 55, 28);
            g.DrawString("10", myFont, whiteBrush, 22, 66);
            g.DrawString("7", myFont, whiteBrush, 64, 210);
            g.DrawString("8", myFont, whiteBrush, 28, 174);
 
            myFont.Dispose();
            whiteBrush.Dispose();
            //DateTime;
            g.TranslateTransform(120, 130, MatrixOrder.Append);
 
            //
            Pen hourPen = new Pen(Color.White, 6);
            hourPen.SetLineCap(LineCap.RoundAnchor, LineCap.ArrowAnchor, DashCap.Flat);
            Pen minutePen = new Pen(Color.White, 4);
            minutePen.SetLineCap(LineCap.RoundAnchor, LineCap.ArrowAnchor, DashCap.Flat);
            Pen secondPen = new Pen(Color.Red, 2);
 
            int sec = DateTime.Now.Second;
            int min = DateTime.Now.Minute;
            int hour = DateTime.Now.Hour;
 
            double secondAngle = 2.0 * Math.PI * sec / 60.0;
            double minuteAngle = 2.0 * Math.PI * (min + sec / 60.0) / 60.0;
            double hourAngle = 2.0 * Math.PI * (hour + min / 60.0) / 12.0;
 
            Point centre = new Point(0, 0);
 
            Point hourHand = new Point((int)(40 * Math.Sin(hourAngle)),
            (int)(-40 * Math.Cos(hourAngle)));
            g.DrawLine(hourPen, centre, hourHand);
 
            Point minHand = new Point((int)(80 * Math.Sin(minuteAngle)),
            (int)(-80 * Math.Cos(minuteAngle)));
            g.DrawLine(minutePen, centre, minHand);
 
            Point secHand = new Point((int)(80 * Math.Sin(secondAngle)),
            (int)(-80 * Math.Cos(secondAngle)));
            g.DrawLine(secondPen, centre, secHand);
 
            hourPen.Dispose();
            minutePen.Dispose();
            secondPen.Dispose();
        }
 
        private void timer1_Tick(object sender, EventArgs e)
        {
            this.Invalidate();             
        }

        private void hideToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (contextMenuStrip1.Items[0].ToString() == "Hide")
            {
                this.Hide();
                contextMenuStrip1.Items[0].Text = "Show";
                return;
            }
 
            if (contextMenuStrip1.Items[0].ToString() == "Show")
            {
                this.Show();
                contextMenuStrip1.Items[0].Text = "Hide";
                return;
            }
        }
 
        private void Form1_MouseDown(object sender, MouseEventArgs e)
        {
            mickeyMouse = new Point(-e.X, -e.Y);
        }
 
        private void Form1_MouseMove(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                Point mousePos = Control.MousePosition;
                mousePos.Offset(mickeyMouse.X, mickeyMouse.Y);
                Location = mousePos;
            }
        }
 
        private void notifyIcon1_DoubleClick(object sender, EventArgs e)
        {
            if (contextMenuStrip1.Items[0].ToString() == "Show")
            {
                this.Show();
                contextMenuStrip1.Items[0].Text = "Hide";
                return;
            }
        }

20.数据库存入二进制字段数据
/*
using System.Data;
using System.IO;
using System.Data.SqlClient;
*/
private string File="";
if(openFileDialog1.ShowDialog()==DialogResult.OK)
{
pictureBox1.Image=new Bitmap(openFileDialog1.FileName);
FileName=openFileDialog1.FileName;
}
try{
DataSet ds=new DataSet();
sqlDataAdapter1.Fill(ds,%%1); //"db"
DataTable MyTable=ds.Tables[0];
DataRow MyRow=MyTable.Rows.Count+1;
MyRow[%%2]=MyTable.NewRow(); //"id"
MyRow[%%3]=%%5; //"FileName"
MyRow[%%4]=%%6; //"Description"
FileStream fs=new FileStream(FileNmae,FileMode.OpenOrCreate,FileAccess.Read);
byte[] MyData=new byte[fs.Length];
fs.Read(MyData,0,(int)fs.Length);
MyRow[%%7[=MyData; //"FileData"
MyTable.Rows.Add(MyRow);
sqlDataAdapter1.Update(ds,%%1);
ds.AcceptChanges();
//存储成功
}
catch(Exception ex)
{
//ex.Message.ToString()
}

21.数据库取出二进制字段数据
/*
using System.Data;
using System.IO;
*/
private int index=1;
private System.Windows.Forms.BindingManagerBase_Bind();
private DataSet ds=new DataSet();
sqlDataAdapter1.Fill(ds,%%1); //"db"
_Bind=BindingContext[ds,%%1];
textBox1.DataBindings.Add("Text",ds,"%%1.%%3"); //filename
textBox2.DataBindings.Add("Text",ds,"%%1.%%4"); //description
if(_Bind.Count!=0)
{
try{
if(pictureBox1.Image!=null)
pictureBox1.Image.Dispose();
pictureBox1.Image=null;
sqlDataAdapter1.SelectCommand.CommandText="Select * From %%1 Where %%2="+Convert.ToString(index); //id
DataSet dataSet=new DataSet();
sqlDataAdapter1.Fill(dataSet,%%1);
byte[] MyData=(byte[])dataSet.Tables[0].Rows[0][%%5]; //"FileData"
Int32 size=MyData.GetUpperBound(0);
FileStream fs=new FileStream(%%6,FileMode.OpenOrCreate,FileAccess.Wrtie); //"temp.bmp"
fs.Write(MyData,0,size+1);
fs.Close();
pictureBox1.Image=new Bitmap(%%6);
}
catch(Exception ex)
{
//ex.Message.ToString()
}
}

目录
相关文章
|
1月前
|
SQL 数据库 C#
C# .NET面试系列十一:数据库SQL查询(附建表语句)
#### 第1题 用一条 SQL 语句 查询出每门课都大于80 分的学生姓名 建表语句: ```sql create table tableA ( name varchar(10), kecheng varchar(10), fenshu int(11) ) DEFAULT CHARSET = 'utf8'; ``` 插入数据 ```sql insert into tableA values ('张三', '语文', 81); insert into tableA values ('张三', '数学', 75); insert into tableA values ('李四',
92 2
C# .NET面试系列十一:数据库SQL查询(附建表语句)
|
1月前
|
SQL 存储 数据库连接
C#SQL Server数据库基本操作(增、删、改、查)
C#SQL Server数据库基本操作(增、删、改、查)
18 0
|
1月前
|
SQL 存储 Oracle
C#利用IDbCommand实现通用数据库脚本执行程序
C#利用IDbCommand实现通用数据库脚本执行程序
|
1月前
|
SQL 存储 数据库连接
C#编程与数据库交互的实现
【4月更文挑战第20天】C#与数据库交互是现代软件开发的关键,涉及数据库连接、数据操作和访问方式。使用ADO.NET建立连接,执行SQL实现读取、插入、更新和删除数据。可通过直接SQL或数据访问对象进行操作。注意性能优化,使用连接池,处理异常,确保安全,以提升应用性能和稳定性。
|
1月前
|
存储 SQL 数据库
C# 将 Word 转文本存储到数据库并进行管理
C# 将 Word 转文本存储到数据库并进行管理
101 2
|
1月前
|
存储 SQL 数据库
C# 将 Word 转文本存储到数据库并进行管理
C# 将 Word 转文本存储到数据库并进行管理
|
1月前
|
SQL 存储 关系型数据库
C# .NET面试系列十:数据库概念知识
#### 1. 为什么要一定要设置主键? 设置主键是数据库设计中的一个重要概念,有几个主要原因: 1、唯一性 ```c# 主键必须保证表中的每一行都有唯一的标识。这样可以避免数据冗余和不一致性。如果没有主键或者主键不唯一,就可能出现数据混乱或错误。 ``` 2、查询性能 ```c# 数据库系统通常会使用主键来加速数据检索。主键通常会被索引,这样可以更快速地找到特定行的数据,提高查询效率。 ``` 3、关联性 ```c# 主键常常用于建立表与表之间的关系。在关系数据库中,一个表的主键通常与其他表中的外键建立关联,这种关系对于数据的一致性和完整性非常重要。 ``` 4、数据完
144 1
C# .NET面试系列十:数据库概念知识
|
1月前
|
C# 数据库 数据库管理
C#使用Poco链接SQLite数据库配置文件和错误的解决
C#使用Poco链接SQLite数据库配置文件和错误的解决
33 1
|
1月前
|
SQL 关系型数据库 MySQL
【Unity 3D】C#从mysql数据库中读取、封装SQL语句(附源码)
【Unity 3D】C#从mysql数据库中读取、封装SQL语句(附源码)
158 0
|
1月前
|
SQL 数据库连接 数据库
C# | 将DataGridView中的数据保存到Accesss数据库
要将WinForm的DataGridView中的数据保存到Access数据库,可以按照本文的步骤进行。 在Visual Studio中,打开项目,右键单击“引用”文件夹,选择“添加引用”,在“COM”选项卡中找到并选中“Microsoft Office 14.0 Access Database Engine Object Library”,然后单击“确定”按钮。
124 0
C# | 将DataGridView中的数据保存到Accesss数据库